Crosstab eating number of days 1

[jehanzebn]

Morning all,

I am using Crosstab in my report, the problem I am getting is that when there is no data against the column (Number of days), crosstab does not show that day nor the number.

For example

Days - 1 2 3 5
Jobs - 1 7 3 6

Here in the above example the Day 4 did not have any data, i.e. 0 number of jobs went through, the crosstab is missing the 4th day. It should show 4 and the number of jobs as in 0.

How can I make crosstab to show all the number of days regardless of number of jobs?

Any ideas

Regards

Jehanzeb

Jehanzeb
Crystal Reports 2008
Visual Studio 2008
Informix Database

 

[Madawc]

Are you able to get tables / datasets added to the database? If you got a table of days set up, this could be linked to the activity using LEFT OUTER and you'd still get a slot when there was no activity. (But note that LEFT OUTER does not work if you also do a selection on the table receiving the LEFT OUTER link.)

If you can't add a table, you'll be stuck with creating a 'Mock Crosstab'. Crosstabs are fine if you don't mind the absence of rows or columns with no data, but you said you wanted empty slots to show.

A 'Mock Crosstab' is something that looks like a Crosstab, but in fact you define each column yourself, normally as a running total. This would need to go in the report footer, because running totals count as the reports 'run' and they will not be complete until then. Crystal should have included an example along with the Crosstabs.

You can save a little time by doing a paste to a dummy report, changing the name and then pasting back.

Each running total will count the record if it was within the criteria - in your case, day.

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[jehanzebn]

Madawc, thanks for the quick reply.

Does that Mock Crosstab works with Graphs too? and how can I create a mock crosstab for number of days?

is it like

@Number0
If ({@NumofDays}) = 1 then
1
else
tonumber(@null).

and then create 10 days formula?

is it how it is going to be?

Regards

Jehanzeb

Jehanzeb
Crystal Reports 2008
Visual Studio 2008
Informix Database

 

[jehanzebn]

I have tried Mock Crosstab but that shows in a row wise where as my report is based on the column wise (number of days on the column side not on row).

I simply cannot understand that why I cannot use a fake number of days which stays there on the column side as fixed and the data underneath gets changed each time.

Any other ideas I can try

Regards

Jehanzeb

Jehanzeb
Crystal Reports 2008
Visual Studio 2008
Informix Database

 

[Madawc]

I think graphs only work with summary values.

A mock-crosstab is based on running totals that are accumulated for one value and shown at the end of the report, or sometimes at the end of the group. You should be able to arrange them as you please.

Check FAQ767-6524 if you're not familiar with running totals.

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[jehanzebn]

Right I have tried the way it says it,

I grouped it by number of days in ascending order.I then used formula (from 0 to 10 days)

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

and placed all of them in the detail section.

Oh before that as it stated I placed a Numberofdays formula field in the detail section as well. (I then hide the details section)

Then I used a Summary button and used DistinctCount on the Grand Total.

After placing all the summaries I ran the report and it added one record on each record.

So where in 0 Days 6 jobs were despatched it showed 7. Similarly with other number of days.

I am not sure why is it adding 1 into all the jobs. When I looked into the details section it was showing 66666,111111, so no doubt it is adding 1 but why I have no idea?

Regards

Jehanzeb

Jehanzeb
Crystal Reports 2008
Visual Studio 2008
Informix Database

 

[Madawc]

Where have you placed the formula? If it is in both the group header and the details, that would give you one extra.

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[lbass]

I think you corrected the conditional formula to have {@null} as the default, correct? Otherwise, you will be off by one when you insert a distinctcount whenever the default value occurs, since it will count as one.

-LB