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

Removing accents for string comparisons in tsql

I've been reading The Daily WTF, and found this one interesting: http://thedailywtf.com/Articles/The-AntiSQL-Coalition-.aspx

By using the Latin1_General_CI_AI   (AI stands for Accent Insensitive)
the user was able to by-pass special characters like: É,È,Ê, and Ë for E when doing a search.
Example:

SELECT*
FROMPersons
WHEREname collate Latin1_General_CI_AI LIKE @name
AND surname collate Latin1_General_CI_AI LIKE @surname
ORDERBY name, surname

Additional references:
http://msdn.microsoft.com/en-us/library/ms187582.aspx


P.S. Funny to see other companies have Database haters too.

TSQL Example: Cumulative

A quick example on implementing cumaltive logic in a tsql query:

WITH CTE(Name, Observation, RowNum)
AS
(
SELECT
Name,
count(*) as Observation,
ROW_NUMBER() OVER (ORDER BY count(*)desc) as rownum
FROM
dbo.TABLE_LOGIC_STUFF
GROUP BY Name
)

select
c1.Name,
c1.Observation,
(select sum(c2.Observation) from cte as c2 where c2.RowNum <= c1.RowNum) as cumulative
from cte as c1



by adding row number in the cte table, I can then use a subquery to sum all the values below the current rownum.

SQL Server Query Performance Analysis

Great post by Carl Nolan(http://blogs.msdn.com/b/mcsuksoldev/archive/2011/11/27/adventure-in-tsql-sql-server-query-performance-analysis-using-dmvs.aspx) on finding the worst offending queries that do the most CPU and Disk I/O loads.

The two queries are:

CPU Query

-- Which Queries are taking the most time/cpu to execute
SELECT TOP 20
    total_worker_time, total_elapsed_time,
    total_worker_time/execution_count AS avg_cpu_cost, execution_count,
    (SELECT DB_NAME(dbid) + ISNULL('..' + OBJECT_NAME(objectid), '')
        FROM sys.dm_exec_sql_text([sql_handle])) AS query_database,
    (SELECT SUBSTRING(est.[text], statement_start_offset/2 + 1,
        (CASE WHEN statement_end_offset = -1
            THEN LEN(CONVERT(nvarchar(max), est.[text])) * 2
            ELSE statement_end_offset
            END - statement_start_offset) / 2
        )
        FROM sys.dm_exec_sql_text([sql_handle]) AS est) AS query_text,
    total_logical_reads/execution_count AS avg_logical_reads,
    total_logical_writes/execution_count AS avg_logical_writes,
    last_worker_time, min_worker_time, max_worker_time,
    last_elapsed_time, min_elapsed_time, max_elapsed_time,
    plan_generation_num, qp.query_plan
FROM sys.dm_exec_query_stats
    OUTER APPLY sys.dm_exec_query_plan([plan_handle]) AS qp
WHERE [dbid] >= 5 AND DB_NAME(dbid) IS NOT NULL
  AND (total_worker_time/execution_count) > 100
--ORDER BY avg_cpu_cost DESC;
--ORDER BY execution_count DESC;
ORDER BY total_worker_time DESC;

 

Disk IO Query

SELECT TOP 20
    total_logical_reads/execution_count AS avg_logical_reads,
    total_logical_writes/execution_count AS avg_logical_writes,
    total_worker_time/execution_count AS avg_cpu_cost, execution_count,
    total_worker_time, total_logical_reads, total_logical_writes,
    (SELECT DB_NAME(dbid) + ISNULL('..' + OBJECT_NAME(objectid), '')
        FROM sys.dm_exec_sql_text([sql_handle])) AS query_database,
    (SELECT SUBSTRING(est.[text], statement_start_offset/2 + 1,
        (CASE WHEN statement_end_offset = -1
            THEN LEN(CONVERT(nvarchar(max), est.[text])) * 2
            ELSE statement_end_offset
            END - statement_start_offset
        ) / 2)
        FROM sys.dm_exec_sql_text(sql_handle) AS est) AS query_text,
    last_logical_reads, min_logical_reads, max_logical_reads,
    last_logical_writes, min_logical_writes, max_logical_writes,
    total_physical_reads, last_physical_reads, min_physical_reads, max_physical_reads,
    (total_logical_reads + (total_logical_writes * 5))/execution_count AS io_weighting,
    plan_generation_num, qp.query_plan
FROM sys.dm_exec_query_stats
    OUTER APPLY sys.dm_exec_query_plan([plan_handle]) AS qp
WHERE [dbid] >= 5 AND DB_NAME(dbid) IS NOT NULL
  and (total_worker_time/execution_count) > 100
ORDER BY io_weighting DESC;
--ORDER BY avg_logical_reads DESC;
--ORDER BY avg_logical_writes DESC;
--ORDER BY avg_cpu_cost DESC;

Dictionary Not Found

Kept getting the error: "Dictionary Not Found..." once I uploaded new changes to the web server to allow spell checking. So, my first step was to open the developer tools in IE (F12) and do a quick capture of the network traffic.

Which gave:

URL: /fieldperformance/C1Spell_en-US.dct
Method: GET
Result: 404
Type: text/html
Received: 1.37 KB
Taken: 265 ms 
Initiator:
Wait‎‎: 1482
Start: 62
Request: 203
Response‎: 0
Cache: 0
read‎‎ Gap‎‎: 5067

So, it's looking in the fieldperformance folder for the dictionary file. Doing a quick check, yes the file is located there. The problem then ends up being the MIME type is missing.

Going to the IIS Manager, I added the Extension dct with a MIME type of application/octet-stream.

WCF & Silverlight max buffer size issue

Ran into these problems a few times with the WCF errors, in which the buffer size was the issue:

• “The remote server returned an error: NotFound”
• "Unable to read data from the transport connection: The connection was closed"
• "The maximum message size quota for incoming messages (65536) has been exceeded. To increase the quota, use the MaxReceivedMessageSize property on the appropriate binding element."

This would be an easy solution if only dealing with the WCF. Since, we are using the WCF layer to pass data to/from the Silverlight layer the problem arises in two locations -- not one.

Over at Mehroz's Experiments (http://smehrozalam.wordpress.com/2009/01/29/retrieving-huge-amount-of-data-from-wcf-service-in-silverlight-application/) he goes into good detail on solving this problem.

So in the Silverlight's ServiceReferences.ClientConfig we increase the buffer size.

Code Snippet

 <binding name="BasicHttpBinding_IScientificDataService"   closeTimeout="00:01:00"openTimeout="00:01:00" receiveTimeout="00:10:00" sendTimeout="00:01:00" maxBufferSize="2147483647" maxReceivedMessageSize="2147483647" textEncoding="utf-8" transferMode="Buffered">
    <security mode="None"/>
binding>

In the WCF's Web.config, we needed to increase the maxBufferSize and maxReceivedMessageSize to a larger number Code Snippet   <system.serviceModel>     <behaviors>       <serviceBehaviors>         <behavior>           <serviceMetadata httpGetEnabled="true" />           <serviceDebug includeExceptionDetailInFaults="true" />           <dataContractSerializer maxItemsInObjectGraph="2147483647"/>         behavior>       serviceBehaviors>     behaviors>     <serviceHostingEnvironment multipleSiteBindingsEnabled="true" aspNetCompatibilityEnabled="false" />   system.serviceModel>

SQL String Manipulations

Interesting article in SQL Server Magazine about String Manipulations by Itzik Ben-Gan (http://www.sqlmag.com/article/tsql/string-manipulation-tips-techniques-part-1-136427)

So to give me a quick reference in the future, I'll summarize it here:

Counting Occurrences of a subString within a string:

DECLARE
  @str    AS VARCHAR(1000) = 'abchellodehellofhello',
  @substr AS VARCHAR(1000) = 'hello';

SELECT (LEN(@str) - LEN(REPLACE(@str, @substr, ''))) / LEN(@substr);


Exactly N Occurrences of a substring within a string:

DECLARE
  @str    AS VARCHAR(1000) = 'abchellodehellofhello',
  @substr AS VARCHAR(1000) = 'hello',
  @N      AS INT           = 3;

SELECT
  CASE
    WHEN (LEN(@str) - LEN(REPLACE(@str, @substr, ''))) / LEN(@substr) = @N
      THEN 'True'
    ELSE 'False or Unknown'
  END;
-OR-

SELECT
  CASE
    WHEN @str LIKE '%' + REPLICATE(@substr + '%', @N)
         AND @str NOT LIKE '%' + REPLICATE(@substr + '%', @N+1)
      THEN 'True'
    ELSE 'False or Unknown'
  END;

Replacing Multiple Contiguous Spaces with a single space:

DECLARE @str AS VARCHAR(1000) = 'this   is     a   string    with     lots   of   spaces';

SELECT REPLACE(REPLACE(REPLACE(@str, ' ', '~ '), ' ~', ''), '~ ', ' ');

Replacing Overlapping Occurrences:

DECLARE @str AS VARCHAR(1000) = '.x.x.x.x.';

SELECT REPLACE(REPLACE(@str, '.x.', '.y.'), '.x.', '.y.');

-OR-

SELECT REPLACE(REPLACE(REPLACE(@str, '.', '..'), '.x.', '.y.'), '..', '.');
String Formatting Numbers with Leading Zeros:

DECLARE @num AS INT = -1759;

SELECT CASE SIGN(@num) WHEN -1 THEN '-' ELSE '' END + REPLACE(STR(ABS(@num), 10), ' ', '0');

-OR-

SELECT CASE SIGN(@num) WHEN -1 THEN '-' ELSE '' END + RIGHT('000000000' + CAST(ABS(@num) AS VARCHAR(10)), 10);

-OR (In Denali)-

SELECT FORMAT(@num, '0000000000');

Left Trimming Leading Occurrences of a Character:

DECLARE @str AS VARCHAR(100) = '0000001709';

SELECT REPLACE(LTRIM(REPLACE(@str, '0', ' ')), ' ', '0');

Checking That a String Is Made of Only Digits:

DECLARE @str AS VARCHAR(1000) = '1759';
SELECT
  CASE
    WHEN @str NOT LIKE '%[^0-9]%' THEN 'True'
    ELSE 'False or Unknown'
  END;

-OR-

CHECK (col1 NOT LIKE '%[^0-9]%')