Wednesday, March 28, 2012

ReportViewer control and Aggregate RDL Expressions

I've been successfully using the WinForm ReportViewer control to view reports from relational databases. I'm starting to investigate viewing reports based on Analysis Services 2005 multi-dimensional sources.

From my reading (including the following thread http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1432935&SiteID=1), it seems that the RDL Aggregate function is key to getting proper results in expressions.

The RDL spec indicates that the Aggregate function behaviour is tightly coupled with the Data Processing Extension (DPE). This is fine for web-based Reporting Services, but with the ReportViewer control, we do all data processing and provide a .NET DataTable object that the viewer consumes and evaluates expressions from.

How does the WinForm ReportViewer handle evaluating the Aggregate function when there is no DPE to talk to?

Thanks!

This has nothing to do with "web based" versus "winform based". There are two versions of the ReportViewer control, one for web forms and one for win forms, and they both handle getting data from a report server versus handling the data and rendering locally (using an RDLC).

The answer to your question is that the local processing mode does data processing, IOW the internal calculation of values, including aggregates, should be the same as what you would get from server processing mode. So I'm not sure why you're worried about this.

I think you might be a little confused about what a Data Processing Extension is compared to the part that I'm saying is also done by the local mode. One is "what am I going to do to get data out of a data source". The other is "once the data is held in datasets as resources for the reports, how am I going to process and calculate expressions on those rows". The aggregate functions belong in the latter set, so I would say, no, there is no tight coupling that could concern you.

>L<

|||

Indeed, I am confused Smile

Here's a more concise description of my scenario: a WinForm ReportViewer control running in local processing mode. The hosting application populates the ReportViewer.LocalReport.DataSources on the ReportViewer's behalf, the viewer renders the report.

The "tight coupling" I worry about is regarding http://technet.microsoft.com/en-us/library/ms155830.aspx, i.e., the Aggregate function "returns a custom aggregate of the specified expression, as defined by the data provider" (my emphasis).

The way I understand this, the report processor consults the Data Processing Extension for help in interpreting the result set via the DPE's IDataReaderExtension interface, if implemented (http://msdn2.microsoft.com/en-us/library/microsoft.reportingservices.dataprocessing.idatareaderextension.aspx). Please correct me if I'm wrong.

In my local processing mode scenario, the ReportViewer doesn't consult any data provider to get aggregate information, so I don't understand how it figures out values for the Aggregate function.

First question: what exactly is IOW? Smile

Second question: how does the local report processing figure out the RDL Aggregate function? I understand simple aggregates like Sum & Count, but what about the Aggregate aggregate function.

|||

IOW = "in other words".

I think (re: your emphasis) that you are misinterpreting what you are reading. How exactly do you think the custom aggregate comes into place and is not available to you in an RDLC, where your code is the data provider <s>?

It is not really true that "the ReportViewer doesn't consult any data provider". When it's in local processing mode it's consulting the data provider that you gave it references to... It is asking this provider for rows in whatever way is necessary to evaluate content and evaluate expressions during the processing of the report.

Am I still missing whatever it is that is concerning you?

>L<

|||

So, I am the data provider because I provide the data. Fair enough Smile

In the ReportViewer local processing case, I fetch data in my own code, and I execute the query and populate a System.Data.DataTable and pass that into the ReportViewer. Here's code snippet:

string dataSetName = "dataSetName"; //The name of the RDL Data Set in the Report

System.Data.DataSet data = ExecuteQueryAndGetData(); //My method to execute the query

_reportViewer.LocalReport.DataSources.Add(new ReportDataSource(dataSetName, data.Tables[0]));

So the data fetching code runs, gets the data rows, and then I toss it into the ReportViewer.

Now, consider the following rowset, which I copied from http://msdn2.microsoft.com/en-us/library/microsoft.reportingservices.dataprocessing.idatareaderextension.isaggregaterow.aspx:

City State Sales
====================================
Seattle WA 150.65
Seattle (null) 150.65
Tacoma WA 75.54
Tacoma (null) 75.54
(null) WA 226.19
Portland OR 112.25
Portland (null) 112.25
(null) OR 112.25
(null) (null) 338.44

The rows in blue have aggregates. From what I understand, RS would need assistance from the DPE in order to understand that the following result set includes aggregates, via the IDataReaderExtension interface (that's what the documentation page is talking about).

I understand that the RS's OLEDB-MD DPE does just this by implementing the IDataReaderExtension interface that I mentioned before.

I don't implement the DPE interfaces in my application at all -- I only give the ReportViewer a result set of rows in a DataTable. So, if I am a data provider for an Analysis Services data source (of course I'm not OLEDB-MD), how exactly do I tell the ReportViewer which rows/fields contain aggregates?

Can ReportViewer really figure this out from the DataTable rowset alone?

|||

>>

I don't implement the DPE interfaces in my application at all -- I only give the ReportViewer a result set of rows in a DataTable. So, if I am a data provider for an Analysis Services data source (of course I'm not OLEDB-MD), how exactly do I tell the ReportViewer which rows/fields contain aggregates?

<<

If YOU don't provide a source with custom aggregation, it is not going to see custom aggregation.

Look: I don't know for sure that ReportViewer's data processing implements IDataReaderExtension. If you look, I think you may see that you can't set a data source using the the analysis services provider in local mode (I went through the wizard and tried -- it isn't showing there although I have the drivers etc). This may just mean that they are ensuring that, when they do their behind-the-scenes wizard-binding to a plain-vanilla DataTable, the ReportViewer does not have deal with it <shrug>.

OR -- and this is equally likely, in fact more so because I doubt they re-implemented the whole data processing engine just for ReportViewer -- the local processing engine may in fact implement IDataReaderExtension but DataTable, their out-of-the-box host, isn't up to the task so it's not in the wizard and the out-of-the-box binding stuff.

Pay attention to the line of code you quoted:

new ReportDataSource(dataSetName, data.Tables[0]));

Look at the constructor for the object you just created. Where does it say anything about the second arg being a DataTable?

An RDLC can use an object data source.

An object data source can contain nested data (see http://blogs.msdn.com/tudortr/archive/2006/04/10/NestedObjectsDataSource.aspx for an example). If you can nest, you can provide aggregate functionality for what you have nested.. you see where I'm going right?

>L<

sql

No comments:

Post a Comment