Trying to display a list, based on compliance,,,

[medmondson]

I work for a healthcare organization, and a standard part of our work is auditing measures. As part of that work, I'm looking to develop a monthly report that identifies the sites/medical centers within our system that performed below threshold for the given measures. For example, we want a C-section rate below 20%. I want to be able to output just the list of sites that failed to achieve that goal. The challenge is, leadership wants this in a "letter" type form, so crosstabs, suppressed groups etc aren't really an option here. What I really need is, I think, a formula that would ultimately output just the sites that didn't meet threshold in a comma delimited list:

"The sites that didn't meet C-section threshold are Site A, Site C, Site Q and Site Z".

Any ideas?

[Former Member]

Welcome to the SAP Community. Thank you for visiting us to get answers to your questions.

Since you're asking a question here for the first time, I'd like to offer some friendly advice on how to get the most out of your community membership and experience.

First, please see https://community.sap.com/resources/questions-and-answers, as this resource page provides tips for preparing questions that draw responses from our members. Second, feel free to take our Q&A tutorial at https://developers.sap.com/tutorials/community-qa.html, as that will help you when submitting questions to the community.

I also recommend that you include a profile picture. By personalizing your profile, you encourage readers to respond: https://developers.sap.com/tutorials/community-profile.html.

Now for some specific suggestions on how you might improve your question:

* Outline what steps you took to find answers (and why they weren't helpful) -- so members don't make suggestions that you've already tried.

* Share screenshots of what you've seen/done (if possible), as images always helps our members better understand your problem.

* Make sure you've applied the appropriate tags -- because if you don't apply the correct tags, the right experts won't see your question to answer it.

* Use the "insert code" feature when sharing your code, so members have an easier time reading.

Should you wish, you can revise your question by selecting Actions, then Edit.

The more details you provide (in questions tagged correctly), the more likely it is that members will be able to respond. As it stands, I don't know if there is enough information here for members to understand your issue. So please consider revising your question because I'd really like to see you get a solution to your problem!

I hope you find this advice useful, and we're happy to have you as part of SAP Community!

[DellSC]

Since you have raw audit data, I'm going to suggest that you use a Command to generate the aggregated/scored data for the report. A command is a SQL Select statement that pulls ALL of the data for the report. Since the aggregation will be processed in the database (assuming you're not using a file-based database like Access), not only will the report run faster, but you'll be able to bring the data from the database to the report in a format that will make it easier to develop the report. Here's some detailed information about how to use Commands: https://blogs.sap.com/2015/04/01/best-practices-when-using-commands-with-crystal-reports/

Also, I would tweak your formulas a bit to make them a little more efficient:

If {R_V_CPMS_PC_M_CSQL_VIEW.PC2A} = "Opportunity for Improvement" then 100
else if {R_V_CPMS_PC_M_CSQL_VIEW.PC2A} <>"In Measure Population" then tonumber({@null})
else 0

You may not need the formula above one if you do it as a case statement in your command.

IF {#PC 2A D} > 0 then {#PC 2A N} % {#PC 2A D}

The % operator will automatically add the "*100" to the formula to make it a percent.

If you're not sure how to write the SQL for what you need, please post the SQL that Crystal generated for your report along with the formulas you're using for the metrics and the type of database you're using and I should be able to help you write it.

-Dell

[DellSC]

This shouldn't be too difficult. Assuming that this is a single letter instead of separate letters for each metric and that you can pull a single dataset that identifies the metric scores for each facility, here's what I would do:

1. Put the text that should appear at the top of the letter in the report header.

2. Add a group on whatever identifies the metrics.

3. Suppress the page header, page footer, group header and details sections.

4. Create a formula to concatenate the facility names and another to display them. They will look something like this:

{@GetFacilities}
WhilePrintingRecords;
StringVar facilities;
if OnFirstRecord or {metric field} <> Previous({metric field}) then
  facilities := {facility name field}
else
  facilities := facilities + ", " + {facility name field};
""
{@ShowFacilities}
WhilePrintingRecords;
StringVar facilities;
facilities

5. Put {@GetFacilities} in the details section - it will calculate even though the section is suppressed.

6. In the metric group footer, put a text box. In the text box, do the following:

- Type "The sites that didn't meet "

- Drag the metric name field into the text box so that is comes after "meet ".

- Put the cursor back in the text field and type " are ".

- Drag the {@ShowFacilities} formula into the text box after "are ".

By doing this, everything should line up correctly and look like letter text instead of being in columns.

7. Put any final text in the report footer.

-Dell

[medmondson]

Thank you for the reply Dell, the challenge I'm facing is my source data isn't the results, it's the raw data of all audits and I therefore first have to determine the compliance (which I've done both in a crosstab by facility (using this):

If {R_V_CPMS_PC_M_CSQL_VIEW.PC2A} <> "Opportunity for Improvement" and {R_V_CPMS_PC_M_CSQL_VIEW.PC2A} <>"In Measure Population" then
tonumber ({@null}) else
If {R_V_CPMS_PC_M_CSQL_VIEW.PC2A} = "Opportunity for Improvement" then 100 else 0

and a stand alone field for a single "overall" number (using running totals for N/D and then this):

IF {#PC 2A D} > 0 then {#PC 2A N} / {#PC 2A D} *100

and then from that I need to extract the non-compliant facilities and convert them into the list.