Tag Archives: SSRS

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 »

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()

Found this reference … JUST IN TIME!

Thanks Thavash This is by far my most visited page.


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.


SQl Server 2005 Reporting Services – Charts

Found this link that give a deep-dive into fomat and functionality of charts in SSRS 2005.  Adds a lot to making charts meaniningful and jumps to details.

TechNet Article:

SSRS Links on sql-server-performance.com

Found this link on http://sql-server-performance.com  to various Reporting Services Links : HERE

Good FAQ on http://www.ssw.com.au  on issues and set-up of SSRS – HERE

SQL Server Management Studio Standard Reports

SQL Server Management Studio 2005 (SP2) updates has a wide range of reports that give the users vital information about server health and database objects.  You can even add your own custom reports to create you own application database dashboards.  The reports are created just like SQL Server Reporting Services RDL’s.

Some Links to help you along:

Listing of some Available Reports HERE

How to make your own Custom Reports HERE