| Dinamicaly create and use columns in multiple calculations on the same sheet. [message #305382] |
Mon, 10 March 2008 10:09  |
alexzor
Messages: 5
Registered: 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: 913
Registered: 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: 5
Registered: 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.
Please help. Thanks a lot.
|
|
|
|
| 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: 913
Registered: 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: 5
Registered: 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 #306306 is a reply to message #306296] |
Thu, 13 March 2008 10:57 |
alexzor
Messages: 5
Registered: 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: 913
Registered: 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.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
(hint: compare the "translation" you make)