Thursday, 31 July 2014

Reporting Services, shared datasets and Parameters

I had a couple of reports in SSRS that I realised shared basically the same dataset with one parameter different. I created a shared dataset, pointed one of the reports at it and starting getting a load of errors. Clearly, I had done something wrong but I wasn't quite sure what. I got errors like "object reference not set to an instance" as well as something about forward references and another about un-passed parameters. Here is what I should have done:

  1. Create your shared dataset. You probably don't want default values specified here but you can if you want to share these defaults across the reports. 
  2. Use the syntax @name to put a parameter into your SQL as you would do in the normal dataset.
  3. If your dataset allows null parameters, these need to be declared in the shared dataset, not just in the report parameters. You can do this in the parameters tab for the shared dataset.
  4. Modify your report dataset and click the radio button, "use a shared dataset" and select your shared dataset. The fields for the shared dataset will appear in the Report Data window in just the same way as if the dataset was local to the report. If you cannot see the Report Data window, it is under the View menu (right at the bottom of my menu) and is also hot key Ctrl-Alt-D by default.
  5. For your parameters, double-click the dataset in the Report Data window and clicking the Parameters tab, assign your report parameters to the dataset parameters. Mine tend to have the same name so @EventId is mapped to [@EventId]
  6. If you need to, change the report parameters (under the Parameters section of the Report Data window) to set defaults, selection sets, nulls etc. It is these that will define what the user can see/set when they run the report.
I think that's it.
Post a Comment