Need expert crosstab advice (Crystal Reports)

Becky Edwards
Becky Edwards used Ask the Experts™
on
Need a crosstab of % of payments to charges with discharge date on vertical and payment date on horizontal.  See attached sample.

In crystal, I have created a formula for total charges, total payments, and %_Paid (Charges/Payments), but they don't seem to work in the crosstab the way I need them to.

Any help would be appreciated.  I must be missing multiple steps.

I have attached an excel sample file of the way it should look when completed
and a screenshot of the way I have my crosstab set up currently.

I have a sequel query that has fields for payment month and year, discharge month and year.  Attached is a word document which shows the crosstab setup - not the way I like it - so hoping someone knows how to set this up the way I need it.
Cross_Tab_Setup.docxReserving_Payment_Lag_draft_BJE.xlsx
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
Hi,

Is it possible to share a copy of the report with saved data if you doesn't have restriction on sharing data?
Per my understanding, you are looking for value 1.09% for the Discharge Date June 17 and Service Date June 17 (a calculation of ($7,10,000.00/$6,50,00,000 )*100) and remaining values as per percentage values in excel sheet.
Mike McCrackenSenior Consultant
Most Valuable Expert 2011
Top Expert 2013

Commented:
Are the totals being calculated in the report or are they returned from the database in the query?

What are you seeing in the cross tab?
Becky EdwardsEpic Clarity Developer

Author

Commented:
I would like for the totals to be calculated in the report or in the crosstab actually.
But if that is not possible then I would appreciate suggestions.
I am attaching the crystal report to see if that will help.

Note:  The crosstab is not correct.  It is just a shell with parts of it working, such as the discharge month/year and payment month/year.
Pmt_lags_testing_EE.rpt
Become a CompTIA Certified Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

Becky EdwardsEpic Clarity Developer

Author

Commented:
And to answer your question RH - yes, that is exactly what I am needing.  

 looking for value 1.09% for the Discharge Date June 17 and Service Date June 17 (a calculation of ($7,10,000.00/$6,50,00,000 )*100) and remaining values as per percentage values in excel sheet

Commented:
Hi,

Thanks for confirming. All the values for payments and charges are 0 except few payments in negative value.

Per my understanding, the percentage you are calculating through "%_Paid" formula and getting a percentage of that will always try to get the percentage with regards to the total value of that row in crosstab and hence the values will be wrong.

Normally, in crosstab, we need to calculate the values with regards to the dynamic values the crosstab builds and hence usage of direct database fields will not help.

If you can provide the report with saved data with actual values, we can take those as a reference as we discussed and we can try to achieve the percentage in crosstab using what we call as GridValue functions where in, we need to refer the values at a particular row index and column index.

Remember that we need values of charges and payments to calculate the percentage.

In case it's easier for you to arrive at the percentage values at query level, you can try with that as that reduces the calculation using dynamic values at crosstab level.

I hope that makes sense to you.
Becky EdwardsEpic Clarity Developer

Author

Commented:
Yes, that makes sense I think.  I will try to build the percentages in the crystal report and then I think you are saying we could build the crosstab with those?    I will try that if you confirm.

Thank you.
Commented:
Hi,

I think it should work since the values are pre-calculated at query level.
But, I would suggest to create a sample query with less data to confirm that.

An alternate approch would be like if you know horizontal values used in the crosstab, I guess the payment related ones, are known before hand or you have fixed number of values (I know this may not be the case), you can try to build a mockup crosstab by creating summaries for year and month by grouping the data and display the values in a normal tabular report as if you are creating a crosstab.

But it all depends on the data and above said points.

If possible, share the current crosstab report with actual data as provided in excel sheet to give it a try to achieve the required results.
Becky EdwardsEpic Clarity Developer

Author

Commented:
i am going to close this for now until I try the method that has been provided to me.  Still not sure how this will work, but thank you both for your input in this.  I will refer to this question when/if I open a new one later.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial