Create table based on Fields selected in List Box

[Anonymous]

Hello all,

I swear that I had seen this as a post sometime ago, but I could not find it with Search (my lack of being able to come up with a key word, I suppose). Does anyone have any macros that will allow a user to make field selections in a list box and then have a table created with those selected field names? I am looking to get rid of the constant requirement of calls to add a certain field to a report. We use the plugin, so it must be able to work in both the developer and plugin environment.

Thanks,

JS

[#2]

Hi

do you have this one in mind?

Regards

Juerg

[Anonymous]

This is definitely traveling down the path that I want to go. This will give me a good start.

In your document, you talk about it crashing the system with more than one person attempting the dynamic reporting, does this happen often? My thought pattern was to create a table on the fly using the create "CreateTableBox" macro function and assign the fields based on selection in a list box (no aggregation). Would this cause me an issue if two users tried to create tables at the same time using the same Table ID?

Thanks for you help here, saves me alot of testing!

JS

[johnw]

I built the template, so I'll try to answer, and attached is my most recent version. No, the crash doesn't happen often. In fact, I haven't seen it in a couple years, I think. However, very few users use this functionality, so that probably doesn't prove anything.

I've tried creating the table on the fly. It was a COMPLETE PAIN and very, very slow. Unacceptably slow. Still, give it a shot. Maybe it will work for you. It DOES give you a lot of control. It's just a whole, whole lot of macro code. And unless you're giving the table an ID yourself, I believe that two different users would have two different table IDs, so there wouldn't be conflicts to crash it.

Another approach we've been looking into, but somehow never found the time to really do it, is to CLONE a hidden table and then remove all of the dimensions and expressions you don't need. Since each user would then have a different table ID, there wouldn't be conflicts to crash it.

Another approach we've been looking into involves the ability in version 9 to conditionally show columns. I'll attach that example as well. Unfortunately for our purposes, data is still broken down by hidden dimensions, so I think we'd still need a macro to manage dimensions, which still exposes us to all the problems of macro use, so it doesn't seem worth "upgrading" our actual applications.

If you only have a few dimensions to choose from, it might be practical to have variables with drop down lists of possible dimensions. Then if you have, say, three possible dimensions, have separate charts for one, two and three dimensions selected. That would probably combine well with conditional show of expressions to create a macro-free version of this, which would be desirable due to the drawbacks of macros.

[johnw]

Here's the version using conditional show of columns. It requires version 9, though.

[Anonymous]

Thanks John! I had already found out creating a table was kinda slow via macro. It can do 1 - 5 columns no problem, but appears to slow down after that. I will take a look at your code and let you know if I have any other questions.

Thanks for the response and your time. It is greatly appreciated!!!

JS

[#7]

Hi John... The dynamic table looks great!

Did you have any luck getting around this??

"This works fairly well for expressions, but even when a dimension is hidden, the data is still broken down by it."

Thansks, Mike

[johnw]

I haven't attempted to do so, and honestly, I suspect it just means that approach won't work. It appears intentional that the data is still broken down by those columns. The columns are merely hidden and not disabled. To get what I need, I'd need a "conditional enable" for the dimensions. I'm not really expecting them to add that feature any time soon. I suppose I could at least suggest it on the customer portal, since it seems like a much cleaner way of handling dynamic reporting (for straight tables at least), and since dynamic reporting comes up frequently.

Looks like there's already a suggestion for a conditional enable of expressions. This is similar, but for dimensions. I think I'll add it as a new request instead of just commenting on the expressions request, but make reference to the expressions request in mine. OK, I added a new request:

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

Conditional Enable of Dimensions

I would like to see an "enable" property added to dimensions, as it is for expressions, and for this property to allow a condition (perhaps a radio button with enable, disable and conditional). This perhaps could be merged with the idea "condition enable status of expression".

https://emea.salesforce.com/ideas/viewIdea.apexp?id=087200000008SuY&srPos=5&srKp=087

And it is similar to the requests (now satisfied) to be able to conditionally hide expressions and dimensions. But I thought I should propose it separately since it IS a different request, even if very similar. In addition to conditionally enabling expressions (which at least appears to be handled now by conditionally hiding them), I would like to be able to conditionally enable dimensions.

The "hide" functionality is not what I'm talking about. First, the hide isn't available on some types of charts where I'd want to use this, such as pivot tables, though obviously that could be addressed without a conditional enable. But second, when you hide a dimension, your chart is still broken down by that dimension. The dimension isn't displayed, but it is still being used. That seems like the correct functionality for "hide", but is not what I want. I want to be able to conditionally disable the dimension completely, making it as if that dimension were no longer part of the chart.

Having a conditional enable for dimensions combined with the conditional hide (or enable) of expressions would allow a clean solution to a long-standing problem, an often-requested type of chart. People want to be able to build dynamic charts where the USER can add and remove dimensions and expressions, such as by selecting fields from a list. I've implemented this in several live applications using a macro, and posted this solution to the forum on a number of occasions. However, it's a big macro with traditional macro weaknesses, and has been prone to crashing over the years. If instead every dimension and expression could just detect if it was selected, like with sum(Dimension='Customer') as the condition, then the macro could go away, and the charts should appear faster and be much more reliable.

A robust solution for a common forum request seems like a good idea to me.

[christian77]

Hi John:

Your code is much better than mine. And much simpler. That´s why you are top 10 member.

The thing is that neither mine nor yours work in QV 10.

What do we do?

[#10]

Could you use a bookmark to save the selections of a table? Then the user wouldn't have make any other selections.