Hello Everyone,
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.
Active Report in TM1web
-
- Community Contributor
- Posts: 180
- Joined: Sat May 05, 2018 11:48 am
- OLAP Product: tm1
- Version: 10.3.10100.8
- Excel Version: 14
Active Report in TM1web
- Attachments
-
- tm1web_sample.png (52.38 KiB) Viewed 3169 times
-
- Community Contributor
- Posts: 292
- Joined: Tue Sep 06, 2016 7:55 am
- OLAP Product: TM1
- Version: 10.1.1
- Excel Version: Excel 2010
Re: Active Report in TM1web
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
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
-
- Community Contributor
- Posts: 180
- Joined: Sat May 05, 2018 11:48 am
- OLAP Product: tm1
- Version: 10.3.10100.8
- Excel Version: 14
Re: Active Report in TM1web
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.
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.
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.
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.
-
- MVP
- Posts: 2832
- 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
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.
-
- Community Contributor
- Posts: 180
- Joined: Sat May 05, 2018 11:48 am
- OLAP Product: tm1
- Version: 10.3.10100.8
- Excel Version: 14
Re: Active Report in TM1web
Thanks tomok, for understanding my problem. I am not a expert in MDX, so writing a MDX in TM1RPTRow help to solve this problem?
-
- MVP
- Posts: 2832
- 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
What part of "There is no way around that" did you not understand?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?
- gtonkin
- MVP
- Posts: 1199
- Joined: Thu May 06, 2010 3:03 pm
- OLAP Product: TM1
- Version: Latest and greatest
- Excel Version: Office 365 64-bit
- Location: JHB, South Africa
- Contact:
Re: Active Report in TM1web
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.
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.