Former Member

# adding custom columns on regular interval in crosstab report

Hi ,

I have simple report of State wise Month wise Sales .

I am showing 12months in columns and states in rows .

My requirement is after every 3 months I want to show a column which will give me sum of those 3 months as a Quarter.

I am tying this XI 3.1.

any idea on this ??

10|10000 characters needed characters exceeded

### Related questions

Former Member
Posted on Jan 09, 2014 at 01:14 PM

Hi,

Create the cross Tab report as below and apply the break in Q, in that Q column just provide the formula as Sum(Sales) In (Q).

if you have Q object it fine, otherwise create a variable for quarter as below

Var_Qtr= if([Month] Inlsit(1;2;3);"Qtr1";if([Month] Inlist (4;5;6);"Qtr2";if([Month] Inlist (7;8;9);"Qtr3";if([Month] Inlist (10;11;12);"Qtr4"))))

Create the cross tab with two rows (Month & Qtr) one column (State) and drag the sales measure in data values place. now apply the break in Qtr row. then provide the formula in that column as Sum([Sales] in ([Qtr]))

Thanks,

Sakthi.

Sales.jpg (48.7 kB)
10|10000 characters needed characters exceeded
• Posted on Jan 04, 2014 at 11:51 AM

You need an extra dimension for Quarter on which you need to apply break. You can create a variable i webi to obtain the quarter from month. But as in XI3.1, there is no option to hide dimension, you need to reduce the width of the quarter column to 4px and make the font and background color same so that it appears to be hidden.

10|10000 characters needed characters exceeded
• Posted on Jan 04, 2014 at 11:31 AM

Hi,

restrict measue with that month

for example ur having month in var1

= If(var1>=1 And var1<=3) Then "Q4" Else If (var1>=4 Andvar1<=6) Then "Q1"

Else If(var1 >=7 And <=9) Then "Q2" Else If (v ar1>=10 And var1<=12) Then "Q3"

10|10000 characters needed characters exceeded
• Posted on Jan 04, 2014 at 12:54 PM

Hi Tushar,

In this case, it may be better you go with vertical table rather than cross tab..

if it is cross tab, then following issues may come

difficult to achieve sort on month, if you need..

In vertical table, you need to create sixteen variables, one variable for each column..[12 months + 4 quarters].. need check the performance of report..

each variable for month should contain formula like sum[sales] where month=<Month Name/No>

each variable for quarter should contain formula like sum[sales] where month<=<month no> and month >=<month no>

Hope this helps..

Thanks,

Bala

10|10000 characters needed characters exceeded
• Former Member
Posted on Jan 06, 2014 at 09:22 AM

Thank you guys for your suuggestion ðŸ˜Š

Ganapathy - by your views we can show Quarters but i want to show months as well ,thansk

Arjit - we r almost done it but formatting and hiding the columns is bit difficult , thanks man

Bala - cross tab is the requrement and create 1 variables will hamper the performance , thanks for views