Handy SQL Queries

is when you use report parameters to supply some of the details.  For instance, I don't know what all my package GUIDs are, so you can add a Dropdown parameter type with a "List type:" of Query Results, and value type of "String" to find all your custom packages (my code below shows only user-created SWD packages, based on the ClassGuid):

SELECT DISTINCT [Name], [Guid]
FROM vSWDPackage pack
WHERE pack.[ClassGuid] = '3DA89D2F-A4D8-4B3E-9FB7-CDA3BFC0B9CE'
AND [Name] NOT IN ('Some Example Package', 'Another Example')
ORDER BY [Name] ASC

If you name the parameter "_PackageID", then in your code example, you put:

WHERE
pg.packageid = %_PackageID%

thanks for the posting sir !

Thanks...I don't have the time to teach myself SQL, so having this type of info handy is helpful.

Can you provide a SQL to find a collection if I have the GUID?

dijoe,

Try this:
 

SELECT vcol.Name, vCol.Guid
FROM vCollection vcol
WHERE vcol.Guid = '<your guid here>'

There are times when you use a table that you want ot use as a drop-down in a report but still want to have the default as bringing back all results first?

Using this query below will create a temporary table that will take advantage of the Hidden and visable fields in a drop down.

To change what information you are wanting to have in the drop-down just replace the highlighted part of the query with what ever you want and you will have the first enry of the drop down as 'All' with the background entry as '%'

---------------------------------------------------------------------------------------
declare @all nvarchar(50)

if(object_id('tempdb..#swversion') is not null)
begin
drop table #swversion
end

--prepare a temp table for the version values
create table #swversion
(
VisVer nvarchar(50),
HidVer nvarchar(50)
)
insert into #swversion
values ('All','%')

insert #swversion (visver,Hidver)

SELECT distinct
T1.[Version] AS 'Version',
T1.[Version] AS 'Version2'
FROM [Inv_AeX_OS_Add_Remove_Programs] T1

WHERE T1.[Name] like '%Office Communicator%'

select * from #swversion

drop table #swversion

----------------------------------------------------------------------------------------

Andy,
I have a bit of a simpler way to do this using the UNION operator..in the parameter query, do something like:

SELECT '--All--' AS [Version], '%' AS [Version2]
 
UNION
SELECT distinct
T1.[Version] AS [Version],
T1.[Version] AS [Version2]
FROM [Inv_AeX_OS_Add_Remove_Programs] T1

WHERE T1.[Name] like '%Office Communicator%'
ORDER BY T1.[Name] ASC

Hi Kyle,

Thanks for that, I've just tried it but the only thing is that the all entry appears at the bottom of the list instead of the default enrty.

Is there any way of changing your script to put the all statement at the top of the list?

Andy

OK, this worked for me:

SELECT '--All--' AS [Version], '%' AS [Version2]

UNION

SELECT
[Version] AS [Version],
[Version] AS [Version2]
FROM [Inv_AeX_OS_Add_Remove_Programs] T1

WHERE [Name] like '%Office Communicator%'
ORDER BY [Version2]

In this case at least the % sorts higher than a number.

Hi andyolly,

The query u posted for drop downs, really helped me.....

Thanks....

Anyone know how to create a sql query (NS7) to find all computers that start with "GUM" and add these to a filter folder

Thanks