Multi Parameters ability to exclude.

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Multi Parameters ability to exclude.

Author

Topic

hbadministrator
Posting Yak Master

120 Posts

Posted - 2014-10-01 : 11:18:49

I have a report that has 5 datasets the first one is the primary that has 4 fields with a where statement (see below). Then every other dataset is each field with a group by (see below). Using SSRS I am trying to get each field to be a multi select parameter. Ideally having each parameter show up as a Default Value as Blank (checkbox) and then they can choose each Parameter they want to use for the query would be great. Any help is greatly appreciated. Example: First selection would be salesperson 3 people and Division and I want to leave Department and Territory Blank. Second Example: I just want to see All Salesperson's and Division, Department and Territory are blank Multi value parameters.

Dataset 1

SELECT
[Slspn-Code]
,[SerDeparment]
,[Divcode]
,[Territory]

FROM [Service].[dbo].[TechServiceDetails]

WHERE ([Slspn-Code] IN (@Slspn))
OR (SerDeparment IN (@SerDeprment))
OR (Divcode IN (@Divcode))
OR (Territory IN (@Territory))

Dataset 2

SELECT [Slspn-Code]
FROM TechServiceDetails
GROUP BY [Slspn-Code]

Dataset 3
SELECT SerDeparment
FROM TechServiceDetails
GROUP BY SerDeparment

Dataset 4
SELECT Divcode
FROM TechServiceDetails
GROUP BY Divcode

Dataset 5
SELECT Territory
FROM TechServiceDetails
GROUP BY Territory

hbadministrator
Posting Yak Master

120 Posts

Posted - 2014-10-07 : 08:15:05

I was able to answer my own question. Walking away for a bit and coming back helped. So to fix this issue since we are using SSRS I needed to create Union's on Dataset 2,3,4,5 and remove the Group by. This creates a field blank so when I actually use the query from Dataset 1 I can select Blank and still use the other Parameters. In ssrs make sure you go to Parameter Properties and select Allow Multiple Values and then go under Available Values select the corresponding Dataset.

Dataset 2
SELECT [Slspn-Code]
FROM TechServiceDetails
Union Select '(BLANK)' AS [Slspn-Code]

Dataset 3
SELECT SerDeparment
FROM TechServiceDetails
Union Select '(BLANK)' AS SerDeparment

Dataset 4
SELECT Divcode
FROM TechServiceDetails
Union Select '(BLANK)' AS Divcode

Dataset 5
SELECT Territory
FROM TechServiceDetails
Union Select '(BLANK)' AS Territory

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources