Showing ALL Values Specified in Selection Criteria

[Toxodont]

I have a report which uses the following selection criteria code:

{PERMIT.TYPEOCC} = ["Single Family","Two Family", "Multi-Family", "Accessory Building", "Home Occupation", "Commercial", "Industrial", "Institutional", "Other" , "Mobile Home"]
and {PERMIT.MUNUNIT} = ["New Glasgow","Pictou", "Stellarton", "Trenton", "Westville"]
and {PERMIT.DATEBLDG} = {?Date}
and {PERMIT.APP} = ["Conventional Building" , "Pre-MFGED Building"]

The crosstab is setup like this:

http://www.nerv.ca/misc/crosstab.jpg

Here is a screenshot of the report it creates for a january 2003 activity report:

http://www.nerv.ca/misc/jan2003.jpg

Notice that the report is quite small. Reason being is that CR is only showing me the columns that actually have data in them. If you look at my selection criteria you will notice that there are 3 other columns that did not show up because they do not have data in them. If there any way to make CR display all columns (and rows too) even if they contain no data (null)?

This is an automated report, not a manual one. I can achieve the desired report manually but it would be nice to know a way to do this through an automatic report. Thanks for your help.


Toxodont

 

[synapsevampire]

A selection criteria does not limit the number of columns, it limits the data returned within the columns.

Since you want to have headings for data which does not exist, you might approach this by changing your data source and the aggregate. Add in a table which has all of the types as the main table, and then left outer to your table.

Now you will have at least 1 record for each type.

-k

 

[ChiTownDiva]

Hi, Toxodont...

Go to "File, Options", click the reporting tab and make sure the "Convert NULL Field Value to Default" is not checked. If it is then Crystal is treating the field the same way the database is. Also make sure the "Suppress Printing if No Records Selected" is also unchecked.

If all else fails, you can write a formula that says:

if isnull({PERMIT.MUNUNIT}) then "Null" else {PERMIT.MUNUNIT}

That will force it pick up those records.

Good Luck.

ChiTownDivaus

 

[Toxodont]

@synapsevampire

I created the table in the database like you suggested (also enabled referential integrity) and then created the left outer join from the new table (mununit) to the main table (permit). I created the join in CR if that makes any difference. Unfortunately although the links worked, it did not force my crosstab to show nulls. Thank you for your suggestion though! I greatly appreciate it!

@ChiTownDiva

I checked the settings out and they were all good. I also created not only the formula field you specified, but forumla fields for my summarized data as well. This also did not force the null columns to display. Thank you very much for your suggestion though! I greatly appreciate it!


Toxodont

 

[synapsevampire]

Toxodant: Add in a count of something in the new table.

-k

 

[Toxodont]

@synapsevampire

Here is the output from adding a summary of the municipal units from the new table:

Here is my setup for the crosstab just to make sure it is correct:

I even made it a forumla field with the following used for the forumla:

if isnull({mununit.mununit}) then "Null" else {mununit.mununit}

If I am doing something wrong please let me know! Thanks for all your help!

Toxodont