SOLVED Prompt for collection in SCCM report

[dj3094]

Hello Experts,

I am trying to add prompt for a report we already have. I created a dataset called collection and gave the below query
SELECT
v_Collection_Alias.CollectionID ,v_Collection_Alias.Name
FROM
fn_rbac_Collection(@UserSIDs) v_Collection_Alias
WHERE
v_Collection_Alias.CollectionType = 2

And then I added a parameter called collection and in properties i chose the data set variable.
when I run the report it prompt fro collection, but it is giving all the machines. from online i got to know i have to add more to main data set
my main data set query is
select CASE WHEN lastmpservername LIKE '' then lastmpservername else UPPER(SUBSTRING(lastmpservername, 1, CHARINDEX('.', lastmpservername) -1 )) END as 'Management Point',
count(lastmpservername) as 'Total Clients'
from v_CH_ClientSummary
group by lastmpservername
order by lastmpservername desc

What to add for this to make prompt to work?

Thanks

 

[Garth]

This blog post will help you with that task.
https://www.enhansoft.com/blog/adding-a-prompt-value-to-the-title-of-a-sccm-report

 

[dj3094]

Thanks Garath, I followed that and I am good till adding values to parameter, I am issue at main query

 

[Garth]

did you see the section entitled. This is what you need for you query too.
Main Query – Relevant Section

 

[dj3094]

yes Garath, i am looking at that, As I am very new to SQL query, i am not able to achieve what I need. I understand I need to join collection table but Having issue.
thanks

 

[Garth]

select
    CASE
        WHEN CS.lastmpservername = '' then lastmpservername
        else UPPER(SUBSTRING(CS.lastmpservername, 1, CHARINDEX('.', CS.lastmpservername) -1 ))
    END as 'Management Point',
    count(CS.lastmpservername) as 'Total Clients'
from
    dbo.v_R_System_Valid RV
    join dbo.v_CH_ClientSummary CS on RV.ResourceID = CS.ResourceID
    join dbo.v_FullCollectionMembership FCM on RV.ResourceID = FCM.ResourceID
Where
    FCM.CollectionID = @Coll
group by
    CS.lastmpservername
order by
    CS.lastmpservername desc

 

[dj3094]

Thanks alot Garath,
It looks like working. But I have other data sets as well

SELECT ER.Result,case ER.Result
when 1 then 'Not yet evaluated' when 2 then 'Not applicable' when 3 then 'Evaluation failed'
when 4 then 'Evaluated, remediation failed' when 5 then 'Not evaluated, dependency failed'
when 6 then 'Evaluated, remediation succeeded' when 7 then 'Evaluated,succeeded: All tests passed'
ELSE 'Others'end as 'Client Health Description',Count(*) [Total Clients]
from v_CH_EvalResults ER,v_r_system sys
where er.NetBiosName=sys.Name0 and
datediff(dd,sys.Last_Logon_Timestamp0,getdate())<30
group by er.Result
order by 3

Will it work with 2 where clauses something like this

SELECT ER.Result,case ER.Result
when 1 then 'Not yet evaluated' when 2 then 'Not applicable' when 3 then 'Evaluation failed'
when 4 then 'Evaluated, remediation failed' when 5 then 'Not evaluated, dependency failed'
when 6 then 'Evaluated, remediation succeeded' when 7 then 'Evaluated,succeeded: All tests passed'
ELSE 'Others'end as 'Client Health Description',Count(*) [Total Clients]

from
dbo.v_R_System_Valid RV,v_CH_EvalResults ER,v_r_system sys
join dbo.v_CH_ClientSummary CS on RV.ResourceID = CS.ResourceID
join dbo.v_FullCollectionMembership FCM on RV.ResourceID = FCM.ResourceID
Where
FCM.CollectionID = @Coll
group by
CS.lastmpservername
order by
CS.lastmpservername desc
where er.NetBiosName=sys.Name0 and
datediff(dd,sys.Last_Logon_Timestamp0,getdate())<30
group by er.Result
order by 3

 

[Garth]

You can only have one where clause per query. Adjust you query to make it just one.

 

[dj3094]

Thanks Garath