Passing variables to SubReports in Telerik Reporting

Anytime you have created a complex report with embedded sub-reports, you might have faced the need to pass a variable from data in the the master report to a sub-report. I have seen this problem solved various ways with differing degrees of unnecessary complexity.

The biggest mistake made in all the code I have seen, is to assume that one must pass the variable through existing mechanisms provided by the Telerik Report class. I think this reply from Steve @ Telerik sums it up best: "Telerik Reports are standard .NET classes so you can abstract yourself from the fact that you're working with reports and achieve your requirement". In other words, use .Net and standard OOP principals without relying on being spoon-fed every feature your project requires.

One of the more interesting solutions I have seen implemented was this one, which was probably not intended as a solution for passing parameters from reports to sub-reports, but which the developer managed to getting working anyhow.

Basically, the common approach people use is to pass the variable using the report parameters collection, which while not wrong, simply takes more code than is necessary.

I find that the most graceful solution is possible when does what Steve recommends and abstracts oneself from the fact that your're working with reports and remember that you're working with standard .Net classes. So what would you do if you needed to get a variable into a instantiated class. Well, you could give the class a property, but even then you have to instantiate the class, and then set the property. So the other solution is to add the variable as a parameter to the class constructor. "But when I create a report it provides the following code where there is not parameter for my variable!" ... I have been told. What developer was talking about was this constructor created when you add a Telerik Report to a C# project:


        public AccountAllocationsSub()
        {
            InitializeComponent();
        }

True, that constructor has no parameters, but if you understand that you are just working with a standard .Net class, then there' reason you cant create your own constructor which takes the parameter you want to pass. Consider this constructor:


       public AccountAllocationsSub(int transactionKey)
        {
            InitializeComponent();
            this.sqlDataSource1.ConnectionString = SqlConnectionString;
            this.sqlDataSource1.Parameters["@TransactionKey"].Value = transactionKey;
            DataSource = sqlDataSource1;
        }

Now all you need to do is get the data from the master report when it's binding to the controls, and invoke your sub report with your custom constructor:


     private void TransactionGroupHeader_ItemDataBinding_1(object sender, EventArgs e)
     {
            Telerik.Reporting.Processing.GroupSection section =
                (sender as Telerik.Reporting.Processing.GroupSection);
            int transactionKey = 0;
            string strTransactionKey = section.DataObject["TransactionKey"].ToString();
            int.TryParse(strTransactionKey, out transactionKey);
            subReport1.ReportSource = new AccountAllocationsSub(transactionKey);
      }


The biggest advantage I have found using this method is that when I use the preview tab to preview the report, it will call the standard constructor, and I can have that constructor populate the report based on the properties I set for the data object at design time and nicely preview the report.

Then at run time when the report is called from the master report, it will use the variable parameter for the master report and the data the other constructor will set the data based on the variable from the master report.

With the solution developed previously by another developer on our team there was no way to preview the data on the report. In fact using the code from the sample he followed the data-source was specifically neutered unless one was viewing the report at runtime. This was the code in the standard ctor (which is called by the previewer):


public MyTelerikMasterReport()
{
// Required for Telerik Reporting designer support
InitializeComponent();
this.DataSource = null;
if (System.ComponentModel.LicenseManager.UsageMode ==
             System.ComponentModel.LicenseUsageMode.Runtime)
this.SqlDataSource1.ConnectionString = ...etc
}

Getting control of your Telerik Reports

Recently I took over a reporting project from another developer. We basically have multiple reports that had to be ported to Telerik Reports (ver 2011 Q3). Upon inspecting what he had created I noticed the following problems throughout the project:

1. He implemented 4 different ways of connecting to the DB instead of one standardized method for all reports [no, there was no good reason for doing what he did, one standardized method would work for all in our situation].
2. He implemented 3 different ways of retrieving data, using 3 different types of objects instead of standardizing. One method used a standard .Net data object that would attempt to lock all the records in the DB while report was loading, which created major headaches for reports that took a while to generate.
3. He used in-line SQL in the DLL - yes, I know, there are different schools of thought on this subject, but my team has a specific policy that dictates that developer's applications only use stored procedures and this is considered good practice in many circles.
4. He created a query for each field in the report header that had to retrieve common header data.
5. In every report, he had code for retrieving report footer data for stuffing into a text box in the footer of the report.
6. He had no standard convention for naming parameters which were used in most reports. For example, most reports would require a "customerId" parameter, yet the variable would be coded as any one of the following (as an example): cust / customerID / custId / cusomterAccountKey / accountKey / customerAccountNumber -- I guess some people like variety.

To mitigate some of these issues I simply created a class named "StandardFormatReport" which inherits from Telerik.Reporting.Report:

public class StandardFormatReport : Telerik.Reporting.Report

To mitigate the first issue, I added a property to the StandardFormatReport which would return the one and only connection string the app is supposed to use according to the one and only approved method for retrieving connection strings:

        protected string SqlConnectionString

        {

            get { return Conf.GetDBConnectionString(); }

        }

In order to make sure that I can more easily know where to look to audit how the report is setting the data source, I added the following virtual function, which the developer would have to override in his report:

        protected virtual void SetDataSource()

        {

            throw new System.Exception("The report class which inherits from RRFS.Reports.StandardFormatReport must override SetDataSource()");

        }

All I would need to do in a code review process is look for where the developer would override the function to set the datasource, and make sure that he was connecting to the database correctly:

        protected override void SetDataSource()

        {

            this.sqlDataSource1.ConnectionString = this.SqlConnectionString;

            ....

        }

Lastly, the SetDataSource function is the logical place to set the reports parameters, which the developer named every different way under the sun with a string. This drives me nuts, not just because of the lack of consistency, but also because the developer could easily typo the param name and the report would not work. I deplore the using strings where constants should be used instead. To remedy this, I added the necessary constants which would define the standardized way to name params, in the StandardFormatReport class:

        protected static class StandardParams

        {

            public const string CollectionAccountKey = "@CustomerAccountId";

            public const string AssociationKey = "@CompanyId";

        }

Thus instead of the developer using the following code:

        protected override void SetDataSource()

        {

            this.sqlDataSource1.ConnectionString = this.SqlConnectionString;

            this.sqlDataSource1.Parameters.AddRange(new Telerik.Reporting.SqlDataSourceParameter[] {

            new Telerik.Reporting.SqlDataSourceParameter("@CollectionAcountKey", System.Data.DbType.Int32, _collectionKey)});

            this.DataSource = sqlDataSource1;

        }

[notice, I deliberately spelled the parameter incorrectly in this case, because this is highlights the typo issue]

... he would have to use the following code:

        protected override void SetDataSource()

        {

            this.sqlDataSource1.ConnectionString = this.SqlConnectionString;

            this.sqlDataSource1.Parameters.AddRange(new Telerik.Reporting.SqlDataSourceParameter[] {

            new Telerik.Reporting.SqlDataSourceParameter(StandardParams.CollectionAccountKey, System.Data.DbType.Int32, _collectionKey)});

            this.DataSource = sqlDataSource1;

        }

You cant typo that and get your app to compile.

Finally, for all the instances of reports that had certain repeated fields that the developer had coded into each report, I added the following function to the StandardFormatReport class:

        public void SetStandardFields()

        {

            Telerik.Reporting.TextBox tb = null;

            

            tb = this.Items.Find("txtFooterDate", true)[0] as Telerik.Reporting.TextBox;

            if (tb != null) tb.Value = "Information as of " + DateTime.Now.ToString("mm/dd/yy");

            tb = this.Items.Find("txtFooterNote", true)[0] as Telerik.Reporting.TextBox;

            if (tb != null) tb.Value = GlobalFunctions.GetFooterNote();

            tb = this.Items.Find("txtFooterAddress", true)[0] as Telerik.Reporting.TextBox;

            if (tb != null) tb.Value = GlobalFunctions.GetFooterAddress();

        } 

Then in the ctor for the StandardFormatReport, I simply add the call to SetStandardFields, and if they exists with the correct names, they will be set, this eliminating the need to have this coded in every single report -- standard DRY compliance issues.

There is more that one could do to put standard functionality into a class that derives from Telerik.Reporting.Report and enforce standardization through everyday OO programming, but I wont go into detail. The point is this: just because the Report Wizard will create a Telerik.Reporting.Report class for you in Visual Studio, it does not mean that you have to use it as is. It's still basically a C# object, subject to all the benefits that come with the OOP feature C# offers. Since most reports for business applications usually need common functionality, it only makes sense to put all that functionality into one custom-class that derives from Telerik.Reporting.Report, then have your developers create their reports by deriving from your custom-class.

I would argue that if you have a large reporting project where there are many typical elements (a common logo, same size, position, etc) required on just about every report, and you're not doing it this way, but repeating your code per report instead of inheriting from a class that already has the correct code, then you're doing it wrong.

So you think you know Software Engineering?

Someone emailed me this. And I have to say I agree with it:

Common problems in the business world (from what I have experienced):

*everyone* thinks they are a developer/software designer
*everyone* thinks they could run IT and the software staff (and thinks they should!)
*everyone* thinks that innovative solutions can be scheduled, and thus can have predictable end dates.

I have to add to that however, that in my experience, none of these people can even do job #1, which is to say, document properly.

Pervasive DI Update for version 10.2.4.47

We finally have the memory leaks which I reported months back, fixed in this release. Now we actually have something usable.

Apparently there were 2 separate memory leaks. One in Java code and another in native C code for the DJImport object. The Java code memory leak was in the the XML-DMS connector. This is what's used when you're pulling data from an XML data source.

Basically what all this means is that if you had a project prior to this release that iterated many XML files to read data from them .. and if you were doing a couple of lookups using the DJImport object while you were processing the map that read the data from the XML file ... you would have run into trouble.

During development, we were processing about 5,000 files, about 250 KB each, through the XML-DMS connector in a map. The map itself would use the DJImport object 7 different times, per transformation. So our job was using the DJImport object 35,000 times in the main processing map and few more times elsewhere.

The first run of the job takes about 70 to 80 minutes depending on resource conditions. Subsequent runs of the job take about 30 to 40 minutes because we allow the map to process XML files it's already processed. After running the job just once we would notice that 2GB or more of system RAM would be used up and not released. Running the job again would take up another 2GB+ of RAM, and so on, until your server ran out of RAM altogether. This is no longer the case after this release. After the job completes, the entire block of RAM that the job was using would be returned to the OS.

There are still other bugs that I reported but received no feedback on, but at least we have something can we push to production now.

Ongoing Woes with Pervasive Data Integrator 10

UPDATE: 2012-05-08

Pervasive tracked me down to where I work (they have even been inspecting my LinkedIn profile) and complained to the company I work for expecting them to lean on me to remove my blog. Apparently they don't like that I have been blogging about the technical defects in their product and that I find their support to be sluggish (save for the fact that they have at least 1 great support person I was eventually able to track down, that's been very sympathetic to our Pervasive Data Integrator issues).

Fortunately I am employed by a magnificent company, run by a benevolent and just CEO who believes in and respects freedom of speech (provided it's fair and based on accurate data). Since everything I've blogged about these Pervasive issues is substantiated by emails and documentation I have no reason to remove my blog. The information is accurate as of when the blog was first posted. To be fair, I have also gone back and noted some things they have fixed.

That being said, I did however remove a non-technical post which I used to have on this page. It was simply a non-technical blog about me ranting over my horrendous support experiences with Pervasive. Out of deep respect for my employer, I will keep my posts about Pervasive's Data Integrator on technical matters.

Pervasive DI Update for version 10.2.4.39

Although not officially released yet, we got early access to version 10.2.4.39 which is supposed to resolve a grave error in XML schema validation. Unfortunately that version does not resolve the bugs they said it resolves. Mind you, they gave us this version specifically because it was supposed to fix the bug.

To make matters worse, I'm finding that there are even more bugs. For example you can't use the built-int LogMessage function from within your own UDF. You wont get any errors. It will just not log anything but instead just silently fail.

With this new version also, we have found that half the time, downloading process logs will give you some HTML mark-up (for wrapping some Flash code) instead of the actual logs you're trying to get to. As a result we've had to come up with yet another kludge - write our own logging mechanism.

What did they fix since the last time I blogged about this broken product. Well, you can actually get back to a running job in the Integration Manager and stop it if it's currently running. Unfortunately you still can't control jobs in the queued. i.e. there is no way to remove an individual job that has been queued. Not even removing it from the schedule will remove it from the queue. To make that issue worse, if you need to run a job immediately it will place it in the queue behind the jobs which have already been queued - regardless of when those other jobs in the queue are scheduled to run. So imagine this scenario: We have a job scheduled to run at 6PM and another at 9PM. Someone in the company decides that they want to run 3rd job that is normally scheduled to run at the end of the month, ... now. No harm will come from running it early, we just want to run it earlier than normal. So we click the "Run the integration immediately" button and expect it run immediately, right?

Nope. Instead for some odd reason it queues the job. So I have to tell the supervisor who requested the ETL to hang tight till tomorrow because PDI decided the "immediately" means "queue" for later.

The Pervasive Data Integration Server is a Java server app which by default uses only 64mb of memory. Increasing that value should resolve the 'Java Heap Space' errors that we have been getting from PDI. To implement this setting one must follow these steps:

1. Locate the PDI installation folder and within that, a folder named "runtime"
2. Locate a file within the "runtime" folder named "cosmos.ini"
3. Open the "cosmos.ini" file in a text editor and locate a section named [UserInfo]
4. Change the default memory allocation by adding a line under the [UserInfo] section as "JvmArgs=-Xmx256m" (or some other valid new memory value

[UPDATE 5/18/12, the original tech provided us the wrong information. The correct information is]:

1. Locate the PDI installation folder and within that folder is a folder named "etc" which you need to navigate to.
2. Locate a text file named "com.pervasive.execution.worker.cfg" and open it with a text editor. There you should find the following line: "#engine.java.options="
3. Remove the hash (#) and add your own -Xmx JVM arg there.

Note: This will affect all your worker processes, so take care because if you have a host with 8GB of RAM and you set your -Xmx setting to 4GB each job you run will use the 4GB.

Implementing this setting may not be effective for your situation. After trying this fix we have found that we are still plagued by JVM memory leaks our main PDI project.

More general info about increasing memory for Java using Xmx parameter can be found online.