Using Temporary Tables with Crystal Reports
I recently wrote a Crystal Report for a customer that queried their recurring bookings for gaps of more than 45 minutes. Easy enough, just sort the report by booking time and use Crystal’s previous() and next() functions to work out when the gap’s too large. A little conditional formatting to hide the rows without gaps, job done.
Two weeks later, the customer wants the report changed to show more information, and in a different order. More information, no problem. However, changing the sort order breaks the previous() and next() functionality.
I couldn’t write a stored procedure to run through the resultset as it would probably be broken by future upgrades of the application database. That didn’t seem like a very elegant solution either.
Other option I could think of was creating a giant array in Crystal, then plucking out the elements as needed. That’s not very elegant either. It’s slow and complex too.
Brief stop for a cup of tea (I am British after all), and inspiration arrived.
The database being used is MS SQL Server 2008 R2, so no method of referring to previous or next record as you can in SQL 2012 and Oracle. That means having to use the rather clunky method of joining the resultset to itself, but with a shift of 1 record.
The following queries are slightly simplified just to avoid getting bogged down in the details of the table joins. Apologies for any errors as I can’t test the made up queries.
My original query:
SELECT DISTINCT runName, startTime, endTime, dayOfWeek, weekNumber FROM regularBooking
ORDER BY runName, weeknumber, dayOfWeek, startTime
became (this is just for the previous record):
SELECT DISTINCT RANK() OVER (ORDER BY runName, weeknumber, dayOfWeek, startTime) as NewKey,
runName, startTime, endTime, dayOfWeek, weekNumber FROM regularBooking
LEFT OUTER JOIN
(SELECT DISTINCT RANK() OVER (ORDER BY runName, weeknumber, dayOfWeek, startTime)-1 as PreviousKey,
runName, startTime, endTime, dayOfWeek, weekNumber FROM regularBooking) as PreviousBooking
ON regularBooking.NewKey = PreviousBooking.PreviousKey
That worked, with 2 problems:
1) There are some duplicate bookings, so the RANK failed as ties would have the same rank, leaving gaps
2) My query that previously took less than a second to run, now took over 6 minutes and that’s before adding the join for the next booking.
Stop for another cup of tea.
I’d not used temporary tables with Crystal before. To be honest, I had no idea if they were supported or not. Most of my internet searches (thank you duckduckgo.com) drew either blanks or said it can’t be done, but no specified reason given.
I needed to be able to create a table with an index to improve performance while being able to find previous and next records with a similar method to my earlier query.
Here’s how it went:
–First create the temporary table (# signifies temporary in MS SQL)
CREATE TABLE #Gaps (
NewKey in IDENTITY (1,1), — Using Identity to automatically create an incrementing key
runName varchar(1000),
startTime Time,
endTime Time,
dayOfWeek varchar(10),
weekNumber tinyInt,
CONSTRAINT PK_NewKey PRIMARY KEY CLUSTERED (NewKey Asc)
)
–insert the data into the temp table, ordered
INSERT INTO #Gaps
SELECT DISTINCT runName, startTime, endTime, dayOfWeek, weekNumber FROM regularBooking
ORDER BY runName, weeknumber, dayOfWeek, startTime
–Then the main query on the nicely indexed table. Original contained NULL checks, removed for simplicity
SELECT CurrentRecord.*, PreviousRecord.EndTime, NextRecord.StartTime
FROM #Gaps as CurrentRecord
LEFT OUTER JOIN #Gaps as PreviousRecord on CurrentRecord.NewKey = PreviousRecord.NewKey+1
LEFT OUTER JOIN #Gaps as NextRecord on CurrentRecord.NewKey = NextRecord.NewKey-1
–Finally drop the temporary table so that data can be refreshed in current session
DROP TABLE #Gaps
Once that was all tested in SQL Server Management Studio, it was simply a case of copying and pasting into a Command in Crystal Reports. The final query still took less than a second to run.
A few things to point out:
1) The user running the report needs the necessary rights to create and drop the temp table
2) I’m used to separating queries like this with GO. That’s not supported in Crystal and just returns errors
3) If you get an error during query execution for some reason, the temp table may not get dropped so you’ll get an error when you next refresh the report. Just logout of the database, refresh will now work.
4) I’ve used TIME datatypes. They were introduced in MS SQL 2008 R2. I wouldn’t use them next time as Crystal just sees them as text which then needs converting. Much easier to just use a fixed date in a DATETIME.
Time for tea.
