Andrus Development: 2015

Wednesday, August 05, 2015

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.

Thursday, June 11, 2015

Mocking a user

After lots of searching and testing, I finally ended up with piece of code to mock a user for unit testing.

            this.Principal = new Mock<IPrincipal>();

            List<Claim> claims = new List<Claim>{
                new Claim("http://schemas.xmlsoap.org/ws/2005/05/identity/claims/name", username), 
                new Claim("http://schemas.xmlsoap.org/ws/2005/05/identity/claims/nameidentifier", userid)
            };

            var fakeIdentity = new GenericIdentity(username);
            //fakeIdentity.RemoveClaim(new Claim("http://schemas.xmlsoap.org/ws/2005/05/identity/claims/name", ""));
            fakeIdentity.AddClaims(claims);

            var fakePrincipal = new GenericPrincipal(fakeIdentity, roles);
            Principal.Setup(n => n.Identity).Returns(fakeIdentity);

            Http.Setup(x => x.User).Returns(fakePrincipal);
            Http.Setup(x => x.User.Identity.Name).Returns(username);
            Http.Setup(x => x.User.Identity).Returns(fakeIdentity);

Tuesday, June 09, 2015

Find a field in SQL Server database

Sometimes you need to find a field and don't know what table or view it might be coming from. A very useful script to pull out:

SELECT b.name, c.name
FROM sys.schemas a              
JOIN sys.all_objects b ON b.schema_id = a.schema_id 
JOIN sys.columns c ON c.object_id = b.object_id 
WHERE c.NAME LIKE '%Find Field%'

Search for fields on ReportServer

/*
 * 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

Andrus Development

About Me