Designing an inventory report

Contents[Hide]

1. Overview

This walkthrough shows you how to use the Dundas BI Report Designer to create a basic inventory stock report, similar to the example from Report basics and concepts.

Before following this walkthrough, have a look at our Scorecards and Reports videos.

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

2. Data preparation

To begin, it is useful to prepare the data for the report by creating data cubes and metric sets. This will allow you to join and filter the necessary data. Additionally, you will be able to use data cube storage options to improve performance.

This walkthrough uses the Adventure Works for SQL Server database to create the following analysis elements:

  1. A data cube that filters certain data from the product subcategory. This will be used for the parent group in the report.
  2. A data cube that joins the Product and ProductInventory tables. This will be used for the child group in the report.
  3. A metric set based on the child group data cube.

The steps required are very similar to the steps described in Using a data cube to join tables.

2.1. Create the parent group data cube

Create a new data cube from the main menu.

Drag the [Production].[ProductSubcategory] table from your data connector to the canvas. From the toolbar, click Configure and include only the columns ProductSubcategoryID and Name in the SQL Select transform.

Select the connection link. From the toolbar, click Insert Common and then click Filter.

Double-click the filter transform to configure it.

In the Filter dialog, click the Edit icon to the right of the Name column. This column corresponds to the name of the product subcategory.

In the Transform Element dialog, set the Element Operator dropdown to Does Not Equal and set the String Value to Mountain Bikes. This will filter out or exclude records where the product subcategory is named "Mountain Bikes."

Filter transform
Filter transform

Click the Process Result transform to configure it.

Click the Edit icon for the Name column and change its caption to SubcategoryName.

Give the data cube a meaningful name and check it in.

2.2. Create the child group data cube

Create a new data cube from the main menu.

Drag the [Production].[Product] and [Production].[ProductInventory] tables from your data connector to the canvas.

Select the connection link, go to the toolbar, and then insert a Join transform.

Connect the [Production].[ProductInventory] table to the join transform by dragging a connecting line.

Double-click the join transform to configure it. Perform the join on the ProductID column, and keep the following columns:

  • From [Production].[Product] keep ProductID, Name, ListPrice, and ProductSubcategoryID.
  • From [Production].[ProductInventory] keep Quantity.

Click the Process Result transform to configure it.

Click the Edit icon for the Name column and change its caption to ProductName.

Give the data cube a meaningful name and check it in.

Child group data cube
Child group data cube

2.3. Create the child group metric set

Create a new metric set from the main menu.

Locate the Child Group data cube from the Explore window and drag all of the measures and hierarchies to the canvas.

For the purpose of this walkthrough, you can leave the metric set with all of the default and automatic settings. Give the metric set a meaningful name and check it in.

Metric set
Metric set

Edit the ListPrice measure, and in the Configure Metric Set Element dialog, set Format to N2 to show only two decimal numbers and Symbol to $.

3. 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

The Report Designer canvas is displayed. The default page size for the report is set according to your locale. For example, en-US and en-CA will default to letter-sized pages (8.5 x 11 inches).

To change the page size of the report, click on the canvas area outside of any report regions. Go to the Properties window, and click the Layout tab.

For this example, change the Paper Size to Custom, Unit to Pixel, and set the Width to 748.

Change the report width
Change the report width

3.1. Configure the title for the report

By default, a label component is added to the Report Header region displaying the text, 'Title'.

Select the label and go to the Properties window. Set the following properties:

  • Set Label Text to Inventory Stock List.
  • Set Text Alignment to Left.
  • Set Font Size to 20px.
  • Set Font Weight to Bold.

Resize the Report Header region so it is not so tall. The label inside is set to an Absolute position kind, so you have to resize the label before resizing the region.

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

Next, from the toolbar, click Components and then click Line. This will insert a line component on the canvas, which you can use as a separator.

Drag the line onto the Report Header region (you may have to resize it to be able to grab it). Go to the Properties window and set the following properties for the line:

  • Set Dock Location to Bottom.
  • Set Stroke Color to Black.

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

Switch to View mode to see how your report looks so far.

3.2. Add a column for grouping

Go to the Explore window and expand your Parent Group data cube. Drag the ProductSubcategoryID column from the data cube and drop it onto the Grouping hierarchy region (drop a column or hierarchy here...).

This will automatically insert corresponding Group Header, Group Body, and Group Footer regions. 

Add a column for grouping
Add a column for grouping

Dundas BI also adds a Header data visualization to the group header and a Data Label data visualization to the group body, both corresponding to the grouping dimension. Delete both of these controls, as they are not needed for the purpose of this example.

3.3. Add labels to the Group Header

Elements in the Group Header region appear only once, they are not repeated. 

From the toolbar, click Components, and then click Label. This adds a regular text label to the canvas. Drag the label and drop it onto the Group Header region.

Go to the Properties window and change the label text to Qty Left. Increase the label's font size to 16px and make it bold.

Use the Clipboard from the toolbar to copy the label and then paste it. Change its text to List Price.

These two labels represent the column headings for the report.

Finally, resize the height of the Group Header to be smaller.

Use static labels to show column headings in the Group Header
Use static labels to show column headings in the Group Header

3.4. Add controls to the Group Body

Controls in the Group Body are repeated once for every ProductSubcategoryID value. In this case, we only want to display the name of the subcategory. 

From the Explore window, drag the SubcategoryName column to the Group Body region.

The column turns into a Data Label visualization control and appears along with a Header data visualization. 

Display subcategory name using a data label
Display subcategory name using a data label

Delete the new Header data visualization.

In Edit mode, the data label displays (All), but will display the subcategory name when you switch to View mode.

In the Properties window, set the appearance of the data label as follows:

  • Set Background to LightGray
  • Set Dock Location to Top. This will dock the label to the top of the Group Body region and fill the whole width
  • Set Text Alignment to Left
  • Set Font Size to 14px
  • Set Font Weight to Bold

Next, add a line component from the toolbar and drag it onto the Group Body region. This line will also be used as a separator between repeated subcategories.

Group Body region with a line and a data label
Group Body region with a line and a data label

Tip
Instead of adding a line component, you can also display a separator in a body region by selecting the region and going to the Properties window. Under Look properties, you'll see a Separator Lines section which lets you add and configure the appearance of a separator line.

Switch to View mode to see the report so far.

Viewing the report so far
Viewing the report so far

3.5. Add a Child Group

The report properly displays subcategories now. But below each subcategory we want to show a list of products, along with the quantity and list price information.

To do this, it is necessary to add a second (or nested) level of grouping. You can drop hierarchies on the child grouping area (the second drop a column or hierarchy here... region), just like you did with the parent group above, or drop a complete metric set there.

From the Explore window, locate your Child Group metric set and drag it to the child grouping area.

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

Newly inserted child group regions
Newly inserted child group regions

Dundas BI automatically adds the hierarchies in the metric set to the child group repeater, as well as adds Header data visualizations to the child group header and Data Label data visualizations to the child group body, corresponding to each element in the metric set. 

Delete all of the controls in the child group header, as you will not need them for this report. Also delete the ProductID and ProductSubcategoryID data labels from the child group body.

Reorganize the remaining labels. Align the ListPrice and Quantity data labels with their respective labels from the parent group, and place the ProductName on the left end.

Finally, add a line component, make it black and 1px wide, and place it in the Child Group Body region above the three data labels. This will be used as a separator between each product.

Complete the visual design of the child group
Complete the visual design of the child group

Since you are using two separate data cubes, Dundas BI will not be able to automatically identify the relationship between the two groups. In this case, the child group will not be filtered by the parent group repeater and will return the same results every time.

Tip
In some cases you may want such a static behavior on a control in the group. Use Select data separately to create a separate metric set for that control and then remove the link from the repeater hierarchy.

To tell Dundas BI how to perform the filtering, click the link icon at the right side of the ProductSubcategoryID hierarchy in the parent group repeater.

Click the link icon
Click the link icon

From the Group Element Linking dialog, under the child group metric set, select the ProductSubcategoryID hierarchy.

Select the child group hierarchy
Select the child group hierarchy

Important
The values in the linked hierarchies must be identical for the repeater filtering to work properly.

3.7. View the final report

Switch to View mode to see the final report.

The final report
The final report

While you could have presented the inventory data with a table visualization, using a report gives you much greater flexibility with the layout. This particular example only used data labels and general components, such as lines and text labels. However, you can add other types of data visualizations to your report, including charts and gauges.

4. 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: 7am-6pm, ET, Mon-Fri