I have created a BAQ report to show an employee time sheet for a specified pay period. If an employee did not work during a specific period, the report throws an error “No Records Selected”. I know this has been a requested feature to not throw this error as some reports may not have any data and be valid. Is there a way to customize the report so that it returns a report with no rows or a message in the report that no records were found?
1 Like
If your first table is the Emp Basic table, you could throw a left outer join on the LaborHed/Dtl table. This would make always at least one record show on the report.
For other problems to circumvent this “Error”, I have usually used a User Code. A good example would be monthly buckets for Supplier Scorecards.
3 Likes
Already joining to LaborHed. The query returns all LaborHed records for all Employees. The BAQ report has Report Options for date and filters on EmployeeNum. There are no records in LaborHed for that time period, so no records are returned.
Take a moment to poke around with what I’m suggesting. No records would be returned if LaborHed is joined with an Inner Join. If you change it to a left, records would still be returned in the report for employees without time.
I’ve never clocked a single hour, but my employee record still gets returned.
1 Like
I have done what Chance said.
I think you could also mess around with counting the rows in the datatable in the SSRS .rdl and then hiding or showing a text object that says “No records” based on the result of the count.
1 Like
I tried this and yes it does work in the BAQ query, but I am creating a BAQ Report from the query and the report filters the results. How do I pass the date parameters from the BAQ Report to the BAQ query, I don’t see that option in the BAQ Report Designer?
I think it is BAQ report options?
I checked, I don’t see the parameters in there.
I don’t believe they have ever allowed BAQ reports to natively allow for parameter pass through.
You can search around on here though, I know some gurus have done it.
Here:
BAQ Report Parameter - Help Needed - ERP 10 - Epicor User Help Forum (epiusers.help)
This link is the parent of the previous link:
BAQ Report Parameter - Help Needed - ERP 10 - Epicor User Help Forum (epiusers.help)
Note that @cfinley didn’t use BAQ report designer. In other words, I don’t think what you are trying to do is possible through BAQ report designer, but I could be completely wrong. I don’t have a ton of expertise in this area as it pertains to BAQs with params.
I checked around, but did not see anything. I will check again. Too bad, because this will not work unless the date is restricted to the pay period required.
Look at my previous post. They were able to use a BAQ with parameters in that post.
However, I don’t know if what they are using (an RDD with BAQ report data) is available in 10.1.500. For some reason I have the understanding that using a BAQ as a report data definition datasource wasn’t an option early on, but that could be because I wasn’t aware of it- not because it wasn’t available… 
1 Like
Thanks, I will check it out.
In BAQ reports you do not use parameters on the BAQ itself. Option fields on the BAQ Report Designer act as parameters. You can also define filters there which act like filters on the BAQ. So remove the parameters from the BAQ and define them on the Option Fields.
In the RDD reports based on BAQ you will need to use parameters defined on the BAQ itself and these are mapped in the RDD.
Vinay Kamboj
1 Like
If all else fails, I’ve used the Company table. There’s always a current company… Although if it was a multi-co report, I guess even that strategy fails.
But I totally agree with what @cfinley and others said.
Union the entire results with a set of calculated fields, all set to ‘’, 0, false, etc. Then in the SSRS, just check that your key row isn’t empty (assuming it’s a nvarchar PK). It will bring in the empty row to the report, but then not display it and should either hide the table or chart, or show the “No data” message within the report.
edit:
My reason for this over opening all results up, or using another table with a result always: Because table joins are expensive, and especially if there’s hundred thousands of results. Trying to filter that in SSRS is going to slow things down. Have the BAQ filter it, then append a “blank” row, then filter off that single row in SSRS,
2nd edit: You could also get fancy and make the union calculated field record indicate there’s no record, or no work entries that period, then use the SSRS tablix rownumber function to only display the row if it’s the first row.
2 Likes
This would get around having to recreate via the BAQ RDD route. Good suggestion. Learning that way wouldn’t be a horrible idea, considering its more versatile.
1 Like
There’s currently another thread looking for the opposite of this. They get a page with just their company info, when there are no records used for the body of the report. Their problem is that there is two datasets in the RDL and one (their company info) always has data, and the other - for customer statments - might be empty.
Take advangtage of this by adding a dataset that will always fetch something (even have it point to the Erp.Company table), and put a filed from that dataset in the report (hidden or “white on white”)