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

x
?
Solved

conditional running total in crystal reports

Posted on 2010-11-22
12
Medium Priority
?
923 Views
Last Modified: 2013-11-15
I'm using Crystal reports 2008 and sql server 2005. I've a report with data as following:
Dealer Name     Account #               Inspection Type
123                   4000857733            DSI
123                   4000857733            No Inspection
123                   4000857733            No Inspection
I've a group on Dealer Name.
Whenever I've a situation like above, where there are multiple inspection types, I always want to do a distinct count of account number based on the inspection type but if there's DSI OR there's both DSI and No Inspection for the same account number, then the running total for the no inspection type condition should be 0 and it should be 1 for DSI. Like in above case, the total for DSI should be 1 and total for No Inspection should be 0.
Please help.

Thanks.
Subash
0
Comment
Question by:subash011
[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
  • 4
  • 2
  • 2
  • +2
12 Comments
 
LVL 7

Expert Comment

by:mkobrin
ID: 34194038
Is the account# unique to the dealer name, and/or can a dealer name have multiple account numbers?
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 34194164
you can group by inspection type then do a count for each type.

What if there is only No Inspection?

mlmcc
0
 

Author Comment

by:subash011
ID: 34196749
mkobrin: Yes a dealer can have multiple account numbers. For eg.,
Dealer Name     Account #               Inspection Type
123                   4000857733            DSI
123                   4000857733            No Inspection
123                   4000857733            No Inspection
123                   4000857734            DSI
123                   4000857735            No Inspection
mlmcc: I did a group on inspection type and did a count. However, it works for other condition except if there's only No Inspection, like in above eg, I don't get a count of 1 for account number 4000857735. The business requirement is if there's more than 1 inspection type for the same account no, DSI and No Inspection, i should only count for DSI. If there's only No Inspection I should count it as No Inspection. In above example, my total for DSI should be 2 (1 for account # 4000857733 and 1 for account # 4000857734) and total for No Inspection should be 1 (for account # 4000857735). Instead I get total for DSI = 2 and total for No Inspection = 3. Please help. Thanks.

0
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 
LVL 11

Expert Comment

by:tickett
ID: 34221430
Create a formula account_no with the code:

If {inspection_type} = "no inspection" then
 1
else
 {account_number}

Then distinct count that.
L
0
 

Author Comment

by:subash011
ID: 34223887
That formula won't work. I think what you meant is:
If {inspection_type} = "no inspection" then
 1
else
0
Then distinct count that. If I do a distinct count on above formula, I get a count of 2. One for DSI and other for No Inspection.
Please advise.
0
 
LVL 35

Accepted Solution

by:
James0628 earned 1200 total points
ID: 34227643
I think that you're going to have to use formulas and variables for this.

 You'll need to add a group on the Account #, if you don't already have one.

 Create a formula like the following and put it in the report header to initialize the variables:

NumberVar no_insp_count;
NumberVar DSI_count;
no_insp_count := 0;
DSI_count := 0;
""

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

 Create 2 formulas like the following (let's call them @no_insp and @DSI):

// no_insp
if {Inspection Type} = "No Inspection" then
  1


// DSI
if {Inspection Type} = "DSI" then
  1


 Create a formula like the following and put it in the Account # group footer:

NumberVar no_insp_count;
NumberVar DSI_count;
if Sum ({@DSI), {Account #}) = 0 then
  no_insp_count := no_insp_count + 1
else
  DSI_count := DSI_count + 1;
""

 The idea is that if there are no DSI records for an account, increment the "No Inspection" count by 1, otherwise increment the DSI count.

 Then use formulas like the following to output the counts:

NumberVar no_insp_count


NumberVar DSI_count



 I don't think you've said whether you're looking for counts for the whole report or each dealer or something else.  If it's for the whole report, you can use formulas like the last 2 above in the report footer.  If it's for the dealer, you can use the formulas in the dealer group footer, but you'll also need to put the first formula above (to initialize the variables) in the dealer group header (in addition to the report header), to reset the counts for each new dealer.  If you need both, you'll need to add 2 more variables, one set for the dealer counts and one for the grand total counts.

 James
0
 
LVL 11

Expert Comment

by:tickett
ID: 34228975
Can you attach the report with data? (or even without data should help).

My formula should work if you've got the grouping setup how i'm imagining.

L
0
 
LVL 35

Expert Comment

by:James0628
ID: 34229561
tickett,

 I don't think a formula like that is going to work, for at least a couple of reasons.  First of all, subash011 wants a separate count for DSI and "No Inspection", and obviously there's no way to get both of those by doing a distinct count on one formula.  And that formula doesn't take into account the fact that the "No Inspection" count should only be incremented when an account has no DSI records.

 James
0
 
LVL 101

Assisted Solution

by:mlmcc
mlmcc earned 300 total points
ID: 34232979
Here is a report that provides the data you want.  If you need an overall summary you will need to use arrays to contain the data.

mlmcc
Q-26632980.rpt
Q-26632980.mdb
0
 

Author Comment

by:subash011
ID: 34284128
Thanks everyone! You guys are great.
mlmcc: Thank you so much for uploading the report with database and looking into for me. I looked at the report and its partially right. It's working for DSI but not for No Inspection. For instance, the count for account no. 4000857735 for no inspection should be 1 but it's 0 in the report. But that was a huge help for me as I created my own version of formula based on your logic. Thanks again.
0
 

Author Closing Comment

by:subash011
ID: 34284154
The solutions provided were great although not exact but gave me few different ideas based on which I created formulas to solve the problem. However without your expertise that wouldn't be possible.
0

Featured Post

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Question has a verified solution.

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

Hi, In my previous Article  (http://www.experts-exchange.com/Database/Reporting/A_15199-Introduction-to-Microstrategy.html)I discussed some basic understanding of Microstrategy that how we can get in Intro of Microstrategy (MSTR). Now it's tim…
I recently went through setting up a JasperReports Server using the AWS EC2 instance, and this article will cover some basic administration tasks I had to perform.
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…

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