So we just learned a very valuable lesson with SSRS that I think is worth sharing. When you are writing reports, particularly BAQ Reports in Epicor and you are using Subreports BE CAREFUL on how you filter the sub-report.
DO NOT, I REPEAT, DO NOT Filter the Subreport in this Tab
Because of the nature of SSRS and Epicor being a “dynamic query expression” SSRS is unable to apply filtering ahead of time so it executes the dataset first, and then it filters. This means that if the data you are retrieving from epicor is 40K rows worth of stuff, and you are running your subreport (say a summary per line) you would end up running 40K times the number of Details which is a VERY VERY Large number.
This is a before and after of the “SAME EXACT REPORT” notice the bottom one has over 19 million rows and it took over 1.5 hours to render / run. Yet the data coming from Epicor came in seconds.
So how should you do it?
Do your subreport filtering inhere
This way the data will be filtered properly and you will be much happier. You have probably been doing it “wrong” for years without issue because you are working with small data-sets but trust me if you ever have a large report you will want to do this the right way.
Note in the example above we went from 1.5 hours to 9 seconds… that is a GARGANTUAN gain in performance.
Makes Sense… The Filter is merely a Post-Filter, just like Epicor Dashboards… Where the Expression is Pre-Filter, the feature we all want in Dashboards. and ware waiting on for decades.
I should clarify that in regular (non Epicor) SSRS the filtering works correctly because normally the SQL Expression isn’t dynamic. So it is able to append the whereClauses. Because Epicor uses the dynamic expression this native functionality breaks. (or under performs)
(Picking my jaw up off the ground)…that’s AMAZING! Thank you for that tip! I will definitely keep that in mind the next time I want to apply filtering on the Datasets in my EPICOR SSRS report. Wow!!!
Kind of taking this thread sideways (at least I didn’t bring up Crystal Reports )…
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!
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:
Create an RDD.
Add in the BAQs you want to use as data sources.
Add the relationships between the BAQs. This is the obvious parent columns match to child columns stuff.
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.
Create a Report Style.
Generate the “empty” SSRS RDL. This will have everything set up so that you can start building the report in Report Builder.
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.
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.
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?
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.
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.
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.
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.
and ware waiting on for decades.
(Picking my jaw up off the ground)…that’s AMAZING! Thank you for that tip! I will definitely keep that in mind the next time I want to apply filtering on the Datasets in my EPICOR SSRS report. Wow!!!
