manual crosstab help 1

[jehanzebn]

Morning all,

I am trying to create a manual crosstab on my report. So far I have managed to show number of days and jobs according to those days.

However when I place all my formulas (from 0 day to 10 days) on the Group footer (Grouped by NumofDays formula), they all shown in a row wise

Days 0 1 2

Jobs 0 0 0
Jobs 6 0 0
Jobs 0 18 0
Jobs 0 0 19
Jobs 0 0 0

What I would like to show is if the day has a value, it should be shown only once and the number of jobs should be shown in one line. so for example it should be

Days 0 1 2

Jobs 6 18 19


here are the formulas

Number of days

WhileReadingRecords;

Local DateVar Start := {order_header.date_entered};   // Starting Date
Local DateVar End := {order_header.act_despatch};  // Ending Date
Local NumberVar Weeks; // Number of weeks
Local NumberVar Days; // Number of days
Local Numbervar Hol:=0; //Number of holidays
DateVar Array Holidays; // Bank holidays dates array
 
Weeks:=(Truncate(End - dayofweek(End) + 1 - (Start - dayofweek(Start) + 1))/7)*5;

Days:=DayOfWeek(end) - Dayofweek(Start) + 1
+ (if dayofweek(start) = 1 then -1 else 0) //adjust for starting on sunday:
+(if dayofweek(end) = 7 then -1 else 0); //adjust for ending on a saturday:
 
//Check for bank holidays
Local NumberVar i;
For i := 1 to Count (Holidays)
do (if DayOfWeek (Holidays[i]) in 2 to 6 and
Holidays[i] in Start to End then Hol:=Hol+1 );
Weeks + Days - Hol;

If (Weeks + Days - Hol) < 11 then
1
else
2;

If (Weeks + Days - Hol) <= 10 then
totext((Weeks+Days-Hol)-1,0,'')
else
"10+"

Number of jobs formulas

If {@NumofDays}="0" then
if ({order_header.order_status})>0 then
if ({order_header.order_status})>-1 then
numbervar a:= distinctcount({order_header.order_no},{@NumofDays})
else
tonumber({@null})

The above number of jobs, there are 10 formulas I have created. One for each day. so it is like

If {@NumofDays}="0"
If {@NumofDays}="1"
If {@NumofDays}="2"
so on....

I have used Text objects to show days. Grouped it by Number of days formula and created formulas and placed them in the group footer.

I have reset each numbervar in the jobs formula on the group header.


Any ideas?

Regards

Jehanzeb



Jehanzeb
Crystal Reports 2008
Visual Studio 2008
Informix Database

 

[lbass]

Your formula should be:

If {@NumofDays}="0" then
if ({order_header.order_status})>0 then
if ({order_header.order_status})>-1 then
{order_header.order_no} else
tonumber({@null})

Place this in the detail section and then insert a distinctcount on it at whatever level you wish.

PS. You should not use variables unless absolutely necessary.

-LB

 

[jehanzebn]

IIbass this has been sorted out now ) I realised it afterwards and fixed it

Now the issue is showing the same number of days on the graph as it does not reflect those values which contain "Null" or "0" value.

As far as the above question it has been sorted.

Regards

Jehanzeb

Jehanzeb
Crystal Reports 2008
Visual Studio 2008
Informix Database