Microsoft has obviously taken this into consideration because the SSRS report builder is a very simple report design tool and they can be easily learned and used by non-technical staff. RDL reports and host them using SQL Server Reporting Services, if you have scenarios with single master-detail tables, you just have to group your table by the master data and the details will appear automatically, even using the report wizard, but what if you have a master table with multiple detail tables? Designing master-detail reports There are two ways to design master-detail reports.
SalesTbl The Data properties screen includes various settings and configuration tab according to dataset. Product P, Production. The detail report displays product names and numbers. In the Parameter tabs, we can find out and define parameters which are related to dataset so we can use parametrized queries in the report builder dataset. In the List Properties dialog click on Edit details group. Then, go to the parameters tab and you will see the parameter is already there, if it does not exist, just type it, and for parameter value, select the parameter we created previously, then click OK to save the dataset.
Now, we will publish the report to SSRS. Click the New button to create a new filter.
A List is used to display fields from the master. In field tab we can add and delete columns of the dataset and we can also change the columns names. Now, we will publish the report to SSRS. In the Grouping and Sorting Properties dialog enter the master's primary key as the Group on Expression. Click Finish We will Run the report The output of the report will be as follows We completed the report design but this report still needs some style improvements.
To do this, right-click on the List and choose Properties from the context menu. Then we proceed to add and configure the second sub report in our main report, as we did earlier: And now the moment of the truth, we want to make sure that each value displays the correct details and that the values are not duplicated: We succeeded on displaying the data the right way, now you can work on customizing the format and layout to suit your needs. Select the dataset in which we want to display the data in summarized format. This is the only option if master and detail rows cannot be easily joined into a single rowset.
The Report builder main screen is not very complicated and it allows us to create reports easily. A List is used to display fields from the master.
The first option is to use subreports. If this is not easy for example, if your data is not coming from a SQL database then use subreports instead. Preparing to sample data Before we start our SSRS report builder demonstration, we need a sample dataset.
To accomplish this, though, report and BI tools must be so easy and understandable by non-technical users. Key stakeholders can gain the ability to develop and create their own reports so that the IT department dependency and waiting time will decrease.
How can we address this issue?
In the List Properties dialog click on Edit details group. In the Property Window navigate to the ReportParameters property and click the ellipses. SQL Server Reporting Services allows us to use two types of connection methods; Use a shared connection or report model: In this method we can use a shared data source. The detail report should now look something like this in the designer.