• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1608
  • Last Modified:

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 ?    

0
TJPRIME
Asked:
TJPRIME
2 Solutions
 
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
 
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
 
TJPRIMEAuthor Commented:
Thanks for the info. Sorry delay response. Other commitments.
0

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now