SSRS PSA on Filtering Subreports Performance Fixing

Kind of taking this thread sideways (at least I didn’t bring up Crystal Reports :slight_smile:)…

You can also think about using the newer BAQ based reports that came in 10.1.600. This is the “standard” reporting framework where you can have multiple data sources. The new feature is that the data sources can be BAQs!!! The only caveat is that you can ONLY have BAQ data sources. The “older” BAQ Reports were a little simpler to build but only allowed a single BAQ. That meant you had to join the data from all of your tables together to fit into the result row. Depending on what you are doing this can be much more efficient.

Hi @JeffLeBert welcome back! Any chance you want to expand a bit on this? I got an expert’s corner article with your name on it.
What’s this new way of doing BAQ Reports? As far as I know we are all still doing it as above.

Pretty please with a cherry on top? @hkeric.wci says that @Bart_Elia will buy you a drink (of your choice) next time he sees you!

2 Likes

Offer him soylent and he may bite

2 Likes

No need for me to write up anything. Go to the wonderful Epicor ERP User Guides / Epicor Implementation User Guild / Reporting Tools / Multiple BAQ/EI Reports.

This is very similar to BAQ Reports (on purpose). Many parts will look very familiar. It is a little more complicated because it is a lot more flexible. Hopefully the balance between simplicity and flexibility is correct. After you see this, I hope you will love the new “Multiple BAQ” reports as much as I do.

The basic steps are:

  1. Create an RDD.
  2. Add in the BAQs you want to use as data sources.
  3. Add the relationships between the BAQs. This is the obvious parent columns match to child columns stuff.
  4. Add a Report Criteria Set. If you are familiar with BAQ Report, this should be fairly familiar. The report criteria you add here will show up on the free report UI later.
  5. Create a Report Style.
  6. Generate the “empty” SSRS RDL. This will have everything set up so that you can start building the report in Report Builder.
  7. Create a menu item to run the report from.

At this point, you should be able to run your report like any other report.

The Epicor docs are better than I could create so go there first. I’m happy to help beyond that.

7 Likes

Interesting, all this time I thought EI was for Financial Stuff Only! Going to try it today! Thanks Jeff.

1 Like

The Electronic Interface (EI) stuff was a “tool” we had on the shelf for running custom code. We needed that in the “Multiple BAQ” reports to be able to support everything from calculated columns to stuff that could radically change how the report worked.

Another place that people probably don’t know even exists is Electronic Compliance. This area is much more general purpose than the name implies. Basically, this will let you take the output of an RDD and create an XML, JSON or CSV file.

Both the Multiple BAQ and Electronic Compliance were built at the same time. When you look at it from above, you start seeing the how and why.

1 Like

Woot! I could probably use this to create my Service Connect XMLs, which I currently create via BPMs… Easier to invoke a Generate on the Report (cleaner) from the BPM by calling the BO. (If I can specify an output path)

Not to mention building your own AutoPrint Labels .csv for BarTender. We all do it via BPMs, Customizations … could prob just invoke a EC Report.

Do any of those 2 require additional License? Module?

2 Likes

Here’s what the docs say. I tend not to remember stuff so have to look it up every time. On the Electronic Compliance Maintenance UI:

This program is only available if the Electronic Compliance Reporting license is installed. No license is needed when importing Electronic Compliance components using the Solution Workbench or command line tool, however, viewing the electronic compliance solution requires a valid license.

1 Like

Thank you for the clarification, I think I got confused thinking EI and EC are the same thing :slight_smile:

Will have some time this week to try EI atleast. Awesomesauce.

1 Like

I’m disappointed that you can’t use a BAQ datasource with table datasources. It seems liked it wouldn’t have taken much to allow that capability, and it would be really helpful when trying to supplement data in native Epicor reports.

I asked for that capability back in E9, then when we upgraded to E10 I saw the new Report BAQ tab in the Report Data Definition form and thought my wish had been granted, but nope, not quite.

Still a useful feature though.

3 Likes

Does this work on standard reports? I’m working on a modification to our SSRS ARForm and I keep getting errors. Do you have to put all Parameters into the where statement?

I’m not the expert on the actual RDLs but I think I can say what you are doing wrong. The RDL Parameters area you are pointing at ONLY gets the TableGuid filled in when the report is run. The report parameters that come from the report UI would be in the Datasets\RptParameter table. I would go to the E10 documentation. There should be documentation on how to add parameters to existing report. If not, you could ask the docs guys to add it.

1 Like

@Randy you don’t need single quotes around the InvoiceNumber but otherwise it looks fine. What error are you seeing?

If I would have had time to get BAQ data sources and table data sources in a single RDD at the same time, I would have. This is a killer feature, but not enough people even know you can use BAQ data sources in a new RDD. If you want this feature added, say it loudly. I’m working in another area of the product now, but will champion any suggestion that we add this. :slight_smile:

4 Likes

I tried a variety of variations of the query syntax but all error out. I even took the whole query into SSMS and adjusted the statement to use the GUID and it works fine, so thinking I’m missing something else.

If I run the main report it’s just the generic “Error: Subreport could not be shown.”

Ending syntax:


WHERE T1.InvoiceNum = "+Parameters!InvcDtl_InvoiceNum.Value+" "

You’re saying I should add the Invoice Number parameter here?

Would that be applicable to a subreport, though?
All of those parameters he’s passing are pushed in from the main report, so they should be available to filter on within the query.

@Randy what happens if you remove the parameter and hard code an invoice number? Does it work then? That would confirm if the issue is with your parameter vs syntax.

Eureka!

If I hard code the invoice number the preview on the subreport worked but main report would still say “Error: Subreport could not be shown.” It was driving me :crazy_face: so I slept on it.

Today I kept working at it and trying various Google searches, finally found this article. Since both InvoiceNum and Company are columns in the dataset I changed the query where to:

WHERE T1.InvoiceNum = @InvoiceNum AND T1.Company = @Company "

Got another error that the parameters were not defined, "Must declare the scalar variable "@… " so I added them to the dataset’s parameters tab and Eureka!

7 Likes

Did you dream about the answer?

I think many of us have had dreams about work from time to time :nerd_face:

1 Like