crystal 9 - crosstab, show percentage differences between columns - how ?

Hi,

   Using Crystal 9 Crosstabs. The Column Heading is Year (2005,2006,2007 etc.). The Row Labels are Descriptions e.g. Red Cars, Blue Cars, Yellow Cars etc. In the Crosstab the intersection of the Year and the Coluor Car shows the Total number of Cars (accumulated in the Crosstab). It is required to insert between each Year a percantage difference column (i.e. %diff 2005 to 2006, %diff 2006 to 2007 and so on).    

I can solve this in the SQL creating a new field which has precalculated values ('2005', 'Diff 2005 to 2006', '2006' and so on ) and use new fields in the Corsstab, howver the performance implications will be severe and hopefully there is a much better solution available.

How can I best perform this ? Should it be possible to use the Summated fields in the Crosstab in a higher Section, calculate the percentage difference and then print a Crosstab (or data manually formatted into a grid) and overlay it over the section below ?    

TJPRIMEAsked:
Who is Participating?
 
frodomanCommented:
Hi TJPrime,

You are far better off calculating these values in your SQL and bringing them into Crystal pre-calculated.  If performace is a concern you might want to check into using a stored procedure instead of straight SQL to improve performance - in Oracle 10g this can make a huge difference.

You could accomplish this in Crystal, but you have to do a bit of a hack that is going to be sloppy to add your manual calculations (eg: http://technicalsupport.businessobjects.com/KanisaSupportSite/search.do?cmd=displayKC&docType=kc&externalId=6362411&sliceId=&dialogID=9526985&stateId=1%200%209524839).  If not that you'd have to dump the crosstab concept and manually create a summarized report that mirrors crosstab functionality.  

I can guarentee that either method will be LESS efficient than pulling the data pre-calculated from your database.  In Crystal, formulas are SLOW and the more complex they get the less efficient your report becomes.

frodoman
0
 
konektorCommented:
oracle has some analytics functions, using which you can create the query which will return you desired result.
http://download-uk.oracle.com/docs/cd/B19306_01/server.102/b14223/analysis.htm
but i'm not sure if you can use them within crystal report query
0
 
TJPRIMEAuthor Commented:
Thanks for the info. Sorry delay response. Other commitments.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.