# Get Started With Drill Through Reports

Learn how you can create a drill-through report using ActiveReportsJS.

## Content

### Introduction

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](/activereportsjs/docs/v6.1/ReportAuthorGuide/Interactivity#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](https://www.odata.org/getting-started/basic-tutorial/#select) and [$filter](https://www.odata.org/getting-started/basic-tutorial/#filter) query options to reduce the volume of retrieved data
* Build [line chart](/activereportsjs/docs/v6.1/ReportAuthorGuide/Report-Items/Data-Regions/Chart/plot/line-chart) and [stacked column chart](/activereportsjs/docs/v6.1/ReportAuthorGuide/Report-Items/Data-Regions/Chart/plot/column#stacked-column-and-bar)
* Use the [Report explorer](/activereportsjs/docs/v6.1/ReportAuthorGuide/Report-Designer-Interface#report-explorer) to navigate through the report hierarchy
* Use the [Lookup](/activereportsjs/docs/v6.1/ReportAuthorGuide/Expressions/Functions/miscellaneous#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](/activereportsjs/docs/v6.1/ReportAuthorGuide/Report-Viewer-Interface)

Upon completion of all tutorial steps, the final report will look like this: [live demo](/activereportsjs/demos/Reports/GetStartedWithDrillThroughReports/purejs).

### Prerequisites

The following content assumes that you have the Standalone Report Designer running. Visit the [tutorial](/activereportsjs/docs/v6.1/ReportAuthorGuide/QuickStart/Get-Started-With-Designer-App) for detailed information. A stable internet connection is also required to access the [Demo Data Hub](https://demodata.mescius.io).

### Creating a New Report

In the standalone report designer, click the [File menu](/activereportsjs/docs/v6.1/ReportAuthorGuide/QuickStart/Get-Started-With-Designer-App#file-menu) and select the [Continuous Page Layout](/activereportsjs/docs/v6.1/ReportAuthorGuide/report-configuration/Report-Layouts#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](/activereportsjs/docs/v6.1/ReportAuthorGuide/Report-Designer-Interface#properties-panel). In the `Margins` section, set the `Style` property to `Narrow` to change the [report page](/activereportsjs/docs/v6.1/ReportAuthorGuide/report-configuration/layout-properties#page-settings) margins size.
DOC-DETAILS-TAG-OPEN
DOC-SUMMARY-TAG-OPEN
Expand to watch creating a new report in action
DOC-SUMMARY-TAG-CLOSE
![CreatingNewReport](https://cdn.mescius.io/document-site-files/images/fcb03d52-fb54-40c3-8356-825c2a095d5b/CreatingNewReport.7e7d22.gif)
DOC-DETAILS-TAG-CLOSE

### Data Binding

[Data binding](/activereportsjs/docs/v6.1/ReportAuthorGuide/Databinding) establishes a connection between a report and the data it displays. In this tutorial, we use the [MESCIUS Demo OData API](https://demodata.mescius.io/swagger/index.html?urls.primaryName=OData%20Contoso) 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.

### Adding Data Source

You can think of the [Contoso OData API](https://demodata.mescius.io/swagger/index.html?urls.primaryName=OData%20Contoso) 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](https://demodata.mescius.io/contoso/odata/v1/DimCustomers), [/DimStores](https://demodata.mescius.io/contoso/odata/v1/DimStores), and [/DimProducts](https://demodata.mescius.io/contoso/odata/v1/DimProducts). You can create a Report DataSource that establishes the connection to such a "database" using the following steps.

* Open the [Data panel](/activereportsjs/docs/v6.1/ReportAuthorGuide/Report-Designer-Interface#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

![image](https://cdn.mescius.io/document-site-files/images/2c0943df-d937-4f5c-966c-3390ae8bbb34/image.4dbc20.png)

### Adding Data Sets

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](https://demodata.mescius.io/contoso/odata/v1/DimChannels) and [/FactSales](https://demodata.mescius.io/contoso/odata/v1/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](https://www.odata.org/getting-started/basic-tutorial/#select) 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.

DOC-DETAILS-TAG-OPEN
DOC-SUMMARY-TAG-OPEN
Expand to watch adding the Channels data set in action
DOC-SUMMARY-TAG-CLOSE
![](https://cdn.mescius.io/document-site-files/images/e637becb-8cbc-4561-bac1-84462097b057/reportauthor-guide/GetStartedWithDrillThroughReports/AddingDataSet.gif)
DOC-DETAILS-TAG-CLOSE
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](https://www.odata.org/getting-started/basic-tutorial/#select) 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.

### Saving report templates

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](/activereportsjs/docs/v6.1/ReportAuthorGuide/QuickStart/Get-Started-With-Designer-App#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.

DOC-DETAILS-TAG-OPEN
DOC-SUMMARY-TAG-OPEN
Expand to watch saving report templates in action
DOC-SUMMARY-TAG-CLOSE
![SavingReportTemplates](https://cdn.mescius.io/document-site-files/images/fcb03d52-fb54-40c3-8356-825c2a095d5b/SavingReportTemplates.8c98de.gif)
DOC-DETAILS-TAG-CLOSE

### Adding Subreport Parameters

A drill-through link between the main report and a subreport has two properties:

* The subreport name
* The collection of subreport [parameter](/activereportsjs/docs/v6.1/ReportAuthorGuide/Report-Parameters) 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.

DOC-DETAILS-TAG-OPEN
DOC-SUMMARY-TAG-OPEN
Expand to watch adding the SalesYear parameter in action
DOC-SUMMARY-TAG-CLOSE
![](https://cdn.mescius.io/document-site-files/images/e637becb-8cbc-4561-bac1-84462097b057/reportauthor-guide/GetStartedWithDrillThroughReports/AddingParameter.gif)
DOC-DETAILS-TAG-CLOSE
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.

### Modifying the Subreport DataSet

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](https://www.odata.org/getting-started/basic-tutorial/#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.

### Adding Line Chart

To display monthly sales data, we use the [Line Chart](/activereportsjs/docs/v6.1/ReportAuthorGuide/Report-Items/Data-Regions/Chart/plot/line-chart):

* Expand the [toolbox](/activereportsjs/docs/v6.1/ReportAuthorGuide/Report-Designer-Interface#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](/activereportsjs/docs/v6.1/ReportAuthorGuide/Report-Designer-Interface#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](/activereportsjs/docs/v6.1/ReportAuthorGuide/Report-Items/Data-Regions/Chart/plot).

DOC-DETAILS-TAG-OPEN
DOC-SUMMARY-TAG-OPEN
Expand to watch adding the Line Chart in action
DOC-SUMMARY-TAG-CLOSE
![AddingLineChart](https://cdn.mescius.io/document-site-files/images/fcb03d52-fb54-40c3-8356-825c2a095d5b/AddingLineChart.026200.gif)
DOC-DETAILS-TAG-CLOSE

### Configuring Line Chart Plot

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.

DOC-DETAILS-TAG-OPEN
DOC-SUMMARY-TAG-OPEN
Expand to watch configuring the plot value in action
DOC-SUMMARY-TAG-CLOSE
![](https://cdn.mescius.io/document-site-files/images/e637becb-8cbc-4561-bac1-84462097b057/reportauthor-guide/GetStartedWithDrillThroughReports/ConfiguringLineChartPlot.gif)
DOC-DETAILS-TAG-CLOSE
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](/activereportsjs/docs/v6.1/ReportAuthorGuide/QuickStart/Get-Started-With-Designer-App#preview-button) the report output and see how the plot we just configured displays monthly sales using dots connected with the line.

### Configuring Line Chart appearance

The [report explorer](/activereportsjs/docs/v6.1/ReportAuthorGuide/Report-Designer-Interface#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.
DOC-DETAILS-TAG-OPEN
DOC-SUMMARY-TAG-OPEN
Expand to watch setting chart dimensions in action
DOC-SUMMARY-TAG-CLOSE
![SettingChartDimensions](https://cdn.mescius.io/document-site-files/images/fcb03d52-fb54-40c3-8356-825c2a095d5b/SettingChartDimensions.d354f1.gif)
DOC-DETAILS-TAG-CLOSE
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](/activereportsjs/docs/v6.1/ReportAuthorGuide/Report-Items/Data-Regions/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.

### Configuring Line Chart Header

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](/activereportsjs/docs/v6.1/ReportAuthorGuide/Expressions), but the `ChannelKey` is the integer value, and we want to display the actual sales channel name. We will use the [Lookup](/activereportsjs/docs/v6.1/ReportAuthorGuide/Expressions/Functions/miscellaneous#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](/activereportsjs/docs/v6.1/ReportAuthorGuide/QuickStart/Get-Started-With-Designer-App#open-save-and-save-as-buttons) of the standalone report designer.

Next, open the previously saved `DrillThroughMainReport.rdlx-json` using the `Open` button of the [File menu](/activereportsjs/docs/v6.1/ReportAuthorGuide/QuickStart/Get-Started-With-Designer-App#file-menu).

### Adding Stacked Column Chart and Configuring its Plot, Appearance, and Header

The main report of the drill-through navigation uses the [stacked column chart](/activereportsjs/docs/v6.1/ReportAuthorGuide/Report-Items/Data-Regions/Chart/plot/column#stacked-column-and-bar) to display the annual sales data:

* Expand the [toolbox](/activereportsjs/docs/v6.1/ReportAuthorGuide/Report-Designer-Interface#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 |

### Configuring Stacked Column Chart Details

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](/activereportsjs/docs/v6.1/ReportAuthorGuide/Report-Items/Data-Regions/Chart/plot/column#details-encoding), [Colors Encoding](/activereportsjs/docs/v6.1/ReportAuthorGuide/Report-Items/Data-Regions/Chart/plot/column#colors-encoding), and the [Legend configuration](/activereportsjs/docs/v6.1/ReportAuthorGuide/Report-Items/Data-Regions/Chart/legend) :

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

### Displaying Plot Labels and Tooltips

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](/activereportsjs/docs/v6.1/ReportAuthorGuide/Report-Items/Data-Regions/Chart/plot/column#tooltip-and-labels-template) 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](/activereportsjs/docs/v6.1/ReportAuthorGuide/Report-Designer-Interface#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`

DOC-DETAILS-TAG-OPEN
DOC-SUMMARY-TAG-OPEN
Expand to watch configuring plot tooltips and labels in action
DOC-SUMMARY-TAG-CLOSE
![ConfiguringTemplatesAndLabels](https://cdn.mescius.io/document-site-files/images/fcb03d52-fb54-40c3-8356-825c2a095d5b/ConfiguringTemplatesAndLabels.fee4ad.gif)
DOC-DETAILS-TAG-CLOSE
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.

### Configuring Drill-Through link

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](/activereportsjs/docs/v6.1/ReportAuthorGuide/Report-Viewer-Interface#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](/activereportsjs/demos/Reports/GetStartedWithDrillThroughReports/purejs).