Need expert crosstab advice (Crystal Reports)

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.
Becky EdwardsEpic Clarity DeveloperAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Raghavendra HullurSoftware DeveloperCommented:

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.
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 DeveloperAuthor 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.
Introduction to Web Design

Develop a strong foundation and understanding of web design by learning HTML, CSS, and additional tools to help you develop your own website.

Becky EdwardsEpic Clarity DeveloperAuthor 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
Raghavendra HullurSoftware DeveloperCommented:

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 DeveloperAuthor 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.
Raghavendra HullurSoftware DeveloperCommented:

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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Becky EdwardsEpic Clarity DeveloperAuthor 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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.