Null Value are coming in report

Stivan_dsouza21 · 2012-01-24

Null Value are coming in report want to skip the null:
Below is the code written:

OBJECT Report 50120 Productwise Volume Sales
{
OBJECT-PROPERTIES
{
Date=24-01-12;
Time=17:39:53;
Modified=Yes;
Version List=Pratyusha;
}
PROPERTIES
{
OnPostReport=BEGIN
file1.CLOSE;
END;

}
DATAITEMS
{
{ PROPERTIES
{
DataItemTable=Table18;
DataItemTableView=SORTING(No.)
WHERE(Customer Type=CONST(Distributor));
OnPreDataItem=BEGIN
{
IF EXISTS('D:\Report\Productwise Sales Summary.csv') THEN
ERASE('D:\Report\Productwise Sales Summary.csv');

file1.CREATE('D:\Report\Productwise Sales Summary.csv');

file1.CREATEOUTSTREAM(OutStreamObj);
}
{IF EXISTS('C:\Users\EBT\Desktop\STIVAN\Productwise Sales Summary.csv') THEN
ERASE('C:\Users\EBT\Desktop\STIVAN\Productwise Sales Summary.csv');

file1.CREATE('C:\Users\EBT\Desktop\STIVAN\Productwise Sales Summary.csv');

file1.CREATEOUTSTREAM(OutStreamObj);}

//EBT STIVAN
IF EXISTS('C:\EBT\Productwise Sales Summary.csv') THEN
ERASE('C:\EBT\Productwise Sales Summary.csv');

file1.CREATE('C:\EBT\Productwise Sales Summary.csv');

file1.CREATEOUTSTREAM(OutStreamObj);
//EBT STIVAN

str3 := 'Distributor Code'+','+'Distributor Name'+','+'FG Code'+','+'FG Description'+','+
'Last Month Volumes Sold'+','+'Gross Value Last Month'+','+'Current Month Till Date Volumes Sold'+','+
'Gross Value Current Month';
OutStreamObj.WRITETEXT(str3);
OutStreamObj.WRITETEXT();
END;

ReqFilterFields=No.;
}
SECTIONS
{
{ PROPERTIES
{
SectionType=Body;
SectionWidth=12000;
SectionHeight=846;
}
CONTROLS
{
}
}
}
}
{ PROPERTIES
{
DataItemIndent=1;
DataItemTable=Table113;
DataItemTableView=SORTING(No.)
WHERE(Type=FILTER(Item));
OnPreDataItem=BEGIN
TempDate:=CALCDATE('-CM',TODAY); //01/12/2011
TempDate1:=CALCDATE('-1D',TempDate); //30/11/2011
LastMonth:=CALCDATE('-CM',TempDate1); //01/11/2011
END;

OnAfterGetRecord=BEGIN
str:=Customer."No."+','+Customer.Name+','+"Sales Invoice Line"."No."+','+"Sales Invoice Line".Description;
END;

GroupTotalFields=No.;
DataItemLink=Sell-to Customer No.=FIELD(No.);
}
SECTIONS
{
{ PROPERTIES
{
SectionType=Body;
SectionWidth=12000;
SectionHeight=978;
}
CONTROLS
{
}
}
{ PROPERTIES
{
SectionType=GroupFooter;
SectionWidth=12000;
SectionHeight=846;
OnPreSection=BEGIN
VolumeSold:=0;
Amt:=0;
SalesInvHeader.RESET;
SalesInvHeader.SETRANGE(SalesInvHeader."Sell-to Customer No.",Customer."No.");
SalesInvHeader.SETRANGE(SalesInvHeader."Posting Date",LastMonth,TempDate1);
IF SalesInvHeader.FINDSET THEN
REPEAT
SalesInvLine.RESET;
SalesInvLine.SETRANGE(SalesInvLine."Document No.",SalesInvHeader."No.");
SalesInvLine.SETRANGE(SalesInvLine."No.","Sales Invoice Line"."No.");
IF SalesInvLine.FINDSET THEN
REPEAT
VolumeSold+=(SalesInvLine."Total Qty BRT/Sale"/1000);
//Amt+=SalesInvLine.Amount;
Amt+=SalesInvLine."Amount To Customer";
UNTIL SalesInvLine.NEXT=0;
UNTIL SalesInvHeader.NEXT=0;

VolumeSoldCurrMnt:=0;
AmtCurrMnt:=0;
SalesInvHeader.RESET;
SalesInvHeader.SETRANGE(SalesInvHeader."Sell-to Customer No.",Customer."No.");
SalesInvHeader.SETRANGE(SalesInvHeader."Posting Date",TempDate,TODAY);
IF SalesInvHeader.FINDSET THEN
REPEAT
SalesInvLine.RESET;
SalesInvLine.SETRANGE(SalesInvLine."Document No.",SalesInvHeader."No.");
SalesInvLine.SETRANGE(SalesInvLine."No.","Sales Invoice Line"."No.");
IF SalesInvLine.FINDSET THEN
REPEAT
VolumeSoldCurrMnt+=(SalesInvLine."Total Qty BRT/Sale"/1000);
AmtCurrMnt+=SalesInvLine."Amount To Customer";
UNTIL SalesInvLine.NEXT=0;
UNTIL SalesInvHeader.NEXT=0;

str1:=','+FORMAT(VolumeSold,0,1)+','+FORMAT(Amt,0,1)+','+FORMAT(VolumeSoldCurrMnt,0,1)+','+FORMAT(AmtCurrMnt,0,1);

OutStreamObj.WRITETEXT(str+str1);
OutStreamObj.WRITETEXT();
END;

}
CONTROLS
{
}
}
}
}
}
REQUESTFORM
{
PROPERTIES
{
Width=9020;
Height=3410;
}
CONTROLS
{
}
}
CODE
{
VAR
OutStreamObj@1000000000 : OutStream;
file1@1000000001 : File;
str@1000000002 : Text[1024];
str1@1000000009 : Text[1024];
SalesInvLine@1000000003 : Record 113;
VolumeSold@1000000004 : Decimal;
VolumeSoldCurrMnt@1000000011 : Decimal;
SalesInvHeader@1000000005 : Record 112;
TempDate@1000000006 : Date;
TempDate1@1000000007 : Date;
LastMonth@1000000008 : Date;
Amt@1000000010 : Decimal;
AmtCurrMnt@1000000012 : Decimal;
str3@1000000013 : Text[1024];
temp@1000000014 : Decimal;

BEGIN
END.
}
}

Can somebody help me out

mohana_cse06 · 2012-01-24

Where did you write code to skip the NULL values?

Stivan_dsouza21 · 2012-01-24

i had written it on Sales Invoice Line - OnAfterGetRecord()
IF "Sales Invoice Line"."Total Qty BRT/Sale" = 0 THEN
currreport.skip;

but then too null values are coming

mohana_cse06 · 2012-01-24

Stivan_dsouza21 wrote:

i had written it on Sales Invoice Line - OnAfterGetRecord()
IF "Sales Invoice Line"."Total Qty BRT/Sale" = 0 THEN
currreport.skip;

but then too null values are coming

I cant see above code in first file..

Is Total Qty BRT/Sale a flowfield?

Stivan_dsouza21 · 2012-01-24

NO

this is the old file re

David_Singleton · 2012-01-24

Navision can not handle Nulls, so you need to go into the backend via SQL and clean them up, change them to zero or blank.

Stivan_dsouza21 · 2012-01-24

It contains zero value and its coming in report excel I want to skip all the zero values

Sog · 2012-01-24

your pasted code shows no sign of skipping anything.
Next, format your code so that you first check everything before exporting, so

will not skip on time:
exporttoexcel(line);
if line.value = 0 then 
  currreport.skip;

will skip on time
if line.value = 0 then 
  currreport.skip
exporttoexcel(line);

Also, if you determine your own row/column, make sure you don't add 1 to rowno if you skip the record.

Stivan_dsouza21 · 2012-02-02

Written Code on Sales Invoice Line, GroupFooter (2) - OnPreSection():

str1:=','+FORMAT(VolumeSold,0,1)+','+FORMAT(Amt,0,1)+','+FORMAT(VolumeSoldCurrMnt,0,1)+','+FORMAT(AmtCurrMnt,0,1);

IF (VolumeSold <> 0) OR (Amt <> 0) OR (VolumeSoldCurrMnt <> 0) OR (AmtCurrMnt <> 0) THEN
BEGIN
OutStreamObj.WRITETEXT(str+str1);
OutStreamObj.WRITETEXT();
END;

Zephyr · 2012-02-02

Show your code properly, neither you did wrote any code SKIP for null value nor its readable.

Null Value are coming in report

Comments

Categories