on 10-17-2018 11:23 PM
You can't use groups like that. What is your {@Patient & Date} formula? Does it return a True/False?
-Dell
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Okay. That’s what I thought may be the issue.
{@Patient & Date} is a formula field that concatenates a Patient account number and the service date from their bill. It returns something like this: 435224 - 7/8/2018. I do a distinct count on this formula field to find out how many patient visits occurred in a year.
I can’t just do a count of the account number alone, as it is a part of a one-to-many relationship, since the bill contains several lines of charges repeating the account number on each line. However, a patient will only have one entire visit with charges per date, so that was my way of “grouping” them for a count.
This is working correctly for a count of total visits. However, I need to display 2 columns in a crosstab further dividing these visits into “new patient” vs “established patient”. The list of procedure codes in my formula in the previous screenshot define a new patient. If a group of charges on a bill doesn't include these codes, it is an established patient visit.
I also tried If {vwGenSvcInfo.Procedure_Code} in ["92002", "92004", "99202", "99203", "99205", "99242", "99243", "99244", "99245"] Then "New Parient Visit" Else "Established Patient Visit". But that counts some visits as 1 new and 1 established, since a new visit will have one of the codes in the formula plus several othr codes not in the list.
I'm not sure this is do-able in a cross-tab, but there might be other ways to do this...
Since you can use the Maximum summary function with strings and "New Patient Visit" is greater than "Established Patient Visit", you might be able to do something like this:
1. Create a running total called {#NewCount} that looks like this:
Field to Summarize: {@Patient & Date}
Type of Summary: Distinct Count
Evaluate: Use a formula: {vwGenSvcInfo.Procedure_Code} in ["92002", "92004", "99202", "99203", "99205", "99242", "99243", "99244", "99245"]
Reset: Never (or on change of whatever field you're using to group your data...)
3. Create a final formula called {@EstCount} that looks like this:
DistinctCount({@Patient & Date}) - {#NewCount}
or, if you're getting numbers for a group:
DistinctCount({@Patient & Date}, <group field>) - {#NewCount}
This makes the assumption that any patients that are not "new" on a given date are "established" for that date.
This won't work in a cross-tab, though. What are you trying to show that you need a cross-tab for? There may be another way to get the same result.
-Dell
Some types of data calculations are too complicated for Crystal to handle internally so, unfortunately, I think the only way to get the numbers you're looking for is going to be to use a command (SQL Select statement) to calculate all of them instead of using the crosstab in Crystal. How are your SQL Skills? What type of database are you connecting to?
If you can post the query that your report is using (Database >> Show SQL Query on the menu) and what you're using for parameters, I might be able to help with that. Once you get the query correct, it will be fairly easy to put the report together because that data will be in the correct format/layout already.
If you want more info about using commands, you can see my blog post here: https://blogs.sap.com/2015/04/01/best-practices-when-using-commands-with-crystal-reports/
-Dell
Unfortunately, I don't know SQL, but here is the query you asked for. Thanks!
SELECT DISTINCT "vwGenSvcInfo"."Patient_Number", "vwGenSvcInfo"."Service_Date_From", "vwGenSvcInfo"."Actual_Dr_Last_Name", "vwGenSvcInfo"."Procedure_Code", "vwGenSvcInfo"."Location_Abbr" FROM "Ntier_VTRL"."PM"."vwGenSvcInfo" "vwGenSvcInfo" WHERE ("vwGenSvcInfo"."Service_Date_From">={ts '2016-10-01 00:00:00'} AND "vwGenSvcInfo"."Service_Date_From"<{ts '2018-10-02 00:00:00'}) AND ("vwGenSvcInfo"."Location_Abbr"='Blaine' OR "vwGenSvcInfo"."Location_Abbr"='Duluth' OR "vwGenSvcInfo"."Location_Abbr"='Edina' OR "vwGenSvcInfo"."Location_Abbr"='Oakdale' OR "vwGenSvcInfo"."Location_Abbr"='SC' OR "vwGenSvcInfo"."Location_Abbr"='St Paul' OR "vwGenSvcInfo"."Location_Abbr"='West') AND NOT ("vwGenSvcInfo"."Actual_Dr_Last_Name"='Ramsay' OR "vwGenSvcInfo"."Actual_Dr_Last_Name"='VRS-misc' OR "vwGenSvcInfo"."Actual_Dr_Last_Name"='zzzCantrill' OR "vwGenSvcInfo"."Actual_Dr_Last_Name"='zzzRamsay') ORDER BY "vwGenSvcInfo"."Patient_Number"
Thanks. Answers to your questions:
Does your report have any parameters? No
What type of database are you connecting to? SQL
What is the "12 months" - calendar year, fiscal year, rolling 12 months from the date the report is run? It is actually a formula field named "YearComparison2" which I created for the Crosstab categories:
If {vwGenSvcInfo.Service_Date_From} < date(year(dateadd("m", -12, currentdate)), month(dateadd("m", -12, currentdate) ), 1) then "*Prev 12 mo" else if {vwGenSvcInfo.Service_Date_From} > date(year(dateadd("m", -24, currentdate)), month(dateadd("m", -12, currentdate) ), 1) then "Curr 12 mo"
My record selection formula is:
not ({vwGenSvcInfo.Actual_Dr_Last_Name} in ["Ramsay", "VRS-misc", "zzzCantrill", "zzzRamsay"]) and {vwGenSvcInfo.Service_Date_From} in Date(DateAdd("m", -24, Date(Year(CurrentDate), Month(CurrentDate), 1))) to Date(Year(CurrentDate), Month(CurrentDate), 1) and {vwGenSvcInfo.Location_Abbr} in ["Blaine", "Duluth", "Edina", "Oakdale", "SC", "St Paul", "West"]
Hi Cheryl,
Try:
If {vwGenSvcInfo.Procedure_Code} IN ["92002", "92004", "99202", "99203", "99205", "99242", "99243", "99244", "99245"] Then "New Parient Visit" Else "Established Patient Visit"
Use this formula field in the crosstab's column and it should work.
-Abhilash
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Sorry, that is the formula I meant to type in Dell’s comment, procedure count (not patient & date). Anyway, this formula gives me an inaccurate count because a patient visit will include several records for that date including other codes than the new patient code, so it puts that visit in both categories, making the established visit column total inflated. That’s why I was hoping to do sme kind of grouping by patient and date first before dividing into columns by code.
P.S. My database looks like this:
Acct# Date Procedure Code
345345 7/5/2018 92002
345345 7/5/2018 67028
14522 7/10/2018 25550
So, this should show as 1 New Patient visit and 1 Established Patient visit.
However, using the formula field: If {vwGenSvcInfo.Procedure_Code} IN ["92002", "92004", "99202", "99203", "99205", "99242", "99243", "99244", "99245"] Then "New Parient Visit" Else "Established Patient Visit" it is showing as 1 New Patient visit and 2 Established Patient visits (which is ncorrect).
If Patient& Date is a Variable that you made combining Patient and Date, then try this:
Patient Visit
If ([vwGenSvcInfoProcedure_Code] InList ["92002", "92004", "99202", "99203", "99205", "99242", "99243", "99244", "99245"]) Then ([Patient & Date]) Else "Established Patient Visit"
See what happens with that, then we can try further.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Thanks, tried both formulas, but still getting the same error.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
can you try removing the closing ) before the IN and placing the closing ) after your IN? That might work.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
If ( {@Patient & Date}, {vwGenSvcInfo.Procedure_Code} in ["92002", "92004", "99202", "99203", "99205", "99242", "99243", "99244", "99245"]) Then "New Parient Visit" Else "Established Patient Visit"
Or Try
If ( {@Patient & Date}, {vwGenSvcInfo.Procedure_Code} InList ["92002", "92004", "99202", "99203", "99205", "99242", "99243", "99244", "99245"]) Then "New Parient Visit" Else "Established Patient Visit"
User | Count |
---|---|
83 | |
24 | |
12 | |
9 | |
7 | |
6 | |
5 | |
5 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.