DBRW is an Excel based function for use with TM1 that retrieves a value from a specific intersection of a cube TM1.
I have a Sales Analysis cube with the following dimensions: Year, Month, Customer, Product, Measures. I want the intersection of:
- 2015 (Year),
- Oct (Month),
- The Corner Cafe (Customer),
- Coffee (Product) and
- Sales $ (Measure).
If I open Architect and navigate to this intersection, it says $1,230.00.
I now want to get that value in a cell in an Excel report. To do this I can use a DBRW or a DBR formula.
Syntax of DBRW
The syntax for DBRW is:
=DBRW(TM1ServerName:cubename, e1, e2, e3, …, en);, where:
- server:dimension, is the TM1 server ID, concatenated with a full colon and then the cube name you wish to interrogate
- e1, is the element from the first dimension in the cube you are interrogating
- e2, is the element from the second dimension in the cube you are interrogating
- e3, is the element from the third dimension in the cube you want are interrogating
- en, is the element from the nth dimension in the cube you want are interrogating
Importantly, the order of the elements listed in the DBRW formula must be identical to the order of the dimensions in the cube (otherwise TM1 will not know which dimension to look at for that element!).
Continuing the example from above, where we want 2015, Oct, The Corner Cafe, Coffee and Sales $ from the Sales Analysis cube, we would write the DBRW in Excel as follows:
DBRW(‘CXMD:Sales Analysis’, ‘2015’, ‘Oct’, ‘The Corner Cafe’, ‘Coffee’, ‘Sales $’);
Obviously I can have these referenced values in other cells in Excel and then refer to those cells rather than using hard coded values (and this is exactly the method that a Slice from Architect uses to create an Excel based report). In the example below, I have the Server-Cube reference, Year, Product and Measure as sequential rows in column A of a spreadsheet and then the values for those in column B. Then in column A beneath that block have The Corner Cafe and all my other customers listed. Finally, in column B I have Jan, Feb, Mar… Dec entered. Then I simply create a DBRW that uses absolute referenced values for Server-Cube, Year, Product and Measure, then relative references for the Customer and Month. We can then copy and paste this for all months and customers and then get our values out of TM1.