Dynamic Parameters 1

[spiego]

I have a report where the user wants to have the ability to run it for either a bill to customer or a ship to customer. I currently have a boolean parameter "Run by bill-to customer?" and it defaults to true. Along with that is a drop down list of the bill-to customers. What I would like to do is if they select false is to have the drop down list show the ship-to customers instead. Is this possible and if so, how do I go about doing it?

Thanks in advance.

 

[mrdenny]

yes, sort of. Have the code which returns the list of customers accept a parameter with that parameter being the value from the boolean value. If the value is 1 then return the bill-to customers. If the value is 0 then return the ship-to customers. The boolean will need to come before the customer dropdown.

Denny
MVP
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / SQL 2005 BI / SQL 2008 DBA / SQL 2008 DBD / SQL 2008 BI / MWSS 3.0: Configuration / MOSS 2007: Configuration)
MCITP (SQL 2005 DBA / SQL 2008 DBA / SQL 2005 DBD / SQL 2008 DBD / SQL 2005 BI / SQL 2008 BI)

My Blog

 

[spiego]

Thanks mrdenny. How do I pass the value from the boolean parameter to the data set for the customers?

 

[xlbo]

Just utilsie the parameter value in the SQL statement / Stored Proc. If the parameter is called @BillTo then you can use something like SELECT Blah FROM tbWhat WHERE CustomerType = @BillTo

This will make the 2nd parameter dependant on the 1st so that they cannot pick a customer without 1st choosing ship to or bill to...

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[spiego]

Thanks xlbo. Ok, I have put together the following statement: =IIF(@RunBy = 1,"Select Distinct INVOICEACCOUNT, SALESNAME From SalesTable
Where DataAreaID = 'nuc' AND (INVENTLOCATIONID IN (@Plant))","Select Distinct CUSTACCOUNT, SALESNAME From SalesTable
Where DataAreaID = 'nuc' AND (INVENTLOCATIONID IN (@Plant))")

My question is, how do I execute this for my drop down list parameter?

 

[mrdenny]

Make the query for for the data set that is the customer drop down look like this.

SELECT DISTINCT CASE WHEN @RunBy = 1 THEN InvoiceAccount ELSE CustAccount END Account, SalesName
FROM SalesTable
Where DataAreaID = 'nuc'
  AND INVENTLOCATIONID IN (@Plant)

Denny
MVP
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / SQL 2005 BI / SQL 2008 DBA / SQL 2008 DBD / SQL 2008 BI / MWSS 3.0: Configuration / MOSS 2007: Configuration)
MCITP (SQL 2005 DBA / SQL 2008 DBA / SQL 2005 DBD / SQL 2008 DBD / SQL 2005 BI / SQL 2008 BI)

My Blog

 

[xlbo]

what he said

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question