Report Design: Compulsory Columns in Cross Tabs

Print Page | Close Window

Compulsory Columns in Cross Tabs

Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Report Design
Forum Discription: The best way to design a report and problems you have encountered
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=2039
Printed Date: 04 May 2024 at 10:14pm

Topic: Compulsory Columns in Cross Tabs

Posted By: ClaireB
Subject: Compulsory Columns in Cross Tabs
Date Posted: 14 Jan 2008 at 2:38am

Hi All,

This is my first time writing to this forum, and I'm relatively new to Crystal Reports.

Basically, what I'm trying to do in Crystal Reports XI, is create a Cross Tab with Months for a specified year across the top and Solicitors down the side. This is not a problem, except if the month has no data in it, it is not displayed.

I would like the table to look like this:

Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec

ABC

XYZ

insetad of:

Feb Apr Jul Oct

ABC

XYZ

Is there anyway I can do this using the Cross Tab? If not, is there a different way to accomplish it?

-------------
Thanks in advance,
C

Replies:

Posted By: Darshanku12
Date Posted: 14 Jan 2008 at 3:50am

hi clarie i have never tried something like this but i think passing a null value for the months which does have data will solve the problem. because in a different context i have used this type of method to get the result in a formatted way. try this out if it works then please post a reply abt how exactly you did this. bye

-------------
Darshan

Posted By: ClaireB
Date Posted: 14 Jan 2008 at 6:07am

Hi Darshan,

Thanks. This is what I thought, but the putting it in practice seems to be beyond me.

Because I am selecting specific data, there is nothing to put a null value in for those months that don't appear. I also can't seem to say to the cross tab, if you don't have a value for this month put 0.

-------------
Thanks in advance,
C

Posted By: yggdrasil
Date Posted: 15 Jan 2008 at 4:03am

I am having the same problem, and I can't see a way at this time.

The users want to see percentages, and year-to-date data which I am trying to create by two adjacent crosstabs, but that doesn't show the missing periods when the report is run part-way through the year.

Like you, I need to find a way of showing all periods with 0 in if there is no data.

This is surely a common problem

Posted By: Lugh
Date Posted: 15 Jan 2008 at 4:35am

The classic solution to this problem is to use one of a couple SQL tricks. Note that this does mean that you either need the ability to modify the original database, or you need to be able to use the Add Command function when selecting your datasource.

The simplest and most efficient solution is to create a calendar table in your database. This is a very simple table, which simply holds the months of the year (well, specifically, the first day of each month, as it needs to be an actual date value). Create an outer join between your data and the calendar table, with the join condition being something like MONTH(Calendar.MyMonth) = MONTH(MyData.MyDate). This will return NULL values for any month for which you do not have data.

Another option is to force records through the use of a UNION query. You can do this through the Add Command function. Your SQL would look something like:


SELECT blah1, blah2, blah3, MyDate
FROM MyData

UNION ALL

SELECT ' ' AS blah1, 0 AS blah2, ' ' AS blah3, '01/01/2007' AS MyDate
FROM MyData
WHERE {insert criteria to ensure only one record returned}

UNION ALL



SELECT ' ' AS blah1, 0 AS blah2, ' ' AS blah3, '02/01/2007' AS MyDate

FROM MyData

WHERE {insert criteria to ensure only one record returned}



UNION ALL



SELECT ' ' AS blah1, 0 AS blah2, ' ' AS blah3, '03/01/2007' AS MyDate

FROM MyData

WHERE {insert criteria to ensure only one record returned}



et cetera, et cetera, et cetera...

This is clumsy, and it creates a series of blank records which you have to manage. But, it will give you a record for each month, as you need.

Print Page | Close Window