Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Running Total Crystal Reports Formula

Posted on 2009-05-13
5
Medium Priority
?
1,067 Views
Last Modified: 2012-05-06
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_NUMBER}) like "INC00000".
The Column "Total" is calculated using the formula below. I am getting 2 counts for one ({HPD_HELP_DESK.INCIDENT_NUMBER}. 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
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
)

Open in new window

0
Comment
Question by:angel7170
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 35

Expert Comment

by:James0628
ID: 24391679
There was no attached document, so I can't see your example.

 > I have grouped the report based on field
 > ({HPD_HELP_DESK.INCIDENT_NUMBER}) like "INC00000".

 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
0
 

Author Comment

by:angel7170
ID: 24394296
Sorry! I have attached it now.

Also, I have grouped based on the field ({HPD_HELP_DESK.INCIDENT_NUMBER})  and not by
({HPD_HELP_DESK.INCIDENT_NUMBER}) 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
0
 
LVL 35

Expert Comment

by:James0628
ID: 24400641
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_RESOLVED_BY} = GroupName ({@Analyst})) and
({HPD_HELP_DESK.INCIDENT_NUMBER}) <> next ({HPD_HELP_DESK.INCIDENT_NUMBER})  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_NUMBER}.

 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
0
 

Author Comment

by:angel7170
ID: 24411729
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.


0
 
LVL 35

Accepted Solution

by:
James0628 earned 500 total points
ID: 24417430
Do you want to keep the Total column where it is?

 If you don't want to see the Total count on each line, just suppress that field.  The formula will still accumulate your total, but you won't see it on every line.

 Do you show a group footer for the Analyst group?  If so, that would seem like the logical place to output the count for each Analyst.  Create a formula like the following (call it whatever you want) and put it in the Analyst group footer:

WhilePrintingRecords;
Global NumberVar Total


 That will simply output the value in Total.


 If you want this running total to be for each Analyst, then you also need to reset the count for each Analyst.  Otherwise, it will just keep growing throughout the report.  Create a formula like the following (call it whatever you want) and put it in the Analyst group header.  It will reset Total to 0 for each new Analyst.

WhilePrintingRecords;
Global NumberVar Total := 0;
""

 The "" at the end is just so the formula doesn't actually produce any output on the report.

 I'm assuming that you don't have the Analyst group set to repeat the group header on each new page.  If you do, you'll need to do something a little different to reset the count, because putting that formula in that group header would reset the count whenever a new page was started.

 James
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Crystal Reports: 5 Tests for Top Performance It is complete, your masterpiece report.  Not only does it meet your customer’s expectations, it blows them out the water, all they want is beautifully summarised and displayed in a myriad of ways. …
Hot fix for .Net Crystal Reports 10.2.3600.0 to fix problems with sub reports running on 64 bit operating systems ISSUE: Reports which contain subreports fail with error "Missing Parameter Value" DEPLOYMENT SERVER OS: Windows 2008 with 64 bi…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…
Suggested Courses

722 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question