Learn MSBI Part 6:- Displaying reports using SSRS

Links to other Labs of Learn MSBI Step by Step.

Learn MSBI Part 1:- Creating SQL tables, Practicals of SSIS-ETL(Extraction, Transformation & Loading)

Learn MSBI Part 2:- Performing Validation on Records

Learn MSBI Part 3:- SCD (Slow Changing Data)

Learn MSBI Part 4:- Fact and Dimension.

Learn MSBI Part 5:- CUBE Creation

Learn MSBI Part 7:- DQS (Data Quality Services)

Learn MSBI Part 8:- Data Profiling Task

Learn MSBI Part 9:- Pivot, UnPivot and Aggregation

Learn MSBI Part 10:- Execute SQL Task component (SSIS)

Learn MSBI Part 11:- Send Mail Task & Script Task Component

Learn MSBI Part 12:- CDC Concept using SSIS

Learn MSBI Part 13:- Asynchronous, Synchronous, Full-blocking, Semi-blocking & No-blocking

Learn MSBI Part 14:- Understanding Page Split and maintaining SSIS Performance

Learn MSBI Part 15:- Debugging in deployment – Instrumentation using Data Taps

Learn MSBI Part 16:- Using Multi-threading and its performance in SSIS program

To create reports in order to display the data of CUBE created in earlier lab. For that now go to SSDT Visual Studio 2013 on Solution of Solution Explorer do a right click on Add a New Project as shown in the image below: –

It will open screen to Add New Project, here select Reporting Services from Business Intelligence from left menu once the selection is done on the right side select “Report Server Project Wizard” and then give a suitable name to the Reporting Services project, here name given is “SSRSCustomer” and also select the location where the project is going to be saved. Once project name and project location is selected then click on OK to close the screen.

It will open Report Wizard as shown in the below image the very first screen is a welcome screen over there is an option to skip this screen just do a tick to avoid welcome screen to get opened from next time.

Depending on your wish to show welcome screen click on OK to move to the next screen of the wizard.

Next Report Wizard screen is to select the Data Source here in this screen select data source Type as “Microsoft SQL Server Analysis Services” and then click on Edit to configure it.

After clicking on Edit it will open Connection Properties here provide Analysis Server Name and the database name.

To know the Server Name go to SQL Server Management Studio click on File a Connect Object Explorer, select “Server type – Analysis Services” and will see Server name.

Once all fields are filled on Connection Properties page then do a Test Connection by clicking on the button available at the left once that is succeeded then click on OK to close its window.

Next screen where designing or writing of the query will be done as already here selection of SSAS database is done so in order to query SSAS CUBE there is a need of MDX(Multi Dimension Queries) in other words to query SSAS CUBE use of MDX will be done.

So in this screen there is requirement of MDX queries to be written, so there are two ways for writing MDX (a) by using Query Builder (b) by writing MDX manually.

Being very new to MDX for time being will use way of automatic created MDX with the help of Query Builder for writing MDX.

Query Builder will give designer where designing will be done and automatically MDX will be created.

So starting it by clicking on Query Builder, which will open Query Designer. On Query Designer drag and drop Fact and Dimension on Designer which will form table with CUBE formation depending on Dimensions selected. Next is to click on OK to close Designer window.

In next window screen it will generate MDX query automatically based on the inputs selected in the Designer mode and it will be displayed under Query string as shown in the image down below.

Next is to select the how the report is going to be displayed, here select Tabular type which will display in simple rows and columns form.

Now Design the table, by selecting Available fields and displaying report it as details and for click on “Details” and all fields from Available fields will move to details fields as shown in the image below.

Next wizard window to select Table Style here select styling of table as per project requirement or choice, here selection is done as Corporate and then click on Next.

Following screen is to choose report deployment location. We kept the default location by keeping already existing default field values unchanged as shown in the image below.

And then click on Next.

Next comes is the final screen of the Report Wizard which is to do final preview of all configuration done in the Report Wizard.

If any configuring setting is found not proper then can go back by clicking “Back” button available at the bottom and can rectify the issue.

In final preview, under Report summary once everything is found correct then finally click on Finish.

Once Report Wizard is closed, under Solution Explorer project named “SSRSCustomer” is created and Designer mode of the report with name Report1.rdl is opened as seen in the image below.

Now in order see report, it has to be executed from the project by doing a right click on “Report1.rdl” and click on Run.

Report will be displayed in the Report Preview as shown in the below image.

Also see our Learn MSBI Project Series video below