Dynamic slice double click.

[Steve Rowe]

Does anyone know how I can double click a piece of hiearchy to expand it in a dynamic slice with VBA?

I'd guess there's too approaches, call whatever the TM1 procedure is that gets exectued directly or somehow spoof a double click in a cell that triggers the double click event.

Trouble is I don't know how to do either...
TIA for any thoughts.
Steve

[Steve Vincent]

Which version Steve? I'm on 9.0 and have all sorts of issues with them but i can still double click the rows and expand them. Columns you can't do, not even in 9.4 but they are working on it. For some reason that is harder than the row expand...

[Steve Rowe]

Well,
What I want to do is produce about 180 copies of the same report for different entities.
I wanted to do it with the dynamic report rolled up so creating the reports run quickly (since they would be retrieveing less cells) and then run some code that went through and expanded all the rows and columns.
But since I can't get print report and dynamic slices working together so this whole thread is rather pointless..
EDIT it's Version 9.0 sp3 (U9), not 9.1 SP3

[Steve Vincent]

never pointless, someone else might have a similar issue and find out the pitfalls as you are doing I'm not sure creating them consolidated would help actually - worth a try but i think when you expand them it'll end up taking just as long...

[ScottW]

What I want to do is produce about 180 copies of the same report for different entities ... I wanted to do it with the dynamic report rolled up so creating the reports run quickly (since they would be retrieving less cells)

I don't think you would get better performance with a dynamic slice as the row and column suppression is done locally at the client not on the server! All the data for the view is first piped to the client before the hiding takes place, I would expect this to be the time consuming portion of the view refresh.

I'm not sure if it would be possible (or the setup effort worthwhile) in your case but you could probably achieve the result you are after by using SUBNM to drive your row and/or column headers and either rebuilding the subset or switching to a different subset for each iteration of the report for each entity. Just a thought.

[paulsimon]

Steve

I have had similar issues recently. I still find the lack of zero suppression and dynamic drill down in TM1 disappointing. The Active Report in 9.4 is a step in the right direction, but even then it is limited to only rows, and you cannot expand an inner nested dimension without expanding it for all the outer nested elements.

My take for zero suppressed reports was to use the old VUSLICE Macro. I have found that this works very quickly. I would assume that this is because all the zero suppression happens on the server, and a pre-calculated set of results is delivered to the spreadsheet. There is no need to be a recalc after it arrives. There are dire warnings in the Help about VUSLICE placing a lock on the server, but if it does, we have not noticed any problems. It is certainly much faster than the previous approach of retrieving data for all possible combinations of 4 nested dimensions on the rows, and then zero suppressing by adding across and using an Auto Filter for zero.

The approach relies on there being a Private View for each user. The Macro uses SUBDEFINE to update a private subset within the View to change it to whatever the user or report generator has selected. The VUSLICE then slices the View to a blank sheet in the workbook. To get formatting, I can then link it to another sheet, or if it is simple enough just add the formatting to the slice.

We have an extra wrinkle in out application, in that they want it to only display valid combinations of Nominals and 3 sub-analysis segments, where this can vary for each combination of Entity and Revenue/Cost Centre. I am not sure that it is the best approach, but I created a merged Nominal and Sub-Analysis Dim, and a merged Entity and Centre Dim. I then defined a subset on the Nominal-Sub-Analysis Dim for each Entity-Centre combination, giving the valid Nominal-Sub-Analysis combinations for each Entity-Centre. That then allows us to generate reports and budget templates that only contain the Nominal and Sub-Analysis combinations that are relevant to each Entity-Centre combination. That cuts things down so much that zero suppressing doesn't make much difference except at the consolidated levels.

Regards


Paul Simon

[ScottW]

Nice one, VUSLICE is certainly fast.

[Steve Rowe]

In the end I went with a QuDefine followed by Qusubset for the row and column dimensions. I then referenced the Qusubset with a subnm in the workbook, recalc'ed and took a copy of the spreadsheet. I had all this working nicely in my dev environment but could not get it working in the same version of the software but on a 64 bit machine. See my other post http://forums.olapforums.com/viewtopic.php?f=3&t=633

So I scrapped that work too and then just sliced all the data I wanted out in one big chunk, with the entity as a row element. I then pointed a pivot table at this data set and set the entity as the page tab with the "Show Pages" option set. This gave me a page per entity that was fully zero suppressed very quickly with no additional coding required.

This is what I should have done in the first place as it only took 5 minutes to do and required no coding at all. I guess I'd railroaded myself into applying a pure TM1 solution rather than just the best / quickest solution.... It is annoying that the basic excel report building functionality is not there for TM1 yet, the SPF print report I remeber being very good and coping with everything, I still don't really understand how we ended up with the Print Report functionality we have when we they have the SPF example of how things should be done.

Cheers,