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?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

merowingerCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Server OS

From novice to tech pro — start learning today.