SSRS report with 3 sections per customer using 1 BAQReportResult dataset? Seems impossible!

Epicor ERP 10
, ,
1

First, let me scream… SSRS SUCKS! I despise the fact that SAP acquired Crystal Reports!
Okay, moving on…

This report seems impossible to create but I am sure that it is just my lack of SSRS knowledge that makes it seem so! However, just to confirm that it should be possible, I am posting this topic. Btw, I have reviewed countless previous SSRS-related topics here and Microsoft’s documentation, too, so this isn’t a matter of not bothering to try on my own.

I am trying to create a simple (at least in my mind) AR past due invoices reminder report using a single BAQ and BAQ Report Designer. Using the same BAQ, in about 20 minutes, I created a dashboard with 3 grid views (for past due invoices, current invoices, and unapplied credits) and 1 tracker view (for CustID filter) that works perfectly. The BAQ has a sub-query with a distinct result set so that the BAQ results include only customers with at least one past due invoice.
Unfortunately, trying to create a SSRS report that mimics the dashboard has been a nightmare. All that I want is a paginated report that breaks according to Cust ID and for each customer lists their past due invoices, current invoices and unapplied credits, in separate sections. I cannot imagine that such a report is not possible and it seems like it should be easy, just like the dashboard. I can get the report to list all of the invoices returned by the BAQ, but the moment that I try to filter or group the records, the pagination goes crazy. At this point, I am working with just a single table, trying to get it to group by CustID and filter by invoice type and due date. If I can get that one to work, then my intention is to copy it twice and modify the filtering in the copies.

Here’s a list of the fields in the BAQReportResult dataset:
Customer1_CustID
Customer_Name
InvcHead2_InvoiceNum
InvcHead2_CreditMemo
InvcHead2_InvoiceSuffix
InvcHead2_InvoiceAmt
InvcHead2_InvoiceBal
InvcHead2_InvoiceDate
InvcHead2_TermsCode
InvcHead2_DueDate
Calculated_DaysPastDue
Customer_PrimBCon
CustCnt_Name
CustCnt_EMailAddress
InvcHead_Company

So, could someone please confirm that this report should be possible? If it should be, am I just taking the wrong approach and making the creation of the report much more difficult than it should be? For example, would it be better to use multiple BAQs and create sub-reports? I am open to any and all suggestions.

2

Scott,

What version of Epicor are you on?

3

10.2.200.40

4

Scott,

One idea is to make one baq with 3 subqueries unioned together. Create a calculated field in each of the BAQs called type. In the type field for the Past due baq put “PASTDUE” and for the other ones make the field be “CURRENT,” etc…

Now you have one dataset with a unique field that you can group on (the type field mentioned above) to make them in separate sections using a page break between groups.

I think you can then break and route based on the customer field…

Try it out and let me know. I’ll also try it on my side cause i’m curious.

2 Likes
5

Alternatively, there is the AR aging report which does very similar reporting… You can break that on custID and also email it.

2 Likes
6

Alternatively you could make all of those subqueries and then make your top level query be the customer table and left outer join the customer table to each subquery. That way you don’t have to worry about the union aspect of it where all the fields and field types have to match. But given that you are going off the invoice table, the union could still work.

1 Like
7

Thank you for the suggestions, Utah. I received another suggestion that will use the dataset as it currently exists. I’m going to try that, but if it doesn’t work, then I will certainly try your suggestions. To which AR aging report were you referring, btw?

1 Like
8

image

9

My apologies, Utah, I misread your reply. Still, I like your suggestion. Creating a new style with the custID breaking might be a good way to go. It won’t have credit memos in a separate section, but that might not really matter. If nothing else, the aging report is a good example of the grouping that I want, so I will take a look at how it is configured.

10

You could somehow I suppose modify the RDD for that aged receivables report maybe and create a calculated field for credit memos?

11

have you tried doing it with multiple tablixes? One for each of the sections of a customers statement. Enable page break on change of customer.

Or maybe make subreports that all use the same BAQ Results.

I think trying to get the BAQ to have its data grouped and/or ordered to make the SSRS easier is the wrong way to go.

One last thing… If you have the Report Break/Routing module you could have it break on customer, and then design the report as if the BAQResults only ever had data for a single customer.

3 Likes
12

Calvin, that is great advice too!

13

Thanks, Calvin. It is my intention to use multiple tablixes, just as you described, but I am just trying to nail down Tablix1 and then I’ll copy and modify that to create Tablix2 and Tablix3. If I cannot get this report working properly with the single dataset, then I probably will resort to the multiple BAQ and subreports tactic.

1 Like
14

There are many ways to get to this goal and some very good suggestions already made by @utaylor and @ckrusen. I’d probably fall on Utah’s idea of using the Aging report as the base unless you’re making a total custom BAQ/RDD report.

1 Like
15

What I have observed (although my knowledge of SSRS is very limited) is that SSRS and dates do not work very well sometimes. What I have done to overcome this is to create a calculated field in the BAQ itself to differentiate between various criteria. Say put a number 1 for current, 2 for past due, 3 for CM and so forth based on your use case.
I then use this calculated field to group/filter the data in the tablix. This approach has worked for me so far. It is also easy to test it working in the BAQ itself.

3 Likes