April 26, 2005 at 6:00 pm
I have a Category dimension on my cube (MS SQL Server 2000) with 770 members. But I want to use only 20 of them as columns in my pivot table in Excel 2003.
This might work if I could just list the categories I want to have visible as columns. But instead it seems that I have to use the TreeviewControl and list the ones that should be hidden. In my case, that means listing 750 items.
Is there a way to do this? I'm running into an error that says I have too many line-continuation characters.
It would be nice if there were a way to loop through the items in the column-cubefield, and set to hidden all but my selected 20.
Got any Ideas?
April 28, 2005 at 12:58 am
Depending on whether you can change your user environment, this might not help, but have you considered/tried the Excel/OFfice Accelerator for OLAP? It basically gives you more powerful report building capabilities within Excel using AS2K. One of the major benefits is not being limited to a tight hierarchical approach to showing members (ie can show children without parent).
Steve.
April 28, 2005 at 8:34 am
Hey stevefromOZ,
Thanks for the info. In another incarnation that may be the answer. But for now, I can't change the environment. Got any ideas on that basis?
April 28, 2005 at 5:29 pm
I can't think of much, other than writing some VBA to either i) set the MDX query string, or ii) leave the string as is and set the (say) rows to only those members your after.
An alternative would be to mod your dimension by adding another level and slotting the 20 as children of a member called "Waht I really want" and the remaining 720 or so belong to "other stuff the boss wants in here".
Similar to above would be to create a named set (which would work well) *but* I don't know if Excel supports using these (pretty certain it doesn't).
Steve.
April 29, 2005 at 11:32 am
Interesting ideas, thanks. I'll investigate.
Jennifer
May 1, 2005 at 10:00 pm
Jennifer,
Getting back to your original problem, how weere you trying to set the visible columns? I ran up a test using one of our cubes tat has 25k+ members (@ the lowest level, 2 levels in total excl 'all'), the only issues I ran into were that I owuld have to individually select the 20 members I wanted plus excel's column count is limited to ~16k cols. Were you trying to create some sort of statement in VBA (hence the line delimiters message)?
Steve.
May 4, 2005 at 7:46 am
Steve,
Thanks for you help on the 28th. But the boss says I can't change the cubes, and I don't know how to change the MDX the pivot table uses. Also named sets won't work for data distribution constraints.
What I'm doing is providing a template with a pivot table that the user modifies. I save the changes to another xls file. The template is not saved when the template closes. When the user wants to see a certain data set, I open the template and load it up from the specified saved info in the other xls.
I've got it working well except for opening the template with only 20 of the 770 column items showing. And when the user selects different column items, then I've got to record those changes in the other xls so I can make them appear in the template when the user needs to use that data again.
I tried recording a macro when I changed the columns using the dropdown in the pivot table. That's when I get the "too many line-continuation characters" message. Following the format of the macro means I need to use 750 line-continuation characters, but Excel chokes recording the macro when I get to about 20.
When I change the columns without recording the macro, Excel does the job without complaining. I don't know if that means there is, or there is not, a way to do this other than the way the macro demonstrates.
Thanks again,
Jennifer
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply