Home » Developer & Programmer » Reports & Discoverer » MATRIX REPORT
MATRIX REPORT [message #525356] Sat, 01 October 2011 10:06 Go to next message
naeemrazaacma
Messages: 6
Registered: January 2011
Location: multan
Junior Member
HI BOSS!


BOSS ! I MAKE A MATRIX REPORT (SALES) ALL CUSTOMER ALL PRODUCTS SUCCESSFULLY BUT NOW I WANT TO ADD A COLUMN NET BALANCE OF CUSTOMER. BUT I AM FAIL TO GET NET BALANCE FROM ACCOUNTS TABLE.

MY TABLES WHERE I GET INFORMATION THESE
SALEM----SALE MASTER=== INVNO,INVDATE,ACODE REFERNCES BY ACC(TABLE)
SALED----SALE DETAIL=== ITEMCODE REFERNCES BY ITEM(TABLE),QTY,AMOUNT
ITEM-----ITEM======ITEMCODE,ITEMNAME
ACC------ACCOUNT REGISTRATION ---------ACODE,AHEAD
ACCDET---ACCOUNT DETAIL-----------ACODE,DR,CR
ACCMAS---ACCOUNT MASTER-------INVNO,INVDATE
THESE ARE MY TABLES AND FIELDS OF TABLES.


MATRIX SALES REPORT THIS QUARY I USE FOR MATRIX I GET ACCURATE SALES REPORT WHEN I ADD ACCOUNT INFORMATION THEN ITS SHOW BALANCE BUT WRONG AND SAME BALANCE FOR ALL CUSTOMER.

SELECT ALL SALED.ITEMCODE,
ITEM.ITEMNAME,
SALED.QTY,
SALED.AMOUNT,
SALEM.ACODE,
ACC.AHEAD
FROM ACC,ITEM,SALED,SALEM
WHERE SALEM.INVNO=SALED.INVNO
AND SALEM.ACODE=ACC.ACODE
AND ITEM.ITEMCODE=SALED.ITEMCODE;

I USE COLUMN FORMULA

function CF_1Formula return Number is
DRS NUMBER;
begin
SELECT SUM(DR) INTO DRS FROM ACCDET
WHERE ACCDET.ACODE=ACODE;
RETURN DRS;


end;

BUT RESULT IS WRONG---PLZ HELP ME.







Re: MATRIX REPORT [message #525380 is a reply to message #525356] Sat, 01 October 2011 13:35 Go to previous messageGo to next message
Littlefoot
Messages: 21549
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Quote:
ITS SHOW BALANCE BUT WRONG AND SAME BALANCE FOR ALL CUSTOMER.

SELECT SUM(DR) INTO DRS FROM ACCDET
WHERE ACCDET.ACODE=ACODE;

Maybe because WHERE clause misses customer identification.
Re: MATRIX REPORT [message #525403 is a reply to message #525380] Sun, 02 October 2011 09:29 Go to previous messageGo to next message
ranamirfan
Messages: 534
Registered: January 2006
Location: Pakistan / Saudi Arabia
Senior Member

Dear,

Create a Function and used it in a main query.
Create or Replace Function Get_Acct_Sum(AccCode in Number) Return Number is

Drs Number;
Begin
Select  Sum(Nvl(DR,0)) Into Drs 
From AccDet
Where  AccDet.ACode=AccCode;
Return Drs;
End;

Then Use this function here.
SELECT Saled.ItemCode,
   Item.ItemName,

   Get_Acct_Sum (Acc.ACode)Acct_Amount,

   Saled.Qty,
   Saled.Amount,
   Saled.Acode,
   Acc.Ahead
FROM Acc,
 Item,
 Saled,
 Salem

WHERE Salem.InvNo=Saled.Invno
  AND Salem.Acode=Acc.Acode
  AND Item.ItemCode=Saled.ItemCode;
 


Try it.


Regards,
Irfan

Re: MATRIX REPORT [message #525472 is a reply to message #525403] Mon, 03 October 2011 07:21 Go to previous messageGo to next message
naeemrazaacma
Messages: 6
Registered: January 2011
Location: multan
Junior Member
SORRY DEAR THIS FUNCTION CANNOT CREATE NOW. AND REPEATDLY SHOWING MESSAGE UNCOMIPLE REP-1217 WHEN I TRY TO COMPILE
Re: MATRIX REPORT [message #525474 is a reply to message #525472] Mon, 03 October 2011 07:23 Go to previous messageGo to next message
naeemrazaacma
Messages: 6
Registered: January 2011
Location: multan
Junior Member
IF U GIVE ME TIME I CAN ONLINE AND THROUGH TEAM VIEWER;
Re: MATRIX REPORT [message #525479 is a reply to message #525474] Mon, 03 October 2011 07:35 Go to previous messageGo to next message
ranamirfan
Messages: 534
Registered: January 2006
Location: Pakistan / Saudi Arabia
Senior Member

Dear,
Please post a copy and paste of a run of what you did from SQL*Plus.


Regards,
Irfan
Re: MATRIX REPORT [message #525483 is a reply to message #525479] Mon, 03 October 2011 08:00 Go to previous messageGo to next message
naeemrazaacma
Messages: 6
Registered: January 2011
Location: multan
Junior Member
DEAR IRFAN SB,
I CREATE FUNTION BUT IN REPORT ITS SHOWING ERROR. I PASTE WHICH U SUGGEST.
create or replace
FUNCTION GET_ACCT_SUM(ACCCODE IN NUMBER) RETURN NUMBER IS
DRS NUMBER;
BEGIN
SELECT (NVL(DR,0)) INTO DRS FROM ACCDET
WHERE ACCDET.ACODE= ACCCODE;
RETURN NULL;
END GET_ACCT_SUM;

Re: MATRIX REPORT [message #525485 is a reply to message #525483] Mon, 03 October 2011 08:06 Go to previous messageGo to next message
naeemrazaacma
Messages: 6
Registered: January 2011
Location: multan
Junior Member
ORA-01422:EXACT FETCH RETURNS MORE THAN REQUESTED NUMBER OF ROWS
ORA-06512: AT "AZIZENTER.GET_ACCT_SUM",LINE 4 ====>GET_ACCT_SUM(ACC.ACODE)ACCT.AMOUNT,

NOW I AM FACING THIS PROBLEM

Re: MATRIX REPORT [message #525486 is a reply to message #525485] Mon, 03 October 2011 08:10 Go to previous messageGo to next message
naeemrazaacma
Messages: 6
Registered: January 2011
Location: multan
Junior Member
I AM VERY THANKS FULL DEAR GURU.
Re: MATRIX REPORT [message #525491 is a reply to message #525486] Mon, 03 October 2011 08:25 Go to previous messageGo to next message
ranamirfan
Messages: 534
Registered: January 2006
Location: Pakistan / Saudi Arabia
Senior Member

Dear,
http://www.orafaq.com/forum/t/88153/0/
Please read & Follow the Posting guidelines as stated in above URL.

Create this Function in DataBase not in Reports.

Quote:

create or replace
FUNCTION GET_ACCT_SUM(ACCCODE IN NUMBER) RETURN NUMBER IS
DRS NUMBER;
BEGIN
SELECT (NVL(DR,0)) INTO DRS FROM ACCDET
WHERE ACCDET.ACODE= ACCCODE;
RETURN NULL;
END GET_ACCT_SUM;


You did not used Sum function - Sum(Nvl(DR,0)).


Regards,
Irfan
Re: MATRIX REPORT [message #525555 is a reply to message #525491] Mon, 03 October 2011 13:41 Go to previous message
Littlefoot
Messages: 21549
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Besides, it appears that you are trying to return NULL. Are you sure you want to do that? I suppose you should return DRS instead.
Previous Topic: one report two pages
Next Topic: Need solution for matrix report formating
Goto Forum:
  


Current Time: Mon Apr 06 01:24:54 CDT 2020