crosstab grouping puzzle

[MartUK]

I am using cr XI. I have 10 string field containg engineer code (upto first 8 characters). When old event opened it puts RE in posion 9 and 10
I want to create a crosstab based on first 8 characters only -{FSCALL.FSCALL_ALLOC_TO} [1 TO 8] .It looks fine in detail data but when I put in crosstab it puts in two summary rows EVEN though they both look the same in the crosstab

eg 1234 xxx
1234 yyy This second row is data on the instances when th original field had an RE in position 9 & 10
Whats going wrong, whats the solution please? Its as though the crosstab isnt seeing the formula or grouping correctly

This is a puzzle

 

[lbass]

Please show actual row labels as they appear in the crosstab. Your formula should work correctly, unless you used the field itself in the crosstab and used the formula only for display? You should be using the formula as the row.

-LB

 

[MartUK]

the row label is @EngCode and this is set to -{FSCALL.FSCALL_ALLOC_TO} [1 TO 8]

I may get for instance 8MFENTON xxx
8MFENTON y


If I edit the formula to just show -{FSCALL.FSCALL_ALLOC_TO} then the result would show

8MFENTO xxx
8MFENTO RE y

Thus it apears the formula not realy grouping in the crosstab, its hiding the RE but not striping it off to put in overall group - A puzzle? How can this be solved please. The database I am using is Oracle

 

[Madawc]

Have you tried left({FSCALL.FSCALL_ALLOC_TO}, 8)? That ought to group OK.

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[lbass]

You have to use the formula AS your rowfield:

{FSCALL.FSCALL_ALLOC_TO} [1 TO 8]

...not just for the label display.

-LB

 

[MartUK]

To Madawc, this makes no difference
To Lbass What do you actualy mean by using my formula@EngCode as the rowfield? I have created the formula and now dragged it in to the crosstab using the cross tab expert wizard. But as I say it doesnt group correctly

 

[lbass]

MartUK,

I can't see anyway that your formula ({FSCALL.FSCALL_ALLOC_TO} [1 TO 8])
will return the following:

8MFENTON xxx
8MFENTON y

...if {FSCALL.FSCALL_ALLOC_TO} returns:

8MFENTO xxx
8MFENTO RE y


It looks instead like you are using:

replace({FSCALL.FSCALL_ALLOC_TO},"RE"," ")

Please verify the content of your formula.

-LB

 

[MartUK]

No I have checked again . I show another example and the results it give in the detail section. The details section looks fine its ONLY when I put eith @EngCode or @SortCode as a row in the cross tab

field @engCode @shortcode value
A1234567RE A1234567 1234567 xxxx
A1234567 A1234567 1234567 yyy

In my cross tab I put either @EngCode or @ShortCode as the row and then do a distinct count of value

For @EngCode I would expect to get

A1234567 Count of xxxx

But what I see is

A1234567 count of xxxx
A1234567 count of yyy

The weird thing is that not only does it separtate out but that is does visibly show A1234567 and not A1234567 as one row and A1234567RE as the other row even though I know this is the case by looking at the data 'value'

This is a puzzle

 

[lbass]

Please show the contents of your formula {@Engcode} once more.

Also, please identify all row fields, column fields, and summary fields that you are using, and if they are formulas, please show the contents. There is something we are missing here, because crosstabs don't behave the way you describe.

-LB

 

[MartUK]

I have as I said used 2 attempted formulas

@EngCode is {FSCALL.FSCALL_ALLOC_TO} [2 TO 8]

@shortcode is ({FSCALL.FSCALL_ALLOC_TO} [1 TO 8])

I just use one of the above as a row field

Column fields are

@backboilers which is if{FSCALL.FSCALL_CALL_TYPE} ="EASV" AND {FSCALL.FSCALL_CATEGORY}= ["60","62","63","36"] THEN "1.SERV.CONV BLR" ELSE

if{FSCALL.FSCALL_CALL_TYPE} ="EASV" AND {FSCALL.FSCALL_CATEGORY}= "61" THEN "2.SERV BACK BLR" ELSE "3.NON SERVICE JOB"

followed by the database field {FSCALL.FSCALL_CALL_TYPE}

My summary fields are distinct count of call, followed by summary of @bonus multiplier where bonus multiplier is

if{FSCALL.FSCALL_ALLOC_TO} startswith "8" then {tbl_CallTypes.BonusServiceMultiplier} ELSE
if{FSCALL.FSCALL_ALLOC_TO} startswith "9" then {tbl_CallTypes.BonusDiagnosticMultiplier} ELSE 1

Its a very simple crosstab, Ive been using CR for years and never seen this which is why Im so puzzled. Pity I cant send you the report whith the data in it?

 

[lbass]

For your summary fields, is "call" a formula or a field? What is the summary you are using on the {@bonusmultiplier}?

I think {@bonusmultiplier} is forcing the two rows, since it is providing two different results based on the starting character of the field, but you aren't making that distinction in your row field. You might need to add a second row field like:

if {FSCALL.FSCALL_ALLOC_TO} startswith "8" then "BonusServiceMultiplier" else
if {FSCALL.FSCALL_ALLOC_TO} startswith "9" then "BonusDiagnosticMultiplier" else
"Other"

Also, not sure, but should the {FSCALL.FSCALL_ALLOC_TO} in the bonusmultiplier formula be changed to match the row field, i.e., use {@EngCode} or {@Shortcode}?

-LB

 

[MartUK]

No dont think this is the case. If we forget about the cross tab report and just look at the detail. If I group be ethier @EngCode or @Short code it works and just shows the one group. Its the cross tab that doesnt work. I think I will delete the cross tab and start again

 

[lbass]

I think you should test the idea that the summary is forcing the extra row before deciding it is an incorrect notion.

-LB