| Author |
 Topic  |
|
|
dbooth
United Kingdom
69 Posts |
 Posted - 07/11/2012 : 06:25:34
|
Please see report 'Product Code LL' in My Reports on demo site. This shows revenue in a filtered period by Customer and Product, in addition there is a Filter to select Products beginning 'LL'.
The situation I want to model is this but with the Product absent, or a drill-down from the (in this case) Customer attribute.
On removing Product from the Rows list, the data panel becomes empty. I was hoping to see a list of Customers with their Internet Sales Amount totals, for Products beginning 'LL'.
The functionality can be emulated in SSAS by specifying the Product selection in the Subcube area, how can I implement subcube-type behaviour in RP?
Thanks,
Dave Booth |
|
|
admin
1640 Posts |
Posted - 07/11/2012 : 21:06:56
|
You would need to create a calculated member on the Product attribute hierarchy with the following MDX:
Aggregate(Filter([Product].[Product].[Product].Members,
left([Product].[Product].CurrentMember.Name,2)="LL"))
Please see report 'Product Code LL 2' in My Reports on demo site. Here is the full MDX used by the report:
with member [Product].[Product].[All Products].[LL Products] as 'Aggregate(Filter([Product].[Product].[Product].Members,
left([Product].[Product].CurrentMember.Name,2)="LL"))'
Select non empty Crossjoin(HIERARCHIZE(DISTINCT({AddCalculatedMembers({
{[Delivery Date].[Fiscal].DefaultMember}})})),{
[Measures].[Internet Sales Amount]}) on columns,
non empty HIERARCHIZE(DISTINCT({AddCalculatedMembers({
[Customer].[Customer Geography].[Customer].AllMembers})})) on rows
From [Adventure Works]
Where (/*Current,_Fiscal Year*/Tail(NonEmptyCrossJoin([Delivery Date].[Fiscal Year].[Fiscal Year].Members,{[Measures].[Internet Sales Amount]}),1).Item(0).Item(0), [Product].[Product].[LL Products]) |
 |
|
| |
Topic |
|
|
|
Report Portal
Filter on attribute not in row or column
