in one report I have implemented, there is a date-time-stamp. Using the FLOOR and DATE functions I have in the script created a date-field and used that in a listbox in the GUI. Using the same method, I have also generated listboxes for the week, the month and the year - all the fields are created in the script, that way the statusbox displays the proper selected values.
Now I'd like to have all four dimensions - date, week, month, year - in my pivot-table, but if I put them side by side, it becomes too big - I'd like to make it dependent on the listbox that has been used - display the date when the date_listbox is used, display the week/ month (and necessarily the year) when either of those listboxes is used.
I guess I can use a formula to conditionally display one of several possible dimensions. One function coming to my mind that might do just that is CASE. The question is only, what command do I need to identify which object (which listbox) the user has activated?
You can create a group on the Dimension tab of a charts properties window or on the Groups tab of the document properties window. You can add your dimensions to the group and then use the group as a dimension in your pivot table. You probably want to use a cycle group instead of a hierarchical group even though the date dimensions are hierarchical.
You can use the getcurrentfield function to get the active field of the group to show/hide the relevant listbox. For example =if(getcurrentfield([MyGroup])='year',1,0) for the conditional display expression for the year listbox.
There are more ways to skin this cat, but defining and using a group is the easiest solution imho.
Edit: using the group as the field for the listbox too is probably the way to go. It's a lot easier than showing/hiding listboxes.
thanks a lot! I was thinking of a CASE or IF-clause, but a cyclical group will do just fine I guess. There's no point in hiding the listboxes as I want to make the diagram itself read-only - so I need to do this the other way round: Depending on which one of the listboxes was used, I'd like to display one or other of those dimensions.
I'll see if I can do that - a cyclical_group is a possible solution, but then the user would have to change two things: a) switch to another dimension within the group and b) select a value on the corresp. listbox.
