Cross Tab keeps suppressing rows with no data

[Former Member]

I have a Crystal Report that is using a Cross Tab. I have 2 rows in the cross tab the first row is the Month name and the second row is the Day Number of the month, and the report will print the Month Name on the left followed by all the Calendar Day Numbers in the month.

The problem is that when there is no data for a Day Number row the entire row is not shown. So you might have day number 1, 2, 3 and then jumps to maybe 6. I found that the ones the cross tab was not showing did not contain any data. I just need the calendar day number to at least print so the days read sequentially instead of jumping to 6 or another number.

Is there any way to show the rows even if they don't contain any data?

I have right clicked and gone into the Cross Tab expert and there isn't anything checked to suppress rows. I don't understand why its still suppressing rows!

Is there a global report option that I missed that says "suppress rows when there are no records"?

Thank you all in advance,

I am using Crystal Reports XIR2 with Service Pack 4

[former_member260594 · Accepted solution]

Levi,

If there is no data returned to the report for a specific day then it will not appear in the report and therefore not in the crosstab. You could create a table that contains all of the days of the year and link it to the existing table with an outer join to return all of the days. It doesn't even have to be a table in the database it could be an excel spreadsheet.

[Former Member · Accepted solution]

This solved my problem! I had been using a command to pull out all the dates and day names for the entire year out of our SQL database, and then connected that to our product database.

But I was doing an inner join instead of an Outer join. So I switched it over to "Left Outer" join and it works now.

Thanks

[Former Member]

I finally fixed this problem, I created a stored procedure and controlled everything inside the procedure.

I also forced a zero into Columns that have no data and it now displays correctly.

[Former Member]

I am still not getting all rows to show up on the report. The rows that are not showing up contain no data in the cross tab but the row still needs to show.

The only reason I got it to work before was because I selected all values for the columns parameter so it brought back everything in the database that was why it was able to show all values.

I have two tables one is a date database and contains all the dates till 2012. The other is the product data which contains the product code and qty that the cross tab summarizes.

I have done a left outer join from the product data to the date database, and I am still not able to get all the dates to list for the selected time range.

I was able to get the dates to show up when I right clicked the link and modified the link properties. I chose Left Outer Join and then under Link Type I selected ">" in the database expert dialog. This brought back all the dates but the data in the report was all the same. Probably just the first record repeated all down the page.

Any ideas how to fix this problem?

[Former Member]

Go to file

report options

check null values to pull them in

format cross tab

customize style

make sure you dont have surpress blank rows and columns checked