Named Ranges in DBRW

[evolver]

hello,

i´ve got a problem with the dbrw function. we moved from mis alea to tm1. for this reason we have to change all functions also from alea syntax to tm1. (dbget to dbrw e.g.)
now there´s a problem with named ranges in the dbrw function. if the named range references to more than one cell, the dbrw function gets no result.
what´s my problem? is there any possibility to use named ranges in the dbrw function with more than one cell in the reference??

sorry for my bad english!

[tomok]

i´ve got a problem with the dbrw function. we moved from mis alea to tm1. for this reason we have to change all functions also from alea syntax to tm1. (dbget to dbrw e.g.)
now there´s a problem with named ranges in the dbrw function. if the named range references to more than one cell, the dbrw function gets no result.
what´s my problem? is there any possibility to use named ranges in the dbrw function with more than one cell in the reference??

What exactly do you mean by "references more than one cell"? Are you saying the range name you are referencing in the DBRW refers to more than one cell in Excel like A1 through B10? That won't work in TM1 because every parameter in a DBRW must be a single string value, either the name of the cube you are pulling from, or a single element from one of the dimensions in the cube. If your range name points to a single cell then you most certainly can use them in a DBRW.

[evolver]

thanks for the reply. that´s exactly what i mean. named ranges e.g. a1 to a12 don´t work. is there no other way to use such ranges in tm1? in mis alea with the dbget function there was no problem!

[tomok]

thanks for the reply. that´s exactly what i mean. named ranges e.g. a1 to a12 don´t work. is there no other way to use such ranges in tm1?

What exactly are you expecting the formula to return? A DBRW formula is designed to return a single value from TM1. Let's say the cube in question has Account as one of the dimensions and the range name you are referring to has a list of account numbers you want. How is TM1 going to know which account to return the value for? Is it the first account in the list? The second? The last? Catch my drift?

[evolver]

i know what you mean, but that isn´t the problem. i know how a dbrw function works and whats the result of it. i try to explain: e.g.

- cells a1 to a12 are the months jan to dec, this range is called MONTH
- in b1 to b12 the dbrw function like this: =DBRW(Server:Cube;Dim1;Dim2;MONTH)

dim1 and dim2 are two single elements out of two dimension!

so, the dbrw function in b1 should take the value out of a1(Jan) , the dbrw in b2 the value of a2 (Feb) an so on!

[tomok]

Each DBRW formula in TM1 stands alone. The formula in b1 has now way of understanding, or knowing about the formula in a1. In order for the DBRW to work it has to pass a single string value to the TM1 engine to resolve to a value. If you pass a range name to a DBRW formula that has more than one possibility (because it relates to multiple cells) then the formula will not work because the TM1 engine does not know which cell in that range to execute the query with.

[Michel Zijlema]

I would think that this should work with TM1/DBRW too: if you f.i. create a range $A$1:$L$1 named 'MONTH' and populate these with values 'jan' to 'dec' and then paste the formula =MONTH in f.i. cells A3 to L3, you will see the values in these cells resolve to 'jan' to 'dec'. So if these labels match the element names in the month dimension I would think this should work...
But anyway, I also would think that it should not be too difficult to replace the MONTH reference with a A$1 type of reference.

Michel

[tomok]

Should and does are two different things. It does not work on 9.5.2, or any previous version of TM1.

[nick_leeson]

I think you are more likely to use the VIEW statement !!

[Alan Kirk]

I think you are more likely to use the VIEW statement !!

And the View function has... what to do with this question, exactly?

[Wim Gielis]

i know what you mean, but that isn´t the problem. i know how a dbrw function works and whats the result of it. i try to explain: e.g.

- cells a1 to a12 are the months jan to dec, this range is called MONTH
- in b1 to b12 the dbrw function like this: =DBRW(Server:Cube;Dim1;Dim2;MONTH)

dim1 and dim2 are two single elements out of two dimension!

so, the dbrw function in b1 should take the value out of a1(Jan) , the dbrw in b2 the value of a2 (Feb) an so on!

Yes, this also works in TM1. I'll show you how:

Select a cell in the range B1:B12.
Hit Ctrl-F3 to create a new named range, for example MyMonth (Month in itself is a function so do not use it)
For the contents of the variable, click = and select the cell immediately to the left (so column A)
Take away the $ signs so that the reference is relative.

Now this will work perfectly:

=DBRW(Server:Cube;Dim1;Dim2;MyMonth)

[Alan Kirk]

i know what you mean, but that isn´t the problem. i know how a dbrw function works and whats the result of it. i try to explain: e.g.

- cells a1 to a12 are the months jan to dec, this range is called MONTH
- in b1 to b12 the dbrw function like this: =DBRW(Server:Cube;Dim1;Dim2;MONTH)

dim1 and dim2 are two single elements out of two dimension!

so, the dbrw function in b1 should take the value out of a1(Jan) , the dbrw in b2 the value of a2 (Feb) an so on!

Yes, this also works in TM1. I'll show you how:

Select a cell in the range B1:B12.
Hit Ctrl-F3 to create a new named range, for example MyMonth (Month in itself is a function so do not use it)
For the contents of the variable, click = and select the cell immediately to the left (so column A)
Take away the $ signs so that the reference is relative.

Now this will work perfectly:

=DBRW(Server:Cube;Dim1;Dim2;MyMonth)

Excellent point; the named relative range reference is a little known feature of Excel. But because it is so little known... I'd be loathe to use it operationally. It would make formula auditing a b*tch (for those who are unfamiliar with it) because the range name refers to a different location in each DBRW. That negates the big advantage of using named ranges in the formulas; the fact that they can be used to "nail down" a cell reference so that it can be easily identified, traced, and will remain unchanging even when columns or rows or ranges are inserted or deleted between the range and the formula using it.

If I was going to use it at all I'd most likely give the range name something which would make it stand out as a relative reference, such as CellToTheLeft.

[lotsaram]

We haven't heard back from the OP but I would have thought the most fundamental (and only) difference between DBR and DBGET formulas is that the later has 2 separate arguments for server and cube while the former uses a single argument with a colon character as an internal delimiter.
E.g. DBGET($B$1,$B$2, ... would need to change to DBRW($B$1&":"&$B$2,...
All else in the formulas would remain the same. There is no difference to how they treat any other arguments. Both only care about how the text property of each argument resolves to give a cell address within the cube. Provided this change is done correctly in a find/replace operation then all else should work.

Excellent point; the named relative range reference is a little known feature of Excel. But because it is so little known... I'd be loathe to use it operationally. It would make formula auditing a b*tch (for those who are unfamiliar with it) because the range name refers to a different location in each DBRW. That negates the big advantage of using named ranges in the formulas; the fact that they can be used to "nail down" a cell reference so that it can be easily identified, traced, and will remain unchanging even when columns or rows or ranges are inserted or deleted between the range and the formula using it.

If I was going to use it at all I'd most likely give the range name something which would make it stand out as a relative reference, such as CellToTheLeft.

Alan, I have to call you on that as I don't think the case against relative named ranges is so one sided. I also don't have a particularly high regard for the average user's Excel skills (including the average finance user). But I have found the concept of relative vs. absolute cell references and their use in named ranges to be something that is within the average Excel users grasp. Using relative named references certainly makes formulas easier to read and understand for most users and there are good arguments both for and against in terms of formula and workbook auditability when using such named ranges. (Certainly the "for" arguments stack up more favorably where the user community is proficient and well trained in Excel and the organization or department has made some steps in adopting some spreadsheet standards.)

[Alan Kirk]

Excellent point; the named relative range reference is a little known feature of Excel. But because it is so little known... I'd be loathe to use it operationally. It would make formula auditing a b*tch (for those who are unfamiliar with it) because the range name refers to a different location in each DBRW. That negates the big advantage of using named ranges in the formulas; the fact that they can be used to "nail down" a cell reference so that it can be easily identified, traced, and will remain unchanging even when columns or rows or ranges are inserted or deleted between the range and the formula using it.

If I was going to use it at all I'd most likely give the range name something which would make it stand out as a relative reference, such as CellToTheLeft.

Alan, I have to call you on that as I don't think the case against relative named ranges is so one sided. I also don't have a particularly high regard for the average user's Excel skills (including the average finance user). But I have found the concept of relative vs. absolute cell references and their use in named ranges to be something that is within the average Excel users grasp. Using relative named references certainly makes formulas easier to read and understand for most users and there are good arguments both for and against in terms of formula and workbook auditability when using such named ranges. (Certainly the "for" arguments stack up more favorably where the user community is proficient and well trained in Excel and the organization or department has made some steps in adopting some spreadsheet standards.)

"Call" me all you want. I didn't say it wasn't within their grasp, I said it wasn't common knowledge in no small part because most users would probably seldom use them even if they knew about them. Relative range references, yes, relative range names, no, they probably wouldn't be bothered creating them. And I did point out that in my view only, and yours can be whatever you want it to be, given that most users who know about named ranges at all know only the absolute type then the "auditability" is most likely to be enhanced only if the range was named in such a way as to make its relative nature stand out.

Your mileage can be whatever you'd like it to be.

[Gregor Koch]

Maybe this is because I did not have my second coffee, yet, but I don't see the real benefit of using a relative named range in this example. Please enlighten me or hand me my coffee.

[Alan Kirk]

Maybe this is because I did not have my second coffee, yet, but I don't see the real benefit of using a relative named range in this example. Please enlighten me or hand me my coffee.

The only significant benefit that I can think of is that it will ensure that any formulas in column B will continue to point to the correct place in column A should you insert a block of cells. (For instance, if you have formulas in column B which read =A8, =A9, =A10 etc and you insert a blank cell in A9 (using the "Shift Cells Down" option), the formulas would then read =A8, =A10, =A11, etcetera. Using the named range would avoid that.) You could do the same thing with an =Offset() formula, but in both cases there will probably (certainly, in the case of the Offset() option) be a larger processing overhead than you'd have by (a) using a naked relative reference and (b) either remembering not to insert blocks of cells which aren't whole rows or columns, or copying down the formulas when you do so. Of course everyone has their own opinion on performance vs readability, and where the balance lies.

I'm out of fresh milk for my espresso machine... will some Moccona instant do?

[Wim Gielis]

Hello,

There are indeed arguments in favor of relative named ranges and arguments against (as clearly pointed out above).
I did not use them in Excel reports showing DBRW's until now, this is the past 4,5 years, probably because I prefer to gather all fixed references either in absolute named references/constants, or in hidden cells / cells on a hidden sheet.
The reports are maintained by average Excel skilled finance users, meaning that I agree with Alan that there could be a moment that they break the functionality and need to me to restore.

Wim

[Gregor Koch]

Thanks Alan and Wim.

Well into the second coffee now. Thanks for the offer on that Alan, any form of caffeine would have/has done the job, I don't think it would be all that far for me to pick it up, is it Classic Dark Roast?

I'll go with readability on that one.
Also, if even an Excel MVP hasn't used it yet I don't think I need to change anything.
Would a DBRW checker/Key Error Tracer, say in the TM1tools, deal with this?
BTW, has TM1 Tools been updated to deal with a TM1RPTVIEW function?

[Alan Kirk]

Thanks Alan and Wim.

Well into the second coffee now. Thanks for the offer on that Alan, any form of caffeine would have/has done the job, I don't think it would be all that far for me to pick it up, is it Classic Dark Roast?

French Style. I was feeling exotic last time I filled up the shopping basket at Woolies.

Would a DBRW checker/Key Error Tracer, say in the TM1tools, deal with this?

Unfortunately not. It recognises it as a range name but has a wobbly in trying to evaluate it.

BTW, has TM1 Tools been updated to deal with a TM1RPTVIEW function?

No, that won't work either I'm afraid.

The tracer component of the add-in is primarily Martin's baby so I'll leave it to him to indicate whether he thinks any updates can/should be made.

[Martin Ryan]

BTW, has TM1 Tools been updated to deal with a TM1RPTVIEW function?

No, that won't work either I'm afraid.

The tracer component of the add-in is primarily Martin's baby so I'll leave it to him to indicate whether he thinks any updates can/should be made.

The tracer will cope with a DBRW that relies on a TM1RPTVIEW ok, but as Alan said it can't work out the TM1RPTVIEW function. Looks like it'll be pretty tricky too, because not every dimension from the cube is mentioned in the function, only those that are title dimensions.

Frankly I can't see it being that useful. The title dimensions are pretty clearly defined in the formula already - and you could always just double click on those referenced cells to validate them via Subset Editor. The row and column headers aren't explicitly mentioned in the TM1RPTVIEW formula and in any case would just be the equivalent of "!" in the =VIEW formula, so not much point.

Though I'm all ears if someone can come up with a reason why it'd be useful. The problem isn't insurmountable, it'd just require a slightly different methodology than I've used for the rest of the tracer.

So far I've completely ignored all the other active form formulas in the tracer too (mostly as I haven't used them enough to figure out how they hang together), but as active forms gain more traction and I start using them more myself, if I can see a use for the tracer I'll start adding functionality.

Any particular requests, then add them into the thread http://www.tm1forum.com/viewtopic.php?f=21&t=549