Removing parameters from SSRS Report Builder Datasets

Question

Hi Everyone,

I have a SSRS SCOM Disk Report and don't want it to prompt for the Computer Group or Free Space.

Instead I just want to target the "Windows Server Computer Group" and Free space Limit of 10%

How can this be achieved?

Thanks.

Dataset SelectGroup

SELECT
FullName
,DisplayName
FROM
OperationsManagerDW.dbo.vManagedEntity vme with (nolock)
WHERE
Path is null
and FullName NOT like '%:%'
--and (DisplayName like '%Computer%' OR DisplayName like '%Instances%')
ORDER BY
DisplayName

Dataset DataWorehouseMain

DECLARE
@MERIOBJ nvarchar(255)
,@TodayBegin DateTime
,@TodayNow DateTime

DECLARE @ManagedEntity TABLE(ManagedEntityRowId int PRIMARY KEY (ManagedEntityRowId))
DECLARE @CounterResults TABLE(RowID INT IDENTITY(1,1) PRIMARY KEY,[DateTime] datetime,AverageValue int,Path varchar(255),InstanceName varchar(255),ObjectName nvarchar(255),CounterName nvarchar(255), PerformanceRuleInstanceRowId INT, ManagedEntityRowId INT )
DECLARE @CounterIDs TABLE(ID INT, ObjectName NVARCHAR(60), CounterName NVARCHAR(60))
DECLARE @mutia keyza TABLE (PathName VARCHAR(255) PRIMARY KEY)

SELECT
@TodayBegin = CONVERT(DATETIME, CONVERT(VARCHAR(50),GetDate(), 101))
,@TodayNow = GETDATE()
,@FullName = ISNULL(@FullName,'Microsoft.SystemCenter.AllComputersGroup')

SELECT
@MERIOBJ =
'<Data><Objects><Object Use="Containment">'
+ CAST(ManagedEntityRowId AS VARCHAR(5))
+ '</Object></Objects></Data>'
FROM
OperationsManagerDW.dbo.vManagedEntity
WHERE
FullName = @FullName

INSERT INTO @CounterIDs
SELECT
RuleRowId
,ObjectName
,CounterName
FROM
OperationsManagerDW.dbo.vPerformanceRule vPR
WHERE
objectName in ('LogicalDisk','Logical Disk')
AND (
(
counterName = '% Free Space'
)
or
(
counterName = 'Free Megabytes'
)
)

INSERT INTO @ManagedEntity
EXEC [OperationsManagerDW].[dbo].[Microsoft_SystemCenter_DataWarehouse_Report_Library_ReportObjectListParse]
@TodayBegin
,@TodayNow
,@MERIOBJ

INSERT INTO @mutia keyza
SELECT DISTINCT
vme.Path
FROM
OperationsManagerDW.dbo.vManagedEntity vme
INNER JOIN @ManagedEntity ME ON
vme.ManagedEntityRowId = ME.ManagedEntityRowId
WHERE vme.Path NOT LIKE '%;%'

Insert Into @CounterResults
SELECT
max(vPerf.DateTime) as DateTime
,max(vPerf.SampleValue) as AverageValue
,vME.Path
,vPRI.InstanceName
,C.ObjectName
,C.CounterName
,vperf.PerformanceRuleInstanceRowId
,vperf.ManagedEntityRowId
FROM
@mutia keyza P
INNER JOIN OperationsManagerDW.dbo.vManagedEntity vME
ON vME.Path = P.PathName
INNER JOIN OperationsManagerDW.Perf.vPerfRaw AS vPerf
ON vPerf.ManagedEntityRowId = vME.ManagedEntityRowId
INNER JOIN OperationsManagerDW.dbo.vPerformanceRuleInstance vPRI
ON vPRI.PerformanceRuleInstanceRowId = vPerf.PerformanceRuleInstanceRowId
INNER JOIN @CounterIDs C
ON vPRI.RuleRowId = C.ID
GROUP BY
vME.Path
,vPRI.InstanceName
,C.ObjectName
,C.CounterName
,vperf.PerformanceRuleInstanceRowId
,vperf.ManagedEntityRowId
ORDER
BY DateTime

UPDATE cr
SET
AverageValue = vPerf.SampleValue
FROM
@CounterResults cr
INNER JOIN OperationsManagerDW.Perf.vPerfRaw AS vPerf
ON vPerf.ManagedEntityRowId = cr.ManagedEntityRowId
AND vPerf.PerformanceRuleInstanceRowId = cr.PerformanceRuleInstanceRowId
AND vPerf.DateTime = cr.DateTime

SELECT
sp.Path as Computer
,sp.InstanceName as LogicalDisk
,sp.AverageValue FreeMegabytes
,pe.AverageValue ProcFreeSpace
,sp.DateTime
FROM
@CounterResults sp
INNER JOIN @CounterResults pe
ON sp.Path = pe.Path
AND sp.InstanceName = pe.InstanceName
AND sp.CounterName = 'Free Megabytes'
AND pe.CounterName = '% Free Space'

Answer 1

Not sure if I understand you correctly , if you do want to have the parameter. You could remove them in your report design and use the value you want.

Or you could hide the parameter, then set the default value for the parameter as "Windows Server Computer Group" and Free space Limit of 10%, this way the report will also not prompt for parameter vaules.