Question

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

  • 15 April 2022
  • 4 replies
  • 308 views
slepley
Userlevel 4

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.

Scott Lepley

4 replies

S

I am by no means well versed in SSRS, but I know enough to be dangerous. In the past I have used a template like the one below to get the results that you are describing.

 

Stephen Gambill Assistant Manager, Information Systems St Marys Foundry, Inc. St Marys OH 4193943346 sg@stmfoundry.com
slepley
Userlevel 4

Thank you, @sg !  I can certainly see the logic in that graphic, but I never would have thought of designing the report in that manner.  I am not at all familiar with the use of rectangles, but I will try that and then let you know if it worked.

Regards,

Scott Lepley
G

First, Preach.    Amen.    I loved Crystal Reports and thought SSRS was a step in the wrong direction other than the fact that you can write reports that expand when someone wants to see detail.

 

Second, I noticed early on that Epicor wrote a lot of sub-reports.    This allows them to feed a list of variables or keys to the subsection and thus display subsets of the data in relative order.

 

Another approach if you have Advanced Print Management is for the data to be spooned out is small subsets of the data.    It creates a series of small specific reports rather than dealing with all of the data at once.    I hope this helps.

slepley
Userlevel 4

Thank you, @gpennington , for the feedback.  I may need to resort to the sub-reports approach.  I’m not intending to use APM but rather APR for reporting breaking and emailing.

Scott Lepley

Reply


Terms & Conditions