Running a query based on a field value (set field value as parameter?)?

  • Greetings!

    In simple terms, I'm trying to take a field value from one dataset (in this case, a field called "Activity" (Fields!Activity.Value)), and then plug that value into a SQL query to run on the next dataset.

    Like, I want to create a where statement similar to:

    WHERE activity = Fields!Activity.Value

    The way our report is set up, we have a bunch of activities that start with an A.  So, A-SOCCER, A-FOOTBALL, A-BASKETBALL.  We want them to be able to query A% and return that list of results for each activity, and then use the activity in each row (A-FOOTBALL, A-SOCCER, A-BASKETBALL) to query it in the next dataset (which then runs a complicated SQL query to pull some financial data for each activity).

    Is there a way to set a field value to a parameter, or something like that?

    Thanks!

  • I tried this:

    Hi Boopathy,

    Please follow these steps:

    1.Create a parameter named RP1. Set the default values for this parameter as:

    Query from DS1. Valud field: CountryId

    2.Open DS2, right-click on this dataset, and then select "Properties"

    3.Go to "Parameters" tab.

    4.Set the parameter:

    Parameter Name:        Parameter Value:

    @RP2                         =Parameters!RP1.Value

    Now, we are able to pass parameter's values to DS2.

    If there is anything unclear, please feel free to ask.

    Thanks,

    Jin Chen

    From: https://social.msdn.microsoft.com/Forums/sqlserver/en-US/3ba70eb7-f644-4d89-84dc-a2fc11dadfc4/passing-datafield-value-as-input-parameter-in-ssrs?forum=sqlreportingservices

    That succeeded in getting me the Activity values, but then it just dumps them in a grayed out drop-down menu and doesn't run any queries.  How do I get it to actually run the query with those values?

    Thanks!

  • Have you looked at sub-reports?

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply