How to create Reports in Activate V8
In previous versions of Activate, reports used to be built using the Microsoft Report Builder. This has been deprecated in V8 and replaced with the Activate Web Grid.
This document explains how to build a new report using the Activate Web Grid in V8. The instructions assume that you are using a SQL script or SQL Query as your data source.
Setting Up the Report
- Open Activate Studio
- Open the existing report
- Create a new WebForm parameter and call it DetailDataWebForm.
- Within the new webform add an ActivateDataSource control.
- Within the ActivateDataSource set the following properties:
ActivateDataSource Properties
Property | Value |
|---|---|
ID | Set the name of the data source (e.g. DetailData). This will become a global variable that can be used in the ActivateWebGrid data source - =//[ID] |
Query | In the Query property, paste in your SQL script. You will need to add @ at the start  |
Add the ActivateWebGrid control beneath the ActivateDataSource.
Within the ActivateWebGrid set the following properties:
ActivateWebGrid Properties
Property | Value |
|---|---|
SelectType | None – this will turn off the check boxes on the rows |
DataSource | This is the global variable that is automatically created by the ActivateDataSource - =//[ID] |
Add the columns to display in the report. There are different column controls available, with the two common ones being the ActivateGridColumn and ObjectLinkColumn.
The ActivateGridColumn is used to display static data, whereas the ObjectLinkColumn can be used to provide a link to allow the user to open an object property page (e.g. Job Properties).
When using an ActivateGridColumn set the following properties:
ActivateGridColumn Properties
Property | Value |
|---|---|
Title | The name of the column |
BoundValue | This is the column in the data source to display. It must start with an @. e.g. @=/Name |
Format * | This allows you to set the format options for the data. e.g. datetime for displaying a date in the dd/mm/yyyy hh:MM:ss AM/PM format |
Width * | This allows you to set the width of the column. If this is left blank the columns will be auto sized |
Hidden * | This sets the column to be hidden by default. Users can unhide these columns when viewing the report in the browser |
- Optional properties
ObjectLinkColumn Additional Properties
When using an ObjectLinkColumn in addition to the properties available on the ActivateGridColumn, the following must be set:
Property | Value |
|---|---|
Link | This is the URL route to the object’s properties page. It must start with an @. e.g. @/job/%=/JobID%/properties |
Target * | This is the target for the link. e.g. Dialog which will open the target in a dialog box |
Icon * | This is the name of the image to use for an icon next to the link. e.g. job |
Optional properties
Report Parameters
Report parameters need to be constructed within their own WebForm (it is recommended that the form is called a meaninful name like SelectionWebForm).
You will need to ensure Arguments have been setup as this will be required to set default values like the date range.
If your report is using a SQL script, you will need to handle when the parameter is null within the WHERE statement. This is done by using the isdefined function – e.g.
%=isdefined(=/user, "and TargetUserGuid = '%=/user/Guid%'\")%
DateRangePicker Controls
When adding a DateRangePicker control you may wish to default the initial option that is displayed when the report loads. To do this you need to set the initial value in the job arguments (assuming range is the ActivateBind):
You will then use the range/to and range/from arguments in the WHERE statement of your SQL script.
For example:
Final Setup
This section explains how to complete the setup of the report.
Firstly, you need to decide on how you want the report parameters to be presented. There are two styles that you can choose from:
- Presented on their own webpage, similar to how they have been done in the out of the box reports in previous versions of Activate.
- Presented within the filter button on the web grid, which is like the reports in the Analytics module.
Report Parameters on their Own Form
To present the report parameters on their own form you will need to setup the Web.Wizard with two System/Form pages.
The first System/Form page needs to be configured with the following properties set:
First System/Form Page
Property | Value |
|---|---|
Title | The name of the report |
Buttons | Default |
Form | Reference to the WebForm containing the report parameters e.g. =//Job/Task/SelectionWebForm |
The second System/Form page needs to be configured with the following properties set:
Second System/Form Page
Property | Value |
|---|---|
Title | The name of the report |
Description * | Enter a description about the report. It could be something like: Jobs between %=//Job/range/from% and %=//Job/range/to% |
Buttons | Cancel |
Form | Reference to the WebForm containing the DataSource and ActivateWebGrid e.g. =//Job/Task/DetailDataWebForm |
- Optional properties
Report Parameters Presented in the Filter Button
To present the report parameters within the filter button you will need to do the following:
- Setup a Web.Wizard and add a single System/Form page.
- On the System/Form page set the following properties:
Single System/Form Page
Property | Value |
|---|---|
Title | The name of the report |
Description * | Enter a description about the report. It could be something like: Jobs between %=//Job/range/from% and %=//Job/range/to% |
Buttons | Cancel |
Form | Reference to the WebForm containing the report parameters e.g. =//Job/Task/DetailDataWebForm |
- Optional properties
Open up the webform that contains the ActivateWebGrid (e.g. DetailDataWebForm) and set the FilterForm property on the web grid with the name of the webform containing the report parameters.
Advanced Setup
Adding Toolbars
When setting up a report you may wish to display a toolbar which contains buttons to take the user to additional pages, or to provide a refresh and/or export button. You can also display certain report parameters outside of the filter (e.g. Date Range).
To do this you will need to setup a parent form and then nest webforms within it. The following example explains how to permanently display the Date Range parameter and add a refresh button to the toolbar.
- Create a new WebForm and call it something like DateRangeWebForm.
- Add the DateRangePicker control to it, and then configure it.
- Set the AutoPostBack property to True on the DateRangePicker control.
- If you have the DateRangePicker Control on the webform containing the other report parameters, you will need to remove it.
- Create a new WebForm and call it something like DetailDataToolbar.
- Add a RibbonPanel control.
- Within the RibbonPanel, add a RibbonButton control.
RibbonButton Properties
Property | Value |
|---|---|
ImageUrl | refresh |
Text | Refresh |
OnClientClick | onRefresh |
- Create a new WebForm and call it WebForm.
- Add a Stack control.
- Within the Stack add a FlexPanel control.
- Within the FlexPanel, add two FormPanel controls.
First FormPanel
Property | Value |
|---|---|
Form | The path to the toolbar webform (e.g. =//Job/Task/DetailDataToolbar) |
CurrentObject | =/ |
FlexGrow | 2 |
Second FormPanel
Property | Value |
|---|---|
Form | The path to the DateRange webform (e.g. =//Job/Task/DateRangeWebForm) |
ActivateBind | . |
CurrentObject | =/ |
Underneath the FlexPanel control add another FormPanel, and then set the following properties.
Report FormPanel
Property | Value |
|---|---|
Form | The path to the report webform (e.g. =//Job/Task/DetailDataWebForm) |
CurrentObject | =/ |
Open the Web.Wizard and update the Form property on the System/Form page and change it to the webform you have just created.
Export to CSV
The ActivateWebGrid has a built in Export to CSV button which will create a CSV file containing the displayed data. However, there may be occasions where you would like to provide a CSV export that contains more information than what is displayed in the grid. For example, additional columns or more rows of data that are not constrained by the Max Rows filter.
This is done by adding an Export button to the toolbar which links to a form containing an ActivateCSVGrid control
- Create a new WebForm and call it ExportWebForm.
- On the new WebForm add an ActivateDataSource control, then set the following properties:
Property | Value |
|---|---|
ID | Set the name of the data source (e.g. DetailData). This will become a global variable that can be used in the ActivateCSVGrid data source - =//[ID] |
Query | Paste in your SQL script. You will need to add an @ at the start  |
3.Add an ActivateCSVGrid control, and then set the following properties:
Property | Value |
|---|---|
AutoGenerateColumns | true |
DataSource | This is the global variable that is automatically created by the ActivateDataSource - =//[ID] |
FileName * | This is the name of the CSV file that is exported. If not set the filename will be the default Export.csv |
*Optional properties
- On the root Form node, set the following properties:
Property | Value |
|---|---|
Title | This is the title that is displayed in the export dialog box |
Buttons | Set this to Cancel to only display a cancel button on the dialog box |
The webform should look similar to the example in the original documentation.
Export RibbonButton
Open the toolbar webform, and add a new Ribbon button, then set the following properties:
Property | Value |
|---|---|
ImageUrl | save |
Text | Export |
CurrentObject | =//Job |
Link | The path to the Export WebForm (e.g. =//ExportWebForm) |
Target | Dialog |
When the export button is clicked a dialog will appear with a link to download the exported CSV file.
Adding Group By
It is possible to add a predefined grouping in the ActivateWebGrid. This is done by setting the GroupBy property with the column that you wish to group by (e.g. @=/TaskName). Note: It is only possible to group by a single column.
Users can also do ad-hoc grouping in the reports using the Group By button but this will not change the predefined GroupBy setting in the report.
Please Note: The datasource is assumed to be sorted by the group by column. If this is not the case then please set the BindingSort as well.
BindingSort
The default action is to assume that the datasource is sorted. Sorting the datasource in SQL is normally the most efficient method. However, you can sort within the Grid. This is a ; separated list of expressions that are used to sort the list.
For example:
- @=/Name will sort by the Name
- @=/Group;@=/Name will sort by the Group and then the Name of the object
Footer Values
It is also possible to add a footer to each column. This can be used to create a sum, count or average of the column values.
FooterText Value | Description |
|---|---|
#SUM() | This will output the sum of the values in the column. This assumes that the values can be converted to a floating point number. |
#AVG() | This will output the average of the values in the column. |
#COUNT() | This will output the count of values in the column. |
=EXPRESSION | The result of the expression. |
String | A constant value String. |
Pivot Tables
One of the new features in V8 is the ability to create Pivot Tables. These are done by setting the properties within the Pivot section of the ActivateDataSource properties:
Property | Value |
|---|---|
Columns | This is a comma separated list of columns (e.g. Month) |
Aggregate | The Aggregate to use for each value. i.e. `(Sum |
PivotBy | This is a comma separated list of columns to pivot on. |
An example of a report that is using the Pivot table feature is the Job History Monthly Report (/Reports/Job Reports/Task Reports/Job History Monthly).