running totals in SCR8

[Guest_imported]

Here is the records got from table. I want sub totals to be calculated for in exams, out exams, in patients and out patients. I have written the desired result for sub totals. I am getting the details part correctly but 'am not able to figure out how to get this sub totals.

techid techtype examid patientid
----------------------------------
71010 OP 4539 693
J0151 IP 2020 696
J0151 IP 4494 1245
J1245 OP 2160 693
J1245 OP 4214 693
W4144 IP 4312 895
W4144 IP 4494 1169
W4144 OP 4214 693
W4144 IP 1748 693

techtype 'ip' ==> in patient/ exams
techtype 'op' ==> out patient/ exams

inex ==> in exams
outex ==> out exams
inpat ==> in patients
outpat ==> out patients

desired report ==>

techid inex outex totex inpat outpat totpat
-----------------------------------------------
71010 0 1 1 0 1 1
j0151 2 0 2 1 0 2
j1245 0 2 2 0 1 1(693 is one patient)
w4144 3 1 4 3 1 3
-----------------------------------------
subtotals:5 4 9 4 3 7

Pl. tell me how to get this sub totals. I tried with running sub totals by using formula option but couldn't get the desired sub totals. Can't we add running subtotal field again in running subtotal. Thanks in advance.

 

[FoxG]

Hi,

Assuming you are grouping on Techid, and suppressing the details...

Insert Summary... (count) on your detail fields, which will be reset on change of group Techid will give the result you show above

Insert Grand Total... (count) on your detail fields will generate the overall totals that you require

Hth,
Geoff

 

[Guest_imported]

Thanks geoff for the response. I am getting the details correct. The problem where 'am facing is with sub totals count for 'in exams' 'out exams' 'in patients' and 'out patients'........Thats bit complicated. Insert Summary won't work 'coz i should check whether the tech type is 'IP' or 'OP' depending on this the count for 'in exam' 'out exam' should be incremented.

Thanks

Satya

 

[kenhamady]

See my FAQ on common formula examples and look at conditional totals. Ken Hamady, On-site/Phone Crystal Reports Training/Consulting
Quick Reference Guide to using Crystal in VB

http://www.kenhamady.com/

 

[Guest_imported]

Thanks Ken. I went through yr FAQs but couldn't find solution for my problem. I went through Conditional Total using If Else and Conditional Total using running total. If u want my problem to be more elaborate i can write again. Pl. help, thanks in advance.

Satya.

 

[kenhamady]

You can use the technique:

"Conditional Total using If Else" Ken Hamady, On-site/Phone Crystal Reports Training/Consulting
Quick Reference Guide to using Crystal in VB

http://www.kenhamady.com/

 

[Guest_imported]

even that didn't help me ken.

Thanks

Satya

 

[kenhamady]

Create a formula field that says:

If {Tech type} = 'IP'
then 1 else 0

No do totals of this field.
Do one formula and total for each column that you need. Ken Hamady, On-site/Phone Crystal Reports Training/Consulting
Quick Reference Guide to using Crystal in VB

http://www.kenhamady.com/

 

[Guest_imported]

Thanks ken. That helped me. I also want to count the number of examids ('ip' and 'op') and number of patientids ('ip' and 'op'). For example the records returned are like this

techid techtype examid patientid
----------------------------------
J1245 OP 2160 693
J1245 IP 4214 693
W4144 OP 4312 895
W4144 IP 4494 1169
W4144 IP 4214 1169 (same patient as previous one)

desired result for sub totals is :

techid inexam outexam totexam inpat outpat totpat
-----------------------------------------------
subtotals: 3 2 5 2 2 4

The subtotals what i am getting is

techid inexam outexam totexam inpat outpat totpat
-----------------------------------------------
subtotals: 2 2 4 2 2 3
What it is doing is finally in subtotals its making distinct count of examids and patientids.

In report i grouped by techid. Added running totals for
examid and patientid. To count in exams, i created a running
total 'TotInExam',

Summary -
Field to summarize : examid
Type of summary : distinct countd

Evaluate :
Selected "Use formula" radio button and coded the formula as {examid} = 'ip' which means it should count only "in patients".

Reset :
Selected "Never" radio button.

Similarly i created the running totals "TotOutExams", "TotInPatients", "TotOutPatients".

Hope u got my problem. This is difficult.

Thanks

Satya

 

[kenhamady]

This is the other technique, using running totals with an Evaluate formula.

The reset should probably be once per group.
Try that and tell me if it works.

Ken Ken Hamady, On-site/Phone Crystal Reports Training/Consulting
Quick Reference Guide to using Crystal in VB

http://www.kenhamady.com/

 

[Guest_imported]

Ken, it didn't work. Some how i have to do it..... but i don't know how to do it.

 

[kenhamady]

It is working. You got a 2 for InExam because two records have the same ExamID. If you did a count instead of a distinct count you would have gotten a 3. Ken Hamady, On-site/Phone Crystal Reports Training/Consulting
Quick Reference Guide to using Crystal in VB

http://www.kenhamady.com/

 

[Guest_imported]

Ken u r right. But here is one more problem, there are other columns along with this. One patientid may have multiple insurance. For example patient 1169 has three insurance, now the records will be repeated for each insurance id.

techid techtype examid patientid insuranceid
----------------------------------------------
J1245 OP 2160 693 1
J1245 IP 4214 693 1 ----> 'in exam' count 1
W4144 OP 4312 895 1
W4144 IP 4494 1169 1 ----> 'in exam' count 2
W4144 IP 4494 1169 2
W4144 IP 4494 1169 3
W4144 IP 4214 1169 1 ----> 'in exam' count 3
W4144 IP 4214 1169 2
W4144 IP 4214 1169 3

patient 1169 has 3 insurance and 2 examids.
--if i make "count" for "in exams" then total count will be 7.
--if i make "distinct count" for "in exams" then total count will be 2.
--But the count for "in exams" should have been 3.

1 for techid 'j1245' + 2 for techid 'w4144'

Pl. help me.

 

[kenhamady]

How about if you append patID and ExamID and do the distinct count of that? Ken Hamady, On-site/Phone Crystal Reports Training/Consulting
Quick Reference Guide to using Crystal in VB

http://www.kenhamady.com/

 

[Guest_imported]

Ken, thats great idea. No wonder u r Numero uno. As u told i concatenated in a formula like this

exams --> {techid} + {examid} + {techtype}
patients -- > {techid} + {patientid} + {techtype}

Then i used these in running formula editor for 'in exams' 'out exams' 'in patients' and 'out patients'.

This counts correctly as per requirement.

Thanks a lot for your help Ken.

Satya.