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.
Cross_Tab_Setup.docxReserving_Payment_Lag_draft_BJE.xlsx
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
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?
What are you seeing in the cross tab?
ASKER
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
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
ASKER
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
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
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.
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.
ASKER
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.
Thank you.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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