[{"id":"a20f26ac-cd31-4e4d-9454-1e035b0f1587","tags":[{"product":null,"links":null,"id":"fe610228-9cf6-417f-813a-dcbfd25406c3","name":"upd","color":"#7e678a","productId":"d699a6af-e150-4da3-ab30-25fd97934601"}]},{"id":"3604b8ce-1da0-413f-8274-7df0f7173573","tags":[{"product":null,"links":null,"id":"fe610228-9cf6-417f-813a-dcbfd25406c3","name":"upd","color":"#7e678a","productId":"d699a6af-e150-4da3-ab30-25fd97934601"}]},{"id":"7fce7040-1bb1-4a61-9fb7-2e19d569de50","tags":[{"product":null,"links":null,"id":"fe610228-9cf6-417f-813a-dcbfd25406c3","name":"upd","color":"#7e678a","productId":"d699a6af-e150-4da3-ab30-25fd97934601"}]},{"id":"0eb72dd3-8f8f-4039-93a8-500bbde2301e","tags":[{"product":null,"links":null,"id":"fe610228-9cf6-417f-813a-dcbfd25406c3","name":"upd","color":"#7e678a","productId":"d699a6af-e150-4da3-ab30-25fd97934601"}]},{"id":"ac046cee-82b9-4edf-a642-ae6d7cb21ebf","tags":[{"product":null,"links":null,"id":"fe610228-9cf6-417f-813a-dcbfd25406c3","name":"upd","color":"#7e678a","productId":"d699a6af-e150-4da3-ab30-25fd97934601"}]},{"id":"75b66fd4-b740-443d-bc6e-1782048b60a6","tags":[{"product":null,"links":null,"id":"fe610228-9cf6-417f-813a-dcbfd25406c3","name":"upd","color":"#7e678a","productId":"d699a6af-e150-4da3-ab30-25fd97934601"}]},{"id":"dde44832-bb5c-45f3-8ebf-3badbc613cd9","tags":[{"product":null,"links":null,"id":"fe610228-9cf6-417f-813a-dcbfd25406c3","name":"upd","color":"#7e678a","productId":"d699a6af-e150-4da3-ab30-25fd97934601"}]},{"id":"43de3664-261b-4cf9-9945-fb519251f4c7","tags":[{"product":null,"links":null,"id":"fe610228-9cf6-417f-813a-dcbfd25406c3","name":"upd","color":"#7e678a","productId":"d699a6af-e150-4da3-ab30-25fd97934601"}]},{"id":"2f064d57-b088-4bc5-a628-4e0d7e1a0c65","tags":[{"product":null,"links":null,"id":"fe610228-9cf6-417f-813a-dcbfd25406c3","name":"upd","color":"#7e678a","productId":"d699a6af-e150-4da3-ab30-25fd97934601"}]},{"id":"f290406b-ced1-4c52-abad-979160a79f6b","tags":[{"product":null,"links":null,"id":"fe610228-9cf6-417f-813a-dcbfd25406c3","name":"upd","color":"#7e678a","productId":"d699a6af-e150-4da3-ab30-25fd97934601"}]},{"id":"1864834b-08ce-43f1-891b-c3cca6e04e95","tags":[{"product":null,"links":null,"id":"4d7b6a40-ab32-4c71-a381-58f3ffd2653e","name":"new","color":"#ed7422","productId":"d699a6af-e150-4da3-ab30-25fd97934601"}]},{"id":"f96cfec1-420c-4ca7-ab0a-cab482731073","tags":[{"product":null,"links":null,"id":"4d7b6a40-ab32-4c71-a381-58f3ffd2653e","name":"new","color":"#ed7422","productId":"d699a6af-e150-4da3-ab30-25fd97934601"}]},{"id":"3f2ea860-c764-43d8-ae3f-6e3b0314ce67","tags":[{"product":null,"links":null,"id":"fe610228-9cf6-417f-813a-dcbfd25406c3","name":"upd","color":"#7e678a","productId":"d699a6af-e150-4da3-ab30-25fd97934601"}]},{"id":"81eb70e8-f770-4384-9167-bc7c755ad052","tags":[{"product":null,"links":null,"id":"4d7b6a40-ab32-4c71-a381-58f3ffd2653e","name":"new","color":"#ed7422","productId":"d699a6af-e150-4da3-ab30-25fd97934601"}]},{"id":"d0dff477-9f56-483a-a9fc-cab648905b41","tags":[{"product":null,"links":null,"id":"4d7b6a40-ab32-4c71-a381-58f3ffd2653e","name":"new","color":"#ed7422","productId":"d699a6af-e150-4da3-ab30-25fd97934601"}]},{"id":"793a62d7-460d-4699-9304-edb2941268ac","tags":[{"product":null,"links":null,"id":"fe610228-9cf6-417f-813a-dcbfd25406c3","name":"upd","color":"#7e678a","productId":"d699a6af-e150-4da3-ab30-25fd97934601"}]},{"id":"2d6ebfad-f7d1-4cc5-8ba1-fdace3472056","tags":[{"product":null,"links":null,"id":"fe610228-9cf6-417f-813a-dcbfd25406c3","name":"upd","color":"#7e678a","productId":"d699a6af-e150-4da3-ab30-25fd97934601"}]},{"id":"c8c7b5d5-f5bd-4b68-a12f-3aae9804a844","tags":[{"product":null,"links":null,"id":"fe610228-9cf6-417f-813a-dcbfd25406c3","name":"upd","color":"#7e678a","productId":"d699a6af-e150-4da3-ab30-25fd97934601"}]},{"id":"577a09da-dea1-42db-812f-4eeaa9274ece","tags":[{"product":null,"links":null,"id":"fe610228-9cf6-417f-813a-dcbfd25406c3","name":"upd","color":"#7e678a","productId":"d699a6af-e150-4da3-ab30-25fd97934601"}]},{"id":"8ddd80d5-886f-41b8-8e3f-f73aa8146c8d","tags":[{"product":null,"links":null,"id":"fe610228-9cf6-417f-813a-dcbfd25406c3","name":"upd","color":"#7e678a","productId":"d699a6af-e150-4da3-ab30-25fd97934601"}]},{"id":"e0909540-8bde-4587-bb4d-02445746db2a","tags":[{"product":null,"links":null,"id":"fe610228-9cf6-417f-813a-dcbfd25406c3","name":"upd","color":"#7e678a","productId":"d699a6af-e150-4da3-ab30-25fd97934601"}]},{"id":"464b8e03-462d-4aed-8818-05d32ec3bf00","tags":[{"product":null,"links":null,"id":"fe610228-9cf6-417f-813a-dcbfd25406c3","name":"upd","color":"#7e678a","productId":"d699a6af-e150-4da3-ab30-25fd97934601"}]},{"id":"f235557b-9781-471f-bf03-38bae6e3e953","tags":[{"product":null,"links":null,"id":"fe610228-9cf6-417f-813a-dcbfd25406c3","name":"upd","color":"#7e678a","productId":"d699a6af-e150-4da3-ab30-25fd97934601"}]},{"id":"fd5d021d-0210-4d74-ba42-1a39ef14a385","tags":[{"product":null,"links":null,"id":"fe610228-9cf6-417f-813a-dcbfd25406c3","name":"upd","color":"#7e678a","productId":"d699a6af-e150-4da3-ab30-25fd97934601"}]},{"id":"548568fe-dbd1-4da5-a489-d9b3edf75329","tags":[{"product":null,"links":null,"id":"fe610228-9cf6-417f-813a-dcbfd25406c3","name":"upd","color":"#7e678a","productId":"d699a6af-e150-4da3-ab30-25fd97934601"}]},{"id":"a0c823ed-e22b-493b-aded-4d201c0fa480","tags":[{"product":null,"links":null,"id":"fe610228-9cf6-417f-813a-dcbfd25406c3","name":"upd","color":"#7e678a","productId":"d699a6af-e150-4da3-ab30-25fd97934601"}]},{"id":"d19bc9cd-ff53-4b0d-ab7e-a68c1497c4c6","tags":[{"product":null,"links":null,"id":"4d7b6a40-ab32-4c71-a381-58f3ffd2653e","name":"new","color":"#ed7422","productId":"d699a6af-e150-4da3-ab30-25fd97934601"}]},{"id":"94759fce-692f-4779-b5f3-fce5c58f242c","tags":[{"product":null,"links":null,"id":"4d7b6a40-ab32-4c71-a381-58f3ffd2653e","name":"new","color":"#ed7422","productId":"d699a6af-e150-4da3-ab30-25fd97934601"}]},{"id":"27658163-a479-432f-a192-dbed286beb3e","tags":[{"product":null,"links":null,"id":"4d7b6a40-ab32-4c71-a381-58f3ffd2653e","name":"new","color":"#ed7422","productId":"d699a6af-e150-4da3-ab30-25fd97934601"}]}]
A drill-through reporting allows a reader to navigate back and forth through a sequence of reports by clicking parametrized links. Such navigation usually consists of the main report that displays items summary and a subreport that provides details for a specific item. In ActiveReportsJS, a report author creates a drill-through link by configuring the Jump to Report action of a report item or its part.
In this tutorial, we build the Drillthrough Sales Chart from the ground up, providing experience with the typical report designing process. By the end, you will be able to do the following:
Create a new report
Bind the report to the OData API with multiple endpoints
Use the OData $select and $filter query options to reduce the volume of retrieved data
Build line chart and stacked column chart
Use the Report explorer to navigate through the report hierarchy
Use the Lookup function to display the data from multiple data sets
Setup the parametrized drill through navigation between the main report and the subreport
Use the Galley mode
and History
functions of the report viewer
Upon completion of all tutorial steps, the final report will look like this: live demo.
The following content assumes that you have the Standalone Report Designer running. Visit the tutorial for detailed information. A stable internet connection is also required to access the Demo Data Hub.
In the standalone report designer, click the File menu and select the Continuous Page Layout template for a newly created report. Click anywhere on the gray area around the report layout to load the report properties in the Properties panel. In the Margins
section, set the Style
property to Narrow
to change the report page margins size.
Expand to watch creating a new report in action
Data binding establishes a connection between a report and the data it displays. In this tutorial, we use the MESCIUS Demo OData API that contains the sales data for a fictitious company called Contoso that is a manufacturing, sales, and support organization with more than 100,000 products.
You can think of the Contoso OData API as the "database" that you can connect to using its root URL https://demodata.mescius.io/contoso/odata/v1
, and that has multiple "tables" which presented with endpoints such as /DimCustomers, /DimStores, and /DimProducts. You can create a Report DataSource that establishes the connection to such a "database" using the following steps.
Open the Data panel of the property inspector and click the Add
button.
In the Data Source editor dialog, type Contoso
in the NAME field and https://demodata.mescius.io/contoso/odata/v1
in the ENDPOINT field.
Click the Save Changes
button
Data Sets represent one or more endpoints of the Data Source. For the Drillthrough Sales Report, we need to have access to the data from the /DimChannels and /FactSales endpoints of the Contoso Data Source that we added in the previous section.
Use the following steps to add the Channels
data set:
Click the +
icon near Contoso
in the Data panel.
In the Data Set Editor dialog, type Channels
in the NAME field, /DimChannels
in the Uri/Path field, and $.value.*
in the Json Path
field.
By default, each Channel
record includes seven fields, but we don't need them all. Therefore we limit the retrieved fields by applying the OData $select query option via a query parameter.
Click the +
icon on the right side of the Parameters
section.
Type $select
in the Parameter
field of the newly added Parameter
item.
Type ChannelKey, ChannelName
in the Value
field of the same Parameter
item.
Click the Validate
button.
Ensure that the DataBase Fields
section displays [2 items]
text.
Click the Save Changes
button.
Expand to watch adding the Channels data set in action
Next, add the Sales
data set with the same approach:
Click the +
icon near Contoso
in the Data panel.
In the Data Set Editor dialog, type Sales
in the NAME field, /FactSales
in the Uri/Path field, and $.value.*
in the Json Path field.
By default, each FactSales
record includes 19 fields, but we don't need them all. Therefore we limit the retrieved fields by applying the OData $select query option via the query parameter.
Click the +
icon on the right side of the Parameters
section.
Type $select
in the Parameter
field of the newly added Parameter
item.
Type DateKey, ChannelKey, SalesAmount
in the Value
field of the same Parameter
item.
Click the Validate
button.
Ensure that the DataBase Fields
section displays [3 items]
text.
Click the Save Changes
button.
The report template that we created is the starting point of both the main report and the subreport. Hence, it's convenient to save this template to two files:
In the standalone report designer, open the File menu, then click the Save As
item on the sidebar and save the report under DrillThroughMainReport.rdlx-json
name in the directory of your choice.
Repeat the same operation to save the report template under DrillThroughSubReport.rdlx-json
in the same directory.
Expand to watch saving report templates in action
A drill-through link between the main report and a subreport has two properties:
The subreport name
The collection of subreport parameter values
The subreport accepts these parameter values and filters the visualized data accordingly.
In this step, we add two parameters that will filter the data displayed in DrillThroughSubReport.rdlx-json
report:
In the Data panel click the + Add
button in the Parameters
Section.
Type SalesYear
in the Name
field.
Select Integer
in the Data Type
drop-down.
Click the Hidden
switch to prevent the parameter input from being shown to a report reader.
In the Default Value
section, select the Non-queried
tab, expand the Values
collection, click the Add Item
button, and type 2007
in the newly added field.
Expand to watch adding the SalesYear parameter in action
Using the same approach, add the ChannelKey
parameter:
Click the Left Arrow
icon near the EDIT PARAMETER
header.
In the Data panel, click the + Add
button in the Parameters Section.
Type ChannelKey
in the Name
field.
Select Integer
in the Data Type
drop-down.
Click the Hidden
switch to prevent the parameter input from being shown to a report reader.
In the Default Value
section, select the Non-queried
tab, expand the Values
collection, click the Add Item
button and type 2
in the newly added field.
The DrillThroughSubReport.rdlx-json
should display monthly sales for the given sales year and channel. Thus, we should modify the Sales
data set by adding the $filter option in the query parameters.
Click the Left Arrow
icon near the EDIT PARAMETER
header.
Click the Pencil
icon near Sales
in the Data panel.
Click the +
icon on the right side of the Parameters
section.
Type $filter
in the Parameter
field of the newly added Parameter
item.
Type ChannelKey+eq+{@ChannelKey}+and+Year(DateKey)+eq+{@SalesYear}
in the Value
field of the same Parameter
item.
Click the Validate
button.
In the Parameter Prompt that appears, type 2
in the @ChannelKey
field, 2007
in the @SalesYear
field and click the Save & Run
button.
Ensure that the DataBase Fields
section displays [3 items]
text.
To display monthly sales data, we use the Line Chart:
Expand the toolbox using the Hamburger
menu located on the left side of the toolbar.
Drag and drop the Chart
item from the toolbox to the report page area's top-left corner.
Switch to the Properties panel in the Property inspector.
Select the Line
item in the Plot Template
drop-down menu. The property panel automatically displays properties of the Chart Plot.
Expand to watch adding the Line Chart in action
The plot properties define the visualization of actual data within a chart. Follow the next steps to configure the line chart plot.
In the Properties panel, expand the Values
editor using the List
icon on the right side.
Click the Add Item
button and open the newly added value editor using the gear
icon on the right side.
Expand the Values
list using the List
icon on the right side.
Click the gray box on the right side of the default value and choose the SalesAmount
element in the drop-down menu.
Choose Sum
in the Aggregate
drop-down.
Expand to watch configuring the plot value in action
In the same manner, configure the plot category:
Return to the Plot properties using the Left Arrow
icon near the Value Properties
header.
Expand the Category
editor in the Encodings
section using the List
icon on the right side.
Type {MonthName(Month(DateKey))}
in the default category field.
Type {Month(DateKey)}
in the Category Sort Expression
field.
Select Ascending
in the Category Sort Direction
drop-down.
Thus, the plot will display the sum of sales amounts for each month extracted from many DateKey
values. Months will appear in the natural order, from January to December.
Next, configure the line appearance:
Scroll down to the Style
section and set the Line Color
to Black
and the Line Width
to 2pt
.
In the Symbols
section, choose Dot
for the Symbol Shape
property, White
for the Background Color
property, and Solid
for Style
property.
You can now preview the report output and see how the plot we just configured displays monthly sales using dots connected with the line.
The report explorer provides a convenient way to navigate through report parts and load their properties for modification.
Click the Explorer
icon on the toolbox and pin the Explorer panel. You can see the report hierarchy, and if you select a node, its properties appear in the properties panel.
Click the Chart
item and set the Width
property to 7.5in
and the Height
property to 6in
in the property panel.
Expand to watch setting chart dimensions in action
Similarly, set the properties of the X Axis - [Plot 1]
, and Y Axis - [Plot 1]
chart sub-items according to tables below. Check the Chart Axes documentation for the detailed explanation of these properties.
X Axis - [Plot 1]
Property | Value |
---|---|
Title | Empty string(Reset the default value) |
Line Visible | False |
Labels Color | #1a1a1a |
Labels Angle | -45 |
Major Grid/Show Grid Lines | True |
Major Grid/Width | 0.25pt |
Major Grid/Color | #ccc |
Y Axis - [Plot 1]
Property | Value |
---|---|
Title | Empty string(Reset the default value) |
Line Visible | False |
Labels Color | #1a1a1a |
Labels Format | c2(select Currency in the drop-down) |
Major Grid/Show Grid Lines | True |
Major Grid/Width | 0.25pt |
Major Grid/Color | Gainsboro |
Major Grid/ Major Interval | 20000 |
The chart might look distorted at design-time, but you can preview the report output and notice that it looks sharp.
The chart header should display the information about the data's sales channel and year. These values are available as the report parameters values so that we could use the Monthly {@ChannelKey} Sales in {@SalesYear}
Expression, but the ChannelKey
is the integer value, and we want to display the actual sales channel name. We will use the Lookup function to resolve the Channel Name by its Key.
Click the Chart1 -> Header
item in the report explorer.
Set the Caption
property to Monthly {Lookup(@ChannelKey, ChannelKey, ChannelName, "Channels")} Sales in {@SalesYear}
expression. The Lookup
function takes the value of the first argument - the ChannelKey
parameter value - finds the row with the same ChannelKey
in the Channels
data set and returns the corresponding ChannelName
.
Also, set the Text Color
of the chart header to #3da7a8
, the Font Size
to 22pt
, and then the Font Weight
to Bold
.
You can preview the report output and notice that the chart header displays the Monthly Online Sales in 2007
header.
We finished configuring the drill-through subreport. Go back to the report design, and save the progress using the Save button of the standalone report designer.
Next, open the previously saved DrillThroughMainReport.rdlx-json
using the Open
button of the File menu.
The main report of the drill-through navigation uses the stacked column chart to display the annual sales data:
Expand the toolbox using the Hamburger
menu located on the left side of the toolbar.
Drag and drop the Chart
item from the toolbox to the report page area's top-left corner.
Switch to the Properties panel in the Property inspector.
Select the Column Stacked
in the Plot Template
drop-down menu. The property panel automatically displays chart plot properties.
In the Properties panel, expand the Values
editor using the List
icon on the right side.
Click the Add Item
button and open the newly added value editor using the gear
icon on the right side.
Expand the Values
list using the List
icon on the right side.
Click the gray box on the right side of the default value and choose the SalesAmount
element in the drop-down menu.
Choose Sum
in the Aggregate
drop-down.
Return to the Plot properties using the Left Arrow
icon near the Value Properties
header.
Expand the Category
editor in the Encodings
section using the List
icon on the right side.
Type {Year(DateKey)}
in the default category field.
Type {Year(DateKey)}
in the Category Sort Expression
field.
Select Ascending
in the Category Sort Direction
drop-down.
Set the following properties for the Chart1
item using the Report Explorer as it was explained in the Configuring Line Chart appearance
section above:
Property | Value |
---|---|
Width | 7.5in |
Height | 7.5in |
For the X Axis - [Plot 1]
item:
Property | Value |
---|---|
Title | Empty string(Reset the default value) |
Line Visible | False |
For the Y Axis - [Plot 1]
item:
Property | Value |
---|---|
Title | Empty string(Reset the default value) |
Line Visible | False |
Labels Color | #3c3c3c |
Labels Format | c2(select Currency in the drop-down) |
Major Grid/Show Grid Lines | True |
Major Grid/Width | 0.25pt |
Major Grid/Style | Dashed |
Major Grid/Color | #ccc |
Major Grid/ Major Interval | 50000 |
And for the Header
item:
Property | Value |
---|---|
Caption | Annual Sales by Channel |
Font Size | 20pt |
Font Weight | Bold |
You can now preview the report output and notice that it displays the sum of sales for each year, but it also should split each column among sales channels and show the legend. To achieve that, we use the Details Encoding, Colors Encoding, and the Legend configuration :
Go back to the report design select the Plot
item in the Report Explorer.
Expand the Details
editor in the Encodings
section using the List
icon on the right side.
Click the Add Item
button and open the newly added value editor using the gear
icon on the right side.
Expand the Values
list using the List
icon on the right side.
Click the gray box on the right side of the default value and choose the ChannelKey
element in the drop-down menu.
Return to the Plot properties using the Left Arrow
icon near the Details Properties
header.
Expand the Color
editor in the Encodings
section using the List
icon on the right side.
Click the Add Item
button and type {Lookup(ChannelKey, ChannelKey, ChannelName, "Channels")}
in the newly added item field. This usage of the Lookup
function is similar to the one for the Line Chart header described earlier.
Select the Legend - Color
item in the Report Explorer.
Set the Orientation
property to Horizontal
and the Position
property to Top
in the Layout
section.
You can now preview the report output and notice that each column splits into four colored parts that correspond to the sales channels, and the legend helps to match the color with the channel name. To improve the data visualization, we configure the Plot Labels and Tooltips appearance:
Go back to the report design select the Plot
item in the Report Explorer.
Select the Color Field Value
item in the Tooltip Template
drop-down in the Common
section.
Click the gray box on the right side of the Template
editor in the Label Text
section of the Properties panel and select the Expression...
item. The Expression Editor will appear. Type {valueField.value:C2}
in the expression text area and click the Save
button.
Set the Label Text/Position
property to Center
Expand to watch configuring plot tooltips and labels in action
You can now preview the report output and notice that the chart columns display the sales amount for each channel and year, and the channel name tooltip shows up when the mouse is over a column.
Finally, we configure the link between the main report and subreport for the drill-through navigation.
Go back to the report design select the Plot
item in the Report Explorer.
Select the Jump to Report
in the Type
editor of the Action
section.
In the Jump to Report
editor select the DrillThroughSubReport.rdlx-json
item.
Expand the Parameters
editor using the List
icon on the right side.
Click the Add Item
button and open the newly added value editor using the gear
icon on the right side.
Type ChannelKey
in the Parameter Name
field and select the ChannelKey
item in the Value
drop-down.
Return to the Plot properties using the Left Arrow
icon near the Parameter Properties
header.
Similarly add another parameter with SalesYear
name and {Year(DateKey)}
value.
You can now preview the report output and click any column and notice that the report viewer opens the subreport with the detailed data on the selected sales year and channel as per drill through configuration. You can go back using the Go Back in History
report viewer toolbar button. You can also click the rightmost button, called Galley Mode
. It switches the viewer mode to display the unpaginated output that is suitable for reading drill-through reports.
The final version of report is available at the demo web-site.