SSRS Loop Through Report Datasets and Modify at Runtime

David_41

Hello all. I have a C# Winforms application. A form has a report viewer control. The Reports and Stored Procedures exist on the report server. Right now we can run the application and display the reports just fine. Everything is working. But now we have a new requirement. The reports display several tablix controls and each tablix is connected to an embedded dataset, which in turn gets is data from a stored procedure. The new requirement is to point the dataset to another stored procedure. The C# application already has the following capability. It reads the SQL from an existing SP. It adds a WHERE clause to the query and then saves the modified query as the same stored procedure name with _modified at the end of the name. For example, an original stored procedure is called SP1. The new stored procedure is SP1_modified. The modified SP has a where clause in it. So, I want to change the embedded dataset (SP1) to point to SP1_modified. The dataset names are the same as the stored procedure names. After the report is rendered in the reportviewer control, I want to change the datasets back to point to SP1, for example. The _modified stored procedures will be deleted from the server. We can already do most of this. The only thing I have not figured out is how to point the embedded dataset to a different stored procedure. Thanks for your help.

Lost User

You can configure a dataset before "loading" it. The SSRS object model's dataset has a Query property used to specify: a stored procedure; text command; whatever. Just like in ADO.NET.

David_41

Hi Gerry. Thanks for your reply. That is good news. I figured that there had to be a way, but I am still a little confused about how to set the values. Our reports can have multiple Embedded datasets. Using SQL Server Management Studio, I have browsed the tables and System Views and cannot find anything that would give me a clue about what Embedded datasets are defined in each report. So I guess in C# I need to get a reference to a report on the server and then it would be really nice if I could create a foreach loop and loop through each of the datasets that are Embedded in the report and change the stored procedure or SQL as you stated. Can you help me figure out how to get a reference to the report and loop through its Embedded datasets? Thanks!

Lost User

I don't know what your run-time looks like. Sometimes the dataset is created in code (Windows Forms); sometimes there's an XML definition (BI Studio; DevExpress; SSRS). In any case, a global solution find on "dataset" will usually locate the references. For code, you usually do any customizing in the constructor of the report's "host"; that usually gets run before any related generated code access. For XML, you can pre-parse the definitions (using say LINQ-to-XML) before running the report.

David_41

Hi Gerry. Thanks for your feedback. I have actually come to a decision as to how to handle this. I have started the work on this, but I haven't finished it yet. I will come back and post my solution once it is finished. Basically my plan is to: download the report as an rdl (XML file) to local computer in folder under .exe. (This works). Modify the XML with the _NEW stored procedures. (Working on this) Save the rdl as OriginalName_New.rdl Upload OriginalName_New.rdl to server. (This works) Render OriginalName_New in the report viewer control. Once the user closes the form, delete OriginalName_New from server (This works) Delete all of the _New datasets from report server that were used by the report. (This works) Delete rdl file. Thanks.

Lost User

Sounds great! Glad to hear things are moving along. You now also have a convenient way of "logging" user activity to the server.

David_41

Thanks. Here is the code which handles modifying the Embeded Dataset to point to a different stored procedure. This code requires that you add a web reference to ReportingService2010. What this code does not show is the following: Earlier in the code we read the SQL from a stored procedure and allow the user to pick some criteria and add a WHERE clause to the query. The new sql is then saved to a new stored procedure which includes the name of the original stored procedure plus _New. So the goal here is to get the report to use the _New stored procedures. The datasets are embedded and must be repointed to the _New stored procedures. The following code runs everything. At this time, the user has selected the report that they want to view.

string LocalPath = "";

        if (DownloadReport(reportViewer1.ServerReport.ReportPath, out LocalPath))
        {
            string newPath = "";
            if (LoopThroughDatasetsXML(LocalPath, out newPath))
            {
                CreateReports(cbMOE.Text + "\_New", newPath, reportViewer1.ServerReport.ReportPath);
            }
        }

private bool DownloadReport(string reportPath, out string LocalFolder)
{
bool ret = true;
ReportingService2010.ReportingService2010 rs = new ReportingService2010.ReportingService2010();
rs.Url = "http://" + m_dbSettings.serverName + "/" + m_dbSettings.reportDbName + "/ReportService2010.asmx";
rs.Credentials = System.Net.CredentialCache.DefaultCredentials;

        byte\[\] reportDefinition = null;
        XmlDocument doc = new XmlDocument();

        try
        {
            reportDefinition = rs.GetItemDefinition(reportPath); // Get the report from the server
            MemoryStream stream = new MemoryStream(reportDefinition);

            //start building the path to save the report to the users computer
            reportPath = reportPath.Replace(@"/", @"\\");
            
            LocalFolder = Directory.GetCurrentDirectory();                

            LocalFolder = LocalFolder + @"\\TrialData\\DataAccess\\Reports" + reportPath + ".rdl";
            string Folder = Path.GetDirectoryName(LocalFolder);

            //if the folder doesn't exist, create it
            if (!Directory.Exists(Folder))
            {
                Directory.CreateDirectory(Folder);
            }

Lost User

Looks good. I would suggest making things more "fine-grained" / modular (once prototyping is complete). There are opportunities to create: a "dataset parser"; a "report creator"; etc.... that will make reusability and streaming easier / simpler. You may find some of this should happen on the server and not "off-loaded".

David_41

Thanks Gerry. Yes, I am still tweaking things a bit, but it initially looks good and is working. Take Care, David

Lost User

You're welcome!