report won't run for range of dates in filter

johnsogj

hello, I've created a report that runs over the sales line table. I have the user filter request set up so that "shipment date" defaults. however, when I enter a range of dates: "11/12/07..11/30/07", it only prints the data for the first date in the range. has anyone seen this happen before?

thanks

Alex_Chow

johnsogj wrote:

hello, I've created a report that runs over the sales line table. I have the user filter request set up so that "shipment date" defaults. however, when I enter a range of dates: "11/12/07..11/30/07", it only prints the data for the first date in the range. has anyone seen this happen before?

thanks

Something is wrong with your code. Post your code on here so people can take a look.

johnsogj

SalesInvLine.RESET;

SalesInvLine.SETCURRENTKEY(Type,"Shipment Date");
SalesInvLine.SETRANGE(Type,SalesInvLine.Type::Item);
SalesInvLine.SETRANGE ("Shipment Date", "Shipment Date");
TotalLinesDay := SalesInvLine.COUNT;
SalesInvLine.CALCSUMS(Quantity);
TotalPiecesDay := SalesInvLine.Quantity;

SalesInvLine.SETCURRENTKEY(Type,"Shipment Date","Sell-to Customer No.");
SalesInvLine.SETRANGE(Type,SalesInvLine.Type::Item);
SalesInvLine.SETRANGE ("Shipment Date", "Shipment Date");
SalesInvLine.SETRANGE ("Sell-to Customer No.", "Sell-to Customer No.");
TotalLines := SalesInvLine.COUNT;
SalesInvLine.CALCSUMS(Quantity);
TotalPieces := SalesInvLine.Quantity;

SalesInvHead.SETRANGE("Shipment Date", "Shipment Date");
IF SalesInvHead.FINDSET THEN
TotalInvoicesDay := SalesInvHead.COUNT;

SalesInvHead.SETRANGE("Shipment Date", "Shipment Date");
SalesInvHead.SETRANGE("Sell-to Customer No.", "Sell-to Customer No.");
IF SalesInvHead.FINDSET THEN
TotalInvoices := SalesInvHead.COUNT;

AvgLinesDay := TotalLinesDay/TotalInvoicesDay;
AvgPiecesDay := TotalPiecesDay/TotalInvoicesDay;

AvgLines := TotalLines/TotalInvoices;
AvgPieces := TotalPieces/TotalInvoices;

garak

SalesInvLine.SETRANGE ("Shipment Date", "Shipment Date");
TotalLinesDay := SalesInvLine.COUNT;

Do you need to filter the SalesInvLine basicly of the insertet filter in field "Shipment Date" from Sales Line :?:

But here you filter SalsInvLine."Shipment date" with the value of "Shipment Date" from a located Rec in SalesLine. So you need to filter.

SalesInvLine.SETfilter("Shipment Date", getfilter("Shipment Date")); //Use the Filter from Request Form for the SalesInvLine
TotalLinesDay := SalesInvLine.COUNT;

The same issue for the other "Shipment date" Filters .....

Regards

johnsogj

I've changed my code as you suggested but I am still seeing the same problem. the report only runs for the first shipment date in the range specified on the filter form:


SalesInvLine.RESET;

SalesInvLine.SETCURRENTKEY(Type,"Shipment Date");
SalesInvLine.SETRANGE(Type,SalesInvLine.Type::Item);
SalesInvLine.SETFILTER("Shipment Date", GETFILTER("Shipment Date"));
TotalLinesDay := SalesInvLine.COUNT;
SalesInvLine.CALCSUMS(Quantity);
TotalPiecesDay := SalesInvLine.Quantity;

SalesInvLine.SETCURRENTKEY(Type,"Shipment Date","Sell-to Customer No.");
SalesInvLine.SETRANGE(Type,SalesInvLine.Type::Item);
SalesInvLine.SETFILTER("Shipment Date", GETFILTER("Shipment Date"));
SalesInvLine.SETRANGE ("Sell-to Customer No.", "Sell-to Customer No.");
TotalLines := SalesInvLine.COUNT;
SalesInvLine.CALCSUMS(Quantity);
TotalPieces := SalesInvLine.Quantity;

SalesInvLine.SETFILTER("Shipment Date", GETFILTER("Shipment Date"));
IF SalesInvHead.FINDSET THEN
TotalInvoicesDay := SalesInvHead.COUNT;

SalesInvLine.SETFILTER("Shipment Date", GETFILTER("Shipment Date"));
SalesInvHead.SETRANGE("Sell-to Customer No.", "Sell-to Customer No.");
IF SalesInvHead.FINDSET THEN
TotalInvoices := SalesInvHead.COUNT;

AvgLinesDay := TotalLinesDay/TotalInvoicesDay;
AvgPiecesDay := TotalPiecesDay/TotalInvoicesDay;

AvgLines := TotalLines/TotalInvoices;

David_Singleton

johnsogj wrote:

I've changed my code as you suggested but I am still seeing the same problem. the report only runs for the first shipment date in the range specified on the filter form:

John,

the code you posted here is NOT the code that is causingthe problem. The filter you are applying is (I will have to assume since you seem to be holding out on us :evil: ) on "Sales Inv. Line"."Shipment date".

that being the record in the Report section. We need to see how you are filtering that one, its irrelevant what you do withthe SalesInvLine table, since that is not the record in the loop.

johnsogj

I posted the entire code that I have.... I'm confused.

DenSter

I also have a feeling that we're not seeing all the code, although that doesn't necessarily have to be a deliberate omission. Maybe the dataitem has the name SalesInvLine? In that case it seems you set the range as a filter before the report starts, and at the first record, you change the filter to the one value.

By the way, you could use a C/AL syntax primer. Some statements are repeated that don't need to be repeated. For instance, once you set a filter on a field, it keeps the filter, you don't have to set it twice when you add a filter on another field. Also, you don't have to say IF FINDSET THEN COUNT, you can leave out the IF FINDSET part. If there are no records the system will return 0. Now you're getting the COUNT as well as the entire record set for every record. Lots of overhead in that code .

garak

To end this confusion ;-) could you export the Report as Text (Object Designer -> file -> Expert -> TextFile) and Post it here.

If you need to output the located SalesInvLines you need a seperate DataItem. I think you source is behind the OnAfterGetRecord Trigger of DataItem Sales Line or?

@DenSter: In his first post he wrote, that he go over the Sales Line. With the CalcSums Function, my opinion was, that he only need the SalesInvLine to calculate some fields basicly on Shipment Filter, etc. That the code is not the best, is an other part ;-)

Regards

Franco_Festi

Hi,
can you please post the dataitems you have in this report and their dataitemindent and DataItemLinkReference ?

Cause maybe we're not getting it right!

DenSter

garak wrote:

@DenSter: In his first post he wrote, that he go over the Sales Line

I know that's what he wrote, I am asking if his dataitem (for the Sales Line table) has the NAME SalesInvLine. You can give a dataitem any NAME you want. If he indeed has named his "Sales Line" dataitem "SalesInvLine", then that explains why his filter gets screwed up. I've seen crazier names than that, so it's not a strange question

ara3n

The problem is that he is setting filter on the SalesLine that he is in. If he wants to set a range,
Create two new variable. StartDate, EndDate.
OnPrereport

Copy the getrangemin and getrangemax to the variable. and in your code add

StartDate := "Sales Line".GetRANGEMIN("SHipment Date");
EndDate := "Sales Line".GetRANGEMAX("SHipment Date");





SalesInvLine.SETFILTER("Shipment Date", StartDate,EndDate);

DenSter

We're all thinking about exactly the same issue, we're just going about getting to the bottom of it in a different way

The only way to really see what is going on is to see the object.

johnsogj

thanks for all of the feedback! I feel so badly that I had a typo in the beginning. The Sales Line table is not part of this at all. All of the data I need is in the Sales Invoice Line table. So that is the data item I'm using. I'm so sorry for the confusion.

I'll try to export the code based on the instructions of one of you previously...

johnsogj

Unfortunately I do not have access to export via text... here is another copy of my code (its in the OnAfterGetRecord section)


SalesInvLine.RESET;

SalesInvLine.SETCURRENTKEY(Type,"Shipment Date");
SalesInvLine.SETRANGE(Type,SalesInvLine.Type::Item);
SalesInvLine.SETFILTER("Shipment Date", GETFILTER("Shipment Date"));
TotalLinesDay := SalesInvLine.COUNT;
SalesInvLine.CALCSUMS(Quantity);
TotalPiecesDay := SalesInvLine.Quantity;

SalesInvLine.SETCURRENTKEY(Type,"Shipment Date","Sell-to Customer No.");
SalesInvLine.SETRANGE(Type,SalesInvLine.Type::Item);
SalesInvLine.SETFILTER("Shipment Date", GETFILTER("Shipment Date"));
SalesInvLine.SETRANGE ("Sell-to Customer No.", "Sell-to Customer No.");
TotalLines := SalesInvLine.COUNT;
SalesInvLine.CALCSUMS(Quantity);
TotalPieces := SalesInvLine.Quantity;

SalesInvLine.SETFILTER("Shipment Date", GETFILTER("Shipment Date"));
IF SalesInvHead.FINDSET THEN
TotalInvoicesDay := SalesInvHead.COUNT;

SalesInvLine.SETFILTER("Shipment Date", GETFILTER("Shipment Date"));
SalesInvHead.SETRANGE("Sell-to Customer No.", "Sell-to Customer No.");
IF SalesInvHead.FINDSET THEN
TotalInvoices := SalesInvHead.COUNT;

AvgLinesDay := TotalLinesDay/TotalInvoicesDay;
AvgPiecesDay := TotalPiecesDay/TotalInvoicesDay;

AvgLines := TotalLines/TotalInvoices;
AvgPieces := TotalPieces/TotalInvoices;

Savatage

I don't understand why you have both of these sections??
You define some fields twice? any reason?
Did you try one than the other? Are these really key's? Or did you make new keys?

SalesInvLine.SETCURRENTKEY(Type,"Shipment Date"); 
SalesInvLine.SETRANGE(Type,SalesInvLine.Type::Item); 
SalesInvLine.SETFILTER("Shipment Date", GETFILTER("Shipment Date")); 
TotalLinesDay := SalesInvLine.COUNT; 
SalesInvLine.CALCSUMS(Quantity); 
TotalPiecesDay := SalesInvLine.Quantity;

SalesInvLine.SETCURRENTKEY(Type,"Shipment Date","Sell-to Customer No."); 
SalesInvLine.SETRANGE(Type,SalesInvLine.Type::Item); 
SalesInvLine.SETFILTER("Shipment Date", GETFILTER("Shipment Date")); 
SalesInvLine.SETRANGE ("Sell-to Customer No.", "Sell-to Customer No."); 
TotalLines := SalesInvLine.COUNT; 
SalesInvLine.CALCSUMS(Quantity); 
TotalPieces := SalesInvLine.Quantity;

ara3n

getfilter does not work onafter get section. Please follow my advise above and you should be set.

DenSter

Yes I agree, follow Ara3n's advice (which is a very safe bet under any circumstances ), put the range into variables, set the date filter to that range in the OnPreDataitem trigger, and you should be set. This is without having a clue about what it is that you are trying to do though.

garak

why an "getfilter" should not work on the onaftergetrecord section :?:

RecVariable.setfilter(Somefield,OtherRecVariableOrDataItemVariable.getfilter(SomeField));

It works. It is the same like getrangemin, getrangemax <- but these have troube if you use a filter like 01.01.08.. or ..31.12.08. Its work only for 01.01.08..31.12.08

Regards

garak

johnsogj

All of the data I need is in the Sales Invoice Line table. So that is the data item I'm using

The Name of you used DataItem is realy SalesInvLine :?:
If yes, why you reset the filters :?:
johnsogj

SalesInvLine.RESET;

if no, forgett this post ....