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?
 
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
 
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
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

 
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
 
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:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.