Dynamically determining the SQL statement?

Hello all,

I am working with a client who will be creating "template" report for users to be able to customize. The customization settings will be stored in meta data on a SQL Server somehere.

Part of the customization features will allow users to filter the data that they view. There may be 20 plus filters on any given report and they may want to filter in many different ways (i.e. sometimes to an Equals, other times an IN (), other times a LIKE, etc...), but only one way per field, per customization.

With this many possible filters, I want to avoid having the report authors be responsbile for coding these as parameters on the CommandText node (it is just using a SQL SELECT statement).

Well, let me get to the question...What is the best way to dynamically set the Report's CommandText or "main" dataset (and I guess while I am at it, the main datasource/connection information).

BTW...currently using SSRS 2005 + ASP.NET 2.0.

Thanks for your help!!

-BrianYou can create a function (sketch) like this:
public static string PrepareStatement(string templateSql, string userID)
{
string userFilters = GoGetUserPrefsFromDB(userID);
return templateSql + " WHERE " + userFilters;
}
Put it into a custom assembly, ref the assembly from your reports and have
<CommandText>=MyClass.PrepareStatement("select * from qqq", User!UserID)</CommandText>

Make sure you grant/assert CAS permissions.|||That is a good idea, the only problem with that I see is that it might be difficult to debug since the rendering engine is going to be responsible for creating the parameters...no to mention the client would have to maintain and deploy this assembly to each reporting server in the SSRS farm...

I guess the *best* way to set the command text and datasource from the front end is the use parameters?

Are there any other possible solutions?

Thanks!

-Brian