We are in E10.2.200.7, there are two of us that are working on this project and the other person that is working on it is faster with crystal reports than i am with SSRS. I took a screen shot of the RDD and Report Style Maintenance as well incase there is anything that we are missing. please let me know if you want to see any other tabs that are in those two.
we understand that recreating the standard report from scratch is not the best of plan, But we tried modifying the packslip ssrs, we tried to subreport in a blank report so that we can make sure that we can do that but both options that we have tried have not gotten us any closer to the end goal.
SSRS is where we both understand we will have to be, but we were trying to bypass the standard packslip (which is where we are having trouble) and replace it with the Crystal for temporary use until I can get the SSRS version completed.
I would suggest we start with the beginning need. If I read correctly, you want to add a BackLog Qty to the PackSlip.
- Have you verified that there isn’t already a Calc_BacklogQty or something like it?
- Have you tried adding the OrderRel table to get the Backlog Qty?
- What issues have you specifically had so far?
1 Like
We want to add the lines that are on the same order that are not shipping on the pack slip so that the customer that is getting the pack slip knows that these specific lines have not shipped because they are in back order.
There is a backlog quantity on the pack slip originally but only for the lines that are on the pack slip, and that are shipping something.
i think ill bypass 1&2 because there is one already on the pack slip originally. We are both going down different rabbit holes currently to see who can get to a solution first.
My Boss is going through trying to sub report in a separate report that would have the back ordered lines from the same order, this way it can be a separate way to get something into the pack slip report.
I am working on getting the order lines and order information that is not on the pack slip to come through by trying to add a data set that will allow me to add a list of what has items back ordered through SSRS. While trying this i have not been successful in getting the data set correctly into the report. any help is appreciated.
What table did you add to the rdd and how did you join it?
i was trying to add the OrderDtl table that was already in the RDD but i wanted to create a new dataset so that it could be used seperately from the data taht the packslip uses. As all we want to do is add the lines that are from the same order that are not on the packslip.
Changing RDD is a very time consuming process. It is one of the screen that stops and thinks a lot. If you have already done the BAQ, it maybe quicker to create a BAQ report and then add a button or menu item to print your BAQ report as the packslip alternative.
I don’t think they have allowed us to add the same table twice… there are other posts on this limitation. Does that sound accurate @Jason_Woods ?
Just tried it, you will get this.
This is a good idea and you got us thinking about how we could make this work, however our CC processing has a customization that is linked to the print button that is on the regular print button that is set there from epicor.
You can’t “add a table twice” to the RDD (I’ve been down that rabbit hole before to no avail).
Your boss has the right idea. You should take your SSRS packslip RDL and add a subreport to it. The subreport’s datasource will need to connect directly to the Epicor10 database. Pass an Order number parameter from the main RDL into the subreport. Then use that value in the subreport’s dataset query.
You might have to get a little creative with the query and/or pass more than the order number to the subreport, but the general architecture will definitely work. I’ve done something very similar on a MtlQueue report – needing to add order lines that were NOT getting picked to display after the order lines that were getting picked (and were therefore in the RDD output).
1 Like
If you do decide to create it from scratch, do not use the BAQ Report Designer – but instead use the new feature BAQ Reports under RDD.
1 Like
Not sure you’ll be able to use these but…
Attached an old Crystal Report rpt file - BackOrder PackSlip that I built for V8.
w/screen shot of RDD and sample output
Note that in my case I used OrderRel instead of OrderDtl
AND… the shipped checkbox had to be selected in shipment entry BEFORE printing this style (since OrderRel table isn’t updated until after shipped is checked).
I know there were some other caveats related to timing/shipped but… I can’t remember the details anymore. Shipping wasn’t thrilled with this style but it was better than nothing for the few customers who demanded to see backorders on their packslips.
The backorder subreport is suppressed if it’s blank.
PackSlip_BackOrderSub.rpt (49 KB)
To Everyone that has been helping with this,
The path that i am going down and that has shown the most promise is subreporting in a second small report that has the lines that have not been shipped or that are partially shipped.
i have gotten the subreport to pop when the packslip is printed, i have also gotten the lines to show when only calling the OrderDtl items that i needed to test the theory, ( OrderNum, PartNum, Line Desc, OpenLine,SellingQty)
The part that i have run into is trying to combine the OrderDtl and the OrderRel on the OrderNum so that i can pull the data for the releases and get the quantities that have been shipped.
i have tried to call the sql by the table name ( OrderDtl.OrderNum) and the query runs? maybe?because the subreport doesnt fail it just doesnt end up showing the data. I was doing this to test to make sure that i could call the variables like i would if i had OrderRel attached.
Please let me know if that made any sense.
Here is how i got to the end goal, i had some help from UTaylor, he webex’d over early in the process and gave me some direction.
TL:DR- I used a sub Report on the packslip to create a secondary report that grabs the order via the OrderNum to pull all of the open lines that are on the order. This gets you the parts on the order number from the packslip so that the person can know exactly how many lines/items have been shipped and how many have been backordered.
For those that want more info the solution is as follows:
1: Took a copy of the SSRS Version of the packslip and called it by a different name.
2: I modified the packslip so that it can expand and contract with various parts of the pack slip that are
not needed, like in the ship to and sold to.
3: I then went and created another report that is located in the same folder as the Custom Reports PackSlip, I called it TestReport. (i know how original)
4: Give your new subReport a parameter that it can use from the main report.
I used the Order Number from the packslip off of the shipdetaildataset that was preconfigured.
5: Within this TestReport, i needed to create a data source, Right Click DataSources and add New,
Name The Datasource so that you will not get it mixed up if you have multiple data sources.
Use a Connection embedded in my report.
Connection Type: Microsoft SQL Server.
Click the Credentials Tab,
Click the Use this UserName and password option,
NOTE: This should be a READ Only UserName and Password.
This will be a user name and password that has access to the specific databases that you need to get info from.
6: After the Data source is figured out, Then you need to create a Data Set.
Click the Use a Dataset Embedded in my report.
Choose the data source that you just created in step 5.
7: Now comes the Big part, Creating a Query,
Obviously if you have done this before its not that bad but for people who may have this issue in the future just look up basic SQL query and you should find something.
I only needed to get the OrderDtl table along with the ShipDtl table and combine them so that i was able to get the items that i needed to finish the report.
I was able to get the query to run based off the parameter that was passed to the report so that the only thing that i am getting is the one order from the query.
8: After you got the query all figured out, you need to add fields.
Fields are the items that you will be using in the report itself.
i added a field for all of the items that i specifically called from the query,
OD_Company as the field Name and OD_Company is the field Source which is the same name from the query.
9: Once you get the report for the items you need you can sub report the TestReport back into the pack slip. when you go to add it into the pack slip make sure that you place a rectangle first, i say this because if you don’t then it will always run the sub report every time that you run the pack slip. I also added a page break so that it does not get caught on the page break and spilts the back order report onto multiple pages.
I put this piece of code into the visibility of the rectangle as i don’t want the sub report to show if the back ordered quantity is not greater than 0.
=IIf(Sum(Fields!Calc_DspBackOrdQty.Value, “ShipHeadShipDtl”) > 1,False,True)
Inside of the rectangle i inserted a Sub Report. what this does is calls a report that you declare,
in the box for the Use This report as a sub report. When you click browse it should open up into the folder where the pack slip report is at, this should put you in the same folder where you saved TestReport. Choose your report that you want to Sub Report into the packslip.
it will give you this really long set of folders that it should be in,
/Epicor10Test/reports/CustomReports/PackingSlip/TestReport
we want to pair this down to what actually works when it is in the report.
TestReport
This should be the only thing that you need to have in the box to get the subreport to run. It sounds funny but it works.
Click on the parameter tab,
Name should be the Parameter that the sub report needs to use, and then the value is what it should use from the packslip report for the value of that parameter.
Mine was OrderNum: =First(Fields!OrderNum.Value, “ShipHeadShipDtl”)
After that i ran the report builder and made sure that it worked and printed out the packslip with the backordered lines that are and are not on the packslip.
EX.
2 Likes
Nice work man!
Does anyone have advice on how to do this same thing for Public Cloud Clients?
I’d suggest creating a new thread but reference back to this one, your request pops this thread to the top.
You might get more interaction with your own thread!
1 Like
I had similar requirement to print in Pack Slip and invoice. I have added a UD field in ShipHead to store the back order details and updated using a BPM… Its a snapshot and no matter when pack slip/invoice is printed, it will remain the same. Different view.