Tuesday, February 16, 2016

Custom Report :Purchase Quantity of Items By Purchase Order No in Export To Excel.

This Report is based on The Requirement of Adam's Post So Thanks  to Him...

Overall Requirement
The Requirement is Report Which should Contain Purchase Details of Items in quantity Per Order No/Document No .

Result should be Like

As we all Know Order Nos/No of Orders Would be more and we would Get Error if you got more records in your table ...

So for the above problem the solution is
1.To Use Template which already Consists Of  Items  and Compare .
2. Dynamically print a Excel Sheet And Open And Compare them with Purchase Order Line items.

Now We are going to implement Second .
As per requirement i'm Using  3 Dataitems

Indention          Name
0                       Item1            //To Print Item List page.
0                       Purchase Line
1                       Item2         // to get wat respective Purchase Line Has.

Now Link item 2 with purchase line by Item No as like Select item2 and Select properties
Like Below DataitemLink.

Now Coming to Technical requirements as we have To Select Orders Range we need two code datatypes  and one filename for taking Excel Filename other For Sheetname ,One Excel Buffer of Record Datatype and Soon

Lets First Come From Request Page ... In Request page I Need  a FileName or select a Previous file created or Create a New File...And Select It and Give a Sheet Name. then

Two Lookup Fields For Document No in Purchase Orders Page....(56) that's It For Request Page

Code For File Name  and LookUp Code...

Same Above Code For End Document No /DocFilterEnd  lookup with Global variable assign to DocFilterEnd

So that these will Be Used To Set the Range / Filter
Request Page::

Now Coming TO Main Code

First  am Creating A New Item List Excel Sheet With Given FileNAme And SheetName then i will
loop through All Items So insert Item Values (Item No, Description) in Excel Sheet.

So that in Next Purchase Line Am Filtering List By Order No which is nothing But Document No...

using variables Passed In Request Page . then Searching /reading the sheet so that to find the value/item No in Purchase Line and When Found in The Respective Row creating a New Column and  Inserting the Values We Wanted (i.e Quantity)
then Result Would be like..

sorry lols For Unusual Sheet Name............
Here's Code

Just To Show My results Clearly i have set those item filter Remove That You will get all Items...

Sorry I Have Almost Got To say that i have Created Another Function i.e GiveUserControl1 So that it Wont Display My Excel Sheet After Creating ..I had Only Changed visible := False in that Excel Buffer.