Report Studio MDX question

[cogswell]

Hello, I have a quick question about a cube-based Report Studio (8.4) report I am writing. I am completely unfamiliar with MDX.

I hope you are familiar with the Great Outdoors dimensional dataset. Assume that I built a cube off that complete dataset.

I am trying to build a simple list report with two columns a)Product Name, and b) Amount. But, I want only the Product Names within the 'Camping Equipment' and 'Mountaineering Equipment' Product Lines to be shown. What should I put in the filter? I don't want to use a slicer.

My question in more generic terms is: how do I obtain a set of lowest-level members (say 'Product Name's) in a hierarchy for a particular intermediate-level-member (say 'Camping Equipment')?

[CognosPaul]

Let's begin with the terminology.

A tuple is the intersection of any measure with one or more members. In SQL you would have to write:

Code Select Expand

select sum(revenue) revenue from sales where year=2010 and country='Hersheba'
in MDX you could write

Code Select Expand

select tuple(revenue,2010,Hersheba) on rows

A slicer will essentially create a tuple on every measure. Suppose you have a list of years and sales. Every row can be considered a tuple: (2008,Sales) (2009,Sales) (2010,Sales). By placing a slicer, Camping Equipment for instance, every row could be thought of as: (2008,Sales,Camping Equipment) (2009,Sales,Camping Equipment) (2010,Sales,Camping Equipment). You could also put a specific year in the slicer: (2008,Sales,2010) (2009,Sales,2010) (2010,Sales,2010). In that scenario every row would be rendered, but only the 2010 row would have data.

The point to realize is that slicers will only effect measures.

A set is a logical grouping of members from the same hierarchy. {2009, 2008/Apr, 2010, 26/02/2014} is a legal set whereas {2009, Canada} is not legal since it pulls members from two separate hierarchies.

It sounds what you need is the descendants of a set of members {Camping Equipment,Mountaineering Equipment}.

I recommend reading the Cognos paper on Cognos 8 Dimension Functions found here.

When talking about dimensional reporting the word filter can be used to describe two separate things. There is a filter function that will filter a set based on Boolean conditions: filter(products,sales>1000) will return a set of all products where the sales were greater than 1000. Filter can also relate to Detail Filters that you see in the query.

Personally I feel that under no circumstances should detail filters ever be used when working with a cube. Detail filters on sets will be processed after the query is run. So if you have a list of years and sales, Cognos will pull the entire set of years and place the filter after it has been processed in the cube. The impact on performance is huge, and the results are unreliable. A filter on a measure will be processed as part of the MDX, but on every set. This can also cause unpredictable results.

When I supervise other authors, or work with clients on site, I will reject any dimensional report that uses a detail filter.

[cogswell]

Brilliant reply; thank you very much.

[cogswell]

Well, I need help again! :)

I have a dimension with four levels. I want to obtain a total of four items from the third level. They are the last item in that level and the fourth, seventh and eleventh items from the bottom respectively, i.e., lag = {3, 6, 9}. These are going to be the columns in a crosstab.

I tried formulating a single set expression (for a memberset), but wasn't successful because my MDX chops are, well, not even chips (sorry). So I tried adding one data item for each of the items, and checking the tabular data.

For Item1, the following worked perfectly (note that I had to use currentmember because otherwise descendants was returning ALL of them in spite of specifying the distance of 2):

Code Select Expand

tail(descendants(currentmember([DIMENSION]), 2))

For Item2, I tried the following:

Code Select Expand

lag(item([Item1], 0), 3)

It returned the correct member for Item2, but the same member for Item1! The expression for Item1 was working perfectly before!  ???

When I tried adding Item3, NO DATA was returned. What in the hell?

Code Select Expand

lag(item([Item1], 0), 6)

Please help; I am at my wits' end here. EDIT: I can't use lastperiods because the items are not sequential.

[CognosPaul]

There are a few things at work here that you should realize.

First, it looks like you're testing the query with "view tabular data". This will not work well when dealing with dimensional queries. It's better to drag the items into a crosstab to see what you get.

Second, you don't always need to use complex dimensional functions to get the result you need. Sometimes a simpler solution is best.

Are these items static? Will they always be Red, Blue, Purple, Octarine? Or will the members change and you'll always need to reference the location in the level?

The former possibility is the most common and easiest to deal with. Simply create a new data item, type in the expression: set() and drag all four members between the parens. That is a simple static set.

The latter possibility can be dealt with using the item() function. item(SET,5) will return the 6th member from SET. So the expression:
set(item([cube].[dim].[hier].[level3],3),item([cube].[dim].[hier].[level3],6),item([cube].[dim].[hier].[level3],9))

Will create a set of the fourth, seventh and tenth members of the referenced set or level.

Note when analyzing any Cognos generated MDX: When using a level as a set, Cognos will wrap the level in the members() command.  Thus item([cube].[dim].[hier].[level3],3) will really look something like item(members([cube].[dim].[hier].[level3]),3)

[cogswell]

There are a few things at work here that you should realize.

First, it looks like you're testing the query with "view tabular data". This will not work well when dealing with dimensional queries. It's better to drag the items into a crosstab to see what you get.

You're right. :) Tabular data gives inconsistent results. I guess I should have paid attention to that warning. I pulled them into a crosstab and they work splendidly. I must say I am enjoying working with dimensional data a lot more.

Second, you don't always need to use complex dimensional functions to get the result you need. Sometimes a simpler solution is best.

Are these items static? Will they always be Red, Blue, Purple, Octarine? Or will the members change and you'll always need to reference the location in the level?

The former possibility is the most common and easiest to deal with. Simply create a new data item, type in the expression: set() and drag all four members between the parens. That is a simple static set.

The latter possibility can be dealt with using the item() function. item(SET,5) will return the 6th member from SET. So the expression:
set(item([cube].[dim].[hier].[level3],3),item([cube].[dim].[hier].[level3],6),item([cube].[dim].[hier].[level3],9))

Will create a set of the fourth, seventh and tenth members of the referenced set or level.

The set I am trying to create is dynamic. Also, I need the *last* element and elements offset from that last element... so my 'lag' statements should work. I don't think I can use your 'item' statements... can I? I'll try this out at work tomorrow.

Note when analyzing any Cognos generated MDX: When using a level as a set, Cognos will wrap the level in the members() command.  Thus item([cube].[dim].[hier].[level3],3) will really look something like item(members([cube].[dim].[hier].[level3]),3)

Noted!

I have a couple of related questions:

• When would you use 'set expression' and 'calculated member' instead of 'data item'? Similarly, when working with measures, when am I to use 'calculated measure' instead of 'data item'? I read through the RS User Guide, but got more confused.
• I have two data items (both of them dynamic single items as discussed above) 'A' and 'B' in a crosstab from alternate hierarchies of a single dimension. When I add another data item C = B - A, it does not allow me to do that, giving me a QE-DEF-0480 'cannot be converted to consistent dimensional object' error. How can I work around this?

Thanks again Paul; you're awesome. :)

[CognosPaul]

I must say I am enjoying working with dimensional data a lot more.

Be careful! Once you get used to dealing with dimensional datasources, relational sources will be almost impossible to work with.  ;D

The set I am trying to create is dynamic. Also, I need the *last* element and elements offset from that last element... so my 'lag' statements should work. I don't think I can use your 'item' statements... can I? I'll try this out at work tomorrow.

Ah, I get it. You need to work with the last member and the members relative to that. The way you suggested should work, but I'd put the item() in item1 instead of in each lag. You can then do something like:
set(lag(),lag(),lag(),lag(),[item1])

I have a couple of related questions:

• When would you use 'set expression' and 'calculated member' instead of 'data item'? Similarly, when working with measures, when am I to use 'calculated measure' instead of 'data item'? I read through the RS User Guide, but got more confused.

I never use anything except data items. When you use set expressions you need to specify the referenced hierarchy. There's no way to change that, and if you need to switch it to something else you'll need to delete that item and create a new one from scratch.  Theoretically the different objects give optimization hints to Cognos when generating the MDX, but I haven't seen any proof of that when analyzing the performance.

• I have two data items (both of them dynamic single items as discussed above) 'A' and 'B' in a crosstab from alternate hierarchies of a single dimension. When I add another data item C = B - A, it does not allow me to do that, giving me a QE-DEF-0480 'cannot be converted to consistent dimensional object' error. How can I work around this?

You're using power cubes? There are a few limitations when dealing with alternate hierarchies with powercubes. I'm not sure you can compare members.

Thanks again Paul; you're awesome. :)

It is a pleasure.

[cogswell]

Ahh, MDX... one day I *will* tame you.

I am trying to create a crosstab on the same Transformer cube.

• Default Measure is a percentage
• On the columns, I have two members from an alternate hierarchy in my Time Dimension. The members are 'Quarter ending 2011-03', 'Quarter ending 2010-12'. In the hierarchy, they are not leaf level members (their children are the constituent months), and they aren't adjacent members either. The hierarchy actually contains *rolling* 3-month periods, i.e., in between the two abovementioned members, I have 'Quarter ending 2011-02', 'Quarter ending 2011-01' as well (any leaf-level member is likely to find itself under 3 'Quarter ending' members). But I am NOT including these in the crosstab.
• On the rows, I have buckets 'Low' and 'High' based on a user-inputted threshold value (text box prompt). If the user enters 90, default measure above 90% will go to the 'High' bucket.

I get the following error while running the crosstab:
DMB-ECB-0093 In a Parent-Child hierarchy, a category can have only one parent. Category = '<%somethingfrom8yearsago%>'

Google's never heard of that error. What gives? Is there an issue with my cube?

[CognosPaul]

I've honestly never seen that error before. My guess is that is has something to do with the alternate hierarchy. But I'm afraid I really have no idea.

[cogswell]

 :-\ Moving on...

I want to create a crosstab where the default measure is a ratio of two measures in the cube (and expressed as a percentage). The 'ratio' is a measure itself.

On the rows, I have some time buckets. Not just any buckets, but special, dynamic time buckets.
Bucket 1 (Col A): 3 months ending March 2011.
Bucket 2 (Col B): 12 months ending November 2010.
Col C: A - B (simple arithmetic difference of ratios)

I have relative time categories in the cube which supposedly make life easy. Both of the above are available as RTCs. But alas! I can't compare them (Col C) because of the problem mentioned above.

So I said aloud, 'screw the RTCs, I'll work with the time dimension directly.' Which brings me to my question: How do I create those buckets? I don't want to create a set, because then the crosstab would show the constituent elements of the set. I want the set to be... implicit, if you know what I mean. I want the rows to show 'Bucket 1' and not 'Jan 2011', 'Feb 2011'...

Also, since the measure is a ratio of two other measures, will it be shown correctly, i.e.,
if Measure A = {3, 5, 7} and Measure B = {6, 15, 28}, then will the ratio for bucket 1 be calculated as (3+5+7)/(6+15+28)?

Or will I have to create an explicit ratio measure with the constituent measures?

I still love MDX, I really do.  ;D

[CognosPaul]

So instead of a set of {Jan/2011, Feb/2011, Mar/2011} you want to display a single value of Bucket1. This is a job for the member function!

Let's create the set expression first:
3Months: set(Jan/2011, Feb/2011, Mar/2011)

Now to aggregate them:
3MonthsTotal: total(currentMeasure within set [3Months])

And finally to turn it into a calculated member:
3MonthsMember: member([3MonthsTotal],'uniqueKey','Caption',[Cube].[Time].[Time])

You can now use [3MonthsMember] as a normal member in the [Time] hierarchy. Create the 12MonthMember with the same logic.

Because it's totaling currentMeasure it will take the total of whatever measure is under the member. Instead of Total you can also use any of the Member Summaries. Special note about the Aggregate function. It should take the aggregate function from the cube, so if you have one measure that is summed, and another that is averaged, then it will apply the correct aggregation to each measure.

So now you can do [3MonthsMember] / [12MonthsMember]. But the important thing to remember about that is you can create a custom member on top of that expression too! (It's getting silly, you can get away with tuple([Measure],[3MonthsMember)/tuple([Measure],[12MonthsMember]) and setting that as your default measure.

[cogswell]

Making some headway; soldiering on. Thank you, PaulM. :) Consider the following crosstab:

City	Level	C = A - B	Col A	Col B
London	--	0.15	0.51	0.36
Munich	--	/0	/0	0.51
Brussels	--	/0	0.16	/0
Phoenix	--	-0.05	0.72	0.77

My problems are the following:
a) How do I consider the /0 values as zeroes? I tried writing IF-THEN-ELSE statements but they don't work! I tried the following:

• IF ([COL A] IS NULL) THEN (-1*[COL B]) ELSE ([COL A] - [COL B]) ... validates but still produces the above resultset
• IF ([COL A] = '') THEN (-1*[COL B]) ELSE ([COL A] - [COL B]) ... QE-DEF-0459 invalid to compare values with different datatypes
• IF ([COL A] = NULL) THEN (-1*[COL B]) ELSE ([COL A] - [COL B]) ... validates but still produces the above resultset

b) The Level column is also fed by a simple conditional logic statement outputting 'Low' or 'High' dependent on the values in the 'C = A - B' column. It doesn't work at all, not even for valid values in that column! The statement is: IF ([COL C] > ?LevelPrompt?) THEN ('High') ELSE ('Low'). For some reason, I get '--'. I even tried moving the ?LevelPrompt? to a data item 'AlarmLevel' and then using IF ([COL C] > [AlarmLevel]) THEN ('High') ELSE ('Low'), but to no avail. I tried associating the 'Level' crosstab nodemember to [AlarmLevel] & [Difference] data items (together) but then the report wouldn't run (OP-ERR-0115; dimensions on the edge are inconsistent). If I associate it to just [AlarmLevel], the report runs but still gives '--'.

???

[cogswell]

I was able to do a bit more testing. If I use the following logic for the 'Level' column in the crosstab:

Code Select Expand

IF ([COL C] < ?LevelPrompt?) THEN (1) ELSE (0)
It works well; the 'Level' column gets populated with 1s and 0s. But I see the damned error characters (--) again if I use

Code Select Expand

IF ([COL C] < ?LevelPrompt?) THEN ('High') ELSE ('Low')

HELP! ???

[cogswell]

Paul, where art thou??

So instead of a set of {Jan/2011, Feb/2011, Mar/2011} you want to display a single value of Bucket1. This is a job for the member function!

Let's create the set expression first:
3Months: set(Jan/2011, Feb/2011, Mar/2011)

Now to aggregate them:
3MonthsTotal: total(currentMeasure within set [3Months])

And finally to turn it into a calculated member:
3MonthsMember: member([3MonthsTotal],'uniqueKey','Caption',[Cube].[Time].[Time])

Does the membercaption of the calculated member ('Caption') have to be a static string? Can it be the member caption of the last member of the set? I tried using

Code Select Expand

member([Total_Last3Months], 'T_L3M', rolevalue('_memberCaption', item(tail([Last3Months]), 0)), [Cube].[Time].[Time])
But I got the following error:

OP-ERR-0025
The following OLAPPlanner internal error occurred: 'OPASSERT(OQP_DYNAMIC_CAST(OPExpressionItemPtr,GetOpChildExpr(exprQE,2))) in OPQueryItemGenerator::CreateOpMember at OLAPPlannerQueryItemGenerator.cpp:2840'.

Finally, what is 'uniqueKey'? Is it the MemberUniqueName?

[CognosPaul]

Hey Cogswell,

Been a bit busy over the last week, but no worries.

The caption value needs to be a string - I'm pretty sure using an existing caption won't work. You can use macro functions for it. You could also use report functions to make it look exactly like you need.

In your question with the prompt Cognos is attempting to aggregate the value. So 1s and 0s will work, but as 'High' and 'Low' aren't numeric values, it will return an error. What you can do is click on the intersection, set Define Contents to yes, drag in a Layout Calculation:

Code Select Expand

case when [Data Item] = 1 then 'High' else 'Low' end
Since the report expression is triggered after all of the aggregations have been completed it will work.

And finally, do you want to display the /0s as 0? You can set the data format in the crosstab to show div0s as 0 (or any other text you want).

[awilbourn]

Let's begin with the terminology.

A tuple is the intersection of any measure with one or more members. In SQL you would have to write:

Code Select Expand

select sum(revenue) revenue from sales where year=2010 and country='Hersheba'
in MDX you could write

Code Select Expand

select tuple(revenue,2010,Hersheba) on rows

A slicer will essentially create a tuple on every measure. Suppose you have a list of years and sales. Every row can be considered a tuple: (2008,Sales) (2009,Sales) (2010,Sales). By placing a slicer, Camping Equipment for instance, every row could be thought of as: (2008,Sales,Camping Equipment) (2009,Sales,Camping Equipment) (2010,Sales,Camping Equipment). You could also put a specific year in the slicer: (2008,Sales,2010) (2009,Sales,2010) (2010,Sales,2010). In that scenario every row would be rendered, but only the 2010 row would have data.

The point to realize is that slicers will only effect measures.

A set is a logical grouping of members from the same hierarchy. {2009, 2008/Apr, 2010, 26/02/2014} is a legal set whereas {2009, Canada} is not legal since it pulls members from two separate hierarchies.

It sounds what you need is the descendants of a set of members {Camping Equipment,Mountaineering Equipment}.

I recommend reading the Cognos paper on Cognos 8 Dimension Functions found here.

When talking about dimensional reporting the word filter can be used to describe two separate things. There is a filter function that will filter a set based on Boolean conditions: filter(products,sales>1000) will return a set of all products where the sales were greater than 1000. Filter can also relate to Detail Filters that you see in the query.

Personally I feel that under no circumstances should detail filters ever be used when working with a cube. Detail filters on sets will be processed after the query is run. So if you have a list of years and sales, Cognos will pull the entire set of years and place the filter after it has been processed in the cube. The impact on performance is huge, and the results are unreliable. A filter on a measure will be processed as part of the MDX, but on every set. This can also cause unpredictable results.

When I supervise other authors, or work with clients on site, I will reject any dimensional report that uses a detail filter.

Love the post. I have used detail filtering for PowerCubes as I could not figure out how to pass a parameter to the slicer. Can you share an example of how that may be accomplished? Let's say you are wanting to look at sales for a particular salesman, or for a certain customer how many different sales people sold to that customer?

Thanks.

[CognosPaul]

I have used detail filtering for PowerCubes as I could not figure out how to pass a parameter to the slicer. Can you share an example of how that may be accomplished? Let's say you are wanting to look at sales for a particular salesman, or for a certain customer how many different sales people sold to that customer?

Hey, I was preachy back then. Some things never change I guess.

The slicers, rows, and columns are all edges of a specific query. The rows and columns are the edges that you're showing to the customer, while the slicer is simply filtering the measures on the visible edge. Every query sent to the cube is always being sliced by every dimension. If a specific dimension isn't referenced in any of the edges (slider, row, or column) then it will automatically be sliced by the default member (which is usually the root member). 

Now having said that, you can use the same methodology to filter members on any of the three edges. The first question sounds like you just want the measure for a specific salesman. If you were writing this in MDX you might write the following:

Code Select Expand

select measures.sales on 0, timedim.timehier.years.members on 1 from cube where staff.corphierarchy.sales->salesman1 (that's really simplified MDX for the sake of this example.)

In that select you're showing sales by years for salesman1.

There are two ways to create the where statement. There's the way that the IBM training suggests which is something like

Code Select Expand

[cube].[dim].[hier].[level]:->?Parameter? Personally I'm not a big fan of that way. The question marks there are making what's called a prompt alias. Cognos will automatically determine what you want filtered and how you want to filter it. A better, but more complicated way, is to use a prompt macro.

Code Select Expand

#prompt('Parameter','mun','[cube].[dim].[hier].[All Level]:->[AllMember]')#

That will accept a member from Parameter. If it doesn't receive a member, it will default to slicing by the all member. I've written other posts here explaining the various things you can do with prompt macros.

The second question is a little more difficult. You can start by using the filter function.

To begin, your crosstab should look something like this:

Corner   | Sales
---------+--------
Customer | 1234

In your query, create a new data item with the following expression:

Code Select Expand

filter([Salesmen],[Sales]>0)

If you were to put that in the place of customer node, it would show every salesmen that ever had any sales. If you put it in the context of the customer, such as nesting it inside it will generate a set of salesmen that ever had sales with that specific customer. Once that works it is then trivial to count the members. Try something like

Code Select Expand

count([Sales] within set filter([Salesmen],[Sales]>0))

That will now act as a value, and you can use it like you might use any other measure. So move the node to the columns and see what happens.