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
- 297 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
Report based on Salesperson/Customer/Value Entry acting odd
emulsified
Member Posts: 139
I have a report with the following structure:
Salesperson
Customer
Value Entry
The report has always worked very well. Let me say that while running the report I specify the following criteria:
Salesperson TAB
Salesperson Code = AJ
Customer TAB
No. = C123456
Value Entry TAB
Posting Date = 03/01/12..03/31/12
Item No. = *@SALE*|A* (these are our sale items)
When I run the above the report is missing SALE items that appear on a Posted Sales Invoice posted on 03/21/12, not all of the items on that invoice are showing up on the report.
If I run the report for a shorter date range like 03/21/12 or 03/21/12..03/22/12 all of the SALE items that appear on the Posted Sales Invoice will also show on this report correctly.
If I run the report for the intended date range Posting Date = 03/01/12..03/31/12 and also specify the Document No. = 120321017 (Posted Invoice#) under the Value Entry TAB all of the items will show up like they're supposed to.
I have looked up the lines in the Item Ledger Entry and Value Entry tables just to verify they look normal and they are.
Now the only different thing that we did this month was back posting invoices using the WORK DATE setting in the Navision Clients. We took great care to make sure that Document Date, Posting Date, Order Date were the same when posting these invoices.
Is Navision having some sort of issue with applying it's filters? I'm not understanding this. Can anyone shed some light onto this. We are on the native database version 4.0 SP3.
Report:
Salesperson
Customer
Value Entry
The report has always worked very well. Let me say that while running the report I specify the following criteria:
Salesperson TAB
Salesperson Code = AJ
Customer TAB
No. = C123456
Value Entry TAB
Posting Date = 03/01/12..03/31/12
Item No. = *@SALE*|A* (these are our sale items)
When I run the above the report is missing SALE items that appear on a Posted Sales Invoice posted on 03/21/12, not all of the items on that invoice are showing up on the report.
If I run the report for a shorter date range like 03/21/12 or 03/21/12..03/22/12 all of the SALE items that appear on the Posted Sales Invoice will also show on this report correctly.
If I run the report for the intended date range Posting Date = 03/01/12..03/31/12 and also specify the Document No. = 120321017 (Posted Invoice#) under the Value Entry TAB all of the items will show up like they're supposed to.
I have looked up the lines in the Item Ledger Entry and Value Entry tables just to verify they look normal and they are.
Now the only different thing that we did this month was back posting invoices using the WORK DATE setting in the Navision Clients. We took great care to make sure that Document Date, Posting Date, Order Date were the same when posting these invoices.
Is Navision having some sort of issue with applying it's filters? I'm not understanding this. Can anyone shed some light onto this. We are on the native database version 4.0 SP3.
Report:
OBJECT Report 10049 Cust./Item Stat. by Salespers.
{
OBJECT-PROPERTIES
{
Date=07/07/10;
Time=[ 3:36:51 PM];
Modified=Yes;
Version List=NAVUS3.70;
}
PROPERTIES
{
CaptionML=[ENU=Cust./Item Stat. by Salespers.;
ESM=Estad. cliente/prod. por vendedor;
FRC=Statistiques articles/client par repr‚sentant;
ENC=Cust./Item Stat. by Salespers.];
TopMargin=200;
BottomMargin=200;
LeftMargin=200;
OnPreReport=BEGIN
CompanyInformation.GET;
FilterString := "Salesperson/Purchaser".GETFILTERS;
FilterString2 := Customer.GETFILTERS;
FilterString3 := "Value Entry".GETFILTERS;
CASE SalespersonToUse OF
SalespersonToUse::"Assigned To Customer":
SalespersonString := Text002;
SalespersonToUse::"Assigned To Sales Order":
SalespersonString := Text003;
ELSE
ERROR(Text001);
END;
END;
}
DATAITEMS
{
{ PROPERTIES
{
DataItemTable=Table13;
DataItemTableView=SORTING(Code);
PrintOnlyIfDetail=Yes;
ReqFilterHeadingML=[ENU=Salesperson;
ESM=Vendedor;
FRC=Repr‚sentant;
ENC=Salesperson];
OnPreDataItem=BEGIN
CurrReport.CREATETOTALS("Value Entry"."Sales Amount (Actual)",Profit,"Value Entry"."Discount Amount");
END;
OnAfterGetRecord=BEGIN
CurrReport.NEWPAGEPERRECORD := OnlyOnePerPage;
END;
ReqFilterFields=Code,Name;
}
SECTIONS
{
{ PROPERTIES
{
SectionType=Header;
PrintOnEveryPage=Yes;
SectionWidth=18150;
SectionHeight=1692;
}
CONTROLS
{
{ 1001;Label ;0 ;0 ;7500 ;423 ;FontSize=8;
FontBold=Yes;
CaptionML=[ENU=Customer/Item Statistics by Salesperson;
ESM=Estad¡sticas cliente/producto por vendedor;
FRC=Statistiques client/article par repr‚sentant;
ENC=Customer/Item Statistics by Salesperson] }
{ 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 <> ''));
END;
}
CONTROLS
{
{ 9 ;TextBox ;0 ;0 ;18150;423 ;FontBold=Yes;
SourceExpr="Salesperson/Purchaser".TABLECAPTION + ': ' + FilterString }
}
}
{ PROPERTIES
{
SectionType=Header;
PrintOnEveryPage=Yes;
SectionWidth=18150;
SectionHeight=423;
OnPreSection=BEGIN
CurrReport.SHOWOUTPUT((CurrReport.PAGENO = 1) AND (FilterString2 <> ''));
END;
}
CONTROLS
{
{ 10 ;TextBox ;0 ;0 ;18150;423 ;SourceExpr=Customer.TABLECAPTION + ': ' + FilterString2 }
}
}
{ PROPERTIES
{
SectionType=Header;
PrintOnEveryPage=Yes;
SectionWidth=18150;
SectionHeight=423;
OnPreSection=BEGIN
CurrReport.SHOWOUTPUT((CurrReport.PAGENO = 1) AND (FilterString3 <> ''));
END;
}
CONTROLS
{
{ 11 ;TextBox ;0 ;0 ;18150;423 ;SourceExpr="Value Entry".TABLECAPTION + ': ' + FilterString3 }
}
}
{ PROPERTIES
{
SectionType=Header;
PrintOnEveryPage=Yes;
SectionWidth=18150;
SectionHeight=846;
OnPreSection=BEGIN
CurrReport.SHOWOUTPUT(CurrReport.PAGENO = 1);
END;
}
CONTROLS
{
{ 1020000;TextBox ;0 ;0 ;18150;423 ;SourceExpr=SalespersonString }
}
}
{ PROPERTIES
{
SectionType=Header;
PrintOnEveryPage=Yes;
SectionWidth=18150;
SectionHeight=1692;
}
CONTROLS
{
{ 12 ;Label ;0 ;0 ;1650 ;423 ;ParentControl=27;
VertAlign=Bottom;
FontBold=Yes }
{ 13 ;Label ;3000 ;0 ;4500 ;423 ;ParentControl=28;
VertAlign=Bottom;
FontBold=Yes }
{ 15 ;Label ;300 ;423 ;1650 ;423 ;ParentControl=34;
VertAlign=Bottom;
FontBold=Yes }
{ 16 ;Label ;3300 ;423 ;4500 ;423 ;ParentControl=35;
VertAlign=Bottom;
FontBold=Yes }
{ 17 ;Label ;14400;423 ;1500 ;846 ;ParentControl=49;
VertAlign=Bottom;
FontBold=Yes;
MultiLine=Yes;
CaptionML=ENU=Price }
{ 20 ;Label ;6150 ;846 ;2250 ;423 ;ParentControl=46;
VertAlign=Bottom;
FontBold=Yes }
{ 21 ;Label ;8550 ;846 ;3750 ;423 ;ParentControl=47;
VertAlign=Bottom;
FontBold=Yes }
{ 22 ;Label ;12750;423 ;1050 ;846 ;ParentControl=48;
HorzAlign=Right;
VertAlign=Bottom;
FontBold=Yes;
MultiLine=Yes }
{ 23 ;Label ;16050;423 ;1800 ;846 ;ParentControl=50;
HorzAlign=Right;
VertAlign=Bottom;
FontBold=Yes }
{ 1000000000;Label ;450 ;846 ;1650 ;423 ;VertAlign=Bottom;
FontBold=Yes;
CaptionML=ENU=Date }
{ 1000000003;Label ;3450 ;846 ;2550 ;423 ;VertAlign=Bottom;
FontBold=Yes;
CaptionML=ENU=Document Number }
}
}
{ PROPERTIES
{
SectionType=Body;
SectionWidth=18150;
SectionHeight=423;
}
CONTROLS
{
{ 27 ;TextBox ;0 ;0 ;1500 ;423 ;HorzAlign=Left;
FontSize=8;
FontBold=Yes;
CaptionML=[ENU=Salesperson;
ESM=Vendedor;
FRC=Repr‚sentant;
ENC=Salesperson];
SourceExpr=Code }
{ 28 ;TextBox ;3000 ;0 ;4500 ;423 ;FontSize=8;
FontBold=Yes;
CaptionML=[ENU=Salesperson Name;
ESM=Nombre vendedor;
FRC=Nom du repr‚sentant;
ENC=Salesperson Name];
SourceExpr=Name }
}
}
{ PROPERTIES
{
SectionType=Footer;
SectionWidth=18150;
SectionHeight=1269;
OnPreSection=BEGIN
"CalculateProfit%";
END;
}
CONTROLS
{
{ 29 ;TextBox ;15150;423 ;2700 ;423 ;FontSize=8;
FontBold=Yes;
SourceExpr="Value Entry"."Sales Amount (Actual)" }
{ 33 ;Label ;3000 ;423 ;6600 ;423 ;FontSize=8;
FontBold=Yes;
PadChar=.;
CaptionML=[ENU=Report Totals;
ESM=Informe totales;
FRC=Rapport des totaux;
ENC=Report Totals] }
}
}
}
}
{ PROPERTIES
{
DataItemIndent=1;
DataItemTable=Table18;
DataItemTableView=SORTING(Salesperson Code,No.);
PrintOnlyIfDetail=Yes;
OnPreDataItem=BEGIN
CASE SalespersonToUse OF
SalespersonToUse::"Assigned To Customer":
BEGIN
SETCURRENTKEY("Salesperson Code","No.");
SETRANGE("Salesperson Code","Salesperson/Purchaser".Code);
END;
SalespersonToUse::"Assigned To Sales Order":
BEGIN
SETCURRENTKEY("No.");
SETRANGE("Salesperson Code");
END;
END;
CurrReport.CREATETOTALS("Value Entry"."Sales Amount (Actual)",Profit,"Value Entry"."Discount Amount");
END;
ReqFilterFields=No.,Search Name;
}
SECTIONS
{
{ PROPERTIES
{
SectionType=Body;
SectionWidth=18150;
SectionHeight=1269;
}
CONTROLS
{
{ 34 ;TextBox ;300 ;423 ;1500 ;423 ;HorzAlign=Left;
CaptionML=[ENU=Customer No.;
ESM=N§ cliente;
FRC=Nø de client;
ENC=Customer No.];
SourceExpr="No." }
{ 35 ;TextBox ;3300 ;423 ;4500 ;423 ;CaptionML=[ENU=Customer Name;
ESM=Nombre cliente;
FRC=Nom du client;
ENC=Customer Name];
SourceExpr=Name }
{ 36 ;TextBox ;10050;423 ;2250 ;423 ;SourceExpr="Phone No." }
{ 37 ;TextBox ;13950;423 ;3750 ;423 ;SourceExpr=Contact }
{ 38 ;Label ;9000 ;423 ;900 ;423 ;FontBold=Yes;
CaptionML=[ENU=Phone:;
ESM=Tel‚fono:;
FRC=T‚l‚phone:;
ENC=Phone:] }
{ 39 ;Label ;12600;423 ;1200 ;423 ;FontBold=Yes;
CaptionML=[ENU=Contact:;
ESM=Contacto:;
FRC=Contact :;
ENC=Contact:] }
{ 1000000004;Shape ;0 ;423 ;18000;423 ;BorderColor=255;
BorderWidth=1pt }
}
}
{ PROPERTIES
{
SectionType=Footer;
SectionWidth=18150;
SectionHeight=1692;
OnPreSection=BEGIN
"CalculateProfit%";
END;
}
CONTROLS
{
{ 40 ;TextBox ;0 ;423 ;1500 ;423 ;HorzAlign=Left;
SourceExpr="Salesperson/Purchaser".Code }
{ 41 ;TextBox ;15150;423 ;2700 ;423 ;FontBold=Yes;
SourceExpr="Value Entry"."Sales Amount (Actual)" }
{ 45 ;Label ;3000 ;423 ;5100 ;423 ;FontBold=Yes;
PadChar=.;
CaptionML=[ENU=Salesperson Totals;
ESM=Totales vendedor;
FRC=Totaux du repr‚sentant;
ENC=Salesperson Totals] }
}
}
}
}
{ PROPERTIES
{
DataItemIndent=2;
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
CASE SalespersonToUse OF
SalespersonToUse::"Assigned To Customer":
SETRANGE("Salespers./Purch. Code");
SalespersonToUse::"Assigned To Sales Order":
SETRANGE("Salespers./Purch. Code","Salesperson/Purchaser".Code);
END;
CurrReport.CREATETOTALS("Invoiced Quantity","Sales Amount (Actual)",Profit,"Discount Amount");
END;
OnAfterGetRecord=BEGIN
// 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;
Profit := "Sales Amount (Actual)" + "Cost Amount (Actual)";
"Discount Amount" := -"Discount Amount";
END;
ReqFilterFields=Item No.,Posting Date,Sales Amount (Actual);
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 := 'Others';
Item."Base Unit of Measure" := '';
END;
END;
}
CONTROLS
{
{ 46 ;TextBox ;6150 ;0 ;2250 ;423 ;HorzAlign=Left;
CaptionML=[ENU=Item Number;
ESM=N§ producto;
FRC=Num‚ro d'article;
ENC=Item Number];
SourceExpr="Item No." }
{ 47 ;TextBox ;8550 ;0 ;3600 ;423 ;CaptionML=[ENU=Item Description;
ESM=Descripci¢n producto;
FRC=Description d'article;
ENC=Item Description];
SourceExpr=Item.Description }
{ 48 ;TextBox ;12750;0 ;1050 ;423 ;CaptionML=[ENU=Quantity;
ESM=Cantidad;
FRC=Quantit‚;
ENC=Quantity];
SourceExpr=-"Invoiced Quantity" }
{ 49 ;TextBox ;14400;0 ;1500 ;423 ;CaptionML=[ENU=Unit of Measure;
ESM=Unidad medida;
FRC=Unit‚ de mesure;
ENC=Unit of Measure];
SourceExpr=Item."Unit Price" }
{ 50 ;TextBox ;16050;0 ;1800 ;423 ;CaptionML=[ENU=Amount;
ESM=Importe;
FRC=Montant;
ENC=Amount];
SourceExpr="Sales Amount (Actual)" }
{ 1000000001;TextBox;450 ;0 ;2250 ;423 ;HorzAlign=Left;
CaptionML=[ENU=Item Number;
ESM=N§ producto;
FRC=Num‚ro d'article;
ENC=Item Number];
SourceExpr="Value Entry"."Posting Date" }
{ 1000000002;TextBox;3450 ;0 ;2100 ;423 ;HorzAlign=Left;
CaptionML=[ENU=Item Number;
ESM=N§ producto;
FRC=Num‚ro d'article;
ENC=Item Number];
SourceExpr="Value Entry"."Document No." }
}
}
{ PROPERTIES
{
SectionType=Footer;
SectionWidth=18150;
SectionHeight=846;
OnPreSection=BEGIN
"CalculateProfit%";
END;
}
CONTROLS
{
{ 54 ;TextBox ;300 ;423 ;1500 ;423 ;HorzAlign=Left;
SourceExpr=Customer."No." }
{ 55 ;TextBox ;16050;423 ;1800 ;423 ;SourceExpr="Sales Amount (Actual)" }
{ 59 ;Label ;3300 ;423 ;4800 ;423 ;MultiLine=No;
PadChar=.;
CaptionML=[ENU=Customer Totals;
ESM=Totales cliente;
FRC=Totaux du client;
ENC=Customer Totals] }
}
}
}
}
}
REQUESTFORM
{
PROPERTIES
{
Width=7590;
Height=2310;
SaveValues=Yes;
}
CONTROLS
{
{ 2 ;CheckBox ;3410 ;550 ;330 ;440 ;ShowCaption=No;
SourceExpr=OnlyOnePerPage }
{ 3 ;Label ;0 ;550 ;3300 ;440 ;ParentControl=2;
CaptionML=[ENU=New Page per Salesperson;
ESM=Nueva p g. por vendedor;
FRC=Nouvelle page par repr‚sentant;
ENC=New Page per Salesperson] }
{ 1020000;TextBox ;3410 ;0 ;3740 ;440 ;CaptionML=[ENU=Salesperson To Use;
ESM=Vendedor que se utilizar ;
FRC=Repr‚sentant … utiliser;
ENC=Salesperson To Use];
OptionCaptionML=[ENU=Assigned To Customer,Assigned To Sales Order;
ESM=Asignado a cliente,Asignado a pedido venta;
FRC=Affect‚ … client,Affect‚ … document de vente;
ENC=Assigned To Customer,Assigned To Sales Order];
OptionString=Assigned To Customer,Assigned To Sales Order;
SourceExpr=SalespersonToUse }
{ 1020001;Label ;0 ;0 ;3300 ;440 ;ParentControl=1020000 }
{ 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
FilterString@1000 : Text[250];
FilterString2@1001 : Text[250];
FilterString3@1002 : Text[250];
Profit@1003 : Decimal;
"Profit%"@1004 : Decimal;
OnlyOnePerPage@1005 : Boolean;
CompanyInformation@1007 : Record 79;
SalespersonToUse@1020000 : 'Assigned To Customer,Assigned To Sales Order';
SalespersonString@1020001 : Text[250];
Text001@1020002 : TextConst 'ENU=Invalid option chosen for Salesperson To Use.;ESM=Opci¢n no v lida para Vendedor que se utilizar .;FRC=Option invalide choisie pour le repr‚sentant … utiliser.;ENC=Invalid option chosen for Salesperson To Use.';
Text002@1020003 : TextConst 'ENU=Individual sale shows under the Salesperson assigned to that Customer.;ESM=Cada venta se muestra en el vendedor asignado a ese cliente.;FRC=La vente individuelle s''affiche sous le repr‚sentant attribu‚ … ce client.;ENC=Individual sale shows under the Salesperson assigned to that Customer.';
Text003@1020004 : TextConst 'ENU=Individual sale shows under the Salesperson assigned to that individual Sales Order.;ESM=Cada venta se muestra en el vendedor asignado al correspondiente pedido de venta.;FRC=La vente individuelle s''affiche sous le repr‚sentant attribu‚ … ce document de vente individuel.;ENC=Individual sale shows under the Salesperson assigned to that individual Sales Order.';
Item@1000000000 : Record 27;
ItemCategoryCode@1000000001 : Text[90];
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;
BEGIN
END.
}
}
Half-empy or half-full how do you view your database?
Thanks.
Thanks.
0
Comments
-Mohana
http://mohana-dynamicsnav.blogspot.in/
https://www.facebook.com/MohanaDynamicsNav
Lets say I have one invoice # 120321017 posted on 03/21/12 with these items:
1204SALE
1234SALE
5678SALE
9012SALE
When running the report with the date range 03/01/12..03/31/12 only these would show up from the invoice 120321017:
1204SALE
5678SALE
When running the report with the date range 03/21/12 all of these show up:
1204SALE
1234SALE
5678SALE
9012SALE
Normally everything shows up. I have never had this happen before. How do I fix this?
Thanks.
However, is it possible in any way that this field is added to the key later?
In this case the change of the plus minus sign could give this effect.
Wouldn't the key need to be set implicitly for it to affect the output? It isn't set anywhere in the report itself that I can see.
This report has always worked very well. The only reason I think this is happening has something to do with the fact that we posted invoices (back-dating) using the WORK DATE in Navision and every invoice posted has the same date for posting date as order date, and document date when they were posted.
In someone's previous reply they mentioned being grouped with something else which I don't think is possible. If I run the same report with a single date then everything with "SALE" in the item number shows up how it is supposed to.
I'm puzzled here. The only thing I can think of is that Navision isn't catching everything it should with the filter on the ITEM tab due to the back posting effecting it somehow.
Thanks.
Now somebody modified this key later for other purposes, then it could look like this: Now when you run the report, the dataitem behaves like it is using the new key, even though the old key is still mentioned in the dataitem properties. Even if you export the object in text format (like above post) the old key is still used there. Only in the case that there is no key at all that starts with the same fields as the one mentioned in the properties, the report will fail.
Although it does not match with your statement that the report always worked very well, i cannot see why this report won't work apart from this reason; all records that match the filter 03/21/12 should match to 03/01/12..03/31/12 as well.
Did you try to add a body section to see what happens?
The problem does seem to be SORTING. Since I want the output from the Value Entry table to show the invoice numbers in order by Posting Date would it work if I added a new key:
Posting Date,Source Type,Source No.,Item Ledger Entry Type,Item No.
Then changed the SORTING to:
Thanks.
I ran a few test reports vs the old version of the report. Guess what? The old version of the report that uses the key with the Posting Date last fails to get all entries per the user entered filter criteria under the Value Entry tab. In other words I enter "*@SALE|A*" under the Value Entry tab whichs filter out our sale items and the old report is missing some of those items that were definitely invoiced and show up if you specify just a single Posting Date rather than a range. The new report with the new key catches every one of the lines and it's much faster.
Why is this the case?
Thanks.
Regarding the speed is that the fields where you filter on just a few unique items should be at the beginning of the key. See this topic as well: viewtopic.php?f=23&t=44264
I see the problem is solved with a new key, but in case you needed the first key for specific view of the report, you could also try to avoid altering the dataitem. To do this, create a global decimal gDecDiscountAmount, and replace with Besides, why is this field "Discount Amount" not on any section? If you don't need it, why (re)calculate it?
See attached image for a view of the keys on my Value Entry table.
I added the last key with the Posting Date first and it fixed my report as I mentioned previously, it makes sense to me that this worked because my filtering criteria was in the following levels: Salesperson->Customer->Value Entry
Salesperson Code specified = Data filtered to Salesperson level
Customer = No filtering so all customers in Data filtered to Salesperson level above (untouched no filtering on Customer)
Value Entry = Data filtered all Customers to Salesperson Assigned and SORTED by my new KEY which the first field is Posting Date of Value Entry
Am I wrong in doing it this way? It works great. Any better way to modify this report?
Thanks.
tonight i did a thorough look at this report, and came to the conlusion that Mohana was right on the grouping. Looks like I got it wrong myself in previous posts...
Let's work this out:
Suppose you have these entries on 03/21/12
item 1233SALE document A
item 1234SALE document A
and an entry on 03/22/12 for item 1234SALE, document B
Now we run this report with both dates in the filter.
Since the (initial) sorting of the report was first Item No. and then Posting Date, AND the grouptotalfield is set to Item No., item 1234SALE will show up only once on the groupfooter, showing a date of 03/22/12, and its corresponding document no (B). Its amount will be the total of 03/21 and 03/22 (more than one document). There is no reporting for item 1234SALE on 03/21/12.
Item 1233SALE will show up once as well, but this time with a correct date.
If you filter this report only on 03/21/12 then of course the corresponding entries will show up, neatly grouped on that one date. (but still showing wrong data if there are more documents on that date!)
To correct this, and to show all entries of one document together, you need to modify the key so that Ddocument No comes before Item No, and also include document no in the GroupTotalFields. To avoid double groupfooters at the end of a document, add this code to the OnPreSection of the groupfooter:
CurrReport.SHOWOUTPUT := CurrReport.TOTALSCAUSEDBY = "Value Entry".FIELDNO("Item No.");It can be assumed that all entries of one document are on the same posting date, so posting date doesn't need to be a grouptotalfield.There are more fields before Item No. in your first key, but all of them are filtered out on one value (Source Type=Customer, Source No=Customer No, Item Ledger Entry Type=Sale), so these fields are not causing any problems. Actually, for best performance it is best if these fields are kept at the beginning of the key.
Since you are filtering on posting date, the most ideal key in my opinion would be SourceType,SourceNo,ItemLedgerEntryType,PostingDate,DocumentNo,ItemNo