Blog

Changing Reports’ Connection Strings at Run-Time

Damyan Petev / Friday, July 27, 2012

Still on the subject of creating awesome reports, after sharing the goodness of using HTML5 Report Viewer, it’s only fair to mention another handy feature that came along with the last release. The NetAdvantage Reporting is designed to be extensible by leveraging Managed Extensibility Framework(MEF). That allows for great flexibility via run-time customization and the Reporting is already exploiting some of those extensibility points with run-time data source (sample here)  and URI resolving (sample here) providers. And I’m sure we’ll be seeing more of those in the future , focusing on the latest changes so far – one more provider is now available:

Database Connection Provider

Or simply put – the connection string switch tool. What it allows you to do is change the connection string in runtime – this is useful if you need a report that can swap between two different data endpoint setting sets. This can include different databases on the same server or on different servers; pretty much anything the connection string includes as options, integrated or SQL authorization and even changing between encrypted and non-encrypted connection whether the reports are accessed from the domain or  outside and so on . Come to think of it changing tables provides plenty of functionality alone – swapping tables with same design and different data and our samples show what is probably the best usage of such feature:

Sample Reporting Solution changing connection string to show localized versions of the data.

A single report that can handle out the same data localized by having the database doubled in different languages ( )

Then you also have the cases of separate databases containing similar data like people profile and PTO-s, but from two different organizations with the same accounting company. In such a case the said accounting company could use software with a unified report design and easily change between clients’ data on the fly.

Then again I came up with one more possible use for this feature – BIG DATA. Take AdventureWorks ( ) Order Details in Purchasing. The whole 8800+ of them. I made a Report using that and while it handled it brilliantly (first page loads instantaneously and then you just see the page count growing, responsive UI and all), but even though I have my first 100 pages of report in no time with that much data it takes a while to get up to over a thousand report pages. If you are like me and you don’t like loading as much and there is a way to split the data, then the connection string provider is your new friend – you can group your data in meaningful pieces and then consume it in pieces.

This feature is for the most part available for all Reporting platforms, however, you may notice the documentation mentioning a limitation with Silverlight (due to lack of SqlConnection). That only affects report cases where client-side rendering is used and it is completely usable with server side rendering. Since the official sample above uses server rendering and HTML5 Viewer, the Silverlight version is an interesting one to see along with a desktop demo as well and that’s coming right up.

Walkthrough

DATA

So for this example the Order Details of Purchasing have been chopped down into more manageable pieces – in this case the first and second 500 rows, based on ‘PurchaseOrderDetailID’. Of course, that isn’t what you’re supposed to do – in a real world scenario the data would be split based on say different sales regions, quarters, months or any other way you can meaningfully split reporting data into smaller pieces. Furthermore, the whole point of making reports can be digesting the huge data easily, so this scenario is for when the ready-to-digest report data is too big as well and a way exists to split it without hurting the report itself. Here’s an illustration of how the data looks like in those databases (the new ones only contain the table to be used):

A comparison between the whole table and the two splits of Purchasing’s Order Details

The idea as explained is to have two separate databases with the same schema for the used table – no matter for which of the scenarios described so far.

THE REPORT

Designing the report doesn’t really require too much effort to accommodate this feature – you do need to design it against one of the intended data sources, but that’s ok since the schemas are presumably the same and the data connection string will get evaluated before loading data from the default (the one you designed with) source is used.

The Report Designer inside Visual Studio with the layout and the Data Explorer showing the additional parameter

One important thing to note is that there is a parameter added to the report. This will be used to hold the setting based on which the data connection string is picked. Now if you have looked into the online sample you will notice (as it is a web application) uses session user identity. That goes to show that, while using a parameter is the intended and easiest way without the need for additional coding, it is by far not limiting – whatever you have access to in the respective platform can be used instead. In this case however, the idea is to be able to control this in some way in code and also do that in a non-web environment. For that reason the parameter is used, it is hidden – as the user is not to be bothered to enter a value on every load – and it is nullable so code doesn’t need to provide/handle default value as well:

Report Parameter's options set to allow for null/Nothing values and hide the parameter from the end-user

THE PROVIDER

In the same project as the report (be it the class library or in the case of Win Forms/ WPF you can have all in one place) we need to implement the data connection provider. Like the others it’s an Interface(IReportDbConnectionProvider) you have to implement. Not to worry, of course, there’s a template ready for you to add:

Infragistics Report Database Connection provider template available in Visual Studio.

Then again this particular interface only defines a single method, so not too much work there. For the GetConnection, the Reporting Engine will pass the name of the data source (SqlDataSource1 from above, for example) and the report’s collection of parameters. For the MEF-based extensibility to work the provider needs to be marked with the ReportDbConnectionExport attribute and provide a search patter. Based on that pattern the engine will match providers and reports. Not the template does this for you, except the fact that the default search pattern in a wildcard and will match all reports – so if you have more than one it’s good to edit that one out. Here’s the complete snippet on that one:

  1. [ReportDbConnectionExport(ReportSearchPattern = "Report1")]
  2. public class ReportDbConnectionProvider1 : IReportDbConnectionProvider
  3. {
  4.     public DbConnection GetConnection(string name, IDictionary<string, ParameterValue> reportParameters)
  5.     {
  6.         if (reportParameters.ContainsKey("Option") && reportParameters["Option"].Value != null)
  7.         {
  8.             if (reportParameters["Option"].Value.ToString() == "Part1")
  9.             {
  10.                 return new SqlConnection(@"Data Source=.\sqlexpress;Initial Catalog=advWrksP1;Integrated Security=True");
  11.             }
  12.             if (reportParameters["Option"].Value.ToString() == "Part2")
  13.             {
  14.                 return new SqlConnection(@"Data Source=.\sqlexpress;Initial Catalog=advWrksP2;Integrated Security=True");
  15.             }
  16.         }
  17.         // will fall back to the default (designer) connection
  18.         return null;
  19.     }
  20. }

Report1 is obviously the name of the report file and the connection strings are to the two databases shown above. As explained, you can provide all sorts of strings here.

THE SILVERLIGHT SOLUTION

NetAdvantage Reporting is nicely integrated with Visual Studio, so there really isn’t much work to be done. The designer makes creating the report structure very easy and along with adding the report to the solution it will display a dialog asking if you want to be able to view this report in Silverlight by adding a Report Service to your Web project. And then all you have to do is add a XamReportViewer to your page and pick an available report:

The XAML version of the easy-to-setup interaction when picking a report for the viewer.

It really is that easy! Of course you could add this manually, the Infragistics namespace ‘xmlns:ig=http://schemas.infragistics.com/xam'l’ and references to the following assemblies:

InfragisticsSL5.Reports.v12.1, InfragisticsSL5.Reports.Client.v12.1, InfragisticsSL5.Models.Presentation.v12.1, InfragisticsSL5.Models.Data.v12.1, InfragisticsSL5.Controls.Reports.v12.1 – all found under Bin in the Reporting installation folder;

are required:

  1. <ig:XamReportViewer Margin="12,35,12,12" Name="xamReportViewer1">
  2.     <ig:XamReportViewer.RenderSettings>
  3.         <ig:ServerRenderSettings DefinitionUri="/ClassLibrary1;component/Report1.igr" ServiceEndpointUri="http://localhost:1221/ReportService1.svc/soapAddress" />
  4.     ig:XamReportViewer.RenderSettings>
  5. ig:XamReportViewer>

In the very same manner ( report and a service in a web application) is how this is done with the HTML sample.

THE DESKTOP SOLUTION

Even though the demo is done in Windows Forms solution, I feel there is no need to create a WPF one as well as the differences are too minor (mostly XAML naming). The approach is very much the same – use the report and provider, add a report viewer to the window, pick that report from the dropdown and enjoy. Again two notes – make sure the report and the provider are in the same project and the app.config either copied over to the project where the report viewer/service is or just the connection string OR you can simple ignore that, as I said the provider will be called first before reverting to defaults and therefore you can return the default connection string there instead of null and ignore the app/web.config part.

THE ACTUAL CHANGE

Finally, you need to set the report parameter at the report viewer level. The Viewers have somewhat different namespaces and mostly identical API-s, say you have 3 buttons this is how the code looks like for the Windows Forms Ultra Win Report Viewer:

  1. private void button1_Click(object sender, EventArgs e)
  2. {
  3.     this.ultraReportViewer1.Parameters.Clear();
  4.     this.ultraReportViewer1.Parameters.Add(new Infragistics.Win.UltraWinReportViewer.Parameter { ParameterName = "Option", ParameterValue = "Part1" });
  5.     this.ultraReportViewer1.RefreshReport();
  6. }
  7.  
  8. private void button2_Click(object sender, EventArgs e)
  9. {
  10.     this.ultraReportViewer1.Parameters.Clear();
  11.     this.ultraReportViewer1.Parameters.Add(new Infragistics.Win.UltraWinReportViewer.Parameter { ParameterName = "Option", ParameterValue = "Part2" });
  12.     this.ultraReportViewer1.RefreshReport();
  13. }
  14.  
  15. private void button3_Click(object sender, EventArgs e)
  16. {
  17.     this.ultraReportViewer1.Parameters.Clear();
  18.     this.ultraReportViewer1.RefreshReport();
  19. }

As for Silverlight/WPF Xam Report Viewer the Parameter type is in a different namespace and the viewer’s method is simply Refresh:

  1. private void Part1_Click(object sender, RoutedEventArgs e)
  2. {
  3.     this.xamReportViewer1.Parameters.Clear();
  4.     this.xamReportViewer1.Parameters.Add(new Infragistics.Controls.Reports.Parameter { ParameterName = "Option", ParameterValue = "Part1" });
  5.     this.xamReportViewer1.Refresh();
  6. }
  7.  
  8. private void Part2_Click(object sender, RoutedEventArgs e)
  9. {
  10.     this.xamReportViewer1.Parameters.Clear();
  11.     this.xamReportViewer1.Parameters.Add(new Infragistics.Controls.Reports.Parameter { ParameterName = "Option", ParameterValue = "Part2" });
  12.     this.xamReportViewer1.Refresh();
  13. }
  14.  
  15. private void Clear_Click(object sender, RoutedEventArgs e)
  16. {
  17.     this.xamReportViewer1.Parameters.Clear();
  18.     this.xamReportViewer1.Refresh();
  19. }

As you can see all you have to do is change the report parameter’s value ( I clear and add every time to skip checks, probably better ways to handle) and refresh the whole report, which causes yet another call to the data connection provider and this time with parameter present a different connection will be returned. And that’s how you change connections in run-time with very little effort.

Wrapping Up

Changing Infragistics Reports Connection Strings at Run-Time is an awesome new feature available for NetAdvantage reporting using Windows Forms, WPF, Silverlight ( server-side rendering) and web application with HTML viewer. There are plenty of situations where changing a connection string can provide functionality even with only considering the actual options the strings have and then considering you can change databases with truly practical applications as a single report handling localized databases, business software generated (such as accounting) databases with identical schema and completely different organizations, regions; if the requirements permit it can be used to reduce the total size of the report by splitting the source or perhaps even change between databases if one goes down and so on! Those are only a few uses and I bet you can come up with your own, so links below can get your started on tinkering a future reporting solution:

First off for the data – the two splits of Purchasing’s Order Details you can find here. Then you have the  Windows Forms demo (this one uses the original AdventureWorks as original source, you can download following the link mentioned ( )) and then the Silverlight demo (only using the two databases). Check out the help documentation and pay a visit to our online samples for more reporting awesomeness. As always, you can follow us on Twitter @DamyanPetev and @Infragistics and stay in touch on Facebook, Google+ and LinkedIn!