Is there really one type of Ad-hoc Reporting with Qlik Sense?

[Michael_Tarallo]

A question for you all to consider. Is there really one type of Ad-hoc Reporting with Qlik Sense? Read-on to learn how you can "Ad-hoc"  with Qlik Sense!

[Michael_Tarallo]

Hi guys - I hope you enjoy this one. It has been a while since I actually typed-out a blog. I have been so used to recording video for so long, man my fingers hurt! 😩 - Stay well all.

Sample .QVF and .MP4 video attached to this post.

Regards,
Mike

What I want from you

I invite you to share your spin in "Ad-hoc Reporting" as well. I am aware that our valued partners have packaged solutions - so please comment and share your solutions. 

What does Ad-hoc mean?

The term "ad-hoc" means "created for a particular purpose" or "when needed". In the analytics world, we have commonly used the the term "ad-hoc reporting" to basically describe the process of easily making your own reports as opposed to consuming standard KPIs in a dashboard or static and operational reports created for you. With the change in BI technology I think it is time to consider new ways and other types of "Ad-hoc" reporting.

Ad-hoc reporting interfaces can vary - and usually provide a means of selecting your tables, fields (dimensions and measures), aggregations, filters etc. They may include ways to combine data and much more. Regardless of the approach and interface it is providing you with answers to your business questions on the fly  when needed with the criteria that you are interested in. Here are some new ways of thinking about "ad-hoc" reporting. 

The Qlik Sense Design Canvas

Calling attention to the obvious, out of the box - the Qlik Sense Design canvas - provides all the tools you need to select tables, add measures, dimensions, selections, visual objects etc. So technically could be considered an "Ad-hoc" approach to creating reports and analytics. BUT you can also use this UI to create easy to use "Ad-hoc-like" apps for others to consume. More on this later. 

Insight Advisor and the Cognitive Engine

Again, out-of-the-box, Qlik Sense provides an easy to use analytical assistant to answer questions using natural language. Our Insight Advisor Search and Insight Advisor Chat interfaces could be seen as means to create ad-hoc content as you are providing the dimensions, measures and filters in a simple chat or search UI and the Qlik Cognitive Engine produces or suggests results to answer your question, that you can then easily review or add to your design canvas for analysis. It is important to note that when analyzing data , at any time you can use Insight Advisor search to further your exploration and discovery - it IS NOT just an easy way to create charts to be added to your design canvas for your dashboard.

Alternative Dimension and Measures feature

There may be a time when you want more control on what is displayed in the chart or table, without having to create multiple copies of the object with different fields, aggregations and properties. This can be done easily with the Alternative Dimensions and Measures feature available in the data tab of the chart object. BUT - with this feature you are predefining the chart object to use a set list of dimensions and measures. It requires the used to open the Exploration menu in the chart to change the fields - or they can use the arrows on the legend. 

Creating Custom Qlik Sense "Ad-hoc" apps

Other approaches to "Ad-hoc" with Qlik Sense involve creating custom Qlik Sense apps (or web-based mashups) that have selection interfaces that drive dynamic "report templates". These report templates are pre-defined with expressions , master items, variables etc. These expressions and/or variables are substituted with dimension and measure fieldname values provided by the selectors. A chart object (table, graph) uses placeholders  variables or fieldnames that will change on the fly based off the selected value. Therefore creating a purely dynamic chart from the user's input. 

Our Tomi Komolafe has contributed a great video showing us one of his examples of how he built a simple Ad-hoc reporting app for a customer. Check it out below! f you are a YouTube user, I suggest you subscribe to his channel to see what other great Qlik tips and techniques he has to offer. Thanks for your valuable contributions Tomi!

For other examples of working with your own ad-hoc interfaces - check out my Do More with Qlik session on Fun with variables. 

• Sign up for a future Do More with Qlik Webinar
• View the Do More with Qlik Archive of past sessions

Ad-Hoc Reporting.qvf

[stevedark]

Hi Mike,

Pleased to share how I approach this:
https://community.qlik.com/t5/Qlik-Gallery/Quick-Intelligence-Instant-Sense-App/ba-p/1721815

Available to download and BYOD to. There's also a blog post if you would prefer to build it yourself.

Has the flexibility to get to any data you want, but the structure that it is not going to come up with strange results.

Interested to see what anyone else suggests.

Steve

[Michael_Tarallo]

Thank you Steve - this is great!

[paulcalvet]

Hello,

Users always ask a customized analysis like in the video. The Problem is That kind of analysis is long to build and not easy to maintain. 
there are extensions which permit to add automatically all master items in a table. 
I think it’s the best solution, is there a chance to have this kind of extension added to the dashboard bundle ?

[Michael_Tarallo]

Hi Paul - thanks for your comment / question. There are a number of robust partner solutions. BUT I agree I think a basic one with some customizable features would be nice. I will check with @Patric_Nordstrom .

Please consider posting your thought in the ideation section of the community:

https://community.qlik.com/t5/Suggest-an-Idea/idb-p/qlik-ideas

[JuanGerardo]

I agree with you both, @paulcalvet and @Michael_Tarallo, about to use robust extensions from partners like Mercanza 😉. This way you can be sure extensions will be maintained and evolved with more features.

A good example for ad-hoc reporting is our MzGenaro extension that uses master dimensions and measures to allow users create their own visualizations, save them for later use and share with other users:

And compared to the above video, this is just drag & drop... and click, no need for scripting.

JG

[barnabyd]

Thanks @Michael_Tarallo for another great post and to Tomi for the video.

This is an interesting discussion as I think the whole self serve design of Qlik Sense is intended to address this issue. Users can already create their own sheets with their own tables and charts - that's the ultimate ad hoc reporting.

However, most users don't want (or are not technical enough) to achieve this, so the Tomi's solution fills a need for the average user.

From @JuanGerardo 's post, this extension looks to be far more powerful than Tomi's solution, so it also fills a need for those users who need more than a basic table.

So, it seems to me that there is an entire spectrum of users and we need a spectrum of solutions to support these users and a built-in ad hoc solution would definitely be used in many apps.

Cheers,

Barnaby.

[paulcalvet]

I posted an idea here :

https://community.qlik.com/t5/Suggest-an-Idea/Ad-hoc-official-extension/idi-p/1822939#M6780

Paul

[Or]

I would say the biggest thing standing between Qlik Sense and a reasonable level of controlled ad-hoc reporting is adding alternatives to the table and pivot table object. Most ad hoc reports involve one or the other, and both are lacking in an alternatives option. Some third-party solutions do allow alternatives in at least the pivot table, which turns it into a good ad-hoc option (albeit not as robust as entering edit mode or using a dedicated ad-hoc tool which typically have more options).

Open ad-hoc reporting is somewhat available using Insight Manager or the design canvas (the latter is only available to Professional users and requires non-trivial expertise), but this comes with a caveat - it's very easy to generate nonsense the developer never intended. Controlled ad-hoc reporting, where the developer allows only specific measures/dimensions to be used in a specific context, is often the more useful approach, and this is something Qlik Sense could improve on for sure by extending the existing Alternatives functionality to more objects.

[Chernov]

Hi!
It is a very bad idea to link conditions to the names of dimensions/measures, we have not done this for several years.
What if you need to rename the dimension/measure? - Do you want to redo formulas in the interface again ?

We create a table with the name of the dimension/measure and the code, and in the expressions we use the formula:

= INDEX (CONCAT ( DISTINCT ' ' & [DimCode] & ' ', ','), ' 2 ') > 0 AND GetSelectedCount ([DimName]) > 0 

so ' 2 ' in this function - is code of dimension