none
How Do I Send SSAS Reporting Action Parameters to a SQL-based SSRS Report? RRS feed

  • Question

  •  

    We have SSRS set up with SharePoint Integration and are migrating current applications from AS 2000 to SSAS 2008 R2.  Either from a SharePoint 2010 PerformancePoint object or from an Excel 2010 Pivot Table the datasource of which is SSAS 2008 R2, we would like to be able to right click on a cell and pass parameters based on the currently selected dimensions to a SQL-datasourced SSRS report to get further details that are not contained in the cube.  Here is one way I have tried to make this work but am having no success.

    - I added a new report, Show_Details_3, as a table object with a SQL data source and an embedded dataset with one parameter, @COSTCENTER, in the Where clause.

    - In the Report Designer, the report previews fine when I enter the one parameter, COSTCENTER, to view the preview.

    - I save and deploy the report to the SharePoint server.

    - I open Internet Explorer and go to SharePoint 2010 to Libraries/Report Services/Reports and find the new report.

    - I click on Show_Details_3.

    - I copy the URL from the top of Internet Explorer.

    - The IE screen shows me the message, "Specify Parameter Values Report parameter values must be specified before the report can be displayed. Choose parameter values in the parameters area and click the Apply button". I enter the COSTCENTER and the report runs and returns results correctly.

    - I go back to BIDS to the cube and set up a new Reporting Action, Show_Details_3 with Target type Cells and Target object All cells.

    - I enter the SharePoint server / report server, dhscswebdev, in the Report Server/Server name.

    - I enter the URL I copied above into the Report path,

    http://dhscswebdev/_layouts/ReportServer/RSViewerPage.aspx?rv:RelativeReportUrl=/Report%20Services/Reports/Show_Details_3.rdl&Source=http%3A%2F%2Fdhscswebdev%2FReport%2520Services%2FForms%2FAllItems%2Easpx%3FRootFolder%3D%252FReport%2520Services%252FReports%26FolderCTID%3D0x0120007C2586FBB37CA141A38FCE4AAE068306%26View%3D%7B22B5E1BF%2DEB68%2D4722%2DB23A%2D4E818B13A940%7D&DefaultItemOpen=1

    - From the Report path, I delete http://dhscswebdev/ and just leave _layouts/ReportServer and beyond.

    - I leave the Report format as HTML5.

    - I enter the single Parameter Name and Parameter Value, COSTCENTER and [Costcenter Mgmt ML2].[Costcenter Mgmt].currentmember.Properties("key").  (I also tried [Costcenter Mgmt ML2].[Costcenter Mgmt].currentmember.name.)

    - I deploy and process the cube.

    - From an Excel 2010 pivot table using the cube as a data source, I Refresh All, right click on a cell, select Additional Actions / Show_Details_3 and launch the report.  I get the same message regarding the parameter as above.  I enter the COSTCENTER and the report runs and returns results.

    - I go back to the report and double-click on the @COSTCENTER parameter, change the parameter visibility to Hidden, save all, and deploy the report.

    - I go back to the Excel 2010 pivot table, refresh all, right click on a cell, select Additional Actions / Show_Details_3 and launch the report.

    - I get the error: "Error

    The report is missing a parameter value but prompting for it has been disabled. "

    What am I doing wrong?  How does the action feed the parameter to the report?

    Monday, February 27, 2012 7:30 PM

All replies