I would like a crosstab to show rows with no data as 0

[pokerace]

Cyrstal 10 - ODBC

I have a crosstab report that is run daily with each row representing a store location and each column the sum of all sales for each department. The report look similar to this:

Dept1 Dept2 Dept3 Dept4 Dept5

Store1 500 490 970 210 0

Store2 390 900 870 150 50

Store3 600 650 700 200 75

and so on. On some days one of more of these stores will have no sales (or no sales entered) at all and it skips that stores row, which I have grouped in a certain order in the Cross Tab expert options. Even if no sales are present for a store, I would like it included in the report with all zeros present instead of just skipping that store completely.

Is there anything I can do to make that happen?

 

[Durango122]

hi
check your option in the report to included null or 0 as a default value

Durango122
Remember to used all fingers when waving to policemen

 

[pokerace]

Hmm. I tried to the null to default option as well as the zero to default option, and neither worked. Any other options I can try?

 

[Madawc]

Crystal won't print a group that has no detail lines, and the same applies to crosstabs.

There isn't an easy solution. You could try a 'left outer' link between 'store' and 'sale'

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

 

[synapsevampire]

I believe what you're asking Crystal to do is remember that there are some stores that aren't in the data being returned, obviously that isn't going to happen without some trickery (hard coding, etc.).

You're better served to always return data from your data source for each store.

Try posting your database type, ODBC is a connectivity.

It may be that you can change the joins in your database to be LEFT OUTER to make sure that all of the stores are represented, but again, nobody here coudl knwo that from what you've posted, but give it a shot.

-k

 

[pokerace]

The database is "built on Sybase's Adaptive Sever Anywhere (ASA) SQL platform." Hopefully that will help?

 

[pokerace]

Any advice for how to make sure that the crosstab report always returns data for stores that don't have any?

 

[synapsevampire]

This is how I address this:

faq767-4532

It has sample code for creating SQL Server tables which you could adapt.

-k

 

[aeronits]

I have the Parts table:
Part_number req res
a 1 1
b 3 1
c -1 1
a 4 2

I want the sum of req and res and also I want all the part number to be seen even if it is - or 0.
I want the output something like:
a 8
b 4
c 0

Please!! help needed desperately

 

[lbass]

This should have been a new thread. What is the problem you are running into? Try using partnumber as your row field in the crosstab, and use a formula:

{table.req} + {table.res}

...as your summary field.

-LB

 

[Zthomas]

I have a similiar problem...need to show group that has no value.

I have define custom groups in the group expert and would like to have the group show up even though there maybe null value? Any suggestion?

 

[Zthomas]

I am writing a report to mimic financial statements. Need to show something similiar to this.

Cash and cash equivalents 100
Short term investment
Long Term Investment 50
Property plant and equipment
Total asset 150

 

[pokerace]

Zthomas,

I think you will be best served to start a new thread and describe your problem in detail, along with your database type and your version of Crystal.