Jump to content


  • 0
cfreeman21

Help Getting Parameter to work... Works if hardcoded

Question

If you put in the Name for Software Update Group and the Collection ID the Main query works I need help getting the parameter to work. Thanks!

Main Query:

declare @SUG VARCHAR(50);
declare @COLID VARCHAR(8);
declare @CI_ID int;
select @CI_ID = CI_ID
from CI_ConfigurationItems
where CIType_ID=9 and CI_UniqueID=(select CI_UniqueID as AuthListID from v_AuthListInfo
Where Title=@SUG)
select Status=sn.StateName, StatusNumber=(CASE WHEN StateName='Compliant' THEN 1 WHEN StateName='Non-Compliant' THEN 3 ELSE 2 END), cs.NumberOfComputers, PercentOfComputers=convert(float, isnull(cs.NumberOfComputers, 0)*100.00) / isnull(nullif(cs.NumTotal, 0), 1)
  from (select CI_ID, NumTotal, [0]=NumUnknown, [1]=NumPresent+NumNotApplicable, [2]=NumMissing
   from v_UpdateSummaryPerCollection
      where CI_ID=@CI_ID and CollectionID=@COLID
    ) cnt
    unpivot (NumberOfComputers for [Status] in ([0], [1], [2])) cs
    left join v_StateNames sn on sn.TopicType=300 and sn.StateID=cs.Status
  where cs.NumberOfComputers>0
  order by StatusNumber

Query for SUG Paramter:

SELECT     CI_UniqueID, Title
FROM         v_AuthListInfo 
WHERE     Title LIKE '%Compliance%' OR Title LIKE '%Software Updates%'
ORDER BY Title

Query for COLID Parameter:

SELECT DISTINCT c.CollectionID,
                c.CollectionName
FROM v_UpdateSummaryPerCollection c
WHERE (CollectionName LIKE '%Patching Group%' OR
c.CollectionName LIKE '%Compliance%' OR
c.CollectionName LIKE '%Server Patching%' OR
c.CollectionName LIKE '%Approved Patching%')
ORDER BY c.CollectionName

Share this post


Link to post
Share on other sites

8 answers to this question

Recommended Posts

  • 0

Hard Coded main Query:

DECLARE @SUG varchar(50);
DECLARE @COLID varchar(8);
DECLARE @CI_ID int;
SET @SUG = 'Software Updates | Servers | 2016'
SET @COLID = 'A1200602'
SELECT
  @CI_ID = CI_ID
FROM CI_ConfigurationItems
WHERE CIType_ID = 9
AND CI_UniqueID = (SELECT
  CI_UniqueID AS AuthListID
FROM v_AuthListInfo
WHERE Title = @SUG)
SELECT
  Status = sn.StateName,
  StatusNumber = (CASE
    WHEN StateName = 'Compliant' THEN 1
    WHEN StateName = 'Non-Compliant' THEN 3
    ELSE 2
  END),
  cs.NumberOfComputers,
  PercentOfComputers = CONVERT(float, ISNULL(cs.NumberOfComputers, 0) * 100.00) / ISNULL(NULLIF(cs.NumTotal, 0), 1)
FROM (SELECT
  CI_ID,
  NumTotal,
  [0] = NumUnknown,
  [1] = NumPresent + NumNotApplicable,
  [2] = NumMissing
FROM v_UpdateSummaryPerCollection
WHERE CI_ID = @CI_ID
AND CollectionID = @COLID) cnt
UNPIVOT (NumberOfComputers FOR [Status] IN ([0], [1], [2])) cs
LEFT JOIN v_StateNames sn
  ON sn.TopicType = 300
  AND sn.StateID = cs.Status
WHERE cs.NumberOfComputers > 0
ORDER BY StatusNumber

Both Parameters are just Pulling Available Values Get Values from a query

Share this post


Link to post
Share on other sites

  • 0

I hate to say it but this doesn't answer my questions.

 

It will be faster for me to fix it that keep bounce back and forth. Send me your RDL. I need to know what version of SSRS you have and what you are using to edit the RDL.

 

As an aside and completely self promotion. I wrote a book on reporting that will walk you though all the steps to create great reports. https://www.amazon.com/System-Configuration-Manager-Reporting-Unleashed/dp/0672337789/

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Answer this question...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...


×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.