Issues with displaying 2 tables in SSRS in a single page

ND 0 Reputation points
2023-01-25T01:04:01.67+00:00

I have 2 tables: Table 1- Header with 10k records and Table 2 line details corresponding to the header table and the number of records are 50k. The requirement is to display header on top of the page and line table beneath the header table. The details being displayed in the line table should correspond to the header table. The line and header table have a common field which is called uid(unique identifier). I need to create this report in SSRS Report Builder.

Work so far which i did:

1.) Created 2 datasets: Header and Line. Inserted a table for header and selected all the fields i will need to display. Likewise created another table for line and selected all the fields i need to display. The fields in line i used LOOKUP function pointing to the uid in header table and populated the data. 2.) I placed the line table just beneath the header. 3.) Since the number of records in header table are 10k, i restricted the rowcount to 50 so i can fit the header and line table in 1 page.

Issues i am facing:

1.) When i place the line table beneath the header table, i can't see the line table details, but when i place the line table next to header then i can see the detail of line table. I need the header table on top with 25 records and corresponding line details for that 25 header table records beneath the header table.

2.) The line table values are not being displayed in correspondence to the header table records being displayed in the page. which means if header table is displaying 25 records, the line table is displaying all the records. I need only the line details of the 25 header table records in that page.

Any help to solve this issue is greatly appreciated. Do let me know if you need any additional information.

SQL Server Reporting Services
SQL Server Reporting Services
A SQL Server technology that supports the creation, management, and delivery of both traditional, paper-oriented reports and interactive, web-based reports.
2,993 questions
{count} votes

3 answers

Sort by: Most helpful
  1. ZoeHui-MSFT 40,511 Reputation points
    2023-01-25T06:44:22.5433333+00:00

    Hi @ND

    It is a bit hard for us to get your meaning via words, could you please provide some screenshots so that we could do more analysis.

    In addition, it seems like a position issue in the report.

    You may try to put the two tables inside two rectangles sitting one after another.

    If I misunderstand your issue, please incorrect me.

    Regards,

    Zoe Hui


    If the answer is helpful, please click "Accept Answer" and upvote it.


  2. ZoeHui-MSFT 40,511 Reputation points
    2023-01-26T06:37:13.7866667+00:00

    Hi @ND

    If you want to display a fixed number of rows per page for an SSRS report, you may take a reference here.

    And in the first table, to match with the first table, you may use look up function to get the data.

    0 comments No comments

  3. ND 0 Reputation points
    2023-01-26T14:31:59.8366667+00:00

    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.

    
    
    0 comments No comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.