I am new to creating reports in Epicor, and so I’m trying to figure out my best options and the steps I’ll need to complete. Here is our current Commission Payable report made in access. We want to get it moved into Epicor if possible, and I believe I can use RDD or BAQ Report designer?
I’m also wondering how I can get that data into format that I need it in. Currently, I have two different BAQs. One is our Cost of Goods report. that report lists our invoices by line items along with commission splits for each commissionable part.
The other is our Monthly Sales report, which lists our orders by line items with the rep information and split percentages.
I’ve linked both BAQs…I’m not sure how to match the dataset together to get the data I need…because I’m pretty sure I can’t use both BAQ’s in the report?
Also to help explain our current report a little more…It is a paginated report split by Rep.
Commision Due is every invoice that has been paid. Not Paid - Shipped, is every invoice that has not been paid. And Production is every Order that has not had an invoice created yet. The commission for Commission Due / Not Paid-Shipped is based off of the total amount of commissionable line items for an invoice after any split has occured. If it is in Production, it is just the total of all commissionable line items in the order after the split has occured.
You can create a report from scratch and make your own RDD containing 2 separate BAQs. They will show up as two separate datasources and able to be referenced on the report. You can either use them independently or you can combine them using a query on the report. Don’t do a BAQ Report. That is limiting you to just one BAQ.
If you’re combining the BAQs in the report into one table, you may want to create a relationship between the data sources. If they will be separate, then you don’t need to do that. I didn’t on this one.
Save that sucker, go back to Report Style and go to Actions > Create SSRS Report. Boom, now you have a blank report out there with which to do your best SSRS work.
If you need criteria on the report like filters and options, you can add them using the Report Criteria Sets. If your BAQs have parameters on them, just create a new Criteria Set on the RDD and go to Criteria Mappings. Check the parameters in the list and click the Create Report Criteria Prompts for Selected button. Then you just have to choose that criteria set from the Report Criteria drop down on Report Style and you’ll get prompts on your report print screen that map to your BAQ parameters.
I am getting an error while trying to create my report style. I’m not sure if it is the report location, but even when entering something similar to the one you showed…it still gave me the error.
You’re right…I thought it was gonna auto create the definition but it didn’t so after some fiddling around I got it to work and I have my SSRS report…now I just gotta figure out how to put the data the way I want it
Ah, so I’m coming back to this because the company does not want to allow me to connect to the SSRS server to work on creating the custom reports. Theoretically I could make the changes locally and upload every time I want to test it, but that would be a major pain.
Going back to my original linked BAQs. Is there any way to merge them together, so I don’t need the custom report? The Cost of Goods essentially shows the invoices for orders by line item…and shows the commissionable items for each invoice. The RepMonthlySales shows each order by line item along with the commissionable line items. Is there a way to merge the data together, and group the part numbers together by order and invoices? For example…I’ll need records of any invoices, but then if the invoice doesn’t take up the complete balance of the order, the order will need to stay in Production with whatever balance is leftover.
I’m not sure I understand. Creating this report vs a BAQ report is the same as far as what you need permissions to access. You don’t necessarily have to have access to the SSRS Server…
I have all permissions within Epicor itself…I just can’t run the report locally…because I do not have an active connection to the SSRS server. So in order to actually run the report to test changes, I’d have to import it every time I wanted to test a change.
I guess the way I’m thinking about this…is can I make a subquery that shows me each line item of an order…and if that line item has an invoice or not. And then we group by order for part numbers that do have an invoice vs those that don’t. Then I have at least two different rows that would show up on my BAQ for an order…until all line items of an order have been invoiced.
That can be frustrating. But it’s no different than any other report you might change/create.
If you want to merge them I think you can. You’re on the right track. Basically just have to recreate the one query as a subquery in the other and then join those together on OrderNum or something to that effect. But I just don’t understand how that solves the problem. If it’s destination is the report, you’ll still have to edit it locally and then reupload every time you make a change. Maybe there’s a better way? I’m not aware of one though if you don’t have access to the SSRS server.
So essentially what I’ve done is grabbed the necessary data from both tables. It lists all part numbers under an order…and lists an invoice if one is available. It also shows the order total and commission split for each part number. Now I just got to figure out how to group the part numbers together into orders, while keeping orders with multiple invoices, and keeping an order with line items that don’t have an invoice separate. And summing the invoice/order total, and commission total.
@dr_dan I hope you enjoyed the long weekend. I thought I’d come back and just see if you would have any insight on this? I think I’ve made good progress, but I’m not sure how to sum this query together. I have part numbers listed under orders, and with or without an invoice. I need to bring it to a higher level where I just show the orders summed together for each part number with and without invoices. So 3 parts have invoices, and 3 parts don’t. The three parts without an invoice should be summed under the order without an invoice, and the 3 with invoices are separate.
I follow your thought process. Basically, you only pay commission on invoiced lines, right? For me, I used the Invoice tables as my basis and then linked in orders as needed. At the end of the day, we don’t use the sales reps as listed on the sales order (which also carries through to the invoice). If you want to keep going down your path, I think the next thing to do is create a subquery and pull in the current subquery you have. Then group on order, part, and invoice. All your invoiced ones will stay separate but your uninvoiced ones will group together because they will all be blank.
Yea, so essentially we pay commission on payed invoice lines. The report will be split into three sections. Orders without an invoice, orders with an unpaid invoice, and orders with a paid invoice (what we pay commission on). In this BAQ we do have Reps 1-5 for commission split, along with their respective commission. It would be nice to be able to split that up on the BAQ as well.
I think I’ve made really good progress and I’m almost done. I’ve linked my BAQ here because there’s one aspect I’m not too sure about yet. I need to be able to filter the report based on two parameters. One is that I want to be able to choose not to show any orders created after a certain month and year. I also want a parameter to only show Paid Invoices (ClosedDate) in a certain month. This seems easy enough for a “current” report. What I’m not sure how to do is make it work back in time. So lets say an order was made 6/22/2023, and the invoice was generated 7/22/2023, and the invoice paid 8/22/2023.
If I run the report and I want to see invoices paid in month of June, I should not see the invoice paid on 8/1, and it should still show up under Invoice Created Date. And then If I run the report on 7/1, it should show the full balance under 6/22/2023, and nothing should be in invoice created or invoice paid. The issue is if I implemented the paramaters as it, since a record exists with those later dates, it would be filtered out, rather than moving my totals back in time persay.
I understand completely. You’re on the right track and I believe you’ll have what you are looking for soon.
I’ll give you what I got. I think there could be multiple ways to pet this cat. If you’re interested in two separate values in any given date range, then you might make two subqueries of the same data set. One subquery would filter Invoices to ClosedDate within the date range and would represent your paid invoices in a given range. Then the second subquery would filter InvoiceDate within the date range and would represent shipments or invoices created in a given date range. Then you could make a third (top-level) subquery and pull in both of those subqueries relative to a sales rep ID. But are you more interested in the monthly totals, the details, or both? It may make you build a few BAQs or subqueries that are nearly identical.
