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 Creating and Viewing Reports video.

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

2. Data preparation

2.1. Create a data cube

To begin, it is useful to prepare the data for the report by creating a data cube. 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.

Using the Adventure Works for SQL Server 2012 database as an example, the goal will be to create a data cube that joins the following three tables. This will produce all of the columns needed for the report.

  • [Production].[Product]
  • [Production].[ProductSubcategory]
  • [Production].[ProductInventory]

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

  • Create a new data cube from the main menu.
  • Drag the [Production].[Product] and [Production].[ProductSubcategory] 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].[ProductSubcategory] table to the join transform by dragging a connecting line.

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

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

Next, join with the third table as follows:

  • Drag the [Production].[ProductInventory] table from the data connector to the canvas.
  • Select the rightmost connection link, go to the toolbar, and insert a second join transform.
  • Connect the [Production].[ProductInventory] table to the second join transform by dragging a connecting line.
  • Double-click the second join transform to configure it. Perform the join on the ProductID column, and keep all of the columns from the left input plus the Quantity column from the right input.

2.2. Insert a filter transform

The product subcategory 'Mountain Bikes' in the AdventureWorks database has a lot of products in it. For the purposes of this walkthrough, follow the steps below to filter out these records which will make it easier to see the different levels of grouping in the final report.

Select the rightmost connection link. Go to the toolbar, click Insert Common, and then click Filter.

Double-click the filter transform to configure it.

In the Filter dialog, locate the second Name column and click its Edit icon on the right. This column corresponds to the name of the product subcategory.

In the Transform Element dialog, set the Element Operator dropdown to Does Not Equal. 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

2.3. Configure the Process Result

Click the Process Result transform to configure it.

You'll see a list of columns representing the output of the data cube. Note that the two Name columns are not duplicates. Click the Edit icon for these two columns and change their captions to ProductName and SubcategoryName.

Final data cube
Final data cube

3. Designing the report

3.1. Create a report and set its width

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. By default, the report is 1024 units wide which is too large for this example.

To change the width of the report, click on the white canvas area outside of any report regions. Go to the Properties window, click the Layout tab, and change the Width property to 700.

Change the report width
Change the report width

Tip
You can also use the resize icon at the bottom right corner to resize a report.

Here's what the initial report looks like in the Report Designer:

Initial report in the designer
Initial report in the designer

3.2. 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 Align 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 will adjust itself automatically because it is docked inside the region.

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

Next, click on the white part of the canvas outside of any report regions. Go to the toolbar, click Components, and then click Line. This will insert a line component on the canvas which will serve as a separator.

Drag the line and drop it onto the Report Header region. 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.3. Add a column for grouping

Go to the Explore window, locate your data cube and expand it. 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. Controls placed in the Group Body region will be repeated once for each ProductSubcategory ID value. These controls will be added later.

Add a column for grouping
Add a column for grouping

3.4. 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 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.5. 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. 

Go to the Explore window, locate your data cube, and then drag the SubcategoryName column to the Group Body region.

The column turns into a Data Label visualization control automatically. In Edit mode, the data label displays a subcategory name, but this value may change as you switch between View and Edit modes.

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

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

  • Set Background to LightGray.
  • Set Dock Location to Center. This will dock the label to the sides of the Group Body region.
  • Set Horizontal 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 with repetitions of subcategories.

Viewing the report so far
Viewing the report so far

3.6. 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. Go to the Explore window, locate your data cube, and then drag the ProductID column to the child grouping region (i.e., the second drop a column or hierarchy here region)

Drag ProductID column to child grouping region
Drag ProductID column to child grouping region

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

There's nothing to put in the Group Header and Group Footer for the child group, so resize those regions very small.

3.7. Add controls to the Child Group Body

Controls in the Child Group Body region are repeated once for every ProductID value.

Go to the Explore window, expand the data cube, and then drag the ProductName, Quantity, and ListPrice columns to the Child Group Body region in turn. They each appear as data labels automatically.

For the ProductName data label, set its Horizontal Text Alignment to Left

Add columns to Child Group Body region
Add columns to Child Group Body region

You'll notice the Quantity data label is shown as a floating point number. Change the number format by right clicking on the data label to open the Data Binding Panel. Edit the Quantity measure, and in the Configure Metric Set Element dialog, set the Format to N0.

Also right click on the ListPrice data label to open its Data Binding Panel. Edit the ListPrice measure, and in the Configure Metric Set Element dialog, set Symbol to $ (i.e., a dollar sign).

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

3.8. 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 are free to 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