Active Report in TM1web

Post Reply
kavitha2002
Posts: 149
Joined: Sat May 05, 2018 11:48 am
OLAP Product: tm1
Version: 10.3.10100.8
Excel Version: 14

Active Report in TM1web

Post by kavitha2002 » Tue Feb 12, 2019 8:47 am

Hello Everyone,
sample.png
sample.png (45.23 KiB) Viewed 417 times
I have 2 sets of data one-sided and two-sided depends on the changes in drop down i have to show one-sided or two sided data in report. Note: I have embedded the tm1web in c# application, for data one-sided and two sided dropdown change will trigger the recalc. If its rebuild things are working fine. But its a standard to have recalc in our app.

I have the same cube attached data in excel for tm1web, but when I have changed to one-sided its show the value correct but shows the zero value also because of the two sided data beside in excel.

How to get rid of the zero value dynamically. But two sided is working fine because I have the two sided beside. The reason of having this two sided data in excel is without that, tm1 web doesnt show two-sided data.

MDX helps me to solve this??

I dont know how far I explained this problem here. Anybody have idea on how to achieve this, kindly throw some light on that. Any help is appreciated.
Attachments
tm1web_sample.png
tm1web_sample.png (52.38 KiB) Viewed 417 times

Mark RMBC
Regular Participant
Posts: 181
Joined: Tue Sep 06, 2016 7:55 am
OLAP Product: TM1
Version: 10.1.1
Excel Version: Excel 2010

Re: Active Report in TM1web

Post by Mark RMBC » Wed Feb 13, 2019 11:28 am

Hi,

I didn't really understand your issue (though I am sure your English is a million times better than my German!).

But you did ask a specific question, How to get rid of the zero value dynamically.

In Active forms you can switch zero suppress on or off via the TM1RPTVIEW formula. The second argument is for zero suppress and 1 supresses zero and 0 turns zero supress off. You can change the literal 0 or 1 to be a link to another cell, so for example,

=TM1RPTVIEW("Instance:Cube:AFID", IF($D$24="Yes",1,0),...

Now I am guessing you know this already and your problem is of a different nature but posted just in case!

cheers, Mark

kavitha2002
Posts: 149
Joined: Sat May 05, 2018 11:48 am
OLAP Product: tm1
Version: 10.3.10100.8
Excel Version: 14

Re: Active Report in TM1web

Post by kavitha2002 » Wed Feb 13, 2019 3:16 pm

Hello Mark,

Thanks for your response. But I already aware of Zero suppress. Even I dont know, how to explain my problem better, its bit tricky.

In our application, we have dropdown for column change for tm1web report. Drop down has value one-sided and two-sided.

When I use one sided in column of excelsheet and uploaded to tm1web, its shows only the one-sided data. when I change to two-sided in column in my application, its shows all empty values. Thatsy I have added the column for two sided in excel sheet like in cube.
sample.png
sample.png (45.23 KiB) Viewed 378 times
Now its shows the data for both one-sided and two-sided column, but for one-sided it shows the zero record also because two sided DBRW formula is beside which has value. How to get rid off these zeros. Thats my question??? I hope now it will be bit more clear on the explanation part. I would like to use recalc not rebuild. Is there any possibility of achieving this using MDX in TM1RPTROW to get rid of zeors for one-sided.

tomok
MVP
Posts: 2561
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: Active Report in TM1web

Post by tomok » Thu Feb 14, 2019 1:54 pm

First off I wish you would dispense with the "one-sided" versus "two-sided" talk. That is not a TM1 concept and it is just making it hard to understand your problem. They are just some elements you created in a dimension. Looking at your screen shot from above I don't see a single row where every column is zero, which is how zero-suppression works in an active form. If ANY column inside the active form area is non-zero then the row is going to appear. There is no way around that. If you mix one-sided with two-sided elements in the columns then all rows when EITHER is non-zero will show.
Tom O'Kelley - Manager Finance Systems
American Tower
http://www.onlinecourtreservations.com/

kavitha2002
Posts: 149
Joined: Sat May 05, 2018 11:48 am
OLAP Product: tm1
Version: 10.3.10100.8
Excel Version: 14

Re: Active Report in TM1web

Post by kavitha2002 » Thu Feb 14, 2019 2:55 pm

Thanks tomok, for understanding my problem. I am not a expert in MDX, so writing a MDX in TM1RPTRow help to solve this problem?

tomok
MVP
Posts: 2561
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: Active Report in TM1web

Post by tomok » Thu Feb 14, 2019 4:42 pm

kavitha2002 wrote:
Thu Feb 14, 2019 2:55 pm
Thanks tomok, for understanding my problem. I am not a expert in MDX, so writing a MDX in TM1RPTRow help to solve this problem?
What part of "There is no way around that" did you not understand?
Tom O'Kelley - Manager Finance Systems
American Tower
http://www.onlinecourtreservations.com/

User avatar
gtonkin
MVP
Posts: 667
Joined: Thu May 06, 2010 3:03 pm
OLAP Product: TM1
Version: PAL 2.0.3
Excel Version: 2016 64-bit
Location: JHB, South Africa
Contact:

Re: Active Report in TM1web

Post by gtonkin » Thu Feb 14, 2019 6:54 pm

I normally start an active form by identifying what drives the results.

In your case, you need to decide which measures drive it i.e. show the row if one-sided is non-zero, show if two-sided is non-zero etc.
Based on the screenshot, the activeform will show a row if any of the columns has a non-zero value.

Once you have identified the driver e.g. one-sided, make sure the DBRW that points to the TM1RPTVIEW reads this measure. For all the other measures, point your DBRW to a cube reference, not the TM1RPTVIEW.

Hope this helps.

Post Reply