angel7170
asked on
Running Total Crystal Reports Formula
Hello,
I am using Crytsal Reports XI. I am using the formula as mentioned below to do a running total. I have attached a sample of the report. I have grouped the report based on field ({HPD_HELP_DESK.INCIDENT_N UMBER}) like "INC00000".
The Column "Total" is calculated using the formula below. I am getting 2 counts for one ({HPD_HELP_DESK.INCIDENT_N UMBER}. You can see I have highlighted in red in the attached document . I just want to distinctcount this field when the condition below is met.
Please assist. Thank you
I am using Crytsal Reports XI. I am using the formula as mentioned below to do a running total. I have attached a sample of the report. I have grouped the report based on field ({HPD_HELP_DESK.INCIDENT_N
The Column "Total" is calculated using the formula below. I am getting 2 counts for one ({HPD_HELP_DESK.INCIDENT_N
Please assist. Thank you
WhilePrintingRecords;
Global NumberVar Total;
//Resolved
if IsNull({@Resolved Date}) or({@Resolved Date} in {?Start Date} to {?End Date} and
{HPD_HELP_DESK.LAST_RESOLVED_BY} = GroupName ({@Analyst})) and
({HPD_HELP_DESK.INCIDENT_NUMBER}) <> next ({HPD_HELP_DESK.INCIDENT_NUMBER}) or
//Touched
(
{@Touched Date} in {?Start Date} to {?End Date} and
{@Touched}like {@Analyst} and
not(next({@Touched}) like {@Analyst}) and
({HPD_HELP_DESK.INCIDENT_NUMBER} = next({HPD_HELP_DESK.INCIDENT_NUMBER}))
or
( {@Touched}like {@Analyst} and
({HPD_HELP_DESK.INCIDENT_NUMBER}) <> next ({HPD_HELP_DESK.INCIDENT_NUMBER}))
)
then
(
Total := Total + 1;
Total
)
ASKER
Sorry! I have attached it now.
Also, I have grouped based on the field ({HPD_HELP_DESK.INCIDENT_N UMBER}) and not by
({HPD_HELP_DESK.INCIDENT_N UMBER}) like "INC00000".
I tried to tell, when you see the attachment, the field will start with INC...
My apologies.. I couldn't tell properly.
sample.doc
Also, I have grouped based on the field ({HPD_HELP_DESK.INCIDENT_N
({HPD_HELP_DESK.INCIDENT_N
I tried to tell, when you see the attachment, the field will start with INC...
My apologies.. I couldn't tell properly.
sample.doc
First of all, you have several conditions in the formula joined by OR and AND. I'd add some () to make sure that those conditions will be evaluated the way you want. For example:
//Resolved
if IsNull({@Resolved Date}) or({@Resolved Date} in {?Start Date} to {?End Date} and
{HPD_HELP_DESK.LAST_RESOLV ED_BY} = GroupName ({@Analyst})) and
({HPD_HELP_DESK.INCIDENT_N UMBER}) <> next ({HPD_HELP_DESK.INCIDENT_N UMBER}) or
//Touched
I'm guessing that everything above //Touched, before the OR, is one set of conditions. If so, I'd put () around all of that. It's possible that what you have is evaluating exactly the way you want it to, but I always use () when combining conditions with OR and AND, so that I _know_ how they will be evaluated.
After //Touched there are more AND's, an OR and another AND. If the conditions before and after that OR are meant to be separate sets of conditions (just guessing here), I'd put () around them as well.
Beyond that, there's only so much I can tell without knowing more about your data, report structure, etc., but ...
It appears that this formula is meant to be a combination of the "Resolved" and "Touch" counts. Every time you have either of those, the count (the Total variable) goes up by 1. What's on the sample page you posted makes perfect sense. The "Touched" conditions are (apparently) met on the first record in the first group, so "Total" is 1. The "Resolved" conditions are (apparently) met on the last record in the first group, so "Total" goes to 2.
> I am getting 2 counts for one ({HPD_HELP_DESK.INCIDENT_N UMBER}.
It's not really two different counts. As you said yourself, that formula does a running total. All you're seeing is each time the running total is incremented. The formula is written so that it only outputs the Total variable when it changes it, so on the lines that don't have "Touched" or "Resolved", the formula just produces 0. If it output the Total variable every time, you'd get something like 1, 1, 1, 1, 1, 1, 2, 2, 3, 3, 3, 3, 4, 4, etc.
The question is, if you don't want to see the running total on every line, where do you want to see it?
On a related note, is that running total meant to be the total for the whole report or each INCIDENT_NUMBER or ??? As it stands, it appears to be a total for the whole report.
James
//Resolved
if IsNull({@Resolved Date}) or({@Resolved Date} in {?Start Date} to {?End Date} and
{HPD_HELP_DESK.LAST_RESOLV
({HPD_HELP_DESK.INCIDENT_N
//Touched
I'm guessing that everything above //Touched, before the OR, is one set of conditions. If so, I'd put () around all of that. It's possible that what you have is evaluating exactly the way you want it to, but I always use () when combining conditions with OR and AND, so that I _know_ how they will be evaluated.
After //Touched there are more AND's, an OR and another AND. If the conditions before and after that OR are meant to be separate sets of conditions (just guessing here), I'd put () around them as well.
Beyond that, there's only so much I can tell without knowing more about your data, report structure, etc., but ...
It appears that this formula is meant to be a combination of the "Resolved" and "Touch" counts. Every time you have either of those, the count (the Total variable) goes up by 1. What's on the sample page you posted makes perfect sense. The "Touched" conditions are (apparently) met on the first record in the first group, so "Total" is 1. The "Resolved" conditions are (apparently) met on the last record in the first group, so "Total" goes to 2.
> I am getting 2 counts for one ({HPD_HELP_DESK.INCIDENT_N
It's not really two different counts. As you said yourself, that formula does a running total. All you're seeing is each time the running total is incremented. The formula is written so that it only outputs the Total variable when it changes it, so on the lines that don't have "Touched" or "Resolved", the formula just produces 0. If it output the Total variable every time, you'd get something like 1, 1, 1, 1, 1, 1, 2, 2, 3, 3, 3, 3, 4, 4, etc.
The question is, if you don't want to see the running total on every line, where do you want to see it?
On a related note, is that running total meant to be the total for the whole report or each INCIDENT_NUMBER or ??? As it stands, it appears to be a total for the whole report.
James
ASKER
Thank you so much James!
I have my report grouped by GROUP1 = Analyst and then by GROUP2 = INCIDENT NUMBER
Basically, I want the total for each Analyst as to how many INCIDENT NUMBER they have either Resolved or Touched. So I am assuming the running total is for each INCIDENT NUMBER. So whenever it's TOUCHED OR RESOLVED it should do a count.
I have my report grouped by GROUP1 = Analyst and then by GROUP2 = INCIDENT NUMBER
Basically, I want the total for each Analyst as to how many INCIDENT NUMBER they have either Resolved or Touched. So I am assuming the running total is for each INCIDENT NUMBER. So whenever it's TOUCHED OR RESOLVED it should do a count.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
> I have grouped the report based on field
> ({HPD_HELP_DESK.INCIDENT_N
Are you sure about that?
That would just be True or False (the field is LIKE that value or not). So, you'd get one group for INC00000 (True) and one group for every other value (False).
Also, if you're just looking for "INC00000", you could just use =. LIKE is for pattern matching (eg. "INC00000*").
James