Column Heading in Cross Tab

[shelby55]

Hello

I have a report with limited space in the width. I want to have the cross tab heading be different dependent on the number of quarters selected via the date prompt.

For instance &quot;if distinctcount({qtr}) <= 2 then qtrname1 else if distinctcount({qtr})>2 then qtrname2

However, I get the error message &quot;a cross-tab column has been specified on a non-recurring field&quot;. Is there any way around this?

Thanks in advance for your help!

 

[nagornyi]

qtrname1 and qtrname2 have in some way be related to the field on which you create columns. For example:

StringVar qtrname1 := {YourColumnField}[1 to 3];
StringVar qtrname2 := {YourColumnField}[1 to 5];
if distinctcount({qtr}) <= 2 then qtrname1 else qtrname2

Then you will be able to put this formula into crosstab columns.

 

[shelby55]

HI

Thanks for the assistance but it's still not working. This is the formula that I have since created but I get the same message.

StringVar qtrName1:=If {@DMonth} in [4 to 6] then &quot;Qtr1&quot; else
If {@Dmonth} in [7 to 8] then &quot;Q2&quot; else
If {@Dmonth} in [9 to 12] then &quot;Q3&quot; else
If {@Dmonth} in [1 to 3] then &quot;Q4&quot;;

StringVar qtrName2:=If {@DMonth} in [4 to 6] then &quot;Q1 & Q2&quot; else
If {@DMonth} in [7 to 8] then &quot;Q1 & Q2&quot; else
If {@DMonth} in [9 to 12] then &quot;Q3 & Q4&quot; else
If {@DMonth} in [1 to 3] then &quot;Q3 & Q4&quot;;

If DistinctCount({@Dmonth}) <= 6 then qtrname1 else qtrname2

The formula of DMonth is DatePart(&quot;m&quot;,{DischargeDate})

Any other suggestions?

 

[nagornyi]

To catch the problem, create a regular repor, not crosstab, and put the original fields, and intermediate formulae, and also your final formula on which you are trying to create the crosstab, into the detail section. Then you will see whether or not the field is recurring and (hopefully) why.

 

[shelby55]

Hi Nagornyi

Thanks again for your suggestion but the formula of qtrname (as above) works as it should in the detail section. If the date range I select is <= 6 distinct months than qtrname1 shows up and qtrname 2 if greater than 6 distinct months. Now what??

Thanks for any and all help you can give because this one is driving me nuts!!

Shelby