Sale batch report with the total quantity sold per item

How i can setup a sale batch report with the total quantity sold per item in 
the given batch # range for each item
Example:

Batch :45

Item :464343     Qty 12
Item :678445    Qty 67
etc.


0
3/13/2009 2:26:02 PM
pos 14173 articles. 0 followers. Follow

1 Replies
602 Views

Similar Articles

[PageSpeed] 46

here is a query that will give that result for the current day:

select t.storeid, batchnumber, itemlookupcode, sum(e.quantity) qty 
from [transaction] t 
join transactionentry e on t.storeid=e.storeid 
and e.transactionnumber=t.transactionnumber 
join item on itemid=item.id where time>convert(char,getdate(),101) 
group by t.storeid, batchnumber, itemlookupcode 
order by t.storeid, batchnumber, itemlookupcode 

Here is the same in an HQ report:

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

Begin ReportSummary
   ReportType = reporttypeItems
   ReportTitle = "Batch ILC Qty"
   PageOrientation = pageorientationPortrait
   WordWrap = False
   ShowDateTimePicker = False
   OutLineMode = True
   Groups = 0
   GroupDescription = "Total"
   DisplayLogo = True
   LogoFileName = "MyLogo.bmp"
   ProcedureCall = ""
   PreQuery1 = ""
   PreQuery2 = ""
   TablesQueried = <BEGIN>
   
from [transaction] t 
join transactionentry e on t.storeid=e.storeid 
and e.transactionnumber=t.transactionnumber 
join item on itemid=item.id 

<END>
   SelCriteria = ""
   GroupBy = "t.storeid, batchnumber, itemlookupcode"
   SortOrder = ""
End ReportSummary


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

Begin TitleRow
   Text = "<Store Name>"
   Font = "Arial"
   FontBold = True
   FontSize = 16
   Color = "Blue"
End TitleRow

Begin TitleRow
   Text = "<Report Title>"
   Font = "Arial"
   FontBold = True
   FontSize = 12
   Color = "Black"
End TitleRow

Begin TitleRow
   Text = "As Of: <Report Date>"
   Font = "Arial"
   FontBold = True
   FontSize = 10
   Color = "Black"
End TitleRow


//--- Filters ---//


Begin Filter
   FieldName = "t.time"
   FilterOp = reportfilteropBetween
   FilterLoLim = <Today>
   FilterHilim = <Today>
   FilterNegated = False
   FilterConnector = reportfilterbooleanconAND
End Filter

//--- Columns ---//

Begin Column
   FieldName = "t.StoreID"
   DrillDownFieldName = ""
   DrillDownReportName = ""
   StoreIDFieldName = ""
   Title = "StoreID"
   VBDataType = vbInteger
   Formula = ""
   ColHidden = False
   ColNotDisplayable = False
   FilterDisabled = False
   ColWidth = 1395
   GroupMethod = groupmethodNone
   ColFormat = ""
End Column

Begin Column
   FieldName = "t.batchnumber"
   DrillDownFieldName = ""
   DrillDownReportName = ""
   StoreIDFieldName = ""
   Title = "Batch"
   VBDataType = vbString
   Formula = ""
   ColHidden = False
   ColNotDisplayable = False
   FilterDisabled = False
   ColWidth = 1605
   GroupMethod = groupmethodNone
   ColFormat = ""
End Column

Begin Column
   FieldName = "Item.ItemLookUpCode"
   DrillDownFieldName = "Item.ItemLookUpCode"
   DrillDownReportName = ""
   StoreIDFieldName = ""
   Title = "LookUp Code"
   VBDataType = vbString
   Formula = ""
   ColHidden = False
   ColNotDisplayable = False
   FilterDisabled = False
   ColWidth = 1245
   GroupMethod = groupmethodCount
   ColFormat = ""
End Column

Begin Column
   FieldName = "qty"
   DrillDownFieldName = ""
   DrillDownReportName = ""
   StoreIDFieldName = ""
   Title = "Quantity"
   VBDataType = vbInteger
   Formula = "sum(e.quantity)"
   ColHidden = False
   ColNotDisplayable = False
   FilterDisabled = False
   ColWidth = 870
   GroupMethod = groupmethodSum
   ColFormat = ""
End Column

Begin Column
   FieldName = "t.time"
   DrillDownFieldName = ""
   DrillDownReportName = ""
   Title = "Date"
   VBDataType = vbDate
   Formula = ""
   ColHidden = True
   ColNotDisplayable = True
   FilterDisabled = False
   ColWidth = 1250
   GroupMethod = groupmethodnone
   ColFormat = ""
End Column

"Ed" wrote:

> How i can setup a sale batch report with the total quantity sold per item in 
> the given batch # range for each item
> Example:
> 
> Batch :45
> 
> Item :464343     Qty 12
> Item :678445    Qty 67
> etc.
> 
> 
> 
0
MattHurst (182)
3/15/2009 1:15:01 AM
Reply:

Similar Artilces:

Recommendations Reporting Services 2008
Can anyone give me their recommendations on what manual to get to learn SSRS 2008? - I am between a beginner and intermediate in writing SQL queries. Your response is appreciated. Thanks -- Message posted via SQLMonster.com http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-programming/201004/1 On Tue, 27 Apr 2010 18:59:41 GMT, "Chamark via SQLMonster.com" <u21870@uwe> wrote: >Can anyone give me their recommendations on what manual to get to learn SSRS >2008? - I am between a beginner and intermediate in writing SQL queries. >Your response is appr...

extra pages on reports
hello all. I have the same issue with 2 reports that I have created. They both have sub-reports attached to them. The problem is that each report has a blank page in addition to the report. How can I delete the blank page from the report format? Any help would be appreciated. ...

Lopsided report
I am trying to create a report which has Cash Contributors on one side of the page with In-Kind Contributors on the other side of the page !! (Side by side each other) I have all the information i need in the same query. If I create a single report, I get the lopsided effect. One group of contributors is higher than the other, leaving blank spaces above one side. I tried creating a report with a sub report. I used the MonthYear as the link, as I could find no other common ground. Doing that, I have encounter two errors, the filter (I used a form filter with the MonthYear as the filter...

SOP-POP and Serialized Items
I have been using Great Plains for a few years, but have not spent much time in the Purchasing module. I have an Item master that is marked for Serial tracking. This Item Master that is marked for serial tracking is purchased from VENDORA. I have a customer that wants to purchase this Serialized Item. Lets call the Item SOFTWAREABC. So I enter the order in sales Transaction Entry for CUSTOMERA and enter Item SOFTWAREABC. I want to use the AutoPO button in Sales Transaction Entry to purchase SOFTWAREABC from VENDORA and it looks like I can not do this unless I mark the item in the sales...

Form Information to Report
Hi, I'm hoping someone can help me with this. First of all, i created a form with multiply text boxes with complex calculations in each text box. When you move from one record to another record, each text box will change due to the calculation. Now what i need to do is to create a separate report for each record. I started creating a report by copying and pasting the text boxes from the form to report in design mode. If at anytime the calculation changes, i don't want to change the calculation on the form and also on the report. (to much of chance of forgetting to change bo...

Fiscal Year settings and reporting
This is a multi-part message in MIME format. ------=_NextPart_000_0006_01C59F4A.30DC56F0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Hi: I know that Fiscal year settings are once and for all. No changes can = take place in it. Is it possible to set the period to monthly and then = roll up by quarter? Can we generate quarterly reports if we select = month? =20 Thanks any help is appreciated.... --=20 Mansoor Awan=20 ------=_NextPart_000_0006_01C59F4A.30DC56F0 Content-Type: text/html; charset="iso-8859-1" Content-Transfe...

Deleting a Batch
Does anyone know how to delete a batch... We just upgraded from QS2000, and we have imported our history... while learning the product we created several transactions and ran a z report... However all of the elements of this batch are bogus and now it would appear as though we can not purge batches like we couldv'e done in QS2000... My Accounting staff is now going crazy because the taxes are now way off the charts... any help would be extreamly appreiciated... Mitchell Mitchell, First backup your files, then backup up again to somewhere else to be safe. If you are on the M...

Missing menu item--how do I get it back?
I am trying to get the news reader feature to work within my Outlook. I have added the News item to the Go menu but it does not show up there after it started once. Noe the news item no longer show when I go to Customize|commands|Go Any help would be great on how to get it back. I have already tried a reinstall and repair but those did not help. To get the News command back, go back to the Programs tab in Control Panel | Internet Options and change the default newsreader to Outlook Express. If OE prompts you to make it your default newsreader the next time you launch it, clear the &quo...

Edit items in "Revieve Inventory"
In the Item receipt, receive inventory, is there a way to edit items as we put them in? This would be a great place to be able to edit basic price, cost & description info while we have Vendor invoices right there. Am I missing the ability to this? -- Thanks! David D. L.S.B. Double-click on the ILC and it will open up the item properties where you can change anything you'd like. Craig "David D." <DavidD@discussions.microsoft.com> wrote in message news:AFD0EF05-CA98-4CB1-824C-AD84E50375DE@microsoft.com... > In the Item receipt, receive inventory,...

Batch in "updating" mode
We posted a batch in A/P, but it is in a perpetual "updating" status. We just want to delete it as we have already rekeyed and posted the payables for that week. We cannot get into the batch. It says it cannot be edited. I was told to rename the SY00800.dat and SY00800.idx files, but that did not work. We use Dynamics cTree v 6.0. Any advice would be appreciated. You should have access to the CustomerSource knowledge base on microsoft.com. Find this article: Batch held in Posting, Receiving, Busy, Marked, Locked, or Edited status in Great Plains Standard (866303) To ac...

Are there any known issues using Line Item comments for all SOP do
Hi all, I recently modified a SOP Packing slip to add Line item comments for each line to show an additional numeric value as a text (weight) printed on each line item. The way it is setup is that there will be unique line item comments (click the -> on the line item comment field, not the magnifying glass lookup and then just type text for each comment, click OK and save the line item). I've used up all of the User-defined fields including tracking #s and so I am down to adding comments. EXT. and Modifier are not options at this point. I also tried adding Document Notes to t...

Page Break in Access Report
How do I get rid of the last page break in a report? The last page of my report is always followed by a blank page. Hi CC, How many pages is your report? If it is normally only one page, then the last page would be page 2. One possible cause of this symptom is having a report that is too wide. For example, if you are printing Portrait, on 8 1/2" X 11" paper, the following applies: 8.5 <= Left Margin + Report Width + Right Margin If your report is more than one page of data, and the sum of the above three settings is greater than 8.5 inches, then the classic sympto...

Conditional Printing of Certain Fields in A Report
i need to have a conditional printing of some fields (SEE BELOW) VENDOR SHARE $999.99 LESS TAX $999.99 country ENGLAND tax (%) 20% AMT DUE $999.99 how do I print only these 5 lines when the tax amount is not zero. Also how do i combine line 2,3 & 4 and looks like LESS ENGLAND (20%) TAX instead. I tried doing the following: ="LESS" & [COUNTRY] &"(" &[TAX (%)] &")" in the control source but i got a #ERROR error Any help would be appreciated. Kevin T <Kevin T@discussions.microsoft.com> wrote: >i need to...

Print multiple pages per Sheet in Excel 2003
I have a spreadsheet with 12 rows that goes for many columns ( with formulas). Is there a way to print this where three pages are on each piece of paper? How about inserting a new worksheet, then copying and pasting (as values) to that new sheet. Then print that "helper" worksheet. If you have to do this lots, this may be worth the trouble. Insert a new sheet. select a nice range on the original sheet Edit|copy go to the new worksheet shift-edit|Paste picture link Repeat so that you have pictures of the ranges so it prints the way you want. Then print this helper worksheet. Th...

Table fields not appearing in report writer
I have created an add-in app for GP. With the app are reports. When I go into Great Plains in the Report Writer and modify the report I need to create a relationship with existing GP tables. When I am in the Report Writer and I choose the MY table and look at the table, I can see all the fields. When I go to create a relationship, I click the Relationship button, select Great Plains for the product, select the table I want and choose the index. In the portion of the form where you associate one field with another, the secondard table (GP) shows the fields that are in the index. H...

Updating Posting Account for Inventory Items
We just re-launched our company and did a massive effort to move as much existing data from the old company to the new. Unfortunately our inventory items accounts were not updated. Is there a way to set a default account for items at the same time? Or do we have to update each item? Are your items assigned to an item class? If so you can set the account at the class level and then roll it down to items in the class. -- Jim@TurboChef "Ken" wrote: > We just re-launched our company and did a massive effort to move as much > existing data from the old company to the new...

How can I count unique values in a query in the report footer
Am using Access 2003 I have a report that gives me the count of the status of individuals . This works fine as long is there is only one record in the query (in my query there is one record per month). When I query 12 months (individuals may appear in various months) it counts each record of an individual. For Example in a query considering 12 months for a widow Jones it may count her 12 times and for a survivor named Smith may count her 8 times: Widows 12 Survivors 8 I would like to add a count in the report footer that will tell me how many unique individuals I have in the repo...

SQL to mak all items TAX and CODE 128A
Hi, I have seen this before, but cannot find it now. I need a query to make all items Code 128A and also need to set all items as TAX (taxable). Thanks for your help. geokar, http://tinyurl.com/sy9lm SO Manager | Wizards | Task 170 Assign Sales Tax Must have access to CustomerSource -- * Get Secure! - www.microsoft.com/security You must be using Outlook Express or some other type of newsgroup reader to see and download the file attachment. If you are not using a reader, follow the link below to setup Outlook Express. Click on "Open with newsreader" under the MS Retail Ma...

VBA to create series of workbooks need to add code to skip creating a report if no lines meet criteria
Hi All, I need some assistance as where I'm at with this report is far beyond my level of knowledge and I'm hoping that someone can point me in the right direction. I've created a template that creates a report and displays variances based on the "search criteria" which is defined in another worksheet and hides any rows that are equal to Zero and are between the value (>= -5000 AND <= +5000) and what I'm trying to figure out is if there are no rows visible within the report then it should not save the report (that way I don't email a blank works...

Sales Literature 07-14-06
Is this a place to store brochures & cutsheets etc? How do you load them in? Is there a dummies guide to CRM3.0 that explains all this basic stuff for us idiots? -- Brendon Reid ...

how do I get more than one sub-total in pivot tables?
I am using pivot tables to show summary HR recruiting data. The data columns are nested at three levels - priority(a, b or c), type(add/replace), number of reqs and number of positions. The pivot table automatically gives me sub-totals within the priority so I get number of reqs that are adds of priority A for example. I also automatically get a total of number of reqs and total of number of positions. What I'm trying to get is the sub-total of number of reqs that are adds regardless of priority. Move Type to the column area, and you'll get columns with totals for Add and...

Calculate total amount on continuous form line in sub-form
Hi, I’m on Access in Office 2003. I have a sub-form which has a default view of “Continuous Forms”. This form is comprised of a single line which has the fields: Work Date Hours Miles Other Expense Total The work date, hours, miles, and Other expenses are data entry fields. The Total field is equal to (Hours * HourlyRate) + (Miles * MilageRate) + Other Expenses. The rates are retrieved from the main form. That part is working fine. The Work Date, Hours, Miles, and Other Expenses are bound to fields on my table. The Total field is unbound. The problem ...

counting totals, but not using "like"
Morning all, i have a pretty simple query totaling up the Incidents for a year. The field I am looking up is a text field, that has a lookup to another table (I know, most people don't like that, but I have a lot of options there) When I run the query: SELECT Count(tblSENEIncidentLogCY.[NATURE OF DISTRESS]) AS [CountOfNATURE OF DISTRESS] FROM tblDISTRESS INNER JOIN tblSENEIncidentLogCY ON tblDISTRESS.DISTRESS = tblSENEIncidentLogCY.[NATURE OF DISTRESS]; no problem, gives me a Total number of all entries in that field. What I am trying to do is have a number of only t...

Locking Items in a file?
Hi, I have a question on creating a template. I don't want to use the Master Page to create an option. I was hoping to find a way to lock in place certain features in the 4 pages so that someone else less skilled can plop in text around them. Is there a way to lock items in a file? For example I want the masthead and logos at the top of the newsletter left alone as well as the editing information on the back. Any help is greatly apprecited! -- -- Karen Peters You cannot lock objects in Publisher. -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ new...

Report not functionning in Money 2007
Hi ! I just finished the upgrade from Money 2004 to Money 2007. Everything went fine except that the Report function no longer work. Every time i try to get a report, the "Loading" stays there forever and nothing happen. Does someone has a clue of what is happening ? Andr� Hi Andre, I am using Money 2006 was also experiencing the problem you described where I would attempt to generate a report, would get the pie chart and then the "Loading" message and nothing would happen. I converted over from Quicken in 2005. I was only experiencing this problem when I tr...