Can any one help me?

[tilakrajsingh]

i am using crystal report 8.5 to create a stock statement having stored procedure as data source.

my store procedure brings all data e.g. Opening balance on year starting (e.g. 01-Apr-03) , all receipts (e.g. till date 31-Oct-03)), all issues (e.g. till date 31-oct-03).

i want to print a stock statement for the period of 01-Jun-03 to 30-Sep-03 with Opening balance (where Opening balance will be opening of year + receipt - issue upto 31-May-03)

i am using formula for report printing e.g. TRANDATE in (?sDate) to (?eDate). Report is grouped on item, sorted on date.

I am able to print all required data for 01-June-03 to 30-Sep-03 BUT NOT ABLE TO calculate OPENING BALANCE...
HOW CAN I DO IT....??

OR ANY OTHER METHOD??? (my stored procedure using three tables : ITEM, GRNOTE, ISSUE with union statement.)

Can you help me please???
Thanks to all of you in advance to support me to get my project done....

Tilak Raj

 

[synapsevampire]

You state that the SP is returning the opening balance, yet you cannot calculate the opening balance.

If your Stored Procedure is returning all of the data, then modify your SP to do so.

If you need help with SQL, I would suggest posting the SQL being used in the approrpiate SQL forum.

I would also add in example data and expected ouput.

If you already do have the required data in the existing rowset being returned, please post example data and expected output here.

-k

 

[tilakrajsingh]

Here is my SP :-

*********
create procedure StockVouchers
as

SELECT akki.GRNHead.GRN_ID as VoucherId, akki.Document.Doc_Type as DocType, akki.GRNHead.GRN_Type as VoucherType, akki.GRNHead.GRN_Unit as VoucherUnit, akki.GRNHead.GRN_VNCD as VoucherVNCD, akki.GRNHead.GRN_Date as VoucherDate,
akki.GRNDetail.GRN_ProdID as Product , akki.GRNDetail.GRN_WtAcc as Weight, akki.GRNDetail.GRN_QtyAcc as Qty, akki.GRNDetail.GRN_UnitVal as Rate, akki.UOM.UOM_Type as PS,
akki.Product.Prod_Type as PRType, akki.Product.Prod_Cat as PRCat, akki.Product.Prod_Name as PRName , akki.Product.Prod_UOM as UOM, akki.Company.Co_Name as CompanyName, akki.Company.Ac_SDate as SDate, akki.Company.Ac_EDate as EDate,
akki.Opening.
FROM akki.GRNHead INNER JOIN
akki.GRNDetail ON akki.GRNHead.GRN_ID = akki.GRNDetail.GRN_ID INNER JOIN
akki.Product ON akki.GRNDetail.GRN_ProdID = akki.Product.Prod_Code INNER JOIN
akki.UOM ON akki.Product.Prod_UOM = akki.UOM.UOM_Code INNER JOIN
akki.Document ON akki.GRNHead.GRN_Type = akki.Document.Doc_ID INNER JOIN
akki.Company ON akki.GRNHead.GRN_Unit = akki.Company.Co_Id inner join
akki.Opening on akki.GRNDetail.GRN_ProdID = akki.Opening.Pr_cd

union

SELECT akki.IssueHead.Is_Id, akki.Document.Doc_Type, akki.IssueHead.Is_Type, akki.IssueHead.Is_Unit, akki.IssueHead.Cl_Id, akki.IssueHead.Is_Date, akki.IssueDetail.Prod_Id,
akki.IssueDetail.WtIs, akki.IssueDetail.QtyIs, akki.IssueDetail.UnitRate, akki.UOM.UOM_Type,
akki.Product.Prod_Type, akki.Product.Prod_Cat, akki.Product.Prod_Name, akki.Product.Prod_UOM, akki.Company.Co_Name, akki.Company.Ac_SDate, akki.Company.Ac_EDate
FROM akki.Document INNER JOIN
akki.Company INNER JOIN
akki.IssueHead INNER JOIN
akki.IssueDetail ON akki.IssueHead.Is_Id = akki.IssueDetail.Is_Id ON akki.Company.Co_Id = akki.IssueHead.Is_Unit ON
akki.Document.Doc_ID = akki.IssueHead.Is_Type INNER JOIN
akki.Product ON akki.IssueDetail.Prod_Id = akki.Product.Prod_Code INNER JOIN
akki.UOM ON akki.Product.Prod_UOM = akki.UOM.UOM_Code

****************
This returns all rows from Product, Grnote, Issue.

Can you please send some tips how to calculate the Opening using the same SP.

Tilak Raj