Null Values

[Former Member]

Hi All,

I am currently having an issue with null values. I wanted to change the blank label for the null group to something like <NULL> and the values for the null group to the right ones rather than 0.

To change the label for the null group I used:

if IsNull({Main.txtCategory2}) then

"<NULL>"

else

{Main.txtCategory2}

This worked fine. So now I just needed to change the 0's to the correct count

I found the option 'Convert Database NULL Values to Default' this then changed all the 0's to the correct numbers, but at the same time changed the label "<NULL>" to blank again.

My question is. How do I have the correct values on count for null fields but also change the blank label. I seem to be able to only have one without the other.

I hope this all makes sense!

Thanks,

Scott

[Former Member · Accepted solution]

If you have 'convert null values to defult' then a null stgin in the database (which is what you had before) will no longer be 'null'.

Try changing the condition in your formula to test the length of the string field that should represent the name of the group.

ie:

if len({your text field]) < 1 then "<NULL>"

I woudl also recomend that you use caution when setting that global flag to convert null DB values to default values. It can do odd things, like this.

What I do to resolve issues like the one you originally described is to create formulas for fields where I need to convert nulls to explicit values, so I can control what that value actually is. You do need to set the 'default values for nulls' option at the FORMULA level, but then you're explicitly handling a condition, and not just globally finding all nulls, and replacing them with some default value.

For more information on default values, there is actually a really great entry in the crystal help files that explains what Crystal does with nulls in specific field types when that option is checkd. Searching for "Default Values for Nulls" should give you the article I mean.

Hope that helps.

[Former Member · Accepted solution]

Ryan,

Thanks for your suggestion but this doesn't work either!

It set them all to blank unless i put a then statement in.

If I put is < 3 then it does change 'HR' to 'Not specified' but doesn't change the blank label!?

Thanks,

Scott

[Former Member]

Someting very odd is happening...

If I were in your possition, my next step would be to create some debug formulas to figure out what's going on, and how to catch it.

Create a formula for each of the test conditions, and see how they evaluate. For all of these formulas, ensure that inthe formula editor you have 'default values for nulls' selected in the top right, rather than 'exceptions for nulls' or you won't get the results you need:

These are all seperate Formulas :

isnull(your field)

len(your field) < 1

len(your field)

<your field> = ""

then put each of those formulas into the group headder where the empty value is appearing and see what you get.

[Former Member]

Hi Scott

Click on File -> Report Options.

Check the boxes for "Convert database Null values to default" and "Convert other Null values to default".

After doing this the report will all null values as 0.

Hope this helps!!!

Regards

Sourashree

[Former Member]

I have checked these boxes so the 0's for all nulls now show the correct values.

My problem is that I want the blank label to say something like "<NULL>" or "Not specified" etc

I had a formula in place like I said to replace blank label with something however when checking them boxes it set the label back to blank!

[Former Member]

I think after making setting the default values for nulls you need to write the formula as

if {Main.txtCategory2}="" then

"<NULL>"

else

{Main.txtCategory2}

Regards,

Raghavendra

[Former Member]

Hi

Only this formula won't show the required result.

First check the "Convert Database Null values to default" and "Convert other null values to default". This would convert all null values to 0.

Now right click on the field and Click on format field.

In the common tab -> Click on X-2 for Display String and open the formula editor.

Now type the following formula

if currentfieldvalue = 0 then "Null"
else totext({fieldname});

Hope this helps!!!

Regards

Sourashree

[Former Member]

Hi All,

Still having no luck. Maybe this will explain it better.

Before ticking the default null options this is something how it looked.

Category Count

Cat1 6

Cat2 10

Cat3 7

0

So I wrote a formula to change the blank label to <Null> so it looked like this:

Category Count

Cat1 6

Cat2 10

Cat3 7

<Null> 0

I then ticked the boxes for default null values and it put in the correct count for the null values but removed the <Null> label like this:

Category Count

Cat1 6

Cat2 10

Cat3 7

5

I want it to show '5' and 'Null' so it looks something like this:

Category Count

Cat1 6

Cat2 10

Cat3 7

<Null> 5

Hope that explains it a bit better!

Cheers

Edited by: ccaasde on Sep 22, 2008 9:25 AM

[Former Member]

Hi

Mentioned below are the steps which I have tried at my end.

1)In my report there is one field as "Cus_ID" which is of number data type.

2)In File menu |options|check the Report options "Convert database Null Value to default" and check "Convert other Null values to Default".

3)As it is number data type it will show as 0.

4)In design window Right Click the particular field|Format Field.

5)Click on common tab.

6)There is one option as "Display String" in front of that we have "X-2" icon.Click on that icon.

7)Write the below query:

if Cus_ID = 0 then "<NULL>" & "5" else ToText({Customer.Cus_ID}).

8)Click on OK.

NOTE:As Cus_ID is the number field so I have to convert it into text otherwise it will give me error as "A string is required here ".

Hope it will help you ,if it doesn't solve the issue let us know.

Regards

Asha.

Edited by: Asha Lund on Sep 22, 2008 2:45 PM

[Former Member]

Still not right.

The Cat1, Cat2 etc are text fields the values for that field being counted. Like I said I have got the count working with all the nulls adding up correctly but the text label is blank.

Thanks

Edited by: ccaasde on Sep 25, 2008 10:06 AM

[Former Member]

Hi

In the File-> report options, when you check the options for Convert databse null values to default and convert other null values to default, all null velues are displayed as 0 on the report.

After doing so, right click on the field -> format field ->Common and apply the below formula in the Display String

if CurrentFieldValue = 0 then "No Data here"
else ToText(fieldname);

You can change the display string for the null values as per your requirement. I have used "No data here".

Hope this helps!!!!

Regards

Sourashree

[Former Member]

This doesn't work as the blank labelled fields are Groups. I can send you a PDF of the report if that will help?

Thanks

[Former Member]

Hi

If the blank labelled fields are Groups then you can try suppressing the group or change the color of the group header from the section expert.

You can try applying a specified order group also with a specific group name with all the null values.

Hope this helps!!!

Regards

Sourashree

[Former Member]

Sourashree, Thanks for your help however its not quite what I want to do.

Here is a link to a example pdf of what I am trying to do.

http://www.ucl.ac.uk/is/remedy/crystal/Queries.pdf

Scott

[Former Member]

Hi Scott

Apply a specified order group by the below way:

1. Open the Group Expert and select the particular group with null values and click on options

2. Select in specified order. This would open a new tab Specified order.

3. In specified order tab click on New.

4. Put the name Not specified and set the field value is equal to 0. Click on ok.

5.In the Others tab, select Leave in their own groups.

6. Click on ok.

Before applying this, please remove the formulas that you have applied on this field for displaying null or any thing else.

Make sure that when you drag the field to the details section, it displays all 0s for all the Null values.

I tried it at my end and it worked.

Hope this helps!!!

Regards

Sourashree

[Former Member]

Sourashree

That doesn't change anything.

However if instead of choosing is equal to 0 and put 'is not like' and choose one of the values in the group then it changes it to Not Specified, but there are a lot of values so I can't keep putting not like this not like that etc, will be there all day.

Thanks for all your help