Running Totals based on a condition
I'm trying to create something similar to a running total for a Crystal report. I would like to find the average of a field "Score" on change of a date field. ( I also am using 2 groupings, first by "location" then by "Source".)
My big problem is that some of the "Score" values are null. I created a statement in my SQL converting all null fields to -1, hoping that I could create a running total field in Crystal to average fields only where the value does not equal -1. I select "Use a Formula" and input {Command.Score} <> -1 under "Evaluate" and then "Reset" on change of the date field, however this seems to be evaluating an aggregate average for each date for all 'Score" values in the report, and not the small sample I have in my grouping.
Is there a way I can average the values (not equal to -1) for each date within my grouping?
Thanks!!
My big problem is that some of the "Score" values are null. I created a statement in my SQL converting all null fields to -1, hoping that I could create a running total field in Crystal to average fields only where the value does not equal -1. I select "Use a Formula" and input {Command.Score} <> -1 under "Evaluate" and then "Reset" on change of the date field, however this seems to be evaluating an aggregate average for each date for all 'Score" values in the report, and not the small sample I have in my grouping.
Is there a way I can average the values (not equal to -1) for each date within my grouping?
Thanks!!
analliah
could you attach sample of your rpt or view of desing view
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I can't use 0 as a value in place of nulls because some of the Scores have a true value of 0...
I've copied all of my source code and a screen shot of a page in my report. If you look at my report you can see what I am trying to do, I have a location (Group #1) with multiple categories (Group #2). For each category in each location, there are19 Numerators and 19 Denominators, N-1 through N-19 and D-1 through D-19. My code is lengthy because I am calculating one field "Score' based on N/D for all 19.
I'm trying to find the average of these 19 score fields for each category in each location. I have a graph on the top of the page that does calculate an "Overall Score" or average score on change fo date, however it does not exclude the -1 values,. This is where I am stuck...
I've copied all of my source code and a screen shot of a page in my report. If you look at my report you can see what I am trying to do, I have a location (Group #1) with multiple categories (Group #2). For each category in each location, there are19 Numerators and 19 Denominators, N-1 through N-19 and D-1 through D-19. My code is lengthy because I am calculating one field "Score' based on N/D for all 19.
I'm trying to find the average of these 19 score fields for each category in each location. I have a graph on the top of the page that does calculate an "Overall Score" or average score on change fo date, however it does not exclude the -1 values,. This is where I am stuck...
SELECT ID, SOURCE, Unit, DateOfReview,'N-1' as label,[N-1], [D-1],
CASE WHEN [D-1] = 0 THEN -1 WHEN [N-1]is null THEN -1 ELSE CAST ((([N-1] / [D-1])*100)as int)
END AS Score
INTO #T_CS_Amb_Tracer1
FROM dbo.T_CS_ALL_N_D
GROUP BY ID,Unit, SOURCE, DateOfReview,[N-1], [D-1]
UNION ALL
SELECT ID,SOURCE, Unit, DateOfReview,'N-2' as label,[N-2], [D-2],
CASE WHEN [D-2] = 0 THEN -1 WHEN [N-2] is null THEN -1 ELSE CAST ((([N-2] / [D-2])*100)as int)
END AS Score
FROM dbo.T_CS_ALL_N_D
GROUP BY ID,Unit, SOURCE, DateOfReview,[N-2], [D-2]
UNION ALL
SELECT ID,SOURCE, Unit, DateOfReview,'N-3' as label,[N-3], [D-3],
CASE WHEN [D-3] = 0 THEN -1 WHEN [N-3] is null THEN -1 ELSE CAST ((([N-3] / [D-3])*100)as int)
END AS Score
FROM dbo.T_CS_ALL_N_D
GROUP BY ID,Unit, SOURCE, DateOfReview,[N-3], [D-3]
UNION ALL
SELECT ID,SOURCE, Unit, DateOfReview,'N-4' as label,[N-4], [D-4],
CASE WHEN [D-4] = 0 THEN -1 WHEN [N-4] is null THEN -1 ELSE CAST ((([N-4] / [D-4])*100)as int)
END AS Score
FROM dbo.T_CS_ALL_N_D
GROUP BY ID,Unit, SOURCE, DateOfReview,[N-4], [D-4]
UNION ALL
SELECT ID,SOURCE, Unit, DateOfReview,'N-5' as label, [N-5], [D-5],
CASE WHEN [D-5] = 0 THEN -1 WHEN [N-5] is null THEN -1 ELSE CAST ((([N-5] / [D-5])*100)as int)
END AS Score
FROM dbo.T_CS_ALL_N_D
GROUP BY ID,Unit, SOURCE, DateOfReview,[N-5], [D-5]
UNION ALL
SELECT ID,SOURCE, Unit, DateOfReview,'N-6' as label,[N-6], [D-6],
CASE WHEN [D-6] = 0 THEN -1 WHEN [N-6] is null THEN -1 ELSE CAST ((([N-6] / [D-6])*100)as int)
END AS Score
FROM dbo.T_CS_ALL_N_D
GROUP BY ID,Unit, SOURCE, DateOfReview,[N-6], [D-6]
UNION ALL
SELECT ID,SOURCE, Unit, DateOfReview,'N-7' as label,[N-7], [D-7],
CASE WHEN [D-7] = 0 THEN -1 WHEN [N-7] is null THEN -1 ELSE CAST ((([N-7] / [D-7])*100)as int)
END AS Score
FROM dbo.T_CS_ALL_N_D
GROUP BY ID,Unit, SOURCE, DateOfReview,[N-7], [D-7]
UNION ALL
SELECT ID,SOURCE, Unit, DateOfReview,'N-8' as label,[N-8], [D-8],
CASE WHEN [D-8] = 0 THEN -1 WHEN [N-8] is null THEN -1 ELSE CAST ((([N-8] / [D-8])*100)as int)
END AS Score
FROM dbo.T_CS_ALL_N_D
GROUP BY ID,Unit, SOURCE, DateOfReview,[N-8], [D-8]
UNION ALL
SELECT ID,SOURCE, Unit, DateOfReview,'N-9' as label,[N-9], [D-9],
CASE WHEN [D-9] = 0 THEN -1 WHEN [N-9] is null THEN -1 ELSE CAST ((([N-9] / [D-9])*100)as int)
END AS Score
FROM dbo.T_CS_ALL_N_D
GROUP BY ID,Unit, SOURCE, DateOfReview,[N-9], [D-9]
UNION ALL
SELECT ID,SOURCE, Unit, DateOfReview,'N-10' as label,[N-10], [D-10],
CASE WHEN [D-10] = 0 THEN -1 WHEN [N-10] is null THEN -1 ELSE CAST ((([N-10] / [D-10])*100)as int)
END AS Score
FROM dbo.T_CS_ALL_N_D
GROUP BY ID,Unit, SOURCE, DateOfReview,[N-10], [D-10]
UNION ALL
SELECT ID,SOURCE, Unit, DateOfReview,'N-11' as label,[N-11], [D-11],
CASE WHEN [D-11] = 0 THEN -1 WHEN [N-11] is null THEN -1 ELSE CAST ((([N-11] / [D-11])*100)as int)
END AS Score
FROM dbo.T_CS_ALL_N_D
GROUP BY ID,Unit, SOURCE, DateOfReview,[N-11], [D-11]
UNION ALL
SELECT ID,SOURCE, Unit, DateOfReview,'N-12' as label,[N-12], [D-12],
CASE WHEN [D-12] = 0 THEN -1 WHEN [N-12] is null THEN -1 ELSE CAST ((([N-12] / [D-12])*100)as int)
END AS Score
FROM dbo.T_CS_ALL_N_D
GROUP BY ID,Unit, SOURCE, DateOfReview,[N-12], [D-12]
UNION ALL
SELECT ID,SOURCE, Unit, DateOfReview,'N-13' as label,[N-13], [D-13],
CASE WHEN [D-13] = 0 THEN -1 WHEN [N-13] is null THEN -1 ELSE CAST ((([N-13] / [D-13])*100)as int)
END AS Score
FROM dbo.T_CS_ALL_N_D
GROUP BY ID,Unit, SOURCE, DateOfReview,[N-13], [D-13]
UNION ALL
SELECT ID,SOURCE, Unit, DateOfReview,'N-14' as label,[N-14], [D-14],
CASE WHEN [D-14] = 0 THEN -1 WHEN [N-14] is null THEN -1 ELSE CAST ((([N-14] / [D-14])*100)as int)
END AS Score
FROM dbo.T_CS_ALL_N_D
GROUP BY ID,Unit, SOURCE, DateOfReview,[N-14], [D-14]
UNION ALL
SELECT ID,SOURCE, Unit, DateOfReview,'N-15' as label,[N-15], [D-15],
CASE WHEN [D-15] = 0 THEN -1 WHEN [N-15] is null THEN -1 ELSE CAST ((([N-15] / [D-15])*100)as int)
END AS Score
FROM dbo.T_CS_ALL_N_D
GROUP BY ID,Unit, SOURCE, DateOfReview,[N-15], [D-15]
UNION ALL
SELECT ID,SOURCE, Unit, DateOfReview,'N-16' as label,[N-16], [D-16],
CASE WHEN [D-16] = 0 THEN -1 WHEN [N-16] is null THEN -1 ELSE CAST ((([N-16] / [D-16])*100)as int)
END AS Score
FROM dbo.T_CS_ALL_N_D
GROUP BY ID,Unit, SOURCE, DateOfReview,[N-16], [D-16]
UNION ALL
SELECT ID,SOURCE, Unit, DateOfReview,'N-17' as label,[N-17], [D-17],
CASE WHEN [D-17] = 0 THEN -1 WHEN [N-17] is null THEN -1 ELSE CAST ((([N-17] / [D-17])*100)as int)
END AS Score
FROM dbo.T_CS_ALL_N_D
GROUP BY ID,Unit, SOURCE, DateOfReview,[N-17], [D-17]
UNION ALL
SELECT ID,SOURCE, Unit, DateOfReview,'N-18' as label,[N-18], [D-18],
CASE WHEN [D-18] = 0 THEN -1 WHEN [N-18] is null THEN -1 ELSE CAST ((([N-18] / [D-18])*100)as int)
END AS Score
FROM dbo.T_CS_ALL_N_D
GROUP BY ID,Unit, SOURCE, DateOfReview,[N-18], [D-18]
UNION ALL
SELECT ID,SOURCE, Unit, DateOfReview,'N-19' as label,[N-19], [D-19],
CASE WHEN [D-19] = 0 THEN -1 WHEN [N-19] is null THEN -1 ELSE CAST ((([N-19] /[D-19])*100)as int)
END AS Score
FROM dbo.T_CS_ALL_N_D
GROUP BY ID,Unit, SOURCE, DateOfReview,[N-19], [D-19]
Select a.*, b.Label AS TracerLabel
INTO #T_CS_AMB_TRACER2
From #T_CS_Amb_Tracer1 a INNER JOIN
csprague.T_CS_D_TRACER_LABEL b ON a.label = b.N
Where a.Source = b.Source
Select *
From #T_CS_AMB_TRACER2
Drop Table #T_CS_Amb_Tracer1
Drop Table #T_CS_Amb_Tracer2
Have you ever tried a Manual Running Total? > lots of flexibility! It has helped me out of many pickles.
ASKER
How do I create a Manual Running Total?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks for your help... I tried creating the 3 formulas and the only problem I still have I think is with the reset formula placement... If you look back to the pdf example of my report, I need the summary to reset with each column of my Cross tab chart... is this possible?
Not sure, I seldom do Cross Tabs
ASKER
I re-solved the reset problem I had by re-creating the groups of the crosstab and then suppressing the new groups in my report. I was then able to create a standard running total field for the suppresed groups and use that in my chart.