Video Screencast Help

New SQL Report - default Parameterised Query given

Created: 12 Sep 2012 • Updated: 17 Sep 2012 | 2 comments
This issue has been solved. See solution.

Ok so im liking the 7.1 console a lot.. in 6 when i created collections you had to use the default sql they give you and add your sql to it to create dynamic collections... i see in 7.1 this is not the case anymore.. really like that..

now back on track...

I have been creating reports in our dev 7.1 to get things rolling. I deleted all the sql code it comes with and it works so my question is...

any reason to use the default sql when you click new sql report?

i delete all of this and put my own in.. example...


DECLARE @v1_TrusteeScope nvarchar(max)
   SET @v1_TrusteeScope = N'%TrusteeScope%'
   [vri2_Resource].[Guid] AS [_ItemGuid],
   [vRM_Resource_Item] AS [vri2_Resource]
      ([vri2_Resource].[Guid] IN (SELECT [ResourceGuid] FROM [ScopeMembership] WHERE [ScopeCollectionGuid] IN (SELECT [ScopeCollectionGuid] FROM dbo.fnGetTrusteeScopeCollections(@v1_TrusteeScope))))
Paste from sql management studio (CompName is a parameter and works)
Select t1.[_ResourceGuid] as _ItemGuid, t1.[Name], t0.[DisplayName], t0.[DisplayVersion], t0.[Publisher]
From [Inv_AddRemoveProgram] t0 INNER JOIN
[Inv_AeX_AC_Identification] t1 on t0.[_ResourceGuid] = t1.[_ResourceGuid]
where t0.[DisplayVersion] is not null and t1.[Name] like '%CompName%'
order by t0.[DisplayName]

Comments 2 CommentsJump to latest comment

SaschaH's picture

The default is in there mainly for security reasons. It scopes report results to resources the user running the report has permission to see, else reports will show everything(I think they run with application credentials) This only gets important in environments with dedicated secured organisational units which need to be separated from the others.

Bechtle – your strong IT partner. Today and tomorrow

If that seems to help, please "Mark as Solution"

TeleFragger's picture

ok so if i wanted security involved stuff i would keep it and place my sql inside of it.. yeah not something we need to worry about..


Did we help you? Please Mark As Solution those posts which resolve your problem,