| Author |
Message |
Jimm
Newbie
Joined: 10 Nov 2016
Online Status: Offline
Posts: 32
|
   Topic: sample crystal report
Posted: 29 Mar 2017 at 9:52am |
|
Working on a simple crystal report, format look like:
Type Medicare Medcaid other Total(1 to 3)
Continues Home care 3 4 10 17
Inpatient 5 6 8 19
General patient 2 4 4 10
Total 10 14 22
write sql code to get numbers for each group, but I don't know how to organize data like above.
It is not chart\line\picture,etc. How can I organize it? thanks.
|
 IP Logged |
|
|
kevlray
Admin Group
Joined: 29 Oct 2009
Online Status: Offline
Posts: 1587
|
Posted: 29 Mar 2017 at 10:52am |
|
Not sure what you mean by 'get numbers for each group'? You can group the report by Type and have running totals (counts?) at the group level, then more running totals to give you grand totals.
|
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 29 Mar 2017 at 11:08am |
|
also consider just using a crosstab (ct) in crystal instead of doing all the calculation in sql.
The ct is similar to a sql pivot which is what i would expect your sql output to do if you are trying to turn row based results into columns
The crostab (as compared to a sql pvt) would also automatically pick up new payer or medical types in the report if they data starts to include it. Meaning you can fix your or alter your data source without having to redesign the report.
|
|
IP Logged |
|
Jimm
Newbie
Joined: 10 Nov 2016
Online Status: Offline
Posts: 32
|
Posted: 30 Mar 2017 at 1:41am |
|
Thanks, Kevlary
|
|
IP Logged |
|
Jimm
Newbie
Joined: 10 Nov 2016
Online Status: Offline
Posts: 32
|
Posted: 30 Mar 2017 at 1:41am |
|
Never use CT before, will try it. Thanks.
|
|
IP Logged |
|
Jimm
Newbie
Joined: 10 Nov 2016
Online Status: Offline
Posts: 32
|
Posted: 30 Mar 2017 at 3:45am |
|
DBlank,
How can I make changes so Ct looks like from format 1 to 2nd format:
BlueCross BlueShield Commercial Medicaid 4 3026 Total 65 Total 554 Total
Inpatient Care
Respite Care
Routine Home Care
Total
BlueCross BlueShield commercial Medicaid Total
Inpatient Care 4 4
Respite Car 3026 65 554 3091
Routine Home Care
Total 3030 65 554 3649
|
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 30 Mar 2017 at 3:51am |
|
Depends on the source data but generally...
the CT need to be in the report header or footer
the Row should be set to use the "Care level field" (e.g. Inpatient care)
the Column should be set to use the payer field (e.g. mro)
the summarized field should be set to use the sum of the count field.
|
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 30 Mar 2017 at 3:52am |
|
if that does not match, what does your source/raw data look like?
|
|
IP Logged |
|
Jimm
Newbie
Joined: 10 Nov 2016
Online Status: Offline
Posts: 32
|
Posted: 30 Mar 2017 at 3:54am |
|
I figured out
|
|
IP Logged |
|
Jimm
Newbie
Joined: 10 Nov 2016
Online Status: Offline
Posts: 32
|
Posted: 30 Mar 2017 at 3:56am |
|
now like this:
BlueCross BlueShield Commercial Medicaid Medicare Self-pay Veterans Administration Total
General Inpatient Care 4.00 0.00 0.00 913.00 250.00 0.00 1,167.00
Inpatient Respite Care 0.00 0.00 0.00 329.00 0.00 0.00 329.00
Routine Home Care 3,026.00 365.00 554.00 13,282.00 882.00 93.00 18,202.00
Total 3,030.00 365.00 554.00 14,524.00 1,132.00 93.00 19,698.00
|
|
IP Logged |
|
|
|
