Continuing the discussion from BAQ report with stored procedure:
Has anybody done similar work.
“I created a BAQ report to get the pretty prompt page they are used to and added my stored procedure to that report.”
I intend to do the same thing.
Are you referencing external report creation?
What I ended up doing to simplify this, was create a config table to reference in my stored procedures with dynamic SQL. And then inside of E10 I created a new menu that stores URL links to my reports for security.
–create DB_CFG to your liking
declare @DBName varchar(max),@SQL varchar(max)
set @dbname = ‘BHFO’ --This will pull from your DB_CFG table instead of static
–Format your sql string
set @sql = 'select * from ’ + @DBName + ‘.dbo.order_header’
–Execute sql can actually add error handling around this as well. Like a try catch rollback, etc
EXEC (@sql)
Thank you @tfenwick11.
Will we be able to pass the context of user, i.e. company code and the likes?
We would like the report to load just like BAQ but with stored procedure to filter other data.
Something like having a BAQ report load with stored procedure data source.
Definitely something that “can” be done, but it is recommended that you stay within the framework for maintenance, security, upgrade-ability etc…
With the E10 BAQ builder there are very very very few things you can’t do. IMO
@josecgomez Totally agree with you there. I just found it easier to have external SSRS reports for my need as the data requires various amounts of manipulation to be presented to the users. And I could rewrite all the hundreds of reports from scratch way faster than I could in a BAQ. LOL
2 Likes
@josecgomez Can you perform temp tables with BAQ’s?
you can do CTE’s… but you can’t “Create Tables even Temp ones”
Cool, Thanks.
This is exactly we need to do, but wanting to use the context of user, authentication and as much as possible stay in the framework.
Is there a way to really use BAQs and get complex reporting done? I am sure there are many other users needing grouping, unions, filtering, aggregations, etc. done for specific reports that can’t be otherwise done (efficiently) without SQL stored procedures.
@miteshpc What we did for the security aspect was create groups in our active directory with read only permissions in relation to folders on the SSRS server and then recreate those same groups and folders in Epicor to assign at user permissions. So there are two forms of validation if the user has access to the report. And back to what @josecgomez said, with enough experience Id imagine you can get the BAQ’s to do some complex stuff. I think it just comes down to experience and how much effort your willing to put into either internal BAQ reports or External Reports.
1 Like