Crystal Reports Cross Tab

I am trying to create a report.  I want it to look like the attached:
What I have at the moment is attached:
The cross tab is built as attached:

The formula is:

//Current
if {F_TASKS.TA_STATUS} = 'Assigned'
and {F_TASKS.TA_EST_DATE} > CurrentDateTime
then "Current Open Tasks"
else
if {F_TASKS.TA_STATUS} = 'Assigned'
and {F_TASKS.TA_EST_DATE} < CurrentDateTime
then "Current Overdue Tasks"
else

//This Week
if {F_TASKS.TA_STATUS} = 'Complete' or {F_TASKS.TA_STATUS}= 'History'
then if {F_TASKS.TA_FINISH_DATE} in date (CurrentDateTime)-7 to CurrentDateTime
and {F_TASKS.TA_FINISH_DATE} < {F_TASKS.TA_EST_DATE}  
then "Tasks Completed On Time Last 7 Days"
else
if {F_TASKS.TA_STATUS} = 'Complete' or {F_TASKS.TA_STATUS} ='History'
then if {F_TASKS.TA_FINISH_DATE} in date (CurrentDateTime)-7 to CurrentDateTime
and {F_TASKS.TA_FINISH_DATE} > {F_TASKS.TA_EST_DATE}
then "Tasks Gone Overdue Last 7 Days"
else

//This Month
if {F_TASKS.TA_STATUS} = 'Complete' or {F_TASKS.TA_STATUS} ='History'
then if {F_TASKS.TA_FINISH_DATE} in date (CurrentDateTime)-30 to CurrentDateTime
and {F_TASKS.TA_FINISH_DATE} < {F_TASKS.TA_EST_DATE}
then "Task Completed On Time 7 - 30 Days"
else
if {F_TASKS.TA_STATUS} = 'Complete' or {F_TASKS.TA_STATUS} ='History'
then if {F_TASKS.TA_FINISH_DATE} in date (CurrentDateTime)-30 to CurrentDateTime
and {F_TASKS.TA_FINISH_DATE} > {F_TASKS.TA_EST_DATE}
then "Tasks Gone Overdue 7 - 30 Days"
else

// 6 weeks
if {F_TASKS.TA_STATUS} = 'Complete' or {F_TASKS.TA_STATUS} ='History'
then if {F_TASKS.TA_FINISH_DATE} in date (CurrentDateTime)-42 to CurrentDateTime
and {F_TASKS.TA_FINISH_DATE} < {F_TASKS.TA_EST_DATE}
then "Task Completed On Time 30 Days - 6 Weeks"
else
if {F_TASKS.TA_STATUS} = 'Complete' or {F_TASKS.TA_STATUS} ='History'
then if {F_TASKS.TA_FINISH_DATE} in date (CurrentDateTime)-42 to CurrentDateTime
and {F_TASKS.TA_FINISH_DATE} > {F_TASKS.TA_EST_DATE}
then "Tasks Gone Overdue 30 Days - 6 Weeks"
Crystal-Issue.png
David NixonAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
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:
Hi,
From the screenshot it's not very clear what you are trying to achieve. Can you confirm whether you want to change formula content or are you looking for changes in display of the report? If possible, attach the report with saved data for better understanding.
0
David NixonAuthor Commented:
Hi,

The first screenshot is from my client.  It is in excel format, and that is what they want the report to look like.

The second screenshot is the report that I have created so far.  I am struggling with the % columns.
0
Raghavendra HullurSoftware DeveloperCommented:
Well, the reason to ask more information is because the screenshots are very blur and the information cannot be seen correctly. So, it would be better if you provide a clear information of what you have and what percentages you need to display. Is it possible to share the report with some data saved? Also clear information of what percentage you are trying to achieve. Which version of Crystal Report you are using.
0
CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

David NixonAuthor Commented:
ah!  Apologies.  Please find attached the excel document and the report.
SnapShot-Report-v01.rpt
Copy-of-SS-KPI-Report.XLSX
0
Raghavendra HullurSoftware DeveloperCommented:
Hi,

Check the attached report with blank column headers. You can include the column headers by right clicking --> Calculated Member --> Edit Column Value Formula.
SnapShot-Report-v01_Modified.rpt
0

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
Raghavendra HullurSoftware DeveloperCommented:
One more thing,
Your excel sheet has 3 columns for 7 day values viz. "Tasks Completed Overdue", "Tasks Completed Ontime" and  "Tasks Gone Overdue". Currently the calculated values are based on 2 columns available in report.
If there is a chance of third column being available in report, then the column indexes will change and the report formulae needs to be modified for the percentage values.
0
mlmccCommented:
What issue are you having?

I think the if can be written in an easier way.  It will also execute faster.

Remember a record can only fit into 1 box so if the gone overdue is some kind of cumulative count your method won't work.  For instance tasks that have gone overdue this week also went overdue this month but will not be included in the monthly count.

mlmcc
0
Raghavendra HullurSoftware DeveloperCommented:
Hi,
Per the excel sheet calculations for percentage, I could see that the percentage is (Tasks completed on time) /(tasks completed ontime + Tasks gone overdue) * 100.
Based on that, the calculated members have been created using row and column indexes. But, if the third column Tasks Completed Overdue gets into report, then the column index will change, that's what I was referring.

If there are issue with count, then the formula with content provided by David Nixon needs to be re-visited for that.
Correct me if I am wrong.
0
David NixonAuthor Commented:
perfect, thank you
0
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
Crystal Reports

From novice to tech pro — start learning today.