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.



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 :

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


Tag Cloud