in Config mgr 2007 i had a query that would pull basic compliance ratings for all the sub collections of a single parent collection, displaying the breakdown in a per site bar graph percentage compliance report

With the migration to CM12 and it not having parent collections this functionality is not available

here is the old query i would appreciate if someone could take a look at this and provide any input they may have:
also note it doesnt seem to like the v_updateliststatus_live for some reason either

select
v_Collection.Name AS [Site],
Status=sn.StateName,
NumberOfComputers=count(*)
from v_UpdateListStatus_Live cs left join v_StateNames sn on sn.TopicType=300 and sn.StateID=isnull(cs.Status, 0) left join v_AuthListInfo ai ON cs.CI_ID = ai.CI_ID inner join v_R_System ON cs.ResourceID = v_R_System.ResourceID INNER JOIN v_FullCollectionMembership ON v_R_System.ResourceID = v_FullCollectionMembership.ResourceID INNER JOIN v_Collection ON v_Collection.CollectionID = v_FullCollectionMembership.CollectionID INNER JOIN v_CollectToSubCollect ON v_CollectToSubCollect.subCollectionID = v_Collection.CollectionID WHERE parentCollectionID = TTC00074 AND Active0 = 1 AND ai.Title = @UpdateListTitle
group by sn.StateName, v_Collection.Name
ORDER BY [Site], sn.StateName

thanks
rob

Yes, I know this is an old post, but Im trying to clean them up. Did you solve this problem, if so what was the solution?

This query will not work because that SQL view doesnt exist in CM12.