I’m looking for some help with .rdl report modification. I’m an Epicor Newbie. Worked with Crystal Reports a long time ago, so I understand some of the concepts of relational database report building. But it’s been a while! So, I’ve been watching a bunch of videos on YouTube, EpicWeb, etc to learn MS Report Builder.
I’m trying to take the PO Form and simplify it considerably. Trying to just make a basic table, really. I’m creating the new table above the old report, referencing the old report to find the fields I need. When I’m all done I’ll delete the fields, etc. from the old report.
Oh, and currently on a Pilot Cloud environment - so no access to the database. I’m using the Generate for Design feature off the print window, then downloading from the SSRS Report Design window, saving and previewing often so I can easily undo any changes that break the report.
There are three fields giving me trouble -
Quantity - I need the value from the POForm_PORelQty (subreport, I guess it is) to show in the table. It does. But I can’t format it to style it like the other fields. I have no options to change the background properties. Can I pass the values to that field instead somehow? Or am I missing how to style it?
Customer - we need to be able to see the customer associated with each line item. As far as I can tell, that information is in the CalcMangCustID field in the PODetail dataset. And I don’t see the PODetail dataset listed in the report. I sort of understand how I can add a field to an already connected dataset by editing the query code, but I’m not sure how to add the PODetail dataset to the report. Or is there a better way?
Description - for some reason that I really can’t understand, whenever I try to widen the description column in my table it throws off Tablix1 below and I get an error that says “The Hidden expression for the tablix ‘Tablix1’ contains an error: The expression references the field ‘RptLiteralsLVend’, which does not exist in the Fields collection.” As soon as I undo changing that particular column width, it runs just fine. I can widen any other column in that table and it’s fine. What in the world?
In case a visual helps, here’s what I get when I run the report:
And please let me know if I’m missing something, or going about this the wrong way! Also if this should be more than one post. Any insights would be much appreciated. Thanks in advance!
Just to be clear on #2 … That the customer that you are selling the part part to, or the Customer who supplies the part to you to use in an item you sell back to them?
The first would be:
Sales Order 1234, for Customer WECOYTE, Line 1 is for P/N: ANVIL (QTY 1)
PO 101 to supplier ACME, for P/N ANVIL (intended to be used on order 1234)
You want the PO Form to show “WECOYTE” in your customer column.
The order would need to be setup as BTO (Buy To Order)
The PO Would need to reference the OrderNUm and line
The second would be if your company panted customer supplied anvils
Sales Order 1235, for Customer WECOYTE, Line 1 is for P/N: INVISIBLE ANVIL (QTY 1)
A job to paint the customer supplied ANVIL, with invisible paint makes that deliverable
PO 102, to supplier WECOYTE, for P/N ANVIL (for job related to order 1235)
PO 103, to supplier ACME, for P/N INVIS-I-PAINT (for job related to order 1235)
Only PO 102 shows Customer WECOYTE, they are the source of the part.
To get this info (customer name for the job the invis-i-paint is for), would require linking to the job (which would be specified on the PO Line, since it is a “Buy For Job” line), then linking the Order to the Job, then the Customer to the order.
Makes sense that that’s where the info would be, and that those connections would need to be made. How do I get that into the report? A calculated field or subreport?
You’ll need to create a copy of the RDD (Report Data Definition) used by the base Report Style. Then add the required tables and their relationships to it.
Not something you want to try on one of your first report customizations.
@ckrusen Thanks so much for confirming that is what needs to be done!
I’ll see what we can do to get by without that column for a bit. Maybe just add the customer to notes for now as a workaround, and revisit adding it as a column down the road.
I would mark your answer as the solution, but that still leaves my other two questions… Maybe I should move them to their own topics?
Thanks so much again for your help and quick response!!
On my system, the Standard SSRS shows ‘RptLiteralsLVend’ in the POHeader Dataset. Then again, it looks a lot different than what you are showing here.
In mine, the region ‘Tablix1’ shows False in the ‘Hidden’ expression. Perhaps you could try making a backup and then changing that expression to ‘False’ and see what happens. Then you’d need to determine if it was necessary.
Alternatively, you’d need to investigate what is going on with the field and your Dataset.
Report back what you find, maybe something strange is going on.
You should be able to affect changes to the formatting by altering the Subreport.
I’ve done quite a bit of research, and unless I am missing something too obvious, it’s not possible in SSRS to directly reference those fields through the Subreport. You could duplicate the Subreport and strip out all the output except that one field to give the appearance of a standalone value. You could make the necessary changes to the RDD and the Dataset to return the value directly.
Yes, what I was showing was the printed version of the report I’m building - inside the red box in my screenshot above. The portion in the orange box is the original report, including Tablix1.
That’s what’s so odd. When I just widen the one column in the new portion I’m building and save it without changing anything else, I try to preview it and I get the error about a hidden expression in the original part of the report. If I undo the change and save it, I can preview it just fine.
I can try digging for that field in the Tablix1 and see if there’s anything odd. I’m so new I’m not sure I would see it.
Theoretically if I’m just going to delete the old portion eventually it may not matter. But I’d like to keep it until I know I have everything I need from it. In the meantime I can’t widen the description column to make the preview easier to work with. Anytime I do it breaks
OK. I won’t assume anything and just ask to make sure your objects are in the Body, not the Page Header. Not sure why you would get such a strange error, but looking at where the Tablix1 is selected, this is probably worth double checking. Report Builder lets you place objects up there, but you won’t be able to reference a field directly, only in aggregate function or in code reference to a set variable let’s say.
Yes, thank you for checking. The yellow and grey rectangles containing the vendor address and ship to information are in the header. Everything below that is in the body. Sorry, it is kinda hard to tell from my screenshot.
So, I don’t think that would do it… Especially since, as you say, it previews fine until I change the width of just the one column in that top table, which is the first one in the body.
Just wanted to post the solutions I found for anyone experiencing the same thing:
Q1: To format a subreport field to match the alternating background colors (green bar or zebra striping) of the the rest of the table in a main report here are the steps. They are a combination of the steps given here and here.
In the subreport (in this case POForm_PORelQty) add a parameter. I called mine BackGroundColor.
2. Set the background property of the text box in the subreport as an expression - =Parameters!BackGroundColor.Value
3. Set the size of the field/textbox in the subreport to be the same size as the cell it’s sitting in in the main report.
4. In the main report - in the cell where the subreport should go, add a rectangle and set it’s background color with the same expression used to alternate the colors in the rest of the row - I used =IIf(RowNumber(Nothing) Mod 2 = 0, “#F2F2F2”,“#e6e6e6”)
I’ll mark his answer as a solution if I can mark more than one.
Q3: I never could figure out what the deal was with the error being caused by Tablix1 only when I resized a column in the other tablix above. Very strange.
I tried looking for RptLiteralsLVend in the report, deleting it wherever I could find it, but that didn’t work. I even tried viewing the report in a code editor, and comparing the code to the original report code to try to find the instance causing the problem. Deleted any instances from the code one a time and checked the report, no joy.
I ended up just working around it till I got all the information I needed copied over from Tablix1, then deleting it as it was no longer needed. The errors stopped.
If I had to make a guess on #3, it was because resizing the column may have moved the order that the fields in it are processed, and if one relied on the other being evaluated first then that might throw an error. Just a wild guess.
I was afraid it might be something like that.
