Let’s do detailed talk on Microsoft report builder!!
Microsoft Report builder is an easy tool to build dashboard and graphical reports with less effort which is provided by Microsoft. This article will describe simple steps to build report using Microsoft report builder tool. the article will explain concept of report generation with an example. Additionally, it will elucidate how to write simple expression in reports and represent report data in tabular form. Learners will be able to build reports including expression and representation of data in table form from the scratch.
According to Microsoft documentation, SSRS reports are also called paginated reports because they fit well on pages and their design is mostly focused on displaying figures on the page.
A paginated report can be shared and printed straight away.
However, paginated reports are represented by .rdl files and contain the definition of the report layout. It is backed up by shared or embedded datasets and data sources which tell the report where to get the data when it’s being processed.
MS Report Builder is used to design paginated reports by hiding the underlying details.
SSRS report SQL Server Reporting Services:
SQL Server Data Tools (SSDT) offers a complete business intelligence solution for developing, testing, deploying, and version-controlling SSRS reports along with creating and managing the data sources behind these SSRS reports in the form of SQL Database Project.
Not only can developers manage their databases through SQL Server Project (which is part of SQL Server Data Tools), but they can also create and run database unit tests against the reporting objects.
On the other hand, MS Report Builder helps IT professionals and power users to create and manage paginated reports comfortably and quickly.
Create RDLC report with Microsoft report builder:
You can download MS report builder simply by clicking the below link:
The page will show you the version details and requirements for the installation as shown in the picture.
By just clicking the Download button it will be downloaded in your system. After installing the downloaded file you can open it from your start menu as shown below.
Overview of MS Report Builder:
After opening the report builder, the “Getting started” prompt will appear where you can select what you want to get started with.
Here we are going to create a new blank report. You can go with Table or Matrix wizard or chart wizard or map wizard there you will get step by step instruction for that particular wizard.
Here You can also create a new dataset from any data source or open any previous/Recent report.
The Blank report will be created with the layout as shown below in the picture.
You can add data source manually by following these steps:
Then the prompt will open for data source properties. Here you can give the name to your data source and select the connection path from which you will get your data.
After setting up data source. On report data panel right click on the data sets and then click add dataset… which will open a prompt for the dataset property you can set up your dataset from this prompt.
Here I am taking my dataset from business central with AL code. Where I have created a report with setting up all the data items I needed to create a dataset. Here we must give properties as shown below to create RDLC report and we have to give exact path of our MS report builder’s file with .rdl extension. As we still not saved our rdl file here I gave a custom path for it.
Now we will save the report builder file from the file menu by clicking on save as. Here we can choose where to save this file and with customized name. as I gave the path of this file in my report program, I will name it as given there which is ‘MyRDLCReport’ and the .rdl extension will be added automatically. I will save this file in the same folder where my AL program file is. To make the path correct which I have given already.
Now the report is saved and after running my AL file once, it will take the dataset automatically which is defined there as shown below.
From view option we can enable or disable the display of various layout fields like ruler or grouping pane. These layout fields are also available in the view menu from the menu bar.
Header and footer options are here to add or remove the header or footer from the report. These options for header and footer are also accessible from the ribbon of insert menu.
Report properties will open a prompt to modify report properties. Which can also be modified by the properties pane which shows the report properties when we normally click the outside area of the report.
By right clicking on the body of the report will show different menu with some different options like copy, insert.
Normal options to cut, copy or paste any report object, text or expression.
This option will show a menu containing various report objects to insert in the report like text box, table etc. Which can be also accessible by the ribbon of Insert menu.
This option will select all the objects of the report.
Body properties will show a prompt to modify the properties of report body. Which can also be accessed by the properties pane while simply clicking on the body area of the report.
Designing Report Layout:
Now we will actually prepare the report layout. We can first add the title to our report. My report is for customer so I will give the title Customer.as well as we can change the background color of the textbox and do all other formatting with the ribbon available on the top and also from the properties pane. If you want your title in all the pages of the report than First add the header to the report and then add the title in the header of the report.
Table Design Format:
Now we will try to insert a table object to this report by many ways. Now we will click on insert menu which will show the ribbon containing table object. We can click on that and choose Insert Table. To ease the process one can also choose Table wizard for step-by-step guide to create a table. By inserting the table, we can see a table with header row and a data row with three columns and properties pane for Tablix properties.
We can add our dataset to this table simply by drag and drop to particular column where we want our data to be displayed. And we can also add dataset to more than three columns and can add extra rows if needed. We can format cells, rows and columns by properties pane like borders, colors, background image etc. we can edit the column name and format the table data as per requirements.
If we want any type of functional expression with constant, variable or any dataset fields we can add an expression by right clicking the empty text box and click on the expression which will open a prompt to add an expression as shown below. I want to display total umber of rows on the first page so First I added the empty textbox and click on the expression by right clicking on the textbox. In the prompt, select common functions àAggregateà CountRows. By clicking on ok it will create an expression with the sign <<Exp>>. After that we can add any text in that textbox and can format as well. This field will not work in header or footer.
We can also add expression in the table as shown below. First right click on the cell where you want your expression in the prompt, we can select any functions here I want the sum of the whole amounts column. So, I can do it by selecting Aggregate à Sum à Fields à CuAmount which will create an expression as shown below.
If we want to add page number to the footer, we can add it by built-in fields from the report data pane. It has the field Page Number we can just drag and drop it where we want to display page number.
Now our report layout is ready to generate report. we made it using visual studio that’s why we will run it from there. So, my demo report is looks like as shown below in the picture.
Hence, this article described the simple steps to a build the report and do various thigs like formatting and calculation using Microsoft Report Builder. The article demonstrated steps to define data source, create datasets and build paginated Reports using Microsoft Report Builder which would be helpful for beginners. As well as learner will be able to write/understand basic knowledge of expressions and show the report data in tabular format in the report.
Feel free to connect with Madhda at firstname.lastname@example.org
Thanks & Regards,
Thanks to all.