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.
.png "Luminary Alumni"){kind=icon}
