Go```
Got help from another user: Below is the solution. The only issue i am facing right now is the main report is taking nearly 8 mins for data retrieval from the sub report.
First create a report that handles just a single "page" of results
So, create a new report and add a dataset (call it something like dsHeaders with the following dataset query
SELECT * FROM (
SELECT
[UID], [Po_number], [Invoice Num], [Total Amount]
, [PageN] = (ROW_NUMBER() OVER(ORDER BY [UID]) - 1) /25
FROM myHeaderTable
) h
WHERE h.PageN = @p
This step should automatically create a report parameter for you called p
Now add a second dataset called dsLines with the following query...
SELECT * FROM (
SELECT d.*, p.PageN FROM myLinesTable d
JOIN ( SELECT
[UID], [Po_number], [Invoice Num], [Total Amount]
, [PageN] = (ROW_NUMBER() OVER(ORDER BY [UID]) - 1) /25
FROM myHeaderTable
) p
on d.UID = p.UID
) a
WHERE a.PageN = @p
This will give us all the lines along with the same PageN values as the headers. If required, you can adjust the number of lines in each page/group by adjusting the 25 in each query.
Now add two tables to the report and drag and drop the fields from dsHeaders into the first and the fields from dsLines into the second.
Run the report and test it by putting in different values for the p parameter.
Once you are happy with it, save the report.
Next.
Create a new report and add a dataset (called say 'dsMain') with the following dataset query.
SELECT [UID], [PageN] = (ROW_NUMBER() OVER(ORDER BY [UID]) - 1) /25 FROM myHeaderTable
Now add a table and remove two of the three generated columns. In the remaining column, right click the textbox and do "Insert ==> Subreport".
Now right-click the new subreport placeholder and choose "Subreport properties"
In the first tab, click the drop-down and choose the subreport you created earlier.
Now click the parameters tab, Click Add and choose "p" (it should be in the drop-down list) as the parameter and choose the PageN field as the value (again this should be in the dropdown list.
if for some reason the drop downs are not working, simply type p into the name column and [PageN] into the value column.
Finally, go to the rowgroup panel under the main designer and right click the rowgroup shown there (there should only be 1 rowgroup).
Open the properties and select "Between each instance..." in the page break settings.
Now run the main report and you shoudl see the subreport generated for each group of 25 records.
That should give you the basic report.