Adding ad-hoc filters to generating a report

If you’ve looked at our new AutoQuery B.I./ad-hoc program, you’ll see that it is built on our reporting engine. But it has added the capability to change the filter in the select to change what data is displayed in the generated report. All very cool.

One of our customers saw it and immediately asked if we could make the API in the engine we use for this functionality public as they want to build an additional application around this functionality. I’ll be honest and admit that it had not occurred to us that customers would want this functionality. But after they walked us through what they are going to do, it became obvious that this is a powerful addition to our system. And since it’s already implemented on the .NET side (AutoQuery is Windows only), it isn’t that much work to document the API and add it on the Java side.

And so we’re about to release some super-cool new functionality to our engine. You can create one template and then, based on the value of variables passed to the datasource when running the report, set what rows of data are rendered in the report. For example, you may have a table listing all sales for your company. You can then pass in filters to limit the table to sales in the western region, over the previous quarter. The next run may be all sales for one sale rep. You can filter any way you wish without ever changing the template.

And how hard is this? Well here’s the additional code to select several specific rows of data:

KeyPairDictionary adHocVariables = new KeyPairDictionary();

adHocVariables.Add(“employeeId”, new FilterList(FilterBase.SORT.NO_OVERRIDE, “employeeId”, new object[] { 1, 2 }));

datasource.Map = adHocVariables;

report.ProcessData(datasource, “”);

And here is is for a condition:

KeyPairDictionary adHocVariables = new KeyPairDictionary();

adHocVariables.Add(“employeeBirthDate”,

 new FilterCondition(FilterBase.SORT.NO_OVERRIDE, “employeeBirthDate”,

new FilterCondition.Condition[] { new FilterCondition.Condition(“BirthDate”, FilterCondition.Condition.OPERATION.GREATER_THAN_OR_EQUAL, new DateTime(1948, 12, 8)),

new FilterCondition.Condition(“BirthDate”, FilterCondition.Condition.OPERATION.LESS_THAN, new DateTime(1958, 1, 9)) }, true));

datasource.Map = adHocVariables;

report.ProcessData(datasource, “”);

It makes it incredibly easy to use this new functionality. And I think this will become very popular (essential for many). Because it moves the filtering logic from the template, and removes it from creating a specific select for each query, and instead leaves it that you just define the filter and we take care of the rest.

You can download the C# SQL/XML sample here (requires version 11.0.6 or later).