OraFAQ Forum: Reports & Discoverer

Home » Developer & Programmer » Reports & Discoverer » MATRIX REPORT

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

MATRIX REPORT [message #525356]

Sat, 01 October 2011 10:06

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.

Attachment: ALLCUSTALLREPORT.png
(Size: 191.96KB, Downloaded 961 times)

Report message to a moderator

Re: MATRIX REPORT [message #525380 is a reply to message #525356]

Sat, 01 October 2011 13:35

Littlefoot
Messages: 21807
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.

Report message to a moderator

Re: MATRIX REPORT [message #525403 is a reply to message #525380]

Sun, 02 October 2011 09:29

ranamirfan
Messages: 535
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

Report message to a moderator

Re: MATRIX REPORT [message #525472 is a reply to message #525403]

Mon, 03 October 2011 07:21

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

Report message to a moderator

Re: MATRIX REPORT [message #525474 is a reply to message #525472]

Mon, 03 October 2011 07:23

naeemrazaacma
Messages: 6
Registered: January 2011
Location: multan

Junior Member

IF U GIVE ME TIME I CAN ONLINE AND THROUGH TEAM VIEWER;

Report message to a moderator

Re: MATRIX REPORT [message #525479 is a reply to message #525474]

Mon, 03 October 2011 07:35

ranamirfan
Messages: 535
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

Report message to a moderator

Re: MATRIX REPORT [message #525483 is a reply to message #525479]

Mon, 03 October 2011 08:00

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;

Report message to a moderator

Re: MATRIX REPORT [message #525485 is a reply to message #525483]

Mon, 03 October 2011 08:06

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

Report message to a moderator

Re: MATRIX REPORT [message #525486 is a reply to message #525485]

Mon, 03 October 2011 08:10

naeemrazaacma
Messages: 6
Registered: January 2011
Location: multan

Junior Member

I AM VERY THANKS FULL DEAR GURU.

Report message to a moderator

Re: MATRIX REPORT [message #525491 is a reply to message #525486]

Mon, 03 October 2011 08:25

ranamirfan
Messages: 535
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

Report message to a moderator

Re: MATRIX REPORT [message #525555 is a reply to message #525491]

Mon, 03 October 2011 13:41

Littlefoot
Messages: 21807
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.

Report message to a moderator

Previous Topic:	one report two pages
Next Topic:	Need solution for matrix report formating

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 19 12:07:09 CDT 2024