Technical Questions
 Crystal Reports Forum : Crystal Reports 9 through 2020 : Technical Questions
Message Icon Topic: group on counted records summary Post Reply Post New Topic
Author Message
Addmed
Newbie
Newbie


Joined: 02 Nov 2013
Location: United States
Online Status: Offline
Posts: 4
Quote Addmed Replybullet Topic: group on counted records summary
    Posted: 20 Nov 2013 at 5:33pm
Hi All. This problem has stymied report writers in our clinics much more experienced than I. In short, what we need is to condense multiple records of consecutive dates into a single record containing the first in the series and then a count of the consecutive dates. We want to then suppress all the constituent records and output just the summary line to a .csv for upload to Epic. There needs to be a control break at each change in medical record number (MRN), and then--in order of subordination--at each change in workstation ID, then DSM code, then HCPCS code, then finally at each break in consecutive dates of service. Thus, from the following data:
MRN01, wkstation1, DSM1, HCPCS1, 11/1/2013
MRN01, wkstation1, DSM1, HCPCS1, 11/2/2013
MRN01, wkstation2, DSM1, HCPCS1, 11/3/2013
MRN01, wkstation2, DSM1, HCPCS1, 11/4/2013
MRN01, wkstation2, DSM1, HCPCS1, 11/5/2013
MRN02, wkstation2, DSM1, HCPCS1, 11/1/2013
MRN02, wkstation2, DSM2, HCPCS1, 11/2/2013
MRN02, wkstation2, DSM2, HCPCS1, 11/3/2013
MRN02, wkstation2, DSM2, HCPCS2, 11/4/2013
MRN02, wkstation2, DSM2, HCPCS2, 11/5/2013
MRN02, wkstation2, DSM2, HCPCS2, 11/8/2013  we would produce:
 
MRN01, wkstation1, DSM1, HCPCS1, 11/1/2013, 2
MRN01, wkstation2, DSM1, HCPCS1, 11/3/2013, 3
MRN02, wkstation2, DSM1, HCPCS1, 11/1/2013, 1
MRN02, wkstation2, DSM2, HCPCS1, 11/2/2013, 2
MRN02, wkstation2, DSM2, HCPCS2, 11/4/2013, 2
MRN02, wkstation2, DSM2, HCPCS2, 11/8/2013, 1
 
Until now, we've been using Crystal to group by variables in the requisite order then sort by date then add a calculation variable to show whether a record is consecutive within the last group break. Then we've exported to Excel in order to subtotal at each break, suppress details, and export the resulting visible records to a .csv. We need to be able automate that much within just Crystal. (It'll have to go through a PERL script to convert for upload.)
 
Bigger brains than mine are sleeping on it for the moment. Any suggestions?
Chris Nolan
IP IP Logged
Sastry
Moderator
Moderator
Avatar

Joined: 16 Jul 2012
Online Status: Offline
Posts: 537
Quote Sastry Replybullet Posted: 21 Nov 2013 at 1:11am
Hi
 
 
You can directly group on date field and go in group expert and change the group for daily so, it will group all you records based on date.
 
Now insert a count of your records for that group and place it on your group header and also pull all your fields to group header and suppress detail and group footer
 
Now export this to .CSV to get summary.
 
I have create a sample report based on your data and it works fine with me.  If you want that .rpt file, update thsi formum to send you
 
Thanks,
Sastry
IP IP Logged
lockwelle
Moderator
Moderator


Joined: 21 Dec 2007
Online Status: Offline
Posts: 4366
Quote lockwelle Replybullet Posted: 21 Nov 2013 at 5:41am
you can do it all in a stored proc...took me about 30 minutes to write...

all you would need to do is show the details...all the filtering/grouping is done along with the unique selection...

Unless you are not allowed to write stored procs...then I would go with Sastry's solution.

HTH
IP IP Logged
Addmed
Newbie
Newbie


Joined: 02 Nov 2013
Location: United States
Online Status: Offline
Posts: 4
Quote Addmed Replybullet Posted: 21 Nov 2013 at 1:10pm
Hmmmm...Sastry, I don't want to group on date, but rather to 'roll up' the consecutive dates into a single line grouped on changes in any one of a cascading set of variables. But for the moment, let's just say the only grouping variable is the primary one - medical record number (MRN). Now, if we have a patient 0000001 who's supposed to have a dose of methadone for every day of September, but misses her clinic attendance days on the 4th, 5th, 6th, 13th, 22nd, 23rd, and 30th, the data would need to look like:
 
[MRN],[Start Consec],[N consec days]
 
0000001, 9/1/2013, 3
0000001, 9/7/2013, 6
0000001, 9/14/2013, 8
0000001, 9/24/2013, 6
 
I guess you could say I want to group on each summary of consecutive dates, where that summary is the last grouping in the order, but I haven't been able to achieve that with your instructions (although it did introduce me to the date grouping interval options I hadn't seen before!). thanks again
IP IP Logged
Addmed
Newbie
Newbie


Joined: 02 Nov 2013
Location: United States
Online Status: Offline
Posts: 4
Quote Addmed Replybullet Posted: 21 Nov 2013 at 1:12pm
Thank you also, lockwelle! We're not allowed to write stored procedure. Our report writer's got her hands on some SQL language that might help us abstract the data amenably. chris
IP IP Logged
Sastry
Moderator
Moderator
Avatar

Joined: 16 Jul 2012
Online Status: Offline
Posts: 537
Quote Sastry Replybullet Posted: 23 Nov 2013 at 6:57pm
Hi
 
To create these kind of reports, we need to first identify the grouping levels.
 
Example :
 
MRN -- First level grouping
Date -- Secnd level grouping etc.. if you have any other fields are you can incorportae.. but need to find out summary should be on which group.  This will help us to insert a summary on right group.
 
Now according to your below statement (results)
 
First you create a group on MRN -- Then create 2nd group on Date.. and insert summary on 2nd group and place first group name, 2nd group name and summary in group 2 footer and suppress rest of the sections to display the required format.
Thanks,
Sastry
IP IP Logged
Addmed
Newbie
Newbie


Joined: 02 Nov 2013
Location: United States
Online Status: Offline
Posts: 4
Quote Addmed Replybullet Posted: 24 Nov 2013 at 2:08pm

It works! Thank you for the kind and concise explanation.

IP IP Logged
Post Reply Post New Topic
Printable version Printable version

Forum Jump
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum



This page was generated in 0.031 seconds.