As someone who has become decently proficient with report creation, something that could add a lot of functionality is some more SQL statements, such as:
- SELECT MAX
- SELECT MIN
- SELECT DISTINCT
by: Ryan B. | over a year ago | Review and Reporting
Comments
It would be nice to have some documentation on the capabilities of the SQL function. This command doesn't seem to handle anything but the most basic SQL Expressions.
If the SQL command was more robust the it would go a long way to removing the limitations of the reporting tool. For many custom reports I develop I need to use Excel or Crystal but then have to run the reports outside Sage 100.
It would also be nice to get some sort of looping function, where it could loop through a table and count each iteration as a "detail" item in the reports.
I'm not really sure why the SQL commands are limited. It's built on SQL server and I know several system reports use SQL commands, because they can't be replicated without it due to the single table join we are allowed when creating custom reports. It's intentionally limiting. I need to make all my queries using MS query
I also just learned about the 'UNION' command in SQL. This would be one of the most amazing additions. It would allow you to refer to several tables with one query, a feature that many have requested.
I would like to see "OUTER JOIN", "RIGHT JOIN", "LEFT JOIN", etc. Right now I have to run 3 separate reports, combine on a Pivot Table, and run calculations just to get what I need from the Job Summary Report (we use selected cost codes & EE positions for our Foremen's bonuses).