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
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
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 C> Are the text files in the Report directory under Store Operations written in
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 > As far as I know, it's a proprietary language, but it might be third
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 C> Thanks for the info. That helps - I do know sql from a former life, will have
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
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.
Randy,
If they want to charge you more than 18%, post again on the newsgroup, I have a cheaper, legal solution.
I'd be very interrested in your "Cheaper, Legal" solution.
What do you have?
"Jeff" wrote:
I'd be very interrested in your "Cheaper, Legal" solution.
What do you have?
"Jeff" wrote:
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.
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 C> Has anyone ever posted a comprehensive syntex/programming guide for this
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.
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,'')" GroupBy = "" SortOrder = ""
End ReportSummary
*the value for TablesQueried should remain on a single line in the text file. Variablesdate of the first day of the month
date of the first day of this week
date of the first day of Last week
date of the first day of the year
current date & time
date of report
today's date
the title of the report
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 = "" 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 = "" FilterHilim = ""
End Filter Values for FilterLoLim & FilterHiLim:
"" "" 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:
BeanSmart website is not affiliated with any of the manufacturers or service providers discussed here. All logos and trade names are the property of their respective owners.