The Situation
We want some photos taken from another website of ours to display in our report. These are not stored locally on the Reporting Server because other services use these photos on our Intranet and we only want one location to upload the photos. These images are provided via a website address (http) or network share (file).

Before you ask, we have several MS Sharepoint sites but we didn't go down the route of uploading 10000+ images into a Sharepoint database though we are still considering it; so this article is more about just getting external images to display when they are provided over an authenticated URL (You shouldn't be having any issues with this if the image is available to anonymous users - eg. Google Logo).

Our Setup

1. Windows XP Workstation
2. Business Intelligence Development Studio 2008 (BIDS)
3. SQL Server 2008 R2 Reporting Server (SSRS) running on Windows Server 2003
4. Team Foundation Server 2010 (TFS)

Problem #1: Image does not appear within SSRS
When previewing the report in BIDS, the photo appears fine. If we deploy the report to our Reporting Server and attempt to view it using a Web-Browser, the image does not display:

1. Get report to see images hosted on an external URL
2. Check if image exists to display alternative placeholding image.

Quick Reminder
I didn't want to do this at the database level, mainly because it meant modifying the SQL query. The zero padding would need to be applicable within an MDX query.

The Situation
We have a database using Oracle 10g, and a SQL Server Reporting Services v2008 R2 environment. My use for this was when displaying an audit log displaying the oracle errors.

Oracle Errors
An Oracle error usually returns in the format of -12345. If we want to look them up the error is ORA-12345. Unfortunately Oracle also returns errors of less than 10000 so ORA-00201 would actually be returned as "-201". As I wanted a link so that the user can just click on this link and it would take them to http://ora-00201.ora-code.com/.

The Issue

After modifying a report and on running it, Report Builder 2.0 returns the following error:

An error occurred during local report processing.
An error has occurred during report processing.
Query execution failed for dataset 'DataSet1'.
The variable name '@GroupName' has already been declared.  Variable names must be unique within a query batch or stored procedure.

Yes, well don't laugh, I could not find this on the WWW so I was obviously not using Google properly.  Anyway here's just a quick note on how to do if else statements in Report Builder 2.0

I'm looking at the following if statement:

If (MyFieldName = 0) Then
     Return 1
Else
     Return MyFieldName

Can be expressed as:

IIf(Fields!MyFieldName.Value = 0, 1, Fields!MyFieldName.Value)

---

 

• SQL Server 2008
• Report Builder 2.0
• BMC Service Desk Express 9.8

• Incident #
• Group Name
• Close Date & Time
• Assigned to Full Name
• Incident Type

Basically I thought that the regional settings of a report generated using Report Builder 2.0 on a MS SQL Server 2008 instance were dependent on either the server or the client machine.  Realised that this was actually specified in the report. Here's a quick note on how to set Dates and Times used in parameter fields to UK format (dd/MM/yyyy).

Once again I've spent a whole morning going in the wrong direction with a convincing SQL script. This article is just a note on when using Business Intelligence Development Studio (BIDS) or Report Builder 2.0 and when previewing the report, the parameters are not accounted for (ie. the SQL query creating the dataset does not use the parameters in its query).


Problems:

• Given a date, the date format was reverting to US format as opposed to European
• Given a Campus as a text value, the SQL query was ignoring this completely
• Hardcoding the scalar local variables worked

This is just a note for me as it took a while to find on the net and even then it was confusing as to why it works but it does. Not sure whether you call this an MDX Query or part of a Transact-SQL mashup. You need to go to "Fill" (of each text box in the row - unless there's a faster way) and instead of color, click on the expression button (fx) and use the following:

=IIF(RowNumber(Nothing) Mod 2, "#ffffff", "#eeeeee")

or 

=IIF(RowNumber(Nothing) Mod 2, "WhiteSmoke", "Garamond")

This is telling the report to be white (=#ffffff) in row 1 (odd row numbers) and a very light grey (=#eeeeee) in row 2 (even row numbers). The first row being of all rows in the dataset and not for a particular drill-down item. It will alternate between the two for the rest of the report. [The second example is what I use most frequently].