Hi There; Don't know SQL - Looking how to Hardcode a collection ID into one of the basic reports and hand it off to the appropriate sections here. ie -  Hardware 01A - Summary of computers in a specific collection select distinct v_R_System_Valid.ResourceID, v_R_System_Valid.Netbios_Name0 AS [Computer Name], v_R_System_Valid.Resource_Domain_OR_Workgr0 AS [Domain/Workgroup], v_Site.SiteName as [SMS Site Name], [Top Console User] = CASE when (v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP.TopConsoleUser0 is NULL or v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP.TopConsoleUser0 = '-1') then 'Unknown' Else v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP.TopConsoleUser0 End, v_GS_OPERATING_SYSTEM.Caption0 AS [Operating System], v_GS_OPERATING_SYSTEM.CSDVersion0 AS [Service Pack Level], v_GS_SYSTEM_ENCLOSURE_UNIQUE.SerialNumber0 AS [Serial Number], v_GS_SYSTEM_ENCLOSURE_UNIQUE.SMBIOSAssetTag0 AS [Asset Tag], v_GS_COMPUTER_SYSTEM.Manufacturer0 AS [Manufacturer], v_GS_COMPUTER_SYSTEM.Model0 AS [Model], v_GS_X86_PC_MEMORY.TotalPhysicalMemory0 AS [Memory (KBytes)], v_GS_PROCESSOR.NormSpeed0 AS [Processor (GHz)], (Select sum(Size0) from v_GS_LOGICAL_DISK inner join v_FullCollectionMembership on (v_FullCollectionMembership.ResourceID = v_GS_LOGICAL_DISK.ResourceID ) where v_GS_LOGICAL_DISK.ResourceID =v_R_System_Valid.ResourceID and v_FullCollectionMembership.CollectionID = @CollectionID) As [Disk Space (MB)], (Select sum(v_GS_LOGICAL_DISK.FreeSpace0) from v_GS_LOGICAL_DISK inner join v_FullCollectionMembership on (v_FullCollectionMembership.ResourceID = v_GS_LOGICAL_DISK.ResourceID ) where v_GS_LOGICAL_DISK.ResourceID =v_R_System_Valid.ResourceID and v_FullCollectionMembership.CollectionID = @CollectionID) As [Free Disk Space (MB)] from v_R_System_Valid inner join v_GS_OPERATING_SYSTEM on (v_GS_OPERATING_SYSTEM.ResourceID = v_R_System_Valid.ResourceID) left join v_GS_SYSTEM_ENCLOSURE_UNIQUE on (v_GS_SYSTEM_ENCLOSURE_UNIQUE.ResourceID = v_R_System_Valid.ResourceID) inner join v_GS_COMPUTER_SYSTEM on (v_GS_COMPUTER_SYSTEM.ResourceID = v_R_System_Valid.ResourceID) inner join v_GS_X86_PC_MEMORY on (v_GS_X86_PC_MEMORY.ResourceID = v_R_System_Valid.ResourceID) inner join v_GS_PROCESSOR on (v_GS_PROCESSOR.ResourceID = v_R_System_Valid.ResourceID) inner join v_FullCollectionMembership on (v_FullCollectionMembership.ResourceID = v_R_System_Valid.ResourceID) left join v_Site on (v_FullCollectionMembership.SiteCode = v_Site.SiteCode) inner join v_GS_LOGICAL_DISK on (v_GS_LOGICAL_DISK.ResourceID = v_R_System_Valid.ResourceID) and v_GS_LOGICAL_DISK.DeviceID0=SUBSTRING(v_GS_OPERATING_SYSTEM.WindowsDirectory0,1,2) left join v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP on (v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP.ResourceID = v_R_System_Valid.ResourceID) Where v_FullCollectionMembership.CollectionID = @CollectionID Order by v_R_System_Valid.Netbios_Name0 I'd like to remove the prompt (properly), and just have it run for collection ID "TRN001E" Can someone please alter the code above to the desired result? Also, if there's no prompt, will I be able to add it to a Dashboard? Thanks very much. Gregg

Where v_FullCollectionMembership.CollectionID = "TRN001E" However... I can tell already that won't work. because the collectionid you provided is only 7 characters long. Every single collectionID, without exception, MUST be 8 characters long, no more, and no less. So... if you've already tried that and it didn't work--it's because you have the wrong collectionid. Please double check it. Yes, with no prompts you'll be able to add it to a dashboard (once you have a valid collection id, that actually exists) Standardize. Simplify. Automate.

Replace variable @CollectionID with 'collection ID'

Hi Sherry; Sorry, my mistake - should be TRN0001E. Thanks, Gregg

Still getting prompted to choose the collection - did a find/replace @CollectionID with "TRN0001E", and pasted the code back in. OK, removed the prompt - now get error - invalid column name 'TRN0001E' - not sure where this is coming from in the code. select distinct v_R_System_Valid.ResourceID, v_R_System_Valid.Netbios_Name0 AS [Computer Name], v_R_System_Valid.Resource_Domain_OR_Workgr0 AS [Domain/Workgroup], v_Site.SiteName as [SMS Site Name], [Top Console User] = CASE when (v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP.TopConsoleUser0 is NULL or v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP.TopConsoleUser0 = '-1') then 'Unknown' Else v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP.TopConsoleUser0 End, v_GS_OPERATING_SYSTEM.Caption0 AS [Operating System], v_GS_OPERATING_SYSTEM.CSDVersion0 AS [Service Pack Level], v_GS_SYSTEM_ENCLOSURE_UNIQUE.SerialNumber0 AS [Serial Number], v_GS_SYSTEM_ENCLOSURE_UNIQUE.SMBIOSAssetTag0 AS [Asset Tag], v_GS_COMPUTER_SYSTEM.Manufacturer0 AS [Manufacturer], v_GS_COMPUTER_SYSTEM.Model0 AS [Model], v_GS_X86_PC_MEMORY.TotalPhysicalMemory0 AS [Memory (KBytes)], v_GS_PROCESSOR.NormSpeed0 AS [Processor (GHz)], (Select sum(Size0) from v_GS_LOGICAL_DISK inner join v_FullCollectionMembership on (v_FullCollectionMembership.ResourceID = v_GS_LOGICAL_DISK.ResourceID ) where v_GS_LOGICAL_DISK.ResourceID =v_R_System_Valid.ResourceID and v_FullCollectionMembership.CollectionID = "TRN0001E") As [Disk Space (MB)], (Select sum(v_GS_LOGICAL_DISK.FreeSpace0) from v_GS_LOGICAL_DISK inner join v_FullCollectionMembership on (v_FullCollectionMembership.ResourceID = v_GS_LOGICAL_DISK.ResourceID ) where v_GS_LOGICAL_DISK.ResourceID =v_R_System_Valid.ResourceID and v_FullCollectionMembership.CollectionID = "TRN0001E") As [Free Disk Space (MB)] from v_R_System_Valid inner join v_GS_OPERATING_SYSTEM on (v_GS_OPERATING_SYSTEM.ResourceID = v_R_System_Valid.ResourceID) left join v_GS_SYSTEM_ENCLOSURE_UNIQUE on (v_GS_SYSTEM_ENCLOSURE_UNIQUE.ResourceID = v_R_System_Valid.ResourceID) inner join v_GS_COMPUTER_SYSTEM on (v_GS_COMPUTER_SYSTEM.ResourceID = v_R_System_Valid.ResourceID) inner join v_GS_X86_PC_MEMORY on (v_GS_X86_PC_MEMORY.ResourceID = v_R_System_Valid.ResourceID) inner join v_GS_PROCESSOR on (v_GS_PROCESSOR.ResourceID = v_R_System_Valid.ResourceID) inner join v_FullCollectionMembership on (v_FullCollectionMembership.ResourceID = v_R_System_Valid.ResourceID) left join v_Site on (v_FullCollectionMembership.SiteCode = v_Site.SiteCode) inner join v_GS_LOGICAL_DISK on (v_GS_LOGICAL_DISK.ResourceID = v_R_System_Valid.ResourceID) and v_GS_LOGICAL_DISK.DeviceID0=SUBSTRING(v_GS_OPERATING_SYSTEM.WindowsDirectory0,1,2) left join v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP on (v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP.ResourceID = v_R_System_Valid.ResourceID) Where v_FullCollectionMembership.CollectionID = "TRN0001E" Order by v_R_System_Valid.Netbios_Name0

copy/paste the sql as it exists now.Standardize. Simplify. Automate.

select distinct v_R_System_Valid.ResourceID, v_R_System_Valid.Netbios_Name0 AS [Computer Name], v_R_System_Valid.Resource_Domain_OR_Workgr0 AS [Domain/Workgroup], v_Site.SiteName as [SMS Site Name], [Top Console User] = CASE when (v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP.TopConsoleUser0 is NULL or v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP.TopConsoleUser0 = '-1') then 'Unknown' Else v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP.TopConsoleUser0 End, v_GS_OPERATING_SYSTEM.Caption0 AS [Operating System], v_GS_OPERATING_SYSTEM.CSDVersion0 AS [Service Pack Level], v_GS_SYSTEM_ENCLOSURE_UNIQUE.SerialNumber0 AS [Serial Number], v_GS_SYSTEM_ENCLOSURE_UNIQUE.SMBIOSAssetTag0 AS [Asset Tag], v_GS_COMPUTER_SYSTEM.Manufacturer0 AS [Manufacturer], v_GS_COMPUTER_SYSTEM.Model0 AS [Model], v_GS_X86_PC_MEMORY.TotalPhysicalMemory0 AS [Memory (KBytes)], v_GS_PROCESSOR.NormSpeed0 AS [Processor (GHz)], (Select sum(Size0) from v_GS_LOGICAL_DISK inner join v_FullCollectionMembership on (v_FullCollectionMembership.ResourceID = v_GS_LOGICAL_DISK.ResourceID ) where v_GS_LOGICAL_DISK.ResourceID =v_R_System_Valid.ResourceID and v_FullCollectionMembership.CollectionID = "TRN0001E") As [Disk Space (MB)], (Select sum(v_GS_LOGICAL_DISK.FreeSpace0) from v_GS_LOGICAL_DISK inner join v_FullCollectionMembership on (v_FullCollectionMembership.ResourceID = v_GS_LOGICAL_DISK.ResourceID ) where v_GS_LOGICAL_DISK.ResourceID =v_R_System_Valid.ResourceID and v_FullCollectionMembership.CollectionID = "TRN0001E") As [Free Disk Space (MB)] from v_R_System_Valid inner join v_GS_OPERATING_SYSTEM on (v_GS_OPERATING_SYSTEM.ResourceID = v_R_System_Valid.ResourceID) left join v_GS_SYSTEM_ENCLOSURE_UNIQUE on (v_GS_SYSTEM_ENCLOSURE_UNIQUE.ResourceID = v_R_System_Valid.ResourceID) inner join v_GS_COMPUTER_SYSTEM on (v_GS_COMPUTER_SYSTEM.ResourceID = v_R_System_Valid.ResourceID) inner join v_GS_X86_PC_MEMORY on (v_GS_X86_PC_MEMORY.ResourceID = v_R_System_Valid.ResourceID) inner join v_GS_PROCESSOR on (v_GS_PROCESSOR.ResourceID = v_R_System_Valid.ResourceID) inner join v_FullCollectionMembership on (v_FullCollectionMembership.ResourceID = v_R_System_Valid.ResourceID) left join v_Site on (v_FullCollectionMembership.SiteCode = v_Site.SiteCode) inner join v_GS_LOGICAL_DISK on (v_GS_LOGICAL_DISK.ResourceID = v_R_System_Valid.ResourceID) and v_GS_LOGICAL_DISK.DeviceID0=SUBSTRING(v_GS_OPERATING_SYSTEM.WindowsDirectory0,1,2) left join v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP on (v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP.ResourceID = v_R_System_Valid.ResourceID) Where v_FullCollectionMembership.CollectionID = "TRN0001E" Order by v_R_System_Valid.Netbios_Name0 

All I can think of is that you are doing this in the wizard; in the console--which I personally haven't used in 3+ years. I use SRS. So... going from a REALLY bad, REALLY old memory... isn't there some extra button you click on, about what prompts to have? Did you clear that? that it shouldn't prompt for collectionid anymore?Standardize. Simplify. Automate.

You need single quote ', not a double quote around the collection ID. http://www.enhansoft.com/

As mentioned earlier Replace variable @CollectionID with 'collection ID'. How ever please find the query. select distinct v_R_System_Valid.ResourceID, v_R_System_Valid.Netbios_Name0 AS [Computer Name], v_R_System_Valid.Resource_Domain_OR_Workgr0 AS [Domain/Workgroup], v_Site.SiteName as [SMS Site Name], [Top Console User] = CASE when (v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP.TopConsoleUser0 is NULL or v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP.TopConsoleUser0 = '-1') then 'Unknown' Else v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP.TopConsoleUser0 End, v_GS_OPERATING_SYSTEM.Caption0 AS [Operating System], v_GS_OPERATING_SYSTEM.CSDVersion0 AS [Service Pack Level], v_GS_SYSTEM_ENCLOSURE_UNIQUE.SerialNumber0 AS [Serial Number], v_GS_SYSTEM_ENCLOSURE_UNIQUE.SMBIOSAssetTag0 AS [Asset Tag], v_GS_COMPUTER_SYSTEM.Manufacturer0 AS [Manufacturer], v_GS_COMPUTER_SYSTEM.Model0 AS [Model], v_GS_X86_PC_MEMORY.TotalPhysicalMemory0 AS [Memory (KBytes)], v_GS_PROCESSOR.NormSpeed0 AS [Processor (GHz)], (Select sum(Size0) from v_GS_LOGICAL_DISK inner join v_FullCollectionMembership on (v_FullCollectionMembership.ResourceID = v_GS_LOGICAL_DISK.ResourceID ) where v_GS_LOGICAL_DISK.ResourceID =v_R_System_Valid.ResourceID and v_FullCollectionMembership.CollectionID = 'TRN0001E') As [Disk Space (MB)], (Select sum(v_GS_LOGICAL_DISK.FreeSpace0) from v_GS_LOGICAL_DISK inner join v_FullCollectionMembership on (v_FullCollectionMembership.ResourceID = v_GS_LOGICAL_DISK.ResourceID ) where v_GS_LOGICAL_DISK.ResourceID =v_R_System_Valid.ResourceID and v_FullCollectionMembership.CollectionID = 'TRN0001E') As [Free Disk Space (MB)] from v_R_System_Valid inner join v_GS_OPERATING_SYSTEM on (v_GS_OPERATING_SYSTEM.ResourceID = v_R_System_Valid.ResourceID) left join v_GS_SYSTEM_ENCLOSURE_UNIQUE on (v_GS_SYSTEM_ENCLOSURE_UNIQUE.ResourceID = v_R_System_Valid.ResourceID) inner join v_GS_COMPUTER_SYSTEM on (v_GS_COMPUTER_SYSTEM.ResourceID = v_R_System_Valid.ResourceID) inner join v_GS_X86_PC_MEMORY on (v_GS_X86_PC_MEMORY.ResourceID = v_R_System_Valid.ResourceID) inner join v_GS_PROCESSOR on (v_GS_PROCESSOR.ResourceID = v_R_System_Valid.ResourceID) inner join v_FullCollectionMembership on (v_FullCollectionMembership.ResourceID = v_R_System_Valid.ResourceID) left join v_Site on (v_FullCollectionMembership.SiteCode = v_Site.SiteCode) inner join v_GS_LOGICAL_DISK on (v_GS_LOGICAL_DISK.ResourceID = v_R_System_Valid.ResourceID) and v_GS_LOGICAL_DISK.DeviceID0=SUBSTRING(v_GS_OPERATING_SYSTEM.WindowsDirectory0,1,2) left join v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP on (v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP.ResourceID = v_R_System_Valid.ResourceID) Where v_FullCollectionMembership.CollectionID = 'TRN0001E' Order by v_R_System_Valid.Netbios_Name0

Thank you all for responding and helping me out. I can apply this to other reports and create pertinent dashboards for our sections. Thanks again, Jho.