Help Getting Parameter to work... Works if hardcoded

[cfreeman21]

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

[cfreeman21]

Anyone?

[GarthMJ]

Your queries look good.... So what exactly is the problem?

[cfreeman21]

If I run report with parameters I get blank data, but I hard code them I get data I do not understand why this is happening.

[GarthMJ]

How exactly are you creating your prompts? Exactly what changes are you making to the main query so that it can be used within the SSRS report? As it sits, you will have problems using it for a report.

[cfreeman21]

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

[GarthMJ]

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/

[cfreeman21]

Sent you a Message... Thanks, Garth!!

[GarthMJ]

When I get back to my lab I will look at it... But If I don't get it done by COB tomorrow, It will have to wait until I get back from holidays.