I have searched many hours attempting to find an end-to-end solution to this problem.
I want to create a report action in an SSAS OLAP cube that generates a list of formatted values to pass into an SSRS report parameter that accepts a multi-valued parameter list. This would be applied at the cell level in the SSAS action. I have found a solution that gets me most of the way: How to Pass Multiple Values from an SSAS Report Drill Through Action to an SSRS Multi-Value Parameter, but not quite. The action does appear in Excel and works if I run the action from a cell that is at or below the dimension attribute I am generating the list for, in this case, Account Key.
Below is a link to a screen capture (unable to embed it due to lack of reputation) showing the action and dimension structure in Excel. The action works as long as I run it at the Account Key level or below. I want to be able to run it at higher levels, such as Account Major and still have it generate all then related Account Key values for the SSRS report parameter. Running it at the higher Account Major level does not trigger the report to run.
Excel Action Screen Shot: http://i.stack.imgur.com/QCGSp.png
Below is the MDX I am using to generate the value for the report parameter:
UrlEscapeFragment(
GENERATE(
DESCENDANTS(
[Account].[Account Key].CurrentMember,
[Account].[Account Key].[Account Key]
),
[Account].[Account Key].CURRENTMEMBER.Name,
"&rp:Account="
)
)
I am hoping that I can somehow modify the MDX above to make it return all the Account Keys for any attribute of the Account dimension when ran from any measure cell, not just when ran at self and children of Account Key in the pivot table.
Also, if it helps, I can execute the following MDX query on the cube and get the results I am looking for.
WITH MEMBER [Measures].[Account Key List] as
GENERATE(
DESCENDANTS([Account].[Account].CurrentMember, [Account].[Account].[Account]),
[Account].[Account].CURRENTMEMBER.NAME,
"&rp:Account=")
SELECT {[Measures].[Account Key List]} on 0,
([Account].[Account Company Number].[Account Company Number],[Account].[Account Major].[Account Major]
) on 1
FROM [Company 10 Action Demo]
Below are partial results:
10.116&rp:Account=10.116.010
10.117&rp:Account=10.117.010&rp:Account=10.117.020
10.120&rp:Account=10.120.005&rp:Account=10.120.006&rp:Account=10.120.010&rp:Account=10.120.020&rp:Account=10.120.030&rp:Account=10.120.040&rp:Account=10.120.050&rp:Account=10.120.060&rp:Account=10.120.380&rp:Account=10.120.999
10.123
- Any ideas what I might need to do to get Account Key to be returned for any attribute of the Account dimension?
- Would I possibly have to alter my Account dimension in the cube to get this to work?
Thanks in advance.
Edit 1 - Adventure Works Cube Version
I was unable to get the suggested answer with the "Exists" function to work. To better demonstrate this issue, I have recreated it using the Adventure Works Cube.
I will focus on the Customer dimension, specifically the Customer and Education attributes. I created a report action called Test Report Action. Below is the XML created for it in the cube.
<Action xsi:type="ReportAction" dwd:design-time-name="f35ad5ee-5167-4fb8-a0e0-0a74cc6e81c6">
<ID>Report Action 1</ID>
<Name>Test Report Action</Name>
<TargetType>Cells</TargetType>
<Target></Target>
<Type>Report</Type>
<ReportServer>SQLSERVER</ReportServer>
<Path>ReportServer?/Test Report</Path>
<ReportParameters>
<ReportParameter>
<Name>Test Customer Existing</Name>
<Value>UrlEscapeFragment(
GENERATE(
EXISTING DESCENDANTS(
[Customer].[Customer].CurrentMember,
[Customer].[Customer].[Customer]
),
[Customer].[Customer].CURRENTMEMBER.Name,
"&rp:Customer="
)
)</Value>
</ReportParameter>
</ReportParameters>
<ReportFormatParameters>
<ReportFormatParameter>
<Name>rs:Command</Name>
<Value>Render</Value>
</ReportFormatParameter>
<ReportFormatParameter>
<Name>rs:Renderer</Name>
<Value>HTML5</Value>
</ReportFormatParameter>
</ReportFormatParameters>
</Action>
Below are the steps to re-create the issue.
- Connect to the cube in Excel
- Add dimension Customer -> More Fields -> Customer
- Add measure Internet Sales -> Internet Sales Amount
- Right-click Internet Sales Amount cell, select "Additional Actions" -> "Test Report Action" and see customer values created for URL
When the action is ran at this point with Customer, I see the values created in the URL shown message box (since there is no SSRS report server at location specified).
Now the part I'm unable to resolve
- Remove the Customer dimension and add Customer -> Demographic -> Education
- Right-click Internet Sales Amount cell, select "Additional Actions" -> "Test Report Action"
Nothing happens. If I ran the action on the cell next to "Bachelors", I would want it to build up all the list of all the "Customers" that make up the "Bachelors" in the Customer dimension as part of the report parameter. If no attributes where used in the cube from the Customer dimension for that cell, then I would like it to return "All Customers", or something similar to show that all customers are included in the aggregations.
I am not too MDX savvy, thus far. I think I need to somehow join the Customers to Internet Sales Amount in the Generate function portion. I have tried several different combinations of the Customer dimension and Internet Sales Amount, along with various functions to see if I could get this to work with no success. I am hoping that someone more knowledgeable the me will have a solution. If you need more details, please ask and I will provide them.
