DBRW will not retrieve data for parent account not in subset.

Post Reply
Bakkone
Posts: 58
Joined: Mon Oct 27, 2014 10:50 am
OLAP Product: TM1
Version: 10.2.2
Excel Version: 2013

DBRW will not retrieve data for parent account not in subset.

Post by Bakkone » Tue Oct 17, 2017 5:07 pm

Hi all,

I have a problem that I don't think I've come across before.

A websheet contains a DBRW formula:
DBRW($B$11;Organization;Market;$D37;Measure)

The TM1RPTView (B11) formula like this:
TM1RPTVIEW("Server:Cube:1"; 0; TM1RPTTITLE("Server:Organization";$F$32); TM1RPTTITLE("Server:Market";$J$32); TM1RPTTITLE("Server:Measure";$O$32);TM1RPTFMTRNG;TM1RPTFMTIDCOL)
All the TM1RPTTitle formulas are connected to cells with SUBNM formulas.

D37 is the TM1RPTRow formula:
TM1RPTROW($B$11;"KlarnaTM1:Account";"Subset";;"Caption";;;0;0)

The Subset contains the following accounts
B1
B2
B3

Which in turn is a subset of the dimension that looks like this:
A1
--B1
--C1
A2
--B2
--C2
A3
--B3
--C3

For some columns I would instead get the value of the parent A. I've tried adding a helper column with parent and pointing to this. Tried adding a DBRA in my row-formula. All of them fails and I never get a value. Values on Bx or Cx show up fine. But trying to get the parent account doesnt work.

What solves the issue is adding the parent accounts to the subset. So the subset looks like this:
A1
A2
A3
B1
B2
B3

I then hide the first three rows. Values are now correctly retrieved for the parent of B1 etc... regardless if i adjust the row-formula, use a helper column etc.

Is this really the way its suppose to work? Am I missing something super simple?

declanr
MVP
Posts: 1542
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: DBRW will not retrieve data for parent account not in subset.

Post by declanr » Tue Oct 17, 2017 5:44 pm

Sounds like you are referencing your active form view in the DBRW. If you reference the active form view for an element that isn’t part of the view (not in the subset) then it can’t pull the resulting value from the cache as it’s not there. Change the referenced view to a hard coded cube name to prove the issue.
Assuming that works just create another view formula on the sheet and reference that (obviously make sure that the view formula covers all your criteria as required.)

Bakkone
Posts: 58
Joined: Mon Oct 27, 2014 10:50 am
OLAP Product: TM1
Version: 10.2.2
Excel Version: 2013

Re: DBRW will not retrieve data for parent account not in subset.

Post by Bakkone » Tue Oct 17, 2017 7:27 pm

Hi,

That solved it. Created another view using the VIEW-formula and referenced that one in the DBRW.

Thanks for your help. I always forget the strange ins and outs with these formulas and their requirements.

Post Reply