I want to utilize scoping by Organizational Group/View on a Raw SQL report, how can this be done?
If you are not using/editing a Raw SQL report please see this KB (KB 51702 - How do you add Scoping by Organizational Group to reports?)
On a Raw SQL Report look for the code highlighted in RED below. First there should be a DECLARATION of a variable ScopeCollectionGuid (in this example v2_ScopeCollectionGuid to be exact, as well as a section in the WHERE statement selecting ScopeCollectionGuid FROM fnGetTrusteScopeCollectionsByScope utilizing the ScopeCollectionGuid (v2_ScopeCollectionGuid).
DECLARE @v1_TrusteeScope nvarchar(max)
SET @v1_TrusteeScope = N'%TrusteeScope%'
DECLARE @v2_ScopeCollectionGuid uniqueidentifier
SET @v2_ScopeCollectionGuid = '%ScopeCollectionGuid%'
[vri4_Computer].[Guid] AS [_ItemGuid],
[vRM_Computer_Item] AS [vri4_Computer]
([vri4_Computer].[Guid] IN (SELECT [ResourceGuid] FROM [ScopeMembership] WHERE [ScopeCollectionGuid] IN (SELECT [ScopeCollectionGuid] FROM dbo.fnGetTrusteeScopeCollectionsByScope(@v1_TrusteeScope, @v2_ScopeCollectionGuid, 1))))
It is likely if you are reading this article, that the above sections are not present - or not present in their entirety. The easiest way to create these statements is to either copy them from this KB article or to create a new report in the SMP console. Here simply select a default Computer type report and add in Report and Query parameters for the 'Advanced Types > Scope by Organizational Group Parameter'. When you've saved and applied the OG parameters convert the report to a Raw SQL report, this would show you some SQL Query code like the above highlighted sections.Of note is the first part of the highlighted WHERE statement. [vri4_Computer].[Guid] is the guid of resources being scoped. In the above quey vri4_Computer is vRM_Computer_Item (as seen in the FROM statement). You will need to modify this to suit your need. If for example you are trying to create a report off of 'vComputer AS vc' you might need to make it [vComputer].[Guid] or [vc].[guid]. This WILL be environmental, so you may need to attempt this multiple times or ask for assistance if this does not work or seem apparant for you given the above instructions.
It is often seen, that built in Raw SQL reports and especially custom reports written does not include the scoping clauses in the WHERE statement. Again, if this is the case add this in as seen above. If you do find that it is added, but that you do not have an ability to select the organizational view/group to perform scoping on, please make sure that the following two items have been added and set on the report;Under Query Parameters, make sure Scope By Organizational Group Parameter is present. If this is not so, add it by clicking Add > Advanced Types > Scope By Organizational Group Parameter. Under Report Parameters, make sure Scope By Organizational Group Parameter is present. If this is not so, add it by clicking Add > Advanced Types > Scope By Organizational Grup Parameter.
Having done all this save and test your report. It should now allow you to select an OG/OV as a 'Resource Selection' option within the report.Please note, that the scoping SQL code in this KB includes that nesseccary for also performing scoping per security role. This is the TrusteeScope seen above.
Rate this Article