locked
Setting a query parameter in a subreport RRS feed

  • Question

  • I would like to pass a parameter to a subreport that can be used in the embedded SQL query. I am using SSRS 2008, and have been searching around on the internet for 2 days. I am new to SSRS, so any help is appreciated. I have been able to pass parameters to filter data in a subreport successfully, but this happens to be in a nested sql subquery, so that option is not available. The subreport runs correctly when run by itself and I put in the query parameter.

    Mike

    Thursday, March 31, 2011 8:57 PM

Answers

  • Hi saskcivic,

    From your scenario, you have mentioned that you cannot pass values to subreport. Of course, it is can pass values to a query in a subreport.

    What do you mean about pass the day value to the main report to the @QueryDate parameter in the query? My understanding is that, at first, passes a day value to the mainreport, and then passes this day parameter to the subreport parameter. If I misunderstand, please let me know. If in this case, please try the following steps again, and then check if it works for you.

    1. Right-click the subreport and then click Subreport Properties in Design view.
    2. Click Parameters in the Subreport Properties dialog box.
    3. Click Add. A new row is added to the parameter grid.
    4. In the Name text box, type the name of a parameter which is in the subreport. Please type in “QueryDate” in the Name textbox , please make sure the main report parameter have the same name as “QueryDate”( this is from your scenario).
    5. In the Value list box, type or select a value to pass to the subreport. This value must exist in the main report, and
    not type in the parameter value.

    After you have tried steps above, if the issue still exists, could you please supply more details about the issue, such as the query in mainreport, how do you set the parameters for the subreport, or the error message you have got, and do on, and then we can help you more effectively.

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

    Thanks,
    Eileen Zhao

    • Marked as answer by Challen Fu Friday, April 8, 2011 11:18 AM
    Tuesday, April 5, 2011 3:28 AM

All replies

  • Hi Mike

    try the below link

    http://msdn.microsoft.com/en-us/library/ms160348.aspx

    I hope this will solve your problem.

    All the best..:)


    Dasari
    Thursday, March 31, 2011 10:20 PM
  • Thanks for the link, but it specifically states that "The parameter name must match the name of a report parameter, not a query parameter" It does not give any way that i have been able to find to apply a value to the query parameter. Is it not possible to pass values to a query in a subreport? The query that I have gets the unique client appointments for a specified day (a client can have more than 1 appointment in a day and we only want to show the first one.) The main report runs a week at a time, and the subreport shows the appointments for each day. I want to pass the day value to the main report to the @QueryDate parameter in the query  

    The query in the subreport looks like this:

     

    select UniqueAppts.[ApptStart], 
      UPPER(Client.[LastName]) + ', ' + Client.[FirstName] as  ClientName, 
      UniqueAppts.[ClientGUID] 
    from (select convert(varchar,min(Appt.[ScheduledStartDtm]),108) ApptStart 
          ,Appt.[ClientGUID] 
        from [SXAESAppointment] Appt 
       WHERE convert(varchar, Appt.[ScheduledStartDtm],103) = convert(varchar, @QueryDate ,103) 
       and Appt.[AppointmentStatus] = 'ACTIVE' 
       group by Appt.[ClientGUID]) UniqueAppts, 
      [CV3Client] Client 
    where UniqueAppts.[ClientGUID] = Client.[GUID]
    order by UniqueAppts.[ApptStart] asc
    
    
    Friday, April 1, 2011 3:07 PM
  • Hi saskcivic,

    From your scenario, you have mentioned that you cannot pass values to subreport. Of course, it is can pass values to a query in a subreport.

    What do you mean about pass the day value to the main report to the @QueryDate parameter in the query? My understanding is that, at first, passes a day value to the mainreport, and then passes this day parameter to the subreport parameter. If I misunderstand, please let me know. If in this case, please try the following steps again, and then check if it works for you.

    1. Right-click the subreport and then click Subreport Properties in Design view.
    2. Click Parameters in the Subreport Properties dialog box.
    3. Click Add. A new row is added to the parameter grid.
    4. In the Name text box, type the name of a parameter which is in the subreport. Please type in “QueryDate” in the Name textbox , please make sure the main report parameter have the same name as “QueryDate”( this is from your scenario).
    5. In the Value list box, type or select a value to pass to the subreport. This value must exist in the main report, and
    not type in the parameter value.

    After you have tried steps above, if the issue still exists, could you please supply more details about the issue, such as the query in mainreport, how do you set the parameters for the subreport, or the error message you have got, and do on, and then we can help you more effectively.

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

    Thanks,
    Eileen Zhao

    • Marked as answer by Challen Fu Friday, April 8, 2011 11:18 AM
    Tuesday, April 5, 2011 3:28 AM