I'm trying to make a report from two excel sheets which have common information in them. Each excel spreadsheet is comprised of a column with the date (week by week) and a second column containing the # of tickets opened or closed (depending on spreadsheet, this is a helpdesk system).

The problem occurs when there are 0 tickets either opened or closed in that given week the field does not get exported into the excel spreadsheet, and then when the spreadsheet is re-imported into Crystal it ignores weeks that do not have a value for each open and closed.

Example:

This is the "opened" spreadsheet:

Week	Opened
April 20, 2008	1
May 4, 2008	3
May 18, 2008	2
May 25, 2008	2
June 1, 2008	141
June 8, 2008	63
June 15, 2008	71
June 22, 2008	7

This is the closed spreadsheet:

Week	Closed
June 1, 2008	91
June 8, 2008	60
June 15, 2008	71
June 22, 2008	62

As you can see the opened spreadsheet contains more values than the closed.

When I combine the two reports in Crystal it links the two databases based on the "Week" field and then outputs the report but starts on June 1,2008, the first week in which they have data in common.


How can I get Crystal to not ignore the values where only one table has data???? I've tried to add another column that uses a combination of week# and year to act as a key but the same problem continues. Any ideas?

THX!!!!

I assume once you get it all into Crystal you will want to group on the week. These date fields will still be seperated and cannot be grouped on to get them allo together.

When i tried to go in to the database expert and set up this link full outer join is disabled as an option, possibly due to being excel databases. I had some success in using a left outer join as most of the holes lied in the closed table, however there are times when the open table is null for the week. Hence the full outer join is definitely what i need.

Is there a strategy to change the database to enable the full outer join. Or... Since the two excel databases are produced in crystal using an SQL database is there a way to enforce group creation even if there is no data found to be grouped in to that week? So for ex. the closed table will have a group called "April 20, 2008" even though there is no data for that week. I can't insert data in to the database without a TON of hassle. I've tried using a formula for group names but that doesn't enfore printing, what I really need is a way to make a formula for grouping but as far as I can see that isn't available in version 10.

~~Thanks!!!

Both these sheets are getting populated from a huuuuuge database, what i'm trying to achieve is finding the amount of tickets opened during a certain time period, and then the amount of tickets closed during that same period. Since I have to group by either open or closed to get an accurate count I made one report for each, export them and re-import both excel tables then use an eq to take the difference and then graph it - it's a lengthy procedure but better than what I was doing before which used arrays to read through all the records and add values to arrays depending on their open/close time. This method works when I run the report for all sites, but once it's drilled down to the site level where tickets may or may not be opening/closing every week I start to lose data!

Maybe there's a much simpler way that I'm just missing!!!

THANK YOU SO MUCH!!!!

It's SQL. And yes I think so, I'm not the DB guru around here but I can ask those in charge if you let me know what to tell them!