| Author |
Message |
Nav522
Senior Member
Joined: 25 Aug 2009
Location: United States
Online Status: Offline
Posts: 166
|
  
Topic: Running total question?
Posted: 14 Mar 2011 at 2:19pm |
Hello Folks,
Am trying to figure out on how to get the count for this report but cudnt find a way.
Basically i have 3 groups like this.
GH1(status)
GH2(clientparty)
GH3(Event_id)
GH34Event_case_id)
D(suppressed)
GF1
GF2
GF3
GF4
RF
Basically am having trouble in getting the Count of coveragecode for each Event_case_id. I mean the data is like following
Event_id Case_id Coveragecode Amount
123 555365 LA $100
444556 LB $50
234 789990 LA $567
345 456677 LA $555
657788 LB $670
878999 LC $640
I want to get the total count for LA across all the events separately i.e. 3, LB count as 2 and finally LC as 1
I have the count of event_id and event_case_id in the report footer which is working fine.
Can anyone throw any ideas on this?
Edited by Nav522 - 15 Mar 2011 at 5:12pm
|
 IP Logged |
|
|
|
lockwelle
Moderator
Joined: 21 Dec 2007
Online Status: Offline
Posts: 4374
|
Posted: 15 Mar 2011 at 3:03am |
Just to be sure, you want to have a count for each LA/LB/LC. Are there any other values that it could be? If not, you could use Running Totals, 1 for each setting or you could create a set of formulas that would do the same. I tend to use the formulas, as I find them easier, but many use Running Totals, as everything is laid out for you.
HTH
|
| IP Logged |
|
FrnhtGLI
Senior Member
Joined: 22 May 2009
Online Status: Offline
Posts: 347
|
Posted: 15 Mar 2011 at 3:04am |
If you know for sure the Coveragecodes are always going to be either LA, LB, or LC then you can create a 3 formula running total. The first formula would be the reset formula that you would put in the Group Header:
whileprintingrecords;
global numbervar nLA:=0;
global numbervar nLB:=0;
global numbervar nLC:=0;
The second formula would be placed on the detail line the information is being displayed on. It can be suppressed. This is the calculation formula:
whileprintingrecords;
global numbervar nLA:=nLA + (if {table.Coveragecode}='LA'
then 1
else 0);
global numbervar nLB:=nLB + (if {table.Coveragecode}='LB'
then 1
else 0);
global numbervar nLC:=nLC + (if {table.Coveragecode}='LC'
then 1
else 0);
Then in the footer, you need a formula for each numbervar to display it:
whileprintingrecords;
global numbervar nLA;
(create two more, one for nLB and nLC)
That should get you the desired result.
EDIT:
What lockwelle said :-) Edited by FrnhtGLI - 15 Mar 2011 at 3:07am
|
|
|< /\ '][' ( )
|
| IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 15 Mar 2011 at 3:48am |
or if you have a lot more codes to deal with or possible new codes coming into the report later toss in a crosstab to dynamically deal with these by using coverage code as either column or row based on your desired output look and a summary as a count of that field.
You can get rid totals and lines to get the look you want.
|
| IP Logged |
|
Nav522
Senior Member
Joined: 25 Aug 2009
Location: United States
Online Status: Offline
Posts: 166
|
Posted: 15 Mar 2011 at 5:10pm |
Hi everyone Thanks for the feedback, I will always have the values 'LA','LB' and 'LC'. But for some reason i think am getting the duplicate counts. I need to count the coveragecode once per Event. For 'LC' the total count is showing 3 instead of 1. Do i need to count based on the whole group or something?
I tried using crosstab as you have mentioned but it returns the same counts as these formulas.
The formulas are placed in GH1 as mentioned
i.e. @Reset formula in the Group Header 1(status)
placed the main formula in the detail section and suppressed it.
@mainform as
whileprintingrecords;
global numbervar nLA:=nLA + (if {table.Coveragecode}='LA'
then 1
else 0);
global numbervar nLB:=nLB + (if {table.Coveragecode}='LB'
then 1
else 0);
global numbervar nLC:=nLC + (if {table.Coveragecode}='LC'
then 1
else 0)
and finally created the formula for displaying the count in the ReportFooter
as
@print as global numbervar nLC;
Edited by Nav522 - 15 Mar 2011 at 5:14pm
|
| IP Logged |
|
lockwelle
Moderator
Joined: 21 Dec 2007
Online Status: Offline
Posts: 4374
|
Posted: 16 Mar 2011 at 3:24am |
well, what I typically do when I can't figure out why my formula is not working, is I display the result...so add nLC at the end of the formula and unsuppress it.
Just as note, I simply put "" at the end of all my formulas that I don't want to see...CR prints an empty string.
HTH
|
| IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 16 Mar 2011 at 3:53am |
for the ct did ou set it as doing a count or a distinctcount. you would want to make sure it is a count.
You coukld aslo try chnaging it to a ditinctcount of case_id field
|
| IP Logged |
|
Nav522
Senior Member
Joined: 25 Aug 2009
Location: United States
Online Status: Offline
Posts: 166
|
Posted: 16 Mar 2011 at 4:28am |
Hi Dblank,
I followed your suggestion and created a running total with a distinct count on event_case_id
and added formula in the
Evaluate as {EVENT_CASE.PAYMENT_COVERAGE_CODE} = 'LA'
Now i have another question. I have a amount for the same thing. Howw can i calculate the amount using running total?
Thanks a Bunch.
|
| IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 16 Mar 2011 at 4:30am |
|
i don't quite understand your question. can you explain further, please.
|
| IP Logged |
|
Nav522
Senior Member
Joined: 25 Aug 2009
Location: United States
Online Status: Offline
Posts: 166
|
Posted: 16 Mar 2011 at 7:33am |
I want to display the sum of the field Table.Amount for each payment_coverage_code. i.e. for example if i want to display Total Sum of the amount for code 'LB' it will be 720.
Event_id Case_id Coveragecode Amount
123 555365 LA $100
444556 LB $50
234 789990 LA $567
345 456677 LA $555
657788 LB $670
878999 LC $640
I have a running total set up with the Field to Summarize as Table.Amount
and used a formula in the evaluate as
{EVENT_CASE.PAYMENT_COVERAGE_CODE} = 'LB'
I had this placed this RT in the report footer. And it shows incorrect amount(looks like its calculating the duplicates as well)
The total sum for LB should be 720. I believe i have to sum it on the CaseID level as i had the logic built for calculating the Count. But am quite not sure of ideas how to write that. Does it makes sense? Edited by Nav522 - 16 Mar 2011 at 8:37am
|
| IP Logged |
|
|
|
