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 ca....
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
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.
(NC)-As the first smog alert warnings of the season approach, Canadians are being urged to use ethanol-blended fuel to alleviate the urban smog problems plaguing many parts of the country. Many regions such Vancouver, Calgary, Montreal and most pa....
Read more
Low Speed impacts are those that take pace at speeds under 10 MPH. There’s often little (if any) visible Property Damage done to the rear of ones motor vehicle. However, those who are struck (especially in the rear) often have injuries, especially....
Read more
Remember back when selling online was a new experience? Dealers tried everything to sell their vehicles online. Today’s market has expanded to include several hot online marketplaces but there is the largest online marketplace, Ebay. Ebay has e....
Read more
Remember back when selling online was a new experience? Dealers tried everything to sell their vehicles online. Today’s market has expanded to include several hot online marketplaces. The largest online marketplace is Ebay. Ebay has evolved in....
Read more
What does BPU™ stand for? (BPU™ is a trademark of SupraStore.com)Basic Performance Upgrades. These modifications are: A full length three inch down-pipe (with or with-out high flow cats), 3” (75mm) or bigger cat-back exhaust system, raised boost....
Read more
