Automate dimension/measure creation for ad hoc report

[Anonymous]

Last year jason.michaelides posted a really helpful blog article entitled "Customisable Straight (and Pivot) Tables - more food for thought". In it, he describes how to make an ad hoc report displaying the first n-dimensions and measures selected by a user from a list box of dimensions and measures. The developer must create a placeholder for each of the n-dimensions and measures using the FirstSortedValue function. So, if the end user needs the ability to add 10 dimensions/measures, the developer must create 10 placeholder dimensions/measures.  From Jason's post, the dimensions and measures are created in a straight/pivot table as follows:

$(='[' & FirstSortedValue(%Dimension_Name,%Dimension_Position,1) & ']') for the first dimension

$(='[' & FirstSortedValue(%Dimension_Name,%Dimension_Position,2) & ']') for the second dimension

etc etc and

$(='[' & FirstSortedValue(%Measure_Expression,%Measure_Position,1) & ']') for the first expression

$(='[' & FirstSortedValue(%Measure_Expression,%Measure_Position,2) & ']') for the second expression

etc etc

For the enable conditions, Dim1 just needs GetSelectedCount(%Dimension_Name) >= 1, Dim2 just needs GetSelectedCount(%Dimension_Name) >= 2 etc and Expr1 just needs GetSelectedCount(%Measure_Name) >= 1, Expr2 just needs GetSelectedCount(%Measure_Name) >= 2 etc

My application requires the end user to be able to add >100 dimensions/measures to the ad hoc chart. Is there a way to automate the process of creating the placeholders so I don't have to create >100 placeholder dimensions/measures? Moreover, is there a way for the placeholder dimensions/measures to by dynamically created so, for instance, if only 12 dimensions/measures are selected by the user, only 12 dimensions/measures are created in the chart?

Thanks!

Liz

[Jason_Michaelides]

Hi Liz,

This can be done with a macro and there are some examples in this forum from a long time ago (there was one in particular but I can't remember who posted it right now).  However it's generally advisable to avoid doing it this way - apart from anything else if you use a macro to create a new chart each time then your .shared file will grow when it may not have to.

I know it's a hassle to set up the first time, however try setting up all the dims/measures as described in my post and see how it performs when only a few are selected (there will be an overhead to evaluate that many conditional expressions, but they are simple and this may well perform OK).

Two points:

1. There is a significant upgrade to this solution I developed with a colleague JoeSimmons‌ that avoids the need for InputField and allows even more flexibility and extended use of bookmarks.  You can see it here (Downloads from Session 4 section). Check it out.

2. Do you REALLY have to allow your users to create a table with over 100 columns?  How is that useful? If so, maybe a different approach is needed that converts columns multiple fields into a single field with CrossTable() then uses the new dimension pivoted across the top? See an explanation here (Session 2 downloads - Handling Wide Datasets)

Hope this helps,

Jason

[bme]

It's not exactly the same method but it's similar. I had the same issue so I created a small macro to create the table for me. The one already saved has 300 dimensions and metrics. It's probably a bit much for most scenarios but it was needed in my case. I then just copy the objects into the final QVW where I'm going to use it and make a copy of the Ad Hoc file to feed in the expressions.

[Anonymous]

Thank you for your response, Benjamin! That's a great idea. I was trying to avoid using macros because of performance requirements, but I will only have to run this once, so it's a great solution.

Thank You!

Liz

[Jason_Michaelides]

Neat!

[Anonymous]

Hi Jason,

Thank you for your detailed reply. I ended up implementing your second solution, and it worked really well!

I have a new issue as a result:

Some of the pivoted columns are currency and some are normal numbers. Do you know if there is a way to conditionally format the contents of the pivoted dimension so only some of the resulting columns are formatted as currency?

Thanks!

Liz

[Jason_Michaelides]

Hi Liz,

We did add that on actually. I'll try and dig it out for you - you set a default in the script/expression source then the user can overwrite them and bookmark the new format codes. The downside of implementing variable formatting however is that on export to Excel all formatting is lost. Excel picks them up as numbers no problem but with all decimal places, no currency formatting etc.

Cheers,

Jason