locked
Combining results from single dataset with multiple values of one parameter: SSRS to wcf service RRS feed

  • Question

  • Hi all..I am new to ssrs and learning various different aspects.

    Our SSRS is connected to WCF service layer. I have one parameter Order Number in the report. (WCF is based on order number as well).As the business requirements changed, i need to build a report using multiple order number in a single report. Is it possible to do that without changing wcf? Is it possible to combine the results from ssrs using different values of single paramater with some expression?

    here is my query for wcf:

    <Query>
    <Method Name="testRequest" Namespace="http://tempuri.org/">
    <Parameters>
    <Parameter Name="OrderNumber"><DefaultValue>111111</DefaultValue></Parameter>
    </Parameters>
    </Method>
    <SoapAction>http://tempuri.org/testService/GettestForOrderNumber
    </SoapAction>
    </Query>

    Currently i am getting results like :  

    Order number :11111

    results in one column only

    I want to have something like this:

    Order number 11111     order number 222    orderNumber 333

    results                              results                         results

    thanks!

    Tuesday, October 2, 2012 10:28 PM

Answers

  • Hi Cornerquery,

    From your description, you want to pass the parameter from the report to the WCF Web Service. For a multi-value parameter, the parameter value is a array which cannot be displayed in a textbox directly. So, if you place "[@OrderID]" in a textbox and the user selects more than one values for it, it displays "#Error" at run time. To get the expected report, the dataset field should contains all of the available values of the parameter. Then, we can drag the corresponding field into the matrix. Please understand that the parameter only helps us to filter the report data which are from the result set of the dataset query.

    If your WCF web service only accpets single value parameter and also accepts a comma separated string value, you can set the parameter "OrderID" to allow multiple values, and then pass the Join(Parameters!OrderID.Value, ",") value to your WCF.

    Hope this helps.

    Regards,
    Mike Yin


    Mike Yin

    TechNet Community Support

    • Marked as answer by Mike Yin Sunday, October 14, 2012 9:22 AM
    Tuesday, October 9, 2012 11:38 AM

All replies

  • Yeah you can use the matrix component, and put the OrderNumber field in the columns... you'll then get the result you're looking for.

    Josh Ash

    Wednesday, October 3, 2012 1:27 AM
  • Thanks Josh for your suggestion.

    Could you please explain more how to put the order number in the columns? As orderNumber is the parameter, should i have to check "allow multiple values"? I have added order number to column and getting following error.

    The remote server returned an error: (500) Internal Server Error.
    <s:Envelope xmlns:s="http://schemas.xmlsoap.org/soap/envelope/"><s:Body><s:Fault><faultcode xmlns:a="http://schemas.microsoft.com/net/2005/12/windowscommunicationfoundation/dispatcher">a:InternalServiceFault</faultcode><faultstring xml:lang="en-US">The server was unable to process the request due to an internal error.

    rsProcessingAborted

    thanks!

    Wednesday, October 3, 2012 3:51 PM
  • If you look at the above you just add the column to the cell labelled 'Columns'.

    Assuming you've done this, can  you remove all the parameters from your query and see if you still get a 500 error?

    There is no need to make it a multi value parameter..


    Josh Ash

    Wednesday, October 3, 2012 9:55 PM
  • Thanks Josh.

    I have removed <Paramaters> from my Xquery and added [@OrderNumber] parameter to the 'columns' and [results] to the 'Data' of the matrix but still getting the same 500 error. I am not sure how can i pass multiple values for a parameter and how the query can run multiple times for each value (order number) of a parameter.

    my query is now :

    <Query>
    <Method Name="testRequest" Namespace="http://tempuri.org/">
    </Method>
    <SoapAction>http://tempuri.org/testService/GettestForOrderNumber
    </SoapAction>
    </Query>

    I hve one paramater @ORderNumber which prompt when i run the report, if i provide one value to this paramter ot works fine but when i provide two comma separeted values it gives 500 error

    Wednesday, October 3, 2012 11:03 PM
  • Here is the screen short:

    

    Wednesday, October 3, 2012 11:09 PM
  • Looks like your problem is passing multiple values to the web service. This could be an issue with your web service - does it support the passing of a comma delimited list of parameters?

    Josh Ash

    Thursday, October 4, 2012 3:38 AM
  • no it doesnt support that yet..that why i am trying to figure out a way if its possible to do a split(@parameter) before passing that to web service and then run it in  loop..???
    Thursday, October 4, 2012 4:09 PM
  • I see. I don't believe it will work that way. What I think will happen is that the same WCF call will be made, but the parameter will simply be passed as a list of values. 
    Why not simply alter your web service to support a csv list. 

    Alternatively you can write a CLR table valued function to take the CSV parameter list, and call the WCF service once for each value passed... and then combine the results into a single table result which is then passed back. 

    You then call the CLR table function from SSRS.


    Josh Ash

    Friday, October 5, 2012 3:44 AM
  • Thanks Josh. I would prefer to change WCF instead of adding CLR table. Do u hva an example of passing comma separated values to a single parameter in wcf?

    My wcf has "Message Contract" like :

     [MessageContract(IsWrapped = true)]
        public class TestResultRequest
        {
            [MessageBodyMember]
            public string OrderID;

        }

    The value of OrderID is taken by other method for parsing and then getting the results back from edmx (connected to oracle)

    Thanks!



    • Edited by cornerquery Friday, October 5, 2012 7:59 PM
    Friday, October 5, 2012 7:57 PM
  • Hi Cornerquery,

    From your description, you want to pass the parameter from the report to the WCF Web Service. For a multi-value parameter, the parameter value is a array which cannot be displayed in a textbox directly. So, if you place "[@OrderID]" in a textbox and the user selects more than one values for it, it displays "#Error" at run time. To get the expected report, the dataset field should contains all of the available values of the parameter. Then, we can drag the corresponding field into the matrix. Please understand that the parameter only helps us to filter the report data which are from the result set of the dataset query.

    If your WCF web service only accpets single value parameter and also accepts a comma separated string value, you can set the parameter "OrderID" to allow multiple values, and then pass the Join(Parameters!OrderID.Value, ",") value to your WCF.

    Hope this helps.

    Regards,
    Mike Yin


    Mike Yin

    TechNet Community Support

    • Marked as answer by Mike Yin Sunday, October 14, 2012 9:22 AM
    Tuesday, October 9, 2012 11:38 AM