 |
Get the data you want out
|
I recently gave some assistance to a customer who wanted to be able to access specific sets of data from Epicor ERP in various formats. After providing a workshop session with them I realised how very generic and broad their requirement was.
I know that many people would find this useful so I’ve put together a brief overview of how you can quickly create Business Activity Query (BAQ) Report Forms that allow your users to filter, sort, and export the data they want, in the format they want it (Screenshots and menus based on Epicor ERP 10 but it’s a very similar approach for Epicor 9).
Step 1 – Create your BAQ.
I’m not going to go into detail here about how you create a BAQ in Epicor ERP (I’ll have another blog on that very soon) aside to say, it is a very simple report builder tool where you can list data (in a grid type flat view) from a number of tables and filter, sort, and choose the fields you want to show. BAQs very often form the base for various other types of displays in Epicor ERP. Once you have built your BAQ (and if you are following this as an example I would just start with a very basic one using data from a single table) then you are ready to get started.
Step 2 – Access the BAQ Report Designer.
The BAQ Report Designer can be found in System Management->Business Activity Queries->BAQ Report Designer
Step 3 – Supply the required info.
The details you need to supply at this point are very minimal. Simply provide a ‘Report ID’ and a description. After that point you are required to supply a ‘BAQ ID’ but you can hit the ‘BAQ ID’ button and search for the BAQ you created earlier. As mentioned previously, your BAQ forms the base for your report and for the report screen you are designing. After choosing your BAQ you can specify a Form Title that will appear in the top banner of the report form when it runs.
Step 4 – Design your SSRS report.
At this point you may be a little worried about carrying out this step, especially if you are unfamiliar with SSRS reports (I’m assuming your server is setup for SSRS reports rather than Crystal reports). Don’t worry too much as we aren’t going to get very complicated in terms of the report itself (but I will be adding some blog posts in the future on SSRS reports). The creation of the report itself however has been made very simple with the ability to go directly into the SSRS report from the ‘Actions’ menu. What you may not have realised is that earlier, at the point of specifying your Report ID, an SSRS report name was defaulted into the form for you. You simply need to choose the ‘Actions’ -> ‘Design SSRS Report’ option. This will generate a skeleton SSRS report for you automatically and open it up in the SSRS Report Designer.
At this point it is best to just simply add a tablix from your dataset which will put all the fields in your BAQ onto your SSRS report. You can always adjust the report and add to it later on. Save the SSRS report at that point and exit the SSRS Report Designer.
Step 5 – Add Option Fields, Filters, and Sorts.
You can give your users the ability to be able to filter the data that will be reported on and give them a choice of how they wish to sort that data. So – back in the BAQ Report Designer screen in Epicor ERP use the ‘Option Fields’ tab, ‘Filters’ tab, and ‘Sorts’ tab to provide your users with some filtering options for their report. These are very simple to add but you will find that you can only filter on certain ‘Key’ fields from your BAQ. Once done you can choose the ‘Test Report Form’ from the ‘Actions’ menu to try it out.
Once added to the menu this gives your users the ability to get the data they want out in the format that they want it in. The ‘Format’ drop down box in the BAQ Report form offers options for XML, CSV, PDF, Excel, and HTML.