Advanced report for advertisement Summary

To build this report:

1. Create a new report and give it a good name.
2. Edit the query statement, parse the following code:

SELECT
AdvState.AdvertisementID, AdvName.AdvertisementName, AdvState.LastStateName, AdvState.number AS 'Number of clients with this Status',
ROUND(100.0*AdvState.number/SUM(CASE AdvTotal.LastState WHEN 0 THEN 0 ELSE 1 END),1) AS 'Percent with this Status',
SUM(CASE AdvTotal.LastState WHEN 0 THEN 0 ELSE 1 END) AS 'Total # Clients with Accepted Status'

FROM v_ClientAdvertisementStatus AdvTotal, v_Advertisement AdvName,
(SELECT a.AdvertisementID,a.LastStateName, count(*) as 'number'
FROM v_ClientAdvertisementStatus a, v_AdvertisementStatusInformation b
WHERE a.LastStatusMessageID = b.MessageID
AND b.MessageStateName = @status
GROUP BY a.AdvertisementID, a.LastStateName
) AS AdvState

WHERE AdvState.AdvertisementID = AdvTotal.AdvertisementID
AND AdvState.AdvertisementID = AdvName.AdvertisementID

GROUP BY AdvState.AdvertisementID, AdvName.AdvertisementName, AdvState.LastStateName, AdvState.number
ORDER BY AdvName.AdvertisementName

3. Click on the "Prompts Button"
4. Create a new prompt with the following Name: "status"
5. Give it a prompt text
6. Provide the following sql statement to the prompt:

SELECT DISTINCT
a.MessageStateName

FROM
v_AdvertisementStatusInformation a,
v_ClientAdvertisementStatus b

WHERE a.MessageID = b.LastStatusMessageID


ORDER BY MessageStateName

7. Press Ok in all windows
8. Now rightclick on your report and choose properties
9. Choose the "Links" tab
10. Choose link Type: "link to another report"
11. Choose Report: "Software Distribution - Advertisement Status - All system resources for a specific advertisement in a specific state"
StateName should be column 3
AdvertID should be column 1


12. Press OK