WebI Hierarchy Issue

[Former Member]

Hi Experts,

Need your help in resolving the hierarchy issue in WebI report pls., I've created a hierarchy path in a universe through IDT tool with the following levels,

Article -> Subclass -> Class -> Department -> Site -> Division.

When I create a WebI report on top of this universe and do a Start Drill option, the drill up option reflects on the same column. Rather I would require it to appear in levels of hierarchy in the expanded format. Attaching the screenshots for reference. Thank You in advance.

Regards,

Karthik.Ch

[vinay_lohakare5 · Accepted solution]

Hi,

No screen shot attached.

your webi report in built on BEx or oracal DB?

Thanks,

Vinay

[Former Member · Accepted solution]

Hi,

The WebI report is built from a universe.

Below are the screenshots for your reference.

Regards,

Karthik.Ch

[Matthew_Shaw · Accepted solution]

The 'drill' feature you are using is really 'drill down and replace the existing object with another'.

What you need the a 'drill' feature that expands to the next level.

This feature is provided with Web Intelligence when connecting to BW using the new BICS connection available in BI4. When you connect in this way, you don't see any universe. (we actually create on on the fly everytime and throw it away),

If you go to http://www.sdn.sap.com/irj/scn/web-intelligence-elearning  and then scroll down you'll see a couple of free training "NEW for 4.0  Create a prompt for hierarchical data" and "Divide a report with hierarchical data into sections" which will show exactly what you're after! (At least I think so!)

This type of functionality is available when Web Intelligence connects to OLAP sources through either:

- BICS connection to BW

- OLAP universe (using the new Semantic Layer UNX) connecting to MS Analysis Services and soon Essbase I think is planned too.

Helps?

[Matthew_Shaw · Accepted solution]

I see there's quite a few more tutorials about "hierarchical" data structures, which is what you're after. Search on that link for "hierarchical" and follow the links! All rather cool features!

[Former Member · Accepted solution]

Hi,

Thank You for your valuable comments. Here I'm using HANA as the backend object from which the universe is built. To explain in detail, I've created a calculation view with union function in HANA studio and the universe is built on it.

I randomly selected few objects to create a hierarchy path in the universe and I want the similar scenario to be reflected in WebI report.

Regards,

Karthik.Ch

[Matthew_Shaw · Accepted solution]

So, you're connecting to a database, in this case HANA, using SQL. This hierarchial feature isn't applicable when connecting 'relationally'.

The best we can provide for you is the 'fold/unfold' function, sometimes called 'expand and collapse'. here you create a number of sections and breaks. You can then select the option called something like 'show outline' and you can then 'fold/unfold' (expand and collapse) the sections and breaks. (only works for vertical breaks, not horizontal ones you get with a crosstab).

The 'hierarchy' you have defined doesn't need to be defined. What you 'expand' and 'collapse' is how you've defined the sections and breaks within the report (there's no dependancy of the hierarchy per say). so you could have different 'drill' paths (really expand/collapse paths) on different report (tabs).

Regards, Matthew

[Former Member · Accepted solution]

Hi,

Thank You for your valuable comments. It would be great if you could share any How To document for the above mentioned solution. Thank you once again.

Regards,

Karthik.Ch

[vinay_lohakare5 · Accepted solution]

Hi Karthik,

Check my blog:

http://scn.sap.com/community/businessobjects-web-intelligence/blog/2012/05/04/hierarchy-in-webi-as-d...

Thanks,

Vinay

[Former Member · Accepted solution]

Hi,

I managed to create the hierarchy with the levels Division -> Region -> District -> Store using the break option and fold / unfold using option "Outline" in WebI 4.0 version, which is working fine. However when I try to include the free characteristics in the result set, it's not allowing,

For instance, when I try to include the Fiscal Week from the left pane to result set in the right pane using drag and drop, it's not allowing. Is there any settings I need to manage, as I see the inclusion of fiscal week is not working after using the fold / unfold option. When the break / fold, unfold option weren't used earlier, I didn't face this issue. Thank You.

Regards,

Karthik.Ch

[Matthew_Shaw · Accepted solution]

Bit tricky to really understand. Try this:

Create a new report (tab) within the same document and add all the objects into one block (table).

Does this work ok?

If so, then add your breaks as necessary.

If not, then the 'dimension' 'Fiscal Week' isn't related to the other objects. How many queries (data providers) do you have within this document?

[Former Member · Accepted solution]

Hi,

I did try that option too, but in vain. Between, I'm using only 1 query here.

Regards,

Karthik.Ch

[Matthew_Shaw · Accepted solution]

Hello Karthik,

So you still have a problem I think.  You have only 1 query, ok.

So, now for that one query, how many SQL statements has it produced? (view SQL will tell you).

(I'm looking to see if there is a query for 'Fiscal Week' only, and a different query for all the other objects) If you have multiple SQL, could you copy/paste all the SQL into here?

Regards, Matthew

[Former Member · Accepted solution]

Hi Matthew,

Looks like it has only 1 SQL statement. Below is the SQL script for your reference,

SELECT

  "_SYS_BIC"."zthd/HIER_SALES"."0MATERIAL",

  "_SYS_BIC"."zthd/HIER_SALES"."YFISCWEEK",

  "_SYS_BIC"."zthd/HIER_SALES"."DIVISION",

  "_SYS_BIC"."zthd/HIER_SALES"."REGION",

  "_SYS_BIC"."zthd/HIER_SALES"."DISTRICT",

  "_SYS_BIC"."zthd/HIER_SALES"."PLANT",

  SUM("_SYS_BIC"."zthd/HIER_SALES"."SALES_UNIT"),

  SUM("_SYS_BIC"."zthd/HIER_SALES"."SALES_DOLLARS")

FROM

  "_SYS_BIC"."zthd/HIER_SALES"

WHERE

  "_SYS_BIC"."zthd/HIER_SALES"."0MATERIAL"  IN  @prompt('Enter values for Article:','A','Zthd Hier Sales\Article',Multi:Any,Free,Persistent,,User:0,Optional)

GROUP BY

  "_SYS_BIC"."zthd/HIER_SALES"."0MATERIAL",

  "_SYS_BIC"."zthd/HIER_SALES"."YFISCWEEK",

  "_SYS_BIC"."zthd/HIER_SALES"."DIVISION",

  "_SYS_BIC"."zthd/HIER_SALES"."REGION",

  "_SYS_BIC"."zthd/HIER_SALES"."DISTRICT",

  "_SYS_BIC"."zthd/HIER_SALES"."PLANT"

Regards,

Karthik.Ch

[Matthew_Shaw · Accepted solution]

Well that has 'foxed' me ! I don't understand why you're unable to add 'Fiscal Period' into the same block as all the other objects. I don't see any problem. How strange! Sorry I've ran out of ideas right now.