How To Publish Reports Using SSRS In Power BI?
The SQL Server Reporting Services (SSRS) is a part of Microsoft SQL server services power Business Intelligence (BI) since the year 2000. Every ordinary Microsoft user with the basic knowledge of SQL can prepare reports using SSRS in Power BI using the below-mentioned steps.
Before a user starts to use SSRS, the user must ensure that their device meets the following requirements:-
- Microsoft SQL Server 2014 database engine.
- SQL Server 2014 reporting service (SSRS).
- SQL Server Data Tools.
- Any database to be used as a data source.
Creating a Report Server Project
The steps involved in creating a new report server project and report definition file are:-
- This starts with opening the SQL Server Data Tools, then click on the File menu, then finding New, and then Project.
- After this, the user needs to click on the option for Business Intelligence.
- Then click on the Reporting Services and then Report Server Project.
- If the user wants to display the Report test project to get started, he or she can type the Report test in the Name option.
- The last step is to click OK to finish the creation of a new server project.
- Go to the View menu, the user needs to find the Solution Explorer, and then right-click on the Reports folder. In the Add section, click on New Item.
- In the window Add New Item, click Report.
- As you can see Report Designer has two available views. In the Design view, the user needs to define their report layout, and in the Preview view, the user can run his or her report.
Adding Tables and Fields to Report Layout
After completing the technical part, the user can try doing the interesting part of designing the SSRS report which is much easier as compared to the technical part. The steps involved in adding tables and field on the report are:-
- Firstly in the View menu, click on the option for Toolbox, then find and click the Table option and drag the mouse to the design area of the report.
- On the left side, the user can expand the dataset of the Dataset1 in order to see all the fields which would be a part of the report.
- Then the user needs to drag one of the fields ( for instance field Date) from Report Data to the column in the table
- Similarly, the user can continue adding fields and the table will automatically add more columns.
Formatting the Report
For formatting the data field to only show the date, the user needs to follow the following steps:-
- In the Design tab, right-click the desired cell and then click on the option for Text Box Properties.
- Find and click Number, then in the field Category, choose the option for Date
- As in the option for Type, the user needs to select the date format which he or she wants, and click OK to finish.
If the report contains information about money which include currency changes as well but the user only finds them as ordinary numbers, he or she needs to follow the following steps to get the number as currency:-
- In the Design tab, right-click the desired cell, then click on the option of Text Box Properties.
- Find and click Number, then in the field Category choose Currency
- In accordance with the needs, the user can change the defaults
- Click OK to finish.
The above steps can help the user publish reports using SSRS in Power BI. With proper Microsoft Power BI Training, organizations can learn to create Power BI reports. EPCGroup provides Power BI Consulting services using which organizations can implement BI solutions.
Sas Chatterjee is a Senior Architect with EPC Group. His focus lies in making sure that the execution of each engagement is delivered in a forward compatible, best practices manner. Sas is an extremely devoted professional and takes each project he is assigned very seriously. During the project execution phase, Sas invests the time needed with his clients to gain a full understanding of their requirements and develops a roadmap for achieving their desired end goal.