Clear Skys

Archive for October, 2009

Cames across an issue today with a DB2 table with a primary key when used in a Crystal report. The report uses charts that appeared at a Group level in Crystal, the charts x-axis is then sequenced by a date, but when the report is saved and reopened the x-axis sequence reverts to the primary key sequence of the main table and not to the date that has been specified.

My options were
1.Log a call with Business Objects and get them to sort out the issue with their software, but I don’t have time for that.

2. Code a SQL command into Crystal Reports and use the ORDER BY clause to sequence the data correctly. This fixed the issue caused by Crystal, but the performance was not great.

3. Create a new View in DB2 and use that to sequence the data correctly before being used by Crystal Reports. This is when I realised that DB2 does not let you use an Order By clause in a view on the main select statement. Yikes, what next - a stored procedure. Thankfully no - using a sub-select statement in the View works great.

CREATE VIEW VIEW_LAB
SELECT * FROM
(SELECT * FROM LAB ORDER BY DATADATE)
AS VIEW_LAB

I know it’s a workaround from the original Crystal Reports Software issue,  but who has the time to fix Crystal Reports.