Start a new topic
Answered

How to create custom columns in cube

I would like to put MTD and YTD on the crosstab (which I know I do by adding multiple copies of the measure and setting time series on them). However I would like to separate them on the report. so it would look like this...


Dimension | Valves MTD | Accessories MTD | Packs MTD | Other MTD | (blank space) |   Valves YTD | Accessories YTD | Packs YTD | Other YTD |

 

Is that possible? Also, I would ideally like to have group headings for the 2 sections so it would look like this.

 

                  |                  Month To Date                     |                     Year To Date                     | 

Dimension | | measure 1 | measure 2 | measure 3 | | | measure 1 | measure 2 | measure 3 |  


Best Answer

For the first point, create report with following steps:

1) Create a cross-tab

2) Add dimension

3-A) Add your measure as many times as there are bifurcations for your family type (i.e., Valves, Accessories, Packs and Others). That means 4 families * 2 measurable YTD and MTD, total 8 times same measure. 

3-B) Or add your family dimension in column section and take that measure twice.

4) In case of 3-A, you will have to apply condition of time series (YTD/MTD) and condition for family on each measure copy. Whereas, in case of 3-B, you will only need to apply condition for YTD and MTD on both the measures respectively.

5) 3-A will allow you to position your measures in following way as desired:

Dimension | Valves MTD | Accessories MTD | Packs MTD | Other MTD | (blank space) |   Valves YTD | Accessories YTD | Packs YTD | Other YTD |

While, 3-B will show output like as follows:

                   |    Valves    |Accessories |     Packs     |      Other    |

Dimension | MTD | YTD | MTD | YTD | MTD | YTD | MTD | YTD |


Apart from this, blank space cannot be added between columns, though you can use padding and margin for grid cells.


For your second Point, measures can be divided within various values of a column dimension. However, Group headings are not possible to group measures separately in a report.

1 Comment

Answer

For the first point, create report with following steps:

1) Create a cross-tab

2) Add dimension

3-A) Add your measure as many times as there are bifurcations for your family type (i.e., Valves, Accessories, Packs and Others). That means 4 families * 2 measurable YTD and MTD, total 8 times same measure. 

3-B) Or add your family dimension in column section and take that measure twice.

4) In case of 3-A, you will have to apply condition of time series (YTD/MTD) and condition for family on each measure copy. Whereas, in case of 3-B, you will only need to apply condition for YTD and MTD on both the measures respectively.

5) 3-A will allow you to position your measures in following way as desired:

Dimension | Valves MTD | Accessories MTD | Packs MTD | Other MTD | (blank space) |   Valves YTD | Accessories YTD | Packs YTD | Other YTD |

While, 3-B will show output like as follows:

                   |    Valves    |Accessories |     Packs     |      Other    |

Dimension | MTD | YTD | MTD | YTD | MTD | YTD | MTD | YTD |


Apart from this, blank space cannot be added between columns, though you can use padding and margin for grid cells.


For your second Point, measures can be divided within various values of a column dimension. However, Group headings are not possible to group measures separately in a report.

Login to post a comment