Designing an inventory report

Contents[Hide]

1. Overview

This walkthrough shows you how to design a pixel-perfect multi-page report in Dundas BI, similar to the example inventory report shown in Report basics and concepts.

It displays two levels of grouping: one for product subcategories, and another for products. For this walkthrough, data is displayed as text using data labels, but you can use any Dundas BI visualization for your data in any report region.

An inventory report with two levels of grouping
An inventory report with two levels of grouping

Related videos: Scorecards and Reports

2. Designing the report

Go to the main menu, click New, and then click Report.

Create a new report from main menu
Create a new report from main menu

A new report is opened in edit mode.

The default paper size for the report is set according to your locale - for example, letter-sized pages (8.5 × 11 inches) are used by default if your culture is en-US or en-CA. To set the paper size, open the Properties window to the Layout tab without any elements selected.

Change the report width
Change the report width

You can set up font properties to take effect for most of the content of the report at once in the Text tab, or by clicking an empty area of the report to bring up Quick Access Properties.

For this example, set the Font Size to 14px.

Change the report font
Change the report font

2.1. Title

A label component was added automatically to the Report Header region as a title when you created the report.

Select the label and go to the Properties window in the Text tab, where you can set the following properties:

  • Set Label Text to Inventory Stock List (or double-click the label to set its text).
  • Set Text Alignment to Left.
  • Set Font Size to 20px.
  • Set Font Weight to 500 (or Bold).

Configure the title in the Report Header
Configure the title in the Report Header

You can optionally add other content to the Report Header, such as another label, found under Components in the toolbar, displaying an address.

Next, click the area labeled RH on the left to select the Report Header region, then in the toolbar click Components and choose to add a Line here to use as a separator below the title.

Go to the Properties window and set up its Look and Layout:

  • Set Stroke Color to Black.
  • Under Dock Location, click the bottom-center square to dock to the bottom.
  • Set Bottom to 0, and set Height to 2 to match the width of the line.

Add a line below the title
Add a line below the title

You can switch to View mode to see how your report looks so far, then return to Edit.

2.2. Add subcategory rows

Go to the Explore window and drag the data that will make up the rows of the report onto the grouping hierarchy region, labeled drop data here to repeat rows for each value.

In this example that uses the Adventure Works sample database, find and expand the table [Production.ProductSubcategory], then drag the ProductSubcategoryID column in order to uniquely identify each product subcategory and display a row for it.

Display rows for subcategories
Display rows for subcategories

This will insert Group Header, Group Body, and Group Footer regions for the subcategories, and automatically add a header and corresponding data label to display the subcategory IDs.

For this example, select the header and data label and click Delete in the toolbar, because the design of this particular report does not display these IDs directly.

2.3. Add subcategory names

For our example, we want to display the name of the subcategory in each of the repeated rows.

In the Explore window, drag the Name column from the same [Production.ProductSubcategory] table to the Group Body region (labeled place content to repeat here).

Display subcategory names in each row
Display subcategory names in each row

This automatically adds a header and corresponding data label for the names. For this example, select and Delete the unneeded header, and resize the width of the data label to fit longer names. (In Edit mode, the data label displays (All), but it will display the subcategory name when you switch to view mode.)

Set the Font Weight property for the data label to 500 (or Bold).

Click an empty part of the group body region to select it. In the Look tab, set its Background property to #F2F2F2, and in the Layout tab set its Height to 40 pixels for this example. (You can also drag the resizer on the left of the group body to change its height).

Next, click Components and add another Line, this time in this group body region to separate each subcategory.

Group body region with a line and background
Group body region with a line and background

Tip
For many report designs, you can also select the group body region and use Row Separator Lines in the Look tab of the Properties window instead of adding your own line components.

Set it up like the previous line, except dock it to the top, with the Top property set to 0.

2.4. Add product rows

We want to show a list of products within each subcategory along with the quantity and list price information. We can do this by adding a child (or nested) group inside our existing subcategory grouping.

In the Explore window, find and expand the [Production].[Product] table, and drag the ProductID column onto the child grouping hierarchy region (drop data here to repeat content for each value within every value above).

Add rows for each ProductID within each subcategory
Add rows for each ProductID within each subcategory

This will automatically insert a Group Header, Group Body, and Group Footer region for the products.

Tip
In this example, Dundas BI automatically joins the data from the product subcategory table with the product table for you. You can also prepare a data cube that combines all needed data ahead of time.

Dundas BI automatically adds the subcategory ID and name hierarchies from the parent group, and adds headers and data labels to display the data. Since we don't want to display subcategories or product IDs in the product rows, we can delete these headers and data labels from the child group header and body.

The parent group and child group each have their own data: the parent has the product subcategory data and the other has products, and they are related in this case by common ProductSubcategoryID values.

In our example, the product rows don't need the subcategory's Name column and we can remove this from the child group we just added in the previous section.

Delete subcategory names from product rows
Delete subcategory names from product rows

In many cases the child group will be linked correctly automatically, but you can confirm or change this by clicking the link icon next to each hierarchy in the parent group. For our example, click the link button for ProductSubcategoryID.

Click the parent group hierarchy link icon
Click the parent group hierarchy link icon

In the Grouping Hierarchy Linking dialog, select the matching ProductSubcategoryID data in the child group.

Link the ProductSubcategoryIDs
Link the ProductSubcategoryIDs

Important
The linked values must be identical.

2.6. Add product data

Now we can drag the data we want to see in the child group body region: from the [Production].[Product] table, drag Name and ListPrice, and from [Production].[ProductInventory], drag Quantity.

Headers for this data are added automatically to the child group header, which will display them above the product data within each subcategory group, repeating them for each subcategory.

Display product names, prices, and quantities
Display product names, prices, and quantities

This would be helpful for many report designs, but to produce the design in our example report, we can Delete the header for product Name and drag the ListPrice header to the right edge of the parent group header instead, followed by Quantity. Reposition the corresponding data labels if needed to the right so they line up with their headers.

You can double-click the ListPrice header or select it and choose the toolbar option to change its text to Price.

The product name data label should be resized to be as wide as possible to fit longer names. These data labels display data from the group metric set by default, so to use currency formatting for the price data, click the cube icon to the left of ProductID so that you can edit ListPrice from the Data Analysis Panel and click Define text formatting.

Click to select the group body region (you can click the area labeled B to the left). In the properties window's Layout tab, set Height to 40 like the subcategory rows.

Choose Components and then Line in the toolbar to add another separator line, this time between products. Set it up similar to the previous ones: Black except with only a 1px width, with height set to 1 to match, and docked to the top.

Product row design complete
Product row design complete

Reports are automatically given labels in the page header and footer displaying the page numbers, but you can optionally customize these. You can also drag predefined placeholder labels like these from the Labels window, normally docked to the right side of the screen.

To recreate the example report design, Delete the label from the page header.

Double-click the label in the page footer or select it and choose the toolbar option to change its text, and set it to Page [PageNumber]. Resize the label's width so that the text is centered on the page.

Customized page footer
Customized page footer

2.8. View the report

Switch to View mode to see the report produced by this design, and scroll down through subcategories to find the products listed for each.

To view the report as a PDF that you can save, share, or print, click Share in the toolbar and choose PDF. You can also set up a Notification to schedule a one-time or recurring e-mail to the recipients of your choice with the PDF attached.

Page 1 of the report
Page 1 of the report

You can also present data in a similar way with a table visualization, but using a design like this one gives you much greater flexibility with the layout.

This particular example only used data labels, but you can add or re-visualize to any type of Dundas BI visualization and use the same set of properties and metric set options to customize them as on dashboards. For example, the report shown in Report basics and concepts added bar charts to the report footer, or you could re-visualize the Quantity or Price data labels. For more report options such as page breaks and displaying headers on each page, see Design tips for reports and scorecards.

3. See also

 

Dundas Data Visualization, Inc.
500-250 Ferrand Drive
Toronto, ON, Canada
M3C 3G8

North America: 1.800.463.1492
International: 1.416.467.5100

Dundas Support Hours:
Phone: 9am-6pm, ET, Mon-Fri
Email: 7am-6pm, ET, Mon-Fri