Get data behind reports

Epicor ERP 10
1

Is there an easy way to get the report data in a format suitable for download into Excel? No headers, footers, subtotals; just one record per row. I am finding myself attempting to re-write reports in a BAQ but it seems like since the logic is already there, we should be able somehow to access the data.

The downloadable Excel versions have a whole bunch of empty rows and it takes forever to go through and remove them just to use the data.

Anyone have an easy way to get at the report data without rewriting everything in a BAQ?

Thanks

—sam

2

Is it an SSRS Report? If so the Data Exists in the Reporting DB, all you have to do is write a Query using the GUID generated by the system when you run the report. Most reports you can run with a “Generate Only” option which will generate the data but not run the SSRS Report.

3

In some cases, we just copied and modified the Standard SSRS report so that it would not have multiple blank rows. One cause for it is field size & alignment, by correcting it you can then use the Excel option. We actually have Report Styles called ‘Excel Safe!’

4

What Jose and Rick said.

Also, we found that CSV works better than Excel. If there are totals, you get a lot more columns but at least you don’t get the extra blank lines and two-line entries.

Also, I don’t do custom reports any more - only a dashboard. The users can cut and paste to excel for a print out.

Mark W.

2 Likes
5

I second that!!!

1 Like
6

I third!!!
(heck, I’m down to just using BAQ Searches many times)

2 Likes
7

Thanks for this idea – seems very logical.

Do I write the query using BAQ or something else?

And how/where do I get the GUID and use it?

Thanks.

8

Can you get this work within epicor? I’ve only done it a couple of times in the SSRS studio.

9

Have you tried using the External Datasource & External BAQ’s?

10

You can use the external datasources to use with within Epicor.

11

I have not, that’s basically what I was asking. I didn’t know how the external BAQ would work with the GUID. Is there a table that can be look at with a parameter to get the GUID? Does each report have it’s own table? My knowledge of external BAQ’s is pretty much none so far, something that I would like to learn soon.

12

The SysTask table has the GUID in it. Yes each report that is Run has it’s own table… actually external Query may get hairy for this… HMMMMMMMMM

13

That’s what I was wondering, how do you set up the fields on one large system table?

If each has it’s own though, you would have to set up a new BAQ for each report, but as long as you are looking at the same report table, you should be able to make some sense out of it right?

14

Right … this is too hairy for that solution, I take it back. I recommend you write a simple version of the SSRS Report that is formatted for exporting as CSV / XL.

1 Like
15

Hmm… So I am hearing the need to point at an existing Epicor Report form Excel?

Everyone knows about Excel to REST at this point right?

So I hear a request for Excel to REST Reporting?

4 Likes
16

At some point I want to learn how to set that up (we need to upgade from 10.0 first, but that should be soon)

2 Likes
17

@josecgomez presented on the Rest to Excel functions at Insights.

3 Likes