TechBubbles Microsoft Technology BLOG

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.

 

image

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

image\

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

image

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

image

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.

image

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.

image 

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

image

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

image

Share this post :

About the author

Kalyan Bandarupalli

My name is kalyan, I am a software architect and builds the applications using Microsoft .NET technologies. Here I am trying to share what I feel and what I think with whoever comes along wandering to Internet home of mine.I hope that this page and its contents will speak for me and that is the reason I am not going to say anything specially about my self here.

1 Comment

  • Is there anyway we can “encapsulate” the MDX query in a MDX object similar to the SQL view/stored prod? That way if the data need a bit tweaking etc, we only need to make changes to the object and not the .rdl.

TechBubbles Microsoft Technology BLOG

Follow me

Archives

Tag Cloud