[{"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 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 and/or 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
Use the OData $select and $filter query options to reduce the volume of retrieved data
Create data set calculated fields
Create a tablix data region
Configure the tablix using the Tablix Wizard
Format rows and columns of the tablix data region
Use the 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.
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 the 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 Sales Report, we need to have access to the data from the /DimStores, /DimChannels and /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 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 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.
Expand to watch adding the Stores data set in action
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 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.
The Sales Report that we are building should ask a reader for the Sales Channel to display the summary data for. Using 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
.
Expand to watch adding the report parameter in action
You can preview 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. 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.
Expand to watch switching to Galley Mode in action
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 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 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.
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 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.
To display the summary data from the Sales
data set we will use the Tablix data region:
Expand the 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 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.
Expand to watch how to set up the Values and Totals in action
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.
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.
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
Expand to watch formatting tablix row in action
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
.
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
.
Expand to watch formatting the first column in action
You can preview the report output, select any sales channel, and notice that it is now a bit more accurate.
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 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
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
Expand to watch adding Store Name and Channel Name Lookup Resolution in action
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.