Create a SSRS Report based on a Cube and Deploy it

In our previous article, we took an overview and need of SSRS and various kinds of report delivery. In this article, we will discuss how to create a report and deploy it. We will create a report based on the cube we created in the article Creating a Cube and Dimension using SSAS

1. Add a new Report Server Project Wizard in the same solution and name it ReportDemo. As we have selected the wizard, it will automatically start with report creation.

2. Since we need to create this report on a cube, we need to change the type to Microsoft SQL Server Analysis Services and click on Edit button as shown below:

Enter the server name for analysis services and select the previously created DemoCube, click Next.

3. Click on Query Builder which will take us to the query designer. Drag Line Total and Order Quantity from measures on to design. Also drag and drop Product Category and Year – Quarter – Month – Data hierarchies. Select Time dimension in filter area and specify the Year as hierarchy. Select Operator as equal, select years 2003 and 2004 and click the checkbox for the parameter as follows

Click Ok. The created query will be shown in next screen. Click Next.

4. Select Matrix as the report type and click Next.

5. Do the selection for design the matrix as follows

Note the check box for Enable drilldown.

Click Next. Select Corporate as the style and click next.

6. In ‘Choose the deployment location’ screen, enter the Report Server name and the deployment folder. If you have the default instance then you can keep the URL as it is. If you have the named instance enter URL as http://<machine name>/ReportServer_<instance name. Enter the report name as Product Information and click Finish. You can click the check box for Preview Report if required.

7. Select Build and deploy the report project or right click on project name in solution explorer and select deploy. To view the deployed report enter the URL for Report Server as follows:

http://localhost/ReportServer (default instance)
http://<machine name>/ReportServer_<instance name> (named instance)

The URL for Report Manager is
http://localhost/Reports (default instance)
http://<machine name>/Reports_<instance name> (named instance)

Note: if the port number is other than 80 it will be given with the machine name followed by semicolon (:)