making a working link between 2 excel sheets
Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Data Connectivity
Forum Discription: How to connect to data sources and export reports
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=7351
Printed Date: 18 May 2024 at 11:47am
Topic: making a working link between 2 excel sheets
Posted By: JennyLynn
Subject: making a working link between 2 excel sheets
Date Posted: 12 Aug 2009 at 7:25am
|
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:
This is the closed spreadsheet:
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!!!!  ------------- ~~~~Jenny Lynn |
Replies:
Posted By: DBlank
Date Posted: 12 Aug 2009 at 8:01am
| Use a full outer join |
Posted By: DBlank
Date Posted: 12 Aug 2009 at 8:05am
|
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.
You will have to create a formula for that:
if isnull(Opened.week) then closed.week else opened.week
|
Posted By: JennyLynn
Date Posted: 12 Aug 2009 at 8:23am
|
Beautiful! Thanks again DBlank! ------------- ~~~~Jenny Lynn |
Posted By: JennyLynn
Date Posted: 13 Aug 2009 at 8:25am
|
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!!! ------------- ~~~~Jenny Lynn |
Posted By: DBlank
Date Posted: 13 Aug 2009 at 8:39am
|
Hmmm, I know certain join types are not available based on the Data source type but I thought excel allowed the full outer join.
Crystal won't let you create data rows, only manipulate what already exists. You need a way to get all of the data in.
Is there a third sheet that these 2 are getting populated from that you can use directly?
|
Posted By: JennyLynn
Date Posted: 13 Aug 2009 at 12:26pm
|
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!!!  ------------- ~~~~Jenny Lynn |
Posted By: DBlank
Date Posted: 13 Aug 2009 at 1:12pm
|
Let me think about this one a little more. Is this SQL data and if so do you have access to adding in views or stored procedures?
|
Posted By: JennyLynn
Date Posted: 13 Aug 2009 at 1:38pm
|
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! ------------- ~~~~Jenny Lynn |
Posted By: DBlank
Date Posted: 14 Aug 2009 at 12:02pm
|
Still not sure but this is what I am thinking so far and not sure what you think on this (or others think if they are reading) ...
What about a Stored Proc as a UNION of the table on itself with start and end dates as variables using a where clause table1 start date in parm1 to param2 and table2 end date in param1 to param2.
Thoughts?
|
Posted By: lockwelle
Date Posted: 18 Aug 2009 at 6:10am
|
I don't know about the excel as a datasource, as mine are all SQL, but if the initial data is already coming from a stored proc, why not make a simple stored proc that unions them, or the very least populates a temp table. Something like
create table #tOpen(
weekStart DATETIME,
tickets INT,
open BIT)
INSERT #tOpen
SELECT weekStart, COUNT(tickets), 1 FROM...
WHERE openTicketCriteria = true
INSERT #tOpen
SELECT weekStart, COUNT(tickets), 0 FROM...
WHERE openTicketCriteria = false
Just a thought
|
Posted By: DBlank
Date Posted: 18 Aug 2009 at 7:04am
|
Thanks Lockwelle, that was where I was headed too. JennyLynn, if the data is too large to do this with you can use a stored procedure with parameters to limit the scope of the data at selection time.
|
Posted By: JennyLynn
Date Posted: 18 Aug 2009 at 8:03am
|
Just got access to the DB server today. Never played with stored procedures before but they seem pretty straight forward. I'll let you know my results! Thanks lockwell & DBlank!!!
------------- ~~~~Jenny Lynn |
Posted By: JennyLynn
Date Posted: 19 Aug 2009 at 2:27pm
|
SUCCESS!!! Thank you so much for your help DBlank and lockwell!  I ended up creating 3 stored procs, one that selected all the ticket open times and grouped them by assignment then date, one that selected all the closed ticket times and grouped them the same way, then the third stored proc essentially did lockwell's suggestion and put them both into the same table. After that when back in crystal I could group them and it drew the information back together (or close enough to it). The sad part is I still need to output the resulting report data to excel if I want to graph it because the backlog data (opened - closed) is done by a running total and Crystal Reports X can't graph on that! For anyone reading who wants the full solution I'll paste it below (because I hate when I'm reading a post and the final solution isn't posted!) Stored Proc #1: CREATE PROCEDURE dbo.usp_interactions_opened_by AS SELECT DatePart(month, PROBSUMMARYM1.OPEN_TIME) as MONTH, DatePart(day, PROBSUMMARYM1.OPEN_TIME) as DAY, DatePart(year, PROBSUMMARYM1.OPEN_TIME) as YEAR, PROBSUMMARYM1.ASSIGNMENT, COUNT(PROBSUMMARYM1.ASSIGNMENT) as OPENED, NULL AS CLOSED FROM database.dbo.PROBSUMMARYM1 PROBSUMMARYM1 GROUP BY PROBSUMMARYM1.ASSIGNMENT, DatePart(year, PROBSUMMARYM1.OPEN_TIME), DatePart(month, PROBSUMMARYM1.OPEN_TIME), DatePart(day, PROBSUMMARYM1.OPEN_TIME) GO Stored Proc #2: CREATE PROCEDURE dbo.usp_interactions_closed_by AS SELECT DatePart(month, PROBSUMMARYM1.CLOSE_TIME) as MONTH, DatePart(day, PROBSUMMARYM1.CLOSE_TIME) as DAY, DatePart(year, PROBSUMMARYM1.CLOSE_TIME) as YEAR, PROBSUMMARYM1.ASSIGNMENT, NULL AS OPENED, COUNT(PROBSUMMARYM1.ASSIGNMENT) as CLOSED FROM database.dbo.PROBSUMMARYM1 PROBSUMMARYM1 WHERE PROBSUMMARYM1.CLOSE_TIME != null GROUP BY PROBSUMMARYM1.ASSIGNMENT, DatePart(year, PROBSUMMARYM1.CLOSE_TIME), DatePart(month, PROBSUMMARYM1.CLOSE_TIME), DatePart(day, PROBSUMMARYM1.CLOSE_TIME) GO Stored Proc #3: CREATE PROCEDURE dbo.usp_together AS create table #tOpen1( MONTH INT, DAY INT, YEAR INT, ASSIGNMENT VARCHAR(130), OPENED INT, CLOSED INT) insert into #tOpen1 exec usp_interactions_opened_by insert into #tOpen1 exec usp_interactions_closed_by select * from #tOpen1 GO Thanks again to both of you! ------------- ~~~~Jenny Lynn |