Microsoft RMS and Crystal Reports
 by: Divine Rigor

Microsoft Retail Management System (MS RMS), formerly known as QuickSell 2000, is an integrated retail management software designed to help small and medium-sized retailers to automate their business processes. It runs on personal computers and can be used by practically any type of a retail environment: sporting goods, hobby stores, convenience food stores, appliance stores, liquor stores, just to name a few. It can also help manage a number of store and headquarters operations including inventory control, employee management, purchase orders, and sales tracking.

Microsoft RMS uses worksheet technology, wherein updates from Headquarters are deployed to the stores by generating a worksheet with a specified command. The stores, on the other hand, upload data including sales transaction and receipt journals back to Headquarters. These transactions are not generated from Headquarters. It is, therefore, not wise to attempt data integration of such transactions between the two entities (store and headquarters).

Crystal Reports is a report-writing software that can integrate itself to Microsoft RMS database. It goes beyond the report creation process of RMS’ own Report Writer. As a developer, Crystal Reports will help your client access decision-driving information to aid in managing their businesses more effectively.

The following are some guidelines in using Crystal Reports in Microsoft RMS:

Microsoft SQL Server or MSDE

A native SQL driver can be deployed in RMS to connect to the database targeted for report generation.

You can also use Open Database Connectivity (ODBC) – an integration tool to connect a client application to an ODBC-compliant database. Microsoft RMS uses the ODBC drivers installed by Microsoft SQL Server. Once it prompts you to connect to the appropriate data source, choose the company database that you are targeting that contains the data needed for your Crystal Reports.

Tables Structure

When a business entity is processed, the information is written onto the RMS database tables. The tables structure is self-explanatory. Entities include Customers, Transactions, Supplier and Tender.

Links

The master table in Microsoft RMS usually displays an ID Column and the Transaction linked to it: Transaction.CustomerID=CustomerID. If you were to create a query that will display customer information and sales transactions, do the following statement:

Select b.FirstName, b.LastName, a.* from [transaction] a join Customer b on a.CustomerID=b.ID

Use this knowledge to create links for your report creation.

SQL Views and Stored Procedures

Since Microsoft SQL can be deployed in RMS, you have unlimited access to SQL Views and execute a number of Transact-SQL statements into one using Stored Procedures.

Advanced Techniques and Tips

You can create a link server in Microsoft SQL Enterprise Manager to connect to all ODBC/OLEDB-compliant databases (Ctree, PervasiveSQL/ Btrieve, MS Access, Oracle, DB2, Unidata) and C/ODBC-compliant database such as Navision.

You can also use the OPENROWSET keyword in MS SQL to create a pool of linked databases for your Crystal Reports.

Need more information?
Contact us: 1-866-528-0577
[email protected]

For customization work and advice, please contact Andrew Karasev, Alba Spectrum Chief Technology Officer. His works include development of applications with the use of Dexterity, SQL, C# .NET, Crystal Reports and Microsoft CRM SDK.

About The Author

Divine Rigor is the Technical Writer for Alba Spectrum Technologies USA ( http://www.albaspectrum.com ), a Great Plains, Microsoft CRM customization company. We are based in Chicago with locations in California, Arizona, Texas, Florida, Georgia, New York and contacts in international markets.

[email protected]