How do I get a table in a table correctly in SSRS?
I am trying to generate a detailed payroll register report. There are several sections in the report, but mostly there is an employee information section (name, ID, hidden SSN, etc.) Income section (pay type, hours, rate and dollars, tax section (detailed list of taxes --fed, state , local, etc.), a deduction section (detailed list of deductions - health, dentistry, etc.), a benefit section (health, 401k, etc.) Here is an image of a report drawn up as a model:
In my stored procedure, I created the following temporary table:
CREATE TABLE #EmployeePayDetail(
[Id] [int] IDENTITY(1,1) NOT NULL,
[EmployeeID] [nvarchar](30) NULL,
[EmployeeName] [nvarchar](50) NULL,
[EmployeeSSN] [nvarchar](50) NULL,
[CheckNumber] [int] NULL,
[Description] [nvarchar](30) NULL,
[Units] [decimal](19,5) NULL,
[Rate] [decimal](19,5) NULL,
[Amount] [decimal](19,5) NULL,
[Section] [int] NULL,
[Sort] [int] NULL )
Most of them are obvious, however the section field is used to determine which section I will put the data in.
1=Earnings
2=Taxes
3=Deductions
4=Benefits
The name, SSN, and employeeID will be denormalized and First (x) will be used in the EmployeeInfo section.
So my thought was to put the list area on the body and then put four tables, one for each section in the list. Then each list will be filtered by section. When I tried this I got the error:
The tablix '' has a detail element with internal members. Part features can only contain static internals.
So, I tried to put tables in cells of another table. Presumably you can nest tables. When I tried this I got the same error. So I asked google and was prompted to try and put them in the table header, not the details. When I tried this, I got all the data running together.
Any suggestions?
source to share
OK, here's what I was able to do to make it work.
I created a table to store the structure of the report. He had a column for each section, "Earnings, Taxes, Deductions, Benefits, and Totals." Then I added the parent group to the Employee group and it added another column to the left of Earnings, which I named Employee Info.
Here's the important part: I deleted the detail row and copied / pasted the data tables into the header row. Then, to get the totals, I added the row below inside the group and made the usual sum of the columns.
Hope this helps others.
source to share