Data-driven Subscription - File Share

Question

Hi all

I have written a query that specifies a different file name for each report produced by a data-driven subscription, however the report production fails for each row of the query where a comma is present.

Please see query below, the comma can appear in the c.CentreName field.

Is it that a SSRS file share just cannot accept commas in the file name, or is there a way to avoid the production error whilst keeping the commas in the file name?

Thanks

James

SELECT
	c.CentreName, 
	c.CentreID, 
	('Retailer Data Listing for ' + c.ShortCentreName) AS EMailSubject,
	('RetailerDataListing-' + c.ShortCentreName + '-Wk47Yr2012') AS FileName
FROM tblCentres c
WHERE c.RetailerDataListing = 1
ORDER BY c.CentreName

Answer 1

Please post the error message/details.

---

Best Regards,
Dattatrey Sindol (Datta)

---

| Blog  | Facebook  | Twitter  |
"Please mark the post as answered if it solves your problem."

---

Answer 2

Hi James,

The issue occurs because the comma (",") is a invalid character in the file name when we create a subscription using the Windows File Share delivery extension. Please note that we must avoid using the following characters in the file name:

/\?*:<>|+,[]"&

Regarding this issue, you can use the REPLACE function to replace the comma with a valid character. For example, you can modify the query as follows to replace the comma with a dash:

SELECT
 c.CentreName, 
 c.CentreID, 
 ('Retailer Data Listing for ' + c.ShortCentreName) AS EMailSubject,
 ('RetailerDataListing-' + Replace(c.ShortCentreName, ',', '-') + '-Wk47Yr2012') AS FileName
FROM tblCentres c
WHERE c.RetailerDataListing = 1
ORDER BY c.CentreName

Regards,

---

Mike Yin
TechNet Community Support

Answer 3

Thank you so much Mike for providing a listing of all the disallowed charaters in one location.  Users records contain invalid characters for a subscription I'm setting up.  This will make it very easy now to strip these characters from the filename.