Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
johnw
Champion III
Champion III

Dynamic Report with User-Selected Dimensions and Expressions

We have several applications that allow users to select dimensions and expressions for a report (edit: Chart. I keep saying report, but that means something different, this is a chart.) from a list box. Those applications currently work by adding and removing dimensions from a pre-existing chart, and by enabling and disabling expressions.

This has caused two problems. First, my understanding is that QlikView really only has one copy of the chart on the server. So if multiple users are using the dynamic report at the same time, this can cause problems. We've seen crashes and hung user sessions that we believe were caused by this. Second, the macro code to add a dimension has, for whatever reason, simply been buggy off and on for years. Different versions of QlikView seem to have different bugs. In one older version, you could only add dimensions to a visible chart, which caused problems since we wanted to leave the chart hidden until we'd finished adding ALL the dimensions to it. In version 10 SR1, adding a dimension causes the chart to fail to render and get a big X through it. If you open the properties and simply click OK, it renders fine, but that isn't acceptable.

So several years ago, Rob Wunderlich and I discussed a possible new approach. We figured that if we could clone a hidden chart and remove dimensions, this would solve both problems. As side effect, it would also give us more control over how the dimensions display. Rob provided some cloning logic at the time (it writes and reads an XML properties file), but I never took the time to expand on what he'd done to get the whole thing working as a dynamic report.

With the version 10 bug and the likelihood that we'll be upgrading soon (we're still on version 9), it seemed time to finally tackle and solve this problem once and for all.

Attached is a working prototype. I've not yet installed this into production with any of our real applications. I wanted to get some feedback first if possible.

One concern of mine is this little bit of code:

sheetObjects = sheet.GetSheetObjects
set chart = sheetObjects(uBound(sheetObjects))

This appears to always give me the new chart. But does anyone know if I can count on that? Will the most recently created object ALWAYS be last in the list returned by GetSheetObjects? I can't find anything useful in the API guide, but perhaps I don't know where to look.

If necessary, I could get the object list both before and after I add the chart, and loop through both to find the new object, but that seems slow and complicated, and I hate slow and complicated.

Also, perhaps there's a simpler way to get a handle for the new chart. Anyone know of a good way?

Beyond that, a simple code review would be appreciated. I am NOT a Visual Basic programmer, and not an expert with the QlikView API. I'm particularly unhappy with my for loops and early exit approach, but I recoded one as a do loop briefly, and it was just as ugly. And there could certainly be other things that could be simplified, sped up, or preferably both at once.

Anyone see problems that this could cause in a production environment? For instance, can I be sure that the new chart will be built as a user object, or is there some way for QlikView to still butt heads with itself if two users are using the dynamic report at the same time? Is there anything in the XML file itself that would keep me from simply copying the XML forward into the production environment instead of needing to somehow reexport in production? Anything else?

Thanks for any feedback.

25 Replies
johnw
Champion III
Champion III
Author


Brian wrote:John, does this work for web users from the AccessPoint? If so, does the XML file need to be in the Access_Point folder?


I honestly don't know. I didn't take the solution forward into production on even a trial basis in a sample file because the performance was unacceptable for us. My guess is that yes, it would work with Access Point. And my guess is that as I coded it, the XML needs to be in the same directory as the distributed copy of the file. If I'm wrong, then probably the directory for the base file. Also, I'd guess that you could change that if desired by adding the directory structure to this line of the macro:

sheet.CreateObjectFromXMLPropertiesFile "DynamicChartBase.xml"

Not applicable

Hi John,

I was digging around for solutions to implement the dynamic charts perspective, based on user selected dimension and pre-caculated expressions.

I ended up with a macro you have posted on a related post ( rebuild() macro) and it seems to work pretty well on a QV 10 SR4 desktop. However when we try to implement it on the QV 10 Server it simply does not refresh the objects.

Some of the comments on this Community says that QV 10 Server can not handle macro VBScript code which is hard to understand and harder to accept.

And since we are depending on having available this user flexible feature that alows them not to depend on anyone to decide whatever dimensions and expressions they want to see in a table or chart, I would kindly ask you if it is possible to implement a solution like yours to achieve a Dynamic Report based on Used selected dimensions and expressions over QV 10 Server for Web users connecting through AccessPoint ?

Kind Regards

Joao Morais

rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

Note that in QV11 full support for conditional dimensions and expressions is available in the chart properties. I believe this makes all the previous dynamic report techniques obsolete. I recommend you look at moving to QV11 if you have a need to implementdynamic rep orting.

-Rob

http://robwunderlich.com

pat_agen
Specialist
Specialist

hi,

having taken part in this thread 18 months ago I thought it would be useful to give you some feedback from our experience.

I took John/Rob's original macro to production and it proved a great hit with our users. We had been using qv for 8 years by then and it was the first time our users got to manage/create their own reports

We are in the throes of migrating to v11 and the macro was no longer working. So taking an 80/20 approach we will propose a "dynamic" solution using the conditional dimensions/expressions Rob mentions. This coupled with bookmarks wil cover, I reckon , 80% of user requirements. It's fast, supported and if you have used the macro all the basic objects (template, dimension and expression lists) were already there.

For the remaining 20% - those users who aren't afraid of either right-clicking a mouse or making their way round the chart dialogue box - we are just going to have them to go a step further and do more work themeselves rather than have the semi-assisted mode they had with the macro before.

I'll take this opportunity to say a big thank you to John and Rob for all the help I've garnered from their posts over the last couple of years.

Not applicable

Ahoy!

I don't know if I got the question right...

or maybe this is radically easier to do on QV 11.2 but take a look at the attached QVW.

I used conditionals on dimensions, and in expressions, and created some inline tables in order to create the list boxes....

I used conditionals on calculation and visualization conditions in order to save RAM.

c ya around

Anonymous
Not applicable

Hello I'm new in qlikview and here's my problem is your solution an option for me? please give your advice and some guidelines
Thanks in advance
How to combine lines and bars in same chart without combo chart