Crosstab SUM returning line count 2

[FMHarris]

I've created a crosstab in CR10(PRO) using a command against an Informix DB. I used the Count(*) function in the command to create totals that are summarized in the crosstab.

The problem I'm having is the SUM field in the crosstab is returning a line count rather than summing the integers (e.g.):

A B
1 x 10
2 xx 4
3 xxx 9

I would expect the summary of Column B to be 23 but I'm getting 3!

Can anyone help?

 

[IanWaterman]

Right click on Cross tab, select item being summarised in bottom right pane. Change summary button will now be available, click this and change summary type to sum. If Sum is not available that would indicate that your summarised field is not a number.

If its a numeric string creat a formula which converts to a number using the crystal function tonumber and thnm summarise on that formula instead. Sum will now be available. Possible cause of failure is if non numeric strings can be entered.

Ian

 

[lbass]

You should show the content of your command and also what your row, column, and summary fields are. From your post, it sounds like you are using a sum of the summary field.

-LB

 

[FMHarris]

Below is the content of the command:
------------------------------------------
SELECT
srvc_ctr_nm SrvcCntr,
a.user_id Employee,
rep_actn_cd,
CASE
WHEN (extend( end_tme, year to second ) - extend( strt_tme, year to second )) < "0 00:01:00" THEN "< 60"
WHEN (extend( end_tme, year to second ) - extend( strt_tme, year to second )) > "0 00:07:30" THEN "> 450"
ELSE "60 - 450"
END AHT,
COUNT(*) NMBR
FROM stat_rep_time a, usr_profile_master b
WHERE a.user_id = b.user_id AND
rep_actn_cd ="N" AND
srvc_ctr_nm ="{?reqSrvcCntr}" AND
a.user_id NOT MATCHES "uut*" AND
a.user_id NOT MATCHES "00*" AND
a.user_id NOT MATCHES "a2*" AND
a.user_id NOT MATCHES "csm*" AND
a.user_id !="SODS" AND
a.user_id != mgr_id AND
quit_reas_desc IS NOT NULL AND
EXTEND(end_tme, Year to Day) BETWEEN Extend({?reqLoDate}, Year to Day) AND Extend({?reqHiDate}, Year to Day)
GROUP BY srvc_ctr_nm, a.user_id, rep_actn_cd, strt_tme, end_tme
------------------------------------------

Crosstab Format:

--ROWS--
Command.SrvcCntr
Command.Employee
Command.AHT

--COLUMNS--
Command.Rep_Actn_Cd

--SUMMARIZED FIELDS--
[SUM of Command.NMBR]
[Percentage of SUM of Command.NMBR]

 

[lbass]

Does {command.NMBR} show the expected values if you place it in the detail section and insert groups on the fields you are using for rows?

-LB

 

[FMHarris]

No it doesn't. . .

Still counting not summarizing. Man this is weird!

 

[lbass]

If it is not showing the correct values in the detail section, then the command is set up incorrectly. Why are you using a command? What is it you think you can't do by setting up the report the usual way?

-LB

 

[FMHarris]

I'm using a command because I'm running a UNION query. And I've written a dozen other reports (UNION and non-UNION) that are CR Crosstabs and all of them SUM that field correctly.

 

[lbass]

Then I guess you did not share the actual query.

-LB

 

[FMHarris]

Below is the complete content of the command:
------------------------------------------
SELECT
srvc_ctr_nm SrvcCntr,
a.user_id Employee,
rep_actn_cd,
CASE
WHEN (extend( end_tme, year to second ) - extend( strt_tme, year to second )) < "0 00:01:00" THEN "< 60 "
WHEN (extend( end_tme, year to second ) - extend( strt_tme, year to second )) > "0 00:07:30" THEN "> 450 "
ELSE "60 - 450"
END AHT,
COUNT(*) NMBR
FROM stat_rep_time a, usr_profile_master b
WHERE a.user_id = b.user_id AND
rep_actn_cd ="N" AND
srvc_ctr_nm ="{?reqSrvcCntr}" AND
a.user_id NOT MATCHES "uut*" AND
a.user_id NOT MATCHES "00*" AND
a.user_id NOT MATCHES "a2*" AND
a.user_id NOT MATCHES "csm*" AND
a.user_id !="SODS" AND
a.user_id != mgr_id AND
quit_reas_desc IS NOT NULL AND
EXTEND(end_tme, Year to Day) BETWEEN Extend({?reqLoDate}, Year to Day) AND Extend({?reqHiDate}, Year to Day)
GROUP BY srvc_ctr_nm, a.user_id, rep_actn_cd, strt_tme, end_tme
UNION SELECT
srvc_ctr_nm SrvcCntr,
a.user_id Employee,
rep_actn_cd,
CASE
WHEN extend( end_tme, year to second ) - extend( strt_tme, year to second ) < "0 00:01:00" THEN "< 60 "
WHEN extend( end_tme, year to second ) - extend( strt_tme, year to second ) > "0 00:07:30" THEN "> 450 "
ELSE "60 - 450"
END AHT,
COUNT(*) NMBR
FROM stat_rep_time a, usr_profile_master b
WHERE a.user_id = b.user_id AND
rep_actn_cd IN("U","S","H","D") AND
srvc_ctr_nm ="{?reqSrvcCntr}" AND
a.user_id NOT MATCHES "uut*" AND
a.user_id NOT MATCHES "00*" AND
a.user_id NOT MATCHES "a2*" AND
a.user_id NOT MATCHES "csm*" AND
a.user_id !="SODS" AND
a.user_id != mgr_id AND
EXTEND(end_tme, Year to Day) BETWEEN Extend({?reqLoDate}, Year to Day) AND Extend({?reqHiDate}, Year to Day)
GROUP BY srvc_ctr_nm, a.user_id, rep_actn_cd, strt_tme, end_tme
------------------------------------------

Crosstab Format:

--ROWS--
Command.SrvcCntr
Command.Employee
Command.AHT

--COLUMNS--
Command.Rep_Actn_Cd

--SUMMARIZED FIELDS--
[SUM of Command.NMBR]
[Percentage of SUM of Command.NMBR]

 

[IanWaterman]

I do not know enough about your data but I would say you problem is your grouping, instead of grouping on

strt_tme, end_tme
you should group both queries on

(CASE
WHEN (extend( end_tme, year to second ) - extend( strt_tme, year to second )) < "0 00:01:00" THEN "< 60 "
WHEN (extend( end_tme, year to second ) - extend( strt_tme, year to second )) > "0 00:07:30" THEN "> 450 "
ELSE "60 - 450"
END)

Ian

 

[lbass]

I agree with Ian, that the grouping is suspect, but also, in what report section is the crosstab placed?

-LB

 

[FMHarris]

Thanks for all the input but I figured it out a little after midnight. The problem was in the UNION.

By default, the UNION operator removes duplicate rows from the result set. If you use UNION ALL, all rows are included in the results and duplicates are not removed.