Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Help needed to replace cross tab 1

Status
Not open for further replies.

SBSMedicalTech

Programmer
Apr 16, 2012
21
US
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.
 
Have you tried staying with your cross tab and turning off Cell margins

Right Click CT select Cross Tab expert. select tab Customise Style uncheck show cell margins.

Ian
 
Ian,

You made my DAY! THANK YOU a MILLION TIMES over!!! That fixed my issue on several reports. I was not aware of that setting.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top