Cross Tab Reports

[jhixson]

I use Crystal Reports 8.5. Does anyone know how to force a cross tab's report to print out ALL column and row's. Right now it is only printing out the ones that have data but the report needs to show 0's for the columns and row's that have no data. Any help is appreciated.

John Hixson

 

[synapsevampire]

If you go to the Format Crosstab->Customize Style tab, there's an option to Suppress empty rows and Suppress empty columns, if these are checked, uncheck them.

If not, then you're likely getting all of the data, and you might be trying to describe a problem such as all dates aren't referenced for a period because there isn't any data for those dates.

This isn't missing information, you don't have that information. There are ways to work around this, so please post an example of what your data looks like (fields/example data) and expected output if you need more help.

-k

 

[jhixson]

I already had the Suppress Empty Columns/Rows unchecked. The report is a government report that wants to look at family types by Reason for entering a program we administer. So I want the report to look like:

Single Male Single Female Male W/Child ETC.
Reason

Loss of Job 0 3 0
Insufficent Income 1 2 0
Condemned Housing 0 0 0
Eviction 2 0 0
Alcohol Abuse 4 3 1

All of the fields are codes that come from 1 table that I have created aliases. The data looks like:{CaseSpecialIdentifiers_HOH.SpecialIdentifierID}{CaseSpecialIdentifiers_FamilyType.SpecialIdentifierID}{CaseSpecialIdentifiers_Reason.SpecialIdentifier}

What I want is if a column and Row have no data to print out the Column and Row name with a zero. Leaving the suppress blank columns/row option unchecked does not do this for the report.

Any guidance is very appreciated.

Thanks,

John

 

[lbass]

I think you might have to create a manual cross tab where your columns are based on individual formulas, e.g., {@singlemale}:

if {CaseSpecialIdentifiers_FamilyType.Name} = "Single Male" then 1 else 0 //create for each column heading and place in details

For rows, create a group on the following formula {@Reason}:

if isnull({CaseSpecialIdentifiers_Reason.yourdescription}) then "Unknown" else ({CaseSpecialIdentifiers_Reason.yourdescription})

(Of course you can use the ID fields for formulas and grouping, and then just use descriptive text boxes or fields for column and row labels.)

Insert summaries (or create running totals, depending on your data) on the details, and drag to the group header (if summaries), and then suppress details. If you have to use running totals, then you must display the group name and running totals in the group footer.

This should allow you to display all rows and columns.

-LB

 

[synapsevampire]

OK, I understand now, and LB has offered the standard solution within Crystal.

Another way to approach this is using SQL in which you might create a View or SP (or even add in a subreport, but I won't touch this right now) which selects distinct from the }{CaseSpecialIdentifiers_Reason.SpecialIdentifier} and CaseSpecialIdentifiers_FamilyType.SpecialIdentifierID} data, and then you join your table to it.

That way you'll have the rows already there, all you need to do is fill in amounts, which for some will remain at 0 (null).

-k

 

[MashMan]

The most simplest way is when you want default data to be published when not existing is to use a Stored Procedure or a View to build it.
For example for field 'Single Male' if data is NULL then use a Select statement such as:

SELECT ISNULL(Single_Male,0) FROM mytable

will return 0 if data is NULL.

 

[jhixson]

Still not able to pull data. Can someone please go over creating a view or stored procedure for me to get the data to default to 0. Right now it will only show data that exists and I need it to show whether it exists or not.

Thanks for your help,

John Hixson