problem filtering Date system table in report's DataItem (NAV 2016)

navmedic · 2017-04-21

If system table Date used as DataItem in report then filter by "Period Start" ignored.
Test report see below, it collects too many lines (must be only one).
Debugger shows field out of filter

Platform bug?

OBJECT Report 69200 Test Dates
{
OBJECT-PROPERTIES
{
Date=21.04.17;
Time=13:01:56;
Modified=Yes;
Version List=;
}
PROPERTIES
{
}
DATASET
{
{ 1000000000;;DataItem; ;
DataItemTable=Table2000000007;
DataItemTableView=SORTING(Period Type,Period Start);
OnPreDataItem=BEGIN
SETRANGE("Period Type",0);
SETRANGE("Period Start", WORKDATE, WORKDATE);
END;
}

{ 1000000001;1;Column;bd ;
SourceExpr=Date."Period Start" }

{ 1000000002;1;Column;ed ;
SourceExpr=Date."Period End" }

{ 1000000003;1;Column;nm ;
SourceExpr=Date."Period Name" }

}
REQUESTPAGE
{
PROPERTIES
{
}
CONTROLS
{
}
}
LABELS
{
}
CODE
{

BEGIN
END.
}
RDLDATA
{
<?xml version="1.0" encoding="utf-8"?>
<Report xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner" xmlns:cl="http://schemas.microsoft.com/sqlserver/reporting/2010/01/componentdefinition" xmlns="http://schemas.microsoft.com/sqlserver/reporting/2010/01/reportdefinition">
<DataSources>
<DataSource Name="DataSource">
<ConnectionProperties>
<DataProvider>SQL</DataProvider>
<ConnectString />
</ConnectionProperties>
</DataSource>
</DataSources>
<ReportSections>
<ReportSection>
<Body>
<Height>2in</Height>
<Style />
</Body>
<Width>6.5in</Width>
<Page>
<Style />
</Page>
</ReportSection>
</ReportSections>
<Code>Public Function BlankZero(ByVal Value As Decimal)
if Value = 0 then
Return ""
end if
Return Value
End Function

Public Function BlankPos(ByVal Value As Decimal)
if Value > 0 then
Return ""
end if
Return Value
End Function

Public Function BlankZeroAndPos(ByVal Value As Decimal)
if Value >= 0 then
Return ""
end if
Return Value
End Function

Public Function BlankNeg(ByVal Value As Decimal)
if Value < 0 then
Return ""
end if
Return Value
End Function

Public Function BlankNegAndZero(ByVal Value As Decimal)
if Value <= 0 then
Return ""
end if
Return Value
End Function
</Code>
<Language>=User!Language</Language>
<ConsumeContainerWhitespace>true</ConsumeContainerWhitespace>
<rd:ReportID>0eeb6585-38ae-40f1-885b-8d50088d51b4</rd:ReportID>
<rd:ReportUnitType>Inch</rd:ReportUnitType>
</Report>
END_OF_RDLDATA
}
}

Dolsha · 2017-04-21

What version of NAV u use?

I'll try that on NAV 2016:

Date - OnPreDataItem()
Date.SETRANGE("Period Type",Date."Period Type"::Date);
Date.SETRANGE("Period Start",TODAY);

Date - OnAfterGetRecord()
MESSAGE('Period Name:'+Date."Period Name"+'\Period Start:'+FORMAT(Date."Period Start")+'\Period End:'+FORMAT(Date."Period End"));

And Have Result:

Period Name:Friday
Period Start:21.04.17
Period End:C21.04.17

Kishorm · 2017-04-21

Try restarting the NST - I suspect the issue will go away.

Howdy, Stranger!

Quick Links

Categories

problem filtering Date system table in report's DataItem (NAV 2016)

Answers