[]
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:
Galley mode
of the report viewerUpon 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.
Data binding establishes a connection between the report and the data it displays. In this tutorial, we use the GrapeCity 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.grapecity.com/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.
Add
button.Contoso
in the NAME field and https://demodata.grapecity.com/contoso/odata/v1
in the ENDPOINT field.Save Changes
buttonData 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:
+
icon near Contoso
in the Data panel.Stores
in the NAME field, /DimStores
in the Uri/Path field, and $.value.*
in the Json Path
field.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
+
icon on the right side of the Parameters
section.$select
in the Parameter
field of the newly added Parameter
item.StoreKey, StoreName
in the Value
field of the same Parameter
item.Validate
button.DataBase Fields
section displays [2 items]
text.Save Changes
button.Next, add the Channels
data set with the same approach:
+
icon near Contoso
in the Data panel.Channels
in the NAME field, /DimChannels
in the Uri/Path field, and $.value.*
in the Json Path field.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.
+
icon on the right side of the Parameters
section.$select
in the Parameter
field of the newly added Parameter
item.ChannelKey, ChannelName
in the Value
field of the same Parameter
item.Validate
button.DataBase Fields
section displays [2 items]
text.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.
+ Add
button in the Parameters
Section.SalesChannel
in the Name
field and Select the Sales Channel
in the Prompt
field.Integer
in the Data Type
drop-down.Available Values
section select Channels
for the Data Set Name
, ChannelKey
for the Value Field
, and ChannelName
for the Label Field
.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.
Next, we will add the Sales
data set containing the actual data that the report will visualize.
EDIT PARAMETER
header.+
icon near Contoso
in the Data panel.Sales
in the NAME field, /FactSales
in the Uri/Path field, and $.value.*
in the Json Path field.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.
+
icon on the right side of the Parameters
section.$select
in the Parameter
field of the newly added Parameter
item.DateKey,StoreKey,SalesAmount
in the Value
field of the same Parameter
item.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.
+
icon on the right side of the Parameters
section.$filter
in the Parameter
field of the newly added Parameter
item.ChannelKey+eq+{@SalesChannel}
in the Value
field of the same Parameter
item.Validate
button.1
in the @SalesChannel
field and click the Save & Run
button.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.
+
icon on the right side of the Calculated Fields
section.SaleYear
in the Field Name
field of the newly added item.{Year(DateKey)}
in the Value
field of the same item.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:
Hamburger
menu located on the left side of the toolbar.Tablix
item from the toolbox to the top-left corner of the report page area. The Tablix Wizard will appear.Sales
node in the Data Sets
list.StoreKey
field to the Row Groups
area.SaleYear
field to the Column Groups
area.sort
icon on the right side of the SalesYear
and select Ascending
in the drop down menu.SalesAmount
field to the Values
area.Σ
icon on the right side of the Count(SalesAmount)
item and select Sum
in the drop down menu.gear
icon that resides next to Σ
and select Currency
in the drop down menu.Totals for Row Groups
and Totals for Column Groups
checkboxes in the Totals
tab of the Layout Options
area.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.
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.
Ctrl
button on Windows or Cmd
on Mac OS and click each row handler of the tablix to select all the rows.Height
property of the selected rows to 0.3in
in the property inspectorTEXTBOX
tab in the Property inspector. You can now modify the properties of all the tablix cells of selected rows.Border Width
property to 0.25pt
Border Style
property to Solid
Border Color
property to Gainsboro
Vertical Align
property to Middle
TEXTBOX
tab in the Property inspector.FontWeight
property to Bold
.TEXTBOX
tab in the Property inspector.BackgroundColor
property to #f7f7f7
.Follow the next steps to format tablix columns.
Width
to 2in
in the Property inspector.TEXTBOX
tab in the Property inspector.Padding
editor using the icon on its right side and set the Left Padding
property to 6pt
TEXTBOX
tab in the Property inspector is still active.FontWeight
property to Bold
.TextAlign
property to Right
.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.
{StoreKey}
text.{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
Store Key
text.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
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.
Submit and view feedback for