I have one of those weird requests from management to pull together a bunch of data with the hopes that it will shine a light on some issues. The goal is to see a report of all the open jobs, and how many pieces are done at each operation. I have other fields to throw in there to make it useful, but I am having trouble with the operation part of it. In this case, we want to see the operations listed out from left to right, until all the operations are shown. This means we need a variable number of columns to display the data. One job might have 4 ops, and the next has 20 ops. We need to show the list of ops like this:
JobNum
AsmSeq
OpSeq
OpSeq
OpSeq
OpSeq
JobNum
AsmSeq
ResourceGroup
ResourceGroup
ResourceGroup
ResourceGroup
JobNum
AsmSeq
Completed
Completed
Completed
Completed
12345
0
10
20
30
900
12345
0
SW
SD
WJ
QC
12345
0
100
100
10
0
45678
0
3
10
20
30
40
50
900
45678
0
DS
FD
AS
RF
AS
CD
QC
45678
0
50
50
0
0
0
0
0
Here you can see that the first job starts at op 10 and goes to op 900, the next job starts at op 3, and goes to 900. How can I get SSRS to display a variable number of columns? I tried to insert a matrix into one of the rows, so that at each job/asm it would show a matrix of the operations. I have never used a matrix before, so I am not sure if it will work. In this case, when I add the matrix to the tablix cell, I get this error when I try to upload it:
The SSRS server returned the status code 500 (InternalServerError) with the following error text:
The tablix ‘Tablix3’ has a detail member with inner members. Detail members can only contain static inner members.
So is there a way to have a variable number of columns appear in my tablix so that I can show the operations all in one row, with their resource group and completed qty below each operation.
In the end we would use this to get a quick glimpse of where the bulk of parts are sitting on any given job.
Does this make sense?
Thanks!
Nate
In the OprSeq Column, I removed the calculated field, and replaced it with a table with 3 rows (OpCode, and 2 calculated values for completed parts and qty waiting at op.
I would like to have all the ops on the row with the job. For example, if that first job has op 10, 20, 40, 900. I only want to see those in the opseq header. I want that op seq header to only list the ops for the job the ops are in. So, I don’t want to see op 2, 3, 4, 5,6, 7… for every job, only on the jobs that have it.
I think it needs a fancy grouping. I am doing trial and error now, but if you have any ideas, I would love to try them!
That is the issue with a cross-tab, if it exists in the data it shows.
Two suggestions. First, just do an expression that combines the data into 1 field with text headers. Have you ever used Placeholders? They are fun and an option for you.
Second, add an extra row and hide it. Write an expression that performs a check of the field that has the data and if true put in 1 and false put in 0. Then put a hide filter on the columns that if the Sum of your formula = 0, hide.
Placeholders are just easier ways to build out a concatenated string. To use them, you put the cursor in the field and right click to get the Placeholders option. You can build text placeholders for titles, field placeholders for displaying data, and you can even do expressions.
Instead of trying to get them all on 1 row, just do an expression (or placeholder) that concatenates them all together.
The problem I am having is probably with the parameters. In the example I posted here, I want the operations to be listed to the right in the subreport. But I only want the ops that are linked to the job in the group. Instead, it seems to be showing some other set of operation.
We don’t need any order data in the subreport, you’re right. It is only the job related labor data about each operation. I can remove those, but, it does not change the output list of operations. The ops should be 10, 20, 40, 50, 60, 70, 900, as in the BAQ. But the output is 10, 15, 20, 25. This makes me think the subreport is not linked correctly.
I used the same dataset. Honestly, I haven’t built a lot of subreports from scratch, so I am a little fuzzy on this part. To create my OPList subreport, I copied the original RDL and changed the name. I deleted the header/footer and grid, and then built it back up. So the dataset in the background is the same as the top report. If I were to build a separate BAQ for the subreport, how would I link it all up?
In the OpList subreport the column is grouped on JobOper_OprSeq. I think the issue could be because I used a matrix as the subreport grid. The matrix requires any fields in the values section to be aggregated. So I set them to first, and sum, which is probably one issue.
When I try to put a matrix for the ops inside my tablix, I get this error when trying to upload it:
The SSRS server returned the status code 500 (InternalServerError) with the following error text:
The tablix ‘Tablix1’ has a detail member with inner members. Detail members can only contain static inner members.
I figured out that you can’t have a tablix in a details section. So I am working on trying to get my Op list to work as an embedded matrix/tablix. I think I am on the right track.
