Checking if view is empty from Excel

zerofeng111

Any suggestions to the below?

I'm currently calling a TI from excel to change the elements contained in some subsets based on some search fields users are inputting to. These effectively generate a pretty decent search function in TM1 to generate a view. After this, the view utilising these subsets is then exported using VUSLICE back into Excel.

Requirement for the user to select more than one search field means that they can make a selection that results in an empty view. Once this is VUSLICE'd, I can obviously identify that there's not data and return a message to this effect but the act of VUSLICE on an empty view is the unfortunate circular reference that you get whenever you try to slice out an empty view.

So basically, although it doesn't affect the functionality of the file, it's annoying and I would like to check if the view is empty prior to exporting. My immediate thoughts are to run a second TI to count the number of processed records (simple counter on data tab) and pass back to excel if this is zero to identify the situation but wondering if I'm missing something simple which is a bit more elegant.

Any thoughts?

TIA

楼上MJJ

Is it the fact their selections may have no data or that they forget to pick at least one value for each dim? If its the latter than a SUBSIZ check for zero could be easier, otherwise i think a TI to count records is probably the simplist plan.

jinanday

Na, I've got a check on the file before it calls to check that the elements exist before it tries to execute.  It's when they select more than one field that then returns no values (because the PO / Supplier that's selected doesn't have any info for example).

Looking at a cube with the }Clients dim to record a boolean in the cube once it processes the first line then quit the TI but trying to avoid calling any more TI's if it can be avoided.  Not the end of the world but if it can be avoided, would rather do something from excel's end.

Any other suggestions?

xiaohei3721

exactly what I would do, create a TI which assembles the view with zero suppression on and put a counter on the data tab and return an error if the view is empty, catch that error before you do the vu slice and that should work fine.

hiba010203

This came up about a month ago I think. Call a TI which processes the view ...
viewtopic.php?f=3&t=3865#p17391
If you just want to test the difference between an empty view and one that contains data my suggestion would be to increment a counter and bail out when the counter exceeds 1. that way a positive test won't get stuck processing the view if it happens to contain a lot of data.