Creating a Self Service Report for QlikView

By:

Every self respecting Qlik Dashboard should end in an option to create a self service report.  I’ve taken the pain out of writing your own one by providing an easy to use template with downloadable code and a printable cheat sheet.  You’re welcome.

Flexi Report is just a chart object (usually Pivot or Straight table) that allows an end user to create reports on demand.  It uses existing out of the box Qlik technology and there are no extensions to install.

How it Works

FlexiReport requires 5 things to work:

  1. A properly configured Chart Object
  2. 6 Variables
  3. A CSV control file where the dimensions and measures are defined
  4. A list box of dimensions
  5. A list box of measures

On completion, you should have something like this:

Adding the Variables

Add the variables to the script (where you add these is up to you, I always declare my variables on the first tab).  You will need to reload the application before the variables become available.  The variables are available on  my gist, or pasted here for convenience:


SET vActivateReportMeasure = If(Index(Concat(MeasureFieldName,';'),SubField(concat(MeasureFieldName,';'),';',$1)) AND GetSelectedCount(MeasureLabel),1,0);
SET vMeasureFieldName      = SubField(concat(MeasureFieldName,';'),';',$1);
SET vMeasureLabel          = Only({<MeasureFieldName={"~(=SubField(concat(MeasureFieldName,';'),';',$1))"}>} MeasureLabel);
LET vMeasureLabel          = Replace(vMeasureLabel,'~','$');

SET vActivateReportDim     = If(Index(Concat(ReportFieldName,';'),SubField(concat(ReportFieldName,';'),';',$1)) AND GetSelectedCount(ReportLabel),1,0);
SET vReportFieldName       = '['&SubField(concat(ReportFieldName,';'),';',$1)&']';
SET vReportLabel           = Only({<ReportFieldName={"~(=SubField(concat(ReportFieldName,';'),';',$1))"}>} ReportLabel);
LET vReportLabel           = Replace(vReportLabel,'~','$');  //I wish QlikView had an escape character!

Once the QlikView document has been reloaded, the variables can be used in their ‘parameterised’ format.

Configuring a Chart Object

The FlexiReport is controlled via a chart object.  Choose a straight table as the initial object type.  You can add quick change options to include pivot, bar and line charts later if desired.

On the dimensions tab, use the variables you created above in the same format as shown in the image here:

  • Add up to 20 calculated dimensions. Use the same variable for each dimension, replacing n with an incrementing number from 1 to a recommended maximum of 20
    =$(=$(vReportFieldName(n))).  Make sure you use the double dollar expansion to ensure the variable is expanded and executed correctly.
  • Enable Conditional for each dimension you added. Use the variable
    $(vActivateReportDim(n)) ensuring the number in the brackets matches the number in the corresponding dimension.
  • Add Label. Use the variable
    $(vReportLabel(n)) for the dimension label.  Again, make sure the number in the brackets matches for the corresponding dimension.

The expressions tab follows a similar pattern to the dimensions.  Again, there are three variables, and each one should have a consecutive number in the brackets.  It is recommended not to make any more than 10 measures available here.

 

  • Enable Conditional for each expression. Use the variable $(vActivateReportMeasure(n)) where n is an incrementing number starting at zero.
  • Add Label. Use the variable $(vMeasureLabel(n)) for the expression label. Again, make sure the number in the brackets matches the corresponding expression.
  • Definition. Use the variable =$(=$(vMeasureFieldName(1))). It is recommended you only use a maximum of 10 expressions here.  Ensure you use the double dollar expansion to ensure the variable is expanded and executed correctly.

The CSV Control File

The CSV Control file is the document where you store and manage all of the dimensions and measures you want to use within the application.  It is made up of 4 fields:

  • Type: Can be either Dim (for dimensions) or Measure.
  • ReportLabel: The label you want to appear for this field.
  • ReportFieldName: The QlikView field name you want to reference for this field, or an expression (if the Type is Measure).
  • Filter: A grouping for this row. All rows with the same filter name will be nested together.  Use a \ backslash to add further levels to the group, eg Patient\Home
Type ReportLabel ReportFieldName Filter
Dim DOB “Person DOB” Patient
Dim Forename “Patient Forename” Patient
Dim Year “Calendar year” Calendar
Dim Month “Calendar Month” Calendar
Measure Average Age Avg([Person Age]) Patient
Measure Oldest Person Max([Person Age]) Patient

You then need to add more code to your script to ensure this file is loaded in correctly:


/*
| ---------------------------------------------------
| Load Reporting Tables
| ---------------------------------------------------
| Loads the tables with the Dimension and Measure
| details.
|
*/
SelfServeReports:
LOAD
Filter&'\'&ReportLabel as ReportLabel
,ReportFieldName
FROM
[FlexiReportSettings.csv]
(txt, UTF8, embedded labels, delimiter is ',', msq)
Where Type = 'Dim';

SelfServeReportsM:
LOAD
Filter&'\'&ReportLabel as MeasureLabel
,ReportFieldName as MeasureFieldName
FROM
[FlexiReportSettings.csv]
(txt, UTF8, embedded labels, delimiter is ',', msq)
Where Type = 'Measure';

Now reload the application to bring in the report settings which will be stored as two island tables.

A List Box of Dimensions and Measures

The final piece of this jigsaw, is to add the two list boxes to allow the user to control which dimensions and measures are switched on in the chart.  The process is almost identical for the measures and dimensions.

Adding the Dimensions Control

  1. Create a new list box object
  2. Under Field, select ReportLabel
  3. Tick Show as TreeView, use a backslash \ as the separator.
  4. On the Presentation Tab, change the Selection Style Override setting to LED Check boxes. Check Single Column.  Configure the rest of the look and to match your own style.

 

Repeat the above for a Measures list box, but in step 2, select MeasureLabel as the field.

Hopefully that will save you a bit of time.  Please let me know in the comments below if any of this is not clear and I will endeavour to add further explanations.

 

 


2 Replies to “Creating a Self Service Report for QlikView”

    1. Hi Anil,
      Many thanks for reading my post and your question. I presume you are talking about the excellent Adhoc Reporting doc written by Jagan Mohan?
      My solution is similar, but better 😉

      1. All dimensions and measures for the report are maintained in an external file making it easy to add, edit or delete.
      2. This solution includes list box grouping for the dimensions and measures
      3. The solution uses parameterised variables in the script to make the solution far more maintainable

      Jagan’s solution requires you to hard code every single expression in the chart (3 for every new dimension or measure added). That will get extremely hard to maintain if you have dozens of dimensions and measures in your data model (as most people do).
      All in all, what I have provided is a far more scalable solution using best practice techniques.
      Best regards
      George.

Leave a Reply

Your e-mail address will not be published. Required fields are marked *