Author: Marek Adamczyk

Downloadable Document also attached

The Report Writer is provided as a standard part of the KeyedIn Projects application.  It provides Admin users the opportunity to create reports allowing you to view and analyse your data in a wide variety of formats.

This article describes the steps involved in creating a Bar graph widget which allows the user to view and drill down through various levels of Project data.

The aim is to create a chart widget which displays Project Chargeable data against ‘Department’ -> ‘Parent Project’ -> ‘Project Manager’ -> ‘Project’

The steps below describe the creation of such a report.

1) Navigate to the ‘Reports’ area of the application using the sidebar:

 2) Select the ‘Add’ button on order to start the report creation process:

3) The ‘Add Report’ view will be displayed and ‘General’ tab will be selected by default;

4) Enter name and a Title for your report – in this case we’ve used “Project Drilldown Example”:

5) Click on the ‘Definition’ tab. This is where the bulk of the report configuration will be done;

6) In the top section, set the ‘Data View’, ‘Report Type’ and ‘Usage Type’ values as displayed in the image below:

The ‘Data View’ selection defines the type of data we will be reporting on – in this case it is Project data that will for the basis of our report. Please note that the version number of the data view in your instance of KeyedIn Projects may differ from that displayed in the image. Data views are updated to accommodate new system functionality. Requests from our clients also often lead to data view updates, e.g. the provision of additional data fields.

The ‘Report Type’ setting indicates the format of the report we would like to create. In this case, we have chosen the ‘Chart’ type. This setting can be specialised further, as will be described later in this article.

The ‘Usage Type’ setting defines where the report is available within the application. The ‘’Widget’ value means that the report will be available on Dashboards, the My Work Home page and the My Projects Home page.

7) In the ‘Fields’ tab, which will be selected by default, the fields to be included in the report can be chosen. In the case of this article, the following fields have been selected:

For this particular report, the ‘Project Code’ field will not be displayed, it will only be used in the calculation of the chart’s values so it can be hidden:

For this particular report, the ‘Project Code’ field will not be displayed, it will only be used in the calculation of the chart’s values so it can be hidden:

8) The aim of the report is to display a count of chargeable vs non-chargeable Project information so it is necessary to group the data using this field. To do this, navigate to the ‘Grouping’ tab;

9) From the drop down list in the ‘When running the report, group rows by’ select the ‘Chargeable’ option:

10) The report being created will display a count of the number of Projects against various other Project Data. To achieve this, it’s necessary to find a way to count Projects uniquely. This is where the hidden ‘Project Code’ fields comes into play.                    KeyedIn Projects uniquely identifies entities via either a key value (numeric) or a code value (alphanumeric). In the case of Project entities, a code is used, so if the number of codes in each view category within the chart is counted, 

      it follows that unique   Projects are being counted. It would be possible to use the ‘Project Name’ field but as this is not forced to be unique it is generally safer to utilise the ‘Project Code’ field.

      The ‘Grouping’ tab is the place to achieve this within the application, setting the report to count the instances of the ‘Project Code’ field:

11) It is now necessary to specify the type and layout of the chart itself. To do this, select the ‘Chart’ tab.

      This tab contains the specification of the layout of the chart as well as how the data is to be  displayed in terms of the categories, series, drilldowns, etc.

12) The first thing to do is select the type of chart to be created. For the purposed of this article, ‘Bar’ has been selected.

13) The chart series will be defined by the drilldown categories selected so the ‘Select the field that contains the Chart Series’ setting is left blank.

14) The chart will display whether or not a certain category of Project data is chargeable, so the ‘Chargeable’ field is set as the chart category. Additionally, to select the fields against which the ‘Chargeable’ field is displayed, it is necessary to select        one or more drilldown categories. If no drilldown categories are selected then the chart will simply display the count of chargeable Projects vs the count of non-chargeable Projects and will not provide any drilldown functionality. The category and drilldown categories for the example report are shown below:

15) There are only a couple of things left to setup on ‘Chart’ tab.

      It is necessary to set the chart values, i.e. what the chart will use to provide the values used in the y-axis. This is done using the ‘Select the field that contains the Chart Values’ setting. In this case, the ‘Project Code’ will be used. The report writer        will use the row grouping and a count of the ‘Project Code’ field when plotting this value, as this is what was setup in points 9) and 10).

16) Clicking on the ‘Display’ tab, the display options should be set as follows:

The most important setting here is the ‘Display summarised data in the report (one row for each unique grouping’ radio button. If this option is not selected then the report will fail to run.

17) It is always a good idea to apply a filter to a report. Doing this ensures that the data included is exactly what the user would expect and report which return massive datasets negatively affect system performance. In the case of this example, the        filter is simple, limiting the data returned to Active Level 1 Projects which are not Project Templates:

18) The configuration of the chart and data is now mainly complete, but where will the report be visible and who can view it? These settings are configured in the ‘Availability’ and ‘Login Groups’ tabs respectively:

19) For this particular example, the report has been made available to the ‘My Work’ dashboard:

and to the ‘Configuration’ Login Group:

20) It’s now time to test the report, so click the ‘Save and Run’ button at the bottom of the ‘Edit report’ window:

21) The report is displaying correctly and the data seems fine, but note that the y-axis is labelled as ‘Project Code’. This would be more accurate as ‘Project Count’. To achieve this, it is necessary to go back and edit the report, navigating to the      ‘Definition’ -> ‘Chart’ tab and setting the ‘Enter a Values Axis Label (optional)’ setting to ‘Project Count’:

22) Run the report again and the label has changed to ‘Project Count’.

23) Note that the drilldowns work as expected, taking us through the data levels we defined.

     The report definition file is attached as part of the Freshdesk knowledgebase article so you can upload to your KeyedIn Projects instance, see how the report works, change the fields, etc.