Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
Jennell_McIntire
Employee
Employee

Four years ago, I wrote the Customizable Straight Table blog illustrating how you can build a custom table in QlikView.  Last year, I wrote the Custom Report Extension blog that shows how to use the Climber Custom Report in Qlik Sense.  Today, with the help of Patric Nordstrom and Jason Michaelides, I am going to blog about how you can build a custom report in Qlik Sense.  With continued enhancements to the Qlik Sense table such as column show conditions and the ability to change the column order of a table, building a custom report in Qlik Sense is possible right out-of-the-box.

 

Patric built the attached Qlik Sense app that illustrates how to build a custom report in Qlik Sense.  In this blog, I will review the steps needs to build your own report.  Below is an example of how the report looks with some dimensions and metrics selected.  On the left there are two filter panes, one for the dimensions and one for the metrics, that can be used to select the columns you would like to see in the report.  Then there is the table which by default will be empty and will prompt the user to select the dimensions and metrics.  In this example app, the user is encouraged to select no more than 5 dimensions and 4 metrics.

table.png

Let’s start by looking the script.  You would load your data as usual but what you will need to add to the data model is a data island for the dimensions and for the metrics.  In the script below, you can see how Patric did it, providing very clear details of each dimension and metric.  The first field in each table is the dimension/metric grouping, the second field is the dimension/metric name as it will appear in the filter panes.  The third field is a number to represent the default order of the dimension/metric columns in the report and the last field is description data about the dimension/metric.  The KPIs table has a fourth field that shows the formulas for the metrics.

script.png

Once the app is loaded with the data islands, you can begin to build the custom report.  You will need to add two filter panes and a table to your sheet.  The Dimension filter pane uses the Dimension field and the Metric filter pane uses the Metric field.  In the table, you will add 5 dimension columns and 4 measure columns since that is what is encouraged in this example.  Now here is where Patric used Jason’s FirstSortedValue trick.  For the first dimension, enter the text below in the Field.

field.png

And this in the Label:

label.png

So, you may be wondering what the expression is doing.  Basically, the Dimension field is being sorted by the DimensionNo field and then, since the third rank parameter is being used, the expression returns the Dimension for the nth sorted value.  In the report screenshot above, the dimensions Product Name and Product Number were selected.  When you sort these by DimensionNo, Product Name comes before Product Number since 3 comes before 4 and when you rank these by their sorted value, Product Name is first and Product Number is second.  This same idea was used in all the dimensions as you can see in the second dimension shown below.

field2.png

The measures are done in the same manner.  Below is the Field and Label for the first measure and the additional measure columns will follow the same concept as the dimension columns.

 

Field

m_field.png

Label

m_label.png

Now the last step is to add a conditional show to the columns.  Basically, you only want the column to show if there is a dimension and/or metric selected to be displayed.  For the first dimension column, you will set the “Show column if” property to:

condition.png

For the second dimension, it will be the same except if greater than or equal to 2 and so on.  The first measure will have the “Show column if” property set to:

m_condition.png

Additional measure columns will follow the same format as described above for dimension columns.

 

Once all the columns are added and the properties are applied, the user can select the dimensions and metrics they would like to see in the report.  By default, the column order will be based on the DimensionNo field but feel free to drag columns in the table to a new position.  For more information on how Jason used FirstSortedValue, check out his document or Qlik Sense Help.

 

Thanks,

Jennell

20 Comments
indhuh44
Contributor
Contributor
Hi, i have tried to use the above method creating a inline for Dimension and Measures and created a straight table. And used the show columns if condition in to display the data. example: IF(WildMatch(GetFieldSelections(xReportType),'*CustName*'),1,0) But max i can select only 6 dimensions and 6 measures at a time. I just want to know whether we have any restrictions on column selection if we use wildmatch in if condition. Thanks, Bindhu.
0 Likes
4,620 Views
robert99
Specialist III
Specialist III

@indhuh44 

Hi Bindhu

Good question regarding using 6 only. (6 selections not 6 fields in a filter pane).

 

I use Custom tables a lot now when developing Apps. For both Pivot Tables and tables. I always use the IF(WildMatch(GetFieldSelections(xReportType),'*CustName*'),1,0) option as its simple and quick to set up (I even prefer it to the Climber option although both are VG)

 

But Ive noticed now that QS can only allow 6 selections at once. I'm sure it allowed more when I first did this but for about a year + now its restricted to 6 only

The way i get around this is

-use one inline selection for more than one dimension or measure.

-Using more than one Adhoc report / filter pane combination. The most I have needed is three. One for main dimension. One for other dimensions. And one for measures.

 

I find grouping dimensions and measures in this way is better than just having one filter pane for with say 15 or so selection options. But I would like to know why there is this restriction of 6 only. It seems odd with a powerful product like QS. And also have the option of more than 6.

 

4,604 Views
millerhm
Partner - Creator
Partner - Creator

We ran into a similar issue while using SubStringCount where we were limited to 6. Not sure if this is the same, but discovered we could add a pipe and a value to expand the number of values available. 

See the discussion between Sweuhl and Sunny_Talwar here: https://community.qlik.com/t5/QlikView-App-Development/Conditional-Enabling/td-p/1144043 

4,561 Views
indhuh44
Contributor
Contributor
Thank you @robert99 for the quick response. Thank you @millerhm for the link. That's help a lot. i think the SubStringCount is the alternate way to resolve this issue. Thanks, Bindhu.
0 Likes
4,522 Views
Avinash1
Contributor
Contributor

Hello All,

I don't see the Column Headers in the straight table after selecting dimensions and Measures from the Filter pane.

Not sure what i am missing.

Appreciate the help.

 

Edit: Just had the Labels incorrectly scripted. Works fine now.

 
 

 

 

1,937 Views
prashanth1712
Contributor III
Contributor III

Hi Jennell,

Thanks for the excellent post. 

I have implemented this in my apps and I have received a query on sorting of the columns. 

After making necessary selections in the table, users have dragged some of the columns into the positions they want and saved it as a bookmark. But upon reopening the app and  created bookmark is selected, the column order which we have arranged by dragging the columns is not saved. 

Can you please let me know if there is any solution to this.

Regards,

Prashanth

0 Likes
1,467 Views
Ejelkerton
Contributor
Contributor

Is there a way to have number formats change with the measures in the custom report

 

931 Views
JordyWegman
Partner - Master
Partner - Master

Hi Ejelkerton,

What would you specifically like to see? Because you can format the number if you want to:2022-04-20 14_30_00-Window.png

 

Jordy

Climber

875 Views
doespirito
Creator
Creator

hi,

Can someone give me the sales.xlsx file so I can test Jennell's app?
Thanks a lot

Arnault

664 Views
snancyiv
Contributor III
Contributor III

I downloaded the QVF but when I tried to used the Custom Report, it has an “Incomplete Visualization” error. 

also when I used the tutorials, does the inline statement not allow spaces in the field names for the dimensions? Keeps getting an error. 

0 Likes
488 Views