Null fields not showing in Crystal Reports

[Former Member]

I have an issue related to null values not showing on my report and upon searching it seems I'm not the only one. It's driving me mad and I've delayed asking this question myself for as long as i could. I assume it's a simple fix, I'm just not very good with any of this and am trying to resolve something that's been bugging me for a while.

I have a simple report that shows customer data (name, address, phone, etc) and then displays sales data associated with that account based on a date range. Issue is, if the date range has no sales, the record is skipped completely and I'd like it to show the customer data with the $0 sales.

my formula is simply this:

{ARMAST.ShipTo} = 16672 and
{INSALES.PostDate} in Date (2016, 07, 01) to Date (2016, 09, 30) and
{ARMAST.Salesman} = 200

I tried messing with isnull and mimicking stuff i found online with no success. This has to be an easy fix for those who knows what they're looking at... I'm just looking for what i need to do to the formula above so that i will get all records in that date range, even if they have no sales.

[abhilash_kumar]

Hi Jason,

CR cannot return records that don't exist!

The fix is also not going to be an easy one.

Here's the workaround:

1. Create a date look-up table that lists ALL dates from a particular year in history to a particular year in future. You may choose the range depending on the user selection pattern - like how far back in history could the user go etc.

You'd need this table in the database.

2. Open the Database Expert > Add this table to the existing tables list.

LEFT JOIN FROM this look-up table to the original table that holds the date column.

3. Modify the selection formula so that it refers to the date field in the look-up table:

{ARMAST.ShipTo} = 16672 and
{Look-upTable.PostDate} in Date (2016, 07, 01) to Date (2016, 09, 30) and
{ARMAST.Salesman} = 200

You'd probably also need one record for every ShiptTo and Salesman value in the date-lookup table.

Make sure every date field on the report refers to the field from the date look-up table.

-Abhilash

[former_member11696]

Hi Jason,

Have you tried the Help file? Hit the F1 key and then search for isnull, has examples...

Don