When you are starting out using Planning Analytics (TM1) the world of rules can be daunting #
There are hundreds of rules and finding the one you need with a good explanation of syntax and purpose can take a long time if you don’t know where to look. In this blog, we’ll be taking a look at the DBRW formula.
DBRW – TM1 Excel Formula
DBRW is a TM1 formula that is used in Excel. These formulas are automatically genereated when either a Dynamic Report or a Custom Report is created in Planning Analytics for Excel.
DBRW returns a numeric and/or string value from a cube intersection and can also be used to write values back into a cube if the user has ‘write’ access to the cube. DBRW is effective as it reduces network traffic on a wide area network.
The syntax of the formula needs to specify elements in each dimension, as such you should make sure that the formula is correct. The DBRW formula applied to a cell in Excel corresponds with an intersection in the cube.
The syntax is as follows:
DBRW (cube, Element1, Element2, Element 3... Elementn)
Cube = source cube that you are going to get the data from
Element1 = First element in the cube
Element2 = Second element in the cube
Element3 = Third element in the cube
Elementn = List the remaining elements in the cube
Things to keep in mind when using this Formula:
- In a Dynamic report, this formula will reference:
-
- SUBNM filters
- Rows
- Columns
- You need to specify all the dimensions in the cube that you want to retrieve the value from. Dimension ordering is also important, so ensure that element #1 corresponds to the first dimension in the cube, element #2 corresponds to second element, and so on.
- You can hardcode components of the formula or refer to different sheets using double quotes. When you do this make sure you refer to an item that corresponds to an element in the cube.
- You can drag or copy and paste DBRW formula to retrieve values from the cube relative to the dynamic report reference point.
- You can use DBRW formulas in conjunction with other Excel formulas.
Modifying the formula:
In this example, there is a cube with 4 dimensions in the following order:
- Car name
- Manufacturing year
- Type
- Price
There is a requirement to create a Dynamic report that shows only SUV type cars.
When you convert a cube view to a data section it will point to the SUBNM filters, rows and columns.
=DBRW($B$12,B24,$C$23,$C$14,$C$15)
We know that ‘Type’ is the 3rd dimension in the cube, so $C$14 will reference the car type in the data grid. To hardcode the formula, you can change the syntax as follows:
=DBRW($B$12,B24,$C$23,”SUV”,$C$15)
This will return the ‘SUV’ element only.
