TM1RPTRow with MDX Expression that Returns zero elements

Post Reply
tm123
Posts: 132
Joined: Thu Oct 23, 2014 10:15 pm
OLAP Product: tm1, cognos bi
Version: 10.2
Excel Version: 2010

TM1RPTRow with MDX Expression that Returns zero elements

Post by tm123 »

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
User avatar
paulsimon
MVP
Posts: 808
Joined: Sat Sep 03, 2011 11:10 pm
OLAP Product: TM1
Version: PA 2.0.5
Excel Version: 2016
Contact:

Re: TM1RPTRow with MDX Expression that Returns zero elements

Post by paulsimon »

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
babytiger
Posts: 78
Joined: Wed Jul 31, 2013 4:32 am
OLAP Product: Cognos TM1, EP, Analyst
Version: 10.2.2
Excel Version: 2013
Location: Sydney AU

Re: TM1RPTRow with MDX Expression that Returns zero elements

Post by babytiger »

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.
MK
tm123
Posts: 132
Joined: Thu Oct 23, 2014 10:15 pm
OLAP Product: tm1, cognos bi
Version: 10.2
Excel Version: 2010

Re: TM1RPTRow with MDX Expression that Returns zero elements

Post by tm123 »

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
lotsaram
MVP
Posts: 3647
Joined: Fri Mar 13, 2009 11:14 am
OLAP Product: TableManager1
Version: PA 2.0.x
Excel Version: Office 365
Location: Switzerland

Re: TM1RPTRow with MDX Expression that Returns zero elements

Post by lotsaram »

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.
Please place all requests for help in a public thread. I will not answer PMs requesting assistance.
tm123
Posts: 132
Joined: Thu Oct 23, 2014 10:15 pm
OLAP Product: tm1, cognos bi
Version: 10.2
Excel Version: 2010

Re: TM1RPTRow with MDX Expression that Returns zero elements

Post by tm123 »

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
Mark RMBC
Community Contributor
Posts: 292
Joined: Tue Sep 06, 2016 7:55 am
OLAP Product: TM1
Version: 10.1.1
Excel Version: Excel 2010

Re: TM1RPTRow with MDX Expression that Returns zero elements

Post by Mark RMBC »

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?
tomok
MVP
Posts: 2831
Joined: Tue Feb 16, 2010 2:39 pm
OLAP Product: TM1, Palo
Version: Beginning of time thru 10.2
Excel Version: 2003-2007-2010-2013
Location: Atlanta, GA
Contact:

Re: TM1RPTRow with MDX Expression that Returns zero elements

Post by tomok »

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.
Tom O'Kelley - Manager Finance Systems
American Tower
http://www.onlinecourtreservations.com/
declanr
MVP
Posts: 1815
Joined: Mon Dec 05, 2011 11:51 am
OLAP Product: Cognos TM1
Version: PA2.0 and most of the old ones
Excel Version: All of em
Location: Manchester, United Kingdom
Contact:

Re: TM1RPTRow with MDX Expression that Returns zero elements

Post by declanr »

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.
Declan Rodger
Post Reply