Welcome to the second part of our blog series on creating master-detail reports in Bold Reports Report Designer. In Part 1, we covered setting up a master-detail report using tables. In this blog, we'll explore how to design a master-detail report using a free-form layout with the list report item in Bold Reports.
While tables offer a structured presentation, lists provide flexibility to arrange data in a more fluid and expressive manner. We'll show you how to unleash your creativity by organizing master data in a way that not only conveys information effectively, but also captivates your audience with a visually engaging presentation.
What is a master-detail list?
Think about creating a report that shows a list of products, like items you'd find in a cycling store. The first part of the report shows different product categories, like caps and helmets. This is the master part. The second part lists the specifics of each item in the categories. This is the detail part.
Presenting information in this format is effective for organizing items that have numerous details.
Creating a master-detail list in Bold Reports
Let’s see how to create a master-detail report like this using lists in Bold Reports. Let’s assume that you are already using the Report Designer from the Report Server or the standalone Report Designer and have created a new report.
Create the data source
To create this report, we are going to use the AdventureWorks database. Here are the steps to connect to the database:
1.Click Data in the configuration panel, select the Datasource tab, and then click New Datasource.
2.Select the type of data source. We are going to connect to Microsoft SQL Server for this report.
3.Provide the database information to establish a connection, and then save the data source. We are using the AdventureWorks database and Windows authentication type.
After creating the data source, the next step is to create the dataset.
Create the dataset
Once the data source is created, we need to create the dataset to retrieve the necessary data from the database and then add it in the master-detail list.
1.Click Data in the configuration panel, switch to the Dataset tab, and click New Data.
2.Select the Existing option and then choose the data source we've just created.
3.Open the Dataset Designer (also known as the Query Designer) by clicking the Connect DataSource button.
4.Switch to code mode in the Dataset Designer by turning on the Code option in the toolbar. This allows us to enter SQL queries.
5.Add the following query to retrieve all the product details from the database:
SELECT top 60 PS.Name AS ProdSubCat, PM.Name AS ProdModel, PC.Name AS ProdCat, PD.Description, PP.LargePhoto,P.Name AS ProdName,P.ProductNumber, P.Color, P.Size, P.Weight, P.StandardCost,P.Style,P.Class, P.ListPrice
FROM Production.Product P INNER JOIN
Production.ProductSubcategory PS INNER JOIN
Production.ProductCategory PC ON PS.ProductCategoryID = PC.ProductCategoryID ON P.ProductSubcategoryID = PS.ProductSubcategoryID INNER JOIN
Production.ProductProductPhoto PPP ON P.ProductID = PPP.ProductID INNER JOIN
Production.ProductPhoto PP ON PPP.ProductPhotoID = PP.ProductPhotoID LEFT OUTER JOIN
Production.ProductDescription PD INNER JOIN
Production.ProductModel PM INNER JOIN
Production.ProductModelProductDescriptionCulture PMPDCL ON PM.ProductModelID = PMPDCL.ProductModelID ON
PD.ProductDescriptionID = PMPDCL.ProductDescriptionID ON P.ProductModelID = PM.ProductModelID
WHERE (PMPDCL.CultureID = 'en')
6.Click Finish. The dataset will be created with the name ProductCatalog.
Add a master list
1.Drag and drop a List report item from them item panel onto the report layout. We will use this to display the master data.
2.Assign the already created ProductCatalog dataset to the master list.
The list we are creating will act as master data, so do not use any fields we will use in the detail table.
3.Select the list. This will open the Row Groups section at the bottom of the designer. Click the (Details) group and then click Set Groups in the Properties panel, which opens the Grouping dialog for setting group information.
4.Add a Group by value for Product Subcategory to group the values based on the product subcategory data in the list. Click OK to update the group.
5.Right-click on the list and select Insert > TextBox.
6.In the added text box, enter the label text Product Subcategory:.
7.In the same way, add another text box. Right-click on this new text box and select Expression to open the Expression dialog.
8.In the Expression dialog, select the Fields (Product Catalog) dataset in the Data dropdown. Then, double-click Product Subcategory in the list below it. The expression will be added in the text box at the top of the Expression dialog. Click OK.
9.A text box with an expression for Product Subcategory has been added to the report.
10.Follow the same process to add text boxes and expressions for Model and Description. The resulting master list is shown in the following image:
Next, we need to create a details table inside the master list.
Implementing a details table
1.Right-click on the list, select Insert, and then choose Table.
2.The details table will be added in the master list.
3.Add fields for product number, name, size, weight, and cost to the table.
We have finished creating the master-detail report with a list. The report is ready for preview.
Preview the report
Preview the report by toggling from Design to Preview in the top panel. This lets us view the master-detail report with product details like product number, name, and cost grouped by subcategory, model, and description in the report.
Publish the report
Let's publish our master-details report. We can publish it publicly or privately.
1.Click the Publish button.
2.In the Publish As Report window, fill in the fields for Category, Name, and Description. Decide whether you want to make the report public or private by using the Mark As Public option.
3.Click Publish to publish the report.
Conclusion
Using lists is a great way to make master-detail reports clear, organized, and visually appealing.
To learn more about Bold Reports, check out our sample reports and documentation. You can access a demonstration report RDL file here for your use.
If you have any questions, please post them in the comments section. You can also contact us through our contact page, or if you already have an account, you can log in to submit your question.
Bold Reports offers a 15-day free trial with no credit card information required. We welcome you to start a free trial and experience Bold Reports. Be sure to let us know what you think!
For announcements about new releases, follow us on our X, Facebook, and LinkedIn pages. Happy reporting!
Top comments (0)