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) \<range> \<value>last7days\</value> \</range> you will then use the range/to and range/from arguments in the where statement of your sql script for example where submittime between '%=format("{0\ s}",=//job/range/from)%' and '%=format("{0\ s}",=//job/range/to)%' 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)