Using SQL Commands instead of SQL Views in Sage 300 Reports

By | November 1, 2017

With Sage 300 Intelligence Reporting, you get a number of Customized and user specific reports that befit your business needs. These crystal and .dll reports are fully customizable and ready to use with drill down capabilities.

New Stuff: Modified Tax Summary Representation in Sage 300 Reports

In Sage 300, we use crystal reports to print several transactions such as Purchase Transaction Reports, Sales Reports, GL Transactional Reports, Inventory Stock Reports, etc. Interestingly, Sage 300 allows you to customize these reports and create new reports as per the user’s requirements.

Often, most of the users get stuck when creating different types of customized reports because of SQL views, as it doesn’t get updated automatically on client’s machine and thus, leads to the below error when the user tries to print reports.
To avoid such issues, we use SQL commands in crystal reports. SQL commands help in faster printing of reports and serves as a robust solution to dodge errors like SQL Connection Failed or Asking Database Login.

The use of SQL views also creates error on Integration from CRM to ERP.
For e.g. when we print quotation report from Integrated CRM of ERP, we get below error:
The above login error pops up when CRM user prints quote report from CRM. This error results due to use of SQL views in reports.

‘SQL Commands’ can help discard above errors in Sage 300 MIS Reports.
So to avoid these types of issues, we use SQL commands in Crystal Reports.

Use of SQL Command:

  • SQL expression is often used as a substitute of (or combined with) a Crystal formula. The user can place SQL expression directly in a report or can use it to sort, select and group criteria.
  • SQL command is more like a data source, which defines the data you want to derive from the server. After you set the command, you can use it just like a table.

Advantages of SQL Command:
SQL commands can speed up reports viewing experience whilst solving all complex reporting issues, which otherwise are difficult to address.

How to add command in the report?
According to above screenshot, we can add command in crystal report. You just need to select ‘Add Command’ option and you will get the below screen, where you have to add your query and link with the tables appropriate.
As per the above screenshot, the command has been added and can be used in Crystal reports for further fetching of data.

The main purpose of this blog is to use SQL commands instead of SQL views to avoid all the above-mentioned issues and save time.

About Us
Greytrix a globally recognized Premier Sage Gold Development Partner is a one stop solution provider for Sage ERP and Sage CRM needs. Being recognized and rewarded for multi-man years of experience, we bring complete end-to-end assistance for your technical consultations, product customizations, data migration, system integrations, third party add-on development and implementation expertise.

Greytrix offers unique GUMU™ integrated solutions of Sage 300 with Sage CRM, and Magento eCommerce along with Sage 300 Migration from Sage 50 US, Sage 50 CA, Sage PRO, QuickBooks, Sage Business Vision and Sage Business Works. We also offer best-in-class Sage 300 customization and development services and integration service for applications such as POS | WMS | Payroll | Shipping System | Business Intelligence | eCommerce for Sage 300 ERP and in Sage 300c development services we offer services such as upgrades of older codes and screens to new web screens, newer integrations using sdata and web services  to Sage business partners, end users and Sage PSG worldwide. Greytrix offers over 20+ Sage 300 productivity enhancing utilities that we can help you with such as GreyMatrixDocument AttachmentDocument NumberingAuto-Bank ReconciliationPurchase Approval SystemThree way PO matchingBill of Lading and VAT for Middle East.

For more details on Sage 300 and 300c Services, please contact us at We will be glad to assist you.