Chat Icon
Webinar A Deep Dive into Report Parts for Accelerated and Reusable Reporting - Thursday, February 29, 10:00 A.M. ET SIGN UP NOW
Created with Sketch.
Login-icon
Create Impressive Master-Detail Reports in Bold Reports Report Designer Part 2: Lists
Master Detail Report using List

Create Impressive Master-Detail Reports in Bold Reports Report Designer Part 2: Lists

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.

Master-Detail List Sample
Master-Detail List Sample

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.

    Data source configuration panel
    Data source configuration panel
  2. Select the type of data source. We are going to connect to Microsoft SQL Server for this report.

    Data Source Connection
    Data Source Connection
  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.

    Update Data Base Credential
    Update Data Base Credential

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.

    Dataset Configuration Panel
    Dataset Configuration Panel
  3. Open the Dataset Designer (also known as the Query Designer) by clicking the Connect DataSource.
  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')

    Dataset Query Designer
    Dataset Query Designer
  1. Click Finish. The dataset will be created with the name ProductCatalog.

    Dataset Created
    Dataset Created

We’ve finished the initial data preparation for the report. Next, let’s design the structure of the master-detail list.

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.

    Master Table Dataset
    Master Table Dataset

The list we are creating will act as master data, so do not use any fields we will use in the detail table.

  1. Select the list. This will open the Row Groups section at the bottom of the designer. Click the and then click Set Groups in the Properties panel, which opens the Grouping dialog for setting group information.

    Set Groups
    Set Groups
  2. 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.

    Update Grouped by Value
    Update Grouped by Value
  3. Right-click on the list and select Insert > TextBox.

    Adding Textbox
    Adding Textbox
  4. In the added text box, enter the label text Product Subcategory:.

    Label Textbox
    Label Textbox
  5. In the same way, add another text box. Right-click on this new text box and select Expression to open the Expression dialog.

    Expression Textbox
    Expression Textbox
  6. In the Expression dialog, select the Fields (Product Catalog) dataset in the Data 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.

    Expression Dialog
    Expression Dialog
  7. A text box with an expression for Product Subcategory has been added to the report.

    Product Subcategory
    Product Subcategory
  8. Follow the same process to add text boxes and expressions for Model and Description. The resulting master list is shown in the following image:

    Master Data
    Master Data

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.

    Adding Details Table
    Adding Details Table
  2. The details table will be added in the master list.

    Details Table
    Details Table
  3. Add fields for product number, name, size, weight, and cost to the table.

    Details Table Added
    Details Table Added
  4. 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.

Master-Detail List
Master-Detail List

Publish Report

Let’s publish our master-details report. We can publish it publicly or privately.

  1. Click the Publish.

    Publish Report
    Publish Report
  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.
  3. Click Publish to publish the report.

    Report Publish
    Report Publish

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!

Tags:

Share this blog

Leave a Reply

Your email address will not be published. Required fields are marked *