All Parameter Query - Data from Access Db

Question

All Parameter Query - Data from Access Db

ian payne

SSC Veteran

Points: 276
More actions
March 23, 2006 at 8:46 am

#171009

Reporting Services All Parameter Query

Trying to obtain data from an Access 2002 database, either a single specific item or All records.

The selection is working against an Autonumber Int field and does not return data for the “ALL” part of the query although this structure works in SQL Server. What needs to be amended to obtain the data from Access?

Thanks

Ian

DROP DOWN SELECTION BOX QUERY

SELECT     ContractorId, Contractor
FROM         tblContractor
UNION
SELECT     - 1, '< ALL > '
FROM         tblcontractor
ORDER BY Contractor

DATA QUERY

SELECT     tblContractor.*, tblContractorAdrs.ContractorAddressLine1, tblContractorAdrs.ContractorAddressLine2, tblContractorAdrs.ContractorAddressLine3,
                      tblContractorAdrs.ContractorAddressLine4, tblContractorAdrs.ContractorAddressLine5, tblContractorAdrs.ContractorPostCode,
                      tblContractor.SapVendID AS Expr1, tblContractor.ContractorId AS ConId
FROM         (tblContractor INNER JOIN
                      tblContractorAdrs ON tblContractor.SapVendID = tblContractorAdrs.SapVendID)
WHERE     (tblContractor.ContractorId = ?) OR
                      (tblContractor.ContractorId = - 1)

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

stevefromOZ SSC-Forever Points: 43646 More actions · Answer 1

I think the problem is in your WHERE clause. In SQL (so this could be different for Access as we use named parameters not '?') your clasue would be

WHERE (tblContractor.ContractorId = @MyParam) OR

(@MyParam = -1)

I'm not sure if this will stand up under 'un-named' parameters as you'd need two question marks which would normally indicate that ytou have two parameters. I gues you could always set the value of parm2 = value of parm1.

An alternative could be to use dynamica sql in the report i.e use the generic query designer and then use an if statement to paste the where clause if it's required. Sort of like

="select ..... from myTable" & If(Parameters!myParm.Value -1, " WHERE ContractorID = " & Parameters!myParm.Value, "")

So basically add the where clause if the parm has a value other than -1 (the 'All') otherwise just append a zero length string to the query (effectively making it no where clause).

Steve.

ian payne SSC Veteran Points: 276 More actions · Answer 2

Thank you for your suggestion Steve.

I have it working now after doing the following, because ODBC connections do not support named parameters.

Ian

DROP DOWN DATA (Key field text)

Select field1, field2, from table UNION Select 'All', 'All', from table

DATA QUERY

Select ... FROM ... WHERE field = ? or ? = 'ALL'

DATA PARAMETERS

two report parameters are created, map both query parameter sto the same one by click on the .... , parameters tab and change the second one to be mapped to the same report parameter as the first

Parameter mapping:

row 1, name field = ?, value field = = Parameters!Parameter1.value

row 2, name field = Parameters!Parameter2.value , value field =

= Parameters!Parameter1.value