SQL Server Reporting with Analysis Services

 

 

SQL Server Analysis Services is used to present the aggregated data to support analysis, ad-hoc reporting and decision making process. Technically CUBE is used in Analysis Services to build the aggregated data. Once it is build then you can easily navigate and produce reports for business analysis. This post discusses using analysis services in SQL Server Reporting with MDX query builder.

To download and configure the AW analysis services database read my previous post.

Create a Data Source

1. Create a data source for the AW20082 Analysis Services database in Report Builder 3.0 and select the SQL Server Analysis Services data provider from the drop down list.

 

2. Click the build button to add the Analysis services server and database name as shown below

\

3. Build the Data-Set query by KPI defined in the Adventure Works CUBE. This report is to show the current value, goal and status of the revenue by product category. To write Data-Set query add a table to report body which displays the following dialogue box

4. Select the data source from drop down list and click the query designer button to construct the query

Expand the KPI node from CUBE Metadata explorer from above dialogue, Drag the Channel Revenue Value,Goal and Status members in to data pane.

5. After dragging the KPI members to the data pane, expand the product dimension and drag the product categories hierarchy to data pane.

6. To filter the results, Drag the Calendar Year attribute hierarchy into the filter pane. Use the filter expression drop down to set the default filter members.

 

7. Drag the data fields to the report body in designer as shown below

8. Here I have set up the gauge for the status field and assign the properties which I discussed in my previous post

You can set the following value for RadialPointer property as =Sum(Fields!ChannelStatus.value) to get different colors in gauge.

9. Run the report and you will see the results as shown below

Share this post :