Home » Developer & Programmer » Reports & Discoverer » Dinamicaly create and use columns in multiple calculations on the same sheet.
Dinamicaly create and use columns in multiple calculations on the same sheet. Mon, 10 March 2008 10:09
 alexzor Messages: 5Registered: March 2008 Junior Member
Is it possible to create dynamic calculated columns in Oracle Discoverer 9.0.4.00.00 using sheets?
I have a Sheet that I need to edit.

The data columns I have imported on the sheet are:
A) Customer
B) Account Type
C) Start Date
D) Invoice Amount (calculated column. It shows the data in the column already)
E) Total Days (calculated column. System Date – Start Date = Amount of Days. This Amount of days is a variable that I need to use in further calculations. If it’s more than 750 days then customer considered to be “Existing Customer” if less than 750 then it considered o be new.

The columns that I need to be created and calculated on the fly:

FF) New/Existing Customer (If column E (Toal days) is more than 750 than letter “E” has to be displayed in the output. If less than 750 days then letter ”N” has to be displayed. “E” stands for Existing Customer and “N” is stands for New Customer.

GG) Commission percentage.
if column B is “INDIRECT ACCOUNT” and column FF is “N” (N -for New Customer) then Commision Percentage is 1.5%
if column B is “DIRECT ACCOUNT” and column FF is “N” (N - for New Customer) then Commision Percentage is 2.0%
if column B is “INDIRECT ACCOUNT” and column FF is “E” (E - for Existing Customer) then Commision Percentage is 0.5%

HH) Commission \$ Amt = (column D * columnsGG). I guess, in order to find percentage of the number I will need to shift decimal point to 2 places to the left ( 1.5% - 0.015, 2.0% - 0.02, 0.5% - 0.005) and multiply by the number of the total number, which is column D.

I have never done it before. I am having a problem seeing how it is possible to accomplish without IF – THEN statements. If anyone has any suggestions I would really appreciate the help.
Re: Dinamicaly create and use columns in multiple calculations on the same sheet. [message #305521 is a reply to message #305382] Tue, 11 March 2008 03:02
 skooman Messages: 913Registered: March 2005 Location: Netherlands Senior Member
You were close! This can be done using case when else end statements. Details can be found in the SQL reference. One example:

 Quote: FF) New/Existing Customer (If column E (Toal days) is more than 750 than letter “E” has to be displayed in the output. If less than 750 days then letter ”N” has to be displayed. “E” stands for Existing Customer and “N” is stands for New Customer.

Will become something like:
`case when (sysdate-start_date) > 750 then 'E' else 'N' end`

This code you put in a Discoverer calculation.

Re: Dinamicaly create and use columns in multiple calculations on the same sheet. [message #306267 is a reply to message #305521] Thu, 13 March 2008 09:10
 alexzor Messages: 5Registered: March 2008 Junior Member
OK, finally I was able to create needed calculations. Thank you for the help. I appreciate the input.
Here is the code for each calculation/column that I needed.

Column E) “TotalDays”Created new Calculation and inserted following code: SYSDATE-Start Date
----------------
Column FF) ” NewExisting” (To display if customer is new or existing. I needed to have one latter showing)
Created new Calculation and inserted following code: CASE WHEN ( TotalDays ) > 750 THEN 'E' ELSE 'N' END
-------------
Column GG) “Percent” (commission percent rate based on Account type and Customer status)
Created new Calculation and inserted following code:
“CASE WHEN ( Accounttyp ) = 'INDIRECT ACCOUNT' THEN CASE WHEN ( NewExisting ) = 'E' THEN '0.50%' ELSE '1.50%' END WHEN ( Accounttyp ) = 'DIRECT ACCOUNT' THEN CASE WHEN ( NewExisting ) = 'N' THEN '2.00%' ELSE '0.50%' END ELSE '0.50%' END”
-------------------
Column XX) “DecimalShift” Additional column that I made hidden. The purpose is just to shift the decimal point 2 points to the left in order to calculate percentages.)
Created new Calculation and inserted following code:
CASE WHEN ( Accounttyp ) = 'INDIRECT ACCOUNT' THEN CASE WHEN ( NewExisting ) = 'E' THEN 0.05 ELSE 0.015 END WHEN ( Accounttyp ) = 'DIRECT ACCOUNT' THEN CASE WHEN ( NewExisting ) = 'N' THEN 0.02 ELSE 0.05 END ELSE 0.05 END
-------------------
Column HH) “CommTotalAmt” (determins commission amount out out of Invoice Amount)
Created new Calculation and inserted following code: Invoice Amount SUM*DecimalShift

Then I used Format Data option to display some numbers as Currency and others as Percentages. Now I want to display TOTAL at the bottom of “CommTotalAmt” column by using Tools --> Totals --> Creating new Total. In calculate field I tried to choose “Sum” and also tried to use “Cell Sum”. Placement: “Grand total at bottom”. However, no total being calculated except colored line showing under all new columns if I select different color using “Format” button.
In TOTALS window I am unable to create TOTAL for the calculation. It seems like I can only create TOTALS for the item that exists.
Re: Dinamicaly create and use columns in multiple calculations on the same sheet. [message #306269 is a reply to message #306267] Thu, 13 March 2008 09:20
 skooman Messages: 913Registered: March 2005 Location: Netherlands Senior Member
There is a check with totals, that you can only sum values that are "certifiably not null", because in BI world summing null values is always a bit of a tricky thing (think about it: what is the total of unknown value + 1??).

So, apparently, there can be null values in your calculations. You will have to check it in detail to make sure that this can not occur. For example, if you have a column in a table which can be null and you create a calculation on it, always include an NVL with it. Or if you use a case when statement, always include an ELSE part.
Re: Dinamicaly create and use columns in multiple calculations on the same sheet. [message #306282 is a reply to message #306269] Thu, 13 March 2008 09:47
 alexzor Messages: 5Registered: March 2008 Junior Member
As far as I see there is no null values. The column's row results are generated by using number of calculations. Every row has a value which is a dollar amount. Code for this calculation is: Invoice Amount SUM*DecimalShift. Calculation column "Invoice Amount SUM" * Calculation column "DecimalShift"
Please explain further what is NVL and where/how I can specify more code to exclude NULLs?
Thank you very much
Re: Dinamicaly create and use columns in multiple calculations on the same sheet. [message #306296 is a reply to message #306282] Thu, 13 March 2008 10:30
 skooman Messages: 913Registered: March 2005 Location: Netherlands Senior Member
With the information available I'm not able to determine why the total is not showing, sorry. Is there any expert in your company who can have a look at your actual code?

Besides, a small detail I noticed:

 Quote: THEN '0.50%' ELSE '1.50%'

 Quote: THEN 0.05 ELSE 0.015

On my side of the planet that's not the way to calculate with percentages (hint: compare the "translation" you make)
Re: Dinamicaly create and use columns in multiple calculations on the same sheet. [message #306306 is a reply to message #306296] Thu, 13 March 2008 10:57
 alexzor Messages: 5Registered: March 2008 Junior Member
I am the expert .

Column GG) “Percent”
Quote:
THEN '0.50%' ELSE '1.50%'
This column is used simply for displaying purposess. Just to let user know what percentage is used to calculate.

Column XX) “DecimalShift”

Quote:
THEN 0.05 ELSE 0.015

This column is doing actual calculations. User have to see the amount of commissions out of Invoice amount. The percentage determins the commission. This code is absolutely the same except I shifted the decimal point, in values, two points to the left. InvoiceAmount * 0.015 (which is 1.5% fo user) = Commission amount.

Now I just simply need to summarize all rows in "CommTotalAmt" column and display total at the bottom of the column

InvoiceAmount | TotalDays | NewExisting | Percent | CommTotalAmt
\$20,877.80........534...........N...................2.00%.........\$417.56
\$51,240.00........189...........N...................2.00%.........\$1,024.80
\$206,207.71.......918...........E...................0.50%.........\$10,310.39
............................................................................TOTAL:xx,xxx.xx

Thank you

[Updated on: Thu, 13 March 2008 11:02]

Report message to a moderator

Re: Dinamicaly create and use columns in multiple calculations on the same sheet. [message #306342 is a reply to message #306306] Thu, 13 March 2008 16:12
 skooman Messages: 913Registered: March 2005 Location: Netherlands Senior Member
Okay. In that case I suggest you do a good search on metalink. There is a lot of documents on totals in Discoverer and how they behave exactly. I'm pretty sure there must be something in there that applies to the situation you describe.

Furthermore, if you like you can post the exact SQL that this workbook generates and I can have a look at it if you like.
 Previous Topic: Display TOTAL under Column/Calculation. Please help Next Topic: Repeat hedder in excel in the next page
Goto Forum:

Current Time: Fri Jun 23 02:06:55 CDT 2017

Total time taken to generate the page: 0.10944 seconds