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

Posted on 2007-10-17
Last Modified: 2013-12-19

   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 ?    

Question by:TJPRIME
    LVL 9

    Assisted Solution

    oracle has some analytics functions, using which you can create the query which will return you desired result.
    but i'm not sure if you can use them within crystal report query
    LVL 42

    Accepted Solution

    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:  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.


    Author Comment

    Thanks for the info. Sorry delay response. Other commitments.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
    Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
    This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
    Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database

    734 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now