Dear Sir/Madam,

I am using reporting services 2014, I am facing below problem,

1> I am using sub report and by default it is hidden and sub report only display once click a value eg. employee name,

--> problem is ,once report open it load all record of sub report also without clicking employee name, but I want this dataset execute only when I click on employee name.

2> In particular date range I have 100000 record, but in one page we can display only 100 record, So I want, once Report execute, Our query should return for only first page record (100 data)  not all entire record ie. 100000, once I navigate to next page only query execute and return data set for second page.

waiting for your kind response....

Thanks Regards,

Saroj

Hi Saroj,

You have to pass the report parameter to the dataset  query in your subreport  so that it can filter the query for particular parameter rather than loading all the records

to get you started. Please follow the below link

http://www.sqlchick.com/entries/2012/1/8/relating-parameters-filters-in-sql-server-reporting-services.html

Thanks Milan for your reply,

I already passed the parameter in sub report query,

Suppose in a page we have 100 employee name, So the sub report executed 100 times for all employee, but I want query execute only I click on employee.

Please correct me if I was wrong.

Best Regards,

Saroj

Hi Saroj, 

According to your description, you want to hide sub report and execute the dataset step by step, right? 

In Reporting Services, the dataset query will only be executed one time during data retrieval. So the report will be processed once, and then Reporting Services render the report. In your scenario, your requirements cannot be achieved currently, since the report is rendered, Reporting Services will not process it again. For your first requirement, there is a workaround that  you can use a report parameter to control the visibility of the sub report. Please refer to the following steps: 

1. Create a report parameter and set the available values "Display" and "Hide". 
2. Right-click the sub report and go to Visibility tab, use the following expression: 
   =IIf(Parameters!test.value = "Hide", true, false)

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

Regards,
Shrek Li