Results 1 to 3 of 3
  1. #1
    Join Date
    May 2006
    Posts
    2

    Unanswered: Columar Report - Grouping on 3 fields. Help!

    Hi

    I have data in two tables - Sales and Dealers.

    Sales
    --------------------
    DealerCode
    WeekEnding
    NumberSold

    Dealers
    --------------------
    DealerCode
    CompanyName
    State

    I have a command in Crystal as follows:

    SELECT d.CompanyName, d.State, s.WeekEnding, s.NumberSold
    FROM sales s
    LEFT JOIN dealers d
    ON s.DealerCode = d.DealerCode
    AND s.WeekEnding BETWEEN '2006-04-01' AND '2006-04-30'

    I would like to group the data returned by CompanyName, State and Date to get a Total of NumberSold.

    I would like to format the report in columns (as in image attachment).

    I would also like to have a two week total and a week-on-week figure.

    I would like to know how to go about achieving this? Any help greatly appreciated.
    Attached Thumbnails Attached Thumbnails report.jpg  

  2. #2
    Join Date
    Aug 2004
    Location
    Pune,India
    Posts
    94
    you can always write a aggregate query to get the data in the desired way and later use the cross tab report to arrange in the desired fashion.

    Hope this makes sense.

  3. #3
    Join Date
    May 2006
    Posts
    2
    Hi

    I want to create a report as shown in picture - containing 8 weeks worth of data. Is it possible to achive this without cross-tab?

    Thanks
    Attached Thumbnails Attached Thumbnails report.jpg  

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •