How do you recreate the "All Advertisement" report? It appears to have been deleted on our SMS Server

I believe I need the exact SQL statements to choose when I'm in "properties"; "Edit SQL statement".  If you look at the attachment we recreated the 'shell" so to speak but it doesn't do what we nned. As far as I know this deleted report was an "out of the box" standard reporting object.  We found it handy and would like to have it back.
SMS-All-Advertisement-Report.pdf
ZAnnAsked:
Who is Participating?
 
merowingerConnect With a Mentor Commented:
This is the "All Advertisement" Report from a SCCM Server, but it should be the same
SELECT	v_Advertisement.AdvertisementName,
	v_Advertisement.Comment, 
	v_Package.Name AS C062,
	ProgramName,
	v_Advertisement.SourceSite,
	CASE WHEN AssignedScheduleEnabled != 0 or
                                      (AdvertFlags & 0x720) != 0
		THEN '*'
		ELSE ''
	END AS C063,	
	v_Collection.Name as C064, 
	CASE IncludeSubCollection
		WHEN 1 THEN '*'
		WHEN 0 THEN ''
	END AS C065,
	AdvertisementID
FROM v_Advertisement 
INNER JOIN v_Package ON v_Advertisement.PackageID = v_Package.PackageID
INNER JOIN v_Collection ON v_Advertisement.CollectionID = v_Collection.CollectionID
ORDER BY v_Advertisement.AdvertisementName

Open in new window

0
 
ZAnnAuthor Commented:
I'll give this a try and let you know how it works out.
0
 
ZAnnAuthor Commented:
That worked, thank you.  Copy and pasted the above into the SQL and adjusted for our collections then had to go to "link tab" and link it to another report for it to display all of the collections we have.  Points are yours!
0
All Courses

From novice to tech pro — start learning today.