Group data on a Report

Business Central/NAV Developers Forum
1

In a report Sales, I have a option fied that filter data on the report.

In the report lines I have several lines, but somes lines has the same No…

Depending on thet option Field value, how camn I group lines By the same No.?

This is my Filter data on the Round Loop Report OnAfterGetRecord

IF (SalesLine.Turno= SalesLine.Turno::A) THEN BEGIN
TMQ:= SalesLine.Quantity
END ELSE BEGIN
IF (SalesLine.Turno= SalesLine.Turno::B) THEN
TTQ:= SalesLine.Quantity
ELSE
TNQ:=SalesLine.Quantity;
END;

TOTCONS+= TMQ+TTQ+TNQ;

I want to SUM SalesLine.Quantity for the Same NO. on The same SalesLine.Turno= SalesLine.Turno::X (A or B)

For the same Turno, i don’s want to see the ame No. repeated on diferente lines.

How can I do That ?

2

HI Luana,

if you Set the Record Variable in Ascending Order like In

Onpredatatitem

“Test table”.SETCURRENTKEY(“Test table”.Type);

“Test table”.ASCENDING(TRUE);

Test table - OnAfterGetRecord()
IF “Test table”.Type = “Test table”.Type::Account THEN
TotAcc := TotAcc + “Test table”.Total;
IF “Test table”.Type = “Test table”.Type::Account2 THEN
TotAcc2 := TotAcc2 + “Test table”.Total;

Test table - OnPostDataItem()
TotalValu := TotAcc + TotAcc2;

3

Hello Raja

On sales Line No. is not a Key, but even if i create a new Key No. the result is the same.

I get one line repeated several times for the same Turno

Thanks

4

Hello Luana,

Can You Provide us with some more details like Dataitems used , Indentation and Code … ANd Result u Need in Pics…

what i understood is first we have to sort/ filter by Option Field so i Made Option Field as Key By SETCURRENTKEY FUnc and Made in Ascending order SO …

If you want to Sort By Line No Then in Sales Line OnPRE dataitem Set Line No as Key and sort in Ascending Order

So that Line No… has same value will come One after other in ONAFTER Like Line no 10000 Line no 10000 with diff order no whatever may be then

have a text variable which Holds The Previous Line No and Compares with latest if Same then calculate Sum and If New…I

Then change the text Variable with new Line No.

You can Use Case For Checking for Option fields…

are you using Integer Dataitem …

5

Hi ,

Which Version you are using is it RTC Or Classic…If RTC you Can Write In RDLC… Directly[:D]

6

Exactly Raja, I was just going to suggest that this kind of grouping is much easier done directly in RDLC.

7

Hello Raja

I am using the classic version.

Sending some details

Thanks

8

In sales line I have this:

Some lines on fifferent Shift’s but consuming the same Itm AD00011

9

In sales line I have this:

Some lines on fifferent Shift’s but consuming the same Itm AD00011

10

OBJECT Report 50104 Consume List

{

OBJECT-PROPERTIES

{

Date=10-03-16;

Time=14:54:09;

Modified=Yes;

Version List=ENF-BLOCO;

}

PROPERTIES

{

CaptionML=[ENU=Order Confirmation;

PTG=Folha de Gastos INT];

TopMargin=400;

BottomMargin=100;

LeftMargin=1000;

RightMargin=1000;

OnInitReport=BEGIN

GLSetup.GET;

CompanyInfo.GET;

SalesSetup.GET;

CASE SalesSetup.“Logo Position on Documents” OF

SalesSetup.“Logo Position on Documents”::“No Logo”:;

SalesSetup.“Logo Position on Documents”::Left:

BEGIN

CompanyInfo.CALCFIELDS(Picture);

END;

SalesSetup.“Logo Position on Documents”::Center:

BEGIN

CompanyInfo1.GET;

CompanyInfo1.CALCFIELDS(Picture);

END;

SalesSetup.“Logo Position on Documents”::Right:

BEGIN

CompanyInfo2.GET;

CompanyInfo2.CALCFIELDS(Picture);

END;

END;

CompanyInfo1.CALCFIELDS(Picture);

END;

HorzGrid=210;

}

DATAITEMS

{

{ PROPERTIES

{

DataItemTable=Table36;

DataItemTableView=SORTING(Document Type,No.)

WHERE(Document Type=CONST(Order));

NewPagePerRecord=Yes;

ReqFilterHeadingML=[ENU=Sales Order;

PTG=Encomenda Venda];

OnAfterGetRecord=BEGIN

CurrReport.LANGUAGE := Language.GetLanguageID(“Language Code”);

CompanyInfo.GET;

IF RespCenter.GET(“Responsibility Center”) THEN BEGIN

FormatAddr.RespCenter(CompanyAddr,RespCenter);

CompanyInfo.“Phone No.” := RespCenter.“Phone No.”;

CompanyInfo.“Fax No.” := RespCenter.“Fax No.”;

END ELSE

FormatAddr.Company(CompanyAddr,CompanyInfo);

DocDim1.SETRANGE(“Table ID”,DATABASE::“Sales Header”);

DocDim1.SETRANGE(“Document Type”,“Sales Header”.“Document Type”);

DocDim1.SETRANGE(“Document No.”,“Sales Header”.“No.”);

IF “Salesperson Code” = ‘’ THEN BEGIN

CLEAR(SalesPurchPerson);

SalesPersonText := ‘’;

END ELSE BEGIN

SalesPurchPerson.GET(“Salesperson Code”);

SalesPersonText := Text000;

END;

IF “Your Reference” = ‘’ THEN

ReferenceText := ‘’

ELSE

ReferenceText := FIELDCAPTION(“Your Reference”);

IF “VAT Registration No.” = ‘’ THEN

VATNoText := ‘’

ELSE

VATNoText := FIELDCAPTION(“VAT Registration No.”);

IF “Currency Code” = ‘’ THEN BEGIN

GLSetup.TESTFIELD(“LCY Code”);

TotalText := STRSUBSTNO(Text001,GLSetup.“LCY Code”);

TotalInclVATText := STRSUBSTNO(Text002,GLSetup.“LCY Code”);

11

Hi Luana

Why Have Wrote Code on Round Loop Instead On Sales Line …

12

Hi Raja

My experience In NAV C/AL isn’t a lot, so I used standard Report 202, and tryed to get What I need.

I Think I’m all most there, IF I get this Item Group per shift the report will be OK.

Thanks for you help

13

Documentation()

RoundLoop - OnPreDataItem()

MoreLines := SalesLine.FIND(’+’);

WHILE MoreLines AND (SalesLine.Description = ‘’) AND (SalesLine.“Description 2”= ‘’) AND (SalesLine.“No.” = ‘’) AND (SalesLine.

Quantity = 0) AND (SalesLine.Amount = 0) DO

MoreLines := SalesLine.NEXT(-1) <> 0;

IF NOT MoreLines THEN

CurrReport.BREAK;

SalesLine.SETRANGE(“Line No.”,0,SalesLine.“Line No.”);

SETRANGE(Number,1,SalesLine.COUNT);

CurrReport.CREATETOTALS(SalesLine.“Line Amount”,SalesLine.“Inv. Discount Amount”);

SalesLine.SETCURRENTKEY(SalesLine.“No.”);

SalesLine.ASCENDING(TRUE);

TOTCONS:=0;

TMQ:=0;

TTQ:=0;

TNQ:=0;

RoundLoop - OnAfterGetRecord()

IF Number = 1 THEN

SalesLine.FIND(’-’)

ELSE

SalesLine.NEXT;

“Sales Line” := SalesLine;

IF NOT “Sales Header”.“Prices Including VAT” AND

(SalesLine.“VAT Calculation Type” = SalesLine.“VAT Calculation Type”::“Full VAT”)

THEN

SalesLine.“Line Amount” := 0;

IF (SalesLine.Type = SalesLine.Type::“G/L Account”) AND (NOT ShowInternalInfo) THEN

“Sales Line”.“No.” := ‘’;

//MYCODE10 03 2016 - SN

SalesLine.SETCURRENTKEY(SalesLine.“No.”);

SalesLine.ASCENDING(TRUE);

SalesLine.SETRANGE(“No.”,SalesLine.“No.”);

IF (SalesLine.Turno= SalesLine.Turno::Manha) THEN BEGIN

TMQ := TMQ + SalesLine.Quantity

Message(‘Tur no%1 and Quyantity : %2’,SalesLine.Turno,TMQ);

END ELSE BEGIN

IF (SalesLine.Turno= SalesLine.Turno::Tarde) THEN

TTQ := TTQ + SalesLine.Quantity

Message(‘Tur no%1 and Quyantity : %2’,SalesLine.Turno,TMQ);

ELSE

TNQ := TNQ + SalesLine.Quantity;

Message(‘Tur no%1 and Quyantity : %2’,SalesLine.Turno,TMQ);

END;

TOTCONS += TMQ + TTQ + TNQ;

//MYCODE10 03 2016 - EN

RoundLoop - OnPostDataItem()

Message(‘TOTCOns Quyantity : %2’,TOTCONS);

SalesLine.DELETEALL;

14

Hi Luana,
One of the best ways to make other members give up to solve your problem. Post a whole object in text, so that you have to scroll several pages down to get it. Next time please just attach the file. :slight_smile:
Otherwise I hope you got it work with Raja latest suggestion.