Conditional grouping in a dimensional report

[snowy]

Calling all dimensional gurus! I've got a list report (Product Line and Quantity from the Great Outdoors Sales Cube), with a simple conditional data item that groups Product Lines into High or Low value, like this:

if( [Quantity sold] > 20000000 ) then( 'High' ) else( 'Low' )

So currently the results are:

Product line	Quantity sold	High/Low
Personal Accessories	34,907,705	High
Camping Equipment	27,301,149	High
Outdoor Protection	12,014,445	Low
Mountaineering Equipment	9,900,091	Low
Golf Equipment	5,113,701	Low

However, my users want to show the High/Low grouping without the Product Lines, like this:

High/Low	Quantity sold
High	62,208,854
Low	27,028,237

..but when I remove the Product Lines item, the list forgets all about them and I get just one row - i.e. all "High". Somehow I need to get the conditional function to loop through the Product Lines and work them out one by one, and only then aggregate the result. But I'm afraid it's got me stumped.

Can anyone suggest a way of doing it?

[MFGF]

Does it have to be a list for any specific reason?

If you use a crosstab, you could do this in the following way...

Drag Quantity Sold in to the Columns area
Drag a Query Calculation in to the rows area. Call it High, leave it as a calculated member, and select the Products Hierarchy. For the expression, use:

aggregate(currentMeasure within set filter([Your Product line level], [Your Quantity sold measure] > 20000000))

Add a second query calculation below High, use the same approach but call it Low, and use the expression:

aggregate(currentMeasure within set filter([Your Product line level], [Your Quantity sold measure] <= 20000000))

I wouldn't be at all surprised if Paul offers a far simpler and more elegant solution :)

Cheers!

MF.

[snowy]

Fantastic. There's no reason why it can't be a crosstab, so that works perfectly.

I've got to stop thinking so damned relationally!

Thanks for your help!

[CognosPaul]

That is exactly how I would do it.  And the best part is that since this is a calculated member, you can use that as a product line grouping for other calculations.

For example: tuple([Revenue],[High]) would return the revenue for all product lines that are in the High group.

[Jiayue.lin]

actually i guess u can hide the product line field... just set the border to none...

[snowy]

actually i guess u can hide the product line field... just set the border to none...

Yeah, that occurred to me, the problem is that you'd still get five rows (one for each product line) - but I only want two.

Dimensional functions are definitely the way forward!

[adik]

just delete product line, you are not bound to it, your calculation doesn't have anything to do with product line

[Satheesh]

By Creating Headers & Footers U can make....


Go through the attachment.. and see in your Cognosie inbox.

[MFGF]

By Creating Headers & Footers U can make....


Go through the attachment.. and see in your Cognosie inbox.

Is this from a dimensional package?

MF.

[Satheesh]

That is from Relational Pkg.

[CognosPaul]

This is the difference between doing it in relational or dimensional. In a relational, the context is always on the detail row level - so doing a case switch like this would work perfectly. However in a dimensional package, the context is always going to be the defaultMember of the hierarchy (usually the ALL member) if not specified. This means that doing a case switch would always result in a single line, since it would always be working against a single value. Think of dimensional as always doing actions after aggregation. Furthermore, it is actually impossible to do a case switch like this in a true OLAP environment as the expression would return neither a value, a member, nor a set.

The correct way to do this would be to create calculated members based on the Product Line set. First by filtering out the members that don't match, then aggregating them into a single value/member.

Code Select Expand

set(
member(aggregate(currentMeasure within set filter([Your Product line level], [Your Quantity sold measure] > 20000000)),'High','High',[Product hierarchy]),
member(aggregate(currentMeasure within set filter([Your Product line level], [Your Quantity sold measure] <= 20000000)),'Low','Low',[Product hierarchy])
)


The previous expression creates two calculated members, places them in the Product hierarchy, and generates a set on them. By adding that into a crosstab (list would also work but are generally inappropriate for a dimensional model), you would the expected results.