I am trying to write a query that generates an html report to be emailed and one of the columns is a hyperlink to our report server that we can click on from the email and it will open the report in report browser. It needs to pass the row value as a parameter to the report:
SELECT @xml = '
Dealer Loss Ratio Report
The following dealers (100+ Warranties Sold) have a current Loss Ratio greater than 35%
(SELECT 'Dealer' th
,'Warranty Count' th
,'Total Sales' th
,'Claims Count' th
,'Total Claims' th
,'Avg Claim' th
,'Loss Ratio' th
FOR XML RAW('tr'), ELEMENTS) +
(SELECT CAST('' AS XML) td
,FORMAT(SUM(RevenueRemittedAmount), 'C') td
,FORMAT(SUM(ClaimsAmount), 'C') td
,FORMAT(SUM(ClaimsAmount) / SUM(ClaimsCount), 'C') td
,FORMAT(SUM(ClaimsAmount) / SUM(RevenueRemittedAmount), 'P') td
WHERE WarrantyStatus IN ('Active')
AND RevenueRemittedAmount <> 0
GROUP BY Dealer
HAVING SUM(warcount) >= 100 AND (SUM(ClaimsAmount) / SUM(RevenueRemittedAmount)) >= .35
ORDER BY 7 DESC
FOR XML RAW('tr'), ELEMENTS) + '</table>'
I am getting the error illegal name character. It's coming from the Dealer column because I can hardcode a name and it works fine.
Not sure what I am missing.
Thanks for any and all suggestions.