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%
<table>' +
(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
,SUM(warcount) td
,FORMAT(SUM(RevenueRemittedAmount), 'C') td
,SUM(ClaimsCount) td
,FORMAT(SUM(ClaimsAmount), 'C') td
,FORMAT(SUM(ClaimsAmount) / SUM(ClaimsCount), 'C') td
,FORMAT(SUM(ClaimsAmount) / SUM(RevenueRemittedAmount), 'P') td
FROM[GlobalWarrantyReports].[dbo].[WarrantyDetail]
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.
Do any of your dealer names contain an ampersand (&)? That might do it.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
August 19, 2019 at 1:53 pm
I think you're right.
That column has a ton of special characters. Might just abandon this and put a link to the main report in the email that they can drill through to.
Thanks.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply