Running specialized CSV in SSRS with different device settings.

So this will be useful if you want to change the default settings for a single report, and not all reports, that are exported to CSV. An example, making semi-colon or tab delimited values.

A good read on bypassing the "CSV Device Information Settings" used on the reporting server.

http://blogs.infosupport.com/modify-reporting-services-export-to-csv-behavior/

Here are a list of the settings that can be changed in the url:
https://msdn.microsoft.com/en-us/library/ms155365%28v=sql.105%29.aspx


So instead of using the standard user-friendly interface, you will be using the file system interface, found in /ReportServer/Pages/ReportViewer.aspx?

and adding on your commands, just like you would do with parameters, to override the settings:

 &rs:Command=Render&rs:Format=CSV&rc:ExcelMode=true&rc:Qualifier=%22&rc:NoHeader=false&rc:FieldDelimiter=, 

Note:
This however, still leaves the problem with the qualifier. I was told that a csv file being sent to a third-party had to have quotation marks around each value (e.g. "First Name"). This doesn't seem possible in SSRS. The qualifier will only put quotes around a field if there was already a quote in the value (e.g. William "Big Bill" Andrus => "William "Big Bill" Andrus") which would also give incorrect usage of quotes if you hard code them (e.g. "First Name" => ""First Name""). And, there seems to be no way to force the qualifier to display. 

Search for fields on ReportServer

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73

/* * Get a list of reports in a specific path/folder */ ;WITH ItemContentBinaries AS ( SELECT ItemID ,Name ,[Type] ,CASE [Type] WHEN 2 THEN 'Report' WHEN 5 THEN 'Data Source' WHEN 7 THEN 'Report Part' WHEN 8 THEN 'Shared Dataset' ELSE 'Other' END AS TypeDescription ,CONVERT(varbinary(max),Content) AS Content FROM ReportServer.dbo.[Catalog] with (nolock) WHERE [PATH] LIKE '/Department Level/%' ) ---ADD WORDS, USED IN FIELDS, TO SEARCH FOR ,WordSearchCTE AS ( SELECT 'SSN' AS Word UNION ALL SELECT 'AGE' UNION ALL SELECT 'DOB' UNION ALL SELECT 'EPMP' UNION ALL SELECT 'PAY' UNION ALL SELECT 'COMP' ) --This CTE strips off the BOM if it exists... ,ItemContentNoBOM AS ( SELECT ItemID ,Name ,[Type] ,TypeDescription ,CASE WHEN LEFT(Content,3) = 0xEFBBBF THEN CONVERT(varbinary(max),SUBSTRING(Content,4,LEN(Content))) ELSE Content END AS Content FROM ItemContentBinaries ) --The old outer query is now a CTE to get the content in its xml form only... ,ItemContentXML AS ( SELECT ItemID,Name,[Type],TypeDescription ,CONVERT(xml,Content) AS ContentXML FROM ItemContentNoBOM ) --now use the XML data type to extract the queries, and their command types and text.... --and only select from those that contain the search terms SELECT * FROM ( SELECT ItemID,Name,[Type],TypeDescription,ContentXML ,ISNULL(Query.value('(./*:CommandType/text())[1]','nvarchar(1024)'),'Query') AS CommandType ,Query.value('(./*:CommandText/text())[1]','nvarchar(max)') AS CommandText FROM ItemContentXML --Get all the Query elements (The "*:" ignores any xml namespaces) CROSS APPLY ItemContentXML.ContentXML.nodes('//*:Query') Queries(Query) )x JOIN ( select '%'+Word+'%' as pattern from WordSearchCTE ) w on CommandText like w.pattern

Pull what reports were ran and by whom.

A very basic query to look at the ReportServer database to pull what reports there are, who has ran it, and how many times.

Code Snippet

1. SELECT
2. c.NAME,
3. el.UserName,
4. el.LATEST_RUN_DATE,
5. el.NUM_TIMES_RAN,
6. c.[path]
7. FROM
8. DBO.[CATALOG] AS c
9. LEFT JOIN
10. (
11.     SELECT
12.     EL.REPORTID
13.     ,EL.UserName
14.     ,COUNT(EL.TIMESTART) NUM_TIMES_RAN
15.     ,MAX(EL.TIMESTART) AS LATEST_RUN_DATE
16.     FROM
17.     dbo.EXECUTIONLOG AS el
18.     GROUP BY
19.     el.REPORTID, EL.UserName
20. )el on el.ReportID = c.ItemID
21. WHERE
22. C.[TYPE] = 2
23. ORDER BY c.NAME, el.UserName