OraFAQ Forum: Reports & Discoverer » Problem With Calculated Field

Home » Developer & Programmer » Reports & Discoverer » Problem With Calculated Field

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

Problem With Calculated Field [message #174035]

Thu, 25 May 2006 12:34

serafo
Messages: 24
Registered: May 2006

Junior Member

Good Morning.

I am trying to build the report show in the file I Sent. I will try to explain , my english is poor.

The column "Ejecucion Anterior" is a calculated field using the LAG function as Follows :

LAG(Saldocapital AVG,1) OVER(PARTITION BY Producto.Grupo Productos,Producto.Producto,Responsable.Filial,Responsable.Zona,Responsable.Ciudad,Responsable.Responsable ORDER BY Tiempo.Fecha ASC , Tiempo.Numero Mes ASC , Tiempo.Trimestre ASC )

It works fine. For example, for the row "Cuenta Corriente" , the "Ejecucion Anterior" value is the value for the "Trimestre 1". The user is searching for information in "Trimestre 2".

The "Variacion $" column is a calculated field as follows:

"Ejecucion Actual" - "Ejecucion Anterior". It works fine too.

The "Variacion %" column must be a calculated field as follows:

("Ejecucion Actual" - "Ejecucion Anterior") / "Ejecucion Anterior"
or
"Variacion $" / "Ejecucion Anterior"

When I tried to build this formula, I always got the error :

ORA-01476 : Divisor is equal to zero.

The values of column "Variacion %" shown in the figure are calculated as follows :
"Variacion $" / "Presupuesto AVG"

and there is no error.

The column "Presupuesto AVG" is not a Calculated Field, is the only difference.

Could you help me please..!

Attachment: Variacion Porcentual.JPG
(Size: 34.32KB, Downloaded 810 times)

Report message to a moderator

Re: Problem With Calculated Field [message #174497 is a reply to message #174035]

Mon, 29 May 2006 07:15

skooman
Messages: 913
Registered: March 2005
Location: Netherlands

Senior Member

Hi Serafo,

You could try to avoid the error by making sure that the "down" part of the division can never be null or zero. You can do this using a decode or a case when else:

decode("Ejecucion Anterior",null,0,"Ejecucion Actual" - "Ejecucion Anterior") / "Ejecucion Anterior")

Regards,
Sabine

Report message to a moderator

Re: Problem With Calculated Field [message #175883 is a reply to message #174497]

Mon, 05 June 2006 18:47

serafo
Messages: 24
Registered: May 2006

Junior Member

Thanks a lot Sabine.

It works fine. Now I have a question, I hope you can help me as always.

The data that I am presenting is too big, i.e 9158245963.36
I was trying to format this in order to show the values in millions, ie 158245963.36 -->9.158.

I haven't been able to do it, please help me.

Best Regards,

Ser@fo

Report message to a moderator

Re: Problem With Calculated Field [message #176270 is a reply to message #175883]

Wed, 07 June 2006 10:16

skooman
Messages: 913
Registered: March 2005
Location: Netherlands

Senior Member

Always welcome... Wink

SQL> select round(9158245963.36/1000000000,3) from dual;

ROUND(9158245963.36/1000000000,3)
---------------------------------
                            9.158

Regards,
Sabine

Report message to a moderator

Re: Problem With Calculated Field [message #176275 is a reply to message #176270]

Wed, 07 June 2006 11:48

serafo
Messages: 24
Registered: May 2006

Junior Member

Thanks as always Sabine .

I dont's want to create calculated fields to do this....is it possible applying some format, for example in excel the contains the complete value but with the format #.###.. it shows the value in millions.

Thanks Sabine, thanks a lot.

Ser@fo Razz

Report message to a moderator

Re: Problem With Calculated Field [message #176648 is a reply to message #176275]

Fri, 09 June 2006 01:24

skooman
Messages: 913
Registered: March 2005
Location: Netherlands

Senior Member

Hi Serafo,

I've looked through all the format models I know, but haven't found the one you need. Sorry! Maybe someone else?

Regards,
Sabine

Report message to a moderator

Previous Topic:	Report 10g Runtime ( RWRUN.EXE) Does not run
Next Topic:	CRYSTAL REPORTS working with Oracle Applications.

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Nov 26 12:22:03 CST 2024