# Get Started With Summary Reports

Learn how you can create a summary report with a pivot table using ActiveReportsJS.

## Content

### Introduction

A summary report is a method of reducing a complete, often large, set of raw data into smaller views that help yield useful information. Usually, a summary report condenses many data records into several categories and displays one or more summary values for each of these categories. A summary report can also group, sort, and filter the data based on pre-defined conditions or user input. In ActiveReportsJS you can build such a report using [Tablix](/activereportsjs/docs/v6.1/ReportAuthorGuide/Report-Items/Data-Regions/Tablix) and/or [Chart](/activereportsjs/docs/v6.1/ReportAuthorGuide/Report-Items/Data-Regions/Chart) data regions. `Tablix` is a combination of words `Table` and `Matrix`. You can think of it as an advanced Pivot Table, similar to Excel one.

In this tutorial, we build a Tablix-based Sales Report 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
* Create the report parameter for interaction with a report reader
* Use the `Galley mode` of the [report viewer](/activereportsjs/docs/v6.1/ReportAuthorGuide/Report-Viewer-Interface)
* 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
* Create data set [calculated fields](/activereportsjs/docs/v6.1/ReportAuthorGuide/Databinding#data-set-fields)
* Create a [tablix data region](/activereportsjs/docs/v6.1/ReportAuthorGuide/Report-Items/Data-Regions/Tablix)
* Configure the tablix using the [Tablix Wizard](/activereportsjs/docs/v6.1/ReportAuthorGuide/Report-Items/Data-Regions/Tablix#tablix-wizard)
* Format [rows and columns](/activereportsjs/docs/v6.1/ReportAuthorGuide/Report-Items/Data-Regions/Tablix#tablix-rows) of the tablix data region
* Use the [Lookup](/activereportsjs/docs/v6.1/ReportAuthorGuide/Expressions/Functions/miscellaneous#lookup) function to display the data from multiple data sets
* Sort tablix columns

Upon completion of all tutorial steps, the final report will look like this: [live demo](/activereportsjs/demos/Reports/GetStartedWithSummaryReports/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.52020a.gif)
DOC-DETAILS-TAG-CLOSE

### Data Binding

[Data Binding](/activereportsjs/docs/v6.1/ReportAuthorGuide/Databinding) establishes a connection between the 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.85e126.png)

### Adding Supplemental Data Sets

Data Sets represent one or more endpoints of the Data Source. For the Sales Report, we need to have access to the data from the [/DimStores](https://demodata.mescius.io/contoso/odata/v1/DimStores), [/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. The first two data sets are supplemental. We will use them to list the available values for a [report parameter](/activereportsjs/docs/v6.1/ReportAuthorGuide/Report-Parameters) and display store names and sales channel names. We will use the `FactSales` data set to build the summary data.

Use the following steps to add the `Stores` data set:

* Click the `+` icon near `Contoso` in the Data panel.
* In the Data Set Editor dialog, type `Stores` in the NAME field, `/DimStores` in the Uri/Path field, and `$.value.*` in the `Json Path` field.
* By default, each `Store` record includes 23 fields, but we don't need them all. Therefore we limit the retrieved fields by employing 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 `StoreKey, StoreName` 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 Stores data set in action
DOC-SUMMARY-TAG-CLOSE
![](https://cdn.mescius.io/document-site-files/images/e637becb-8cbc-4561-bac1-84462097b057/reportauthor-guide/GetStartedWithAnalyticalReport/AddingDataSet.gif)
DOC-DETAILS-TAG-CLOSE
Next, add the `Channels` data set with the same approach:

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

### Adding Report Parameter

The Sales Report that we are building should ask a reader for the Sales Channel to display the summary data for. Using [report parameters](/activereportsjs/docs/v6.1/ReportAuthorGuide/Report-Parameters) is the primary method for such an user input. Follow the next steps to create the `SalesChannel` report parameter.

* In the Data panel click the `+ Add` button in the `Parameters` Section.
* Type `SalesChannel` in the `Name` field and `Select the Sales Channel` in the `Prompt` field.
* Select `Integer` in the `Data Type` drop-down.
* In the `Available Values` section select `Channels` for the `Data Set Name`, `ChannelKey` for the `Value Field`, and `ChannelName` for the `Label Field`.

DOC-DETAILS-TAG-OPEN
DOC-SUMMARY-TAG-OPEN
Expand to watch adding the report parameter in action
DOC-SUMMARY-TAG-CLOSE
![](https://cdn.mescius.io/document-site-files/images/e637becb-8cbc-4561-bac1-84462097b057/reportauthor-guide/GetStartedWithAnalyticalReport/AddingReportParameter.gif)
DOC-DETAILS-TAG-CLOSE

### Using the Galley Mode for Preview

You can [preview](/activereportsjs/docs/v6.1/ReportAuthorGuide/QuickStart/Get-Started-With-Designer-App#preview-button) the report output and notice that the `Parameters panel` appears and asks to select the sales channel from the list of pre-determined values. These values come from the `Channels` data set at preview time. Therefore, if a new sales channel appears in the data, the report parameter selection will update the available values list.
Select any value and click the `Preview` button. The report displays the blank page because we haven't added any report items yet.
Click the rightmost button, called `Galley Mode`, on the [report viewer toolbar](/activereportsjs/docs/v6.1/ReportAuthorGuide/Report-Viewer-Interface#toolbar). This button switches the viewer mode to display the unpaginated output that is suitable for reading summary reports. Thus, the blank page disappears, but that is perfectly fine. The report output will appear later.
DOC-DETAILS-TAG-OPEN
DOC-SUMMARY-TAG-OPEN
Expand to watch switching to Galley Mode in action
DOC-SUMMARY-TAG-CLOSE
![](https://cdn.mescius.io/document-site-files/images/e637becb-8cbc-4561-bac1-84462097b057/reportauthor-guide/GetStartedWithAnalyticalReport/GalleyPreview.gif)
DOC-DETAILS-TAG-CLOSE

### Adding Sales Data Set

Next, we will add the `Sales` data set containing the actual data that the report will visualize.

* Go back to the report design and exit the Parameter Editor by clicking the Left Arrow icon near the `EDIT PARAMETER` header.
* 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,StoreKey,SalesAmount` in the `Value` field of the same `Parameter` item.
* By default, the `FactSales` data set includes data for all the sales channels, but the report should only display the selected sales channel's data. Therefore we filter the retrieved data by using the OData [$filter query option](https://www.odata.org/getting-started/basic-tutorial/#select) via the query parameter. The filter value is bound to the report parameter that we added in the previous step.
    * 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+{@SalesChannel}` in the `Value` field of the same `Parameter` item.
* Click the `Validate` button.
* In the Parameter Prompt that appears, type `1` in the `@SalesChannel` field and click the `Save & Run` button.
* Ensure that the `DataBase Fields` section displays `[3 items]` text.

### Adding Calculated Field

The `Sales` data set includes the `DateKey` field that is the sale date in `YYYY-MM-DD` format. To simplify report development, we now add the [calculated field](/activereportsjs/docs/v6.1/ReportAuthorGuide/Databinding#data-set-fields) that will contain the year of the sale date. We assume that you keep the `Sales` data set editor open.

* Click the `+` icon on the right side of the `Calculated Fields` section.
* Type `SaleYear` in the `Field Name` field of the newly added item.
* Type `{Year(DateKey)}` in the `Value` field of the same item.
* Click the `Save Changes` button in the `Sales` data set editor dialog.

### Adding Tablix Data Region

To display the summary data from the `Sales` data set we will use the [Tablix](/activereportsjs/docs/v6.1/ReportAuthorGuide/Report-Items/Data-Regions/Tablix) data region:

* 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 `Tablix` item from the toolbox to the top-left corner of the report page area. The [Tablix Wizard](/activereportsjs/docs/v6.1/ReportAuthorGuide/Report-Items/Data-Regions/Tablix#tablix-wizard) will appear.
* In the tablix wizard drag and drop expand the `Sales` node in the `Data Sets` list.
* Drag and drop the `StoreKey` field to the `Row Groups` area.
* Drag and drop the `SaleYear` field to the `Column Groups` area.
* Click the `sort` icon on the right side of the `SalesYear` and select `Ascending` in the drop down menu.
* Drag and drop the `SalesAmount` field to the `Values` area.
* Click the `Σ` icon on the right side of the `Count(SalesAmount)` item and select `Sum` in the drop down menu.
* Click the `gear` icon that resides next to `Σ` and select `Currency` in the drop down menu.
* Click the `Totals for Row Groups` and `Totals for Column Groups` checkboxes in the `Totals` tab of the `Layout Options` area.
* Click the `OK` button.

With this configuration, the tablix will generate rows for each unique Store Key, columns for each Sale Year, and display the sum of SalesAmount in their intersection. Additional rows and columns will show the grand totals for each store and each year.
DOC-DETAILS-TAG-OPEN
DOC-SUMMARY-TAG-OPEN
Expand to watch how to set up the Values and Totals in action
DOC-SUMMARY-TAG-CLOSE
![](https://cdn.mescius.io/document-site-files/images/e637becb-8cbc-4561-bac1-84462097b057/reportauthor-guide/GetStartedWithAnalyticalReport/AddingTablix.gif)
DOC-DETAILS-TAG-CLOSE
You can preview the report output, try to choose different sales channels in the parameter panel, and notice that the report displays the summary data for the selected channel only. The tablix needs to be well-formatted, though.

### Formatting Tablix Rows

Follow the next steps to format tablix rows.

* Go back to the report design and click anywhere inside the tablix to display the columns and rows handlers of the [tablix designer](/activereportsjs/docs/v6.1/ReportAuthorGuide/Report-Items/Data-Regions/Tablix#tablix-designer).
* Press the `Ctrl` button on Windows or `Cmd` on Mac OS and click each row handler of the tablix to select all the rows.
* Set the `Height` property of the selected rows to `0.3in` in the property inspector
* Switch to the `TEXTBOX` tab in the Property inspector. You can now modify the properties of all the tablix cells of selected rows.
* Set the `Border Width` property to `0.25pt`
* Set the `Border Style` property to `Solid`
* Set the `Border Color` property to `Gainsboro`
* Set the `Vertical Align` property to `Middle`

DOC-DETAILS-TAG-OPEN
DOC-SUMMARY-TAG-OPEN
Expand to watch formatting tablix row in action
DOC-SUMMARY-TAG-CLOSE
![FormattingRows](https://cdn.mescius.io/document-site-files/images/fcb03d52-fb54-40c3-8356-825c2a095d5b/FormattingRows.3d9555.gif)
DOC-DETAILS-TAG-CLOSE

* In the same manner, select the first and the third tablix rows by clicking the corresponding row handlers.
* Switch to the `TEXTBOX` tab in the Property inspector.
* Set the `FontWeight` property to `Bold`.
* Select the first tablix row.
* Switch to the `TEXTBOX` tab in the Property inspector.
* Set the `BackgroundColor` property to `#f7f7f7`.

### Formatting Tablix Columns

Follow the next steps to format tablix columns.

* Click the first column handler and set its `Width` to `2in` in the Property inspector.
* Switch to the `TEXTBOX` tab in the Property inspector.
* Expand the `Padding` editor using the icon on its right side and set the `Left Padding` property to `6pt`
* Click the last column handler. The `TEXTBOX` tab in the Property inspector is still active.
* Set the `FontWeight` property to `Bold`.
* Set the `TextAlign` property to `Right`.

DOC-DETAILS-TAG-OPEN
DOC-SUMMARY-TAG-OPEN
Expand to watch formatting the first column in action
DOC-SUMMARY-TAG-CLOSE
![FormattingColumns](https://cdn.mescius.io/document-site-files/images/fcb03d52-fb54-40c3-8356-825c2a095d5b/FormattingColumns.9083a4.gif)
DOC-DETAILS-TAG-CLOSE
You can preview the report output, select any sales channel, and notice that it is now a bit more accurate.

### Displaying fields from multiple data sets

Displaying the `StoreKey` in the first column of the tablix rows is not very informative, so let's display the Store Name instead. The `Sales` data set does not contain the `StoreName` field, but the `Stores` data set does. We will use the [Lookup](/activereportsjs/docs/v6.1/ReportAuthorGuide/Expressions/Functions/miscellaneous#lookup) function to resolve the Store Name by its Key.

* Go back to the report design.
* Double click the tablix cell on the first column of the second row. It displays the `{StoreKey}` text.
* Replace this text with `{Lookup(StoreKey, StoreKey, StoreName, "Stores")}`. This function takes the value of the first argument in the current scope - the `StoreKey` of the current tablix row - finds the row with the same `StoreKey` value in the `Stores` data set and returns the corresponding `StoreName` value.

In the same manner, we can display the name of the selected Sales Channel in the [tablix corner](/activereportsjs/docs/v6.1/ReportAuthorGuide/Report-Items/Data-Regions/Tablix#tablix-corner)

* Double click the tablix cell on the first column of the first row. It displays the `Store Key` text.
* Replace this text with `Annual {Lookup(@SalesChannel, ChannelKey, ChannelName, "Channels")} Sales`. The `Lookup` function takes the value of the first argument that is the current `SalesChannel` parameter value, finds the row with the same `ChannelKey` value in the `Channels` data set and returns the corresponding `ChannelName` value. At the preview time, this expression will display values like `Annual Online Sales`

DOC-DETAILS-TAG-OPEN
DOC-SUMMARY-TAG-OPEN
Expand to watch adding Store Name and Channel Name Lookup Resolution in action
DOC-SUMMARY-TAG-CLOSE
![](https://cdn.mescius.io/document-site-files/images/e637becb-8cbc-4561-bac1-84462097b057/reportauthor-guide/GetStartedWithAnalyticalReport/AddingLookup.gif)
DOC-DETAILS-TAG-CLOSE
You can preview the report output, select any sales channel, preview the output, and notice that it now displays both Sales Channel Name and Store Names.

The final version of report is available at the [demo web-site](/activereportsjs/demos/Reports/GetStartedWithSummaryReports/purejs).