How to generate the first group of data in report?

wangjanym · 2011-10-13

Hi, I have a question. Let's say there are data like below and I want to run a report to capture first group of data (Invoice No. 10000 and PO Number ABC1234).

Invoice No. PO Number
10000 ABC1234
10000 ABC1234
10000 ABC1234
10001 ABC1234
10001 ABC1234
10001 ABC1234

Output should be
10000 ABC1234
10000 ABC1234
10000 ABC1234

Any idea? I will be appreciated.

mohana_cse06 · 2011-10-14

Did you try by grouping on both fields and also adding a condition to print only first group?

julkifli33 · 2011-12-14

i already created report that i want in classic report
by grouping item ledger entry, for item no, variant code and lot no
so i show what in group footer
from body itself i hide it
my question is.... how to export in excel what i have done in report
which means i want to export to excel (group footer part)

Zephyr · 2011-12-14

Dear, you should use Table - Excel Buffer's function.

For more details you can search in Report - Customer - Order Detail, Report ID :: 108

julkifli33 · 2011-12-14

Zephyr wrote:

Dear, you should use Table - Excel Buffer's function.

For more details you can search in Report - Customer - Order Detail, Report ID :: 108

my question is not about export to excel
i can export to excel for raw data
but for grouping data?
i still confused

vijay_g · 2011-12-14

Did you try to set a condition when you are going to export group data?

If (exporttoexcel ) and (CurrReport.TOTALSCAUSEDBY = dataitem.FIELDNO(fieldno);)

julkifli33 · 2011-12-14

vijay_g wrote:
Did you try to set a condition when you are going to export group data?
If (exporttoexcel ) and (CurrReport.TOTALSCAUSEDBY = dataitem.FIELDNO(fieldno);)

i use this

           Counter := Counter +1;
           Row := 1;
           RecNo :=0;

          Window.OPEN(
           Text001 +
           '@1@@@@@@@@@@@@@@@@@@@@@\');

           //ExcelBuf.DELETEALL;
           CLEAR(ExcelBuf);
           REPEAT
              IF (("Item No." = TempItemNo) AND ("Variant Code" = TempVariantCode) AND ("Lot No." = TempLotNo)) THEN
                 QtyPerLot := QtyPerLot + Quantity
              ELSE
                 QtyPerLot := Quantity;
              IF (("Item No." <> TempItemNo) AND ("Variant Code" <> TempVariantCode) AND ("Lot No." <> TempLotNo)) THEN
              BEGIN
                Row := Row + 1;
                EnterCell(Row, 1,"Item No.", FALSE, FALSE, FALSE);
                EnterCell(Row, 2,"Variant Code", FALSE, FALSE, FALSE);
                EnterCell(Row, 3,"Lot No.", FALSE, FALSE, FALSE);
                EnterCell(Row, 4,FORMAT(QtyPerLot), FALSE, FALSE, FALSE);
                RecNo := RecNo + 1;
              END;
              //Window.UPDATE(1,ROUND(RecNo / TotalRecNo * 10000,1));
          UNTIL NEXT = 0;
          //Window.CLOSE;
          CreateExcelbook;
          TempItemNo := "Item No.";
          TempVariantCode := "Variant Code";
          TempLotNo := "Lot No.";

it means if the same item no, variant code, lot no.... it should sum up

Zephyr · 2011-12-14

Don't get confuse, its very simple

If you have done grouping then, keep same code(for Export to excel as your format for footer) on Group Footer's section with increasing Row no.

like this

Row := Row + 1;
EnterCell(Row, 4,FORMAT(GroupTotalQuantity), FALSE, FALSE, FALSE);

julkifli33 · 2011-12-14

Zephyr wrote:
Don't get confuse, its very simple

If you have done grouping then, keep same code(for Export to excel as your format for footer) on Group Footer's section with increasing Row no.

like this
Row := Row + 1;
EnterCell(Row, 4,FORMAT(GroupTotalQuantity), FALSE, FALSE, FALSE);

my raw data is like this
ITEM A VARIANT A LOT 1 = 10
ITEM A VARIANT A LOT 1 = 20
ITEM A VARIANT A LOT 1 = 30
these 3 will not showing in my report

only show like this
ITEM A VARIANT A LOT 1 = 60

but this one using grouping from wizard in standard NAV
so the textbox name is also the same (Quantity)
even i put my code in grouping section
but it still became raw data in my excel

Zephyr · 2011-12-14

why dont you use TotalFields property or CurrReport.CREATETOTALS() function.

vijay_g · 2011-12-14

where you put your code(on which groupfield section)..?
what are the grouping field?
what is key for dataitem?

it seems that you are doing manual grouping..!!

julkifli33 · 2011-12-14

Zephyr wrote:

why dont you use TotalFields property or CurrReport.CREATETOTALS() function.

can i use it for grouping?
i put this code --> CurrReport.CREATETOTALS(Quantity);
in data item
but still the same
how to use it?

Zephyr · 2011-12-14

1. Set you key in DataItemTableView of Data Item.

2. Put your field on which you want to do grouping in GroupTotalFields of Data Item.

3. Define the field on which you want to get Total in TotalFields of Data Item. OR Put the code in

[b]DataItem - OnPreDataItem()[/b]

CurrReport.CREATETOTALS();

(Note if that field is flow field then use CalcFields, otherwise you will get zero in total)

4. Put your field and code in group footer.

Hope it will help you.

julkifli33 · 2011-12-14

Zephyr wrote:
1. Set you key in DataItemTableView of Data Item.

2. Put your field on which you want to do grouping in GroupTotalFields of Data Item.

3. Define the field on which you want to get Total in TotalFields of Data Item. OR Put the code in
[b]DataItem - OnPreDataItem()[/b]

CurrReport.CREATETOTALS();
(Note if that field is flow field then use CalcFields, otherwise you will get zero in total)

4. Put your field and code in group footer.

Hope it will help you.

I already follow your step but still fail
here is my steps :
1. Item Ledger Entry, i add secondary keys : Item No,Variant Code,Lot No.
2. i create report using wizard that used this secondary code
3. i group it... i hide the body,i just show group per lot no. (because this one is the lowest level grouping)
(user need to know they have how many quantity for this item & variant in each lot no)
4. i create code to export to excel

as you can see here in my attachment, it show every transaction in item ledger entry
what i want is just only the one i highlight in yellow

vijay_g · 2011-12-14

As per your attachment you want your data to group with "variant code" not with "Lot No.". so just put your code on
groupfooter of "variant code" field and use createtotal(Quantity). You don't need to calculate it manually(Qty += Quantity).

hope now it will clear all.....!!!

julkifli33 · 2011-12-14

vijay_g wrote:

As per your attachment you want your data to group with "variant code" not with "Lot No.". so just put your code on
groupfooter of "variant code" field and use createtotal(Quantity). You don't need to calculate it manually(Qty += Quantity).

hope now it will clear all.....!!!

that's because my sample data only have 1 lot no
actually have multiple lot no

vijay_g · 2011-12-14

Then put your code on groupfooter of "Lot No.".

julkifli33 · 2011-12-14

vijay_g wrote:

Then put your code on groupfooter of "Lot No.".

that what i have done until now

vijay_g · 2011-12-14

why are you taking all unnecessary variable? and why are you writing bunch of code? just write which field you want to export..
Try to understand the basic way of grouping happening in Nav then go ahead until or unless you will get tired if you will go with your own way.

julkifli33 · 2011-12-14

vijay_g wrote:

why are you taking all unnecessary variable? and why are you writing bunch of code? just write which field you want to export..
Try to understand the basic way of grouping happening in Nav then go ahead until or unless you will get tired if you will go with your own way.

what do you mean unnecessary variable?
if you advice me to use this code, it can export
but only raw data... not grouping

Item Ledger Entry, GroupFooter (10) - OnPreSection()
CurrReport.SHOWOUTPUT :=
  CurrReport.TOTALSCAUSEDBY = "Item Ledger Entry".FIELDNO("Lot No.");

Item Ledger Entry, GroupFooter (10) - OnPostSection()
           Counter := Counter +1;
           Row := 1;
           RecNo :=0;

          Window.OPEN(
           Text001 +
           '@1@@@@@@@@@@@@@@@@@@@@@\');

          CLEAR(ExcelBuf);

           REPEAT
              BEGIN
                Row := Row + 1;
                EnterCell(Row, 1,"Item No.", FALSE, FALSE, FALSE);
                EnterCell(Row, 2,"Variant Code", FALSE, FALSE, FALSE);
                EnterCell(Row, 3,"Lot No.", FALSE, FALSE, FALSE);
                EnterCell(Row, 4,FORMAT(Quantity), FALSE, FALSE, FALSE);
                    RecNo := RecNo + 1;
              END;
              //Window.UPDATE(1,ROUND(RecNo / TotalRecNo * 10000,1));
                TempItemNo := "Item No.";
                TempVariantCode := "Variant Code";
                TempLotNo := "Lot No.";
            UNTIL NEXT = 0;
          //Window.CLOSE;
    CreateExcelbook;

vijay_g · 2011-12-14

Try it..

IF CurrReport.TOTALSCAUSEDBY = "Item Ledger Entry".FIELDNO("Lot No.") THEN
              BEGIN
                Row := Row + 1;
                EnterCell(Row, 1,"Item No.", FALSE, FALSE, FALSE);
                EnterCell(Row, 2,"Variant Code", FALSE, FALSE, FALSE);
                EnterCell(Row, 3,"Lot No.", FALSE, FALSE, FALSE);
                EnterCell(Row, 4,FORMAT(Quantity), FALSE, FALSE, FALSE);
              END;

julkifli33 · 2011-12-14

vijay_g wrote:

Try it..

IF CurrReport.TOTALSCAUSEDBY = "Item Ledger Entry".FIELDNO("Lot No.") THEN
              BEGIN
                Row := Row + 1;
                EnterCell(Row, 1,"Item No.", FALSE, FALSE, FALSE);
                EnterCell(Row, 2,"Variant Code", FALSE, FALSE, FALSE);
                EnterCell(Row, 3,"Lot No.", FALSE, FALSE, FALSE);
                EnterCell(Row, 4,FORMAT(Quantity), FALSE, FALSE, FALSE);
              END;

tried it...
it export raw data

How to generate the first group of data in report?

Comments

Categories