TM1 Forum

Hi,

I am having an issue (seems to be a bug of TM1 10.2.2 FP7). I have a websheet where I have an Active Form and some Drop Down cells that I use to compose the MDX To be used in my TM1RPTRow.

When the MDX returns no Elements, then the Active Form does not work. When I run this from Perspectives, excel crashes. When I run it from TM1Web, it just takes forever to build the Websheet and if I monitor it from TM1Top, TM1Top shows running state with function SubsetElementFirstIndexByNameGet_NR, which takes a few minutes and then instead of returning an empty active form, it returns all the elements of the dimension.

This was never an issue, and If I execute my MDX Statement directly from Subset Editor, it does not through any error, and it just does not return any element (es expected)

The Websheet works fine if the MDX Returns at least one element.

Has anyone ever experienced this?
Thanks

Hi

Are you sure that the MDX is not invalid rather than returning zero elements?

Typically in TM1RptRow you need to define a named subset and then you define an MDX expression that should give a subset of the elements in the named subset. The named subset will typically also be an MDX based subset that either gives the full hierarchy in the dimension or the list of base level elements. If the MDX is invalid then TM1RptRow will default back to showing everything in the named subset. Particularly if this is the full hierarchy and you have nested dimensions on the rows, this can lead to a very large retrieval.

One way to test this would be to modify your MDX to do a UNION with an element that will never have data but will guarantee that your MDX does return at least one element. Our dimensions have zzSelect element which will never have data. In case you don't know the MDX for a UNION is
{UNION( { [MyDim].[MyElement] } , { mdx expression } ) }

Regards

Paul Simon

I would check the composed MDX statement. Sounds like the MDX expression built from your list of dropdowns may be incorrect.

I would suggest take a copy of your websheet, remove the active forms section, and output the MDX that you use for the TM1RptRow to a cell, and copy it to Subset Editor to validate.

Just to iron out the potential causes one at a time.

Thanks for your replies,

The MDX statement is fine, it works fine if it returns at least one element. What I am doing is, I am filtering by cube Value, (STring Measure).

My Cube is an Employee Details Cube and I have a few String Measures (EMployee Class, Pay Cycle etc).

And users like to filter employees of a certain class or certain pay cycle ( and a few other text measures).

So I have drop downs to allow to apply filters.

But if there is no employee that meets the filter criteria, then instead of returning nothing, excel crashes and if I run the websheet through web, it returns all the elements of the dimension, including consolidations which takes a very long time.

As I mentioned in my original post, I run the MDX Query directly in Subset Editor, it works as expected (returns no elements).

SO this is definitely a bug.
I am doing this on TM1 10.2.2 FP7 on Unix (AIX)

Thanks

If Excel is crashing if the set is null then you could quite easily work around this by doing a Union with a dummy employee (e.g. +{[employee].[no employee]} ) at the end of your existing MDX.

If you don’t have a dummy element in the dimension you could create it. You could even then apply special formatting to the element to make the row not visible to the users.

Although it is a workaround to a bug it would be quite easy to implement.

lotsaram wrote: ↑Tue Aug 14, 2018 4:51 pm If Excel is crashing if the set is null then you could quite easily work around this by doing a Union with a dummy employee (e.g. +{[employee].[no employee]} ) at the end of your existing MDX.

If you don’t have a dummy element in the dimension you could create it. You could even then apply special formatting to the element to make the row not visible to the users.

Although it is a workaround to a bug it would be quite easy to implement.

This is what I am doing but in some cases I don't suppress zeroes and the dummy element shows. This is a bug and this was never an issue for me in the past

Thanks

Hi,

I feel your pain, if I had to update all my websheets and dimension builds due to a bug I would be annoyed! I have an upgrade from 10.1 to PA on the distant horizon and expecting issues like this!

Re the workaround, you could call the Dummy element in the dimension something like No Data Available and set an alias to something like Blank Row and display depending on if zero suppression or not. Or in the format row formula ensure the formatting makes the data invisible if the element = this dummy element.

But whatever way I suspect you will need a conversation with the client along the lines of , “we don’t live in an ideal world…”

Out of interest if instead of building the mdx within the worksheet you create an empty subset based on your mdx and directly reference this MDX based empty subset (and leave the mdx parameter blank) in the TM1RPTRow formula do you get the same issue?

This is definitely a bug but it's been around awhile as I've had the same issue before. As a matter of principle I always put dummy elements in dimensions where there are going to be reports that may have no rows returned that say something like "No cost centers found", or something along those lines and have the element show when no rows are returned in the MDX. This keeps it from crashing Excel and it is way more user friendly. I hate a report that returns no rows at all. You don't know if it really returned no rows or the form is broken.

tm123 wrote: ↑Tue Aug 14, 2018 1:46 pm And users like to filter employees of a certain class or certain pay cycle ( and a few other text measures).

So I have drop downs to allow to apply filters.

Another option I have employed previously is to have each of these drop downs being driven by MDX themselves that only allow the user to pick an element based on whether it will return a result or not, the downside is that you need the selections to be made in a specific order e.g.
1/ User selects a class (from a subset that only returns classes that have values in the cube)
2/ User selects from a list of pay cycles that have values when a filter is done on the selected class
3/ continue for other filter requirements

Note that the selections need to be made in a cube cell with a picklist as a subnm would allow the user to open the editors and ignore the predefined subset.