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. 

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

Query to help create RDL

Usually, I would use the SSRS wizard to quickly create my table with all my fields. In this case I have to add more fields onto an existing report that has my custom formatting of each column (eg. showing/hiding columns).

So for a quick solution, I developed this ad hoc query that I can then use to pull the fields I would need to add from a view into the report. This ends up being a lot of copy & paste actions into the xml (view code) of the rdl.

 

CREATE PROCEDURE [hrr].[usp_RDL_ColumnField_XML]

(

       @view varchar(50)

)

AS

BEGIN

       -- SET NOCOUNT ON added to prevent extra result sets from

       -- interfering with SELECT statements.

       SET NOCOUNT ON;

 

       --DECLARE @view varchar(50)

       --SET @view = 'V_EVALUATIONS'

      

--COPY & PASTE INTO DATASET TO ADD MORE FIELDS

SELECT

' + c.NAME  + '">

       ' + c.NAME  + '

       ' +

       CASE t.NAME

       WHEN 'varchar' THEN 'System.String'

       WHEN 'int' THEN 'System.Int32'

       ELSE 'true'

       END +'

' as 'COPY & PASTE INTO DATASET TO ADD MORE FIELDS'

FROM sys.schemas a                                                                                           

INNER JOIN sys.VIEWS b ON a.schema_id = b.schema_id    AND a.NAME = 'hrr'  

INNER JOIN sys.columns c ON c.object_id = b.object_id 

INNER JOIN sys.types t ON c.system_type_id = t.system_type_id

WHERE c.NAME <> 'DPSID'

AND b.NAME = @view

 

---COPY & PASTE INTO COLUMN SECTION  --

SELECT

'

       1.5in

' as 'COPY & PASTE INTO COLUMN SECTION  -- '

FROM sys.schemas a                                                                                           

INNER JOIN sys.VIEWS b ON a.schema_id = b.schema_id    AND a.NAME = 'hrr'  

INNER JOIN sys.columns c ON c.object_id = b.object_id 

WHERE c.NAME <> 'DPSID'

AND b.NAME = @view

 

 

---COPY & PASTE INTO FIRST TABLIX CELLS HEADER --

SELECT

'

      

              + c.NAME + '">

                     true

                     true

                    

                          

                                 

                                        

                                                ' + REPLACE(c.NAME,'_',' ') + '

                                               

                                        

                                 

                                 

                          

                    

            Textbox' + c.NAME + '

           

                          

                           SteelBlue

                           2pt

                           2pt

                           2pt

                           2pt

                    

             

      

' as 'COPY & PASTE INTO FIRST TABLIX CELLS HEADER -- '

FROM sys.schemas a                                                                                           

INNER JOIN sys.VIEWS b ON a.schema_id = b.schema_id    AND a.NAME = 'hrr'  

INNER JOIN sys.columns c ON c.object_id = b.object_id 

WHERE c.NAME <> 'DPSID'

AND b.NAME = @view

ORDER BY c.NAME

 

---COPY & PASTE INTO FIRST TABLIX CELLS Rows --

SELECT

'

      

              +c.NAME+'">

                     true

                     true

                    

                          

                                 

                                        

                                                =Fields!'+ c.NAME + '.Value

                                               

                                        

                                 

                                 

                          

                           2pt

                           2pt

                           2pt

                           2pt

                    

             

      

' AS 'COPY & PASTE INTO FIRST TABLIX CELLS Rows -- '

FROM sys.schemas a                                                                                           

INNER JOIN sys.VIEWS b ON a.schema_id = b.schema_id    AND a.NAME = 'hrr'  

INNER JOIN sys.columns c ON c.object_id = b.object_id 

WHERE c.NAME <> 'DPSID'

AND b.NAME = @view

ORDER BY c.NAME

 

 

--- COPY & PASTE INTO Tablix Column Hierarchy section --

SELECT

'

      

              =IIF(INSTR(JOIN(Parameters!fields.Value,","),"' + b.NAME + '.' + c.NAME+'") > 0,FALSE,TRUE)

      

' AS 'COPY & PASTE INTO Tablix Column Hierarchy section -- '

FROM sys.schemas a                                                                                           

INNER JOIN sys.VIEWS b ON a.schema_id = b.schema_id    AND a.NAME = 'hrr'  

INNER JOIN sys.columns c ON c.object_id = b.object_id 

WHERE c.NAME <> 'DPSID'

AND b.NAME = @view

ORDER BY c.NAME

END

 

 

GO

 

An error occured while attempting to start the report preview worker process.

"An error occured while attempting to start the report preview worker process."

Ran into this problem when using runas with SSDT. As a consultant, the runas is useful for connecting to a customer's domain and sending the necessary credentials without setting the laptop to be on the customer's domain.

The workaround, is to instead, right-click the report and select "run".

Source:
http://social.msdn.microsoft.com/Forums/sqlserver/en-US/f717c592-5022-43e3-9872-c10c1cf7f653/ssdt-2012-report-preview-not-working?forum=sqlreportingservices&prof=required

SSRS: Saving User Settings from the Report

Sometimes, different report's parameters default values are needed for each users. Here is a way, each user can save their default settings for the next time they run an SSRS report, from the report itself.

We will need to save the user's settings to a database table.
Table create query:

SETQUOTED_IDENTIFIER ON
GO

SETANSI_PADDING ON
GO

CREATETABLE [dbo].[UserSettings](
[UserSettingSQN] [int] IDENTITY(1,1) NOT NULL,
[UserID] [varchar] (50) NOT NULL,
[ReportName] [varchar] (50) NOT NULL,
[ParameterName] [varchar](50) NULL,[ParameterValue] [varchar](max) NULL,
[LastUpdateDate] [datetime] NOT NULL,
CONSTRAINT [PK_UserSettingSQN] PRIMARY KEY CLUSTERED
(   [UserSettingSQN] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
CONSTRAINT [IX_UserSettings] UNIQUE NONCLUSTERED
([UserID] ASC,
[ReportName] ASC,
[ParameterName] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
)ON [PRIMARY]
GO
SETANSI_PADDING OFF
GO

The fields:

• UserID: to store the user info that they used to access the report. I currently store both the computer and username.
• ReportName: The name of the report
• ParameterName: Parameter that is used in the report that is to be saved.
• ParameterValue: Value the user set to save.

An example of the data:

 The Report:

In the Reports, I've created 3 additional parameters, to be used for saving the user's default parameter settings:

• UserID: I use the SSRS's built-in field, User!UserID, to get the ID of the user running the report. Set the visibility to be internal.
• ReportName: Again, I use the SSRS's built-in field, Globals!ReportName, to get the name of the report as it is stored on the report server database. Set the visibility to be internal.
• Save: A boolean field that is visible to the user. I have the default set to "False", so when the user wants to change his/her settings they can change it to true when they run the report.

So when they run the report, I have the stored procedure check to see if the boolean was set to 1 -- if so then save with update/insert.

IF@Save = 1
BEGIN
UPDATE [ScientificArray].dbo.UserSettings WITH (SERIALIZABLE) SET ParameterValue = @StringNames
WHERE UserID = @UserID AND ReportName = @ReportName AND ParameterName = 'InverterString'
IF @@ROWCOUNT = 0
BEGIN
INSERT INTO [ScientificArray].dbo.UserSettings (UserID, ReportName, ParameterName, ParameterValue, LastUpdateDate) VALUES (@UserID, @ReportName, 'InverterString', @StringNames, GETDATE())
END
END