Link to home
Start Free TrialLog in
Avatar of gpsdh
gpsdh

asked on

Crystal Reports

I have a report that I wrote in SQL that hard codes what the customer number is for the report.  I set up parameters to be able to run the report so that I can specify the customer.  After setting that up I am attempting to comment out the hard coded customer number so that anyone can choose whatever customer they prefer.  The issue is that Crystal tries to retrieve all the data once I take that out of the command.  There are 28,000 results for this specific report, so when I take the customer number out I believe that it is trying to bring in every possible result from every customer and crashing the temp db.  Is there a way that I can set the crystal report to not attempt to run/verify  unless I actually run it with the correct parameters?

I have unchecked everything in Database - Advanced Options and Reporting.
Avatar of VanDongen Consulting
VanDongen Consulting
Flag of United States of America image

Have you added the new parameter to your selection formula?
...
and {dbtable.customernumber} = {?CustNumberParam}
//and {dbtable.customernumber} = 12345
...

Is this happening when you switch to preview mode, and it's refreshing data without prompting you for the new parameter?  Maybe you could try checking the option to save data with report just to get your change saved, and then uncheck that option to see if the next time you refresh it will prompt you for the parameter.  If this is an issue where you're being prompted and it's easy to just forget to enter the parameter, maybe you could put a default value for the parameter so that the database doesn't try to search for accounts where accountnumber is null.  That could cause a full table scan since nulls can't be indexed in the database.
Avatar of gpsdh
gpsdh

ASKER

SELECT  custprice.cust_code,
vendpart.vend_code ,
        vendpart.vend_name ,
        pricemtx.part_code ,
        partmstr.alt_part1 ,
        partmstr.upc_code ,
        partmstr.alt_part2 ,
        partmstr.part_desc ,
        partmstr.uom ,
        partmstr.avail ,
        partmstr.gros_wt ,
        partmstr.part_length ,
        partmstr.part_width ,
partmstr.part_wt,
        partmstr.part_height ,
        vendpart.min_ord_qty ,
        partmstr.royalty_code ,
        partmstr.part_price ,
        partmstr.cost_no6 ,
        ISNULL(MIN(CASE WHEN pricemtx.price_meth = 'C'
                             AND priceid.channel = '1'
                        THEN ROUND(( ( pricemtx.part_price * .01 )
                                     * partmstr.cost_no6 ), 2)
                        WHEN ( pricemtx.price_meth = 'R'
                               AND priceid.channel = '1'
                             ) THEN ROUND(( pricemtx.part_price ), 2)
                        WHEN ( pricemtx.price_meth = 'M'
                               AND priceid.channel = '1'
                             )
                        THEN ROUND(( cost_no6 * 100 ) / ( 100
                                                          - pricemtx.part_price ),
                                   2)
                        WHEN ( pricemtx.price_meth = 'B'
                               AND priceid.channel = '1'
                               AND pricemtx.disc_type = '%'
                               AND pricemtx.part_disc > '0'
                             )
                        THEN ROUND(( ( 100 - pricemtx.part_disc ) * .01
                                     * partmstr.part_price ), 2)
                        WHEN ( pricemtx.price_meth = 'B'
                               AND priceid.channel = '1'
                               AND pricemtx.disc_type = '%'
                               AND pricemtx.part_disc = '0'
                             ) THEN partmstr.cost_no6
                                     
                                     
                                     
                        WHEN pricemtx.price_meth = 'B'
                             AND priceid.channel = '1'
                             AND pricemtx.disc_type = '$'
                             AND pricemtx.formula_id = 'BASELIST'
                        THEN ROUND(( partmstr.part_price - pricemtx.part_disc ),
                                   2)
                        WHEN pricemtx.price_meth = 'B'
                             AND priceid.channel = '1'
                             AND pricemtx.disc_type = '$'
                             AND pricemtx.formula_id <> 'BASELIST'
                        THEN ROUND(( ( pricemtx_1.part_price * .01 )
                                     * ( partmstr.cost_no6 )
                                     - pricemtx.part_disc ), 2)
                   END),
               MIN(CASE WHEN pricemtx.price_meth = 'C'
                        THEN ROUND(( ( pricemtx.part_price * .01 )
                                     * partmstr.cost_no6 ), 2)
                        WHEN ( pricemtx.price_meth = 'R' )
                        THEN ROUND(( pricemtx.part_price ), 2)
                        WHEN ( pricemtx.price_meth = 'M' )
                        THEN ROUND(( cost_no6 * 100 ) / ( 100
                                                          - pricemtx.part_price ),
                                   2)
                        WHEN ( pricemtx.price_meth = 'B'
                               AND pricemtx.disc_type = '%'
                             )
                        THEN ROUND(( ( 100 - pricemtx.part_disc ) * .01
                                     * partmstr.part_price ), 2)
                        WHEN pricemtx.price_meth = 'B'
                             AND pricemtx.disc_type = '$'
                             AND pricemtx.formula_id = 'BASELIST'
                        THEN ROUND(( partmstr.part_price - pricemtx.part_disc ),
                                   2)
                        WHEN pricemtx.price_meth = 'B'
                             AND pricemtx.disc_type = '$'
                             AND pricemtx.formula_id <> 'BASELIST'
                        THEN ROUND(( ( pricemtx_1.part_price * .01 )
                                     * ( partmstr.cost_no6 )
                                     - pricemtx.part_disc ), 2)
                   END)) AS 'NET_PRICE' ,
        priceid.channel ,
        pricemtx.price_meth,
        priceid.price_id,
       pricemtx.end_date,
       vend_part
FROM    pricemtx
        INNER JOIN partmstr ON pricemtx.part_code = partmstr.part_code
        LEFT OUTER JOIN priceid ON pricemtx.price_id = priceid.price_id
        LEFT OUTER JOIN vendpart ON partmstr.part_code = vendpart.part_code
                                    AND partmstr.price_cat1 = vendpart.vend_code
        LEFT OUTER JOIN pricemtx AS pricemtx_1 ON pricemtx.formula_id = pricemtx_1.price_id
                                                  AND pricemtx.part_code = pricemtx_1.part_code
        LEFT OUTER JOIN custprice ON pricemtx.price_id = custprice.price_id
WHERE   ( pricemtx.end_date > DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), 0) )
       AND ( custprice.cust_code = '901260' )
        AND ( partmstr.avail = 'S' )
        --AND vend_code <> '100'
       --AND vend_code <> '225'
      --  AND pricemtx.part_code NOT like 'CHD%'
      --  AND vend_code <> '8810'
      AND ( partmstr.tech_level BETWEEN '1' AND '3' )
       AND cost_no6 > '0'
      -- AND vend_code <> '391'
        --AND dbo.partmstr.part_code = 'SFP00461'
GROUP BY pricemtx.part_code ,
        partmstr.alt_part1 ,
        partmstr.upc_code ,
        partmstr.alt_part2 ,
        partmstr.part_desc ,
        partmstr.uom ,
        partmstr.avail ,
        partmstr.gros_wt ,
        partmstr.part_length ,
        partmstr.part_width ,
        partmstr.part_height ,
partmstr.part_wt,
        vendpart.min_ord_qty ,
        partmstr.royalty_code ,
        vendpart.vend_code ,
        vendpart.vend_name ,
        partmstr.part_price ,
        partmstr.cost_no6 ,
        partmstr.part_code,
     priceid.channel ,
      pricemtx.price_meth,
       priceid.price_id,
       pricemtx.end_date,
       vend_part,
       custprice.cust_code
ORDER BY dbo.partmstr.part_code

Open in new window


I have this in the select expert

(not HasValue({?cust_code}) OR {Command.cust_code} = {?cust_code})

If I comment out this    "AND ( custprice.cust_code = '901260' ) "   in the code it attempts to bring back billions of records.  If I have that in it runs for a second and brings back the results.

I have the default record as 901260, but when I alter the command it doesn't use this and just tries to query the database.
ASKER CERTIFIED SOLUTION
Avatar of VanDongen Consulting
VanDongen Consulting
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of gpsdh

ASKER

User generated image

Here are screen shots of what I have and errors.
What if you try this?

IF
   isnull({?cust_code}) or {?cust_code} = '' THEN True
ELSE
   {Command.cust_code} = {?cust_code}
Avatar of gpsdh

ASKER

That allowed me to save it.  The issue comes when I comment out the code for the customer.  It just spins until it crashes.

User generated image
You don't have a command parameter in that screen shot.  Try clicking Create from the command window Parmeter List, and then instead of commenting out --AND (custprice.cust_code = '901260'), try this:

AND (custprice.cust_code = {?cust_code})

If you created the parameter somewhere else, you may have to delete it there before you can use the same name in the command.

Also, it looks like cust_code is a string field, so you may have to figure out how to add the single quotes either when you put a value in for the parameter, or concatenating the parameter in between single quotes.
And, I would think you can then remove the select formula from where you initially had it since it's now an explicit part of the where clause in the query.
Avatar of Mike McCracken
That is what is needed.  Don't create the parameter in the report but create one in the Command.  You won't have an option for optional but it could be set to accept ALL

You don't need the record selection formula then.

mlmcc
Avatar of gpsdh

ASKER

Pricelist.rpt

I created my query as a view without the customer code hard coded.  If I run this it comes back with no data.  All the other things didn't work.  Sorry was out of the office.

SELECT *
FROM dbo.pricelist
WHERE cust_code = '{?cust_code}' 

Open in new window

Take ' ' off the parameter

SELECT *
FROM dbo.pricelist
WHERE cust_code =  {?cust_code}

Open in new window


mlmcc
Avatar of gpsdh

ASKER

Thanks for the help.  I ended up creating it as a view and then instead of pasting in a command I just brought in the view.  Crystal dealt with that better and didn't crash the tempdb.