1 2 3 10 Previous Next

SAP BusinessObjects Web Intelligence

145 Posts


The starting situation is as follows. I've used multiple columns (universe -> odbc -> excel) for my organizational units (main department, department, group, cost center).

For Filtering the database I've set up some command prompts as you can see in the following picture.


prompts.png


 

For a dynamic chart label showing you on which layer you are at the moment depending on what you've chosen at the prompts:

 

Right Click on the chart you want to format -> Format Chart… -> Title -> Title Label -> fill in the statement below!



="YOUR_TEXT " + UserResponse("Main Department:") + (If UserResponse("Main Department:") = "" Then "" Else (If UserResponse("Department:") <> "" Or UserResponse("Group:") <> "" Or UserResponse("CostCenter:") <> "" Then ", " Else "")) + UserResponse("Department:") + (If UserResponse("Department:") = "" Then "" Else (If UserResponse("Group:") <> "" Or UserResponse("CostCenter:") <> "" Then ", " Else "")) + UserResponse("Group:") + (If UserResponse("Group:") = "" Then "" Else (If UserResponse("CostCenter:") <> "" Then ", " Else "")) + UserResponse("CostCenter:")

 

 

You can easily port this method for every dimension you want to be shown in the chart label by using prompts for other dimensions and adapt the statement for example like this: dimension KPI with prompt text "choose kpi:" -> UserResponse("choose kpi:")

 

That's it. You now have a dynamically changing chart label depending on what you've chosen at the prompts.

Hari Dutt

Dynamic Headers in WebI

Posted by Hari Dutt Sep 15, 2014

Let me know i can show column values as Headers Names With multiple columns.

 

For example

 

 

Item    Module_Name   Qty      CalculatedQty

1         P1                     1          1

2         P2                     1          1

3         P3                     1          1

4         P4                     1          1

 

 

Actually Required

 

Item                    P1                P2             P3         P4   CalculatedQty

111
211
311
411

In my project, I did face a requirement of sorting month in charts.

Let's create a simple report to demonstrate this. For this example we will be using two objects. in report

 

1. Month Name

2. Number of incident

 

When we create the column chart  using above objects as shown below :

 

 

Month.PNG

 

Requirement : Sorting Month Name in Chart.

 

Create a variable in report.

 

Month_Number:-   If[Month]=April Then "4" ElseIf[Month]=May Then "5" ElseIf[Month]=June Then "6" ElseIf[Month]=July Then "7" Else [Month]

 

Step 1: Assign Data in chart section

               Select below values

Month.PNG

Step 2:

 

Hide 'Month Number'

Month.PNG

 

Step 3: Right click on the chart and select 'Sort' option

 

Sort -> Manage Sorts ->Add

 

Month.PNG

Add Month Number and select order  as Ascending

 

 

You can then see the chart output as shown below :

 

 

Month.PNG

 

Hope this helps

 

Regards,

Pranay

Hi Team ,

This is a trick to use BW multi language in Webi reports .

Environment : ECC6,SAP BW ,SAP BI 4.X UDT/IDT .

 

In BW Language pack deployed and Master data of objects maintained in different languages(From ECC).In BW they have option to choose language when log on , data and screen will get change as per language selected.

 

In BO , we have option to pass only one language in universe connection .By default we'll passing EN .So, though we have data maintained in multiple languages in BW ,we'll not be leveraging it for reporting in BO.

 

Possible Solution :

 

Scenario 1: Use Universe with SSO as connection type .Login to Launchpad with user and change Preferred Viewing Locale to see metadata and data in different languages .


In BI 4.1 SP3 ,we will get Codepage error .It is a bug in BI 4.1 .Please find SAP Note 2037630. It is not yet fixed either in  SAP BI 4.1 SP4 patch2(Latest Verion)  or SAP BI 4.1 SP3 Patch2 (our current version)

So we wanted to look for another solution which will work with universe.


Scenario 2 : With Manage Access Restrictions feature we can switch toggle between the connections based on user login.

Assume you have a ES_SUPPORT Group/User , to whom Spanish data required when logged in.

 

Scenario 3: Use BICS connection with SSO as connection type.Login to Launchpad with user and change Preferred Viewing Locale to see metadata and data in different languages .

Since we use Unv only in our project . we are not using this solution.


Procedure for Scenario 2:

1.Create a connection Cnn_MATERIAL_TEST on top of BEx query MATERIAL_TEST with default language EN.

2.Create universe with the connection and Create Webi report and save.You'll get only English data.

3.Create another connection Cnn_MATERIAL_TEST_ES on top of BEx query MATERIAL_TEST with language key ES for Spanish language .

4.Universe ->Tools ->Manage Security ->Manage Access Restrictions

5.Click New ,Enter Name: ES Language .From Connections drop down , select Cnn_MATERIAL_TEST_ES

6.From right hand side ,click Add user or group . Select ES_SUPPORT (user/group , for which different language data required)

7.from left side click Access Restriction ES Language , from right side click ES_SUPPORT . click on Apply.


Now log on to to Launchpad with ES_SUPPORT and refresh the report .You'll see data maintained in Spanish .

we can do same in IDT on top of cube in Data security .

 

Limitation /Considerations:

 

* Not possible with OLAP connections , universe is mandatory.

* Creating multiple connections for universe(per BEx query).

* Need to maintain user groups based on language in BO.

* Need to create Access Restrictions in Universe.

 

Hope someone find this helpful .Please share if there is another way of doing it.

Using the drillfilters() function, we can see where we are in a hierarchy when drilling is enabled. However, sometimes you may want to format report elements or data based on your whereabouts in the hierarchy. This is very easy to do and can be an effective mechanism.

 

For this example we will be using E-Fashion and the State>City>Store name hierarchy. We create a query returning just these objects:

Efashion Hierarchy Query.JPG

 

Without any arguments or parameters, the drillfilters() function will show the selections you have made to drill into your hierarchy (if you haven't yet drilled into your data, this will return a blank cell). However, we can add a Dimension parameter to the function to indicate the value clicked when drilling to the next level in the hierarchy.

 

In the E-Fashion hierarchy we are using as an example, if we drill down from the State level to the City level by clicking California, then the drillfilters([State]) function will show the value California.

 

Let's create a simple report to demonstrate this.

Efashion Hierarchy Report Design.JPG

Efashion Hierarchy Report.JPG

 

 

 

When we are at the top of our Hierarchy, we can see that no drilling has yet taken place; the drillfilters() functions are all blank. However, if we drill down a level we get:

Efashion Hierarchy Report D1.JPG

..the drillfilters([State]) function now returns a result - the state name we drilled down on.

 

Using this logic we can craft an alerter:

Efashion Hierarchy Alerter.JPG

We're going to change the colour of the text in the header cell depending upon where we are in the hierarchy. In the above example, when the drillfilters([State]) function returns a null - which is the default before we drill into the data - the text will be red. However, if we drill down to City, drillfilters([State]) is no longer blank but drillfilters([City]) is - hence the text becomes green:

Efashion Hierarchy Report D3.JPG

..and finally, if we drill down again, the text becomes blue:

Efashion Hierarchy Report D4.JPG

This is the bottom of the Hierarchy; we can't click on the Store name to drill any further, hence drillfilters([Store name]) will always return a null.

 

If you wanted, you could expand on this mechanism to create a very visual indication of where you are in the drill hierarchy:

Efashion Hierarchy Report D5.JPG

Efashion Hierarchy Report D6.JPG

Efashion Hierarchy Report D7.JPG

This last example involves a little bit more work - one alerter for each hierarchy level, to achieve the above result. See if you can figure it out for yourself!

 

I hope you find useful

The SAP Enterprise Support Academy is running a Meet the Expert Session (a webinar) entitled “BI 4.1 Web Intelligence & Java Runtime Best Practices” on 10th September 2014 at:  9.30am Eastern Time (US), 3.30pm CEST (Europe), 2.30 pm BST (UK)

 

Please join our SAP Active Global Support experts in a session designed to improve your BI 4.1 ROI and best manage your Web Intelligence environment.

 

Topics covered are the following:

  • Know how to maintain your Web Intelligence environment in regards to potential Java Run-time Issues
  • Validation of BI 4.1 Administration best practices
  • Known issues & resolutions for your Web Intelligence with Java
  • Sources of information to best plan and maintain your landscape

 

Duration 1 hour

Register at the Support Academy Web Site


In my previous blog I described how we can consume HANA Views using UDT(.unv) in web intelligence.

 

Consuming HANA views in Web Intelligence report

 

Here we will see how we can consume HANA views(variables and input parameters) in webi through IDT(.unx).

 

Keep in mind only JDBC middleware is supported for consuming variables and input parameters

 

At first make sure you have View created in HANA Studio and variables or input parameter over it.

 

HANA11.png

 

Once this is executed we can create a Variable over this analytical view

 

HANA12.png

This was the HANA part and after this View is executed in HANA Studio and ready to consume in BO we can now move to Information Design Tool and see how we can create connection over this HANA view and use variables and Input Parameters

 

 

HANA13.png

 

So now we are creating a relational connection using JDBC as middleware for HANA

 

You need to specify HANA server name,port number and redentials remember in order to consume variables/input parameters only JDBC is supported with webi and not ODBC.

 

Refer https://websmp207.sap-ag.de/~sapidb/011000358700000171062014E/sbo41sp3_rel_restric_en.pdf for this.

 

Once local connection is created we will now reate HANA Business Layer as below

 

HANA14.png

This will allow you to create data foundation and business layer in one go,once you have given names below will be the pane to select HANA View

 

 

HANA15.png

Once you click on OK here it will create both data foundation and business layer.In the data foudation once we click on table we can see the variable dependant on it.

 

HANA16.png

The next steps is very crucial we have created data foundation and business layer over a local connection hence inorder to publish the HANA business layer we need to first publish the connection and then point the data foundation to the published connection shortcut(.cns)

 

HANA17.png

Now the HANA Business Layer is ready to be published to repository and for reporting.

 

Now you can create webi over this HANA universe and run it successfully.

 

HANA18.png

 

Hope this blogs helps all

 

I am open for any comments or queries on this.

Hana Views can be consumed in Web Intelligence reports through a universe created from Universe Design Tool using JDBC/ODBC connectivity.

 

Once we have View created in HANA Studio in the Content section under project

 

HANA1.png

 

We can see them under _sys_BIC schema under Catalog part

HANA2.png

 

Remember this is purely a relational component of HANA and HANA Studio and it is a tabular form of View(data foundation).

 

After this is activated in HANA system and executed we can create a relational connection using Universe Design Tool through odbc/jdbc middleware.

 

HANA3.png

 

The above figure depicts the tables under _sys_BIC and all the entried are HANA View in relational form.

 

After this it is a similar procedure like we create a relational universe in UDT.

 

Always remember Web Intelligence does not support Variables and Input Parameters refer below link

 

http://help.sap.com/businessobject/product_guides/sbo41/en/sbo41sp2_webi_user_guide_en.pdf

 

When the universe is created you can create a Web Intelligence report over it.

 

Hope this blog helps users who wish to consume HANA Views in Web Intelligence

Hi Team,

 

We’ll be asking for dynamic aging buckets many times at customer places. Each user want to see different aging intervals ,so forced to do multiple copies of  same BEx query with different interval for buckets so do Webi report.

 

I have got Order Number ,Invoice Number ,Aging Days (Difference between 2 dates Due date and current date)  and Outstanding Amount objects from backend (BEx query).

 

Logic behind : We’ll be having 6 buckets (Number of buckets are fixed) and will change range  of buckets dynamically with 5 input controls entered by user.

 

Process:

 

Create 5 Variables with default values which will act as Input controls.

Bucket1 : Measure variable :=15(default value , so at first we’ll get 0-15 range of bucket)

Bucket2 : Measure variable :=15(default value , so at first we’ll get 16-30 range of bucket)

Bucket3 : Measure variable :=15(default value , so at first we’ll get 31-45 range of bucket)

Bucket4 : Measure variable :=45(default value , so at first we’ll get 46-90 range of bucket)

Bucket5 : Measure variable :=90(default value , so at first we’ll get 91-180 range of bucket)

The last bucket we’ll be the >([Bucket1]+...+[Bucket5]) bucket ,which is >180 .

 

Add Bucket1..Bucket5 variables input controls with Entry Field selection .

 

Then create 4 variables to get sum of buckets to add up dynamically.

Bucket 1+2                         =[Bucket 1]+[Bucket 2]

Bucket 1+2+3                     =[Bucket 1]+[Bucket 2]+[Bucket 3]

Bucket 1+2+3+4                 =[Bucket 1]+[Bucket 2]+[Bucket 3]+[Bucket 4]

Bucket 1+2+3+4+5             =[Bucket 1]+[Bucket 2]+[Bucket 3]+[Bucket 4]+[Bucket 5]

 

Now derive values for bucket ranges .Since we are not showing/using Invoice number and Order number in the Webi table .I have included Invoice number and deal number in calculation context. You can remove them when you are working on with your requirement.


Bucket1 value  

=Sum([Outstanding Amount] Where ([Ageing Days] Between(0;[Bucket 1])) ForEach([L01 Order Number];[L01 Invoice Number]))

Bucket 2 Value

=If([Bucket 1+2+3+4+5]=[Bucket 1]) Then  Sum([Outstanding Amount] Where ([Ageing Days]>[Bucket 1])ForEach([L01 Order Number];[L01 Invoice Number])) Else Sum([Outstanding Amount] Where ([Ageing Days] Between([Bucket 1]+1;[Bucket 1+2]))ForEach([L01 Order Number];[L01 Invoice Number]))

Bucket 3 Value

=If([Bucket 1+2+3+4+5]=[Bucket 1+2])  Then Sum([Outstanding Amount] Where ([Ageing Days]  >[Bucket 1+2]) ForEach([L01 Order Number];[L01 Invoice Number])) Else Sum([Outstanding Amount] Where ([Ageing Days]  Between([Bucket 1+2]+1;[Bucket 1+2+3])) ForEach([L01 Order Number];[L01 Invoice Number]))

Bucket 4 Value

=If([Bucket 1+2+3+4+5]=[Bucket 1+2+3]) Then Sum([Outstanding Amount] Where ([Ageing Days] >[Bucket 1+2+3]) ForEach([L01 Order Number];[L01 Invoice Number])) Else Sum([Outstanding Amount] Where ([Ageing Days] Between([Bucket 1+2+3]+1;[Bucket 1+2+3+4])) ForEach([L01 Order Number];[L01 Invoice Number]))

Bucket 5 Value

= If([Bucket 1+2+3+4+5]=[Bucket 1+2+3+4]) Then Sum([Outstanding Amount] Where ([Ageing Days] >[Bucket 1+2+3+4])ForEach([L01 Order Number];[L01 Invoice Number])) Else Sum([Outstanding Amount] Where ([Ageing Days] Between([Bucket 1+2+3+4]+1;[Bucket 1+2+3+4+5]))ForEach([L01 Order Number];[L01 Invoice Number]))

Bucket 6 Value

=Sum([Outstanding Amount] Where ([Ageing Days] >([Bucket 1+2+3+4+5]))ForEach([L01 Order Number];[L01 Invoice Number]))

 

Now ,derive Buckets headers without this the report is not meaningful.


Bucket 1 Header

=0+"-"+[Bucket 1] +" Days"

Bucket 2 Header

=If([Bucket 1+2+3+4+5]=[Bucket 1]) Then "> "+[Bucket 1] +" Days" Else [Bucket 1]+1+"-"+[Bucket 1+2]+" Days"

Bucket 3 Header

=If([Bucket 1+2+3+4+5]=[Bucket 1+2]) Then  "> "+[Bucket 1+2]+" Days" Else [Bucket 1+2]+1+"-"+[Bucket 1+2+3]+" Days"

Bucket 4 Header

=If([Bucket 1+2+3+4+5]=[Bucket 1+2+3]) Then  "> "+[Bucket 1+2+3]+" Days" Else [Bucket 1+2+3]+1+"-"+[Bucket 1+2+3+4]+" Days"

Bucket 5 Header

= If([Bucket 1+2+3+4+5]=[Bucket 1+2+3+4]) Then  "> "+[Bucket 1+2+3+4]+" Days" Else  [Bucket 1+2+3+4]+1+"-"+[Bucket 1+2+3+4+5]+" Days"

Bucket 6 Header

= "> "+[Bucket 1+2+3+4+5]+" Days"

 

(This part is optional , we can leave blank columns also.)

The last part is to make the columns shrink/hide when there is 0 value specified as bucket value.

Since anyhow we’ll get Bucket1 and bucket2 value (If at least Bucket1 value is specified ) write formula to hide remaining columns and make them auto width with 0 inch .

3rd bucket column Value

=If([Bucket 3 Header]<>[Bucket 2 Header];[Bucket 3 Value])

3rd bucket column Header

=If([Bucket 3 Header]<>[Bucket 2 Header];[Bucket 3 Header])

4th  bucket column Value

=If([Bucket 4 Header]<>[Bucket 3 Header];[Bucket 4 Value])

4th bucket column Header

=If([Bucket 4 Header]<>[Bucket 3 Header];[Bucket 4 Header])

5th  bucket column Value

=If([Bucket 5 Header]<>[Bucket 4 Header];[Bucket 5 Value])

5th bucket column Header

=If([Bucket 5 Header]<>[Bucket 4 Header];[Bucket 5 Header])

6th  bucket column Value

=If([Bucket 6 Header]<>[Bucket 5 Header];[Bucket 6 Value])

6th bucket column Header

=If([Bucket 6 Header]<>[Bucket 5 Header];[Bucket 6 Header])

 

 

Caution : Don’t implement this logic if report brings more than 10k rows at view time .This can kill performance.

We can follow similar logic to give bucket range at @prompt level too. But the burden on Webi is same.

 

Thanks to Sabari Vasan. S for seed of this tip.

 

Hope somebody will find it helpful. Feel free to suggest changes in logic /other way of doing this.

In China and English.for  the people with poor reading English like me in mainland China

中英文对照,方便中国大陆英语和我一样差的人阅读。

 

 

1. Create a Variable named <input options> =""

1. 新建一个维度变量 input options=""

define1.jpg

 

 

2.Create a Input Control, on variable <input options>, and input A B C

2.新建一个控件(Input Control),关联到变量《input options》,输入 值 A B C

define2.jpg

 

 

3.Create a Variable named < Rank USER >

3.新建一个维度变量 <Rank USER>

=If [input optins]="A" Then ([凭证中的过帐日期])

ElseIf [input optins]="B" Then ([总分类账账目])

ElseIf [input optins]="C" Then ([会计凭证编号])

 

define3.jpg

 

 

4.In the Table , insert a column on the table left, input  Variable  < Rank USER >,

and  Apply an ascending(descending) Sort to the  column of Rank USER(you can hide the column )

4.在报表左边插入一列,输入数据是变量 《Rank USER》

并且根据需要做升序,降序的排序(可以隐藏此列)。

 

5.Using the input control, you can dynamically change which column is sorted.

5.点击控件选项即可实现用户自定义 的列排序

define4.jpg

 

That's it. Thanks

Hope this works for you

The goal of this blog is to update you on the enhancements delivered since SAP BusinessObjects BI4.1 to leverage the SAP HANA platform. Please refer to the SAP 4.1 documentation to have more details.

 

This blog is covering several features introduced in SAP BI 4.1 Web Intelligence and Information Design Tool that will help you leverage your HANA investment. Please note that previous recommendations with BI 4.0 on top of SAP HANA still apply.

 

  • SAP BI 4.1 Universes on top of HANA: HANA Business Layer authoring enhancement, HANA Multi-view universes
  • HANA variables and input parameters with BI 4.1
  • HANA Database Ranking with BI 4.1
  • Query stripping Relational with HANA + Auto-refresh use case.

 

 

Automatic HANA universe generation (available since BI4.1 SP2)

 

From an authoring standpoint, the Information Design Tool (IDT) has been enhanced to facilitate Universes development on top of SAP HANA. IDT is now able to generate a HANA Business Layer automatically based on HANA Views.

The default process for creating a business layer on a data foundation containing SAP HANA views takes into account the metadata as defined in the SAP HANA information model. The New Business Layer wizard automatically creates the dimensions and attributes in each SAP HANA view in a business layer folder, and creates measures with the appropriate aggregation function.

You could do this manually prior to BI4.1 SP2. Please refer to this document for more details on the manual steps: Business Case for the BI4 Semantic layer and Web Intelligence on SAP HANA

 

The SAP HANA Business Layer option is available when you create a new IDT project

HANA BL 1.png

HANA BL 2.png

HANA BL3.png

 

The New SAP HANA Business Layer wizard automatically creates a data foundation and business layer based on selected SAP HANA views. When multiple SAP HANA views are present in the data foundation, any dimensions and attributes that are common to different views are created as a single business layer object, and special aggregate-aware objects are generated to make queries on multiple views possible.


In the following screenshot, we are selecting 2 distinct Analytic Views

HANA BL4.png

The HANA Business Layer is generated with the appropriate @Aggregate_aware function. By doing this, the Universe information engine will be able to generate the most optimal query to HANA. In this scenario, the Universe is leveraging aggregate awareness to access the HANA view in the most optimal manner.

 

HANA BL5.png

In the Data Foundation, a self-join is generated randomly for each Analytic view. The reason behind is to avoid joining HANA views with each other as that could impact performances. As you can see in the below screenshot, a “dummy” filter will be generated in the SQL statement and the 2 HANA views are not joined.

HANA BL6.png

The HANA Business Layer automatic generation will also configure the aggregate navigation’s incompatible objects for you. This will make sure the end user does not query incompatible objects from the query panel.

In the below screenshot, we can see that the incompatible objects were set automatically for each table.

 

  • Table foodmart/SALES_2006 is incompatible with SALES_2007 objects

HANA BL7.png

  • Table foodmart/SALES_2007 is incompatible with SALES_2006 objects

HANA BL8.png

 

We can test the results by creating a query with a dimension “Product” and 2 measures (“Store Sales” from the SALES_2006 HANA View and “Store Sales” from the SALES_2007 HANA View.

In the below screenshot, the Universe will generate two separate queries hitting the 2 HANA Views with a full outer join performed on the client side.

 

HANA BL9.png

 

HANA BL10.png

As a result, the query will generate a result set from both SQL flows in the same table whenever possible.

HANA BL11.png

 

The goal of this functionality is to enable ad-hoc WebIntelligence reporting on top of HANA. The end user doesn’t have to know which or how many HANA views are being accessed and the Universe will generate the most optimal and performing query transparently.

 

 

HANA variables and input parameters (available since BI 4.1 SP3)

Variables and input columns defined in the SAP HANA information model are now included in the data foundation. When refreshing the data foundation, new, deleted, and updated variables in the data source are taken into account.

HANA IP1.png

 

In Web Intelligence, SAP HANA universes behave like any other relational UNX universe; HANA variables and input parameters in SAP HANA information models are associated with the corresponding tables in the data foundation.

HANA IP2.png

 

When you run a query that includes HANA variables and input parameters in the Query Panel or when a document is refreshed, prompts appear that require you to specify values for those variables and parameters. The values available in the prompts come directly from the HANA source

 

HANA IP3.png

HANA Database Ranking (available since BI4.1 SP3)

 

You can now set the ranking of data in an SAP HANA universe. Queries based on objects with universe-level ranking will take less time to fetch data. You can use HANA Analytic functions such as RANK() and PERCENT_RANK for ranking filters

 

HANA Rank1.png

 

  • SQL generation for RANK()

 

HANA rank 3.png

  • SQL generation for PERCENT_RANK()

HANA Rank 4.png

 

Query Stripping Relational (available since BI4.1 SP2)

 

Query stripping is now available for relational universes (including universes on SAP HANA) as well as OLAP universes. Query stripping is a reporting feature that can be used to optimize performance by automatically rewriting the query to retrieve only objects included in the report. It is used only by SAP BusinessObjects Web Intelligence.

The following steps will show you how to configure and enable query stripping on relational Universe (in this example a HANA Universe)

In the Business Layer Query options, check the Allow query stripping option and publish the HANA Business Layer

 

QS1.png

In the Web Intelligence query panel> Query properties, check “Enable query stripping”

QS2.png

 

In the Web Intelligence document property, check “Enable query stripping”

QS3.png

 

Create a report to test the query stripping. In the screenshot below, the dimension in bold are stripped and are not included in the SQL statement anymore unless you add them in the report canvas.

QS4.png

 

If you drag and drop a stripped dimension into the report canvas, you’ll get a #REFRESH message instead of the dimension’s data. This is normal as the dimension is not in the SQL anymore, you have to refresh the dataprovider manually by clicking “Refresh”

QS5.png

If you want to perform this action more transparently for the end user, you can enable the Automatic Refresh feature available since BI 4.1 :

In the CMC go to Application > Web Intelligence > Properties > Automatic Refresh

autoR1.png

 

In the document property, enable the “Automatic Refresh”

autoR2.png

 

Auto-refresh  works only when delegated measures are involved in the report

autoR4.png

 

If you now drag and drop a stripped dimension into the report canvas, a refresh will automatically run so you don’t have to manually click on the refresh button.

Hi Team,

 

Purpose : How to show Top N and Bottom N records controlled by a single Input control , and show Top and Bottom records in single table ordered as per their functionality  and format them with alerts .

Desired Output

In above table top 2 records are colored with green ,ordered by descending and bottom 2 records are colored with red,ordered by ascending.

 

How to do :

* Create a variable that acts as Input control for ranking.

Top =2 (Default value )

* Create another variable which distinguish Top and Bottom and filter the records in table for Top and Bottom records.

Top/Bottom =If(Rank([Prod])<=[Top] ) Then "Top" Else If( Rank([Prod];Bottom)<=[Top]) Then "Bottom"


Filter the table with Top/Bottom variable inlist Top and Bottom . By default we get only Top as value for Filter , we need to add Bottom value manually to Filter.

 

Now we get below table . Rank column is added to table.Here values are not in order.

Next to do is to order values .(If you sort on measure /Rank we get only ascending /descending , but our purpose is to have Top values in descending and bottom values in ascending).

* Create another variable for sort

Sort =If([Top/Bottom]="Bottom";Max(Rank([Prod]))In Block-Rank([Prod]) ; Rank([Prod]))

 

Add Top/Bottom and Sort tables to table .

Do descending sort on Top/Bottom  and ascending sort on Sort column.

 

Hide Sort and Top/Bottom columns ( don't delete)

Create alert to distinguish Top and Bottom records.

 

..Hope you'll enjoy this trick.

Would like to share the behavior of objects in Merged Universe when we upgraded Linked Universe from BO 5.1.5 to BI 4.1 and convert to UNX

 

 

When the linked universes in 5.1.5 is upgraded and converted to UNX, they result in a merged universe in 4.1.

 

Issue:

 

The state of the objects that were from Core Universe was not as expected in 4.1 merged UNX Universe.

 

Issue Description:

 

Noticed objects that were in hidden state in the core universe in 5.1.5 are visible in the merged universe in 4.1 UNX

which might cause issues by exposing the hidden objects when released to users without proper testing.

 

Resolution:

 

Manually need to change the state of the object in business layer so that
the object state would be the same in both 5.1.5 Universe and 4.1 UNX Universe

 

Note: Linked universes are no longer supported in Business Objects 4.x UNX.

However, linked universes can be converted as merged UNX universes i.e. conversion of derived
universe to UNX automatically includes Core Universe objects also.

 

Thanks,

Archana

Welcome !!

 

This is a simple & quick how to procedure which explains about how to make an Excel as a source to WebI. Thanks if you found this interesting and utile.

 

Note: This is a special feature available from BO Version 4.1 (SP2) onwards.

 

I've tried to post this maximum with the screenshots hope we all love more of a screenshots rather composing big paragraphs. This process is not a big deal still will hopefully help fresh guys.

 

Here is how we can upload an excel file and make it as a source for WebI report.

Untitled.jpg

Choose Local Document, and choose the destination folder to upload an excel file.

Untitled1.jpg


Choose file from your computer as highlighted.

Untitled2.jpg


Here you can choose the file you want to upload and make your WebI Report

Untitled3.jpg


Click Add to upload the file.

Untitled4.jpg


It will be uploaded as highlighted here.

Untitled5.jpg


Now, it is time to create a WebI Report based on uploaded file.

Untitled6.jpg


Select new report.

Untitled7.jpg

Choose Excel as a Data Source.

Untitled8.jpg


Choose the destination folder on which the excel was uploaded and saved.

Untitled9.jpg


Here you can do settings for the excel file.

Untitled10.jpg

Now the WebI will show all the dimensions and measure as default.

Untitled11.jpg


You can choose and change a dimension into a measure as per requirement.

Untitled12.jpg


Here you can even select type of the object(String/Date/Number).

Untitled13.jpg


After doing all necessary steps you can run query to get result as intended.

Untitled14.jpg


You can also save this as WebI query as highlighted.

Untitled15.jpg


That's it. Thanks

Hi

 

This blog will help you to remove the underline from hyperlink and Document and change the visited and unvisited Hyperlink color.

 

 

1st we will learn how to remove the underline from the Hyperlink or document link 

 

Step 1: Create a hyperlink on a column.

 

 

 

Step 2:  The below image I had created a hyperlink on a NAME column.

 

Step 3: After creating the hyperlink we will see a above image.(i.e hyperlink with underline)

 

Step 4:

 
 

 

 

We can see the formula bar; The new formula has been display.

For  eg we had created a hyperlink for a www.google.com

The formula bar will contain the formula as

 

="<a href=\"www.goggle.com\" title=\"\" target=\"_blank\" nav=\"web\" >"+[Name]+"</a>"

Step 5:

After that we have to edit the above formula

="<a href=\"www.goggle.com\" title=\"\" target=\"_blank\" nav=\"web\" style=\"text-decoration:none!important\">"+[Name]+"</a>"

We have to add the below text to the hyperlink

=style=\"text-decoration:none!important\"

Step6 :

 

After that right click on the name column and select the format cell

 

 

 

Select the “READ CONTENT AS HTML”.

 

 

Step 7: After selecting the Display as Read Content as HTML.

 

 

Note

After this step we have to save our  Webi  to BI LAUNCH PAD

 

 

Step 8:After saving the webi in bi launch pad we will see the below output ie hyperlink without under line

 

 

 

After that we will change the hyperlink visited and unvisited color of hyperlink

 

Step 1: Right click on a report name

 

 

 

And select the Format Report.

Step  2:

 

Select the Appearance we can see the hyperlink color.

 

 

Finally save the webi in BI launch pad and see the changes.

Actions

Filter Blog

By author:
By date:
By tag: