Use a Formula as a Group Name 1

[sammybee77]

Hi All,

I'm using the formula below to group my report, however where I ask it to group on multiple ID's the group names fall over. ANy ideas?

Cheers

Sam

IF {FieldA} = 138 then 'UK Contract'
else if {FieldA} = 139 then 'CE Contract'
else if {FieldA} = 137 then 'UK Perm'
else if {FieldA} = 136 then 'CE Perm'
else if {FieldA} in [138,139] then 'Total Contract'
else if {FieldA} in [137,136] then 'Total Perm'
else if {FieldA} in [138,137] then 'UK Total'
else if {FieldA} in [139,136] then 'CE Total'
else if {FieldA} in [138,139,137,136] then 'PRS Total'

 

[troyboy]

Sam,

I am presuming you are defining a new Formula field with the formula attached. If not that is what I suggest you do and then use the formula field as your group.

 

[dgillz]

Your formula makes no sense. If the value of fieldA is 136, 137, 138, or 139, it will never make it past the 4th line of code.

What are you trying to accomplish here? Are you grouping on this formula field or are you trying to make a group name via a formula as your post's title suggested? Software Training and Support for Macola, Crystal Reports and Goldmine
714-348-0964
dgilsdorf@mchsi.com

 

[sammybee77]

HI,

I'm trying to create a crosstab report where the columns at the top are months and the rows are my formula. So the first four rows of the formula are fine. The other rows are combinations to maket totals per month. Any ideas?

Many thanks

Sam

 

[Naith]

Sammy,

If you look at an excerpt of your formula, what you're saying is:

IF {FieldA} = 138 then 'UK Contract'
else if {FieldA} = 139 then 'CE Contract'
...
else if {FieldA} in [138,139] then 'Total Contract'

Logically speaking, 'Total Contract' will never get assessed - because to be a 'Total Contract' Field A must either equal 138, or 139. But you've already said in lines one and two that if Field A is equal to 138 then it's a 'UK Contract', and if it's equal to 139, then it's a 'CE Contract'. This eliminates all 138s and 139s before the formula even gets close to the 'Total Contract' part.

Naith

 

[dgillz]

Sammy-

What do you mean by "combination"? As both Naith and I have pointed out, lines 5 thru 9 of your code will never be evaluated. Software Training and Support for Macola, Crystal Reports and Goldmine
714-348-0964
dgilsdorf@mchsi.com

 

[sammybee77]

Thanks for your help! Do you know of any way that I could group with total rows? e.g combinations of the first four lines to get Totals?

Many thanks

Sam

 

[dgillz]

Sammy,

I don't understand your question. Please define the term "combinations" for me. Software Training and Support for Macola, Crystal Reports and Goldmine
714-348-0964
dgilsdorf@mchsi.com

 

[Naith]

I think I might be seeing what you're getting at.

You want to group all the 1st criteria one group, all the 2nd criteria in another group, and then group both of them together and provide a total.

To do that, you want to be splitting your formula into two formulae, and group on them respectively.

//@Group1
If {FieldA} in [138,139] then 'Total Contract'
else if {FieldA} in [137,136] then 'Total Perm'
else if {FieldA} in [138,137] then 'UK Total'
else if {FieldA} in [139,136] then 'CE Total'

//@Group2
IF {FieldA} = 138 then 'UK Contract'
else if {FieldA} = 139 then 'CE Contract'
else if {FieldA} = 137 then 'UK Perm'
else if {FieldA} = 136 then 'CE Perm'

If these are all your field values, you don't need a PRS group. Just create grand total summaries, and they can be your PRS Total.

Naith

 

[sammybee77]

Hi Guys,

Unfortunately that doesn't work as the ID's for e.g. UK Total in Group 1 are already specified in Total Contract and Total Perm. Also would a crosstab report work if there is more than one grouping?

Many thanks

Sam

 

[Naith]

You're right, of course. Group1 should be split into 2 fields.

//One
If {FieldA} in [138,139] then 'Total Contract'
else if {FieldA} in [137,136] then 'Total Perm'
//Two
If {FieldA} in [138,137] then 'UK Total'
else if {FieldA} in [139,136] then 'CE Total'

Naith

 

[SmpTraining]

Yes, you will be better off using a cross-tab and including the month as the column and your formula as the row. Totals will be added automatically to the rows and columns.

I don't see why the cross tab would need more than one grouping given your explanation, but yes, you can have more than one level of grouping. Steve Phillips, Crystal Consultant

 

[Ngolem]

SammyBee77 -

Personally I think you are doing this the hard way.

You haven't laid out the structure of your report very well but I am guessing from this formula it would look something like this

Jan Feb Mar Apr May Jun Jly Aug Sep Oct Nov Dec
UK Contract
CE Contract
UK Perm
CE Perm
Total Contract
Total Perm
UK Total
CE Total
PRS Total

This can easily be done using arrays storing the totals and the report printed in the report or Group footer if it is to be repeated under another criteria

An initialize formula to set up the arrays (only 4 arrays are required since the last 5 rows are really various sums of the first 4 rows)

@initialize (suppressed in report header)
WhilePrintingRecords;
NumberVar array UKC := [0,0,0,0,0,0,0,0,0,0,0,0];
NumberVar array CEC := [0,0,0,0,0,0,0,0,0,0,0,0];
NumberVar array UKP := [0,0,0,0,0,0,0,0,0,0,0,0];
NumberVar array CEP := [0,0,0,0,0,0,0,0,0,0,0,0];
" "; //required to make the formula legal


Now Group1 the report by FieldA

suppress Group1 header,footer and detail section

Place the following formula in the details section

@Calculation (suppressed in the detail)
WhilePrintingRecords;
NumberVar array UKC ;
NumberVar array CEC ;
NumberVar array UKP ;
NumberVar array CEP ;

IF {table.FieldA} = 138 then
UKC[month({table.date})] := UKC[month({table.date})] +
{table.value};
if {FieldA} = 139 then
CEC[month({table.date})] := CEC[month({table.date})] +
{table.value};
if {FieldA} = 137 then
KUP[month({table.date})] := KUP[month({table.date})] +
{table.value};
else if {FieldA} = 136 then
CEP[month({table.date})] := CEP[month({table.date})] +
{table.value};


Now all your calculations are done ...now it is just setting up your table in the Report footer....this is a little tedious but the formulas are easily cloned for the different months...I will show the formulas for the month of January

@Display_Jan_UKContract
WhilePrintingRecords;
NumberVar array UKC ;

UKC[1];

@Display_Jan_UKPerm
WhilePrintingRecords;
NumberVar array UKP ;

UKP[1];

@Display_Jan_CEContract
WhilePrintingRecords;
NumberVar array CEC ;

CEC[1];

@Display_Jan_UKPerm
WhilePrintingRecords;
NumberVar array CEP ;

CEP[1];


@Display_Jan_TotalContract
WhilePrintingRecords;
NumberVar array UKC ;
NumberVar array CEC ;

UKC[1] + CEC[1]

@Display_Jan_TotalPerm
WhilePrintingRecords;
NumberVar array UKP ;
NumberVar array CEP ;

UKP[1] + CEP[1]

I think you get the idea...it is just a manner of adding the appropriate array elements to get the other January totals.

The formulas as you can see are easily cloned for the other months

This is a manual crosstab report...I hate using the Crystal crosstab since I have more control this way.

If you were repeating this table over and over then the initialize formula would go in the Group header that is repeated and the crosstab would be in the footer of that group.

Hope this helps

Jim Broadbent

 

[sammybee77]

Hi Jim,

Thank you - that's fantastic. Is there anyway I can amend the calculation formula so it still displays months but only for this year?

Many thanks

Sam

 

[Ngolem]

if you only want data for this year then just specify that in your record select formula.

year(table.date) = year(currentdate)

or you could use a parameter to specify a particular year to make it more flexible

year(table.date) = {?Year}

Using arrays this way allows you to fill in any data range (for a one year period of course.)

If you want to suppress months where there is no data you can do this 2 ways

In the numeric format tab...you can suppress zero

this may be ok...except that if the real result is zero
it will be suppressed as well. otherwise you can initialize your arrays to a ridiculous number (say -999999) but this becomes more complicated to process

Jim Broadbent