Free Shipping on All Orders 1-866-764-1801

Vist our Online Store
+ Reply to Thread
Results 1 to 7 of 7
  1. #1

    Member Sales Rating: (20)

    Join Date
    Jan 2004
    Location
    7th Circle of Hell - aka DFW
    Posts
    10,656

    Default Anyone here know Crystal Reports?

    May not be too complex for someone else, but here's what I need to do.

    I have a set of data I'm grouping on Customer, and am trying to figure out a way to find out if item y appears after item x within that group. The tricky part is that item y can also come before item x, but I only care if it comes after. So here's what my data looks like:

    Data within the group is sorted by date)

    Customer Number 123
    Date Item Y
    Date Item X
    Date Item Y
    Date Item Z

    Customer Number 456
    Date Item Y
    Date Item X
    Date Item Z

    So for Customer Number 123 I'd want the formula to return a 1 or a yes or whatever, but for Customer Number 456 I wouldn't want anything returned since no Item Y comes after Item X.

    Here's what I've done so far. I created a running total to count Item x, have this total in every row of the report and at the group level, total resets itself on each group.

    Then I have a formula, on every record, that basically says if the running total (above) is greater than 0 and this row is item y, then stamp eith a 1.

    All that works great, and when I unhide the details section I can see it's working correctly. So that leaves one last step, which Crystal apparently does NOT support. I need to know at the group level what the total of that formula I created above is. However, when I go to create a running total at the group, the formula field is NOT one of the fields I can choose to total, likely because it's also based on a total...

    So it's the very last piece that I'm stuck on.

    Any help is appreciated.
    Main HT
    Magnepan 1.6QR fronts, POlk R15 surrounds, Pioneer SC-25, Parasound Halo A23, Oppo BDP-105, Panasonic TC-P60ZT60, Sony PS3, Apple TV

    Bedroom System
    Polk Blackstone TL3, Polk PSWi225 Wireless Sub, HK 3490 Integrated, Oppo BDP-103, Sharp Aquos 32" TV, Apple TV

    Office Rig
    27" iMac w/Amarra, AudioQuest Dragonfly 1.2, Focal XS Book, Schiit Valhalla, Cypher Labs Theorem 720, Philips Fidelio X1, Sennheiser HD600, HiFiMan HE-500, B&W P7, LG 47LM7600, Sony PS3, Apple TV

  2. #2

    Member Sales Rating: (19)

    Join Date
    Aug 2003
    Location
    Kansas City KS/Central IA
    Posts
    5,649

    Default

    Can you send the results of your formula to a report, then total the report at the group level?

    A BI tool like business objects would make that easier!
    DKG999
    -----------------------------------------
    HT System: LSi9, LSiCx2, LSiFX, LSi7, SVS 20-39 PC+, B&K 507.s2 AVR, B&K Ref 125.2, Tripplite LCR-2400, Cambridge 650BD, Signal Cable PC/SC, BJC IC, Samsung 55" LED

    Music System: Magnepan 1.6QR, SVS SB12+, ARC pre, Parasound HCA1500 vertically bi-amped, Jolida CDP, Pro-Ject RM5.1SE TT, Pro-Ject TubeBox SE phono pre, SBT, PS Audio DLIII DAC

  3. #3

    Member Sales Rating: (20)

    Join Date
    Jan 2004
    Location
    7th Circle of Hell - aka DFW
    Posts
    10,656

    Default

    I'm not sure how I would do that. I don't know Crystal all that well. This is a complex report overall, and I was hoping to avoid having to recreate it in SQL, but looks like I may have to go that route...this sucks...
    Main HT
    Magnepan 1.6QR fronts, POlk R15 surrounds, Pioneer SC-25, Parasound Halo A23, Oppo BDP-105, Panasonic TC-P60ZT60, Sony PS3, Apple TV

    Bedroom System
    Polk Blackstone TL3, Polk PSWi225 Wireless Sub, HK 3490 Integrated, Oppo BDP-103, Sharp Aquos 32" TV, Apple TV

    Office Rig
    27" iMac w/Amarra, AudioQuest Dragonfly 1.2, Focal XS Book, Schiit Valhalla, Cypher Labs Theorem 720, Philips Fidelio X1, Sennheiser HD600, HiFiMan HE-500, B&W P7, LG 47LM7600, Sony PS3, Apple TV

  4. #4

    Member Sales Rating: (29)

    Join Date
    Apr 2007
    Posts
    1,068

    Default

    You can do it in MS Reporting Services. Create Matrix report.

  5. #5

    Member Sales Rating: (20)

    Join Date
    Jan 2004
    Location
    7th Circle of Hell - aka DFW
    Posts
    10,656

    Default

    Yeah, it's just that's going to take at least a full day to complete, and was hoping to avoid that
    Main HT
    Magnepan 1.6QR fronts, POlk R15 surrounds, Pioneer SC-25, Parasound Halo A23, Oppo BDP-105, Panasonic TC-P60ZT60, Sony PS3, Apple TV

    Bedroom System
    Polk Blackstone TL3, Polk PSWi225 Wireless Sub, HK 3490 Integrated, Oppo BDP-103, Sharp Aquos 32" TV, Apple TV

    Office Rig
    27" iMac w/Amarra, AudioQuest Dragonfly 1.2, Focal XS Book, Schiit Valhalla, Cypher Labs Theorem 720, Philips Fidelio X1, Sennheiser HD600, HiFiMan HE-500, B&W P7, LG 47LM7600, Sony PS3, Apple TV

  6. #6

    Member Sales Rating: (10)

    Join Date
    Jun 2007
    Location
    Cape Cod, Massachusetts
    Posts
    1,286

    Default

    I would look to SQL for this. You'll probably have to create two sub-sql calls to calculate the first date occurrence of X and the first date occurreance of Y, and then wrap them in another SQL call that ensures X is less than Y.

    Example:

    SELECT DISTINCT
    Orders.CustomerID,
    QueryX.XDate,
    QueryY.YDate

    FROM
    (
    Orders INNER JOIN
    (
    SELECT
    CustomerID,
    First(Date) as XDate
    FROM Orders
    GROUP BY CustomerID, Item
    HAVING Item="X"
    ) as QueryX
    ON Orders.CustomerID=QueryX.CustomerID
    )

    INNER JOIN

    (
    SELECT
    CustomerID,
    First(Date) as YDate
    FROM Orders
    GROUP BY CustomerID, Item
    HAVING Item="Y"
    ) as QueryY
    ON Orders.CustomerID=QueryY.CustomerID

    WHERE

    QueryX.XDate < QueryY.YDate
    Speakers: Polk LSi15
    Pre: Adcom GFP-750 with HT Bypass
    Amp: Pass Labs X-150
    CD/DVD Player: Classe CDP-10
    Interconnects: MIT Shortgun S3 Pro XLR
    Speaker cables: MIT MH-750 bi-wire
    TT:Micro Seiki DD-35
    Cartridge:Denon DL-160
    Phono Pre:PS Audio GCPH

  7. #7

    Member Sales Rating: (19)

    Join Date
    Feb 2006
    Location
    Tampa, FL
    Posts
    7,425

    Default

    You have to know the location of the parameters you are looking for in the database. Then you have to pray that when you add the next location you are grouping does not make you data invisible. Good luck. I have do trial and error.


    engtaz

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

     

Similar Threads

  1. The Crystal Method
    By VR3 in forum Music & Movies
    Replies: 14
    Last Post: 04-12-2008, 11:25 PM
  2. Crystal Acoustics?
    By zingo in forum Speakers
    Replies: 0
    Last Post: 10-19-2007, 05:46 PM
  3. Carfax reports / VIN lookup?
    By michael_w in forum The Clubhouse
    Replies: 9
    Last Post: 08-03-2007, 08:24 PM
  4. Wharfedale Crystal 30
    By AzN_plyR in forum Speakers
    Replies: 5
    Last Post: 01-26-2006, 09:35 PM
  5. Crystal Method
    By subcrit in forum Music & Movies
    Replies: 10
    Last Post: 12-18-2001, 10:22 AM

Posting Permissions

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