Bug in Cross-Tab Control?

[lynchg]

I have a report that uses a cross-tab to display the data. The source of data for the report is a SQL Server view. The report is supposed to display the sum of the lengths of time that a service request spent in various statuses. The status field is the column field, row field is the request ID, and the data field is the sum of the time segments (grouped by status). The results are inconsistent, some of the sum values are correct and some aren't.

Since there is nothing I can do to directly control the values produced by the cross-tab I am at a loss. And this leads me to wonder if there is a bug in the cross-tab control. Has anyone heard of a bug with these?

I am using Crystal Reports 11.5 with a SQL Server 8 backend.

Thanks for any help anyone can offer.

 

[Madawc]

You need to give a few more details. Are you using a formula, and if so which. Also what wrong results are you getting?

A common error in Crystal is to include a record field twice, because it is linked to some other record that occurs twice and so occurs on two detail lines. If that might be the problem, try doing a test report to group the same data in the same way the crosstab does, but showing details. (If that's the problem, the way round is to group for the duplicates and to have totals at that group level.)

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

 

[lynchg]

Below is the SQL for the view that is the source of the data for the report, and also for the cross-tab control. This data deals with a service request system for our IT dept, a service request (SR), is usually a small job to address a specific problem or some smal IT job that isn’t large enough to be considered a project.

The RQST table holds these service requests, the RQST_ID field is a text field (ex: P119876), that the users use as the identifier for an SR, the unique identifier on that table is the RQST_KEY field, it is also the foreign key in the RQST_HSTRY table.

The RQST_HSTRY table holds the history of each SR as it progresses through its lifecycle, from one status to another, as people make notes, etc. We have status values (STS_VLU), which are grouped into status categories (STS_CAT). Each history record has a time in and time out value, thses are timestamps on when that record was created, the time out value of the first history record is the time in of the next history record. For example an SR is created at 03/20/2009 11:32:41am, the first status is “Requested”, that SR is updated and given a status of “Triage” at 03/21/2009 8:47:18 am, so it spent about 21 hrs and 19 min (about 0.89 days), in Requested status.

The long nested case statement is there to deal with all possible values that could be in the TIME_OUT field, that field in null until the next history record is created, so if it is null and the SR is not closed, we use getDate() because the SR is still in that status, if it is null and the SR is closed, we use the closed date timestamp because the TIME_OUT value of the very last history record will be null. I had to use seconds as the interval on these timestamp values and then divide by 86,400 to get an accurate value in days for the ‘StatusAge’.

The Summarized fields are Avg of StatusAge and Sum of StatusAge. I display the StatusAge sums for individual SRs in the detail lines and the averages of those sums at the bottom.

SELECT R.RQST_ID, RH.RQST_KEY, RH.RQST_HSTRY_KEY, RH.STS_VLU, RH.STS_CAT,
Cast(CASE WHEN RH.TIME_OUT IS NULL THEN CASE WHEN R.CLSD_DATE IS NULL THEN DATEDIFF(ss, RH.TIME_IN, getDate())
ELSE DATEDIFF(ss, RH.TIME_IN, R.CLSD_DATE) END ELSE DATEDIFF(ss, RH.TIME_IN, RH.TIME_OUT) END as Float)/86400 'StatusAge',
R.CRTE_DATE, R.CLSD_DATE
FROM dbo.RQST_HSTRY RH INNER JOIN dbo.RQST R ON RH.RQST_KEY = R.RQST_KEY
WHERE RH.STS_CAT IS NOT NULL
AND RH.STS_CAT NOT IN ('Completed', 'Waiting for Sub', 'Closed - MOB Project', 'Cancelled')

 

[lbass]

You should follow Madawc's suggestion and verify how the data is displaying in the detail section when grouped. You could create a manual crosstab that would resolve any duplication issues.

-LB

 

[lynchg]

I can fix this by grouping and summing in the view and not relying on the cross-tab to come up with the sums of time segments that each SR spent in each status.

I was just curious as to why the cross-tab control wasn't doing it properly, it seems like a bug to me.

The example that hit me as I was reviewing my report was that for one SR that entered into the Testing/Review status 3 separate times for 9.1 days, 2.7 days, and then 2.8 days, the report displayed the total as 9.1 days instead of the 14.6 days that should have appeared.

 

[lbass]

We don't have enough info to evaluate why that happened. We would have to know how the data displays if placed in the details section and then grouped by status and request ID.

-LB

 

[lynchg]

The cross-tab control is in the group footer of the work team field. A work team is assigned to work on a service request (SR), it is possible that during the lifetime of an SR that it could be passed from one team to another. The users select the dept and team (or possibly all teams within a dept), the report groups on teams.

The total time that an SR spends in each particular status while assigned to a particular team is what is displayed on the report. The critical fields in the data are the SR number, status, the total time spent in each status for a particular team, and team name. If an SR spent 3.4 days in Tech Analysis for Team A and then 2.7 days in Tech Analysis for Team B those would be 2 separate records.

The cross-tab is displayed with the SR number as the leftmost column, the various statuses as column headers, and the time spent (in days), as the values in individual cells.

 

[lynchg]

I should also state that the problem that I was having initially has been resolved. That problem was really with my querying, I added a field to my view that holds the total time per team per status.

The problem that I have now is that sometimes some of the time-spent values are not displaying and I can't figure out why. I have copied the text of "Show SQL Query" and run it in Query Analyzer just to verify that the report is really receiving the data that I expect. The raw data looks fine but some of the cells that should have data do not, I am not using any special formulas within the report that could be restricting the output.