Reporting Services, RDL, and filters

[Arjay]

Our clients are using SQL 2005 Report builder to generate queries based on the data model we've supplied (the env is Sql 2005 and VS 2005, .Net 2.0).

The client is quite happy that they can create the report and we can programmically display the report using the report viewer in Asp.Net. Everyone is happy, almost.

What we want to do is to take the report they've created, dis-assemble it and use the query (or filter) from the rdl to perform some other database operations using the filter portion of the query.

I know I can retrieve the rdl and load it up into the Reports class (created by running xsd on ReportDefinition.xsd) as follows:


privatestaticvoid LoadReportDefinition( )
{
ReportingService2005 rs = newReportingService2005( );
rs.Credentials = System.Net.CredentialCache.DefaultCredentials;

string reportPath = "/EmployeeReport";

// Retrieve the report definition from the report server
byte[ ] bytes = rs.GetReportDefinition( reportPath );
Report report = null;

if( bytes != null )
{
XmlSerializer serializer = newXmlSerializer( typeof( Report ) );

// Load the report bytes into a memory stream
using( MemoryStream stream = newMemoryStream( bytes ) )
{
// Deserialize the report stream to an instance of the Report class
report = ( Report ) serializer.Deserialize( stream );
}
}
}


From there I can drill down and retrieve the CommandText which contains the filter.

What I'm looking to do is take that filter command and convert it back into the sql query that I can run against the db.

Is there a way to do this programmatically or do I have to derive it on my own based on the filter elements?

[Arjay]

I ended up loading the CommandText into a SemanticQuery serialization class that I wrote. It was a bit of a pain because xsd.exe and XsdObjectGen.ext can't create recursive classes very well so I needed to hand write everything. In all this worked out better because the xsd.exe generated classes were over 2500 lines long (as compared to the hand written code that came in at 372).