Dynamic report with multiple DBRW's per cell

[JohnO]

Hopefully a very simple one.

We are using 2.0.93 PAfE and 2.0.93 Spreadsheet services.

I have a report in PAfE where I have DBRW() + DBRW(). The DBRW's are pointing to separate measures.

In PAfE this report works fine but in TM1Web no rows are returned. If I pull it back to one DBRW it is fine in both PAfE and TM1. We don't have a natural aggregation for the 2 measures so I'm trying to keep it to 2 DBRW's.

I have looked at all of the fix lists in Spreadsheet Services since 2.0.93 but I didn't find anything related to this.

Should I expect this to work in TM1Web?

[MarenC]

Hi John,

Dynamic reports simple, is it April fools day!

Can you share exactly how the DBRW looks, because as nuts as this sounds I had an issue where the DBRW did not work if I put a space between the + sign and the second DBRW, I had to do something like,

=@DBRW(cube,dim1,dim2,measure1) +@DBRW(cube,dim1,dim2,measure2)

rather than
=@DBRW(cube,dim1,dim2,measure1) + @DBRW(cube,dim1,dim2,measure2)

Though note this was in PAW, not TM1Web.

Also, is the Measure in the Title, or is it part of the row/columns?

Maren

[JohnO]

Thanks. That's interesting but not the case for me.

The measures are in the column heading and the only difference between the two DBRW's is the measure. The formula is subtraction, so DBRW()-DBRW()

[burnstripe]

Try changing from dbrw to dbr just for the components that make the header

[MarenC]

The formula is subtraction, so DBRW()-DBRW()

Well you said initially,

I have a report in PAfE where I have DBRW() + DBRW()

I guess this is going to be a minimum information problem!

If Burnstripe's suggestion doesn't work you could try adding the measure dim to the TM1RPTITLE.

Or say having the first column just looking at the single measure (just a dbrw), and then the next column doing the DBRW()-DBRW(), and hiding the first column. messing around like that may give a clue as to the source of the issue.

maren

[JohnO]

The formula is subtraction, so DBRW()-DBRW()

Well you said initially,

I have a report in PAfE where I have DBRW() + DBRW()

My apologies and thanks for your suggestions

maren

[JohnO]

I logged a support ticket with IBM. This functionality is not supported.

"
I have confirmed with the Development team, compound DBRW usage like this is not supported in tm1web.

Please see this technote: Compound DBRW formulas are not supported in TM1 Web

https://www.ibm.com/support/pages/compo ... ed-tm1-web

There is already enhancement request logged for this. Please refer to the comment from Product management.

https://ibm-data-and-ai.ideas.ibm.com/ideas/PAOP-I-343
"

[lotsaram]

We don't have a natural aggregation for the 2 measures so I'm trying to keep it to 2 DBRW's.

Why not? Quite simply put, this is how it should be done if you are using TM1 properly; with rollups and hierarchies.

The fact that it works in Perspectives or PAfE doesn't mean this is how it should be done. It's very easy to misuse Excel and get away with a lot of things, which although they work, may impose issues like poor performance.

In this case you now know definitivly that it isn't supported. Although the use case is pretty simple and there's a good argument that if it works in Excel then it should also work in PASS, I wouldn't be holding my breath for this feature. You can solve the issue right now by creating the appropriate consolidation of members, which I would argue is anyway the "correct" solution anyway.

[JohnO]

We don't have a natural aggregation for the 2 measures so I'm trying to keep it to 2 DBRW's.

Why not?

My words were "natural aggregation", by that I mean the 2 measures do not naturally aggregate from the perspective of a financial model or from the perspective of usability. In this case it is a matter of building an aggregation to overcome the limitation of the user interface or adding in a bunch more columns, that doesn't make either a better solution than what I was seeking.

It will be interesting to see what the planned user defined columns/rows (supporting Excel calculations) in Universal Reports will support.

Thanks