SSRS: Caching a shared Oracle Dataset with date parameter

Question

• I am using SSRS (2008R2) to create a large report against an Oracle database.

• Because of the size of the report I wanted to cache the shared dataset overnight.

• The dataset has a single parameter which should default to today's
  date but be changeable in the report (although this would trigger
  another cache).

I have struggled with this for 3 days and wanted to document how I got it to work, as well as get feedback on my approach.

Answer 1

1. Oracle Datasource:

• I found that passing parameters to the dataset only worked well for me using the native Oracle drivers, not the OLE DB drivers.

2. Shared Dataset:

• I was never able to get SSRS to pass a date parameter to Oracle. In my Oracle query I therefore used the conversion TO_DATE(:EffectiveDate,'YYYY-MM-DD"T"HH24:MI:SS') (you'll see why I had to use ISO 8601 format later).
• When adding the variable :EffectiveDate to my SQL query a parameter was automatically added to by shared dataset. This initially caused the Oracle error ORA-01008: not all variables bound. This was resolved by deleting the dataset parameter and then clicking "refresh fields" on the dataset query screen to re-create it. The only difference apparently being that now the parameter name has a colon.
• I have set the dataset parameter up as shown below in the screenshot:

• The parameter is of type "text" and defaults to today at midnight in ISO 8601 format (=Format(Today(),"yyyy-MM-dd\T\0\0:\0\0:\0\0"))

3. Report Dataset:

• The Report Dataset uses the Shared Dataset above and also has a dataset parameter. In this case I want the report dataset parameter to take a date from a report parameter but pass a string to the shared dataset parameter as below:

• The Dataset Parameter is set to the value =Format(Parameters!EffectiveDate.Value,"yyyy-MM-dd\T\0\0:\0\0:\0\0")

4. Report Parameter:

• The Report Parameter is set to "date" type as below with a default of =Today():

5. Shared Dataset Caching:

• First I set the Shared Dataset to use cached data (on the report server via your browser):

• Then I set up a "Cache Refresh Plan":

• The Cache Refresh Plan seems very picky about Parameter format and it was this last step that only seems to work if passing dates as ISO 8601 compliant strings.

6. Notes:

• :EffectiveDate is the name of my Oracle string bind variable and EffectiveDate is the name of my Report parameter. Change your code appropriately.
• I was only interested in the date (i.e. not the time). If you need time as well you may need to make some changes.
• In order to let the end user know how old the data was, I added an extra column ,SYSDATE AS DATA_AGE to my SQL Query. I was then able to reference the age of the data in my report header with First(Fields!Report_Date.Value, "<report_dataset_name")
• I am able to create snapshots of the report but all of the prompts are greyed out, even though all the other prompts only effect report filtering.
• To have different prompt defaults in my snapshots I used the test IsNothing(User!UserID). I'd love to know if there was a better way.
• My example isn't necessarily in the order in which I set things up (i.e. I reference the Report Parameter before showing its setup).