Accounting wants some fields added to the Inventory WIP/Reconciliation report. Things like our PO # when its a PUR-??? transaction, and the OrderNum for ???-CUS trans.
The InvWIP RDD for WIP/Recon only has two views(Recon and Recap), no tables. So it looks like linking tables in the RDD is a no go.
So I attacked it by creating sub-reports (with datasets that connect directly to the Live DB) in the RDL, and parse the “Reference” field to get linkable data.
A sample Reference value for a ???-CUS tran is “Cust:0005587 PS:27531”
A sample Reference value for a PUR-??? tran is “Supplier: WAY44 PS: 113486”
The first subreport is of the RcptHead (sp?) table, linking the VendID and Vend PS from the Reference field.
The second is similar, but with the ShipDtl. Linking the PartNum field and the and Packer from the Reference field.
It works, But I prefer to use sub-reports as a last resort. Is there a way to link tables to the Views in the Wip/Recon RDD?
Note: This breaks when trying it in the test company, as the sub-reports are linked to the Live company.
Been thinking about this for a bit. Does that have to happen for historical reports or moving forward? The reason I ask is I believe you can change what the reference field says in the posting rules quite easily. I think there was a session on it at the last Insights and the EUG did a presentation a few months ago.
Interesting … I had no idea the “calculated” reference field was controlled by posting rules.
Which means it must be created and stored during the posting process. I thought it was calculated (in the RDD) when the report was run.
I thought PartTran was the primary table for the WIP/Recon report. But it’s not in there.(the source info for the reference field is, but not the actual reference field)
That can’t come from the GLJrnDtl, as those records are “summaries” of transactions. The GLJrnDtl.Description field of those is always “Periodic Posting Process”.
What I’ve done is to parse that Reference field, extracting the Vendor ID and Packslip, and passing them on to a sub-report to determine our PO num for those lines.
You are correct, it does not pull it from there but that’s where it ends up when you run the Capture COGS. I’m pretty sure it’s calculated from the posting rules though and not pulled from anywhere.
If it comes from the Capture COGS, how come there are values in the WIP/Recon report prior to running Capture COGS?
Anyway …
Is it a bad Idea to do what I’m doing? That would be using Subreports that don’t use the master reports dataset, but rather use ones that draw right from the DB. And use fields from the master report’s dataset as parameters to the sub’s datasets.
If you think about what the WIP/Recon report is doing (telling you which account numbers are going to be credited and debitied), the only way to get that information is through the Posting Engine. That way, if something is out of place, one can alter a GL Control so the transaction goes to the right accounts.
It is quite possible that I’m FoS on this and there is some secret place where the reference field is calculated - right in the report extract. Someone might have to do some dotnet peeking to see what’s really going on there…
Now, having a single transaction - you should be able to retrieve the source document. As you’re finding out, the linkage changes based on the transaction type and that makes the RDD very difficult to do except for a single transaction type. Maybe there’s a way to download the report into Excel and then use a look up there to get what you need.
I have 4 report variables that I set based on the TranType and Reference values.
if TranType = “PUR-???” then extract the VendID, and vendor’s Packslip. These get passed to the subreport to display our PO for the receipt.
if TranType = “???-CUS” then extract the CustID, and Packer. These (along with the PartNum) get passed to another subreport to display some info from the order (linked via the Packer line).
My fear was that this was very taxing on the SQL server when running the WIP/Recon report - which is already long enough as it is.
I would like to do some more modifications to this for our accounting department. @ckrusen was wondering if you could share your copy of the SSRS reports and Subreports. So we can see what that does for us and modify as we need thanks…
I have a similar problem at one of my customers: they want to have additional information on the COS&WIP Reconciliation report (ex: LaborDtl data). I have raised an enhancement request with Epicor Support Europe to add to the Recon Table the Parttran.trannum field. This would enable us to do the proper link to Parttran and get anything we need.
However it needs to be approved, will take some time and by the next upgrade will be there.
In the meantime the only solution I found is to store the needed data into the transaction description (with some separator) via posting rules changes, mark the posting rule revision to always go to review journal and from review journal you can extract data via a dashboard into excel. If all good, confirm the transaction, if not cancel it, fix the problem and repeat. There are only 2 problems: GL description field is limited in length and you have to work like 2 days to get all COS&WIP posting rules have the new description instead of “Periodic Posting”.
I would love this. Do you use this to reconcile AP Clearing? That’s what i need! But this canned WIP/Inventory report doesn’t show PUR-MTL and PUR-UKN transactions for some reason!? When I run the g/l detail for our AP Clearing (2151) it shows 5 journalcodes: ADJ-PUR, PUR-INS, PUR-MTL (doesn’t show on report), PUR-STK, and PUR-UKN (doesn’t show on report). ANY help would be appreciated!
