Active Form TM1RPTVIEW and DBRW Problem

maps · Post by **maps** » Wed Nov 23, 2011 7:54 am

Hello Everyone,

My question is related to the behavior of the TM1RPTVIEW command in an active form. I’m using something like a template active form which I use as a basis for different active form reports...
In my Active form there is a “standard” cell which defines the TM1RPTVIEW. The "master row" is using this as first parameter. The first column after the TM1RPTROW is also using the TM1RPTVIEW as first Parameter of the DBRW function. For all other data to show I used the DBRW statement and the cube name directly (not the RPTVIEW) to load the data. Everything worked pretty well... until I had to change the first data load column which is using the TM1RPTVIEW as first parameter of the DBRW statement. Suddenly my active form didn’t work anymore (no data is loaded anymore). Not even the TM1RPTROW is showing the elements of the dimension... no REF error or N/A... it's just empty (of course the formulas are still in the cells when i click on them).

Master row -------------------------------GetDataA-----------------------GetDataB
TM1RPTROW(TM1RPTVIEW;…;MDX;..) DBRW(TM1RPTVIEW;…) DBRW(Cubename;…)

It seems, like there has to be at least one column which uses the TM1RPTVIEW as Parameter (DBRW), otherwise the active form doesn't work.
The explanations in the manual are pretty limited. I don't understand why the TM1RPTVIEW is that important for the active form... Actually I don't understand why the RPTVIEW is needed at all in a DBRW statement (instead of a cube name)!?
By the way I’m using a MDX statement in the TM1RPTROW function. The MDX statement is restricting the elements based on an entry in a control cube…

It wasn’t that easy to explain my problem. Hopefully you understand it…

lotsaram · Post by **lotsaram** » Wed Nov 23, 2011 9:23 am

maps wrote:It seems, like there has to be at least one column which uses the TM1RPTVIEW as Parameter (DBRW), otherwise the active form doesn't work.

Yes absolutely! If there is no DBRW referring to the TM1RptRow then the active form view cache won't get built and you will get no rowset returned! You need at least one column of DBRWs referring to BOTH your TM1RptView and TM1RptRow formulas. You can have adjacent columns referring to whatever else you want (be it DBRWs linking to another standard TM1 View reference cell or any old Excel formula) and all formulas will be copied down and formatted.

If you only want to return the rowset but aren't actually interested in the DBRW values from the "real" active form then the trick is just to limit this to a single column and simply hide that column. QED.

qml · Post by **qml** » Wed Nov 23, 2011 9:44 am

Lotsaram is 100% right.

Let me just add that this:

For all other data to show I used the DBRW statement and the cube name directly

is not the best idea. Even if you don't use the TM1RPTVIEW reference in all your formulas (you just need one) then you should at least use the good old VIEW instead of a hardcoded cube name. This will improve the performance of your report, especially over slower networks.

maps · Post by **maps** » Wed Nov 23, 2011 9:45 am

I just created a simple active form out of the cube viewer. Then I deleted all the DBRW statements... and surprise: The master row (TM1RPTROW) is still showing all the elements... I refreshed it several times, seems to work.

I other point is. Although I'm defining a view for the active form and use this in the DBRW statement, I can acces elements which are not defined in the view. So the View is not limiting my data?!

Example: TM1RPTVIEW("bla:..."; ...; TM1RPTTITLE("dimYear;2000");TM1RPTTITLE("dimMonth;April");...)

DBRW(reference to View...; "2000";"January";...)

I have problems understanding what the View is actually for. On a technical point of view I would just need my "Masterrow" to access all other information I need.

qml · Post by **qml** » Wed Nov 23, 2011 10:31 am

maps wrote:I just created a simple active form out of the cube viewer. Then I deleted all the DBRW statements... and surprise: The master row (TM1RPTROW) is still showing all the elements... I refreshed it several times, seems to work.

You refreshed it, but did you rebuild it? After a rebuild the report should not work, regardless of whether you use MDX or just list the elements in TM1RPTROW. Active Forms don't need TM1RPTVIEW to be a form, they need it to be active.

maps · Post by **maps** » Wed Nov 23, 2011 10:50 am

I pushed ALT and F9. And it still worked. What do you mean with "active"? Do you have an answer for my other questions I've asked in the post before? I really want to understand this.

qml · Post by **qml** » Wed Nov 23, 2011 11:32 am

First of all, I couldn't recreate the behaviour you're describing. I'm currently on 9.5.1 and deleting all the DBRW formulae from the first row and rebuilding the active form resulted in the master column also disappearing. I have also found it to be the case on previous occasions.

When I say "active" I mean it can be rebuilt and will adjust the element list automatically, among other features that make it "active".

The syntax in your last example of TM1RPTVIEW usage is way off, do you mind posting the actual one that you're using?

TM1RPTVIEW is obviously not the best documented function out there, but the 2 purposes it has that I know of are to create an optimised view cache (similar to VIEW) and to associate the TM1RPTDATARNGX range with the correct view formula that acts as the main "definition node" of the Active Form concept. Why? Because it's been designed that way.

You can have multiple Active Forms in one sheet and each one has to have a range to be rebuilt assigned (TM1RPTDATARNGX ), a TM1RPTVIEW formula, a TM1RPTROW formula and at least one data retrieval formula (DBRW) that will reference those.

I think you will find that most available knowledge on this subject will rather be of the practical variety ("how to...?") than the theoretical one ("why...?").

tomok · Post by **tomok** » Wed Nov 23, 2011 12:49 pm

The reason a DBRW is required is because of zero-suppression. Without a DBRW pulling back data the zero suppression will always result in no rows being shown. If you turn off zero suppression the report will return all the possible rows, even without a DBRW (the last time I checked).

qml · Post by **qml** » Wed Nov 23, 2011 1:05 pm

tomok wrote:The reason a DBRW is required is because of zero-suppression.

Maybe I'm doing sth wrong or it's version-dependent, but even with zero suppression switched off (set to 0 in TM1RPTVIEW) if I delete all DBRWs then all elements returned by TM1RPTROW disappear as well and I get an empty active form. So I can't really confirm your observations on this one.

maps · Post by **maps** » Thu Nov 24, 2011 10:45 am

Okay I just double-checked it. As soon as I delete the last DBRW statements the master row data will not be shown anymore. I don't know why it worked the first time.

Thanks a lot for your information. The performance aspect you mentioned makes sense for me and also the need of the view for the supress zero functionality. Is there an aspect I'm missing?

rkpeck · Post by **rkpeck** » Tue Jan 10, 2012 11:04 pm

I've read this entire thread and got a lot of good info on active forms. An issue I'm running into is the following so I'm wondering if there is a way to redefine the original report view? I have a dimension that is driving the rows in my report with zero suppression on. When the active form was built originally, it was selected with actuals data only from our accounting system. The problem I have is that a particular row may not have had any data for actuals but there is budget information for that line. I'm able to retrieve the budget info using dbrw and the specific cube but the zero suppression is suppressing rows solely based on the actual data. Without recreating a new active form, how can I get the original cached view to include budget and actuals? Hope this makes sense.

tomok · Post by **tomok** » Tue Jan 10, 2012 11:13 pm

rkpeck wrote:When the active form was built originally, it was selected with actuals data only from our accounting system. The problem I have is that a particular row may not have had any data for actuals but there is budget information for that line. I'm able to retrieve the budget info using dbrw and the specific cube but the zero suppression is suppressing rows solely based on the actual data. Without recreating a new active form, how can I get the original cached view to include budget and actuals? Hope this makes sense.

It doesn't matter how the active form was originally built, it all depends on how the dimensions are currently referenced in the TM1TPTVIEW formula. I'm guessing in your case the form was built with the dimension that contains Actual as one of the title dimensions and you have the element "Actual" in that cell. If that is the case then you would need to change the element in that cell to one that includes both Actual and Budget. If you don't have one then create one and call it Actual_plus_Budget, with Actual and Budget as children. Then change the value in the title cell to Actual_Plus_Budget and the zero suppress will only suppress rows where there is no value in either Actual or Budget.

Gregor Koch · Post by **Gregor Koch** » Tue Jan 10, 2012 11:30 pm

Hi
One way would be to delete the TM1RPTTITLE reference for you version dimension in the TM1RPTVIEW function.

It might look something like this:

=TM1RPTVIEW("server:cube:1", 0, TM1RPTTITLE("server:dim1",$C$11), TM1RPTTITLE("server:dim2",$C$12), TM1RPTTITLE("server:version",$C$13),TM1RPTFMTRNG,TM1RPTFMTIDCOL)

where cell C13 might be the reference for the version "Actual". If you delete that part you RPTVIEW will actually cater for all versions.

Sigh, tomok beat me to it.

rkpeck · Post by **rkpeck** » Wed Jan 11, 2012 12:58 am

I actually do not have the version dimension referenced as a title dimension in the reportview formula. I originally created the view with only actuals selected in my column dimension, it seems I can't reference any other version and get accurate numbers. When I change my dbrw formula to referece the report view instead of the server:cube, I get numbers back, my row that was gettting surpressed now shows up, but my data is nonsensical. I can go through each cell and hit F2 and Enter and get the correct results, but as soon as I do a rebuild of the sheet, the values go back to nonsense. Any ideas?

tomok · Post by **tomok** » Wed Jan 11, 2012 3:06 am

Sorry, it's almost impossible to debug active form problems without actually seeing the Excel file and how the formulas and ranges are specified. My suggestion would be to re-create the active form and then you can copy in the relevant formatting and stuff from the "bad" file.

rkpeck · Post by **rkpeck** » Wed Jan 11, 2012 7:14 pm

Starting recreating a new one last night, making sure the version dimension was a column dimension. Hopefully this works but active forms sure seem a bit buggy with not much documentation. Good to see these forums are active though, I'm sure I'll be back.

tiagoblauth · Post by **tiagoblauth** » Mon Apr 04, 2016 4:12 pm

It works for me but I did put a "Rebuild Sheet" button with a "Refresh" sign. Not very elegant but it works.

My problem was about active forms being presented on the tm1web. I don't want to have pre selected items on the filters, then I did clean them before publish. When you do it, the cells are present blank.

cdredmond · Post by **cdredmond** » Fri Oct 14, 2016 4:54 am

OK. Virtually all of the info in this thread I know and follow.
Here's my challenge under v10.2.2 FP4: When attempting to convert the DBRW formulas to DBR (due to dependencies on other DBR formulas), the Active Form will continue to work with just one DBRW formula. Once the last one is deleted, the TM1RPTROW formula fails to return any elements. I am certain I had done this successfully under older versions of TM1. I can not find any input on this issue on the IBM website. I am about ready to open a PMR unless one of you can tell me what to do to keep DBR formulas working in place of DBRW formulas without having to create a hidden column for the one ghost DBRW to keep TM1RPTROW happy.

babytiger · Post by **babytiger** » Tue Oct 18, 2016 12:16 am

Trying to get an understanding of how your structure your websheet, what is the key reason for having to drop all DBRWs. I know you've mentioned it is due to dependencies on other DBRs. Even certain activeform cell is depending on a DBR (or multiple DBRs), doesn't necessary require to drop all DBRWs. Because there is a risk with that, when suppress zero is used.

I've always thought the 3 TM1 formulas are inter-linked, TM1RPTVIEW, TM1RPTROW, and DBRW. Is it a requirement to build the table in a batch, i.e. all details/parameters of the activeform table must be known before making the request from TM1 server.

cdredmond · Post by **cdredmond** » Tue Oct 18, 2016 12:29 am

Thanks for the question babytiger. I have a DBR formula which pulls the current "system date" from a control cube. This "system date" identifies the latest month of actual data loaded into the server. All of the other DBR formulas pulling data from the primary data cube reference the "system date" DBR formula to know which timeframe to pull the data from the cube.

TM1 Forum

Active Form TM1RPTVIEW and DBRW Problem

Active Form TM1RPTVIEW and DBRW Problem

Re: Active Form TM1RPTVIEW and DBRW Problem

Re: Active Form TM1RPTVIEW and DBRW Problem

Re: Active Form TM1RPTVIEW and DBRW Problem

Re: Active Form TM1RPTVIEW and DBRW Problem

Re: Active Form TM1RPTVIEW and DBRW Problem

Re: Active Form TM1RPTVIEW and DBRW Problem

Re: Active Form TM1RPTVIEW and DBRW Problem

Re: Active Form TM1RPTVIEW and DBRW Problem

Re: Active Form TM1RPTVIEW and DBRW Problem

Re: Active Form TM1RPTVIEW and DBRW Problem

Re: Active Form TM1RPTVIEW and DBRW Problem

Re: Active Form TM1RPTVIEW and DBRW Problem

Re: Active Form TM1RPTVIEW and DBRW Problem

Re: Active Form TM1RPTVIEW and DBRW Problem

Re: Active Form TM1RPTVIEW and DBRW Problem

Re: Active Form TM1RPTVIEW and DBRW Problem

Re: Active Form TM1RPTVIEW and DBRW Problem

Re: Active Form TM1RPTVIEW and DBRW Problem

Re: Active Form TM1RPTVIEW and DBRW Problem