TM1 Forum

Is there a way to publish an Excel spreadsheet out through the web that has zero suppression on it. For Example

Country UK

Product1 10
Product2 0
Product3 20

When I change the country to US and there are numbers in Product 2 and 3, they appear but product 1 filters out.

Any ideas?

Depends on your version. For 9.1, there was dynamic slices, which did that, but wasn't amazingly stable. For 9.4, there is (now) Active Forms, which does that. It's quite new though and we don't (yet) recommend it for production use.

I think Ben Hill did some pressure testing on active forums and was impressed. However, I do recall the InSpreadsheet browser performance being better (not really relevant since you asked about Web, but threw it out there anyways)

Create a Subset via MDX which filters Measure <>0, then use this Subset in a view to snapshot your Excelsheet

If Rogers approach is too static (when having multiple columns and rows that should be zero-suppressed) use an action-button to run a process which creates the necessary subsets. simply name the subsets after the user calling the process (using TM1USER on TI side and TM1USER(server_as_string) on Excel side) generates some nice subsets - you can't have enough of those!
I think you need 9.1+ to have an Action Button and TM1USER (on either side )
afaik is TM1USER as TI-constant (well, it isn't that constant, is it?) not documented and might stop working in later versions (which would make me a very sad developer since I would have to add extra parameters and going crazy sooner or instantly )

This leads me again to an incredibly stupid idea I have and want to do - insert the name of the created subset in a garbage-collection dimension and having a chore running deleting the referenced elements every now and then ... who needs a clear and readable message log anyways?

Lukas

Hmmm

I've always used the approach of having some kind of standard naming prefix, followed by user name, TI process name, datestamp, whatever, to make sure the subset name is unique for those instances where you can't create and destroy a subset in the same process, that is the returned subset needs to be used by a user say from a drill process or action button to build a subset from parameters defined on a report. This works fine combined with a TI to run periodically to clean up the subsets by searching the directory structure for .sub files matching the starting prefix and deleting them. However, Lukas's idea of a "garbage dimension" is very interesting, would certainly simplify to process of cleaning up unwanted objects. Will have to think about this and perhaps try it out.