How to Create a Copy of an SSRS Dataset
A lot of times, when creating SSRS reports, I find myself wanting to copy/paste existing objects from the report data window (ex. parameters, data sources, and datasets) either to create duplicates in the same report or create copies in separate reports. Sometimes it’s just easier to start from a copy of an existing object and start the editing there, rather than starting from scratch. I think this functionality would make a nice BIDS Helper feature.
Here two common scenarios that I’ve run into recently where having the ability to copy/paste report objects would have made life way easier:
- When a report has a dataset that is based on a dynamic MDX query (think: string concatenation in the expression editor) and you want to duplicate it or copy it between reports. In this case, you can’t simply create a new dataset and copy over the query-expression. Because it’s dynamic MDX, you also have to copy over the XML field definitions (on the fields-tab of the dataset properties window) one-by-one to the new dataset.
- When you are updating an existing set of parameterized reports that are related via drillthrough actions and you want to add new parameter(s) to each report. In this case, it would be nice to be able to create the new parameter(s) and corresponding dataset(s) in one report (probably the top level report) and then simply copy/paste the parameter(s) and dataset(s) to the rest of the related reports.
The only way I’ve found to do this is by editing the XML behind the report – so that’s what I’m going demonstrate.
- Here’s our base report:
- Right-Click the report in the object explorer and choose “View Code”:
- This opens the XML code behind the pretty UI you’re used to working through. Visual Studio does a nice job with XML, so you can easily collapse items in the hierarchy – stop when you come to the <datasets> level.
- At this point, you’ll want to collapse the actual dataset that you want to duplicate. Then you can highlight the text area and hit Ctrl+C to copy it to your clipboard:
- Make some room beneath the existing dataset and paste the dataset from the clipboard (Ctrl+P)…then rename the new copy of the dataset:
- Ctrl+S to save.
Now close the code window (and the UI window if it’s still open) and then re-open the report. You should now have a nice duplicated copy of the original dataset showing up in your Report Data window.
Hopefully this hack will save you some time in the future. Also, keep in mind that manually editing the XML can also be handy for duplicating (or making bulk changes to) other objects including actions, tooltips, and formulas…basically most items that require a few too many clicks via the UI.