Category Archives: SSRS

SSRS Active Session Information

Posted by on March 11, 2014 Comments Off on SSRS Active Session Information

SQL Server Reporting Services (SSRS) showed up to the party in SQL Server 2000.  A really awesome tool for data delivery.  Most SQL Server environments heavily leverage this powerful tool.  Microsoft has baked in some excellent views and tables for report rendering information (dbo.ExecutionLog, dbo.ExecutionLog2 and dbo.ExecutionLog3).  SSRS also supplies a text log with additional details for errors and warnings.  All are  useful when trouble shooting a failure or identifying report execution trends, but what happens if you want to know what is running, now?

Recently, I needed to  identify an active report that was creating a considerable amount of blocking.  We were able to identify a reporting login , verify the  blocking originated from SSRS. I could grab the query being executed, but needed to find the report and user or maybe subscription causing the blocking.  As I mentioned, for the most part the ReportServer database gives us historical information.  I always based most of my queries on the ReportServer database.  In a brief moment of clarity, I realized that maybe just maybe this ReportServerTempDB database might just store ‘temporary’ data about active sessions. Welcome back to the show, Jack!
Read more »

T-SQL Using Parameter Table like a Cursor

Posted by on February 3, 2010 Comments Off on T-SQL Using Parameter Table like a Cursor

The Google gods seem to like a very old page of mine about using Cursors in T-SQL. So, from time to time I get commnets via email.  Thought I  would share an alternative to using cursors.

Read more »

Simple Talk Blog – Top 10 SSRS Challenges and Solutions

Posted by on November 6, 2009 Comments Off on Simple Talk Blog – Top 10 SSRS Challenges and Solutions

Ryan Duclos wrote a great article on 10 Common Issues with SSRS. Download his Code, too.

FROM  http://www.simple-talk.com/sql/reporting-services/ten-common-sql-server-reporting-services-challenges-and-solutions/

Read more »

SSRS – Format() and FormateDateTime()

Posted by on April 15, 2009 Comments Off on SSRS – Format() and FormateDateTime()

Found this reference … JUST IN TIME!

Thanks Thavash This is by far my most visited page.

http://thavash.spaces.live.com/blog/cns!CF6232111374DFD2!155.entry

April 10

Working with Dates in Reporting Services

As with any other technology ( eg. SQL , C# ), you always find people running into problems with processing of dates. I was asked today , “How to I format dates when using the Date Picker and sending it through to a stored procedure” ? Let’s have a look ….
1) The FormatDateTime command
This is pretty easy to use, but maybe a bit limiting. You can specify 1 of 4 formats using the command arguments. Let’s say we have selected a date such as 10th April 2007 , our results will be as follows :
Command Result
FormatDateTime(Parameters!Date.Value,1) Tuesday, April 10, 2007
FormatDateTime(Parameters!Date.Value,2) 4/10/2007
FormatDateTime(Parameters!Date.Value,3) 12:00:00 AM
FormatDateTime(Parameters!Date.Value,4) 00:00

…but the better way to do it would be to use …

2) The Format command and specify the exact format you require. For example…

Command Result
Format(Parameters!Date.Value,”dd-MM-yyyy”) 10-04-2007
Format(Parameters!Date.Value,”dd/MM/yyyy”) 10/04/2007
Format(Parameters!Date.Value,”MMM-dd-yyyy”) Apr-10-2007
Format(Parameters!Date.Value,”MMM-dd-yy”) Apr-10-07

So 3 M’s give you “Apr” ….anyway this is quite useful if you’re looking for Day/Month/Year , since the system will default to MM/DD/YYYY.

Using this you should be able to display the date format you want , or send through a particular format to a Stored Proc.

EDITED 22/08/2007 : If the Format Command doesn’t work , try converting the value to a date , eg.

Format(Cdate(Parameters!Date.Value),”dd-MM-yyyy”)