My requirement is to aggregate all the levels into single column to show in report.
What do you mean? A hierarchical column where you can drill into the data?
Please be precise about your requirements so proper help can be provided.
I concur with Christian, more detail please.
OBIEE does not provide rollup aggregation, if your data source is a database table / view then you would need to add the additional levels as explicit column(s) in that table, then default sum aggregation behaviour on those columns will do the rest for you, and if you can put them together in a data hierarchy then you will also get a drill behaviour similar in principal to MOLAP.
Alternatively but less performant you can also join to your fact table's base level members to a hierarchy table, base to base, and make the table self join in tree walking fashion, not as fast as the above option which flattens your hierarchy, but if you don't have the luxury of changing your ETL and can live with the performance then it is an option.
So you just have a hierarchy that's ragged and skip-level. Still it's just a normal hierarchy object in the RPD:
Thanks again Christian. I looked into this, my requirement is little different, i.e., I don't want hierarchal view and then click + symbol to expand to see further. When I place acct column with measure, it should aggregate to all the levels and also we are going to create filters using this column.
Thanks Again for you time and inputs.
Hello gurus any suggestions?
What you "want" is flattening the hierarchy...
Then again I put "want" into quotes sicne that already smells like you're already fixed on HOW you want to do things (compared to WHAT you want to do - i.e. fulfilling the requirement / business need).
Also your "example" with the "measures" doesn't help at all since your made up numbers look like they are non-additive and just show the value for a member in the level.
tl;dr - if you can't describe your requirement precisely and correctly we will have a hard time helping you with any pertinent solution. Be precise.
only 1 measure value for all levels? as it joins like that
Unfortunately that is the requirement.
I still don't see how that is NOT just a hierarchical column and a measure next to it...
I agree, it is hierarchical column but user doesn't want to open each level. When report runs default all level should populate as shown above.
How does that have any bearing on anything? If you open all the levels of a hierarchical column when BUILDING the analysis in Answers, then that STATE will be saved in the analysis definition and always rendered like that!
The tool is around since some time so it actually know a thing or two and isn't the dumb "list reporting" tool most people use it as...
It's all in the XML definition. Insisting on "different" approaches rather than using things as they are designed just wastes time and resources and pointlessly creates error sources and things you need to maintain and administer without any added value.
<saw:report xmlns:saw="com.siebel.analytics.web/report/v1.1" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlVersion="201201160" xmlns:sawx="com.siebel.analytics.web/expression/v1.1">
<saw:criteria xsi:type="saw:simpleCriteria" subjectArea=""PC2"">
<saw:column xsi:type="saw:hierarchicalColumn" hierarchyID="Segments" tableName=""Customers"" dimensionID="Customers" columnID="c7ccf64c77fb2dbe7">
<saw:hierarchyLevel levelID="Cust Segment"/>
<saw:view xsi:type="saw:compoundView" name="compoundView!1">
<saw:view xsi:type="saw:titleView" name="titleView!1"/>
<saw:view xsi:type="saw:pivotTableView" name="pivotTableView!1" scrollingEnabled="false" rowsPerPage="50000">
<saw:edge axis="page" showColumnHeader="true"/>
<saw:edge axis="row" showColumnHeader="true">
<saw:edgeLayer type="column" columnID="c7ccf64c77fb2dbe7">
<saw:selectionGroup columnID="c7ccf64c77fb2dbe7" groupID="0"/></saw:selectionGroups></saw:drillStep></saw:drillState></saw:edgeLayer></saw:edgeLayers></saw:edge>
<saw:edge axis="column" showColumnHeader="rollover"/></saw:edges>
Edit: not sure how that picture ended up inside the code