How to put zeros in there is no data in Cross Tabs

[CrystalUser1]

Hi,

I am using using Crystal reports 9.0 with oralce 9i database.

I have a cross tab report like the following:



year

State Name 2000 2001 2002

VA 100 101 102
MD 200
DC 20 1000 200

In the above cross table, MD state does not have any counts for the years 2000 and 2002. But still
its showing the blank columns. How to replace those blank columns with zeros? Anybody has any ideas?

thanks in advance

 

[rosemaryl]

Dear Crystaluser1,

Is Crystal summarizing a database field? If it is null then crystal will show that.

What I do is replace with a formula field.

If isnull({Table.Field})
then 0
else {Table.Field}

As an example the above will print 0 when the field is null and the value in the field is it is not null. This can be used in a sum.

If you are counting the field then change it to:

If isnull({Table.Field})
then 0
else 1

Now replace the field that you are counting or summarizing with the formula field.

Hope that helps,

regards,

ro



If you are cou

Rosemary Lieberman
rosemary-at-microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.

You will get answers more quickly if you read this before posting: faq149-3762

 

[CrystalUser1]

Thank you Rosemary.

Yes, it is summarizing a database field. But i am converting that field into text field in the database view that i am using for this report.

And also there is no real data for that columns which are showing as Nulls, because its a cross tab and the preceding and the following columns have data, so its showing nulls. When i try to do the formula like you showed above, its not working because there is no real data for that columns.

thanks

 

[rosemaryl]

Dear CrystalUser1,

I am truly sorry but I have read your response 3 times and do not understand what you are saying.

Maybe an example of the data, specifically the fields that you are using in the crosstab for the row, column, and summarized value.

I understand that there is no data for the column in the crosstab, that's why I use a formula. If there is no real data for the columns then exactly what are you summarizing?

regards,

ro

Rosemary Lieberman
rosemary-at-microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.

You will get answers more quickly if you read this before posting: faq149-3762

 

[CrystalUser1]

thank you rosemary,

the actual will be like this when i run the database view:

State Year Count
VA 2000 100
VA 2001 101
VA 2002 102

MD 2001 200

DC 2000 20
DC 2001 1000
DC 2002 200
I am creating the cross tab report based on the above view summarizing the count field.

VA and DC has the data for all the years where has MD has data for only 2001.

That's what I mean there is no data. There is no ROW AT ALL FOR THAT YEAR. Thats why Its not working. I tried. Please let me know. HOpe you understand this time.

thanks in advance.

 

[rosemaryl]

Dear Crystal User,

I just tested this with data of my own.

I used a field that might or might not have values for a given year and used that as my row.

I used as the column Year of a datetime field.

I summarized as a sum a field that I new would be null when the row field was null and got the same results you did.

Then however, I followed my advice to you and did a formula:

If isnull({Table.Fieldbeingsummarized})
then 0
else {Table.Fieldbeingsummarized}

and now have 0's populating as would be expected.

Did you try my formula or just stating that you didn't think it would work?

regards,

ro



Rosemary Lieberman
rosemary-at-microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.

You will get answers more quickly if you read this before posting: faq149-3762

 

[CrystalUser1]

Hi rosemary,

I once again tested with your formula, still I am getting the Blanks, No zeros. I used your formula as follows:

if isnull({mytable.field}) then "0" else {mytable.field}. I am getting the same Blanks as it was before when I directly used the database field.

I used the summary function as max.

my database view is as follows (example):

CREATE OR REPLACE VIEW Test
(MyID,MyField)
AS
admin_id, trim(to_char(amount, '9,999,999')) as myfieldcount

from Materialized_view1

union all

select

admin_id, trim(to_char(decode(allcount, 0, 0, round(mycount / allcount * 100, 1)) , '999.9')) as myfieldcount

from Materialized_View1


thanks

 

[rosemaryl]

Dear crystaluser,

Okay, now I am just curious? Why are you transforming your field to To_Char ... you should just be able to bring that in as a number field.

I cannot duplicate the behavior you are having with the numerical fields I have, but when I changed my data toText then I was able to duplicate this exactly and cannot fix it.

I would say that you should be returning numerical values for your counts and then using the maximum of those and the problem will be corrected.

regards,

ro

Rosemary Lieberman
rosemary-at-microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.

You will get answers more quickly if you read this before posting: faq149-3762

 

[CrystalUser1]

If you see my database view, i am getting two values for one column, i.e, one is for count and the other is percentage. I am using union all for that. I am not sure how can i achieve those fields if i get only one field.

thanks

 

[rosemaryl]

Dear CrystalUser,

Please excuse me for mispeaking. My point was that I don't understand why you are converting your value to a varchar, that is the problem with your crosstab. There is no reason that I can see that it would be necessary to convert the math you are doing to a varhar. Just change your view so that you are returning a number for your MyfieldCount.

regards,

ro

Rosemary Lieberman
rosemary-at-microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.

You will get answers more quickly if you read this before posting: faq149-3762

 

[CrystalUser1]

thank you, i understand now. I am using to_char function to convert the number and to format the number. can you let me know what is the function i can use for number format in oracle sql?

thank you very much for your help.

thanks

 

[Trixie2004]

Hi,

I think that why it's not working is because instead of:

if isnull({mytable.field}) then "0" else {mytable.field}

you need to use CurrentFieldValue, rather than the db fields, because the cross tab is generating the summaries on the fly. So try:
if isnull(CurrentFieldValue)then "0" else CurrentFieldValue

and please let me know if it works.

 

[CrystalUser1]

thank you rosemary for your help. I have taken out the conversion and kept it as number field. I am getting the zeros if its null.

thanks

 

[rosemaryl]

Dear CrystalUser,

You are welcome and glad it is fixed.

Trixie, the currentfieldvalue can only be used in conditional formatting formulas and would not apply here.

regards,

ro

Rosemary Lieberman
rosemary-at-microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.

You will get answers more quickly if you read this before posting: faq149-3762

 

[CrystalUser1]

thank you rosemary, can you help me in the following:


I have to format the summarized field which is number type in cross tab.

I have to format the summarize the field based on the certan conditon like the following:

if type=count, then 9,999,999
if type=percentage, then 99.9

thanks in advance.