none
SSAS Report Action with multiple parameters RRS feed

  • Question

  • Hi,

    I wanted to test the reporting action in one of our test cubes however I am having trouble passing multiple parameters into the report.

    The SSRS report is very basic, links to the database and has one parameter (Customer) which accepts the name of 'Customer No' from the customer dimension of the cube. It is set-up to accept multiple values.

    With the following MDX I am able to get the report to run for one customer at a time. Example:

    UrlEscapeFragment( [Customer].[Customer no].CurrentMember.Name )

    When I click the reporting action in the cube, it generates the following URL.

    http://gdhacorbidb/ReportServer/Pages/ReportViewer.aspx?/IT%20Reports/Inovices%20Test&Customer=0623&rs:Command=Render&rs:Renderer=HTML5

    If I manually alter this URL to include the second customer number from the screen grab it loads fine.

    http://gdhacorbidb/ReportServer/Pages/ReportViewer.aspx?/IT%20Reports/Inovices%20Test&Customer=0623&Customer=0658&rs:Command=Render&rs:Renderer=HTML5

    Using the article below on the issue I have created this MDX which should return the currently selected customers and pass them into the URL as generated above.

    https://social.technet.microsoft.com/Forums/windows/en-US/293459cf-bf34-4843-b52d-d83084968792/ssas-report-action-to-pass-multivalue-list-of-dimention-key-values-to-a-ssrs-report-parameter?forum=sqlanalysisservices

    WITH MEMBER [Measures].[Order Param List] AS
      
     UrlEscapeFragment (  GENERATE(
            EXISTS( [Customer].[Customer no].[Customer no].members , ,
                "Sales"),
        [Customer].[Customer no].CurrentMember.Name,
        "&Customer=") )
        
    
    SELECT {[Measures].[Order Param List], [Measures].[Invoiced Value]} ON 0
        ,([Time].[Fiscal Month].[Fiscal Month]) ON 1
    FROM 
     [Corbi_Cube]

    Example results 

    N40926%26Customer%3dN40975%26Customer%3dN42261

    ABB002%26Customer%3dADD001%26Customer%3dALT002

    However when I paste the code below into my parameter in the cube the option to run the report is available and runs fine for a single customer, for multiple customers when I click on a sub total or grand total cell it does not pass in the parameters correctly. 

     UrlEscapeFragment (  GENERATE(
            EXISTS( [Customer].[Customer no].[Customer no].members , ,
                "Sales"),
        [Customer].[Customer no].CurrentMember.Name,
        "&Customer=") )

    The URL loads as below

    http://gdhacorbidb/ReportServer/Pages/ReportViewer.aspx?/IT%20Reports/Inovices%20Test&Customer=All&rs:Command=Render&rs:Renderer=HTML5

    I have fiddled with various combinations of this MDX and various ways to pass in the parameters with no success.

    Any help with this would be greatly appreciated.

    Thanks,

    Richard

     



    • Edited by richy999 Monday, March 14, 2016 3:33 PM
    Monday, March 14, 2016 3:10 PM