Working with MS RMS Active Reports

Introduction: 
Microsoft retail management system provides a wide variety of reports with Store operations manager and headquarters. The report shows details of items, sales transactions, suppliers etc. RMS provides its own specialized reports called Active Reports and also Crystal Reports.

Customizing RMS Reports:
 Users can customize the existing reports for additional functionality like adding new columns or hiding any columns or can create a new report altogether. Best way to create customized report is to create a custom active report which will show data in tabular layout. This is a limitation of active report. For any other report layout such as form, we can use crystal reports. By default the active reports are saved in the RMS installation directory at [C:\Program Files\Microsoft Retail Management System\Store Operations\Reports].

Active Reports:
Active reports are nothing but Report template language definition files saved as .qrp files. Users can open these reports in any text editors and customize according to their needs. To create a new custom report we need to just append “Custom-“to the name of the qrp file. And the report will appear in the Reports menu, custom sub menu in Store operations manager.

In the store operations manager we can sort, filter, group or print reports in the Report preview window through different toolbar buttons.

Report Template Language Definition File:
The report file is divided into 4 sections.

• Report Summary

• Title Row

• Filter

• Column

Report summary:
This section includes the basic data source (query), grouping, sorting information. It includes properties like Report title, Tables queried, Report type, SelCriteria etc. Let’s take a look at some of the most important properties. 

• PreQuery1: It represents a valid SQL statement that should be executed before the main report query. We can create views or execute SQL stored procedures using this property. 

• TablesQueried: It represents the FROM clause of SQL query used to display results in the report. 

• SelCriteria: This is SQL WHERE clause.

 All these information are written inside [Begin ReportSummary] and [Begin ReportSummary] tag.

 //--- Report Summary --- //

Begin ReportSummary

ReportType = reporttypeSales

ReportTitle = "My Report"

PageOrientation = pageorientationPortrait

OutLineMode = True

Groups = 1

GroupDescription = ""

DisplayLogo = True

LogoFileName = "MyLogo.bmp"

ProcedureCall = ""

TablesQueried = "FROM MyTable"

SelCriteria = ""

GroupBy = ""

SortOrder = ""

End ReportSummary

There are certain report template variables that are valid in report summary section, such as <MonthStart> [Date of the first day of the month.], <Now> [Current date and time], <Store Name> [Name of the store.] etc.

Title Row: We can have 10 different title rows for a report. They can be Store name, report generation date etc. Two lines are always skipped before the column headers. We can also define the font properties of the title row text. This section starts with [Begin TitleRow] and [End TitleRow].

//--- Title Rows ---//

Begin TitleRow

Text = "<Store Name>"

Font = "Arial"

FontBold = True

FontSize = 16

Color = "Blue"

End TitleRow

Filter: This section starts with [Begin Filter] and ends with [End Filter] tag. We can have up to 20 default or extra filters defined and those can be added to the main query of the report. It has different properties like FilterOP, FilterLoLim, and FilterHiLim etc. Let’s say we can have the default filter as Transaction Date on today.

//--- Filters ---//

Begin Filter

FieldName = "PurchaseOrder.DateCreated"

FilterOp = reportfilteropBetween

FilterLoLim = "<Today>"

FilterHilim = "<Today>"

End Filter

Column: This section represents the columns to be displayed in the report as selected from the database table. We can have up to 40 columns. We need to define the field properties like FieldName, Title, VBDatatype, GroupMethod etc inside [Begin Column] and [End Column] tags. For calculated fields we can use the formula properties.

//--- Columns ---//

Begin Column

FieldName = "MyFieldName"

DrillDownFieldName = ""

DrillDownReportName = ""

Title = "My Field Header"

VBDataType = vbString

Formula = ""

ColHidden = False

ColNotDisplayable = False

FilterDisabled = False

ColWidth = 2205

GroupMethod = groupmethodNone

ColFormat = ""

ColAlignment = flexAlignLeftCenter

End Column

Drilldown keys for report templates: There are certain predefined fields which when used as drilldownfieldname in columns section will open up the details screen on double clicking the column. Valid DrillDown keys are like Item.ItemLookupCode, Cashier.Name, Category.Name, SalesRep.Name etc.

Report Template Enumerations: There are 3 different report template enumerated fields. They are reporttypeEnum, groupmethodEnum, pageorientationEnum. These values are used in columns or report summary sections.

Conclusion:
Customization of active reports can be done by following few easy steps. We just need to create the .qrp file, append the name with custom- and place it in RMS default report folder. The active report’s different sections are self explanatory so can be easily customized as per end users needs.