how can i calculate posted and unposted sales?

I need posted and unposted sales.

Report will come from sales header, sales invoice header,

sales invoice line, sales line,general gournal line, g/l entry tbale.

and it will be filter with posting date and responsibility centre.

Report outlook will be as like

total cash= if payment method code=cash then posted and unposted total amount

total credit=if payment method code=credit then posted and unposted total amount

total other=if payment method code=other then posted and unposted total amount

total transportation=posted and unposted Transportation Fee

total installation= posted and unposted Construction Fee

total cost amount= posted and unposted unit cost*quantity

total check amount=totalcash+ totalcredit;

total sales amount=total sales amount

total discount= posted and unposted invoice discount +line discount

total discounted amount=total discounted amount


if posted only has sales, it will show posted. if unposted then it will show unposted, if both then it will show both.

how can i set data item here? I need this report within one page.

Hi,

Best way is to create a table with above filed as flow field rather than creating a report which will be again a performance issue.

Reporting fro document tables in general is bad idea, but in your exact case there is no alternative to get the UNPOSTED part. Remember, that fully posted docs can be deleted (there are even special batchjobs for this purpose), so your report may become incomplete.

Besides, bear in mind that this one:

is useless - Docs tables NEVER contain precise costing info, this you can get only from ILE. This has been discussed here a bazillon times, do some search for reporting docs tables, you’ll get many threads explaining the case.

i have done this and it is running well.

OBJECT Report 50002 Daily Report
{
OBJECT-PROPERTIES
{
Date=26.12.11;
Time=11:34:47;
Version List=Monir;
}
PROPERTIES
{
}
DATAITEMS
{
{ PROPERTIES
{
DataItemTable=Table2000000026;
DataItemTableView=SORTING(Number)
WHERE(Number=CONST(1));
OnPreDataItem=BEGIN
totalcash_posted:=0;
totalcredit_posted:=0;
totalother_posted:=0;
END;

OnAfterGetRecord=BEGIN

IF ((StartDate <> 0D ) AND( EndDate <>0D)) THEN BEGIN
SaleH.SETRANGE(“Posting Date”,StartDate,EndDate);

END;

// SaleH.SETFILTER(“POS Order No.”, ‘<>%1’, ‘’);

IF (ResponsibilityCenter<>’’) THEN BEGIN
SaleH.SETFILTER( SaleH.“Responsibility Center”,ResponsibilityCenter);
END;

/////////////////////////////////////////////////////Start of Sales Header
WITH SaleH DO BEGIN

IF SaleH.FIND(’-’) THEN BEGIN
REPEAT

IF(SaleH.“Transportation Fee”>0) THEN BEGIN

totaltransporation_unpostedH:= totaltransporation_unpostedH+SaleH.“Transportation Fee”;

END;

IF (SaleH.“Construction Fee”>0) THEN BEGIN
totalinstallation_unpostedH:= totalinstallation_unpostedH+SaleH.“Construction Fee”;
END;

UNTIL SaleH.NEXT<=0;
END;

END;
// END OF WITH

///////////////////////////////////////////Sales InVoice Header
IF ((StartDate <> 0D ) AND( EndDate <>0D)) THEN BEGIN
SaleInH.SETRANGE(“Posting Date”,StartDate,EndDate);

END;

//SaleInH.SETFILTER(“POS Order No.”, ‘<>%1’, ‘’);

IF (ResponsibilityCenter<>’’) THEN BEGIN
SaleInH.SETFILTER( SaleInH.“Responsibility Center”,ResponsibilityCenter);
END;

WITH SaleInH DO BEGIN

IF SaleInH.FIND(’-’) THEN BEGIN

REPEAT

IF(SaleInH.“Transportation Fee”>0) THEN BEGIN

totaltransportation_postedH:= totaltransportation_postedH+SaleInH.“Transportation Fee”;

END;

IF (SaleInH.“Construction Fee”>0) THEN BEGIN
totalinstallation_postedH:= totalinstallation_postedH+SaleInH.“Construction Fee”;
END;

UNTIL SaleInH.NEXT<=0;

END;

END;
// END OF WITH

/////////////////////////////START OF SALES INVOICE LINE.

IF ((StartDate <> 0D ) AND( EndDate <>0D)) THEN BEGIN
SalesInvLine.SETRANGE(“Posting Date”,StartDate,EndDate);

END;

//SalesInvLine.SETFILTER(“POS Order No.”, ‘<>%1’, ‘’);

IF (ResponsibilityCenter<>’’) THEN BEGIN

SalesInvLine.SETFILTER(SalesInvLine.“Responsibility Center”,ResponsibilityCenter);

END;

WITH SalesInvLine DO BEGIN

IF SalesInvLine.FIND(’-’) THEN BEGIN

REPEAT

totalcostamountSIn:=totalcostamountSIn+(SalesInvLine.“Unit Cost”*SalesInvLine.Quantity);
totalsalesamountSIn:=totalsalesamountSIn+ ( SalesInvLine.“Unit Price”*SalesInvLine.Quantity);

totalLinediscountSIn:=totalLinediscountSIn+SalesInvLine.“Line Discount Amount”;
totalInvdiscountSIn:=totalInvdiscountSIn+SalesInvLine.“Inv. Discount Amount”;

UNTIL SalesInvLine.NEXT<=0;
END;
END;

IF ((StartDate <> 0D ) AND( EndDate <>0D)) THEN BEGIN
SLine.SETRANGE(SLine.“Shipment Date”,StartDate,EndDate);

END;

// SLine.SETFILTER(“POS Order No.”, ‘<>%1’, ‘’);

IF (ResponsibilityCenter<>’’) THEN BEGIN
SLine.SETFILTER(“Responsibility Center”,ResponsibilityCenter);

END;

WITH SLine DO BEGIN

IF SLine.FIND(’-’) THEN BEGIN
REPEAT

IF (SLine.“Document Type”= “Document Type”::Order) THEN BEGIN

totalcostamountSL:=totalcostamountSL+(SLine.“Unit Cost”*SLine.Quantity);
totalsalesamountSL:=totalsalesamountSL+(SLine.“Unit Price”*SLine.Quantity);

totalLinediscountSL:=totalLinediscountSL+SLine.“Line Discount Amount”;
totalInvdiscountSL:=totalInvdiscountSL+SLine.“Inv. Discount Amount”;

END;
UNTIL SLine.NEXT<=0;
END;
END;

///////////////////////////////// Start of General Journal Line
IF ((StartDate <> 0D ) AND( EndDate <>0D)) THEN BEGIN
GLine.SETRANGE(“Posting Date”,StartDate,EndDate);

END;

//GLine.SETFILTER(“POS Order No.”, ‘<>%1’, ‘’);

IF (ResponsibilityCenter<>’’) THEN BEGIN
GLine.SETFILTER(GLine.“Resposibility Center”,ResponsibilityCenter);
END;

WITH GLine DO BEGIN

IF GLine.FIND(’-’) THEN BEGIN

REPEAT

IF( GLine.Amount>0) THEN BEGIN

CASE GLine.“Payment Method Code” OF
‘CASH’:
BEGIN

totalcash_unposted:= totalcash_unposted+GLine.Amount;

END;

‘CREDCARD’:
BEGIN

totalcredit_unposted:= totalcredit_unposted+GLine.Amount;

END;

ELSE BEGIN

totalother_unposted:=totalother_unposted+GLine.Amount;

END;

END;

END;

UNTIL GLine.NEXT<=0;

END;

END;

///////////////////////////////// End of General Journal Line

////////////// End of Sales InVoice Header

//////////////Start of G/L Entry Amount Calculation
IF ((StartDate <> 0D ) AND( EndDate <>0D)) THEN BEGIN
“G/L Entry”.SETRANGE(“Posting Date”,StartDate,EndDate);

END;

//“G/L Entry”.SETFILTER(“POS Order No.”, ‘<>%1’, ‘’);

IF (ResponsibilityCenter<>’’) THEN BEGIN
“G/L Entry”.SETFILTER(“G/L Entry”.“Resposibility Center”,ResponsibilityCenter);
END;

WITH “G/L Entry” DO BEGIN

IF “G/L Entry”.FIND(’-’) THEN BEGIN
REPEAT

IF (“G/L Entry”.Amount>0) THEN BEGIN