SBSMedicalTech
Programmer
I am using Crystal Reports version 11.5.12.1838
In my report, I have 2 Groups
Group 1: {ado.Company}
Group 2: {ado.Doctor}
My SQL Report is retuning 9 months of summarized data on 4 CPT code categories (New Patient, Established Patient, Consult and OB Global).
For each of these CPT code Categories, I am getting back 9 rows of summarized data in SQL. In my Group 2 Footer or Header, I need to add in these values by category. I can not use a cross tab report, because the spacing in the box's is creating too many issues for me, so I am hoping to replicate what my prior cross tab displayed.
In my prior cross tab, I returned the Rows: {ado.Description} to supply me with the CPT Code Category Name - ((New Patient, Established Patient, Consult or OB Global), the summarized Field was the sum of {ado.Encounters} and under the Columns, I was returning the ColumnLabel (the name of the Month the summary related to).
I added a column in my SQL query (ROW_NUMBER() OVER (PARTITION BY ... ) to give me the ranking 1 through 9 for each CPT Code Category by provider, ordered by Month in Ascending order. I tried to add in a running total on my Group 2 footer as such:
Field to Summarize - Encounters
Under Evaluate, I selected "Use a Formula" and used this to pull the "New Patient", Month 1 value: {ado.RowRank} = 1 AND {ado.Description} = "New Patient"
Under the Reset, I told it to on Change of group: {ado.Doctor}.
I created 9 of these for each CPT Category and changed the Rank from 1 to 9 and the Description to be for the unique name of the category. My totals are coming out really off and not consistent with what the SQL results are and how the cross tab was handling it. Any assistance is appreciated. If I can elaborate more, please let me know.
In my report, I have 2 Groups
Group 1: {ado.Company}
Group 2: {ado.Doctor}
My SQL Report is retuning 9 months of summarized data on 4 CPT code categories (New Patient, Established Patient, Consult and OB Global).
For each of these CPT code Categories, I am getting back 9 rows of summarized data in SQL. In my Group 2 Footer or Header, I need to add in these values by category. I can not use a cross tab report, because the spacing in the box's is creating too many issues for me, so I am hoping to replicate what my prior cross tab displayed.
In my prior cross tab, I returned the Rows: {ado.Description} to supply me with the CPT Code Category Name - ((New Patient, Established Patient, Consult or OB Global), the summarized Field was the sum of {ado.Encounters} and under the Columns, I was returning the ColumnLabel (the name of the Month the summary related to).
I added a column in my SQL query (ROW_NUMBER() OVER (PARTITION BY ... ) to give me the ranking 1 through 9 for each CPT Code Category by provider, ordered by Month in Ascending order. I tried to add in a running total on my Group 2 footer as such:
Field to Summarize - Encounters
Under Evaluate, I selected "Use a Formula" and used this to pull the "New Patient", Month 1 value: {ado.RowRank} = 1 AND {ado.Description} = "New Patient"
Under the Reset, I told it to on Change of group: {ado.Doctor}.
I created 9 of these for each CPT Category and changed the Rank from 1 to 9 and the Description to be for the unique name of the category. My totals are coming out really off and not consistent with what the SQL results are and how the cross tab was handling it. Any assistance is appreciated. If I can elaborate more, please let me know.