topic Dynamic Report with User-Selected Dimensions and Expressions in QlikView

Dynamic Report with User-Selected Dimensions and Expressions

johnw — Fri, 25 Mar 2011 19:29:35 GMT

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.

Dynamic Report with User-Selected Dimensions and Expressions

pover — Fri, 25 Mar 2011 20:25:16 GMT

I am not a VB programmer either, but I'll give your solution a test run. In the meantime, I found a workaround for the v10 SR1 bug. After adding each column to a tablebox or a straight table, I run the following subroutine:

Sub AddColumn_Workaround (Object_Type,Name)

set vTB = ActiveDocument.GetSheetObject(Name)

if Object_Type = "Straight_Table" then
'In v10 SR 1 had to change a property of the table to refresh the table
'Begin
set cp = vTB.GetProperties
if cp.TableProperties.ShowSortIndicators = true then
cp.TableProperties.ShowSortIndicators = false
else
cp.TableProperties.ShowSortIndicators = true
end if

vTB.SetProperties cp
'End

else if Object_Type = "Table_Box" then

'In v10 SR 1 had to change a property of the table to refresh the table
'Begin
set cp = vTB.GetProperties
if cp.Layout.ShowSortIndicators = true then
cp.Layout.ShowSortIndicators = false
else
cp.Layout.ShowSortIndicators = true
end if

vTB.SetProperties cp
'End
end if
end if

End Sub

Dynamic Report with User-Selected Dimensions and Expressions

Oleg_Troyansky — Fri, 25 Mar 2011 20:35:39 GMT

John,

I developed a number of macros for "dynamic" reports, and I found that all of them are a bit flaky... I found that in most cases adding or removing dimensions and expressions is more troublesome than replacing a definition for an existing Dimension/Expression.

The whole idea of using macros all together is a hazard for a production environment, and it's well known, no need to dwell on that...

If ever possible, I think using show/hide conditions (except for Pivot Tables, of course), and using variable with $() - expansions for dynamically re-assigning the definitions for Expressions and Dimensions is by far "cleaner" than tinkering with Macros. You are a bit more limited with your choices (number of Dimensions, etc..) but it works cleaner.

Another option, of course, is to educate users on creating charts and to enable collaboration, but that a whole different story...

Specifically about your question about ubound - it sounds totally logical that the last object in the stack is the newest one, I can't imagine how else would the objects be sorted, but it doesn't seem to be documented in any way.

good luck, whichever way you chose.

Dynamic Report with User-Selected Dimensions and Expressions

johnw — Fri, 25 Mar 2011 21:03:58 GMT

I have shown a couple of users how to create their own charts. But I think most of my users have enough trouble with the concept of simply picking from list(s) of fields on the screen. I just don't see most of them dealing with custom user objects.

Reassigning columns with variables sounds intriguing, but won't substitute in this case. Just something for me to keep in mind for applications that need some dynamic control, but not as much.

And yes, I know that I'm just asking for trouble with big, complicated macros like this. My suggestion on the customer portal was that they allow us to conditionally enable and disable dimensions.

https://eu1.salesforce.com/ideas/viewIdea.apexp?id=08720000000HMz1

It appears that my idea was merged with "dynamic hide of dimensions and expressions". Hiding and enabling are different concepts, though, so I'm not sure it should have been merged. Hidden dimensions still affect how the chart is broken down; they simply don't DISPLAY. But I agree that the concepts are similar, so I guess I can understand how a QlikTech employee either got confused and considered them the same thing, or understands the difference but thought it all belonged together in a general category of giving us more control. On the positive side, that means it has a lot more points than otherwise, so I suppose I shouldn't complain.

If we can conditionally enable dimensions, then I no longer need a macro. Every dimension would have an enable condition that it is in the selected values. Every expression would have a show condition that it is in the selected values. Seems like it should work.

Maybe we'll get something like that some day. Until then, I think I'm stuck with long, complicated macro code, and dealing with bugs every few releases. I'm just hoping that the new approach will be less bug-prone than the old approach. Hard to say, though.

Dynamic Report with User-Selected Dimensions and Expressions

johnw — Fri, 25 Mar 2011 21:09:34 GMT


Karl Pover wrote:I found a workaround for the v10 SR1 bug.

Interesting idea. Makes sense, given what I saw with opening the properties and clicking OK. Not sure I want to mess with the sort indicators, but I probably have a lot of choices for similarly fairly harmless properties to modify if we have to move quickly to fix it instead of completely recoding and retesting everything.

Dynamic Report with User-Selected Dimensions and Expressions

rwunderlich — Sun, 27 Mar 2011 05:04:58 GMT


John Witherspoon wrote:
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?

It almost always works for me, but like you I've never seen any doc that promises it will continue to work. The only time I've seen the ubound technique not work is if a doc is saved with a 9+ version, it will not work if used with 8.5. Object BU01 will be returned instead of the new CHxx object. It's "fixed" if you resave in 8.5. So for example, your posted example will not work if downloaded as is and opened on 8.5.

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.


John Witherspoon wrote:For instance, can I be sure that the new chart will be built as a user object

Yes, it will always be built as a user ("collaboration" (v9) or "server" (v10)) object. This is great as modifying the properties of a document object wth a server macro was always explicitly prohibited in the Ref Guide. However, SetProperties is documented as being supported for Server objects. From the V10 Guide:

"Note :Layout operations acting on the properties of Server objects are supported."


John Witherspoon wrote: 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?

I would think you can export the file once and reuse. You may want to reexport everytime you change SR.

I didn't take a hard look at the looping code, but I know you've spent a lot of time with this over the years so I'm sure it's pretty good.

Dynamic Report with User-Selected Dimensions and Expressions

Anonymous — Sun, 27 Mar 2011 13:14:56 GMT

John,
My 2 cents regaring your "...since we wanted to leave the chart hidden until we'd finished adding ALL the dimensions to it"
I prefer to disable calculations at the beginning of the macro before starting process of adding/removing dimensions, and allow calculations at the end of macro. No need to hide.

Dynamic Report with User-Selected Dimensions and Expressions

pat_agen — Mon, 28 Mar 2011 09:03:11 GMT

John,

I am very interested in this has been a long standing objective both for our users and ourselves in IT. We've reached a point with a couple applications where even if 80 - 90% are happy just taking the canned charts available the others always want that slight variant. As these are the keen users it can add up to lots of requests, lots of duplication etc. I think you know the story.

We have a version of your idea - built for us by a consultant back in version 7, today we are on v8.5. The macro code adds and removes dimensions and expressions one by one. We've never deployed it aggressively, But given the architecture - the adding etc. is done on the template, your object CH01 - the potential for conflict if there is concurrent building is apparent. Also the addition of one element at a time is clunky and 8.5 tends to add new dimensions to the right of the expressions (must be one of the bugs you mention).

To cut this long story short I'm happy to test your code, albeit with an older version.

First results I can confirm what RobWunderlich said earlier. The

set chart = sheetObjects(uBound(sheetObjects))

does not give you the latest object when using 8.5. Sometimes it brings back the BU01, but I've also got back both listboxes. The "close" comand works fine because it makes whichever object is brought back dissapear from the screen if you ignore the macro functionality failed warnings and plough ahead.

The new chart is created fine just from the copy/pasted xml file. It looks, via the plugin, like a user object because contrary to the CH01 object, I can access the properties of the created object.

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?

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.

Dynamic Report with User-Selected Dimensions and Expressions

johnw — Mon, 28 Mar 2011 15:39:48 GMT


Rob Wunderlich wrote:It almost always works for me, but like you I've never seen any doc that promises it will continue to work. The only time I've seen the ubound technique not work is if a doc is saved with a 9+ version, it will not work if used with 8.5. Object BU01 will be returned instead of the new CHxx object. It's "fixed" if you resave in 8.5. So for example, your posted example will not work if downloaded as is and opened on 8.5.

OK. 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.


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.

Makes sense. I'll add it as an idea on the customer portal.

Dynamic Report with User-Selected Dimensions and Expressions

johnw — Mon, 28 Mar 2011 15:48:16 GMT


Michael Solomovich wrote:My 2 cents regaring your "...since we wanted to leave the chart hidden until we'd finished adding ALL the dimensions to it" I prefer to disable calculations at the beginning of the macro before starting process of adding/removing dimensions, and allow calculations at the end of macro. No need to hide.

I think we may have done that at one point when we were having problems with hidden charts. I suppose that instead of hiding and exporting, I could disable calculations and export, and only then hide it to get it off the screen. After cloning, enable again at the end of the script where I currently unhide. Since I've had more trouble with hidden charts than with disabled charts, that may be a good change for me to make. Though it's difficult to predict what functionality will have bugs in the future from what functionality had bugs in the past, and that's a couple extra steps (thus points of failure) when making changes to the base chart. Hmmm.

Dynamic Report with User-Selected Dimensions and Expressions

johnw — Mon, 28 Mar 2011 16:16:12 GMT


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.

Dynamic Report with User-Selected Dimensions and Expressions

johnw — Mon, 28 Mar 2011 17:35:09 GMT


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.

Dynamic Report with User-Selected Dimensions and Expressions

johnw — Mon, 28 Mar 2011 17:36:45 GMT


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.

Dynamic Report with User-Selected Dimensions and Expressions

pat_agen — Mon, 28 Mar 2011 20:24:29 GMT

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.

Dynamic Report with User-Selected Dimensions and Expressions

rwunderlich — Mon, 28 Mar 2011 20:42:30 GMT


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 🙂

Dynamic Report with User-Selected Dimensions and Expressions

johnw — Tue, 29 Mar 2011 22:21:18 GMT

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.

Dynamic Report with User-Selected Dimensions and Expressions

pat_agen — Wed, 30 Mar 2011 14:19:31 GMT

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.

Dynamic Report with User-Selected Dimensions and Expressions

johnw — Wed, 30 Mar 2011 17:18:59 GMT

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.

Dynamic Report with User-Selected Dimensions and Expressions

johnw — Wed, 30 Mar 2011 21:37:21 GMT


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.

Dynamic Report with User-Selected Dimensions and Expressions

Anonymous — Mon, 18 Apr 2011 20:37:00 GMT

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