Monday, September 21, 2009

Setting A report's Data Source to a Shared Data Source Programmatically

One of the things that you will surely need when adding reports to the reporting service programmatically, is setting its data source to the shared data source you have in the reporting server. I personally had this problem and I thought that "I can create a new DataSource Object, then use the SetItemDataSources method to attach this data source to the report". That's what I actually did, but I always had an exception telling me that, The data source ABC is not found, although am sure that the data source name is the same like the shared data source's name.

What I found out later is, the SetItemDataSources method searches the report's data sources for the name you have set, i.e this name is not the shared data sources' name, but the data sources that is in the rdl file (in the <DataSources> tag).
So in order to fix that, we need to tell the reporting service that the report's data source with the name X (which we will see how to find it out) is still there, but it will reference to the shared data source.
so now lets get our hands dirty with some code.

How to

//Get the Shared Data Source
DataSource[] ds = GetSharedDataSource(service, reportsFolder, reportName);
// Set Report's DataSource
service.SetItemDataSources(@"/" + reportsFolder + @"/" + reportName, ds);

public static DataSource[] GetSharedDataSource(ReportingService2005 service, string reportsFolder, string reportName)
        {
            DataSourceReference reference = new DataSourceReference();
            DataSource ds = new DataSource();
            reference.Reference = "/" + reportsFolder + "/" + "SharedDataSource";
            ds.Item = (DataSourceDefinitionOrReference)reference;
            // Get original report Data Source Name
            DataSource[] reportDataSource = service.GetItemDataSources(@"/" + reportsFolder  + @"/" + reportName);
            ds.Name = reportDataSource[0].Name;
            return new DataSource[] { ds };
        }

4 comments:

  1. Thanks man, this was very helpful for me.
    Keep up with your excellent work ;).

    Cheers

    ReplyDelete
  2. well done dude and thanks so much..) i was looking for this for a long time..

    ReplyDelete
  3. I am unable to sue same code on sharepoint site which is configured in ADFS claim mode.

    ReplyDelete
  4. Is there any way of querying reporting server for specific datasource for a report. Issue is we have 100s of reports with around 5 datasources. The datasources are named similarly, only difference is the folder where they are created in and of course the connection string.
    /BusinessUnit1Folder/DataSourceName1
    /BusinessUnit2Folder/DataSourceName1
    /BusinessUnit3Folder/DataSourceName1

    Is there a way to extract the actual datasourcename/foldername for specific reports?

    ReplyDelete