Get all hierarchy elements who apply to filter

Question

Post reply

Get all hierarchy elements who apply to filter

ktflash

Default port

Points: 1400
More actions
October 28, 2019 at 8:50 am

#3692293
Ahoi,
the following cube elements are relevant:
[Ticket].[Tickethierarchie]   --> hierarchy which i need to find the relevant elements of
[Ticket].[Status]                    --> attribut has multiple elements which i need to filter the relevant hierarchy elements
[Measures].[Aufwand]         --> Measure which is used to filter aswell, > 0.1

What i need to do is find a filter for all [Ticket].[Tickethierarchie] where:
- Measure is > 0.1 AND [Ticket].[Status] is in one of these 4
```
FILTER(
             {
                     [Ticket].[Status].&[Geschlossen]
    ,[Ticket].[Status].&[Gelöst] 
    ,[Ticket].[Status].&[Abgelehnt - nicht realisierbar] 
    ,[Ticket].[Status].&[Duplikat] 

              },[Measures].[Aufwand] > 0.1
       )
```
- AND All Tickets where [Ticket].[Status] is any of these
```
                    {
[Ticket].[Status].&[Angenommen]
,[Ticket].[Status].&[Bewertung] 
,[Ticket].[Status].&[CAB relevant] 
,[Ticket].[Status].&[Eingereicht]
,[Ticket].[Status].&[Entwurf] 
,[Ticket].[Status].&[Freigabe] 
,[Ticket].[Status].&[Implementieren und Testen]
,[Ticket].[Status].&[Offen]
,[Ticket].[Status].&[Rollout] 
,[Ticket].[Status].&[Warte auf] 
,[Ticket].[Status].&[Zu prüfen]
     }
```
Basically i want all [Ticket].[Tickethierarchie] elements, except the the combination of where the Measure [Measures].[Aufwand] is 0 and the [Ticket].[Status] is one of these

{
[Ticket].[Status].&[Geschlossen]
,[Ticket].[Status].&[Gelöst]
,[Ticket].[Status].&[Abgelehnt - nicht realisierbar]
,[Ticket].[Status].&[Duplikat]
}

I want to be the very best
Like no one ever was
https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Site Owners SSC Guru Points: 80330 More actions · Answer 1

Thanks for posting your issue and hopefully someone will answer soon.

This is an automated bump to increase visibility of your question.

ktflash Default port Points: 1400 More actions · Answer 2

Ok, how do i merch these 2 Queries into 1 so i can use it as a column in a dimension as a filter?

This is 50% of it:

CREATE MEMBER CURRENTCUBE.[Ticket].[Status].[Relevanter Status]
 AS 
[Ticket].[Status].&[Angenommen]+
[Ticket].[Status].&[Bewertung] +
[Ticket].[Status].&[CAB relevant] +
[Ticket].[Status].&[Eingereicht]+
[Ticket].[Status].&[Entwurf] +
[Ticket].[Status].&[Freigabe] +
[Ticket].[Status].&[Implementieren und Testen]+
[Ticket].[Status].&[Offen]+
[Ticket].[Status].&[Rollout] +
[Ticket].[Status].&[Warte auf] +
[Ticket].[Status].&[Zu prüfen], 
VISIBLE = 1;

But i also need this to apply as OR, so either the top condiation or the one below:

SELECT [Measures].[Aufwand]  ON COLUMNS ,
filter(
                  [Ticket].[Tickethierarchie].Members,
                  Aggregate([Ticket].[Status].&[Geschlossen],[Measures].[Aufwand] < 0.1)
  OR
  Aggregate([Ticket].[Status].&[Gelöst],[Measures].[Aufwand] < 0.1)
  OR
  Aggregate([Ticket].[Status].&[Duplikat],[Measures].[Aufwand] < 0.1)
  OR
  Aggregate([Ticket].[Status].&[Abgelehnt - nicht realisierbar],[Measures].[Aufwand] < 0.1)
  ) ON ROWS

       FROM IT

Certain Status OR (Certain Status AND Measure is > 0.1)

I want to be the very best
Like no one ever was

https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help