Howdy, Stranger!
Categories
- All Categories
- 73 General
- 73 Announcements
- 66.6K Microsoft Dynamics NAV
- 18.6K NAV Three Tier
- 38.4K NAV/Navision Classic Client
- 3.6K Navision Attain
- 2.4K Navision Financials
- 115 Navision DOS
- 854 Navision e-Commerce
- 1K NAV Tips & Tricks
- 772 NAV Dutch speaking only
- 615 NAV Courses, Exams & Certification
- 2K Microsoft Dynamics-Other
- 1.5K Dynamics AX
- 295 Dynamics CRM
- 109 Dynamics GP
- 10 Dynamics SL
- 1.5K Other
- 993 SQL General
- 384 SQL Performance
- 34 SQL Tips & Tricks
- 34 Design Patterns (General & Best Practices)
- Architectural Patterns
- 10 Design Patterns
- 5 Implementation Patterns
- 53 3rd Party Products, Services & Events
- 1.7K General
- 1.1K General Chat
- 1.6K Website
- 79 Testing
- 1.2K Download section
- 23 How Tos section
- 259 Feedback
- 12 NAV TechDays 2013 Sessions
- 13 NAV TechDays 2012 Sessions
Top __ Customers Report - By "Item Category Code"-CHALLENGE!
emulsified
Member Posts: 139
I have a report that I want to modify so that I can filter it by a "Item Category Code" which is only available on the Item Card, however I did accomplish this on another report that used the Value Entry table by adding a drop-down box to the Options tab of that report and in code I used something similar to this:
Here is the report I want to modify below:
Here is the other report that I got the idea from:
Anybody have any ideas??
// Added to show ONLY Items of chosen "Item Category Code" on Options form
Item.GET("Value Entry"."Item No.");
IF ItemCategoryCode <> '' THEN BEGIN
IF Item."Item Category Code" <> ItemCategoryCode THEN CurrReport.SKIP;
END;
// END MODIFICATION
Here is the report I want to modify below:
OBJECT Report 10062 Top __ Customer List
{
OBJECT-PROPERTIES
{
Date=08/07/06;
Time=12:00:00 PM;
Version List=NAVNA4.00.03;
}
PROPERTIES
{
CaptionML=[ENU=Top __ Customer List;
ESM=Listado __ mejores clientes;
FRC=Liste des meilleurs __ clients;
ENC=Top __ Customer List];
OnPreReport=BEGIN
CompanyInformation.GET;
IF CustomersToRank = 0 THEN // default
CustomersToRank := 20;
IF TopType = TopType::"Balance ($)" THEN BEGIN
SubTitle := Text001;
ColHead := Text002;
END ELSE BEGIN
IF Customer.GETFILTER("Date Filter") = '' THEN
SubTitle := Text003
ELSE
SubTitle := Text004 + ' ' +
Customer.GETFILTER("Date Filter") + ')';
ColHead := Text005;
END;
MainTitle := Text006 + ' '+ FORMAT(CustomersToRank) + Text007;
{ Temporarily remove date filter, since it will show in the header anyway }
Customer.SETRANGE("Date Filter");
FilterString := Customer.GETFILTERS;
END;
}
DATAITEMS
{
{ PROPERTIES
{
DataItemTable=Table2000000026;
DataItemTableView=SORTING(Number)
WHERE(Number=CONST(1));
DataItemVarName=Heading;
}
SECTIONS
{
{ PROPERTIES
{
SectionType=Header;
PrintOnEveryPage=Yes;
SectionWidth=18150;
SectionHeight=1692;
}
CONTROLS
{
{ 1001;TextBox ;0 ;0 ;7500 ;423 ;FontSize=8;
FontBold=Yes;
SourceExpr=MainTitle }
{ 1002;TextBox ;14850;0 ;2100 ;423 ;HorzAlign=Right;
SourceExpr=FORMAT(TODAY,0,4) }
{ 1003;TextBox ;17100;0 ;1050 ;423 ;HorzAlign=Right;
SourceExpr=TIME;
Format=<Hours12>:<Minutes,2> <AM/PM> }
{ 1004;TextBox ;0 ;423 ;7500 ;423 ;SourceExpr=CompanyInformation.Name }
{ 1005;Label ;16950;423 ;750 ;423 ;ParentControl=1006;
HorzAlign=Right }
{ 1006;TextBox ;17700;423 ;450 ;423 ;CaptionML=[ENU=Page;
ESM=P gina;
FRC=Page;
ENC=Page];
SourceExpr=CurrReport.PAGENO }
{ 1007;TextBox ;16650;846 ;1500 ;423 ;HorzAlign=Right;
SourceExpr=USERID }
{ 1008;TextBox ;0 ;1269 ;7500 ;423 ;SourceExpr=SubTitle }
}
}
{ PROPERTIES
{
SectionType=Header;
PrintOnEveryPage=Yes;
SectionWidth=18150;
SectionHeight=423;
OnPreSection=BEGIN
CurrReport.SHOWOUTPUT := (CurrReport.PAGENO = 1) AND (FilterString <> '');
END;
}
CONTROLS
{
{ 10 ;TextBox ;0 ;0 ;18150;423 ;SourceExpr=FilterString }
}
}
{ PROPERTIES
{
SectionType=Header;
PrintOnEveryPage=Yes;
SectionWidth=18150;
SectionHeight=1269;
}
CONTROLS
{
{ 11 ;TextBox ;8250 ;0 ;1800 ;846 ;ParentControl=19;
HorzAlign=Right;
VertAlign=Bottom;
FontBold=Yes;
MultiLine=Yes;
SourceExpr=ColHead }
{ 12 ;Label ;0 ;0 ;750 ;846 ;ParentControl=15;
HorzAlign=Right;
VertAlign=Bottom;
FontBold=Yes;
MultiLine=Yes }
{ 14 ;Label ;7200 ;0 ;900 ;846 ;ParentControl=18;
HorzAlign=Right;
VertAlign=Bottom;
FontBold=Yes;
MultiLine=Yes;
CaptionML=[ENU=%;
ESM=%;
FRC=%;
ENC=%] }
{ 1 ;Label ;900 ;0 ;1800 ;846 ;ParentControl=16;
VertAlign=Bottom;
FontBold=Yes;
MultiLine=Yes }
{ 13 ;Label ;2850 ;0 ;4200 ;846 ;ParentControl=17;
VertAlign=Bottom;
FontBold=Yes;
MultiLine=Yes;
CaptionML=[ENU=Name;
ESM=Nombre;
FRC=Nom;
ENC=Name] }
}
}
}
}
{ PROPERTIES
{
DataItemIndent=1;
DataItemTable=Table18;
DataItemTableView=SORTING(No.);
OnPreDataItem=BEGIN
NextTopLineNo := 1;
Window.OPEN(Text000 + ' #1########');
END;
OnAfterGetRecord=BEGIN
Window.UPDATE(1,"No.");
IF TopType = TopType::"Balance ($)" THEN BEGIN
CALCFIELDS("Balance on Date (LCY)");
TempAmount := "Balance on Date (LCY)";
END ELSE BEGIN
CALCFIELDS("Sales (LCY)");
TempAmount := "Sales (LCY)";
END;
GrandTotal := GrandTotal + TempAmount;
TopNo[NextTopLineNo] := "No.";
TopAmount[NextTopLineNo] := TempAmount;
TopName[NextTopLineNo] := Name;
i := NextTopLineNo;
IF NextTopLineNo < (CustomersToRank + 1) THEN
NextTopLineNo := NextTopLineNo + 1;
WHILE (i > 1) DO BEGIN
i := i - 1;
IF (TopAmount[i + 1] > TopAmount[i]) THEN BEGIN
// Sort the Customers by amount, largest should be first, smallest last. Put
// values from position i into save variables, move values from position
// i+1 to position i then put save values back in array in position i+1.
TempNo := TopNo[i];
TempAmount := TopAmount[i];
TempName := TopName[i];
TopNo[i] := TopNo[i + 1];
TopAmount[i] := TopAmount[i + 1];
TopName[i] := TopName[i + 1];
TopNo[i + 1] := TempNo;
TopAmount[i + 1] := TempAmount;
TopName[i + 1] := TempName;
END;
END;
END;
ReqFilterFields=No.,Customer Posting Group,Salesperson Code,Date Filter;
}
SECTIONS
{
}
}
{ PROPERTIES
{
DataItemIndent=1;
DataItemTable=Table2000000026;
DataItemTableView=SORTING(Number);
MaxIteration=99;
DataItemVarName=Print Loop;
OnPreDataItem=BEGIN
Window.CLOSE;
i := 0;
END;
OnAfterGetRecord=BEGIN
i := i + 1;
IF i = NextTopLineNo THEN
CurrReport.BREAK;
TopTotal := TopTotal + TopAmount[i];
IF (TopAmount[1] > 0) AND (TopAmount[i] > 0) THEN
BarText :=
ParagraphHandling.PadStrProportional('',ROUND(TopAmount[i] / TopAmount[1] * 61,1),7,'|')
ELSE
BarText := '';
IF GrandTotal <> 0 THEN
"Top%" := ROUND(TopAmount[i] / GrandTotal * 100,0.1)
ELSE
"Top%" := 0;
END;
}
SECTIONS
{
{ PROPERTIES
{
SectionType=Body;
SectionWidth=18150;
SectionHeight=423;
}
CONTROLS
{
{ 15 ;TextBox ;0 ;0 ;750 ;423 ;CaptionML=[ENU=Rank;
ESM=Posici¢n;
FRC=Classe;
ENC=Rank];
SourceExpr=i }
{ 16 ;TextBox ;900 ;0 ;1800 ;423 ;HorzAlign=Left;
CaptionML=[ENU=Customer;
ESM=Cliente;
FRC=Client;
ENC=Customer];
SourceExpr=TopNo[i] }
{ 18 ;TextBox ;7200 ;0 ;900 ;423 ;DecimalPlaces=1:1;
SourceExpr="Top%" }
{ 19 ;TextBox ;8250 ;0 ;1800 ;423 ;SourceExpr=TopAmount[i] }
{ 20 ;TextBox ;10200;0 ;7950 ;423 ;SourceExpr=BarText }
{ 17 ;TextBox ;2850 ;0 ;4200 ;423 ;SourceExpr=TopName[i] }
}
}
{ PROPERTIES
{
SectionType=Footer;
SectionWidth=18150;
SectionHeight=846;
OnPreSection=BEGIN
IF TopType = TopType::"Balance ($)" THEN
TopTotalText := 'Amount Outstanding'
ELSE
TopTotalText := 'Sales';
IF GrandTotal <> 0 THEN
"Top%" := ROUND(TopTotal / GrandTotal * 100,0.1)
ELSE
"Top%" := 0;
END;
}
CONTROLS
{
{ 21 ;TextBox ;2850 ;0 ;4200 ;423 ;HorzAlign=General;
SourceExpr=STRSUBSTNO('Top %1 Total %2',CustomersToRank,TopTotalText) }
{ 23 ;TextBox ;7200 ;0 ;900 ;423 ;DecimalPlaces=1:1;
SourceExpr="Top%" }
{ 24 ;TextBox ;8250 ;0 ;1800 ;423 ;SourceExpr=TopTotal }
}
}
{ PROPERTIES
{
SectionType=Footer;
SectionWidth=18150;
SectionHeight=1269;
OnPreSection=BEGIN
CurrReport.SHOWOUTPUT := (GrandTotal <> TopTotal);
END;
}
CONTROLS
{
{ 27 ;TextBox ;7200 ;0 ;900 ;423 ;DecimalPlaces=1:1;
SourceExpr=100.0 - "Top%" }
{ 28 ;TextBox ;8250 ;0 ;1800 ;423 ;SourceExpr=GrandTotal - TopTotal }
{ 29 ;Label ;2850 ;0 ;4200 ;423 ;ParentControl=27;
CaptionML=[ENU=All other customers;
ESM=Otros clientes;
FRC=Tous les autres clients;
ENC=All other customers] }
{ 30 ;TextBox ;2850 ;846 ;4200 ;423 ;HorzAlign=Left;
SourceExpr='Total ' + TopTotalText }
{ 31 ;TextBox ;8250 ;846 ;1800 ;423 ;SourceExpr=GrandTotal }
{ 33 ;Label ;7200 ;846 ;900 ;423 ;HorzAlign=Right;
CaptionML=[ENU=100.0;
ESM=100,0;
FRC=100,0;
ENC=100.0] }
}
}
}
}
}
REQUESTFORM
{
PROPERTIES
{
Width=7260;
Height=1650;
SaveValues=Yes;
}
CONTROLS
{
{ 1 ;OptionButton ;3410 ;0 ;3850 ;440 ;CaptionML=[ENU=Sales ($);
ESM=Ventas ($);
FRC=Ventes ($);
ENC=Sales ($)];
SourceExpr=TopType;
OptionValue=Sales ($) }
{ 3 ;Label ;0 ;0 ;3300 ;440 ;ParentControl=1;
CaptionML=[ENU=Show;
ESM=Mostrar;
FRC=Afficher;
ENC=Show] }
{ 5 ;OptionButton ;3410 ;550 ;3850 ;440 ;CaptionML=[ENU=Balance ($);
ESM=Saldo ($);
FRC=Solde ($);
ENC=Balance ($)];
SourceExpr=TopType;
OptionValue=Balance ($) }
{ 6 ;TextBox ;3410 ;1210 ;1650 ;440 ;CaptionML=[ENU=Quantity;
ESM=Cantidad;
FRC=Quantit‚;
ENC=Quantity];
SourceExpr=CustomersToRank;
OnValidate=BEGIN
IF CustomersToRank > 99 THEN
ERROR('Number of customers must be no greater than 99');
END;
}
{ 7 ;Label ;0 ;1210 ;3300 ;440 ;ParentControl=6;
CaptionML=[ENU=No. of Customers to Rank;
ESM=N§ clientes para clasificar;
FRC=Nbre clients … classifier;
ENC=No. of Customers to Rank] }
}
}
CODE
{
VAR
FilterString@1000 : Text[250];
MainTitle@1001 : Text[88];
SubTitle@1002 : Text[132];
ColHead@1003 : Text[9];
TempName@1004 : Text[30];
TempNo@1005 : Text[40];
TopTotalText@1006 : Text[40];
BarText@1007 : Text[250];
TopName@1008 : ARRAY [100] OF Text[30];
"Top%"@1009 : Decimal;
TempAmount@1010 : Decimal;
GrandTotal@1011 : Decimal;
TopAmount@1012 : ARRAY [100] OF Decimal;
TopTotal@1013 : Decimal;
i@1014 : Integer;
NextTopLineNo@1015 : Integer;
CustomersToRank@1016 : Integer;
TopType@1017 : 'Sales ($),Balance ($)';
TopNo@1018 : ARRAY [100] OF Code[20];
CompanyInformation@1019 : Record 79;
Window@1020 : Dialog;
ParagraphHandling@1021 : Codeunit 10025;
Text000@1022 : TextConst 'ENU="Going through customers ";ESM="Examinando clientes ";FRC="Clients en traitement ";ENC="Going through customers "';
Text001@1023 : TextConst 'ENU=(by Balance Due);ESM=(por saldo vencido);FRC=(par solde d–);ENC=(by Balance Due)';
Text002@1024 : TextConst 'ENU=Balances;ESM=Saldos;FRC=Soldes;ENC=Balances';
Text003@1025 : TextConst 'ENU=(by Total Sales);ESM=(por vtas. totales);FRC=(par ventes totales);ENC=(by Total Sales)';
Text004@1026 : TextConst 'ENU=(by Sales During the Period;ESM=(por vtas. durante el per¡odo;FRC=(par ventes pendant la p‚riode;ENC=(by Sales During the Period';
Text005@1027 : TextConst 'ENU=Sales;ESM=Ventas;FRC=Ventes;ENC=Sales';
Text006@1028 : TextConst 'ENU=Top;ESM=Superior;FRC=Haut;ENC=Top';
Text007@1029 : TextConst 'ENU=" Customers";ESM=" Clientes";FRC=" Clients";ENC=" Customers"';
BEGIN
END.
}
}
Here is the other report that I got the idea from:
OBJECT Report 10048 Customer/Item Statistics
{
OBJECT-PROPERTIES
{
Date=06/18/07;
Time=[ 9:55:53 AM];
Modified=Yes;
Version List=NAVNA4.00.02;
}
PROPERTIES
{
CaptionML=[ENU=Customer/Item Statistics;
ESM=Estad. cliente/producto;
FRC=Statistiques client/article;
ENC=Customer/Item Statistics];
OnPreReport=BEGIN
PeriodText := "Value Entry".GETFILTER("Posting Date");
CompanyInformation.GET;
FilterString := Customer.GETFILTERS;
FilterString2 := "Value Entry".GETFILTERS;
IF PrintToExcel THEN
MakeExcelInfo;
END;
OnPostReport=BEGIN
IF PrintToExcel THEN
CreateExcelbook;
END;
}
DATAITEMS
{
{ PROPERTIES
{
DataItemTable=Table18;
PrintOnlyIfDetail=Yes;
OnPreDataItem=BEGIN
CurrReport.NEWPAGEPERRECORD := OnlyOnePerPage;
CurrReport.CREATETOTALS("Value Entry"."Sales Amount (Actual)",Profit,"Value Entry"."Discount Amount");
END;
ReqFilterFields=No.,Search Name,Customer Posting Group;
}
SECTIONS
{
{ PROPERTIES
{
SectionType=Header;
PrintOnEveryPage=Yes;
SectionWidth=18150;
SectionHeight=1692;
OnPreSection=BEGIN
IF PrintToExcel THEN
CurrReport.SHOWOUTPUT(FALSE);
END;
}
CONTROLS
{
{ 1001;Label ;0 ;0 ;7500 ;423 ;FontSize=8;
FontBold=Yes;
CaptionML=[ENU=Customer/Item Statistics;
ESM=Estad. cliente/producto;
FRC=Statistiques client/article;
ENC=Customer/Item Statistics] }
{ 1002;TextBox ;14850;0 ;2100 ;423 ;HorzAlign=Right;
SourceExpr=FORMAT(TODAY,0,4) }
{ 1003;TextBox ;17100;0 ;1050 ;423 ;HorzAlign=Right;
SourceExpr=TIME;
Format=<Hours12>:<Minutes,2> <AM/PM> }
{ 1004;TextBox ;0 ;423 ;7500 ;423 ;SourceExpr=CompanyInformation.Name }
{ 1005;Label ;16950;423 ;750 ;423 ;ParentControl=1006;
HorzAlign=Right }
{ 1006;TextBox ;17700;423 ;450 ;423 ;CaptionML=[ENU=Page;
ESM=P gina;
FRC=Page;
ENC=Page];
SourceExpr=CurrReport.PAGENO }
{ 1007;TextBox ;16650;846 ;1500 ;423 ;HorzAlign=Right;
SourceExpr=USERID }
}
}
{ PROPERTIES
{
SectionType=Header;
PrintOnEveryPage=Yes;
SectionWidth=18150;
SectionHeight=423;
OnPreSection=BEGIN
CurrReport.SHOWOUTPUT((CurrReport.PAGENO = 1) AND (FilterString <> '') AND NOT PrintToExcel);
END;
}
CONTROLS
{
{ 9 ;TextBox ;0 ;0 ;18150;423 ;SourceExpr=Customer.TABLECAPTION + ': ' + FilterString }
}
}
{ PROPERTIES
{
SectionType=Header;
PrintOnEveryPage=Yes;
SectionWidth=18150;
SectionHeight=423;
OnPreSection=BEGIN
CurrReport.SHOWOUTPUT((CurrReport.PAGENO = 1) AND (FilterString2 <> '') AND NOT PrintToExcel);
END;
}
CONTROLS
{
{ 10 ;TextBox ;0 ;0 ;18150;423 ;SourceExpr="Value Entry".TABLECAPTION + ': ' + FilterString2 }
}
}
{ PROPERTIES
{
SectionType=Header;
PrintOnEveryPage=Yes;
SectionWidth=18150;
SectionHeight=1692;
OnPreSection=BEGIN
IF PrintToExcel THEN
CurrReport.SHOWOUTPUT(FALSE);
END;
}
CONTROLS
{
{ 11 ;Label ;0 ;423 ;1800 ;423 ;ParentControl=22;
VertAlign=Bottom;
FontBold=Yes;
CaptionML=[ENU=Customer No;
ESM=N§ cliente;
FRC=Nø de client;
ENC=Customer No] }
{ 12 ;Label ;2700 ;423 ;4500 ;423 ;ParentControl=23;
VertAlign=Bottom;
FontBold=Yes }
{ 15 ;Label ;300 ;846 ;2250 ;423 ;ParentControl=33;
VertAlign=Bottom;
FontBold=Yes }
{ 16 ;Label ;3000 ;846 ;4500 ;423 ;ParentControl=34;
VertAlign=Bottom;
FontBold=Yes }
{ 17 ;Label ;7650 ;423 ;1050 ;846 ;ParentControl=35;
HorzAlign=Right;
VertAlign=Bottom;
FontBold=Yes;
MultiLine=Yes }
{ 18 ;Label ;10500;423 ;1800 ;846 ;ParentControl=37;
HorzAlign=Right;
VertAlign=Bottom;
FontBold=Yes;
MultiLine=Yes }
{ 19 ;Label ;12450;423 ;1800 ;846 ;ParentControl=38;
HorzAlign=Right;
VertAlign=Bottom;
FontBold=Yes;
MultiLine=Yes }
{ 20 ;Label ;14400;423 ;1800 ;846 ;ParentControl=39;
HorzAlign=Right;
VertAlign=Bottom;
FontBold=Yes;
MultiLine=Yes }
{ 21 ;Label ;16350;423 ;900 ;846 ;ParentControl=40;
HorzAlign=Right;
VertAlign=Bottom;
FontBold=Yes;
MultiLine=Yes }
{ 1 ;Label ;8850 ;423 ;1500 ;846 ;ParentControl=36;
HorzAlign=General;
VertAlign=Bottom;
FontBold=Yes;
MultiLine=Yes }
}
}
{ PROPERTIES
{
SectionType=Body;
SectionWidth=18150;
SectionHeight=846;
OnPreSection=BEGIN
IF PrintToExcel THEN
CurrReport.SHOWOUTPUT(FALSE);
END;
}
CONTROLS
{
{ 22 ;TextBox ;0 ;0 ;1800 ;423 ;HorzAlign=Left;
CaptionML=[ENU=Customer No.;
ESM=N§ cliente;
FRC=Nø de client;
ENC=Customer No.];
SourceExpr="No." }
{ 23 ;TextBox ;2700 ;0 ;4500 ;423 ;SourceExpr=Name }
{ 24 ;TextBox ;8850 ;0 ;2250 ;423 ;SourceExpr="Phone No." }
{ 25 ;TextBox ;12750;0 ;3750 ;423 ;SourceExpr=Contact }
{ 26 ;Label ;7800 ;0 ;900 ;423 ;FontBold=Yes;
CaptionML=[ENU=Phone:;
ESM=Tel‚fono:;
FRC=T‚l‚phone:;
ENC=Phone:] }
{ 27 ;Label ;11400;0 ;1200 ;423 ;FontBold=Yes;
CaptionML=[ENU=Contact:;
ESM=Contacto:;
FRC=Contact :;
ENC=Contact:] }
}
}
{ PROPERTIES
{
SectionType=Footer;
SectionWidth=18150;
SectionHeight=1269;
OnPreSection=BEGIN
"CalculateProfit%";
IF PrintToExcel THEN
CurrReport.SHOWOUTPUT(FALSE);
END;
}
CONTROLS
{
{ 28 ;TextBox ;10500;423 ;1800 ;423 ;SourceExpr="Value Entry"."Sales Amount (Actual)" }
{ 29 ;TextBox ;12450;423 ;1800 ;423 ;SourceExpr=Profit }
{ 30 ;TextBox ;14400;423 ;1800 ;423 ;SourceExpr="Value Entry"."Discount Amount" }
{ 31 ;TextBox ;16350;423 ;900 ;423 ;HorzAlign=Right;
DecimalPlaces=1:1;
SourceExpr="Profit%" }
{ 32 ;Label ;2700 ;423 ;4800 ;423 ;CaptionML=[ENU=Report Total;
ESM=Informe total;
FRC=Total du rapport;
ENC=Report Total] }
}
}
}
}
{ PROPERTIES
{
DataItemIndent=1;
DataItemTable=Table5802;
DataItemTableView=SORTING(Source Type,Source No.,Item Ledger Entry Type,Item No.,Posting Date)
WHERE(Source Type=CONST(Customer),
Item Ledger Entry Type=CONST(Sale),
Expected Cost=CONST(No));
OnPreDataItem=BEGIN
CurrReport.CREATETOTALS("Invoiced Quantity","Sales Amount (Actual)",Profit,"Discount Amount");
END;
OnAfterGetRecord=BEGIN
Profit := "Sales Amount (Actual)" + "Cost Amount (Actual)";
"Discount Amount" := -"Discount Amount";
// Added to show ONLY Items of chosen "Item Category Code" on Options form
Item.GET("Value Entry"."Item No.");
IF ItemCategoryCode <> '' THEN BEGIN
IF Item."Item Category Code" <> ItemCategoryCode THEN CurrReport.SKIP;
END;
// END MODIFICATION
END;
ReqFilterFields=Item No.,Inventory Posting Group,Posting Date;
GroupTotalFields=Item No.;
DataItemLink=Source No.=FIELD(No.),
Global Dimension 1 Code=FIELD(Global Dimension 1 Filter),
Global Dimension 2 Code=FIELD(Global Dimension 2 Filter);
}
SECTIONS
{
{ PROPERTIES
{
SectionType=GroupFooter;
SectionWidth=18150;
SectionHeight=423;
OnPreSection=BEGIN
"CalculateProfit%";
IF NOT Item.GET("Item No.") THEN BEGIN
Item.Description := Text000;
Item."Base Unit of Measure" := '';
END;
IF PrintToExcel THEN BEGIN
MakeExcelDataBody;
CurrReport.SHOWOUTPUT(FALSE);
END;
END;
}
CONTROLS
{
{ 33 ;TextBox ;300 ;0 ;2250 ;423 ;HorzAlign=Left;
SourceExpr="Item No." }
{ 34 ;TextBox ;3000 ;0 ;4500 ;423 ;CaptionML=[ENU=Item Description;
ESM=Descripci¢n producto;
FRC=Description d'article;
ENC=Item Description];
SourceExpr=Item.Description }
{ 35 ;TextBox ;7650 ;0 ;1050 ;423 ;CaptionML=[ENU=Quantity;
ESM=Cantidad;
FRC=Quantit‚;
ENC=Quantity];
SourceExpr=-"Invoiced Quantity" }
{ 36 ;TextBox ;8850 ;0 ;1500 ;423 ;CaptionML=[ENU=Unit;
ESM=Unidad;
FRC=Unit‚;
ENC=Unit];
SourceExpr=Item."Base Unit of Measure" }
{ 38 ;TextBox ;12450;0 ;1800 ;423 ;CaptionML=[ENU=Contribution Margin;
ESM=Margen contribuci¢n;
FRC=Marge contribution;
ENC=Contribution Margin];
SourceExpr=Profit }
{ 39 ;TextBox ;14400;0 ;1800 ;423 ;CaptionML=[ENU=Discount;
ESM=Descuento;
FRC=Escompte;
ENC=Discount];
SourceExpr="Discount Amount" }
{ 40 ;TextBox ;16350;0 ;900 ;423 ;HorzAlign=Right;
CaptionML=[ENU=Contrib Ratio;
ESM=Ratio contrib.;
FRC=Ratio contribution;
ENC=Contrib Ratio];
DecimalPlaces=1:1;
SourceExpr="Profit%" }
{ 37 ;TextBox ;10500;0 ;1800 ;423 ;CaptionML=[ENU=Amount;
ESM=Importe;
FRC=Montant;
ENC=Amount];
SourceExpr="Sales Amount (Actual)" }
}
}
{ PROPERTIES
{
SectionType=Footer;
SectionWidth=18150;
SectionHeight=1692;
OnPreSection=BEGIN
"CalculateProfit%";
IF PrintToExcel THEN
CurrReport.SHOWOUTPUT(FALSE);
END;
}
CONTROLS
{
{ 41 ;TextBox ;0 ;423 ;1950 ;423 ;HorzAlign=Left;
SourceExpr=Customer."No." }
{ 46 ;Label ;2700 ;423 ;4800 ;423 ;CaptionML=[ENU=Customer Total;
ESM=Total cliente;
FRC=Total du client;
ENC=Customer Total] }
{ 42 ;TextBox ;10500;423 ;1800 ;423 ;SourceExpr="Sales Amount (Actual)" }
{ 43 ;TextBox ;12450;423 ;1800 ;423 ;SourceExpr=Profit }
{ 44 ;TextBox ;14400;423 ;1800 ;423 ;SourceExpr="Discount Amount" }
{ 45 ;TextBox ;16350;423 ;900 ;423 ;HorzAlign=Right;
DecimalPlaces=1:1;
SourceExpr="Profit%" }
}
}
}
}
}
REQUESTFORM
{
PROPERTIES
{
Width=7590;
Height=2750;
SaveValues=Yes;
}
CONTROLS
{
{ 2 ;CheckBox ;3410 ;0 ;330 ;440 ;ShowCaption=No;
SourceExpr=OnlyOnePerPage }
{ 3 ;Label ;0 ;0 ;3300 ;440 ;ParentControl=2;
CaptionML=[ENU=New Page per Account;
ESM=Nueva p g. por cuenta;
FRC=Nouvelle page par compte;
ENC=New Page per Account] }
{ 1480000;CheckBox ;3410 ;550 ;440 ;440 ;ShowCaption=No;
CaptionML=ENC=Print to Excel;
SourceExpr=PrintToExcel }
{ 1480001;Label ;0 ;550 ;3300 ;440 ;ParentControl=1480000 }
{ 1000000000;Label ;0 ;1100 ;3300 ;440 ;CaptionML=ENU=Item Category Code }
{ 1000000001;TextBox;3410 ;1100 ;3740 ;440 ;DropDown=No;
BlankZero=Yes;
SourceExpr=ItemCategoryCode;
TableRelation="Item Category".Code }
}
}
CODE
{
VAR
ExcelBuf@1480000 : TEMPORARY Record 370;
FilterString@1000 : Text[250];
FilterString2@1001 : Text[250];
PeriodText@1002 : Text[30];
Profit@1003 : Decimal;
"Profit%"@1004 : Decimal;
OnlyOnePerPage@1005 : Boolean;
Item@1006 : Record 27;
CompanyInformation@1007 : Record 79;
PrintToExcel@1480001 : Boolean;
Text000@1480002 : TextConst 'ENU=Invalid Item;ESM=Producto no v lido;FRC=Article non valide;ENC=Invalid Item';
Text001@1480013 : TextConst 'ENU=Data;ESM=Datos;FRC=Donn‚es;ENC=Data';
Text002@1480012 : TextConst 'ENU=Customer/Item Statistics;ESM=Estad. cliente/producto;FRC=Statistiques client/article;ENC=Customer/Item Statistics';
Text003@1480011 : TextConst 'ENU=Company Name;ESM=Nombre empresa;FRC=Nom de compagnie;ENC=Company Name';
Text004@1480010 : TextConst 'ENU=Report No.;ESM=N§ informe;FRC=Nø rapport;ENC=Report No.';
Text005@1480009 : TextConst 'ENU=Report Name;ESM=Nombre informe;FRC=Nom de rapport;ENC=Report Name';
Text006@1480008 : TextConst 'ENU=User ID;ESM=Id. usuario;FRC=Code utilisateur;ENC=User ID';
Text007@1480007 : TextConst 'ENU=Date / Time;ESM=Fecha / Hora;FRC=Date / Heure;ENC=Date / Time';
Text008@1480006 : TextConst 'ENU=Customer Filters;ESM=Filtros cliente;FRC=Filtres client;ENC=Customer Filters';
Text009@1480005 : TextConst 'ENU=Value Entry Filters;ESM=Filtros mov. valor;FRC=Filtres d''‚criture valeur;ENC=Value Entry Filters';
Text010@1480004 : TextConst 'ENU=Contribution Margin;ESM=Margen contribuci¢n;FRC=Marge contribution;ENC=Contribution Margin';
Text011@1480003 : TextConst 'ENU=Contribution Ratio;ESM=Ratio contribuci¢n;FRC=Ratio contribution;ENC=Contribution Ratio';
ItemCategoryCode@1000000000 : Text[250];
PROCEDURE "CalculateProfit%"@1();
BEGIN
IF "Value Entry"."Sales Amount (Actual)" <> 0 THEN
"Profit%" := ROUND(100 * Profit / "Value Entry"."Sales Amount (Actual)",0.1)
ELSE
"Profit%" := 0;
END;
LOCAL PROCEDURE MakeExcelInfo@5();
BEGIN
ExcelBuf.SetUseInfoSheed;
ExcelBuf.AddInfoColumn(FORMAT(Text003),FALSE,'',TRUE,FALSE,FALSE,'');
ExcelBuf.AddInfoColumn(CompanyInformation.Name,FALSE,'',FALSE,FALSE,FALSE,'');
ExcelBuf.NewRow;
ExcelBuf.AddInfoColumn(FORMAT(Text005),FALSE,'',TRUE,FALSE,FALSE,'');
ExcelBuf.AddInfoColumn(FORMAT(Text002),FALSE,'',FALSE,FALSE,FALSE,'');
ExcelBuf.NewRow;
ExcelBuf.AddInfoColumn(FORMAT(Text004),FALSE,'',TRUE,FALSE,FALSE,'');
ExcelBuf.AddInfoColumn(REPORT::"Customer/Item Statistics",FALSE,'',FALSE,FALSE,FALSE,'');
ExcelBuf.NewRow;
ExcelBuf.AddInfoColumn(FORMAT(Text006),FALSE,'',TRUE,FALSE,FALSE,'');
ExcelBuf.AddInfoColumn(USERID,FALSE,'',FALSE,FALSE,FALSE,'');
ExcelBuf.NewRow;
ExcelBuf.AddInfoColumn(FORMAT(Text007),FALSE,'',TRUE,FALSE,FALSE,'');
ExcelBuf.AddInfoColumn(TODAY,FALSE,'',FALSE,FALSE,FALSE,'');
ExcelBuf.AddInfoColumn(TIME,FALSE,'',FALSE,FALSE,FALSE,'');
ExcelBuf.NewRow;
ExcelBuf.AddInfoColumn(FORMAT(Text008),FALSE,'',TRUE,FALSE,FALSE,'');
ExcelBuf.AddInfoColumn(FilterString,FALSE,'',FALSE,FALSE,FALSE,'');
ExcelBuf.NewRow;
ExcelBuf.AddInfoColumn(FORMAT(Text009),FALSE,'',TRUE,FALSE,FALSE,'');
ExcelBuf.AddInfoColumn(FilterString2,FALSE,'',FALSE,FALSE,FALSE,'');
ExcelBuf.ClearNewRow;
MakeExcelDataHeader;
END;
LOCAL PROCEDURE MakeExcelDataHeader@3();
BEGIN
ExcelBuf.NewRow;
ExcelBuf.AddColumn(Customer.FIELDCAPTION("No."),FALSE,'',TRUE,FALSE,TRUE,'@');
ExcelBuf.AddColumn(Customer.FIELDCAPTION(Name),FALSE,'',TRUE,FALSE,TRUE,'');
ExcelBuf.AddColumn("Value Entry".FIELDCAPTION("Item No."),FALSE,'',TRUE,FALSE,TRUE,'@');
ExcelBuf.AddColumn(Item.FIELDCAPTION(Description),FALSE,'',TRUE,FALSE,TRUE,'');
ExcelBuf.AddColumn("Value Entry".FIELDCAPTION("Invoiced Quantity"),FALSE,'',TRUE,FALSE,TRUE,'');
ExcelBuf.AddColumn(Item.FIELDCAPTION("Base Unit of Measure"),FALSE,'',TRUE,FALSE,TRUE,'');
ExcelBuf.AddColumn("Value Entry".FIELDCAPTION("Sales Amount (Actual)"),FALSE,'',TRUE,FALSE,TRUE,'');
ExcelBuf.AddColumn(FORMAT(Text010),FALSE,'',TRUE,FALSE,TRUE,'');
ExcelBuf.AddColumn(FORMAT(Text011),FALSE,'',TRUE,FALSE,TRUE,'');
ExcelBuf.AddColumn("Value Entry".FIELDCAPTION("Discount Amount"),FALSE,'',TRUE,FALSE,TRUE,'');
END;
LOCAL PROCEDURE MakeExcelDataBody@4();
BEGIN
ExcelBuf.NewRow;
ExcelBuf.AddColumn(Customer."No.",FALSE,'',FALSE,FALSE,FALSE,'');
ExcelBuf.AddColumn(Customer.Name,FALSE,'',FALSE,FALSE,FALSE,'');
ExcelBuf.AddColumn("Value Entry"."Item No.",FALSE,'',FALSE,FALSE,FALSE,'');
ExcelBuf.AddColumn(Item.Description,FALSE,'',FALSE,FALSE,FALSE,'');
ExcelBuf.AddColumn(-"Value Entry"."Invoiced Quantity",FALSE,'',FALSE,FALSE,FALSE,'');
ExcelBuf.AddColumn(Item."Base Unit of Measure",FALSE,'',FALSE,FALSE,FALSE,'');
ExcelBuf.AddColumn("Value Entry"."Sales Amount (Actual)",FALSE,'',FALSE,FALSE,FALSE,'#,##0.00');
ExcelBuf.AddColumn(Profit,FALSE,'',FALSE,FALSE,FALSE,'#,##0.00');
ExcelBuf.AddColumn("Profit%"/100,FALSE,'',FALSE,FALSE,FALSE,'0.0%');
ExcelBuf.AddColumn("Value Entry"."Discount Amount",FALSE,'',FALSE,FALSE,FALSE,'#,##0.00');
END;
LOCAL PROCEDURE CreateExcelbook@2();
BEGIN
ExcelBuf.CreateBook;
ExcelBuf.CreateSheet(Text001,Text002,COMPANYNAME,USERID);
ExcelBuf.GiveUserControl;
ERROR('');
END;
BEGIN
END.
}
}
Anybody have any ideas??
Half-empy or half-full how do you view your database?
Thanks.
Thanks.
0
Answers
Thanks.
1 way you could do it would be to pass a filter for the item category code via the Report Request Form (options tab). you could then have a dataitem for customer, and OnAfterGetRecord, you could loop through items that meet your filter, retrieving the ILE or VEs for that customer and that item and summing the sales. Then use an Array like in the stock report you are looking at, where you are storing the customer no. and the sales amount and sorting based on the sales. You will have to create some new code, but you will also be able to "copy and paste" some code from your existing report to get you started - think of it as reverse engineering what you have to create what you want.
Thank you. I know my thoughts were in the right track. I will save Report 10062 Top __ Customers List as 50062 and modify that as my starting point.
I guess I would have to insert a dataitem somewhere and then add the appropriate code to different triggers and report sections.
Then again - the other report I have already filters by Item Category Code in the options tab and displays Total Sales for each customer for Item Category Code if I could only modify that one to put the customers and their totals into a temporary table then sort it ASCENDING by Sales Total and have the report show user specified ## of results.
I know this is fundamentally a "no brainer" for some people with many years of Navision experience.
Any thoughts. I would be grateful and wouldn't mind sending a small Christmas or Holiday gift for the solution.
Thanks.
The problem you are facing is this the top customer report is using a field "Sales (LCY)" which is a flowfield. This cannot be filtered down by item category. So the way I see it you have two options.
1. Calculate the report using a record variable
2. add a dataitem to calculate/ print the value.
The easier concept is to use the variable.
create a record variable for value entry table.
use code like (off the top my my head so might have compile errors) in the customer dataitem
customertotal := 0; valueentry.reset; valueentry.setrange( "item ledger entry type", valueentry."item ledger entry type"::sale ); valueentry.setfilter( "posting date", yourdatefilter ); valueentry.setrange( "source no.", customer."no." ); if valueentry.findset then begin repeat if item.get( "valueentry."item no." ) then if item."item category code" = ItemCategoryCode then customertotal += valueentry."Sales Amount (Actual)" until valueentry.next = 0; end;Give this a try and let us know how you get on.
Dynamics Nav Add-ons
http://www.simplydynamics.ie/Addons.html
So the code has to filter down to each Customer No. entry in Value Entry table then check each Item in Value Entry table against Item.No from Item table to see if the Item belongs to Item Category Code we selected.
I am desparate to get this report working quickly. Can anyone lend a hand this holiday season?
Thanks.
Posted a reply last night but server crashed at the time :evil:
Anyway:
If performance is an issue then you could look at using the Item Ledger table and mostly the same code. Here you can filter on item category code as it is on the tables. You might consider adding a key or if CALCSUMS can be used.
Also it might be no harm to take a step backwards and see if something like analysis view are of benfit.
Dynamics Nav Add-ons
http://www.simplydynamics.ie/Addons.html
I assume I just need to create a report with DataItems of Value Entry and Customer or just Value Entry and then lookup additional customer information through a Customer record variable as needed?
I am trying it out now. I want to sort by ascending total sales so I guess I would need to base my report on a temporary table.
Thanks.
You wont need a dataitem for this until you need to print detail you can use a variable of type record. Don't forget the use CALCFIELDS for flowfields e.g. Sales Amount (Actual)
Dynamics Nav Add-ons
http://www.simplydynamics.ie/Addons.html
OBJECT Report 50062 Top __ Customer List Mod { OBJECT-PROPERTIES { Date=12/04/08; Time=[ 8:50:40 AM]; Modified=Yes; Version List=NAVNA4.00.03; } PROPERTIES { CaptionML=[ENU=Top __ Customer List; ESM=Listado __ mejores clientes; FRC=Liste des meilleurs __ clients; ENC=Top __ Customer List]; OnPreReport=BEGIN CompanyInformation.GET; IF CustomersToRank = 0 THEN // default CustomersToRank := 20; IF TopType = TopType::"Balance ($)" THEN BEGIN SubTitle := Text001; ColHead := Text002; END ELSE BEGIN IF Customer.GETFILTER("Date Filter") = '' THEN SubTitle := Text003 ELSE SubTitle := Text004 + ' ' + Customer.GETFILTER("Date Filter") + ')'; ColHead := Text005; END; MainTitle := Text006 + ' '+ FORMAT(CustomersToRank) + Text007; { Temporarily remove date filter, since it will show in the header anyway } Customer.SETRANGE("Date Filter"); FilterString := Customer.GETFILTERS; END; } DATAITEMS { { PROPERTIES { DataItemTable=Table2000000026; DataItemTableView=SORTING(Number) WHERE(Number=CONST(1)); DataItemVarName=Heading; } SECTIONS { { PROPERTIES { SectionType=Header; PrintOnEveryPage=Yes; SectionWidth=18150; SectionHeight=1692; } CONTROLS { { 1001;TextBox ;0 ;0 ;7500 ;423 ;FontSize=8; FontBold=Yes; SourceExpr=MainTitle } { 1002;TextBox ;14850;0 ;2100 ;423 ;HorzAlign=Right; SourceExpr=FORMAT(TODAY,0,4) } { 1003;TextBox ;17100;0 ;1050 ;423 ;HorzAlign=Right; SourceExpr=TIME; Format=<Hours12>:<Minutes,2> <AM/PM> } { 1004;TextBox ;0 ;423 ;7500 ;423 ;SourceExpr=CompanyInformation.Name } { 1005;Label ;16950;423 ;750 ;423 ;ParentControl=1006; HorzAlign=Right } { 1006;TextBox ;17700;423 ;450 ;423 ;CaptionML=[ENU=Page; ESM=P gina; FRC=Page; ENC=Page]; SourceExpr=CurrReport.PAGENO } { 1007;TextBox ;16650;846 ;1500 ;423 ;HorzAlign=Right; SourceExpr=USERID } { 1008;TextBox ;0 ;1269 ;7500 ;423 ;SourceExpr=SubTitle } } } { PROPERTIES { SectionType=Header; PrintOnEveryPage=Yes; SectionWidth=18150; SectionHeight=423; OnPreSection=BEGIN CurrReport.SHOWOUTPUT := (CurrReport.PAGENO = 1) AND (FilterString <> ''); END; } CONTROLS { { 10 ;TextBox ;0 ;0 ;18150;423 ;SourceExpr=FilterString } } } { PROPERTIES { SectionType=Header; PrintOnEveryPage=Yes; SectionWidth=18150; SectionHeight=1269; } CONTROLS { { 11 ;TextBox ;8250 ;0 ;1800 ;846 ;ParentControl=19; HorzAlign=Right; VertAlign=Bottom; FontBold=Yes; MultiLine=Yes; SourceExpr=ColHead } { 12 ;Label ;0 ;0 ;750 ;846 ;ParentControl=15; HorzAlign=Right; VertAlign=Bottom; FontBold=Yes; MultiLine=Yes } { 14 ;Label ;7200 ;0 ;900 ;846 ;ParentControl=18; HorzAlign=Right; VertAlign=Bottom; FontBold=Yes; MultiLine=Yes; CaptionML=[ENU=%; ESM=%; FRC=%; ENC=%] } { 1 ;Label ;900 ;0 ;1800 ;846 ;ParentControl=16; VertAlign=Bottom; FontBold=Yes; MultiLine=Yes } { 13 ;Label ;2850 ;0 ;4200 ;846 ;ParentControl=17; VertAlign=Bottom; FontBold=Yes; MultiLine=Yes; CaptionML=[ENU=Name; ESM=Nombre; FRC=Nom; ENC=Name] } } } } } { PROPERTIES { DataItemIndent=1; DataItemTable=Table32; OnPreDataItem=BEGIN NextTopLineNo := 1; //Window.OPEN(Text000 + ' #1########'); END; OnAfterGetRecord=BEGIN //Window.UPDATE(1,Customer."No."); IF TopType = TopType::"Balance ($)" THEN BEGIN //CALCFIELDS("Balance on Date (LCY)"); //TempAmount := "Balance on Date (LCY)"; END ELSE BEGIN yourdatefilter := '01/05/08..01/06/08'; ItemCategoryCode := 'COSTUMES'; customertotal := 0; ItemLedgerEntry.RESET; ItemLedgerEntry.SETRANGE( "Entry Type", ItemLedgerEntry."Entry Type"::Sale ); ItemLedgerEntry.SETFILTER( "Posting Date", yourdatefilter ); ItemLedgerEntry.SETRANGE( "Source No.", Customer."No." ); IF ItemLedgerEntry.FINDSET THEN BEGIN REPEAT IF item.GET(ItemLedgerEntry."Item No." ) THEN IF item."Item Category Code" = ItemCategoryCode THEN customertotal += ItemLedgerEntry."Sales Amount (Actual)" UNTIL ItemLedgerEntry.NEXT = 0; END; TempAmount := customertotal; // CALCFIELDS("Sales (LCY)"); // TempAmount := "Sales (LCY)"; END; GrandTotal := GrandTotal + TempAmount; TopNo[NextTopLineNo] := Customer."No."; TopAmount[NextTopLineNo] := TempAmount; TopName[NextTopLineNo] := Customer.Name; i := NextTopLineNo; IF NextTopLineNo < (CustomersToRank + 1) THEN NextTopLineNo := NextTopLineNo + 1; WHILE (i > 1) DO BEGIN i := i - 1; IF (TopAmount[i + 1] > TopAmount[i]) THEN BEGIN // Sort the Customers by amount, largest should be first, smallest last. Put // values from position i into save variables, move values from position // i+1 to position i then put save values back in array in position i+1. TempNo := TopNo[i]; TempAmount := TopAmount[i]; TempName := TopName[i]; TopNo[i] := TopNo[i + 1]; TopAmount[i] := TopAmount[i + 1]; TopName[i] := TopName[i + 1]; TopNo[i + 1] := TempNo; TopAmount[i + 1] := TempAmount; TopName[i + 1] := TempName; END; END; END; ReqFilterFields=Posting Date,Item Category Code; } SECTIONS { } } { PROPERTIES { DataItemIndent=1; DataItemTable=Table2000000026; DataItemTableView=SORTING(Number); MaxIteration=99; DataItemVarName=Print Loop; OnPreDataItem=BEGIN Window.CLOSE; i := 0; END; OnAfterGetRecord=BEGIN i := i + 1; IF i = NextTopLineNo THEN CurrReport.BREAK; TopTotal := TopTotal + TopAmount[i]; IF (TopAmount[1] > 0) AND (TopAmount[i] > 0) THEN BarText := ParagraphHandling.PadStrProportional('',ROUND(TopAmount[i] / TopAmount[1] * 61,1),7,'|') ELSE BarText := ''; IF GrandTotal <> 0 THEN "Top%" := ROUND(TopAmount[i] / GrandTotal * 100,0.1) ELSE "Top%" := 0; END; } SECTIONS { { PROPERTIES { SectionType=Body; SectionWidth=18150; SectionHeight=423; } CONTROLS { { 15 ;TextBox ;0 ;0 ;750 ;423 ;CaptionML=[ENU=Rank; ESM=Posici¢n; FRC=Classe; ENC=Rank]; SourceExpr=i } { 16 ;TextBox ;900 ;0 ;1800 ;423 ;HorzAlign=Left; CaptionML=[ENU=Customer; ESM=Cliente; FRC=Client; ENC=Customer]; SourceExpr=TopNo[i] } { 18 ;TextBox ;7200 ;0 ;900 ;423 ;DecimalPlaces=1:1; SourceExpr="Top%" } { 19 ;TextBox ;8250 ;0 ;1800 ;423 ;SourceExpr=TopAmount[i] } { 20 ;TextBox ;10200;0 ;7950 ;423 ;SourceExpr=BarText } { 17 ;TextBox ;2850 ;0 ;4200 ;423 ;SourceExpr=TopName[i] } } } { PROPERTIES { SectionType=Footer; SectionWidth=18150; SectionHeight=846; OnPreSection=BEGIN IF TopType = TopType::"Balance ($)" THEN TopTotalText := 'Amount Outstanding' ELSE TopTotalText := 'Sales'; IF GrandTotal <> 0 THEN "Top%" := ROUND(TopTotal / GrandTotal * 100,0.1) ELSE "Top%" := 0; END; } CONTROLS { { 21 ;TextBox ;2850 ;0 ;4200 ;423 ;HorzAlign=General; SourceExpr=STRSUBSTNO('Top %1 Total %2',CustomersToRank,TopTotalText) } { 23 ;TextBox ;7200 ;0 ;900 ;423 ;DecimalPlaces=1:1; SourceExpr="Top%" } { 24 ;TextBox ;8250 ;0 ;1800 ;423 ;SourceExpr=TopTotal } } } { PROPERTIES { SectionType=Footer; SectionWidth=18150; SectionHeight=1269; OnPreSection=BEGIN CurrReport.SHOWOUTPUT := (GrandTotal <> TopTotal); END; } CONTROLS { { 27 ;TextBox ;7200 ;0 ;900 ;423 ;DecimalPlaces=1:1; SourceExpr=100.0 - "Top%" } { 28 ;TextBox ;8250 ;0 ;1800 ;423 ;SourceExpr=GrandTotal - TopTotal } { 29 ;Label ;2850 ;0 ;4200 ;423 ;ParentControl=27; CaptionML=[ENU=All other customers; ESM=Otros clientes; FRC=Tous les autres clients; ENC=All other customers] } { 30 ;TextBox ;2850 ;846 ;4200 ;423 ;HorzAlign=Left; SourceExpr='Total ' + TopTotalText } { 31 ;TextBox ;8250 ;846 ;1800 ;423 ;SourceExpr=GrandTotal } { 33 ;Label ;7200 ;846 ;900 ;423 ;HorzAlign=Right; CaptionML=[ENU=100.0; ESM=100,0; FRC=100,0; ENC=100.0] } } } } } } REQUESTFORM { PROPERTIES { Width=7260; Height=1650; SaveValues=Yes; } CONTROLS { { 1 ;OptionButton ;3410 ;0 ;3850 ;440 ;CaptionML=[ENU=Sales ($); ESM=Ventas ($); FRC=Ventes ($); ENC=Sales ($)]; SourceExpr=TopType; OptionValue=Sales ($) } { 3 ;Label ;0 ;0 ;3300 ;440 ;ParentControl=1; CaptionML=[ENU=Show; ESM=Mostrar; FRC=Afficher; ENC=Show] } { 5 ;OptionButton ;3410 ;550 ;3850 ;440 ;CaptionML=[ENU=Balance ($); ESM=Saldo ($); FRC=Solde ($); ENC=Balance ($)]; SourceExpr=TopType; OptionValue=Balance ($) } { 6 ;TextBox ;3410 ;1210 ;1650 ;440 ;CaptionML=[ENU=Quantity; ESM=Cantidad; FRC=Quantit‚; ENC=Quantity]; SourceExpr=CustomersToRank; OnValidate=BEGIN IF CustomersToRank > 99 THEN ERROR('Number of customers must be no greater than 99'); END; } { 7 ;Label ;0 ;1210 ;3300 ;440 ;ParentControl=6; CaptionML=[ENU=No. of Customers to Rank; ESM=N§ clientes para clasificar; FRC=Nbre clients … classifier; ENC=No. of Customers to Rank] } } } CODE { VAR FilterString@1000 : Text[250]; MainTitle@1001 : Text[88]; SubTitle@1002 : Text[132]; ColHead@1003 : Text[9]; TempName@1004 : Text[30]; TempNo@1005 : Text[40]; TopTotalText@1006 : Text[40]; BarText@1007 : Text[250]; TopName@1008 : ARRAY [100] OF Text[30]; "Top%"@1009 : Decimal; TempAmount@1010 : Decimal; GrandTotal@1011 : Decimal; TopAmount@1012 : ARRAY [100] OF Decimal; TopTotal@1013 : Decimal; i@1014 : Integer; NextTopLineNo@1015 : Integer; CustomersToRank@1016 : Integer; TopType@1017 : 'Sales ($),Balance ($)'; TopNo@1018 : ARRAY [100] OF Code[20]; CompanyInformation@1019 : Record 79; Window@1020 : Dialog; ParagraphHandling@1021 : Codeunit 10025; Text000@1022 : TextConst 'ENU="Going through customers ";ESM="Examinando clientes ";FRC="Clients en traitement ";ENC="Going through customers "'; Text001@1023 : TextConst 'ENU=(by Balance Due);ESM=(por saldo vencido);FRC=(par solde d–);ENC=(by Balance Due)'; Text002@1024 : TextConst 'ENU=Balances;ESM=Saldos;FRC=Soldes;ENC=Balances'; Text003@1025 : TextConst 'ENU=(by Total Sales);ESM=(por vtas. totales);FRC=(par ventes totales);ENC=(by Total Sales)'; Text004@1026 : TextConst 'ENU=(by Sales During the Period;ESM=(por vtas. durante el per¡odo;FRC=(par ventes pendant la p‚riode;ENC=(by Sales During the Period'; Text005@1027 : TextConst 'ENU=Sales;ESM=Ventas;FRC=Ventes;ENC=Sales'; Text006@1028 : TextConst 'ENU=Top;ESM=Superior;FRC=Haut;ENC=Top'; Text007@1029 : TextConst 'ENU=" Customers";ESM=" Clientes";FRC=" Clients";ENC=" Customers"'; customertotal@1000000000 : Integer; ValueEntry@1000000001 : Record 5802; yourdatefilter@1000000002 : Text[40]; item@1000000003 : Record 27; ItemCategoryCode@1000000004 : Text[30]; ItemLedgerEntry@1000000005 : Record 32; Customer@1000000006 : Record 18; BEGIN END. } }I obviously set some variables that I would be getting from the Options or Request Form later. Am I doing this right? You're probably shaking your head right now?
Thanks.
From an initial look this seems to be a problem
ItemLedgerEntry.RESET; ItemLedgerEntry.SETRANGE( "Entry Type", ItemLedgerEntry."Entry Type"::Sale ); ItemLedgerEntry.SETFILTER( "Posting Date", yourdatefilter ); ItemLedgerEntry.SETRANGE( "Source No.", Customer."No." ); IF ItemLedgerEntry.FINDSET THEN BEGIN REPEAT IF item.GET(ItemLedgerEntry."Item No." ) THEN IF item."Item Category Code" = ItemCategoryCode THEN customertotal += ItemLedgerEntry."Sales Amount (Actual)" UNTIL ItemLedgerEntry.NEXT = 0; END;should be
ItemLedgerEntry.RESET; ItemLedgerEntry.SETRANGE( "Entry Type", ItemLedgerEntry."Entry Type"::Sale ); ItemLedgerEntry.SETFILTER( "Posting Date", yourdatefilter ); ItemLedgerEntry.SETRANGE( "Source No.", Customer."No." ); ItemLedgerEntry.SETRANGE( "Item Category Code", ItemCategoryCode ); IF ItemLedgerEntry.FINDSET THEN BEGIN REPEAT customertotal += ItemLedgerEntry."Sales Amount (Actual)" UNTIL ItemLedgerEntry.NEXT = 0; END;Dynamics Nav Add-ons
http://www.simplydynamics.ie/Addons.html
What can I do to get my results more efficiently and quickly?
Thanks.
You might consider adding a key for Item Category Code, Source No., posting date, entry type
Dynamics Nav Add-ons
http://www.simplydynamics.ie/Addons.html
Just a question, in the code segments discussed should some of the code go into OnPreDataItem() instead of OnAfterGetRecord() possibly like this or not:
OnPreDataItem()
OnAfterGetRecord()
customertotal := 0; IF ItemLedgerEntry.FINDSET THEN BEGIN REPEAT customertotal += ItemLedgerEntry."Sales Amount (Actual)" UNTIL ItemLedgerEntry.NEXT = 0; END; TempAmount := customertotal; GrandTotal := GrandTotal + TempAmount; TopNo[NextTopLineNo] := Customer."No."; TopAmount[NextTopLineNo] := TempAmount; TopName[NextTopLineNo] := Customer.Name; i := NextTopLineNo; IF NextTopLineNo < (CustomersToRank + 1) THEN NextTopLineNo := NextTopLineNo + 1; WHILE (i > 1) DO BEGIN i := i - 1; IF (TopAmount[i + 1] > TopAmount[i]) THEN BEGIN // Sort the Customers by amount, largest should be first, smallest last. Put // values from position i into save variables, move values from position // i+1 to position i then put save values back in array in position i+1. TempNo := TopNo[i]; TempAmount := TopAmount[i]; TempName := TopName[i]; TopNo[i] := TopNo[i + 1]; TopAmount[i] := TopAmount[i + 1]; TopName[i] := TopName[i + 1]; TopNo[i + 1] := TempNo; TopAmount[i + 1] := TempAmount; TopName[i + 1] := TempName; END; END;I was reading through some of my Navision manuals and they recommended using SETRANGE,SETFILER, etc.. in OnPreDataItem() triggers.
Am I wrong or maybe it doesn't matter in this case?
Thanks.
GOOD = The report finishes in about 2 minutes.
BAD = It shows the top 2 as I set the filter for, but the customer name and totals are blank and 0.00
Here is the Report Code:
OBJECT Report 50062 Top __ Customer List Mod { OBJECT-PROPERTIES { Date=12/04/08; Time=12:49:14 PM; Modified=Yes; Version List=NAVNA4.00.03; } PROPERTIES { CaptionML=[ENU=Top __ Customer List; ESM=Listado __ mejores clientes; FRC=Liste des meilleurs __ clients; ENC=Top __ Customer List]; OnPreReport=BEGIN CompanyInformation.GET; IF CustomersToRank = 0 THEN // default CustomersToRank := 20; IF TopType = TopType::"Balance ($)" THEN BEGIN SubTitle := Text001; ColHead := Text002; END ELSE BEGIN IF Customer.GETFILTER("Date Filter") = '' THEN SubTitle := Text003 ELSE SubTitle := Text004 + ' ' + Customer.GETFILTER("Date Filter") + ')'; ColHead := Text005; END; MainTitle := Text006 + ' '+ FORMAT(CustomersToRank) + Text007; { Temporarily remove date filter, since it will show in the header anyway } Customer.SETRANGE("Date Filter"); FilterString := Customer.GETFILTERS; END; } DATAITEMS { { PROPERTIES { DataItemTable=Table2000000026; DataItemTableView=SORTING(Number) WHERE(Number=CONST(1)); DataItemVarName=Heading; } SECTIONS { { PROPERTIES { SectionType=Header; PrintOnEveryPage=Yes; SectionWidth=18150; SectionHeight=1692; } CONTROLS { { 1001;TextBox ;0 ;0 ;7500 ;423 ;FontSize=8; FontBold=Yes; SourceExpr=MainTitle } { 1002;TextBox ;14850;0 ;2100 ;423 ;HorzAlign=Right; SourceExpr=FORMAT(TODAY,0,4) } { 1003;TextBox ;17100;0 ;1050 ;423 ;HorzAlign=Right; SourceExpr=TIME; Format=<Hours12>:<Minutes,2> <AM/PM> } { 1004;TextBox ;0 ;423 ;7500 ;423 ;SourceExpr=CompanyInformation.Name } { 1005;Label ;16950;423 ;750 ;423 ;ParentControl=1006; HorzAlign=Right } { 1006;TextBox ;17700;423 ;450 ;423 ;CaptionML=[ENU=Page; ESM=P gina; FRC=Page; ENC=Page]; SourceExpr=CurrReport.PAGENO } { 1007;TextBox ;16650;846 ;1500 ;423 ;HorzAlign=Right; SourceExpr=USERID } { 1008;TextBox ;0 ;1269 ;7500 ;423 ;SourceExpr=SubTitle } } } { PROPERTIES { SectionType=Header; PrintOnEveryPage=Yes; SectionWidth=18150; SectionHeight=423; OnPreSection=BEGIN CurrReport.SHOWOUTPUT := (CurrReport.PAGENO = 1) AND (FilterString <> ''); END; } CONTROLS { { 10 ;TextBox ;0 ;0 ;18150;423 ;SourceExpr=FilterString } } } { PROPERTIES { SectionType=Header; PrintOnEveryPage=Yes; SectionWidth=18150; SectionHeight=1269; } CONTROLS { { 11 ;TextBox ;8250 ;0 ;1800 ;846 ;ParentControl=19; HorzAlign=Right; VertAlign=Bottom; FontBold=Yes; MultiLine=Yes; SourceExpr=ColHead } { 12 ;Label ;0 ;0 ;750 ;846 ;ParentControl=15; HorzAlign=Right; VertAlign=Bottom; FontBold=Yes; MultiLine=Yes } { 14 ;Label ;7200 ;0 ;900 ;846 ;ParentControl=18; HorzAlign=Right; VertAlign=Bottom; FontBold=Yes; MultiLine=Yes; CaptionML=[ENU=%; ESM=%; FRC=%; ENC=%] } { 1 ;Label ;900 ;0 ;1800 ;846 ;ParentControl=16; VertAlign=Bottom; FontBold=Yes; MultiLine=Yes } { 13 ;Label ;2850 ;0 ;4200 ;846 ;ParentControl=17; VertAlign=Bottom; FontBold=Yes; MultiLine=Yes; CaptionML=[ENU=Name; ESM=Nombre; FRC=Nom; ENC=Name] } } } } } { PROPERTIES { DataItemIndent=1; DataItemTable=Table32; OnPreDataItem=BEGIN NextTopLineNo := 1; END; OnAfterGetRecord=BEGIN customertotal := 0; yourdatefilter := '04/23/08'; ItemCategoryCode := 'COSTUMES'; ItemLedgerEntry.RESET; ItemLedgerEntry.SETCURRENTKEY("Item Category Code","Source No.","Posting Date","Entry Type"); ItemLedgerEntry.SETRANGE( "Source No.", Customer."No." ); ItemLedgerEntry.SETRANGE( "Entry Type", ItemLedgerEntry."Entry Type"::Sale ); ItemLedgerEntry.SETFILTER( "Posting Date", yourdatefilter ); ItemLedgerEntry.SETRANGE( "Item Category Code", ItemCategoryCode ); IF ItemLedgerEntry.FINDSET THEN BEGIN REPEAT customertotal += ItemLedgerEntry."Sales Amount (Actual)" UNTIL ItemLedgerEntry.NEXT = 0; END; TempAmount := customertotal; GrandTotal := GrandTotal + TempAmount; TopNo[NextTopLineNo] := Customer."No."; TopAmount[NextTopLineNo] := TempAmount; TopName[NextTopLineNo] := Customer.Name; i := NextTopLineNo; IF NextTopLineNo < (CustomersToRank + 1) THEN NextTopLineNo := NextTopLineNo + 1; WHILE (i > 1) DO BEGIN i := i - 1; IF (TopAmount[i + 1] > TopAmount[i]) THEN BEGIN // Sort the Customers by amount, largest should be first, smallest last. Put // values from position i into save variables, move values from position // i+1 to position i then put save values back in array in position i+1. TempNo := TopNo[i]; TempAmount := TopAmount[i]; TempName := TopName[i]; TopNo[i] := TopNo[i + 1]; TopAmount[i] := TopAmount[i + 1]; TopName[i] := TopName[i + 1]; TopNo[i + 1] := TempNo; TopAmount[i + 1] := TempAmount; TopName[i + 1] := TempName; END; END; END; ReqFilterFields=Posting Date,Item Category Code; } SECTIONS { } } { PROPERTIES { DataItemIndent=1; DataItemTable=Table2000000026; DataItemTableView=SORTING(Number); MaxIteration=99; DataItemVarName=Print Loop; OnPreDataItem=BEGIN //Window.CLOSE; i := 0; END; OnAfterGetRecord=BEGIN i := i + 1; IF i = NextTopLineNo THEN CurrReport.BREAK; TopTotal := TopTotal + TopAmount[i]; IF (TopAmount[1] > 0) AND (TopAmount[i] > 0) THEN BarText := ParagraphHandling.PadStrProportional('',ROUND(TopAmount[i] / TopAmount[1] * 61,1),7,'|') ELSE BarText := ''; IF GrandTotal <> 0 THEN "Top%" := ROUND(TopAmount[i] / GrandTotal * 100,0.1) ELSE "Top%" := 0; END; } SECTIONS { { PROPERTIES { SectionType=Body; SectionWidth=18150; SectionHeight=423; } CONTROLS { { 15 ;TextBox ;0 ;0 ;750 ;423 ;CaptionML=[ENU=Rank; ESM=Posici¢n; FRC=Classe; ENC=Rank]; SourceExpr=i } { 16 ;TextBox ;900 ;0 ;1800 ;423 ;HorzAlign=Left; CaptionML=[ENU=Customer; ESM=Cliente; FRC=Client; ENC=Customer]; SourceExpr=TopNo[i] } { 18 ;TextBox ;7200 ;0 ;900 ;423 ;DecimalPlaces=1:1; SourceExpr="Top%" } { 19 ;TextBox ;8250 ;0 ;1800 ;423 ;SourceExpr=TopAmount[i] } { 20 ;TextBox ;10200;0 ;7950 ;423 ;SourceExpr=BarText } { 17 ;TextBox ;2850 ;0 ;4200 ;423 ;SourceExpr=TopName[i] } } } { PROPERTIES { SectionType=Footer; SectionWidth=18150; SectionHeight=846; OnPreSection=BEGIN IF TopType = TopType::"Balance ($)" THEN TopTotalText := 'Amount Outstanding' ELSE TopTotalText := 'Sales'; IF GrandTotal <> 0 THEN "Top%" := ROUND(TopTotal / GrandTotal * 100,0.1) ELSE "Top%" := 0; END; } CONTROLS { { 21 ;TextBox ;2850 ;0 ;4200 ;423 ;HorzAlign=General; SourceExpr=STRSUBSTNO('Top %1 Total %2',CustomersToRank,TopTotalText) } { 23 ;TextBox ;7200 ;0 ;900 ;423 ;DecimalPlaces=1:1; SourceExpr="Top%" } { 24 ;TextBox ;8250 ;0 ;1800 ;423 ;SourceExpr=TopTotal } } } { PROPERTIES { SectionType=Footer; SectionWidth=18150; SectionHeight=1269; OnPreSection=BEGIN CurrReport.SHOWOUTPUT := (GrandTotal <> TopTotal); END; } CONTROLS { { 27 ;TextBox ;7200 ;0 ;900 ;423 ;DecimalPlaces=1:1; SourceExpr=100.0 - "Top%" } { 28 ;TextBox ;8250 ;0 ;1800 ;423 ;SourceExpr=GrandTotal - TopTotal } { 29 ;Label ;2850 ;0 ;4200 ;423 ;ParentControl=27; CaptionML=[ENU=All other customers; ESM=Otros clientes; FRC=Tous les autres clients; ENC=All other customers] } { 30 ;TextBox ;2850 ;846 ;4200 ;423 ;HorzAlign=Left; SourceExpr='Total ' + TopTotalText } { 31 ;TextBox ;8250 ;846 ;1800 ;423 ;SourceExpr=GrandTotal } { 33 ;Label ;7200 ;846 ;900 ;423 ;HorzAlign=Right; CaptionML=[ENU=100.0; ESM=100,0; FRC=100,0; ENC=100.0] } } } } } } REQUESTFORM { PROPERTIES { Width=7260; Height=1650; SaveValues=Yes; } CONTROLS { { 1 ;OptionButton ;3410 ;0 ;3850 ;440 ;CaptionML=[ENU=Sales ($); ESM=Ventas ($); FRC=Ventes ($); ENC=Sales ($)]; SourceExpr=TopType; OptionValue=Sales ($) } { 3 ;Label ;0 ;0 ;3300 ;440 ;ParentControl=1; CaptionML=[ENU=Show; ESM=Mostrar; FRC=Afficher; ENC=Show] } { 5 ;OptionButton ;3410 ;550 ;3850 ;440 ;CaptionML=[ENU=Balance ($); ESM=Saldo ($); FRC=Solde ($); ENC=Balance ($)]; SourceExpr=TopType; OptionValue=Balance ($) } { 6 ;TextBox ;3410 ;1210 ;1650 ;440 ;CaptionML=[ENU=Quantity; ESM=Cantidad; FRC=Quantit‚; ENC=Quantity]; SourceExpr=CustomersToRank; OnValidate=BEGIN IF CustomersToRank > 99 THEN ERROR('Number of customers must be no greater than 99'); END; } { 7 ;Label ;0 ;1210 ;3300 ;440 ;ParentControl=6; CaptionML=[ENU=No. of Customers to Rank; ESM=N§ clientes para clasificar; FRC=Nbre clients … classifier; ENC=No. of Customers to Rank] } } } CODE { VAR FilterString@1000 : Text[250]; MainTitle@1001 : Text[88]; SubTitle@1002 : Text[132]; ColHead@1003 : Text[9]; TempName@1004 : Text[30]; TempNo@1005 : Text[40]; TopTotalText@1006 : Text[40]; BarText@1007 : Text[250]; TopName@1008 : ARRAY [100] OF Text[30]; "Top%"@1009 : Decimal; TempAmount@1010 : Decimal; GrandTotal@1011 : Decimal; TopAmount@1012 : ARRAY [100] OF Decimal; TopTotal@1013 : Decimal; i@1014 : Integer; NextTopLineNo@1015 : Integer; CustomersToRank@1016 : Integer; TopType@1017 : 'Sales ($),Balance ($)'; TopNo@1018 : ARRAY [100] OF Code[20]; CompanyInformation@1019 : Record 79; Window@1020 : Dialog; ParagraphHandling@1021 : Codeunit 10025; Text000@1022 : TextConst 'ENU="Going through customers ";ESM="Examinando clientes ";FRC="Clients en traitement ";ENC="Going through customers "'; Text001@1023 : TextConst 'ENU=(by Balance Due);ESM=(por saldo vencido);FRC=(par solde d–);ENC=(by Balance Due)'; Text002@1024 : TextConst 'ENU=Balances;ESM=Saldos;FRC=Soldes;ENC=Balances'; Text003@1025 : TextConst 'ENU=(by Total Sales);ESM=(por vtas. totales);FRC=(par ventes totales);ENC=(by Total Sales)'; Text004@1026 : TextConst 'ENU=(by Sales During the Period;ESM=(por vtas. durante el per¡odo;FRC=(par ventes pendant la p‚riode;ENC=(by Sales During the Period'; Text005@1027 : TextConst 'ENU=Sales;ESM=Ventas;FRC=Ventes;ENC=Sales'; Text006@1028 : TextConst 'ENU=Top;ESM=Superior;FRC=Haut;ENC=Top'; Text007@1029 : TextConst 'ENU=" Customers";ESM=" Clientes";FRC=" Clients";ENC=" Customers"'; customertotal@1000000000 : Integer; ValueEntry@1000000001 : Record 5802; yourdatefilter@1000000002 : Text[40]; item@1000000003 : Record 27; ItemCategoryCode@1000000004 : Text[30]; ItemLedgerEntry@1000000005 : Record 32; Customer@1000000006 : Record 18; BEGIN END. } }Thanks.
It need to go in the OnAfterGetRecord trigger because you are filtering down on the customer no.
OK :oops: didn't follow my own advice - you need a ItemLedgerEntry.calcfields( "Sales Amount (Actual)" );
Also if you have added the key use the SETCURRENTKEY function on the ItemLedgerEntry i.e.
ItemLedgerEntry.SETCURRENTKEY( fields added...., , , );
Dynamics Nav Add-ons
http://www.simplydynamics.ie/Addons.html
ItemLedgerEntry.SETCURRENTKEY("Item Category Code","Source No.","Posting Date","Entry Type");
Where should ItemLedgerEntry.calcfields( "Sales Amount (Actual)" ); go.
Once the totals get in there I should be good to go.
Thanks.
Sorry should have said - to explain flowfield do not return a value be default (unless they are displayed/printed) so after the find i.e. after the REPEAT line.
Dynamics Nav Add-ons
http://www.simplydynamics.ie/Addons.html
Thanks.
i do not see where you are ever looping through customers. Your report goes straight to the first ILE it finds within the filters you set, and then you initiate another record set of the ILE. I do not see where you process through the customer recordset. :-k
i believe your dataitem should be customer, not ILE, and then loop through the ILE OnAfterGetRecord as you are doing. you would need to get rid of the global Customer variable you created...
if i have totally missed the boat here, watch your report through debugger and look at the customer totals variable value as it is calculated - you should see straight away the numbers being entered into your ranking array.
OBJECT Report 50065 Top __CustomerList ItemCatCode { OBJECT-PROPERTIES { Date=07/04/07; Time=[ 2:38:05 PM]; Modified=Yes; Version List=NAVNA4.00.03; } PROPERTIES { CaptionML=[ENU=Top __ Customer List; ESM=Listado __ mejores clientes; FRC=Liste des meilleurs __ clients; ENC=Top __ Customer List]; OnPreReport=BEGIN CompanyInformation.GET; IF CustomersToRank = 0 THEN // default CustomersToRank := 20; IF TopType = TopType::"Balance ($)" THEN BEGIN SubTitle := Text001; ColHead := Text002; END ELSE BEGIN IF Customer.GETFILTER("Date Filter") = '' THEN SubTitle := Text003 ELSE SubTitle := Text004 + ' ' + Customer.GETFILTER("Date Filter") + ')'; ColHead := Text005; END; MainTitle := Text006 + ' '+ FORMAT(CustomersToRank) + Text007; { Temporarily remove date filter, since it will show in the header anyway } Customer.SETRANGE("Date Filter"); FilterString := Customer.GETFILTERS; END; } DATAITEMS { { PROPERTIES { DataItemTable=Table2000000026; DataItemTableView=SORTING(Number) WHERE(Number=CONST(1)); DataItemVarName=Heading; } SECTIONS { { PROPERTIES { SectionType=Header; PrintOnEveryPage=Yes; SectionWidth=18150; SectionHeight=1692; } CONTROLS { { 1001;TextBox ;0 ;0 ;7500 ;423 ;FontSize=8; FontBold=Yes; SourceExpr=MainTitle } { 1002;TextBox ;14850;0 ;2100 ;423 ;HorzAlign=Right; SourceExpr=FORMAT(TODAY,0,4) } { 1003;TextBox ;17100;0 ;1050 ;423 ;HorzAlign=Right; SourceExpr=TIME; Format=<Hours12>:<Minutes,2> <AM/PM> } { 1004;TextBox ;0 ;423 ;7500 ;423 ;SourceExpr=CompanyInformation.Name } { 1005;Label ;16950;423 ;750 ;423 ;ParentControl=1006; HorzAlign=Right } { 1006;TextBox ;17700;423 ;450 ;423 ;CaptionML=[ENU=Page; ESM=P gina; FRC=Page; ENC=Page]; SourceExpr=CurrReport.PAGENO } { 1007;TextBox ;16650;846 ;1500 ;423 ;HorzAlign=Right; SourceExpr=USERID } { 1008;TextBox ;0 ;1269 ;7500 ;423 ;SourceExpr=SubTitle } } } { PROPERTIES { SectionType=Header; PrintOnEveryPage=Yes; SectionWidth=18150; SectionHeight=423; OnPreSection=BEGIN CurrReport.SHOWOUTPUT := (CurrReport.PAGENO = 1) AND (FilterString <> ''); END; } CONTROLS { { 10 ;TextBox ;0 ;0 ;18150;423 ;SourceExpr=FilterString } } } { PROPERTIES { SectionType=Header; PrintOnEveryPage=Yes; SectionWidth=18150; SectionHeight=1269; } CONTROLS { { 11 ;TextBox ;8250 ;0 ;1800 ;846 ;ParentControl=19; HorzAlign=Right; VertAlign=Bottom; FontBold=Yes; MultiLine=Yes; SourceExpr=ColHead } { 12 ;Label ;0 ;0 ;750 ;846 ;ParentControl=15; HorzAlign=Right; VertAlign=Bottom; FontBold=Yes; MultiLine=Yes } { 14 ;Label ;7200 ;0 ;900 ;846 ;ParentControl=18; HorzAlign=Right; VertAlign=Bottom; FontBold=Yes; MultiLine=Yes; CaptionML=[ENU=%; ESM=%; FRC=%; ENC=%] } { 1 ;Label ;900 ;0 ;1800 ;846 ;ParentControl=16; VertAlign=Bottom; FontBold=Yes; MultiLine=Yes } { 13 ;Label ;2850 ;0 ;4200 ;846 ;ParentControl=17; VertAlign=Bottom; FontBold=Yes; MultiLine=Yes; CaptionML=[ENU=Name; ESM=Nombre; FRC=Nom; ENC=Name] } } } } } { PROPERTIES { DataItemIndent=1; DataItemTable=Table18; DataItemTableView=SORTING(No.); OnPreDataItem=BEGIN NextTopLineNo := 1; Window.OPEN(Text000 + ' #1########'); END; OnAfterGetRecord=BEGIN Window.UPDATE(1,"No."); IF TopType = TopType::"Balance ($)" THEN BEGIN CALCFIELDS("Balance on Date (LCY)"); TempAmount := "Balance on Date (LCY)"; END ELSE BEGIN // CALCFIELDS("Sales (LCY)"); // TempAmount := "Sales (LCY)"; // ADDED THIS HERE - BEGIN yourdatefilter := '01/05/08'; ItemCategoryCode := 'COSTUMES'; // customertotal := 0; ItemLedgerEntry.RESET; ItemLedgerEntry.SETCURRENTKEY("Item Category Code","Source No.","Posting Date","Entry Type"); ItemLedgerEntry.SETRANGE( "Entry Type", ItemLedgerEntry."Entry Type"::Sale ); ItemLedgerEntry.SETFILTER( "Posting Date", yourdatefilter ); ItemLedgerEntry.SETRANGE( "Source No.", Customer."No." ); ItemLedgerEntry.SETRANGE( "Item Category Code", ItemCategoryCode ); IF ItemLedgerEntry.FINDSET THEN BEGIN REPEAT // ItemLedgerEntry.CALCFIELDS( "Sales Amount (Actual)" ); customertotal += ItemLedgerEntry."Sales Amount (Actual)" UNTIL ItemLedgerEntry.NEXT = 0; END; ItemLedgerEntry.CALCFIELDS( "Sales Amount (Actual)" ); TempAmount := ItemLedgerEntry."Sales Amount (Actual)"; // ADDED THIS HERE - END END; GrandTotal := GrandTotal + TempAmount; TopNo[NextTopLineNo] := "No."; TopAmount[NextTopLineNo] := TempAmount; TopName[NextTopLineNo] := Name; i := NextTopLineNo; IF NextTopLineNo < (CustomersToRank + 1) THEN NextTopLineNo := NextTopLineNo + 1; WHILE (i > 1) DO BEGIN i := i - 1; IF (TopAmount[i + 1] > TopAmount[i]) THEN BEGIN // Sort the Customers by amount, largest should be first, smallest last. Put // values from position i into save variables, move values from position // i+1 to position i then put save values back in array in position i+1. TempNo := TopNo[i]; TempAmount := TopAmount[i]; TempName := TopName[i]; TopNo[i] := TopNo[i + 1]; TopAmount[i] := TopAmount[i + 1]; TopName[i] := TopName[i + 1]; TopNo[i + 1] := TempNo; TopAmount[i + 1] := TempAmount; TopName[i + 1] := TempName; END; END; END; ReqFilterFields=No.,Customer Posting Group,Salesperson Code,Date Filter; } SECTIONS { } } { PROPERTIES { DataItemIndent=1; DataItemTable=Table2000000026; DataItemTableView=SORTING(Number); MaxIteration=99; DataItemVarName=Print Loop; OnPreDataItem=BEGIN Window.CLOSE; i := 0; END; OnAfterGetRecord=BEGIN i := i + 1; IF i = NextTopLineNo THEN CurrReport.BREAK; TopTotal := TopTotal + TopAmount[i]; IF (TopAmount[1] > 0) AND (TopAmount[i] > 0) THEN BarText := ParagraphHandling.PadStrProportional('',ROUND(TopAmount[i] / TopAmount[1] * 61,1),7,'|') ELSE BarText := ''; IF GrandTotal <> 0 THEN "Top%" := ROUND(TopAmount[i] / GrandTotal * 100,0.1) ELSE "Top%" := 0; END; } SECTIONS { { PROPERTIES { SectionType=Body; SectionWidth=18150; SectionHeight=423; } CONTROLS { { 15 ;TextBox ;0 ;0 ;750 ;423 ;CaptionML=[ENU=Rank; ESM=Posici¢n; FRC=Classe; ENC=Rank]; SourceExpr=i } { 16 ;TextBox ;900 ;0 ;1800 ;423 ;HorzAlign=Left; CaptionML=[ENU=Customer; ESM=Cliente; FRC=Client; ENC=Customer]; SourceExpr=TopNo[i] } { 18 ;TextBox ;7200 ;0 ;900 ;423 ;DecimalPlaces=1:1; SourceExpr="Top%" } { 19 ;TextBox ;8250 ;0 ;1800 ;423 ;SourceExpr=TopAmount[i] } { 20 ;TextBox ;10200;0 ;7950 ;423 ;SourceExpr=BarText } { 17 ;TextBox ;2850 ;0 ;4200 ;423 ;SourceExpr=TopName[i] } } } { PROPERTIES { SectionType=Footer; SectionWidth=18150; SectionHeight=846; OnPreSection=BEGIN IF TopType = TopType::"Balance ($)" THEN TopTotalText := 'Amount Outstanding' ELSE TopTotalText := 'Sales'; IF GrandTotal <> 0 THEN "Top%" := ROUND(TopTotal / GrandTotal * 100,0.1) ELSE "Top%" := 0; END; } CONTROLS { { 21 ;TextBox ;2850 ;0 ;4200 ;423 ;HorzAlign=General; SourceExpr=STRSUBSTNO('Top %1 Total %2',CustomersToRank,TopTotalText) } { 23 ;TextBox ;7200 ;0 ;900 ;423 ;DecimalPlaces=1:1; SourceExpr="Top%" } { 24 ;TextBox ;8250 ;0 ;1800 ;423 ;SourceExpr=TopTotal } } } { PROPERTIES { SectionType=Footer; SectionWidth=18150; SectionHeight=1269; OnPreSection=BEGIN CurrReport.SHOWOUTPUT := (GrandTotal <> TopTotal); END; } CONTROLS { { 27 ;TextBox ;7200 ;0 ;900 ;423 ;DecimalPlaces=1:1; SourceExpr=100.0 - "Top%" } { 28 ;TextBox ;8250 ;0 ;1800 ;423 ;SourceExpr=GrandTotal - TopTotal } { 29 ;Label ;2850 ;0 ;4200 ;423 ;ParentControl=27; CaptionML=[ENU=All other customers; ESM=Otros clientes; FRC=Tous les autres clients; ENC=All other customers] } { 30 ;TextBox ;2850 ;846 ;4200 ;423 ;HorzAlign=Left; SourceExpr='Total ' + TopTotalText } { 31 ;TextBox ;8250 ;846 ;1800 ;423 ;SourceExpr=GrandTotal } { 33 ;Label ;7200 ;846 ;900 ;423 ;HorzAlign=Right; CaptionML=[ENU=100.0; ESM=100,0; FRC=100,0; ENC=100.0] } } } } } } REQUESTFORM { PROPERTIES { Width=7260; Height=1650; SaveValues=Yes; } CONTROLS { { 1 ;OptionButton ;3410 ;0 ;3850 ;440 ;CaptionML=[ENU=Sales ($); ESM=Ventas ($); FRC=Ventes ($); ENC=Sales ($)]; SourceExpr=TopType; OptionValue=Sales ($) } { 3 ;Label ;0 ;0 ;3300 ;440 ;ParentControl=1; CaptionML=[ENU=Show; ESM=Mostrar; FRC=Afficher; ENC=Show] } { 5 ;OptionButton ;3410 ;550 ;3850 ;440 ;CaptionML=[ENU=Balance ($); ESM=Saldo ($); FRC=Solde ($); ENC=Balance ($)]; SourceExpr=TopType; OptionValue=Balance ($) } { 6 ;TextBox ;3410 ;1210 ;1650 ;440 ;CaptionML=[ENU=Quantity; ESM=Cantidad; FRC=Quantit‚; ENC=Quantity]; SourceExpr=CustomersToRank; OnValidate=BEGIN IF CustomersToRank > 99 THEN ERROR('Number of customers must be no greater than 99'); END; } { 7 ;Label ;0 ;1210 ;3300 ;440 ;ParentControl=6; CaptionML=[ENU=No. of Customers to Rank; ESM=N§ clientes para clasificar; FRC=Nbre clients … classifier; ENC=No. of Customers to Rank] } } } CODE { VAR FilterString@1000 : Text[250]; MainTitle@1001 : Text[88]; SubTitle@1002 : Text[132]; ColHead@1003 : Text[9]; TempName@1004 : Text[30]; TempNo@1005 : Text[40]; TopTotalText@1006 : Text[40]; BarText@1007 : Text[250]; TopName@1008 : ARRAY [100] OF Text[30]; "Top%"@1009 : Decimal; TempAmount@1010 : Decimal; GrandTotal@1011 : Decimal; TopAmount@1012 : ARRAY [100] OF Decimal; TopTotal@1013 : Decimal; i@1014 : Integer; NextTopLineNo@1015 : Integer; CustomersToRank@1016 : Integer; TopType@1017 : 'Sales ($),Balance ($)'; TopNo@1018 : ARRAY [100] OF Code[20]; CompanyInformation@1019 : Record 79; Window@1020 : Dialog; ParagraphHandling@1021 : Codeunit 10025; Text000@1022 : TextConst 'ENU="Going through customers ";ESM="Examinando clientes ";FRC="Clients en traitement ";ENC="Going through customers "'; Text001@1023 : TextConst 'ENU=(by Balance Due);ESM=(por saldo vencido);FRC=(par solde d–);ENC=(by Balance Due)'; Text002@1024 : TextConst 'ENU=Balances;ESM=Saldos;FRC=Soldes;ENC=Balances'; Text003@1025 : TextConst 'ENU=(by Total Sales);ESM=(por vtas. totales);FRC=(par ventes totales);ENC=(by Total Sales)'; Text004@1026 : TextConst 'ENU=(by Sales During the Period;ESM=(por vtas. durante el per¡odo;FRC=(par ventes pendant la p‚riode;ENC=(by Sales During the Period'; Text005@1027 : TextConst 'ENU=Sales;ESM=Ventas;FRC=Ventes;ENC=Sales'; Text006@1028 : TextConst 'ENU=Top;ESM=Superior;FRC=Haut;ENC=Top'; Text007@1029 : TextConst 'ENU=" Customers";ESM=" Clientes";FRC=" Clients";ENC=" Customers"'; yourdatefilter@1000000000 : Text[30]; ItemLedgerEntry@1000000001 : Record 32; ItemCategoryCode@1000000002 : Text[30]; customertotal@1000000003 : Integer; BEGIN END. } }The report runs and produces results but I believe that I may be using CALCFIELDS incorrectly.
Can someone take a look at this report and make a recommendation to have it working correctly if there is a problem?
I am desperately trying to make this work, I needed it to be working 5 days ago as my employer needed to have the report run before departure this Friday morning.
Thanks.
Thanks.
IF ItemLedgerEntry.FINDSET THEN BEGIN REPEAT // ItemLedgerEntry.CALCFIELDS( "Sales Amount (Actual)" ); customertotal += ItemLedgerEntry."Sales Amount (Actual)" UNTIL ItemLedgerEntry.NEXT = 0; END; ItemLedgerEntry.CALCFIELDS( "Sales Amount (Actual)" ); TempAmount := ItemLedgerEntry."Sales Amount (Actual)"; // ADDED THIS HERE - ENDtry this
IF ItemLedgerEntry.FINDSET THEN BEGIN REPEAT ItemLedgerEntry.CALCFIELDS( "Sales Amount (Actual)" ); customertotal += ItemLedgerEntry."Sales Amount (Actual)" UNTIL ItemLedgerEntry.NEXT = 0; END; // ADDED THIS HERE - ENDyour code has you calculating the ILE after you already looped through all the records within the filter.
also, if you keep with tempamount, you may want to be sure to zero it out.
@ jversusj
Great spot - I should have imported the file first but I hit compile errors on the IE/GB versions
@ emulsified
I think the correct code is
customertotal := 0; // Reset for each customer ItemLedgerEntry.RESET; ItemLedgerEntry.SETCURRENTKEY("Item Category Code","Source No.","Posting Date","Entry Type"); ItemLedgerEntry.SETRANGE( "Entry Type", ItemLedgerEntry."Entry Type"::Sale ); ItemLedgerEntry.SETFILTER( "Posting Date", yourdatefilter ); ItemLedgerEntry.SETRANGE( "Source No.", Customer."No." ); ItemLedgerEntry.SETRANGE( "Item Category Code", ItemCategoryCode ); IF ItemLedgerEntry.FINDSET THEN BEGIN REPEAT ItemLedgerEntry.CALCFIELDS( "Sales Amount (Actual)" ); // calculates the value for each ILE entry customertotal += ItemLedgerEntry."Sales Amount (Actual)" // Create a total for the customer UNTIL ItemLedgerEntry.NEXT = 0; END; //ItemLedgerEntry.CALCFIELDS( "Sales Amount (Actual)" ); - Not needed TempAmount := customertotal; // Needed for the code later on to get the top nnn customersA comment - although the code is fine, I would not normally have an integer loop for the heading but use a heading section on the customer dataitem instead.
Dynamics Nav Add-ons
http://www.simplydynamics.ie/Addons.html