SSRS - Joel Lipman .Com

SSRS Display question mark when date is blank

: Joel Lipman; SQL Server Reporting Services; 15 April 2014; Hits: 6626

Applies to:

Microsoft SQL Server Reporting Services 2012
Microsoft Visual Studio 2012 Premium
Microsoft Windows 7 Enterprise

What?
So I have a column in an SSRS report which displays a date. Being rather pernickety, I would like a question mark to display if there is no date to populate the field.

Why?
At the moment, the expression in there is something like this:

copy raw

=Format(Fields!MyCompletionTime.Value, "dd/MM/yyyy HH:mm:ss")
-- yields 11/02/2014 11:21:32

=Format(Fields!MyCompletionTime.Value, "dd/MM/yyyy HH:mm:ss")
-- yields 11/02/2014 11:21:32

Sounds good, where's the problem?

Category: SQL Server Reporting Services :: Article: 562

: Joel Lipman; SQL Server Reporting Services; 11 April 2014; Hits: 7502

What?
This article describes a solution our reporting server administrators found to cut the loading time of the first report of the day.

Why?
Take any SSRS report, if one of us was the first to run it on that day, it would take an additional 60 seconds to get with it and display the report. Any subsequent running of the report loaded it almost immediately.

How?

: Joel Lipman; SQL Server Reporting Services; 08 April 2014; Hits: 13530

What?
Sounds easy but actually if you use an image which has transparent areas, ie. has one color which will be transparent, the transparent pixels will be colored in with the page background color.

Why?
I want an image to display per row as a status marker for 3 different types of results: Success, Failure, Unknown. My images are circles with the background being transparent. I want the first column to display an image based on the status result.

If I simply insert an image, the report would use the background color of the report.
How?

: Joel Lipman; SQL Server Reporting Services; 08 April 2014; Hits: 8429

Applies to:

Microsoft SQL Server Reporting Services (SSRS) 2008 R2
Microsoft Visual Studio 2012
Microsoft Business Intelligence Development Studio

What?
I recently revamped our standard report with a new template comprising of a single Tablix which contained the images of the corners and sides of the report. It looked beautiful if simply displayed on a single page (the top half of the page) as long as the end-user doesn't scroll. If the page was scrolled, what should display went behind my report and instead the whole report acted as a header on top of the scrolling content and would only ever display, the top rows of the dataset.

Why?
A lot of articles out there on the net are offering solutions that do the exact opposite. Basically, I want the opposite result of "Fixed headers while scrolling".

How?

: Joel Lipman; SQL Server Reporting Services; 07 March 2014; Hits: 9949

What?
This article details a method we used on a search form in a SQL Server Reporting Services (SSRS) environment. The requirement (amongst others) was that the report ran in less than a few seconds.

Analyzing the report through the ReportServer databases (internal auditing) only showed how long the SQL query would take to recover the data and present it in a report, which would average about 0.4 seconds.

We're obviously not interested in shaving off time from 0.4 seconds; no, we're more interested in the fact that 0.4 Microsoft seconds seems to translate to about 10 minutes in the real world. Where do the extra 9 minutes come from? Not the SQL as this runs in 00:00 seconds in SQL Server Management Studio. Not the internal processing and rendering as the report server says it did this in 0.4 seconds. So where's the problem?

How?

: Joel Lipman; SQL Server Reporting Services; 20 January 2014; Hits: 8590

What?
A very quick note on why borders may be misbehaving in SSRS. This note corrects the situation at Design time. This is where you are trying to select a cell to have an outline border but then cells around it without a border. You may find that BIDS or Report Builder (however you're building your report) will apply the border to some cells and sometimes a whole section of the report with just one border???

How?

: Joel Lipman; SQL Server Reporting Services; 22 November 2013; Hits: 10022

Applies to:

Microsoft Business Intelligence Development Studio 2008 (BIDS)
Microsoft SQL Server 2008 R2

What?
This article describes solutions to the error:

Subscriptions cannot be created because the credentials used to run the report are not stored, or if a linked report, the link is no longer valid.

How?

: Joel Lipman; SQL Server Reporting Services; 11 November 2013; Hits: 4383

What?
A quick article on if you are trying to create a new subscription to a SQL Server Reporting Server (SSRS) report and you are getting a warning message similar to the following:

copy raw

Message from webpage

Subscriptions cannot be created because the credentials used to run the report are not stored, or if a linked report, the link is no longer valid.

Message from webpage
Subscriptions cannot be created because the credentials used to run the report are not stored, or if a linked report, the link is no longer valid.

Why?
The problem happens because of the data source used in the report. For static data source connections, this is straightforward but in our case, we have a report which points to different servers based on a report parameter.

How?

Category: SQL Server Reporting Services :: Article: 533

: Joel Lipman; SQL Server Reporting Services; 19 January 2012; Hits: 53548

What?
So I've spent a fun time googling and binging but still haven't found a simple and complete example of getting a resultset from an Oracle stored procedure and displaying this in SQL Server Reporting Services (SSRS). Well "non-productive" more than "fun" as most of the examples on the net are either half-complete or partially documented. So here goes...

Using Business Intelligence Development Studio v2008 (BIDS)
SQL Server Reporting Services v2008 R2 (SSRS)
Oracle SQL Developer v3 (you can use any equivalent, eg. SQL*Plus)

Why?
I think this is one of those very rare occasions that Microsoft people can say "it's so much easier using a Microsoft product to work with another Microsoft product" (ie "Seamless integration"). Yes, I'm trying to get an SSRS report to display the results from an Oracle stored procedure. I have a previous article describing a basic stored procedure in Oracle, this article aims to outline how to apply this to an SSRS 2008 R2 report.

: Joel Lipman; SQL Server Reporting Services; 14 December 2011; Hits: 27608

What?
I have created an SSRS report which can compare 4 reports side by side and brings up their latest execution times to the nearest millisecond. The report has 4 parameters. Each parameter is a dropdown populated by a list of all available reports.

Why?
I want the report to be run with the 3rd and 4th parameter as OPTIONAL. When I leave the 3rd and 4th parameter untouched (="<Select a value>"), the report complains saying "Report #3 parameter cannot be blank!". Before you ask, I have ticked both "Allow Blank" and "Allow NULL".

How?
This is the tough part. I was reading up on the MSDN page for the closest solution but it still didn't work for me. But the idea of inserting a NULL entry to select sounded good.

Aim / Objective
The plan will be to replace the default "<Select a Value>" with a custom null entry and the end-user will be none the wiser.

: Joel Lipman; SQL Server Reporting Services; 02 December 2011; Hits: 14383

Why?
I've recently written a report for SQL Server Reporting Services 2008 R2 (SSRS) which will compare up to 4 reports and will compare the time taken for each one. The breakdown or what I was able to measure with the default installation are the times taken for "data retrieval", "processing", "rendering", and then the totals of these.

I haven't Googled this at the time of print so there may be a million better solutions out there, this is just how I did it. This may look like a horrible report which would fail an accessibility test but visually it says straight away which is the better report; and when comparing to the previous runs (using a second dataset) you can tell where changes were made and how this affected the reports' performance.

What?
What I'm trying to do is display a set of results (comparing various reports) in a table and then to color the backgrounds based on whether they are the fastest or slowest in the set.

Something like:

: Joel Lipman; SQL Server Reporting Services; 17 November 2011; Hits: 36129

How to Display Report Execution Time in SQL Server Reporting Services 2008 R2
So there are other articles out there but I was looking to display in milliseconds the execution time it took for a particular report (which searches for results matching the submitted parameter) to run.

Lifted from Dattatray Sindol's blog
Other sites have this solution as well so who copied off who is not my concern as this is not the solution to our problem. This is the solution that I initially used but my end-users were asking why is it always 0 seconds. This was because we were using the following MDX statement which had seconds as its smallest denominator:

copy raw

="Execution Time: " +
CStr(System.DateTime.Now.Subtract(Globals!ExecutionTime).Hours) + " hour(s)" + " , " +
CStr(System.DateTime.Now.Subtract(Globals!ExecutionTime).Minutes) + " minute(s)" + ", " +
CStr(System.DateTime.Now.Subtract(Globals!ExecutionTime).Seconds) + " second(s)"

// yields
// 0 hour(s), 1 minute(s), 2 second(s)

="Execution Time: " +
CStr(System.DateTime.Now.Subtract(Globals!ExecutionTime).Hours) + " hour(s)" + " , " +
CStr(System.DateTime.Now.Subtract(Globals!ExecutionTime).Minutes) + " minute(s)" + ", " +
CStr(System.DateTime.Now.Subtract(Globals!ExecutionTime).Seconds) + " second(s)"
// yields
// 0 hour(s), 1 minute(s), 2 second(s)