Crystal Reports for Microsoft RMS Overview for Developer/report Designer

Powering Retail Reporting with Microsoft RMS

Microsoft Retail Management System, previously known as QuickSell 2000, has become the backbone of many retail chains, from small shops to multi‑store supermarkets. The system automates everything from inventory control to point‑of‑sale transactions, giving retailers a single source of truth for all business data. Because the core data lives in Microsoft SQL Server or the lightweight MSDE version, developers can rely on a mature relational engine that supports complex joins, window functions, and full‑text search. That foundation makes it straightforward to pull the data you need for daily sales reports, end‑of‑day reconciliations, or inventory audits.

One reason RMS stays in the spotlight is its tight integration with Microsoft Great Plains. The two applications exchange data at the General Ledger and Purchasing layers, allowing financial statements to reflect real‑time retail activity. Microsoft’s marketing muscle also helps RMS gain traction: partners, channel resellers, and even independent consultants often bundle it with other Microsoft Enterprise solutions, widening its user base. As a result, many developers are asked to extend RMS’s reporting capabilities beyond what the built‑in forms offer.

For a report designer, the real challenge lies in bridging the gap between RMS’s transactional tables and the Crystal Reports designer. The data model is clean but somewhat verbose: tables like Customer, Transaction, TransactionAmount, Tender, and Cashier each hold specific pieces of a sale. Mapping those tables to a Crystal Report means understanding the relationships and the business logic behind each field. A common pitfall is ignoring how the data moves into the headquarters database via worksheets, which can lead to duplicated or stale information in the final report.

Crystal Reports has long been a go‑to tool for turning raw database tables into polished, printable documents. When you bring Crystal into the RMS environment, you gain powerful formatting, parameterization, and export options that the built‑in RMS reports simply don’t provide. The key is to write your queries in a way that Crystal can consume efficiently - avoiding unnecessary joins, filtering early, and using indexed views where possible. This approach ensures that your reports run quickly even when the underlying RMS database grows to millions of rows.

In the sections that follow, we’ll walk through the practical steps you need to take - starting with the database setup, moving through query and view creation, and ending with advanced techniques for pulling data from external systems. By the time you finish reading, you’ll have a concrete plan for building Crystal Reports that deliver accurate, timely insights for any RMS‑based retailer.

Configuring the Database for Crystal Reports in RMS

RMS supports two primary database engines: the full‑featured Microsoft SQL Server and the smaller MSDE edition. Choosing between them depends on your environment - larger chains typically favor the scalability of SQL Server, while small shops might opt for MSDE to reduce licensing costs. Both engines allow you to deploy the native ODBC driver, which keeps connection strings simple and performance high. When you first set up your Crystal Reports project, point the data source wizard to the RMS database instance and test connectivity. A quick “SELECT 1” query will confirm that the driver talks to the server correctly.

Once connectivity is confirmed, look at the table structure. RMS follows a straightforward schema: the Customer table holds customer master data, Transaction records capture every sale or return, TransactionAmount breaks down the totals, Tender records payment types, and Cashier tracks the employee who processed the transaction. Each of these tables has a numeric primary key - ID for Customer, TransactionID for Transaction, and so on. The foreign‑key columns in the detail tables reference the master tables through these ID values, creating a classic one‑to‑many relationship. Crystal can pull data from any of these tables, but the real power comes from joining them together to create meaningful reports.

The link between tables is typically expressed through columns named something like CustomerID, TransactionID, or TenderID. For example, the Transaction table contains a CustomerID column that points back to the Customer table’s ID. When writing SQL for Crystal, use a JOIN clause that references these columns to pull the full name or other customer details alongside the transaction data. The following query illustrates this pattern:

SELECT b.FirstName, b.LastName, a.* FROM [Transaction] a JOIN Customer b ON a.CustomerID = b.ID;

Notice the brackets around the Transaction table name. In Transact‑SQL, Transaction is a reserved word, so the brackets prevent the engine from misinterpreting it as a keyword. If you forget them, you’ll get a syntax error, and the report will fail to compile. This small detail can save you a lot of debugging time later on.

With the tables mapped and connections verified, the next step is to decide how you’ll expose the data to Crystal. If you plan to build complex reports that need multiple joins or aggregate calculations, consider creating a view or a stored procedure that encapsulates the logic. Views make the report designer’s life easier because you can treat the view like a single table, while stored procedures give you the flexibility to pass parameters for date ranges or product categories. In the next section, we’ll cover how to create and use these database objects effectively.

Crafting Queries, Views, and Stored Procedures for Reliable Reports

RMS relies on a worksheet‑based data flow: each retail location sends its transactional data back to headquarters in a set of worksheets, which the central database then imports. This process means that the headquarters database never creates transactions directly; instead, it processes the incoming worksheets and builds the final tables. Because of that, you need to be careful when writing queries that reference the Transaction table. If you accidentally treat the headquarters database as a primary source for new transactions, you’ll end up with duplicates or incomplete data in your reports.

One of the safest ways to build reliable reports is to use the pre‑built views that RMS ships with. These views already handle the worksheet import logic and present a clean, deduplicated view of the data. If you need something more specific, you can create your own view in SQL Server Management Studio. The view should perform all the necessary joins, groupings, and calculations, and it should expose only the columns that Crystal will need. Keep the view lean - add indexes on the underlying tables if you’re going to filter on large columns like TransactionDate or ProductID.

When you need more dynamic filtering, stored procedures come into play. A typical stored procedure for a sales report might accept a start date, an end date, and an optional product category. Inside the procedure, you’d write a SELECT statement that joins Transaction, TransactionAmount, and Tender, filters by the date range, groups by product, and returns a result set. Crystal can call the procedure directly, passing the parameters through the report’s parameter fields. This approach keeps the report logic in the database, where it can leverage the engine’s optimization, and reduces the amount of data Crystal has to transfer over the network.

Because RMS is often part of a larger Microsoft ecosystem, you can also tap into external data sources by using linked servers or OPENROWSET. For instance, if your retailer also runs a Microsoft Access file for a small shop or an Oracle database for a legacy system, you can create a linked server that points to that database. Then, in a view or stored procedure, you can reference the external tables just like local ones. That means you can pull data from multiple systems into a single Crystal report, creating a unified view of the retailer’s entire operation.

To keep the report fast and maintainable, remember a few best practices: always filter early, avoid SELECT *, create indexes on the join keys, and keep the number of tables in a single query to a minimum. Also, schedule the creation or refresh of your views and stored procedures during off‑peak hours to avoid locking critical tables while the retail system is busy processing transactions. Following these guidelines will help you deliver Crystal Reports that run quickly and return accurate data to the business.

Advanced Cross‑System Reporting with Linked Servers and OpenRowSet

In many retail environments, data isn’t confined to a single database. Some stores still use legacy systems like Ctree or PervasiveSQL for point‑of‑sale, while others may rely on Oracle or DB2 for finance. Crystal Reports can handle all of these sources, but you first need to expose them to SQL Server. The easiest way is to set up a linked server in SQL Server Management Studio. During setup, choose the ODBC driver that matches your external database, provide the connection string, and test the link. Once the linked server is up, you can reference its tables using the three‑part naming convention: Server.Database.Schema.Table.

For databases that don’t support native OLEDB drivers, you can still pull data using OPENROWSET. This function allows you to issue a dynamic SQL statement that reads from an external data source on the fly. For example, to pull a list of products from a Navision database that only offers an ODBC interface, you could write:

SELECT FROM OPENROWSET('MSDASQL', 'DSN=NavisionDSN', 'SELECT FROM Products');

OPENROWSET is powerful but also resource‑intensive, so use it sparingly. Prefer linked servers when possible, as they maintain a persistent connection and can take advantage of server‑side execution plans. Once you have linked servers or OPENROWSET queries set up, wrap them in a view that aggregates data across all systems. That view becomes the single source for Crystal Reports, eliminating the need for the report designer to worry about multiple data connections.

When you have a multi‑source view, the Crystal report itself becomes a thin presentation layer. In the report designer, connect to the view, drag the fields you need, and apply formatting. Because the view already resolves joins and aggregates, the report engine doesn’t have to perform heavy computations. That results in faster report generation, even for large datasets. Additionally, by keeping the data logic in the view, you make it easier to maintain the report if any underlying system changes its schema. A single update to the view will propagate through all reports that depend on it.

Performance is critical when dealing with cross‑system data. Monitor query plans for your views and stored procedures to spot any table scans or missing indexes. If a view becomes a bottleneck, consider materializing it with an indexed view or a nightly ETL process that writes the aggregated data to a staging table. Finally, keep an eye on data freshness: because worksheets arrive asynchronously, the latest transactions might still be in transit when you generate a report. Add a “last updated” timestamp in your view to let users know how current the data is, and consider adding a parameter that lets the report run in a “delayed” mode to wait for the next worksheet cycle.

By following these steps - setting up linked servers, crafting efficient views, and delegating heavy lifting to SQL Server - you’ll create Crystal Reports that pull together data from RMS, legacy POS systems, and financial platforms. The result is a single, coherent view of the retailer’s performance that can drive decisions faster and with greater confidence.