1 Reply Latest reply: Apr 12, 2012 7:03 AM by lpeterso RSS

    query won't pull in parameter value

    Rookie

      I'm trying to create a report with 2 columns that pull in the device name I specify in the parameter to give me the information for software and printers for that specific device.

       

      This is the setup I have:

      2 datasets in the report - one named "software" and one named "printers"

      1 parameter: "PDeviceName"

       

      Both datasets have the parameter set to =[@PDeviceName].ToString().Replace("*", "%").ToUpper()

       

      The report has 2 tables set up: one to show the software list and one to show the printers installed

           The software table properties dataset name is set to "software"

           The printers table properties dataset name is set to "printers"

       

      I've put the following queries into the datasets to return all software for the specifice device entered in the parameter prompt and another query to return a list of all printers.

       

      The software query correctly pulls in the device name that is entered and provides the list of software needed, but the printer query will not display the printers on the report. That table is always blank.

       

      Software query:

       

      SELECT DISTINCT
          A0.DEVICENAME DeviceName,
          A2.title SuiteName,
          A2.VERSION appVersion,
          A2.PUBLISHER Publisher
      FROM
          Computer A0
          INNER JOIN productcomputer A1 ON A0.Computer_Idn = A1.Computer_Idn
          inner join product a2 on a1.product_idn = a2.product_idn
          LEFT OUTER JOIN SCOPECOMPUTER ON A0.COMPUTER_IDN = SCOPECOMPUTER.COMPUTER_IDN 
      WHERE A0.DEVICENAME IS NOT NULL
          AND A0.DEVICENAME LIKE ?   
      ORDER BY
          A0.DEVICENAME, a2.title
      Printer query:
      SELECT DISTINCT

       

           A0.DEVICENAME DeviceName,    

           A1.NAME AS "Name"

      FROM

           Computer A0

           LEFT OUTER JOIN Printer A1 ON A0.Computer_Idn = A1.Computer_Idn

           LEFT OUTER JOIN ScopeComputer A2 ON A0.Computer_Idn = A2.Computer_Idn 

      WHERE A1.NAME LIKE N'%BJU.iPrin%'

           AND A0.DEVICENAME LIKE ?

      ORDER BY 
           A0.DEVICENAME, a2.NAME
      Can anyone see where I'm going wrong? Thank you.