DDS - multivalue param sourced from dataset, single export report

Question

Good afternoon,

I've read through a plethora of different threads on the subject of multi-value parameters in data-driven subscriptions, but couldn't find recent explanations relevant to my specific issue.

I have a report with a parameter called @ClientCode, which is of type text, multi-valued, blank/null not allowed, and has available values sourced from a dataset; no default values are used for this parameter.  Previously, I successfully set up a DDS which created a separate export report for each client code value contained in the list; this was accomplished with the below query, with the [CLIENTCODE] column being passed to the parameter:

SELECT DISTINCT

'Datamart_BillingReportDetail_' + ClientCode AS [REPORTFILENAME],

ClientCode AS [CLIENTCODE],

DATEADD(MONTH, -1, DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0))
AS [STARTDATE],

DATEADD(MILLISECOND, -3, DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0)) AS [ENDDATE]

FROM TableA

This worked well in the past; however, I now need to have the subscription run the report for all client codes returned by the above query, but writing out only a single report export file (containing the report data for all client codes).  I removed the "+ ClientCode" section from the [REPORTFILENAME] column, which I thought would prevent SSRS from segmenting the report exports, but instead SSRS just repeatedly overwrote the export file, since it processed for only one client code [or row returned from the above query] at a time.

I read about passing a single comma-delimited parameter to address this issue, so I altered my DDS query as follows:

DECLARE @ClientList VARCHAR(MAX)

SELECT @ClientList = COALESCE(@ClientList + ', ' + ClientCode, ClientCode)
FROM (
 SELECT DISTINCT
  ClientCode
 FROM TableA
) AS A
ORDER BY ClientCode

SELECT DISTINCT

'Datamart_BillingReportSummary' AS [REPORTFILENAME],

@ClientList AS [CLIENTLIST],

DATEADD(MONTH, -1, DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0))
AS [STARTDATE],

DATEADD(MILLISECOND, -3, DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0)) AS [ENDDATE]

FROM TableA

This gives me a single row result, with a comma-delimited parameter string, so I thought I would be all set, with only one report exported which included data for all the client codes; however, the subscription then failed with this error:

library!WindowsService_89!254!04/05/2012-11:24:53:: e ERROR: Throwing Microsoft.ReportingServices.Diagnostics.Utilities.InvalidReportParameterException: Default value or value provided for the report parameter 'ClientCode' is not a valid value., ;
 Info: Microsoft.ReportingServices.Diagnostics.Utilities.InvalidReportParameterException: Default value or value provided for the report parameter 'ClientCode' is not a valid value.

Any ideas on what I can try, short of using the SOAP API?  I was expecting this to be fairly simple, and I'm hoping the complicated solutions offered years ago have since been superceded by some simple option I'm just missing.  All I'm trying to do is map one comma-delimited column to a single parameter, so I don't know why this would need extensive programming.

Thanks!

-Chris

Answer 1

Still experiencing this issue and have been unable to figure out a solution - anyone's suggestions or thoughts on possibilities will be greatly appreciated! :)

Answer 2

Hi Chackett - This is an interesting question. I've not had a chance to play with it to figure out and repro your error. But, if you have the liberty of modifying the report, it seems to me that (given you now only need to have the report executed once - not once for each row in the DDS query results) you could accomplish your goal without even using a parameter for the ClientCode list. For example, I assume the dataset query in your current report is something like:

SELECT x, y, z FROM ClientTable WHERE ClientCode in (@ClientCode)

Could you just put your DDS parameter query directly in the report's data set query, instead of passing the values into it via a parameter? For example:

SELECT x, y, z FROM ClientTable WHERE ClientCode in (SELECT DISTINCT ClientCode FROM TableA)

As a side note, I've been using SQL Server for over 20 years and have never considered the technique of assigning a variable value using "SELECT @var = @var + ColName", and I've needed it. I learned something new from you today!

I hope this helps, let me know if it doesn't and I'll dig into it more for you.

Dean

This posting is provided "AS IS" with no warranties, and confers no rights.

Answer 3

Hi Dean,

Thank you for your reply!  I do have full liberty to modify the report.  Your suggestion would be an option if the report was only being used for this particular subscription - unfortunately, my accounting department also needs to be able to run the report on-demand for one (or more) client code values from the parameter list (meaning, the report isn't always run for all parameter values, it only runs for all parameter values when it's the dynamic data subscription; when accessed via Report Manager, it may be run for a varying number of values).

I'm so glad I was able to provide helpful information, I know that particular method has saved me quite a bit of coding cursors/loops/functions in the past :)

Thanks again,

-Chris

Answer 4

Hi Chris, you're certainly welcome, and yes, I have written many functions with cursors to build a list of values in a string, DOH!, thanks again.

Anyways, I've been thinking about your problem a bit more and did some research. This thread http://social.msdn.microsoft.com/Forums/en-US/sqlreportingservices/thread/b2c50aea-2032-4025-a155-306c00fcb856 discusses several of the relevant issues and options, but I think I'd still approach this with some creative parameters and queries.

For example, you could add a new hidden parameter to your report I'll call "ClientCodeSource" with available values of "FromDropDown" and "FromQuery", and a default value of "FromDropDown". Then you could use this technique in your dataset query:

    SELECT x, y, z
    FROM ClientTable
    WHERE
    (
        -- This would support your current interactive mode for the report
        @ClientCodeSource = 'FromDropDown'
        and
        ClientCode in (@ClientCode)
    )
    or
    (
        -- This would support your subscription mode for the report
        @ClientCodeSource = 'FromQuery'
        and
        ClientCode in

        (

         SELECT DISTINCT ClientCode

         FROM TableA

         where <you current DDS query logic>

         )
      )
 
I assume your current "ClientCode" parameter is populated by a dataset, you may also want to build the ClientCodeSource parameter logic into that dataset's query.

If your report is set up like this, your interactive users would still choose the values they want by default, and you could use a static (not DDS) subscription with the ClientCodeSource parameter value to "FromQuery", and it would derive the proper ClientCode values from within the dataset query rather than being sent in via the MV parameter.

Of course I hate using OR's in my queries, and the perf impact of it may render this a non-option, but I thought it may give you some other methods to consider.

I hope this helps, thanks, Dean
 
This posting is provided "AS IS" with no warranties, and confers no rights.