Parsing an array of Values for a single Parameter

[GShort]

I have a parameter that returns all the service tickets from a paticular office when that office is chosen. But now the VP is wanting a choice of all.

Can anyone help me out on this.

 

[JohnDTampaBay]

I take it the service tickets parameter cascades from the office parameter and the report query uses both parameters - or at least service tickets - for its query. Does the veep want a selection for all tickets or all offices? Either way the solution is about the same.

In the query that returns the list of offices or tickets, add a UNION ALL to include a selection for ALL. In the query that accepts the ALL value, add code to handle the ALL option.

For example, your tickets query may look something like this:

SELECT TicketID, TicketDate, etc.
FROM dbo.Tickets
WHERE OfficeID = @OfficeID

For the ALL selection, change the query like so:

SELECT TicketID, TicketDate, etc.
FROM dbo.Tickets
WHERE OfficeID = CASE WHEN @OfficeID = 'ALL'
                      THEN OfficeID
                      ELSE @OfficeID END

--John
-----------------------------------
Behold! As a wild ass in the desert
go forth I to do my work.
--Gurnie Hallock (Dune)

 

[Catadmin]

Something I did was select the names into a table. Here's an example of me pulling Column Names for my Data Dictionary Report:

DataSet AllColumnNames

Declare @collist2 Table (id int identity(1,1), ColName VarChar(255))

	Insert into @collist2(ColName) Values('All Columns')
	Insert into @collist2
	Select distinct c.[name] as ColName
	From sysobjects o inner join syscolumns c on o.[id] = c.[id]
	Where  o.xtype = 'U' and
		o.[name] = @tblName
	
	Select * from @collist2
	Order by id asc

Then set up a report parameter to use this query as its source. I called my @colName and set it up to look at the AllColumnNames dataset, ColumnName field.

Lastly, you'll need to put an IIF statement in the expression properties of the field where the data is printed. Such as:

=iif( Parameters!colName.Value <> "All Columns",  Parameters!colName.Value, Fields!ColumnName.Value )

You can reverse the IIF() function to be '= "All Columns" and then switch the True and False parts of the function around if you want. That's mostly cosmetic.

BTW, if you don't know, the IIF() function is an If-Then-Else statement that works as IIF(Conditional check, True/Then Part, False/Else Part).

Hope that helps.



Catadmin - MCDBA, MCSA
"If a person is Microsoft Certified, does that mean that Microsoft pays the bills for the funny white jackets that tie in the back???