[1] My own preference is to house all the DDS parameter data within one set of tables. You do not need to add more tables to your schema when a new report subscription needs to be set up, and all your queries to drive subscriptions are similar. [2] Generally the only fields I explicitly pass through as text are dates (in YYYY-MM-DD format) and actual text parameters. This is because of date format issues - passing a date in the mentioned format always works, no matter what locale settings are applied to the report itself, or the report server. (There are some issues around this).

Thanks for your reply! what do you mean by "one set of tables?" How do you pass params of different types if you're not adding new tables or columns?--ACG

I have to create a data driven subscription database which will store subscription information for many different reports. These reports have different numbers of Parameters. BOL only has a very basic example of a DDS database with one table which does not fit my needs. Some questions: 1: Is it a better practice to create one table for each different report where I could have parameter type granularity for a particular report or is it better to have one large table housing all report subscriptions and convert the types in the DDS query? I would think the first option is best. 2: In the DDS tables do we define the types for the parameters based on the type defined in the report ie. Text, Integer, Date, Boolean, Float, OR should we define those types based on the actual types in the report's dataset?? I ask because I usually set everything to Text in the report and let it do the conversions. BOL is not clear on this. Thanks!!--ACG

Thanks very much!--ACG

You can have a parameter table like so (you could extend this to use a ReportID etc in a normalised way but this is just an example showing it can be done within a single table) :- [ReportName], [InstanceID], [ParamName], [ParamValue] ------------------------------------------------------------------------ 'TestReport1', 1, Date, '2010-11-01' 'TestReport1',1, ClientID, 27 WITH DateParams AS (SELECT InstanceID, ParamValue, ReportName FROM ReportParamExample WHERE ParamName = 'Date' ), ClientParams AS (SELECT InstanceID, ParamValue, ReportName FROM ReportParamExample WHERE ParamName = 'ClientID' ) SELECT DateParams.InstanceID, DateParams.ParamValue AS DateParam, ClientParams.ParamValue AS ClientParam FROM DateParams INNER JOIN ClientParams ON DateParams.InstanceID = ClientParams.InstanceID AND ClientParams.ReportName = 'TestReport1' AND DateParams.ReportName = 'TestReport1' [Instance ID], DateParam, ClientParam -------------------------------- 1 2010-11-01 27 Obviously the disadvantage of such a model is a new join needs to be created to the query to add another parameter type. You can do whatever conversions you need from the paramvalue, or you can add paramvalue columns for each data type you want.