cancel
Showing results for 
Search instead for 
Did you mean: 

Can a Formula Field contain a Group?

cwarner
Participant
0 Kudos

I am trying to create a Formula Field in Crystal Reports that contains a group. My purpose is to look at several records in a voucher and create a new name based on a value in the group to then be used in a Crosstab. However, I keep getting an error.

Any help is appreciated!

cwarner
Participant
0 Kudos

Thanks to all who contributed helpful information!

I found a solution by creating a subreport that contains only the "Established Patient" information in the crosstab by using a Group Selection formula to filter out New Patient visits.

Then, I added this subreport to my main report. This is working fine.

Accepted Solutions (1)

Accepted Solutions (1)

DellSC
Active Contributor
0 Kudos

You can't use groups like that. What is your {@Patient & Date} formula? Does it return a True/False?

-Dell

cwarner
Participant
0 Kudos

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.

DellSC
Active Contributor
0 Kudos

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

cwarner
Participant
0 Kudos

Thanks, Dell. I could try that, but my to get it work with the logic I am using in the Crosstab may be too difficult, or not possible.

See my comment to Abhilash showing the screenshot of my crosstab.

DellSC
Active Contributor
0 Kudos

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

cwarner
Participant
0 Kudos

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"

DellSC
Active Contributor
0 Kudos

Does your report have any parameters? What type of database are you connecting to? What is the "12 months" - calendar year, fiscal year, rolling 12 months from the date the report is run?

Thanks!

cwarner
Participant
0 Kudos

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"]

Answers (4)

Answers (4)

abhilash_kumar
Active Contributor
0 Kudos

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

cwarner
Participant
0 Kudos

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.

cwarner
Participant
0 Kudos

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).

abhilash_kumar
Active Contributor
0 Kudos

Could you post a screenshot of the crosstab's?

What are the other fields on the crosstab?

I believe these two new fields would be 'summary' fields?

-Abhilash

cwarner
Participant
0 Kudos

Sure. The total of ALL Visits in "Current 12 Months" is 107488, so, as you can see, the "Established Patient Visit" totals are inflated. However, "New Patient Visits" are correct.

0 Kudos

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.

cwarner
Participant
0 Kudos

Thanks, tried both formulas, but still getting the same error.

0 Kudos

can you try removing the closing ) before the IN and placing the closing ) after your IN? That might work.

0 Kudos

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"