RMS 1.2 New field for detailed sales report

I would like to print the tender type on the detail sales report.Could
anybody tell me what table, field and syntax I need for the inner join of
the report?

Thanks
Gerd


0
6/22/2004 1:34:38 AM
pos 14173 articles. 0 followers. Follow

3 Replies
678 Views

Similar Articles

[PageSpeed] 42

I made some progress with the report. I found the tables I need and have
linked those to the report. However, when running the report I get duplicate
records for some transactions when the tender type was cash with some money
back. Reason being that there are 2 records in table tenderentry.

Here it the report definition with all the various joins:

Begin ReportSummary
   ReportType = reporttypeSales
   ReportTitle = "Detailed Sales Report"
   PageOrientation = pageorientationLandscape
   OutLineMode = True
   Groups = 1
   GroupDescription = ""
   DisplayLogo = False
   LogoFileName = "MyLogo.bmp"
   ProcedureCall = ""
   TablesQueried = <BEGIN>

      FROM TransactionEntry WITH(NOLOCK) INNER JOIN [Transaction]
WITH(NOLOCK) ON TransactionEntry.TransactionNumber =
[Transaction].TransactionNumber
      INNER JOIN  Batch WITH(NOLOCK) ON [Transaction].BatchNumber =
Batch.BatchNumber
      LEFT JOIN   Item WITH(NOLOCK) ON TransactionEntry.ItemID = Item.ID
      LEFT JOIN   Department WITH(NOLOCK) ON Item.DepartmentID =
Department.ID
      LEFT JOIN   Category WITH(NOLOCK) ON Item.CategoryID = Category.ID
      LEFT JOIN   Tenderentry WITH(NOLOCK) on
[Transaction].TransactionNumber = TenderEntry.TransactionNumber
      LEFT JOIN   Tender WITH(NOLOCK) On TenderEntry.TenderID = Tender.ID


   <END>
   SelCriteria = ""
   GroupBy = ""
   SortOrder = ""
End ReportSummary

I found the tenderentry trable in the sales commision report however in that
report they filter on tender amounts > 0 which provides a unique record.
However, for the sales report I cannot only use amounts > 0 as I also need
to account for cancellations, returns and void transaction (which have
negative amounts).

There is probably a syntax on the join statement I could use to just read 1
record but I would not know what that could be. I also am looking for some
help files or other documentation I could read up on how to use the various
SQL tables. At the moment I kind of feel like walking in the (absolute)
dark.

"Gerd" <gerd.goebel@nospambavarian-cons.com> wrote in message
news:%23sD9Vk$VEHA.712@TK2MSFTNGP11.phx.gbl...
> I would like to print the tender type on the detail sales report.Could
> anybody tell me what table, field and syntax I need for the inner join of
> the report?
>
> Thanks
> Gerd
>
>


0
6/23/2004 3:25:29 PM
I just looked at this for a minute, so I may be wrong on this...

I think you're going to need to create a view that uses a GROUP BY clause to
SUM() the tender entry amounts into a single record.  Or if you don't really
care about the tendered amount, you could use a SELECT DISTINCT in the view.

Check out the ItemMovementHistory report for an example of creating a view
within an active report

-- 
Glenn Adams
Tiber Creek Consulting
http://www.tibercreek.com
glenn@tibercreek.com
----------------------------------------------
Please DO NOT respond to me directly but post all responses here in the
newsgroup so that all can share the information



"Gerd" <gerd.goebel@nospambavarian-cons.com> wrote in message
news:OALNSZTWEHA.3236@tk2msftngp13.phx.gbl...
> I made some progress with the report. I found the tables I need and have
> linked those to the report. However, when running the report I get
duplicate
> records for some transactions when the tender type was cash with some
money
> back. Reason being that there are 2 records in table tenderentry.
>
> Here it the report definition with all the various joins:
>
> Begin ReportSummary
>    ReportType = reporttypeSales
>    ReportTitle = "Detailed Sales Report"
>    PageOrientation = pageorientationLandscape
>    OutLineMode = True
>    Groups = 1
>    GroupDescription = ""
>    DisplayLogo = False
>    LogoFileName = "MyLogo.bmp"
>    ProcedureCall = ""
>    TablesQueried = <BEGIN>
>
>       FROM TransactionEntry WITH(NOLOCK) INNER JOIN [Transaction]
> WITH(NOLOCK) ON TransactionEntry.TransactionNumber =
> [Transaction].TransactionNumber
>       INNER JOIN  Batch WITH(NOLOCK) ON [Transaction].BatchNumber =
> Batch.BatchNumber
>       LEFT JOIN   Item WITH(NOLOCK) ON TransactionEntry.ItemID = Item.ID
>       LEFT JOIN   Department WITH(NOLOCK) ON Item.DepartmentID =
> Department.ID
>       LEFT JOIN   Category WITH(NOLOCK) ON Item.CategoryID = Category.ID
>       LEFT JOIN   Tenderentry WITH(NOLOCK) on
> [Transaction].TransactionNumber = TenderEntry.TransactionNumber
>       LEFT JOIN   Tender WITH(NOLOCK) On TenderEntry.TenderID = Tender.ID
>
>
>    <END>
>    SelCriteria = ""
>    GroupBy = ""
>    SortOrder = ""
> End ReportSummary
>
> I found the tenderentry trable in the sales commision report however in
that
> report they filter on tender amounts > 0 which provides a unique record.
> However, for the sales report I cannot only use amounts > 0 as I also need
> to account for cancellations, returns and void transaction (which have
> negative amounts).
>
> There is probably a syntax on the join statement I could use to just read
1
> record but I would not know what that could be. I also am looking for some
> help files or other documentation I could read up on how to use the
various
> SQL tables. At the moment I kind of feel like walking in the (absolute)
> dark.
>
> "Gerd" <gerd.goebel@nospambavarian-cons.com> wrote in message
> news:%23sD9Vk$VEHA.712@TK2MSFTNGP11.phx.gbl...
> > I would like to print the tender type on the detail sales report.Could
> > anybody tell me what table, field and syntax I need for the inner join
of
> > the report?
> >
> > Thanks
> > Gerd
> >
> >
>
>


0
glenn1806 (721)
6/23/2004 8:22:07 PM
Thanks for your reply, Glenn.

I don't care about the tender amount as all I need is the tender type for my
detailed sales report with the sales taxes I had downloaded from the MS
web-site.

With the tender type I can use this report to verify the QB interface.
Grouping the report by tender type allows me to see the total amount by
tender type that comes over to QB and grouping by department I see the
totals by product group. And the report also shows the total sales tax. So
this report is ideal for me as I see immediately if the QB interface posted
into the wrong accounts.

I had at the item history report. Well, I guess I need to play around with
the create view to get familiar with the syntax and try to adjust it to my
sales report.

Thanks
Gerd
"Glenn Adams" <glenn@tibercreek.com.nospam> wrote in message
news:Oy1g8%23VWEHA.1128@TK2MSFTNGP10.phx.gbl...
> I just looked at this for a minute, so I may be wrong on this...
>
> I think you're going to need to create a view that uses a GROUP BY clause
to
> SUM() the tender entry amounts into a single record.  Or if you don't
really
> care about the tendered amount, you could use a SELECT DISTINCT in the
view.
>
> Check out the ItemMovementHistory report for an example of creating a view
> within an active report
>
> -- 
> Glenn Adams
> Tiber Creek Consulting
> http://www.tibercreek.com
> glenn@tibercreek.com
> ----------------------------------------------
> Please DO NOT respond to me directly but post all responses here in the
> newsgroup so that all can share the information
>
>
>
> "Gerd" <gerd.goebel@nospambavarian-cons.com> wrote in message
> news:OALNSZTWEHA.3236@tk2msftngp13.phx.gbl...
> > I made some progress with the report. I found the tables I need and have
> > linked those to the report. However, when running the report I get
> duplicate
> > records for some transactions when the tender type was cash with some
> money
> > back. Reason being that there are 2 records in table tenderentry.
> >
> > Here it the report definition with all the various joins:
> >
> > Begin ReportSummary
> >    ReportType = reporttypeSales
> >    ReportTitle = "Detailed Sales Report"
> >    PageOrientation = pageorientationLandscape
> >    OutLineMode = True
> >    Groups = 1
> >    GroupDescription = ""
> >    DisplayLogo = False
> >    LogoFileName = "MyLogo.bmp"
> >    ProcedureCall = ""
> >    TablesQueried = <BEGIN>
> >
> >       FROM TransactionEntry WITH(NOLOCK) INNER JOIN [Transaction]
> > WITH(NOLOCK) ON TransactionEntry.TransactionNumber =
> > [Transaction].TransactionNumber
> >       INNER JOIN  Batch WITH(NOLOCK) ON [Transaction].BatchNumber =
> > Batch.BatchNumber
> >       LEFT JOIN   Item WITH(NOLOCK) ON TransactionEntry.ItemID = Item.ID
> >       LEFT JOIN   Department WITH(NOLOCK) ON Item.DepartmentID =
> > Department.ID
> >       LEFT JOIN   Category WITH(NOLOCK) ON Item.CategoryID = Category.ID
> >       LEFT JOIN   Tenderentry WITH(NOLOCK) on
> > [Transaction].TransactionNumber = TenderEntry.TransactionNumber
> >       LEFT JOIN   Tender WITH(NOLOCK) On TenderEntry.TenderID =
Tender.ID
> >
> >
> >    <END>
> >    SelCriteria = ""
> >    GroupBy = ""
> >    SortOrder = ""
> > End ReportSummary
> >
> > I found the tenderentry trable in the sales commision report however in
> that
> > report they filter on tender amounts > 0 which provides a unique record.
> > However, for the sales report I cannot only use amounts > 0 as I also
need
> > to account for cancellations, returns and void transaction (which have
> > negative amounts).
> >
> > There is probably a syntax on the join statement I could use to just
read
> 1
> > record but I would not know what that could be. I also am looking for
some
> > help files or other documentation I could read up on how to use the
> various
> > SQL tables. At the moment I kind of feel like walking in the (absolute)
> > dark.
> >
> > "Gerd" <gerd.goebel@nospambavarian-cons.com> wrote in message
> > news:%23sD9Vk$VEHA.712@TK2MSFTNGP11.phx.gbl...
> > > I would like to print the tender type on the detail sales report.Could
> > > anybody tell me what table, field and syntax I need for the inner join
> of
> > > the report?
> > >
> > > Thanks
> > > Gerd
> > >
> > >
> >
> >
>
>


0
6/23/2004 8:43:26 PM
Reply:

Similar Artilces:

Trying to update data in RMS with a web interface
My project is much more complicated, but I am hoping someone can give me some generic tips and/or code to assist me. I want to update a field in RMS with some data the cashier inputs on an HTML form. For example, if a cashier selects a customer using F7, I want them to press a custom button that brings up an HTML window. That html file will have a form where the cashier will enter some text in a textbox. When the cashier presses Submit, the text in the textbox will be written to the Customer.CustomText1 field. The two major issues are 1) how to pass the Customer.ID to the HTML window ...

Problems with attachments #2
I have not been able to send attachments from Outlook that originate with my machine (I have win ME on a Dell laptop). Also, some people will get the attachment, but it will be changed from .doc to .dat, etc. However, I can forward attachments sent to me and the recipients will get them. Please help! Thanks. Make sure you use HTML or Plain Text format when sending messages to users of any other e-mail client besides Outlook. Don't use Rich Text format, which is proprietary to Outlook and will cause problems for other recipients, one of which is that they won't get your attac...

CRM 1.2 Reporting Tools Hell
Hi We have installed Crystal Reports 9.2.2 on CRM 1.2. We worked fine for a couple of reports but then we received a memory instruction error. We checked many similar problems posted on Google and opther sites and it seems there is no solution As CRM 3.0 will use SQL Reporting Tools we decided to start using this tool with CRM 1.2. We Installed SQL Reporting Services as an alternative, but when the IIS web directory is activated CRM is giving an error. When we stop the SQL Web site CRM works fine This is frustrating ... Please advise -- Microsoft Navision Developer / Consultant Di...

collection letter #2
I am trying to create collection letters to include all A/R detail. I have managed to include the invoice detail but this does not show any payments, returns or credit memos. Is there a way to include these? We have been using a modified trial balance as a statement to show all activity and have it aged in buckets. This requires an additional cover sheet since there is no easy way to have this report formatted to align in a window envelope. This is a time consuming task. I do not like the statements that are generated using the sales routines since that includes every transaction ...

Word Cells into Excel Cells #2
How does one import a cell formatted Word file *.doc into cells within Excel *.xls. By "cell formatted Word file", do you mean a table? I would simply select table and click copy. Then open excel and paste it where you want it to appear. J "gary" wrote: > How does one import a cell formatted Word file *.doc into > cells within Excel *.xls. > > > jayceejay Wrote: > By "cell formatted Word file", do you mean a table? I would simpl > select > table and click copy. Then open excel and paste it where you want i > to > ap...

Stapling Reports
Hi, Does anybody have any experience on how to get an Access report to staple the pages in a group together? Is there an API in the printer driver? Regards Bj=F8rn A bit printer-specific, no? Most printers don't even do stapling. "Bj�rn" <bjornsuneandersen@gmail.com> wrote in message news:1185866091.590436.146060@w3g2000hsg.googlegroups.com... Hi, Does anybody have any experience on how to get an Access report to staple the pages in a group together? Is there an API in the printer driver? Regards Bj�rn As Baz points out, stapling is a function of the printer....

hotkeys and keyup #2
Hi I would like to thank everyone for suggestions concerning hotkeys and keyup. What I am using is CE .Net though if there is a solution that also works in regular windows that would be good. I am willing to give most anything a try. What I have is a program that sits in the background and waits for a keystroke to run certain functions. One for keydown and one for keyup. The functions talk to an external device on a serial connection, but the main thing is that I need a method to get those keystrokes no matter what application is on top. The impression that I get is that Hotkey will send the...

Unable to open default folders #2
Mail was working fine until I tried to merge documents using to print labels. Now when I try to open out I get the message: Unable to open default e-mail folders.....do not have access to file xxx/xxx/outlook.pst How can I recover this file. It seems to have vanished. If you're using Windows 2000 or XP, the .PST file may be in a hidden or system folder. When searching for all .PST files, make sure you've enabled searching within hidden and system folders. -- Jocelyn Fiorello MVP - Outlook *** Messages sent to my e-mail address will NOT be answered -- please reply only to the new...

mail enabled public folder #2
I have created a public folder and mail enabled, I have also made sure that anonymous has contributor permission. However, I cannot send emails to this public folder from an external domain. Internal is OK, anyone have any ideas What's the text of the NDR? kevin wells wrote: > I have created a public folder and mail enabled, I have > also made sure that anonymous has contributor permission. > > However, I cannot send emails to this public folder from > an external domain. Internal is OK, > > anyone have any ideas "Default" must also have contri...

RMS Turns off
While almost completing a transaction the program closes itself and the desktop secreen is visible. I have two pos machines but it only happens on one. At other times all together it gives a error messages soposuser.exe. Someone please help Alex, having the same problem on 4 terminals running RMS. The problem happens periodically when the user tries to tender a sale. Microsoft support has not been any help on this. Usually an error is generated that can be viewed in the application log. What equipment are you running RMS on? What other peripherals do you have attached? Maybe w...

CRM 1.2 How to get queue to e-mail CRMEnabled users
We are running Windows Server 2003 Standard Edition and a Windows Server 2003 Standard Edition MS Exchange server. Can not get the CRM Queue to e-mail other users. E-mails come fine to support queue, but not to us. What do we have to do to configure the mail server properly, the CRM Router is in place on mail server, but there is something else missing. Any help would be great full thanks!!! alfuller@mobilewires.com ...

Customize report
Hello Everyone. I am using RMS 2.0. I am trying to get card transaction detail report from system but I dont think this program has it. I need user report showing information in 'tenderentry' table + card type( if possible) How can I create the report and add it to system so user can use it? (What kind of program should I use?) Please let me know. THank you!! There is an existing EDC Report that has this info in store mgr under reports and then misc. -- Maurice Gordon Maurice@americanretailsupply.com Sales/Support 1-800-426-5708 XT 1307 "Alex" <blacksky80@gmail.com&...

Barra de Publicidade que =?ISO-8859-1?Q?d=E1?= dinheiritos !!! #2
Se queres ganhar uns Euritos s� tens de instalar esta barra de publicidade, que podes at� minimizar ou fechar em caso de utilizar programas de ecr� completo. N�o incomoda nada, nem consome recursos. Apenas d� dinheiro, �ptima para quando deixas o comptdor a sacar a noite toda... Recebes o dinheiro na tua conta PayPal. http://www.eurobarre.com/index_fr.php?p=373093897695 100% seguro by CrashOverride If you want to earn money just by sitting in front of your PC, just install this Bar that shows some publicity now and then. 100% safe, no malicious ware. http://www.eurobarre.com...

New Docking Framework
I am trying to programatically provide a set of "preset" docking profiles in my application - I can build the default one when I create the windows fine, but afterwards if I want to "reset" them or move them it just goes wrong. So far I have worked out that if AutoHide is on, I must turne it off first (before moving) etc. But I have been unable to undock a window that is docked as a tab in another docked window... Any thoughts / help would be appreciated! Gordon. ...

How to hide and un-hide fields upon condition
I have a text box on a form and next to it I have another text box that I want to hide unless there is data in the first text box. So, if no information is entered in text box 1 then hide text box 2 and vice versa. Is this easy to do? In the form's Current event, put: Me.Textbox2.Visible = Len(Me.Textbox1 & vbNullString) > 0 Put the same code in the AfterUpdate event of Textbox1. If you want the content of Textbox2 to be erased if the content of Textbox1 is, use Private Sub Textbox1_AfterUpdate() If Len(Me.Textbox1 & vbNullString) = 0 Then Me.Textbox2 = Null M...

event id 9360 #2
Hello, I am getting the error in my eventlog Event ID: 9360 OALGen encountered an error while generating the changes.oab file for version 2 and 3 differential downloads of address list '\Global Address List'. The offline address list has not been updated so clients will not be able to download the current set of changes. Check other logged events to find the cause of this error. If the cause of the problem was intentional or cannot be resolved, OALGen can be forced to post a full offline address list by creating the DWORD registry key 'HKEY_LOCAL_MACHINE\SYSTEM\CurrentCo...

RMS SO POS Bug ?
Hi, I use the standard EAN13 weight-code (that like with 20...). When Item with this code have a sale price, the POS do not show the real quantity (price of scale/standard price)*sale price = new quantity for pos. Do You have a solution? Thank you Antonio ...

Office 10.1.4 Update
I've downloaded 10.1.4 update several times in both formats, but I keep getting "error prevented update from completing." No apps are open and I already updated to 10.1.3. I have a G4 Dual 1.4 GHz, 2GB mem, OS 10.2.6. Can someone please help out here. From what I understand, this update will help integrate Entourage with Microsoft Exchange Server? Currently we are having to use Outlook for Exchange, but that only runs in Classic OS 9, which is causing many problems. I'm trying to convert to Entourage. Any help would be greatly appreciated. If any links are being supplied ...

xslt 2.0 support in .net 3.5
I just started working with .net 3.5 using Visual C# 2008 Express Edition. I tried to run XslCompiledTransform.Transform with a style sheet that uses the xsl:sequence instruction, like so: <xsl:sequence select="$Vvar21_price_double"/> The Transform method throws an exception with the message 'Select is not a recognized extension element' Am I correct in assuming that XslCompiledTransform does not support xslt 2.0 in .net 3.5? <rakel@fc.hp.com> wrote in message news:4cdfa3f0-0368-4f03-8c53-4b498d1b04b3@l39g2000yqn.googlegroups.com... > I just started workin...

Stopping chart line where 1 of 2 columns of data in calculation is blank
I have a chart where there are two columns of data used to create a number in a third column. I have tried to use both #N/A, NA() and "" to stop the resulting chart line when one of the 2 columns is blank but have had no success. Any suggestions would be greatly appreciated, Roger is there not a provision in tools-opiton-chart plot empty cells - not plotted Roger B. <rb10@canada.com> wrote in message news:uVTrncciFHA.320@TK2MSFTNGP09.phx.gbl... > I have a chart where there are two columns of data used to create a number > in a third column. I have tried to use...

Any Liquor store RMS Users
Are there any Liquor store using RMS ? Is anyone willing to share some of their best practices, some ways they do things. Is there anything as "best practices' booklet for Liquor store using RMS, Few questions i have How is your inventory kept? Is everything by case based on supplier item numbers and than having a parent/child relationship to single item (bottle, 6 pack , etc) Or is it better to keep inventory by bottle with fast moving items having parent/child relationship How do you cover the need for extra fields needed Size Price per ounce Type, Proof Verital(types ...

How? #2
I want to format a cell to show a Upper case X just by clicking on it. Hi this would require VBA (using the Selectionchange event for example). See: http://www.cpearson.com/excel/events.htm -- Regards Frank Kabel Frankfurt, Germany "Lee and Rebecca" <LeeandRebecca@discussions.microsoft.com> schrieb im Newsbeitrag news:ED511CBB-3AF1-4AF9-B5EB-0353B6204E46@microsoft.com... > I want to format a cell to show a Upper case X just by clicking on it. Can you help me with this? I don't understand what to do from that web site. "Frank Kabel" wrote: > Hi > th...

Side-by-side installation of Visio 2003 and VEA that came with VS 1.0 Enterprise
Please let me know if installation of both Visio 2003 and VEA that came with VS 1.0 Enterprise on the same machine is officially supported. I can't speak for M$, but they respond that it does work (although a pain in the butt to reboot when you need to move from one to the other, also since the VEA version is really v2002 understand the VEA version may not read the v2003 files). Al "TomTom" <no_spam@nospamfordiscussion.com> wrote in message news:OZTSpD2ZEHA.3708@TK2MSFTNGP10.phx.gbl... > Please let me know if installation of both Visio 2003 and VEA that came >...

I can't get any reports
Hi, I'm using Money 2002. I can't seem to get any reports e.g. How much I spent on gas last month. I select the account, define the time frame, select the expense category but I get a completely blank report. Any idea what may be causing this? Thanks, Sam In microsoft.public.money, Sam wrote: >Hi, > >I'm using Money 2002. I can't seem to get any reports e.g. How much I spent >on gas last month. I select the account, define the time frame, select the >expense category but I get a completely blank report. > >Any idea what may be causing this? > I ...

RMS 2.0 and PC Charge
Is anyone using PC Charge Pro with RMS 2.0? I haven't been able to get any answers from Verifone as to certification with RMS 2.0. Does PC Charge Pro support the AVS function in RMS or does it do it itself? I'm changing Merchants and the new one requires using PC Charge Pro. Thanks, TomT Funny thing... I have basically the same question. Our MS VAR told me to check with PCCharge (Verifone) . VeriFone told me "They (MS RMS) test their versions with our software and keep a list of which is compatible. If you are using the newer version of RMS then I would say 5.7.1I SP...