Christa Carpentiere has done yet another implementation of reporting off an ADO.Net Dataset but this time it's in an MSDN article, and it's in both VB.Net and C#.

This one add something interesting though, as it also shows how you might call an external assembly to generate the DataSet.

Toby Riley emailed me to alert me to his extension that can take a select query and execute it against any DB using a connection string as a parameter and pass the data back to the designer:

http://workspaces.gotdotnet.com/appworld

Another blogger of note has also come online,  lead developer for the Reporting Services product by the name of 'Tudor'.

In my previous post I stated that I had succeeded in converting a Reporting Services Data Processing Extension for reporting on ad-hoc data from a serialized ADO.Net DataSet into VB.Net. I have since extended this with an extra parameter (IDataParameterCollection) to allow the report designer to define at design-time (or indeed at run-time) which DataTable within the DataSet should be used as the source table. The implementation of DataProcessing.IDataReader also now iterates over a DataView of the table rather than the DataTable itself thus allowing me to pass a third parameter (actually passed via the Generic Query Designer as the command text (IDbCommand.CommandText)) which I then use as the RowFilter for the DataView.

This all works great. I can supply the parameters which I have told the extension to request from the designer (via the implementation of IDbCommandAnalysis.GetParameters method), and the query designer screen returns the data into the results screen, but if I attempt to navigate to the report layout tab, or I click on the 'Refresh Fields' button within the Generic Query Designer, I get an exception.

I deliberately decided to Debug.Assert that the @DataSource parameter should not be empty, but it should not be empty anyway as I have already supplied it via the designer. Following my debug trace information I can see that ExecuteReader in being called, but that the parameters are being passed as empty strings. If I elect to ignore the error then I can see via the trace that ExecuteReader gets called a second time, this time passing the parameters as I have entered them, and all is well.

The solution of course is not to require there to be non-empty parameters, but surely this is a bug?

UPDATE: after lots more detective work I have discovered that presumably the Report Designer graciously handles any ArgumentExceptions, and just carries on and attempts the ExecuteReader a second time.

How did I discover this? I turned off my assertions and just let the code proceed, the code gets as far as trying to load the DataSet:

Try
   dataset.ReadXml(dataSource)
Catch ex As System.Exception
   Trace.WriteLine(String.Format("Error Reading XML into dataset: {0}:{1}", ex.Message, ex.GetType))
   Throw ex
End Try

but inevitably it fails because the dataSource parameter is a blank string, so I catch an ArgumentException and write a message to the trace as follows:

Error Reading XML into dataset: The path is not of a legal form.:System.ArgumentException

The Report Designer then carries on and repeats the call to ExecuteReader, this time passing the correct parameters and everything goes smoothly.

So, still a bug in my opinion (unless anyone can enlighten me otherwise) - but it works!

UPDATE 2:

Robert Bruckner has now kindly enlightened me as follows (see also this thread):

If you actually execute the query when ExecuteReader(SchemaOnly) is called
then the implementation is incorrect. For SchemaOnly execution, no parameter
values are passed in because no query should be executed - just the columns
should be returned.

When Refreshing Fields, the report designer first tries to determine the
fields based on ExecuteReader(SchemaOnly) because this should not affect any
database state (and it is cheaper). If SchemaOnly fails, then report
designer tries to actually execute the query and determine the fields based
on the returned dataset. In order to execute the query it has to pass in
parameter values.

Having decided to use Windows SharePoint Services (WSS) as a company-wide document repository I'm thinking I'm probably going to need to report on the documents and list content (in combination with data from other enterprise systems) and not just rely on SharePoint Search, so Reporting Services seems like a sensible option. Clearly though RPTSVCS can't do this out-of-the-box so I'll need to write a data processing extension. So I thought I'd see if anyone else had tried so far...

Other than what I read in this thread, and this one so far no-one has written an extension for accessing a web service (or at least, if Bryan Keller hasnt found it the I doubt it's out there yet). So I doubt theres one floating out there for accessing info in WSS and I'll need to roll my own. I found these links which I think will prove useful, any other pointers would be welcome:

File Share Extension
XML Data Extension
Clarification on the Placement of CodeGroup Elements for Extensions
How hard is it to write Reporting Services extensions?

If I manage it I'll post and article here!

UPDATE: Ohad has listed some other useful links including two for extensions for custom ADO.Net DataSets by Gavin Joyce and by a GotDotNet community

UPDATE 2: I have converted the original GotDotNet sample to VB.Net and it works! Now to write one to work with web services and ultimately with WSS lists so I can report on WSS lists to my heart's content...

I had been reading in Bryan Keller's post "How hard is it to write Reporting Services Extensions?" 

The documentation for Reporting Services includes a sample, however, that sample does not cover the use of data extensions that handle parameters. This is the only area where it gets confusing. Hopefully we will include a more comprehensive sample in the future.

...but I didnt need to search too far to find Gavin Joyce's post with some sample source code for doing just that.

Now to try it out for myself!

In reply to Tom Rizzo's post Bryant Likes questions why you would want to export a report to XML, and Tom provides a useful reply. I'd like to add how I found this feature useful too:

Lets say you have a report where, for the sake of readbility, you have indented some of the text, and you also have quite a bit of grouping going on. Lets also say you have a user who likes playing around with the data in Excel using pivot tables and all that kind of stuff, and to be honest, you would rather leave them to decide how they want to arrange the data rather than trying to get a fixed spec off them which might not then suit somone else's needs. If they export direct to Excel, they get the data arranged in the same grouping and indenting and they can't easily manipulate it. If however they are willing to do this in two steps (ok, you'll need to give them a bit of training) they can export to XML, then import in to Excel 2003 and they can pick and choose the fields they want to display, and where, because Excel can work out the schema all by itself (well, sometimes!).