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?
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.
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!
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.
actually i guess u can hide the product line field... just set the border to none...
Quote from: Jiayue.lin on 11 Jun 2013 04:09:17 AM
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!
just delete product line, you are not bound to it, your calculation doesn't have anything to do with product line
By Creating Headers & Footers U can make....
Go through the attachment.. and see in your Cognosie inbox.
Quote from: Satheesh on 12 Jun 2013 01:42:16 AM
By Creating Headers & Footers U can make....
Go through the attachment.. and see in your Cognosie inbox.
Is this from a dimensional package?
MF.
That is from Relational Pkg.
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.
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.