question

DoJu-3609 avatar image
0 Votes"
DoJu-3609 asked DoJu-3609 commented

Removing parameters from SSRS Report Builder Datasets

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 @Path 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 @Path
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
@Path 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'



sql-server-reporting-services
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

LukasYu-msft avatar image
0 Votes"
LukasYu-msft answered DoJu-3609 commented

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.

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thanks LukasYu-msft,

In regard to the Report Parameter Properties, I want to set the "Group" parameter to "Windows Server Computer Group" but everytime I try, it says "missing group parameter".

Under General / Select parameter visiblity / Hidden

Under Default Values / Specify Values /Add / Expression

Category:
Parameters / Group / =Parameters!Group.Value

OR

Datasets / SelectGroup / values First(Fullname), First (DisplayName)

0 Votes 0 ·