We have a report that is generated daily and attached to an email via the System Agent Scheduled tasks. However, if there is no data generated in the report we would still like the email sent with a message stating there were no records for that particular day.
Is something like this possible with a BPM or does anyone have any suggestions on how we could accomplish this?
Thanks for any suggestions,
Carol
Is this a BAQ report? If so make sure it has data. You could use the company table to ensure you always have at least one record. After that you should be able to test if you have “real” data in your report and display meaningful message if there isn’t any real data.
Yes it is a BAQ report, but not quite sure what you are describing.
There is real data in the report now, but there may be times when no records will be returned if the criteria is not met.
Right now the report provides a list of parts if certain data is not entered in a UD field. If all parts specified in the query have data in those fields nothing will be returned.
Does that make sense?
Thanks
Carol
You could make a Sub-query that is a UNION to the top level. And just make each field of the sub-query be a calc field (in the same order and type as the top level query). Make the calc fileds be static values. Then in the RDL,hide any row that matches the static text.
Make sure the static values you use in the sub query don’t get caught by filters of the BAQ report. This might be tricky if your BAQ Report uses a Date or date range as the filter.
Calvin,
We tried your suggestion yesterday and it worked wonderfully. Thank you so much for your help!
Carol
It does have a major weak point…
If your BAQ report uses a date field, like if you wanted a list of all the Packers with a user specified date, it might fail.
This is because that Union’d subquery just adds an extra row with fixed values in it. If my original BAQ had a column for ShipDate, my subquery would need to have one too. If I set it to 1/1/1900, then when the BAQ Report runs, if the user specified date was something other than 1/1/1900, then that row would be filtered out when the BAQ executes. If the user entered a date with no shipments (and wasn’t 1/1/1900), the data set would still have no records.
I haven’t been able to figure out how to make sure the BAQ results would not be empty in a case where the user input filters the results.
An alternate approach would be to monitor the SysTasks for the “No records” type of error, and generate a report stating such.
Calvin,
Thank you for noting that. Will definitely keep that in mind for any future applications we can use this with.
Carol