I am working on several SQL queries that will return a list of referenced collections names of a top level collection. I am able to query the top level collection name along with the refrenced collections but I can't seem to seperate the two. I'm wanting to import this into Report Builder and have sub-selects. For instance, I have 8 top level collections. One of the collection names is 'XXX WRK_NA' it has 24 referenced collections. I want to be able to click the '+' for the subselect and it would reveal the 24 referenced collections in a list. I have included two queries below:
Top Level Collection Query:
SELECT
DISTINCT
COUNT(v_R_System.Operating_System_Name_and0)as 'Windows 7',v_Collection.Name,(v_Collection.MemberCount) as 'Number of Workstations'
FROM
v_R_System INNER JOIN
v_FullCollectionMembership_Valid
ON v_R_System.ResourceID = v_FullCollectionMembership_Valid.ResourceID INNER JOIN
v_Collection
ON v_FullCollectionMembership_Valid.CollectionID = v_Collection.CollectionID
WHERE
(v_Collection.Name LIKE 'XXXX WRK[_]%')AND (v_R_System.Operating_System_Name_and0 like 'Microsoft Windows NT Workstation 6.1')
GROUP
BY v_Collection.Name, v_Collection.MemberCount, v_R_System.Operating_System_Name_and0
Subselect Query:
SELECT
DISTINCT
COUNT(v_R_System.Operating_System_Name_and0)as 'Windows 7',(v_Collection.Name) AS 'Site',(v_Collection.MemberCount) as 'Number of Workstations'
FROM
v_R_System INNER JOIN
v_FullCollectionMembership_Valid
ON v_R_System.ResourceID = v_FullCollectionMembership_Valid.ResourceID INNER JOIN
v_Collection
ON v_FullCollectionMembership_Valid.CollectionID = v_Collection.CollectionID
WHERE
(v_Collection.Name LIKE 'XXXX WRK%')AND(v_Collection.Name NOT LIKE 'XXXX WRK[_]%')
AND(v_R_System.Operating_System_Name_and0 like 'Microsoft Windows NT Workstation 6.1')
GROUP
BY v_Collection.Name, v_Collection.MemberCount, v_R_System.Operating_System_Name_and0
Both Queries work, but I'm trying to associate the list of queried collections from the subselect with each collection names in the top level collection.
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.
I am working on several SQL queries that will return a list of referenced collections names of a top level collection. I am able to query the top level collection name along with the refrenced collections but I can't seem to seperate the two. I'm wanting to import this into Report Builder and have sub-selects. For instance, I have 8 top level collections. One of the collection names is 'XXX WRK_NA' it has 24 referenced collections. I want to be able to click the '+' for the subselect and it would reveal the 24 referenced collections in a list. I have included two queries below:
Top Level Collection Query:
SELECT
DISTINCT
COUNT(v_R_System.Operating_System_Name_and0)as 'Windows 7',v_Collection.Name,(v_Collection.MemberCount) as 'Number of Workstations'
FROM
v_R_System INNER JOIN
v_FullCollectionMembership_Valid
ON v_R_System.ResourceID = v_FullCollectionMembership_Valid.ResourceID INNER JOIN
v_Collection
ON v_FullCollectionMembership_Valid.CollectionID = v_Collection.CollectionID
WHERE
(v_Collection.Name LIKE 'XXXX WRK[_]%')AND (v_R_System.Operating_System_Name_and0 like 'Microsoft Windows NT Workstation 6.1')
GROUP
BY v_Collection.Name, v_Collection.MemberCount, v_R_System.Operating_System_Name_and0
Subselect Query:
SELECT
DISTINCT
COUNT(v_R_System.Operating_System_Name_and0)as 'Windows 7',(v_Collection.Name) AS 'Site',(v_Collection.MemberCount) as 'Number of Workstations'
FROM
v_R_System INNER JOIN
v_FullCollectionMembership_Valid
ON v_R_System.ResourceID = v_FullCollectionMembership_Valid.ResourceID INNER JOIN
v_Collection
ON v_FullCollectionMembership_Valid.CollectionID = v_Collection.CollectionID
WHERE
(v_Collection.Name LIKE 'XXXX WRK%')AND(v_Collection.Name NOT LIKE 'XXXX WRK[_]%')
AND(v_R_System.Operating_System_Name_and0 like 'Microsoft Windows NT Workstation 6.1')
GROUP
BY v_Collection.Name, v_Collection.MemberCount, v_R_System.Operating_System_Name_and0
Both Queries work, but I'm trying to associate the list of queried collections from the subselect with each collection names in the top level collection.
Share this post
Link to post
Share on other sites