In this session we'll show you how to create a Reporting Action using Analysis Services 2012 Cube Designer. We'll explain what a Reporting Action is, how to create one in SSAS 2012 and finally demonstrate how to render a SQL Server Reporting Services report by executing the Reporting Action from Excel.
This article assumes you have completed the following:
- Installed SQL Server 2012 or 2008 R2, with Analysis Services.
- Installed and configured SQL Server Reporting Services (SSRS) for use.
- Attached AdventureWorksDW2012 Data File.
- Experience with SQL Server Data Tools (SSDT) or SQL Server Management Studio (SSMS). Note: For this session we'll be using SSDT.
- Experience with deploying and attaching to an exisitng Analysis Services database using SSDT or Business Intelligence Development Studio (BIDS).
- Some experience developing with Multidimensional Expressions (MDX) language.
- Experience with designing and deploying SQL Server Reporting Services reports.
- You have download and deployed the AS-ReportDemo.zip sample report project to your local client.
- You have download the Analysis Services Tutorial.zip sample SSAS project.
Note: A download(s) is available at the end of this session.
What is a Reporting Action?
A Reporting Action is used to render a SQL Server Reporting Services (SSRS) report. You define a Reporting Action from the Actions view (tab) in SQL Server Analysis Services Cube Designer. While defining your Reporting Action, you may add parameters to overwrite the default values of the report at runtime.
Note: You can create an action only if the cube is in a processed state.
Creating a Reporting Action
1) Open the Analysis Services Tutorial project.
2) From within the Solution Explorer, expand the Cubes folder and double click on Analysis Services Tutorial.cube.
3) Select the Actions tab.
4) From within the Action Organizer pane, right click and select New Reporting Action.
5) From within the Expression Pane, complete the following:
- Name (name of reporting action) = Sales Reason Comparison
- Action Target (defines the scope of the action)
- Target Type = Attribute Members
- Target Object = Product.Category
- Condition (an MDX expression that limits the scope of the action) = (leave blank)
- Report Server (the name of your report server, report path and format)
- Server Name = localhost
- Report Path = ReportServer?/AS-ReportDemo/Sales Reason Comparisons
- Report Format = HTML5
- Parameters (parameter names and values to pass to the report)
- Parameter Name = ProductCategory
- Parameter Value = UrlEscapeFragment( [Product].[Category].CurrentMember.UniqueName )
- Additional Properties
- Invocation = Interactive
- Application = (leave blank)
- Description = (enter a description for your reporting action)
- Caption = "Sales Reason Comparisons for " + [Product].[Category].CurrentMember.Member_Caption + "..."
- Caption is MDX = True
Your Expression Pane should look similar to below:
"Before you complete the next steps, remember to deploy the sample AS-ReportDemo project to your local Report Server"
6) From the menubar, select Build >> Process (select Yes when prompted). If you receieve another prompt regarding 'database will be overwritten', click Yes.
7) Click Run... when the Process Cube window shows. After the processing completes, close the window and select the Browser tab from the Cube Designer.
Note: You may need to click the "Reconnect" link at the bottom of the Browser pane. If you don't see it, move on to the next step.
8) On the toolbar, click the Excel icon.
Note: This will open Excel and connect to this instance of the cube.
9) From the PivotTable Fields pane, scroll to the Reseller Sales measures group and check Reseller Sales-Sales Amount item.
10) Scroll to the Product dimension, expand ...More Fields and check Category item.
You should see something similar to below:
11) Place your cursor on the Bikes row item and right click.
12) Go to the Addtional Actions menu item and select Sales Reason Comparisons for Bikes...
13) Your SQL Server Reporting Services report should render. Optionally, you can continue to change the parameter values once viewing the report.
Note: Notice the parameter value is set to the Category item you selected from Excel.
Analysis Services Tutorial.zip