RMS Report Files - What Language?

Are the text files in the Report directory under Store Operations written in SQL or some other query language?
Also, what is the best editor to use to create or edit these files?
Thanks,
Randy
Add pictures here
<% if( /^image/.test(type) ){ %>
<% } %>
<%-name%>
Add image file
Upload
As far as I know, it's a proprietary language, but it might be third party. The reports are called "Active Reports", so you could try googling that.
The files are just text - you can edit them in Notepad or any other text editor. I like one called UltraEdit, but it's just a matter of taste - you aren't going to get any text coloring or anything like that.
The language is based on SQL. If you know SQL, it is very easy to figure out the basics of the report language. Many examples have been posted here (try searching Google Groups - they keep history much longer than the MS news server). Also, there is a Reports Library on Customer Source that has quite a few additional reports - these can be used as is or as additional examples.
Glenn Adams Tiber Creek Consulting http://www.tibercreek.com snipped-for-privacy@tibercreek.com ---------------------------------------------- Please DO NOT respond to me directly but post all responses here in the newsgroup so that all can share the information.
Randy wrote:

Add pictures here
<% if( /^image/.test(type) ){ %>
<% } %>
<%-name%>
Add image file
Upload
Thanks for the info. That helps - I do know sql from a former life, will have to brush up a bit - it's been about 10 years since I used it.
I tried to log on CustomerSource but I'm going to need to subscribe to a maintainence plan. Any idea what it costs?
"Glenn Adams [MVP - Retail Mgmt]" wrote:

Add pictures here
<% if( /^image/.test(type) ){ %>
<% } %>
<%-name%>
Add image file
Upload
Depends on how long ago you purchased. At the time of purchase it's 18% of the MSRP of the software. After purchase or if your original agreement expires, it steps up to something like 27% based on how long you wait to renew. Your reseller should be able to give you an exact quote.
Glenn Adams Tiber Creek Consulting http://www.tibercreek.com snipped-for-privacy@tibercreek.com ---------------------------------------------- Please DO NOT respond to me directly but post all responses here in the newsgroup so that all can share the information.
Randy wrote:

Add pictures here
<% if( /^image/.test(type) ){ %>
<% } %>
<%-name%>
Add image file
Upload
Randy,
If they want to charge you more than 18%, post again on the newsgroup, I have a cheaper, legal solution.
--
=

Get Secure! - www.microsoft.com/security

You must be using Outlook Express or some other type of newsgroup reader
  Click to see the full signature.
Add pictures here
<% if( /^image/.test(type) ){ %>
<% } %>
<%-name%>
Add image file
Upload
I'd be very interrested in your "Cheaper, Legal" solution.
What do you have?
"Jeff" wrote:

Add pictures here
<% if( /^image/.test(type) ){ %>
<% } %>
<%-name%>
Add image file
Upload
Randy,
MS is going to really push 2.0, so they will be/are offering a "come back for 2.0" without penalties. Check with your reseller in about 2 weeks. You will be able to re-up your Maintenance for 18% of the MSRP at time of purchase, instead of the 27, 32 or 37% lapsed charges.
--
Jeff Check Point Software
=====================================================
You must be using Outlook Express or some other type of newsgroup reader to see and download the file attachment(s). If you are not using a reader, follow the link below to setup Outlook Express. Click on "Open with newsreader" under the MS Retail Management System on the right.
http://tinyurl.com/75bgz =====================================================
&gt;&gt;&gt; ----------------------------------------------<BR>&gt; &gt;&gt;&gt; Please DO NOT respond to me directly but post all responses here in the<BR>&gt; &gt;&gt;&gt; newsgroup so that all can share the information.<BR>&gt; &gt;&gt;&gt;<BR>&gt; &gt;&gt;&gt;<BR>&gt; &gt;&gt;&gt; Randy wrote:<BR>&gt; &gt;&gt;&gt;&gt; Are the text files in the Report directory under Store Operations <BR>&gt; &gt;&gt;&gt;&gt; written in SQL or some other query language?<BR>&gt; &gt;&gt;&gt;&gt; Also, what is the best editor to use to create or edit these files?<BR>&gt; &gt;&gt;&gt;&gt;<BR>&gt; &gt;&gt;&gt;&gt; Thanks,<BR>&gt; &gt;&gt;&gt;&gt;<BR>&gt; &gt;&gt;&gt;&gt; Randy <BR>&gt; <BR>&gt; <BR>&gt;</BLOCKQUOTE></BODY></HTML>
------=
Add pictures here
<% if( /^image/.test(type) ){ %>
<% } %>
<%-name%>
Add image file
Upload
Has anyone ever posted a comprehensive syntex/programming guide for this "proprietry language"? I don't have the time or desire to scour through every .QRP in StoreOps/Reports or Headquarters/Reports to try to find every little nuance.
I wonder if this sort of guide is only available to "certified" RMS resellers. I am not a reseller, have no desire to be one, but I do need to write a bucketload of cusotmized reports for our store managers and executives. If getting certified will give me access to the SDK and to the report builder documentation, then so be it. I just wish someone would come out and say it.
--
Bill Yater
The Worth Collection
  Click to see the full signature.
Add pictures here
<% if( /^image/.test(type) ){ %>
<% } %>
<%-name%>
Add image file
Upload
The customization guide is a partner only resource (defined by MS). I don't necessarily agree with that decision, but that's how it is. I'm pretty sure I've come out and said that a few times over the years.
If you know SQL, the report language is obvious - I think i found one or two things in the customization guide that I hadn't figured out on my own by looking at the detailed sales report for an hour or so.
Try searching the newsgroup through Google Groups - they have years worth of history instead of months. There have been many postings over the years that provide bits and pieces of the reports language.
I think the bigger problem is the lack of a true data dictionary. I believe there is a document on Customer Source (it may also be partner only, I'm not sure), but I don't think it really provides enough info to do much of anything. You just have to dig in and figure the DB pout on your own - or pay a partner to do it for you...
Glenn Adams Tiber Creek Consulting http://www.tibercreek.com snipped-for-privacy@tibercreek.com ---------------------------------------------- Please DO NOT respond to me directly but post all responses here in the newsgroup so that all can share the information.
Bill Yater wrote:

Add pictures here
<% if( /^image/.test(type) ){ %>
<% } %>
<%-name%>
Add image file
Upload
<Rant mode on> This kind of "control mentality" by Microsoft is totally counterproductive. MS is trying to protect it's partners, yes, but there are many out there like me who will NEVER, NEVER hire a partner to do something I can do myself.
So... we are forced to scour the internet for obscure references about the syntax and dictionary for a language we should have a complete reference available for. <Rant mode off>
I've found some piecemeal documents that were removed but still had google html pages stored... Try this:
RMS Report Template Language Definition version 1.05c
ReportSummary Properties ReportType reporttypeCustomer reporttypeSales reporttypeItems reporttypeOrders reporttypeLayaway reporttypeMisc reporttypeCustom reportypeMemorized ReportTitle String, Title of report PageOrientation pageorientationPortrait pageorientationLandscape Sets the default size of the report window in Manager, use Landscape for reports with lots of columns. OutlineMode True False Whether or not to use the "tree" outline structure when grouping. Groups Number,Specifies the number of levels to automatically group the report data. GroupDescription string, Title for summary rows in outline-mode DisplayLogo True False LogoFileName String ,Name of logo filename in the RMS picture folder. ProcedureCall String, Name of SQL StoredProcedure to run. "" if none. TablesQueried string. A valid SQL FROM clause, eg:
"FROM Item LEFT JOIN Department ON Item.DepartmentID = Department.ID" SelCriteria string. An expression suitable for inclusion in an SQL WHERE clause, eg:
"(Item.SalePrice > 0.0)"
*Note: To ensure that the selection criteria is properly evaluated when filters are used, surround it with parentheses. GroupBy string. Column name to group results by, per SQL GROUP BY clause. eg:
"Cashier.Number" SortOrder String,Column name to sort by, per SQL ORDER BY clause. May end with "ASC" or "DESC" to specify direction of sort order. eg:
"Cashier.Name DESC" Notes Define the basic source, grouping and sorting information for the report.
example:
Begin ReportSummary
ReportType = reporttypeCustomer ReportTitle = "Customer Account Activities" PageOrientation = pageorientationPortrait OutLineMode = True Groups = 1 GroupDescription = "Balance" DisplayLogo = True LogoFileName = "MyLogo.bmp" ProcedureCall = "" TablesQueried = "FROM AccountReceivable WITH(NOLOCK) LEFT JOIN Customer WITH(NOLOCK) ON AccountReceivable.CustomerID = Customer.ID" SelCriteria = "AccountReceivable.Date < DATEADD(day,1,'<Report Date>')" GroupBy = "" SortOrder = ""
End ReportSummary
*the value for TablesQueried should remain on a single line in the text file. Variables <MonthStart> date of the first day of the month <WeekStart> date of the first day of this week <LastWeekStart> date of the first day of Last week <YearStart> date of the first day of the year <Now> current date & time <Report Date> date of report <Today> today's date <Report Title> the title of the report <Store Name> <Store Address1> <Store Address2> <Store City> <Store State> <Store Zip> <Store Country> <Store Phone> <Store Fax>
TitleRow Properties Text string, Text to appear in title row. Font string, Name of font to use. FontBold True False FontSize number, Size of font. Color string, color of font. Notes Used to define the title rows for the report.
example:
Begin TitleRow
Text = "<Store Name>" Font = "Arial" FontBold = True FontSize = 16 Color = "Blue"
End TitleRow
Filter Properties FieldName String, Name of column to filter. FilterOp reportfilteropBetween reportfilteropEqual reportfilteropNotEqual reportfilteropGreater reportfilteropLess reportfilteropGreaterEqual reportfilteropLesserEqual reportfilteropLike Operation to filter by FilterLoLim string, minimum value. See Notes. FilterHiLim string, maximum value. See Notes. Notes Used to define any extra or default filtering to apply to the basic query.
example:
Begin Filter
FieldName = "[Transaction].Time" FilterOp = reportfilteropBetween FilterLoLim = "<YearStart>" FilterHilim = "<Today>"
End Filter Values for FilterLoLim & FilterHiLim:
"<YearStart>" "<Today>" etc..
Column Properties FieldName String, Name of column. DrillDownFieldName string, DrillDown Key, or "" for no DrillDown. See notes. DrillDownReportName string, Name of report to launch on DrillDown. See Notes. Title string, Title of column in report.
The following special values may also be used (where “#” is 1,2,3 or 4):
!CaptionCustomerText# !CaptionCustomerDate# !CaptionCustomerNumber# These titles, are replaced with the corresponding custom field titles specified on the Configuration form in RMS Manager. VBDataType vbString vbInteger vbSingle vbLong vbDouble vbCurrency vbBoolean vbDate Data type of column (controls default format of data) Formula string, SQL formula if data is to be calculated, "" if no calculation is to be used. ColHidden True False ColNotDisplayable True False FilterDisabled True False ColWidth number, width of column in TWIPS (1440 twips = 1 inch) ColAlignment flexAlignLeftTop flexAlignLeftCenter flexAlignLeftBottom flexAlignCenterTop flexAlignCenterCenter flexAlignCenterBottom flexAlignRightTop flexAlignRightCenter flexAlignRightBottom flexAlignGeneral GroupMethod groupmethodNone groupmethodSum groupmethodPercent groupmethodCount groupmethodAverage groupmethodMax groupmethodMin ColFormat string, standard VB format string. Notes Used to define any extra or default filtering to apply to the basic query.
example:
Begin Column
FieldName = "CashierName" DrillDownFieldName = "Cashier.Name" DrillDownReportName = "" Title = "Name" VBDataType = vbString Formula = "MAX(Cashier.Name)" ColHidden = False ColNotDisplayable = False FilterDisabled = False ColWidth = 1875 GroupMethod = groupmethodNone ColFormat = ""
End Column Valid DrillDown Keys RMS will use the supplied DrillDown Key in conjunction with the data in the column to try to automatically open the appropriate editor for that key.
For example, if the key Item.ItemLookupCode is used, and the user double-clicks on that column, RMS will try to open the properties screen for an Item with the matching ItemLookupCode
AccountReceivable.ID Item.ItemLookupCode Batch.BatchNumber Cashier.Name Category.Name Customer.AccountNumber Department.Name Item.ItemLookupCode ItemMessage.Title ItemTax.Description OldJournal.BatchNumber Schedule.Description PurchaseOrder.PONumber ReasonCode.Code SalesRep.Name Supplier.SupplierName Tax.Description Tender.Description [Transaction].TransactionNumber Journal.ID
Comments:
Any line beginning with "//" is a comment and ignored by RMS
"Glenn Adams [MVP - Retail Mgmt]" wrote:

Add pictures here
<% if( /^image/.test(type) ){ %>
<% } %>
<%-name%>
Add image file
Upload
You can use Notepad to edit and create custom reports and templates. A knowledge of SQL is required to create new queries but you can also cut and paste content from existing reports to create new ones. Over 100 reports/XML templates and product signage templates are included free in our RMS Toolkit trial. The Custom Reports/Templates Bundle and SmartPOP signage add-ins are yours to keep just for trying our software. The 30 day trial download for RMS Toolkit is available at the link below. To purchase any of our add-ins, contact your RMS reseller.
Greg Digital Retail Solutions www.digitalretailer.com/rmsaddins

Add pictures here
<% if( /^image/.test(type) ){ %>
<% } %>
<%-name%>
Add image file
Upload
There is a report generator called Active Report from datadynamics but this is not what MS uses. It is a proprietory language on top ComponentOne GRID.
Afshin

Add pictures here
<% if( /^image/.test(type) ){ %>
<% } %>
<%-name%>
Add image file
Upload

BeanSmart.com is a site by and for consumers of financial services and advice. We are not affiliated with any of the banks, financial services or software manufacturers discussed here. All logos and trade names are the property of their respective owners.

Tax and financial advice you come across on this site is freely given by your peers and professionals on their own time and out of the kindness of their hearts. We can guarantee neither accuracy of such advice nor its applicability for your situation. Simply put, you are fully responsible for the results of using information from this site in real life situations.