Native ad-hoc report in Qlik Sense
Updated: Nov 20, 2020
An ad-hoc report can be created using native Qlik objects and functions. No extensions required.
One day, a Qlik developer found an app that had 8 base sheets and more than 100 private sheets. Most of them were not used in the last 90 days… (Operations Monitor > Sheet Usage).
And so she asked around. The answer was generally the same: “You know, the app is great, it’s just that I sort off need those same charts as on them base sheets, but a little different each time. And like really… in a table…“. Yup, self-service at its greatest. On top of that, many people with just an analyzer license did not use the app very much, because they just couldn’t create a report they needed.
No sulking, she set off developing. A few days later and with a help of one Qlik Luminary, an Ad-hoc Report emerged from native Qlik objects and functions. No extensions were used. 🎉 🥂
But honestly, that Qlik Luminary just recycled those same principles he used in QlikView. Mainly:
a data island with Names and IDs of measures and dimensions that you can choose to show
Show column if condition in Straight and Pivot table settings
a function that evaluates as True when some measure(s) or dimension(s) is selected :
SubStringCount( Concat( [ReportDimension], ‘,’), ‘<mea or dim name>‘ ) = 1
a calculation condition that will show the report only if some measure(s) and dimension(s) are selected
GetSelectedCount( [ReportDimension] ) > 0 AND GetSelectedCount( [ReportMeasure] ) > 0
… And added one enhancement:A timeframe selection. This complicates the setting quite a bit but adds a crucial possibility : YtD, MtD, WtD etc. comparisons.
Did it solve all her troubles? Of course not. There had to be enablement and proactive support, but yes the situation is much better now. And more importantly her increased communication with business users led to further enhancements in the app (more measures and dimensions, cohort analysis, simple what-if forecasting…) which translated to its higher ROI.
The core functionality was also described many times on Qlik Community, e.g. in Jennell McIntire’s blog post.
We usually need just a few hours to implement this full Ad-hoc Report in Qlik (with the timeframe selection for time comparisons) in one app. Let us know if you need help.