Categories
- All Categories
- 75 Oracle Analytics News
- 7 Oracle Analytics Videos
- 14K Oracle Analytics Forums
- 5.2K Oracle Analytics Idea Labs
- Oracle Analytics User Groups
- 40 Oracle Analytics Trainings
- 59 Oracle Analytics Data Visualizations
- 2 Oracle Analytics Data Visualizations Challenge
- 3 Oracle Analytics Career
- 4 Oracle Analytics Industry
- Find Partners
- For Partners
Field not giving Total even after conversion to a number
Hi,
I have a string field (values are numbers) which I converted to Double in the formula .
Totaling the field not giving a total why ?
Answers
-
Hi @User_AD53Q ,
I suspect that your column is treated as an attribute, hence the tool is not calculating any total.
You have to force the tool to treat your column as a measure in order to display totals. To do this you can replace your column expression as follows:
CASE WHEN 1=0 THEN <Put any measure here> ELSE <Put your current expression here> END
Do not forget to set up the Aggregation Rule (Totals Row) as well.
0 -
Thanks for the reply yes the field is varchar in RPD
I edited the Formula as : Cast("ColumnName" as Double) and still it didn't aggregate.
what is <Put any measure here> ? I have only one column, there is no need for Two conditions here...
the only values this column has in 'null' as a string and numbers as a string (null was filtered out on the report level
so All I have is numeric values as string and casting should solve the problem .
0 -
so All I have is numeric values as string and casting should solve the problem .
Not really, and that's what Federico was saying: you can cast all you want, but if you don't tell OBIEE/OAS that the column is a measure, the tool isn't going to aggregate anything.
Did you tell the tool that column is a measure and should be aggregated (and telling the tool how to aggregate it) ?
What Federico posted with the `<Put any measure here>` is a way to trick the tool, because the first condition of the CASE WHEN contains a measure (and not an attribute), the tool will think this column (with this expression) is also a measure because the formula is returning a measure. Of course your measure will never be returned because of the 1=0 condition which is always FALSE, and therefore only your ELSE expression is returning and the tool will also consider it as being a measure.
Ideally you move all that into the RPD and don't have to trick the tool into thinking anything and just mark that as a real measure. Move the CAST into the mapping to the physical column and job done: the tool will see numbers and a measure by default.
0 -
Hi @User_AD53Q ,
@Gianni Ceresa already clarified my answer and hope you were able to fix your issue.
The fundamental thing is that your column must be displayed with a yellow icon next to it in the Criteria tab of the Analysis. The yellow icon means that the column will be treated as measure, values will be aggregated according to other attributes in the analysis and the total will be displayed.
Please let us know if you need further information.
0 -
Hi,
Thank you all so kind of you .
It didn't resolve my issue, see pictures:
0 -
Hi @User_AD53Q ,
The column "Fact"."FTE" is an attribute and not a measure. You can clearly see this from the icon displayed next to it (which is blue and not yellow).
You can either replace the current expression with something like SUM("Fact"."FTE"), or set up the default aggregation in the RPD directly. In both cases you don't need to use the CASE syntax.
0 -
I used the SUM with Casting as follows : SUM(cast("Fact"."FTE" as DOUBLE))
And it worked. its not possible to SUM without casting ..
Thank you !
0
