none
Displaying results based on the selected value dynamically RRS feed

  • Question

  • Hi

    I've a challenge in creating one of the reports.

    I've created a SSRS report with the parameters as - from Date, To Date, State, Group, Territory,CallType.

    All are multi select.

    In Call Type, we have 4 values - Direct, Customer, Telephone,Merch

    In result set am displaying all the above parameters as columns and 3 more fields - Called, Not Called and Percentage Called.

    Logic is: When user selects Call Type as Direct then it should display all the customers irrespective of the Call Type but for the customers who have made call as Direct, for them Called value is 1 and Not Called as 0. The customers who have made calls other than direct, Called is 0 and Not called is 1.

    When user selects more than one call type, lets say direct and customer then it should display all the customers irrespective of the call type but the called value is 1 for the customers who made either direct or customer call. And for the other customers Not called is 1.

    Could you please help.

    Thanks.

    Monday, August 13, 2012 8:42 AM

Answers

  • Hi NoorBi,
    From your description, the value of “Called” and “Not Called” column should depend on the value of “CallType” parameter, right?
    In your scenario, you can use Join and InStr function to achieve you requirement. Jion function is used to display the selected values from a multivalue parameter. And InStr function is useful for returning a substring. Type in the expression below to the “Called” column:
    =iif(InStr(Join(parameters!CallType.Value,""),Cstr(fields!CallType.Value))>0,1,0)
    And type the expression below to the “NotCalled” column:
    =iif(InStr(Join(parameters!CallType.Value,""),Cstr(fields!CallType.Value))>0,0,1)

    Here has a screenshot about the result of the expression:

    If you have any questions, please feel free to ask.

    Regards,
    Charlie Liao

    Thursday, August 16, 2012 2:25 AM
    Moderator

All replies

  • Hi,

    If I got you correctly you want to parametrise your report.

    If so and If you are connecting to an OLTP, in the first step you need to define your parameters. To do so, double click on your dataset in Report Data pane in BIDS, click query designer and in query designer in where clause set your parameters. For instance, From_Date= @FromDate.

    Know when you preview your report you have to enter a value for each parameter.

    You can show parameters in dropdown lists in your report. In this case, you need to define some other datasets to query your parameters from OLTP.

    Cheers


    Please Mark as Answer or Vote As Helpful if a post solves your problem. MCP, MCTS, MCITP

    Monday, August 13, 2012 10:10 AM
  • Hi NoorBi,
    From your description, the value of “Called” and “Not Called” column should depend on the value of “CallType” parameter, right?
    In your scenario, you can use Join and InStr function to achieve you requirement. Jion function is used to display the selected values from a multivalue parameter. And InStr function is useful for returning a substring. Type in the expression below to the “Called” column:
    =iif(InStr(Join(parameters!CallType.Value,""),Cstr(fields!CallType.Value))>0,1,0)
    And type the expression below to the “NotCalled” column:
    =iif(InStr(Join(parameters!CallType.Value,""),Cstr(fields!CallType.Value))>0,0,1)

    Here has a screenshot about the result of the expression:

    If you have any questions, please feel free to ask.

    Regards,
    Charlie Liao

    Thursday, August 16, 2012 2:25 AM
    Moderator