This may be a basic question but I can't seem to find a working solution. In my report, I have 4 groups all with a summary count contained in each header. Everything is working correctly except that I'm displaying a blank line in my 4th group which corresponds to the NULL value contained in the database. The actual values are displayed as well, so an example of my data might be this:

Location 1 (group 1)
Department 1 (group 2)
    User 1 (group 3)
      (blank line w/ a count total of 0) (group 4)
      Reason 1 w/ count total (group 4)
      Reason 2 w/ count total (group 4)

Is there anyway to suppress the blank line while still maintaining the group? I tried to convert the NULL to default values already, but no luck. Suppressing the blank section didn't help, and I still need to account for the Group 4, so doing a select ISNULL will not work either. I'm just trying to get rid of the blank line.

Thanks, this corrected the immediate issue however without enabling the "Use default Values", my 3 running totals are not calculating. Upon enabling this, the empty line comes back with a count of the NULL values but the Running Total formula works again. I have my running totals created in the group 3 footer, and they are very simple running totals. I am also using a completely different field that the field that Group 3 is based off on (i.e. Group 3 is based on user, running total is based on employee specialization which outside of the running total isn't anywhere else on the report):

Total1 (footer 3A) - field count, eval formula stating when field = "Employee", reset upon change of group 3.

Total2 (footer 3B) - field count, eval formula stating when field = "Non-Employee", reset upon change of group 3.

Total3 (footer 3c) - field count, eval formula stating when (field != "Non-Employee" and field != "Employee), reset upon change of group 3.

So the only thing that is changing is the eval formula. I didn't realize it originally, but the Group 4 field is a string field. Any ideas on the best way to correct this? I've already tried another ISNULL in the group selection statement, and I'm not sure how to set the default value for NULLs in formula editor if that would help.

Edited by cant2ny - 12 Oct 2011 at 3:36am

The running total is 0, which I think is occurring because the Group Select statement is causing it to not select even valid records (NULL is a valid option in my SQL table, as the other data may still be valid). So I guess I'm not sure on how to set what value Crystal Reports uses for the NULL values when the "Use Default Values for Database NULLs".

So by enabling the "Use default values for Database NULLs", Crystal Report, we're selecting all records and setting it to a default value. How would I set this information?

Also, the blank line is occurring in Group 4's header. Maybe I'm misunderstanding the flow of how Crystal Works, I'm self taught over the past few months but I thought it worked:

Record Select > Group Select > execution of formulas, running totals, etc.

If this isn't how it works, then maybe I'm completely off and the problem may lie elsewhere.

Edited by cant2ny - 12 Oct 2011 at 4:34am

Thanks for the info, I've been using Brian's book on-and-off with problems and how-to's but I'll look specifically for the flow. I figured out a round about way to fix this, but I'll post my information to see if there is a better way.

Due to the constructs of the original program whose table I'm pulling from, the first 3 groupings could never be blank or NULL. They are set automatically at run time, and written to the row upon submission. The 4th grouping was a user defined entry, so if this wasn't set, a NULL entry was entered.

So when running the Insert Summary statement, I believe Crystal reports was evaluating it as the first 3 groupings were valid, so it pulled the NULL values from the 4th grouping as well. But since there was nothing set (at this time "Default Values for Database NULLs" was not enabled), the summary couldn't do anything since techinically, there was no count to be based on. Once this was enabled, the program could evaluate it as a 0, and increase the count by 1. However, by tossing a group select statement, at runtime, Crystal Reports filtered out the entries that didn't fit the criteria (count > 0). This also filtered out the valid entries since the Group Select statement was run before the groupings occurred. The valid entries also happened to be part of my 2 running totals, while the 3rd was based on the filtered groupings. So that's why the 3rd was still working, while the first 2 were not. When the default value for database values was enabled, all entries were selected, allowing for use of the running totals again.

In the end, I changed the grouping from ascending to descending. Suppressed the original grouping, created a Formula field based on this suppressed grouping:

global stringvar Reason;
If GroupName ({employee_.Reason})=""
then Reason:="NULL"
else Reason := GroupName ({employee_.Reason})

I then placed the @Reason field over the group 4, and suppressed it when the entry = NULL. I also suppressed the count. So since the formula field is on the Group Heading, it's only suppressing the one line, along with the count specific to NULL. I adjusted some formatting in my report, so now it looks like I just have a space in between them for easier readability.

At least this is what I think is happening, but I'll gladly take any corrections on what is happening to gain a better understanding.