Designing an inventory report
- Data preparation
- Designing the report
- See also
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.
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.
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'.
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.
3. Designing the report
3.1. Create a report and set its width
Go to the main menu, click New, and then click Report.
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.
Here's what the initial report looks like in the Report 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.
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.
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.
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.
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.
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.
Switch to View mode to see the report so far with repetitions of subcategories.
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)
This will automatically insert a Group Header, Group Body, and Group Footer region for the child group.
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.
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.
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
- Report basics and concepts
- Building a scorecard
- Using a data cube to join tables
- Data cube storage types
- Video: Creating and Viewing Reports
- Video: Introduction to Scorecards