Crosstab default value - how can it be changed??

building reports crystal reports
1

Simple scenario: I have Crystal crosstab. Values coming from the database can include a zero - and it should show. However, crystal appears to put in 0 when there is no corresponding value at all for the given row/column intersection. This is misleading, as it suggest that the actual value is 0, when it does not exist!

I have tried everything to find the solution - any ideas how I can get rid of hte default value of zero!!?? :hb:

Anyone who gives the solution will get a cookie.

Thanks.
T.

Trigor :zimbabwe: (BOB member since 2008-04-03)

2

Is there not a crosstab property of suppress zeros. I seem to recall one. Don`t have the SW on my laptop to check.

ABILtd :uk: (BOB member since 2006-02-08)

3

If you right clcik on any number in your crosstab and click on ‘Format Field’

Click on the ‘Number’ tab.

Click the ‘Customise’ button.

Change the ‘Show zero vales as’ to a ‘-’ in the drop down list rather than the default.

Hope this helps

foxy :uk: (BOB member since 2007-10-22)

4

THANK YOU!! I was trying to accomplish the same thing, and spent all morning :hb: trying to use variable formulas for conditional supression.

ericci2744 (BOB member since 2008-08-08)

5

Guys, thanks for your suggestions. I know those formatting settings work to suppress zeros, but the problem is that Crystal appears to assign a zero value to those cells in the crosstab, which don’t have a value coming from the database at all. I solved this problem by forcing my DB query to return nulls, but I am still curious as to how to get Crystal to differentiate between 0’s coming from the DB and empty cells.

Trigor :zimbabwe: (BOB member since 2008-04-03)

6

Here is a scenario to explain what is happening better. Lets say we have a simple table:

CAR_MAKE CAR_TYPE COLOR PRICE

TOYOTA TRUCK BLACK $4000
TOYOTA CAR BLACK $1500
NISSAN CAR BLUE $800

Now, if we were to get this data into a Crystal report, and create a crosstab, where on the right (rows) we would have car make and car type, and columns would be colors, we would have something like this:

____BLACK BLUE
TOYOTA___TRUCK$4000
TOYOTA___CAR$1500
NISSAN CAR ________________$800

As you can see, there are three places with NULL values: blue colors for toyota, and black color for nissan. What Crystal does, is places a zero instead of keeping the cell blank. Now, as you suggested, I can suppress it, but the problem is that in our database the equivalent field to the price above can contain an actuall value of zero, which needs to be displayed.

And, the problem is, that crystal does not see the difference! So again, what I did to solve hte issue, is to create a query to union with the original select out of table that would actually return NISSAN CAR BLACK NULL and same for BLUE toyota’s. This way, Crystal appears to view the NULL value and display it correclty, while showing the value of 0 whenever it is pulled out of DB.

This is a nasty little puzzle and I would love to find out a solution … :slight_smile:

Again, thanks for your help on this!

:crazy_face:

Trigor :zimbabwe: (BOB member since 2008-04-03)

7

Still have not found an answer … help?

Trigor :zimbabwe: (BOB member since 2008-04-03)

8

The crazy one, but it is easy to implement if you pulling data by means of stored procedure or sql.
Return -1 (or other impossible price) for a real price of zero. That way you would be able to differentiate in the resulting crosstab what is really should be suppressed and what should be shown as zero.

White Owl (BOB member since 2007-05-24)

9

Thank you for your suggestion. I used car price example to simplify explanation of what I am struggling with. Our shop is within a financial company, so -1 is a valid value! :slight_smile: Can’t go that route.

Like I have mentioned, I did solve the problem by implementing statements on DB level to return NULL values for those that are not there, but I’d still love to hear if anyone had faced same problem and how they have solved it!

Thanks again for your help!

Trig.

Trigor :zimbabwe: (BOB member since 2008-04-03)

10

The reality is that the crosstab is summarizing your values which is why you get a 0 in the spot where you only had null values. A summary, whether sum, average, etc., of nulls is = 0.

If you want the same effect as a crosstab, but want control over the nulls, you will need to create one manually with running totals for each column. not a fun solution, but it will get you there.

JBray :us: (BOB member since 2008-07-15)