Situation
The requirement in a lot of my reports in SSRS has always been a date range rather than just the one date. The user must be allowed to select all results between two dates instead of just specifying the one day. If the second parameter (ToDate) is the same as the first (FromDate), then the range is for that particular date.

Solution

The situation is that we are replacing a website-application with a single SQL Server Reporting Services (SSRS 2008 R2) report. We're using Business Intelligence Development Studio (BIDS VS2008) with Team Foundation Server (TFS VS2010) and connecting to an Oracle database (ie. "seamless integration because our setup is perfect and well thought through" not - note the DBMS is not hugely relevant for the purposes of this article).

The end-user must be able to search on EITHER the student's username or the student's ID (2 report parameters: @StudentADAccount [varchar] and @StudentReference [int] respectively). Most of the remaining datasets use the resulting @StudentReference number in their "where" clause. A student always has a "Student Reference" but not necessarily a student AD account (enquired/applied only).

So I find myself using date ranges endlessly as I've been working in SSRS. The below is derived from a collection of various sources across the web as well as some of my own. These are what worked in my environment: WinXP, BIDS (vs2008), TFS (vs2010), SSRS 2008 R2. The following examples assume today's date is Wednesday 03 August 2011 @ 11:46:

This Week:

-- Start Date (US format - mm/dd/yyyy)
=DateAdd("d", -(WeekDay(Today(),2))+1, Today()) // yields: 8/1/2011

-- End Date (US format - mm/dd/yyyy)
=DateAdd("d", -1, DateAdd("d", 7-(WeekDay(Today(),2))+1, Today())) // yields: 8/7/2011

-------------------------------------------------------------------------

-- Start Date (european format - dd/mm/yyyy)
=Format(DateAdd("d", -(WeekDay(Today(),2))+1, Today()), "dd/MM/yyyy") //yields 01/08/2011

-- End Date (european format - dd/mm/yyyy)
=Format(DateAdd("d", -1, DateAdd("d", 7-(WeekDay(Today(),2))+1, Today())), "dd/MM/yyyy") // yields: 07/08/2011

This Month:

So I'm looking for a SQL query that could do this all in one go and return all the results in one table.

With PHP & MySQL it's pretty simple: use individual SQL queries to get the count of yesterday, yesterweek, yestermonth, yesteryear and do the layout in PHP.

Now let's say I have one RDL or SSRS Solution. I could do a dataset per SQL query but it doesn't seem that ideal.

In Theory:

What?
This article serves to describe several workarounds or proofs of concept.

Why?
We have a first report which acts like a search page. You enter the student you are looking for by reference or name and if there is only 1 result in the results page, then we want it to redirect to the details report for that student automatically.

How?
So there are various solutions out there, here's an overview of some:

Method #1

1. Create one report with all selectable reports as subreports - toggle visibilty based on parameter.

Method #2

1. Add a button below "View Report" linked to some script code (requires change to ASP pages on the ReportingServer, ie. affects all reports on that server).

Method #3

1. Use ASP instead of SSRS.

Method #4

1. Use a TimeInterval refreshing the page after a certain time.

Conclusion
In the end, this came down to a design decision. Time spent on trying to find a solution was not considered to be warranted. My reports use a parameter for the connection string to specify which server and database to connect to and the follow on link was enough as a solution. My seniors advised that if we had time in the future we could expand on this further.


Google Searches that got me nowhere:

• ssrs process parameter after report execution
• auto-redirect after are a report is run
• ssrs vbscript post report processing
• ssrs auto redirect based on a dataset value

---

I'm storing a note here because it took a while to figure out and googling other solutions did not answer our questions but did lead us to a workaround.

So this is regarding an error when trying to add parameters to a Stored Procedure of an Oracle database from within Microsoft SQL Server Reporting Services.

The Error

ORA-00911: invalid character 
ORA-06512: at "SYS.DBMS_UTILITY", line 114 
ORA-06512: at line 1 (System.Data.OracleClient)

The Situation
I'm using Business Intelligence Development Studio 2008 to develop a Reporting solution on a Windows XP workstation. We are connecting to a SQL Server 2008 R2 Reporting Service (SSRS) with Team Foundation Server 2010. The database with our stored procedure is Oracle 10g.

The Stored Procedure
Rather than a returned dataset, our Stored Procedure updates a field in one of our Oracle tables and returns nothing. It accepts 3 parameters: The StudentID (reference) varchar2, Username (who's running the report) varchar2 and a JobID (request reference) number.

The Problem
I've set some tablix headers but when I request the same report in PDF format, the tablix headers only appear once on the first page. Every subsequent page simply displays the report header (which does not include the tablix header... obviously). I right-clicked on the header row of the tablix and checked the box "Repeat header columns on each page.

My Setup

• XP SP3 Workstation
• Business Intelligence Development Studio 2008 (BIDS)
• SQL Server Reporting Services 2008 R2 (SSRS)

What do I want?
I have a report displaying room bookings. Each row lists the day, date, room name, start/finish times, booking details and the staff contact. I want an empty row to appear between each day in the list, so I have: