Dynamic Report with User-Selected Dimensions and Expressions

[johnw]

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.

[johnw]

PatAgen wrote:The macros fire off when selecting/changing the two list boxes and when opening the document via the plugin but not in the desktop but not when recalling your bookmarks. Is this just a v8.5 thing?

Ah, I'd forgotten about that bug because I don't use bookmarks (this is not a recommendation, merely an observation). The bug is still there in version 9 sr6 and version 10 sr1. The only workaround I know of is clunky, which is to include a "rebuild chart" button.

PatAgen wrote:Is there an alternative to the uBound(sheetObjects) proposal? I'm sorry but I can't bring anything to the table but my time and our setup, ie I have no VB knowledge and my grasp of the API is sketchy to say the least.

Yep. I can get the list of objects before and after I add the new one, then loop to determine which one is new. It's more robust, just slower. I'll be doing that.

[johnw]

John Witherspoon wrote:Since it doesn't seem to be documented, and the functionality here has changed over time, we can't count on the ubound() technique continuing to work in the future. Also, it would be nice to have a template that works for earlier versions of QlikView for people who haven't upgraded. I'll implement a loop to compare before and after to get the right ID, and post when I have that working.

I appear to have the looping approach working. I start at the end of the array of new objects, so as long as the new chart is at the end of the array (as it seems to be in v9 and v10), this will limit our looping to the inner loop, just checking the new chart's ID against all previously-existing objects. I'd like to know if it works in v8.5 where the new chart is NOT at the end of the array, of course.

[johnw]

Rob Wunderlich wrote:A couple years ago I submitted a suggestion to the old product enhancement system to have CreateObjectFromXmlPropertiesFile return the id of the new object. It looks like the suggestion didn't get carried over to the new ideas system. It may be worth requesting.

Looks like someone else suggested it back in 2009:

https://eu1.salesforce.com/ideas/viewIdea.apexp?id=087200000008Sx6AAE

I promoted it. That takes it to 20 points, so I wouldn't expect this any time soon. At least we have a workaround.

[pat_agen]

Hi John,

so far so good. Working out of the box in the desktop with 8.5. Will test more on the server tomorrow my time.

[rwunderlich]

John Witherspoon wrote:
Looks like someone else suggested it back in 2009:
https://eu1.salesforce.com/ideas/viewIdea.apexp?id=087200000008Sx6AAE

Thanks. That is my orginal submission. The submittor name "Bluewolf Consultants" is what everything got copied over as. I promoted it as well. Now it has 30 points 🙂

[johnw]

Bad news. Performance is absolutely horrible in version 9 SR6 for the smallest of the three applications I have using dynamic charts. It took 34 seconds to create a chart with a single dimension with a single possible value and no expressions. Using message boxes as a timing mechanism, I can see that it takes a few seconds to build the chart from XML, another five or ten seconds to remove the 20 or so expressions, and the rest of the time to remove the 100 or so dimensions. Performance isn't going to be acceptable for any live applications at our company unless someone sees major performance mistakes in my code. The latest version is attached.

The approach I'm currently using in the live applications (adding dimensions, enabling/disabling expressions) is much faster in practice, probably because there are usually only a few dimensions and expressions selected.

Oh, I also hit cases today where the new chart was NOT the last in the list of objects, so the extra looping code is necessary even for versions 9 and 10 of QlikView.

[pat_agen]

Hi John,

I used your latest build and get different results depending on the connection between the desktop with the plugin and the server.

My test setup is a template with 40 dimension fields and 30 expression fields.
I tested on a reduced data set (<6 000 records) and a bigger one (>1.6m records).

I did the test on my laptop (but not on the bigger dataset), on the server desktop via rdp and then with the plugin. Created three reports with varying numbers of expressions and dimensions but always on the low side (ie less than 7 of each).

I set up the message boxes and took the three times for cloning, expressions then dimensions.

The two desktop tests gave the same results more or less on each report and each dataset. These were around 1 sec for cloning, 6 secs for expressions, then 2 for dimensions. So around 10 seconds.

Via the plugin my results echoed yours, again regardless of the number of elements nor the size of the dataset. Now I got 5 secs for cloning, 17 for expressions and 10 for dimensions.
I thought this was less catastrophic then your post suggested and have tried adding a text box which updates with progress so that the user see something else than the timer.

To test this I rang a colleague on site (I am working remotely via vpn). The colleague did the same tests as myself via the plugin and got the results I was getting on my laptop using the qv desktop.
We let it a while and ran the test again. This tme trying out the concurrency (which i take it is what your WaitForIdle statement is meant to work around).
My colleague again got the same return time, mine was even slower. My connections are pretty slow this afternoon (there is a big cricketgame going on in India today which might explain things slowing down! ).

Do these results make any sense, ie a user sitting inside our firewall getting better return times? Where is the macro being run?

Personally when I saw your post 30+ seconds did not look that painful to me. After all we are not comparing this to running a precanned report but helping the users get round the bottleneck of having IT tweak some report for them, which, believe me when all best practice procedures are followed, is guaranteed to take more than 1 minute.
As for sub 20 seconds it's enough to make me want to test further and get some more users invloved.

[johnw]

What I'm comparing 30 seconds to is the previous iteration of the dynamic charting. The same test using the previous code takes about 1 second. I've seen bigger charts take a handful of seconds, but that sort of range of response is what my users are accustomed to. I don't think I can get away with changing that to 30 seconds. So in my case, I'll probably have to fix this with a workaround like Karl Pover suggested.

A text box with progress is a good solution if you're otherwise happy with the approach, and just want your users to realize that the program is still working. You could even make a simple progress bar by calculating a percent done as you go. Maybe I'll experiment with that. The server should be faster than my machine, so maybe if it's more like 10 seconds, waiting with a progress bar will be acceptable. If it works, I'll copy it back to the template, even if the progress bar is meaningless in the template itself since it's so fast.

So far as I know, the macro is executing on the server, so I don't know why a user sitting inside the firewall would get better return times other than any additional time taken on communication through the firewall.

[johnw]

John Witherspoon wrote:You could even make a simple progress bar by calculating a percent done as you go. Maybe I'll experiment with that. The server should be faster than my machine, so maybe if it's more like 10 seconds, waiting with a progress bar will be acceptable

The progress bar is a failure. It looks pretty good, but it only renders if I pause and wait for the chart to catch up by doing a WaitForIdle after every update. Even then, it's not fully reliable, rendering now and again during the progress instead of after every step. Plus, all that waiting for idle more than doubles the time required to create the dynamic chart.

[Anonymous]

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