Avatar of csprague7331
csprague7331Flag for United States of America asked on

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!!
DatabasesCrystal ReportsSQL

Avatar of undefined
Last Comment
csprague7331

8/22/2022 - Mon
analliah

could you attach sample of your rpt or view of desing view
SOLUTION
habibb

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER
csprague7331

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...  
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

Open in new window

ScreenShot.doc
DaveNDarlene

Have you ever tried a Manual Running Total? > lots of flexibility!  It has helped me out of many pickles.
Your help has saved me hundreds of hours of internet surfing.
fblack61
ASKER
csprague7331

How do I create a Manual Running Total?
ASKER CERTIFIED SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER
csprague7331

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?
DaveNDarlene

Not sure, I seldom do Cross Tabs
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER
csprague7331

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.