Results 1 to 5 of 5

Thread: Crystal Report with conditional selection criteria

  1. #1

    Thread Starter
    PowerPoster MMock's Avatar
    Join Date
    Apr 2007
    Location
    Driving a new Maserati down a dead end street
    Posts
    4,245

    Crystal Report with conditional selection criteria

    Hello. I need to design a Crystal report that can branch one way or another with its selection criteria. I have a database table containing a column called lines and those lines are grouped together by column order number. So let's say I have order number XYZABC and in that order I have lines 1 -10 represented by 10 rows in the table. I might want grab all 10 lines for the report or specific ones specified at runtime. Do you know how I would be able to have one report (the way we've done this is in the past is to have two reports, but I'd prefer to only have to maintain one) and have it branch conditionally and say something like "WHERE SONumber = 'XYZABC'" when users want the whole thing but also "WHERE SONumber = 'XYZABC' AND line IN (1, 2, 8)" when they want certain lines? If it's relevant to know this, the report will only be run through an application where the users hit a button "Print Labels" and in a grid they've either selected all rows (the first criterion would run) or selected individual rows (2nd criterion would run). And I know I could have the "all rows" scenario execute "line IN (1, 2, etc 10) but how less efficient is that than just asking the DB provider (SQL Server) for the whole SONumber? Thank you!
    There are 10 kinds of people in this world. Those who understand binary, and those who don't.

  2. #2
    PowerPoster techgnome's Avatar
    Join Date
    May 2002
    Posts
    33,401

    Re: Crystal Report with conditional selection criteria

    how less efficient is that
    Depends on the query. IF it has to be dynamically built to support the sometimes there's rows selected, and sometimes not, then it maybe more efficient to have it always to an IN when all rows are selected. Because then you're always passing in that list, and it's always there, and it wouldn't need to be dynamic. So it's largely dependent on the construction on the query. Personally, I'd construct it to pass in a CSV list, parse it out into a temp table or table variable, then use that as a join, rather than an IN query. Then I would pass in all rows selected, whether that means ALL rows, or only some rows.

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  3. #3

    Thread Starter
    PowerPoster MMock's Avatar
    Join Date
    Apr 2007
    Location
    Driving a new Maserati down a dead end street
    Posts
    4,245

    Re: Crystal Report with conditional selection criteria

    Yes, tg. One thing I was considering was something I've done in the past that I thought was very cool based on this stackoverflow post. I can do the same thing here. I wasn't sure if there was a better way to have Crystal handle it. Thanks.
    There are 10 kinds of people in this world. Those who understand binary, and those who don't.

  4. #4

    Thread Starter
    PowerPoster MMock's Avatar
    Join Date
    Apr 2007
    Location
    Driving a new Maserati down a dead end street
    Posts
    4,245

    Re: Crystal Report with conditional selection criteria

    I have my original problem solved and now I am onto something a bit more complex.

    I need to run my crystal report based on two parameters (same as described above): order number (SONO) and line number (SLINE), and multiple sets of them. Meaning, the SQL Server criteria would be:
    where (SONO = '1056840' and SLINE in (2, 3, 4))
    or (SONO = '1056841' and SLINE in (7, 8, 9))
    or (SONO = '1056842' and SLINE in (5, 10))

    I seem to be generating the equivalent of
    and SONO in ('1056840', '1056841', '1056842')
    and SLINE in (2, 3, 4, 5, 7, 8, 9, 10)

    How do I configure the report in crystal to keep the lines within the orders?
    There are 10 kinds of people in this world. Those who understand binary, and those who don't.

  5. #5

    Thread Starter
    PowerPoster MMock's Avatar
    Join Date
    Apr 2007
    Location
    Driving a new Maserati down a dead end street
    Posts
    4,245

    Re: Crystal Report with conditional selection criteria

    I found a clever way to deal with this. I am concatenating a string made up of SONO and line. SO my criterion goes like this: SONO + '|' + CONVERT(nvarchar(4), SLINE), and that works. (I am calling it clever, you are probably calling it obvious).

    But I have bigger problems. SONO and SLINE are defined as a compound primary key in this database table. This database is a vendor database but I'm not sure how much of it we customized and how much of it came as-is. I would hate to think the vendor did this. Or maybe it's okay and I'm just mad it's okay because after all that hard work this is going to mess me up. I am now being told that SLINE can change. They occasionially reorder lines. I don't know why yet. But what I've been doing is working on items in an order and printing labels for the items and keeping track of what's been printed and what's been queued up for printing later. So let's say SONO = 1056840 and SLINE is 2 and the description says it's a Connector Kit. There's another item in the order, SLINE = 3 and it's an antenna. So if we print the antenna label I will update SLINE 3 as having been printed. And then they're going to switch the lines but the database will think SLINE 3 the antenna was printed, but now SLINE 3 is the connector kit. Right? I don't understand this. Was I stupid to think the primary key was set in stone?
    There are 10 kinds of people in this world. Those who understand binary, and those who don't.

Posting Permissions

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



Click Here to Expand Forum to Full Width