Reviewing Jayaram’s other OLAP related articles may greatly help in understanding this article.
Creating the CUBE will be essential to work with this hands-on. If you are already experienced and have an appropriate CUBE to work with you may directly go to the section on deriving a dataset. The CUBE used in this hands-on is a simple one. It does not even have a “Time” dimension although Microsoft in Visual Studio gives a warning that you should have one. However for the purposes of demonstration and doing the hands-on a CUBE without the “Time” dimension should be adequate. The two previous articles on creating a CUBE provide the necessary background for this article.
Create a data source with a CUBE
Create an Analysis Services Cube (herein called MyNwind.cube) using the TestNorthwind as the database. The Measures and Dimensions for this cube are as shown in the next figure.
Start your Report Builder 2.0. Make sure you have started the reporting services using the Reporting Services Configuration Manager. In Report Builder 2.0 click on New | Data source… under Report Data. Provide the following information:
Name: SrcCube
Select connection type: Microsoft SQL Server Analysis Services
Connection String: Data Source=Hodentek2SANGAM; Initial Catalog=NwindRTM
Herein NwindRTM is the name of the Datasource in the Analysis Server. (Alternately you may build this source as shown).
Click OK on the Data Source Properties window.
Add a dataset to the report
Right click on SrcCube and choose Add DataSet… In the Dataset Properties window make the following changes:
Name: QryCube
Data source: SrcCube
Query Type: text
Click on Query Designer… button. At first you will see only the Metadata of the cube displayed as in the left side of the next figure. Expand Customers, Orders, and Products in the dimensions and drop them on the list header in the bottom pane on the right hand side of the next figure.
Drag and drop Freight from Orders under Measures as shown. For each of the dimensions using an operator create a filter expression as shown. For example, the Products included in the query range from Product IDs 3 to 9; the Customer ID (some specific ones are chosen), similarly theCategory ID (only those with ID’s 2, 3, 4, and 5 are included in the query).
The design of the query with these filtering choices is as shown in the previous figure.
The filtering tool is very flexible and all the items namely Dimension, Hierarchy, Operator and Filter Expression can be designed in this interface using either drop-down pick lists choices or drop-downs with checkboxes as shown here.
Run the query by clicking on the toolbar item (!) and review the results. Click OK on the Query Designer. The Dataset Properties window shows up with this query designed according to your choices in the query item window as shown. Close the Dataset Properties window.
Report Design
In Report Builder 2.0 click on New Table or Matrix wizard. In the Choose a dataset window accept the default and click Next. Drag and drop Customer_ID in the Row Groups drop area. Drag and drop Order_ID below Customer_ID in the Row Groups drop area. Drag and drop Product_ID in the Column Groups drop area. Drag and drop Freight into the Values drop area and click on Next.
In the Choose the layout window accept Expand/collapse groups and choose the option Stepped, subtotal above.
Click Next and choose some style (herein Ocean) and click Finish. The final report design is as shown here.
Click Home | Run. The report gets displayed after processing as shown here. The orders from a particular customer have been expended in this view.
Summary
Report Builder 2.0 can used to author reports based on Analysis Services Cubes. The interface is very flexible and the Query Designer is very easy to use as shown in this article.
If you have read this article you may be interested to view :
- Creating an Analysis Services Cube with Visual Studio 2008 – Part 1
- Creating an Analysis Services Cube with Visual Studio 2008 – Part 2
