locked
Is it possible to publish a data set that uses a command text expression? RRS feed

  • Question

  • Good morning,

    We're currently looking at using SSRS for operational reporting. Our main transaction system is DB2 (iSeries AS400) and we're using SSRS 2008 R2 Report Builder.
    DB2 doesn't support multi-value parameters so we have to use the command text expression for the query as a workaround.
    I.e. the query is a string built around the parameter object.

    Has anyone been able to use this method and publish the data set to the Report Server?

    I always get the error message: 
    The CommandText expression for the report ‘body’ refers to a non-existing report parameter ‘Parameter1’. Letters in the names of parameters must use the correct case.

    I'm publishing the parameter at the same time, and have also tried publishing the parameter first. The parameter works with the query so the naming seems fine.

    Thanks for any input or advice on this.

    Friday, January 23, 2015 2:53 PM

All replies

  • Open the dataset in your report and select the Parameters tab. Does Parameter1 appear on that tab?


    "You will find a fortune, though it will not be the one you seek." - Blind Seer, O Brother Where Art Thou
    Please Mark posts as answers or helpful so that others may find the fortune they seek.

    Friday, January 23, 2015 2:58 PM
  • Hi Tim,

    I had removed the parameter as the query won't run with it in the data set properties. I just tried re-adding it and publishing but got the same error message. So currently the parameter is on that tab but it wasn't when I posted the question.

    Thanks,

    Simon

    Friday, January 23, 2015 3:55 PM
  • Can you post your commandtext and a screenshot of the parameters tab?

    "You will find a fortune, though it will not be the one you seek." - Blind Seer, O Brother Where Art Thou
    Please Mark posts as answers or helpful so that others may find the fortune they seek.

    Friday, January 23, 2015 4:03 PM
  • Here's the commandtext:

    ="SELECT DISTINCT RHPYR FROM GCTFILES.RSRSHDP0 WHERE RHPYR IN (" & Parameters!Parameter1.Value(0) & ")"

    Here's the parameter tab:



    • Edited by SBlackshaw Friday, January 23, 2015 5:53 PM
    Friday, January 23, 2015 5:03 PM
  • Parameter name is set to "?". Change it to "@Parameter1". In your command text, are you intentionally using only the first value of the multivalue parameter (Parameter1)? If your intent is to send all values the remove "(0)".

    "You will find a fortune, though it will not be the one you seek." - Blind Seer, O Brother Where Art Thou
    Please Mark posts as answers or helpful so that others may find the fortune they seek.

    Friday, January 23, 2015 10:06 PM
  • Hi SBlackshaw,

    I have tested on my local environment and can reproduce the issue, as you know that the DB2 doesn't support multi-value parameters, so we can use the expression in the dataset, I have check the issue can be caused by you have done the wrong setting when using the expression and set the parameter.

    Details information below about how to add filter to support multiple value for your reference:

    1. Right click to  the "DataSet Properties " and  select he "Parameters", delete all the parameters in the list
    2. Modify the comment text expression as below:
      Multiple Value parameter:
      ="select DISTINCT RHPYR FROM GCTFILES.RSRSHDP0 WHERE RHPYR IN (" + Join(Parameters!Parameter1.Value,", ") + ")"
    3. Check to make sure you have check the "Allow Multiple Values" for the Parameter1
    4. Preview that the multiple parameter will works fine.

    If you still have any problem, please feel free to ask.

    Regards
    Vicky Liu

    If you have any feedback on our support, please click here.


    Vicky Liu
    TechNet Community Support


    • Edited by Vicky_Liu Monday, January 26, 2015 3:57 AM
    Monday, January 26, 2015 3:57 AM
  • Hi Vicky,

    Thanks for the information. I've been able to use the expression workaround already to get around the DB2 limitation regarding multi-value parameters.

    What I haven't been able to do is publish the data set as a report part when it is written as an expression. This means that all reports using the data set will have to be maintained individually.

    I get the error message notes above when I try and publish the data set: The CommandText expression for the report ‘body’ refers to a non-existing report parameter ‘Parameter1’. Letters in the names of parameters must use the correct case.

    This is the case when I publish the parameter report part at the same time or have it published already. Do you know of a way around this?

    Let me know if you need further clarification or details.

    Thanks,

    Simon 

    Monday, January 26, 2015 4:26 PM
  • Have you updated per my suggestions above?

    "You will find a fortune, though it will not be the one you seek." - Blind Seer, O Brother Where Art Thou
    Please Mark posts as answers or helpful so that others may find the fortune they seek.

    Monday, January 26, 2015 6:29 PM
  • Hi Simon,

    Did you tried the method which I have provided last time to recreate the dataset?

    I think the issue can be caused by the method you have used to  filtered the multi-value parameters, I have tested the method by using this comment text in my side and delete the parameter in the dataset properties with no error:
    ="select DISTINCT RHPYR FROM GCTFILES.RSRSHDP0 WHERE RHPYR IN (" + Join(Parameters!Parameter1.Value,", ") + ")"

    If you got some issue of using this method ,please let me know.

    Regards
    Vicky Liu


    Vicky Liu
    TechNet Community Support


    • Proposed as answer by Vicky_Liu Friday, January 30, 2015 1:02 AM
    Tuesday, January 27, 2015 8:07 AM
  • Hi Tim,

    I was able to publish the shared data set when I did what you suggested and renamed the parameter. Though I got an error message when trying to use the shared in another report that was caused by the embedded parameter being an unsupported multi-value parameter.

    This lead me to discover another issue; if I use the Join function on a multi value parameter, SSRS returns an error message if only one value is selected from a list.

    So it seems the parameter can only be single value or multi value and not both.

    Let me know if you have any way around this.

    Thanks again,

    Tuesday, February 3, 2015 8:52 PM
  • Sorry I have been away so long. I have not had the same experience. I just did a simple test with a multivalue parameter where I can select user names. I display the results in the report in a test box using the formula:

    =Join(Parameters!Param1.Value, ", ")

    When I select a sing name, only that name is displayed (no error). When I select multiple names, I see the comma-separated list of selected names.

    This makes me think it is an implementation issue. Please copy your formula/text into this thread so we can have a look.


    "You will find a fortune, though it will not be the one you seek." - Blind Seer, O Brother Where Art Thou
    Please Mark posts as answers or helpful so that others may find the fortune they seek.

    Monday, February 9, 2015 11:03 PM