SSRS - using a dummy data source when designing a report

Question

I am working on a report that has a very long running query. It is on an IBM iSeries DB2 database and I have no access to affect the performance.

I am designing a complex report around this data. It is taking a long time as when I make a bunch of changes and run the report (report is local, not connected to a server yet), it takes so long to display. And I've been going back and forth.

I thought I could get around this by exporting the query results to Excel and creating an Excel data source and dataset that mimics the DB2 one. However, when I switched my Tablix to the new dataset and ran the report, it still was running the DB2 query. Is there anyway to get around this and temporarily use my Excel datasource until I get the formatting/design finished?

Note: I don't have access to a SSRS report server with this data source yet so I can't use caching there.

Are you using BIDS to develop your RDL? BIDS will cache data to the project directory during development, and read from that cache as long as the report parameters are the same values each time you preview the report. It's a huge time saver. — R. Richards
I'm using Report Builder 3.0. I can use BIDS though. I'll give that a try. There are no parameters (the query uses the latest date). — user1612851

Gregg Gregg · Accepted Answer · 2016-08-22T19:57:33

Create an .rsd with Text command and and some select statements unioned together like this:

  SELECT 12345 AS CaseID, 2014 AS CaseYear, 'Q1' AS CaseQuarter, 'Human Resources' 
  UNION
  SELECT 541 AS CaseID, 2014 AS CaseYear, 'Q2' AS CaseQuarter, 'Human Resources' 
  UNION
  SELECT 999 AS CaseID, 2014 AS CaseYear, 'Q2' AS CaseQuarter, 'Human Resources'

SSRS - using a dummy data source when designing a report

1 Answers