1 2 3 17 Previous Next

SAP BusinessObjects Web Intelligence

249 Posts

When: May 4, 2015 - 11:30 am ET
Register  for this event…

APOS is pleased to present this online live  Q & A session in conjunction with SAP and SAPinsider. During this session,  SAP product experts will explore how SAP BI 4.2 can enrich the experiences of  both users and information consumers.

 

During planning for this session, the session  title went through a few iterations, including:

 

  • Will SAP's  BI Tool Convergence and Interoperability Strategy Make Your BI Team Better?
  • Platform  Progress, Tool Convergence, and Quadrant Qualms….Where are we now??

 

These titles, and the title  we finally settled on, all describe the session accurately, but the last, which  was deemed a bit controversial, describes a particular quandary that developed when  Gartner published its most recent BI Magic Quadrant for Business  Intelligence and Analytics Platforms.

 

Back in February - on Super  Bowl Sunday, to be precise -- Mark Richardson published a critique of Gartner's  change in direction. His post was called 'Moving  the Goalposts': Why #DataViz Often Fails to Reflect Reality. In his  post, Mark noted:

GARTNER has moved their focus for this document away from “the long-standing  BI requirement for centrally provisioned, highly governed and scalable  system-of-record reporting” – and toward “analytical agility and business user  autonomy”. That’s their call – but I think it is the wrong one.

There is a danger in reliance on ad hoc reporting  and data visualizations to the exclusion of the underlying data, which is why a  “system-of-record” reporting engine is so important.

 

It’s easy to be deceptive  with visualizations, even to deceive yourself, but the real problem in such  cases is generally inaccurate data, especially when taken cumulatively, in  which case simple, seemingly inconsequential variances can distort the picture  and lead to poor decision-making.

 

What is required above all, as Mark  Richardson points out, is stability, accuracy, and governance. Can these  qualities be said to be characteristics of a platform?

 

It has been nearly 2  years since Jayne Landry published Run Simple: Convergence of the SAP  BI Product Portfolio. Now, with the release of SAP BI 4.2, it's time to review the progress  SAP has made on the platform, and check the convergence and interoperability of  the BI tool portfolio.

 

From the conceptual to  the technical, this session will let you explore the boundaries and synergies  of the SAP BI 4.2 BI tool set, and discover the potential for Lumira, Design  Studio, Web Intelligence, Analysis for Office and HANA within your deployment of  the SAP BI platform.

Panelists will  include:

 

  • Jayne Landry - Global VP & GM, Business Intelligence, SAP
  • Ty Miller - Senior Director of Solution Management for Enterprise Business  Intelligence, SAP
  • Olivier Duvelleroy - Senior Director, Solution Management, Business  Intelligence, SAP
  • Alexander Peter - Product Manager, SAP
  • David Stocker - Senior Product Manager, SAP
  • Gregory Botticchio - Product Manager, Web  Intelligence, SAP
  • Adrian Westmoreland - Product Manager, SAP


Register  for this event…

Hello everyone,

 

Another long standing customer request has been implemented in Web Intelligence, but without great fanfare. So little so that I did not realize it had been implemented until recently.

 

We now have the ability to export URL based images to PDF and Excel from Web Intelligence.

 

This has been a complaint from users for ages and it is now possible to do.

 

Previously if you created a WebI report with an image from a URL, if you exported it to PDF or Excel you would get a blank cell.

 

Starting with BI 4.1 SP6, SP7, and BI 4.2 that is no longer necessarily the case.

 

You can make registry edits to allow the URL images to be embedded in both formats.

(Note that the instructions for Linux are also included in the user guide below.)

 

This information is found in the Web Intelligence User manuals.

I am using http://help.sap.com/businessobject/product_guides/sbo41/en/sbo41sp7_webi_user_guide_en.pdf

In section 7.7 on page 448.

 

Here is step by step how you can do it:

 

Create a blank WebI document.

 

Add a blank cell.

 

Right click on the Cell and select Format Call.

 

Click on the Appearance selection on the left.

 

Select the Image from Address radio button and insert the image address as follows:

Image1.png

 

 

 

Click Ok.

 

After resizing the cell I have the following:

Image2.png

Save the Report.

 

If you now export to PDF you will not see the image, you will see something like this:

Image3.png

Now to implement the registry edits to enable this to work.

 

Open the registry and go to HKEY_LOCAL_MACHINE\SOFTWARE\SAP BusinessObjects\Suite XI 4.0\default\WebIntelligence\BlobManager

 

Create/Modify the string value ResolveHTTPUrl and set its value to yes.

 

Create/Modify the string value PROXY and set its value to <proxy>:<portnumber> where your replace <proxy> with your proxy name and <portnumber> with the proxy’s port.

 

It will look something like this:

Image4.png

Now that those are set, you can try exporting again.

 

Assuming the above information is configured properly, you should now see the image as such:

 

PDF

Image5.png

 

Excel

Image6.png

 

That’s it!

 

You can now export your URL based images to PDF or Excel from Web Intelligence.


If you are using the Web Intelligence Rich Client, you would make the same changes as above, but under the HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\SAP BusinessObjects\Suite XI 4.0\default\WebIntelligence\BlobManager


On Unix:


You need to modify the boconfig.cfg file found in the $installdir/setup folder.

 

In the boconfig.cfg file, locate the Software\SAP BusinessObjects\Suite XI 4.0\default\WebIntelligence\BlobManager section and do the following:

 

1.Create or modify the ResolveHTTPUrl value to yes to have the following line:

"<ResolveHTTPUrl>"=string:"<yes>".

 

2.Create or modify the PROXY value to <proxy>:<port>, where <proxy> is your client proxy server and <port> is the port of that server, to have the following line:

"<PROXY>"=string:"<proxy>:<port>"

 

It has been a long time coming, but it is finally here.

Hello All,

 

I came across a thread some days back where an end user wanted the Age of a customer in X Years Y Months and Z Days format given tow dates.

 

I searched through a lot of blogs but most of them didn't considered leap year which may lead to an incorrect data.

 

I tweaked the logic which I found in one of the blog and got that working so thought of sharing with all of you as we may come across such requirements in future.

 

So here how it goes. I will take two dates as Current Date([CurrDate]) and  Birth Date([BDate]).

 

First I will calculate the number of years in a formula which I have been told by an expert that breaking a huge formula into smaller pieces makes it more readable and easy to understand

 

[NumberOfYears]=Year ([CurrDate]) - Year ([BDate]) - If ( [CurrDate] <[BDate];1 ; 0 )+" Years "

 

Now I will calculate the number of months

 

[NumberOfMonths]=(Mod (((MonthNumberOfYear([CurrDate]) - MonthNumberOfYear([BDate])) + 12) - If ( DayNumberOfMonth([CurrDate]) < DayNumberOfMonth([BDate]) ; 1 ; 0 ); 12 ))+" Months "

 

And last the number of Days

 

[NumberOfDays]=(DayNumberOfYear([CurrDate]) - DayNumberOfYear( [BDate]) + If ( DayNumberOfYear([CurrDate])  >= DayNumberOfYear( [BDate]) ; 0 ; If ( DayNumberOfYear( [CurrDate]) - DayNumberOfYear([CurrDate])  < DayNumberOfYear( [BDate]) ; DayNumberOfYear( [BDate]) ; DayNumberOfYear( [CurrDate])- DayNumberOfYear( [CurrDate])  ) ))+" Days"

 

 

After we do all this only task left is to concatenate these three formula's.

 

[Age]=[NumberOfYears]+[NumberOfMonths]+[NumberOfDays]

 

and you will get the data in below format.

 

Capture.JPG

 

I have tested the code with Leap year Dates and some other random dates but your valuable feed backs and suggestions are always welcome.

 

Hope you like it.

 

Regards

Niraj

Many users/developers want to see/display custom comments in a Column chart. Not just displaying custom comments, accurately positioning them is a challenge as well. I've seen people use workarounds which are GREAT, but users/developers end up compromising (on few things) due to limitations in Web Intelligence.

 

Let's consider the below scenario of a Column Chart with Company Names and their Revenue in $B (dummy numbers over a period of time), plus the Custom Comments to be shown on top of each bar with Traded As names (of those companies on NASDAQ/NYSE), their Stock Price (at a point in time) and also conditional formatting of those custom comments depending on the Revenue (in $B).

Snap 08.PNG

Let's see how to do it.

 

Below is the data I used to develop this report.

 

Company NameRevenueTraded AsStock Price
LinkedIn2.99LNKD117
Apple95.00AAPL106
Google74.98GOOG754
Yahoo4.96YHOO36
Facebook17.98FB109
Adobe4.75ADBE96
Intel55.40INTC32
Oracle38.23ORCL41
HP85.00HPQ13
IBM81.74IBM145


Step 1:

  • Create the below variables
    • v_MaxRev =Max([Revenue]) In Block

(To find out the maximum Revenue out of all Companies)

 

    • v_PctofMaxRev =[Revenue] / [v_MaxRev]

(To find what Percent is the Revenue of a Company when compared to the maximum Revenue out of all the Companies)

 

    • v_TradedAsandStockPrice =[Traded As]+" - $"+[Stock Price]

(To concatenate Traded As name and Stock Price of a Company, which we want to show as Custom Comments)

 

    • v_LinestoSkip (Main variable of this trick, to skip number of lines (or move down) depending on the Revenue (measure) value)

=If([v_PctofMaxRev] <= 0.0249) Then 40

ElseIf([v_PctofMaxRev] Between (0.025;0.0499)) Then 39

ElseIf([v_PctofMaxRev] Between (0.05;0.0749)) Then 38

ElseIf([v_PctofMaxRev] Between (0.075;0.0999)) Then 37

ElseIf([v_PctofMaxRev] Between (0.1;0.1249)) Then 36

ElseIf([v_PctofMaxRev] Between (0.125;0.1499)) Then 35

ElseIf([v_PctofMaxRev] Between (0.15;0.1749)) Then 34

ElseIf([v_PctofMaxRev] Between (0.175;0.1999)) Then 33

ElseIf([v_PctofMaxRev] Between (0.2;0.2249)) Then 32

ElseIf([v_PctofMaxRev] Between (0.225;0.2499)) Then 31

ElseIf([v_PctofMaxRev] Between (0.25;0.2749)) Then 30

ElseIf([v_PctofMaxRev] Between (0.275;0.2999)) Then 29

ElseIf([v_PctofMaxRev] Between (0.3;0.3249)) Then 28

ElseIf([v_PctofMaxRev] Between (0.325;0.3499)) Then 27

ElseIf([v_PctofMaxRev] Between (0.35;0.3749)) Then 26

ElseIf([v_PctofMaxRev] Between (0.375;0.3999)) Then 25

ElseIf([v_PctofMaxRev] Between (0.4;0.4249)) Then 24

ElseIf([v_PctofMaxRev] Between (0.425;0.4499)) Then 23

ElseIf([v_PctofMaxRev] Between (0.45;0.4749)) Then 22

ElseIf([v_PctofMaxRev] Between (0.475;0.4999)) Then 21

ElseIf([v_PctofMaxRev] Between (0.5;0.5249)) Then 20

ElseIf([v_PctofMaxRev] Between (0.525;0.5499)) Then 19

ElseIf([v_PctofMaxRev] Between (0.55;0.5749)) Then 18

ElseIf([v_PctofMaxRev] Between (0.575;0.5999)) Then 17

ElseIf([v_PctofMaxRev] Between (0.6;0.6249)) Then 16

ElseIf([v_PctofMaxRev] Between (0.625;0.6499)) Then 15

ElseIf([v_PctofMaxRev] Between (0.65;0.6749)) Then 14

ElseIf([v_PctofMaxRev] Between (0.675;0.6999)) Then 13

ElseIf([v_PctofMaxRev] Between (0.7;0.7249)) Then 12

ElseIf([v_PctofMaxRev] Between (0.725;0.7499)) Then 11

ElseIf([v_PctofMaxRev] Between (0.75;0.7749)) Then 10

ElseIf([v_PctofMaxRev] Between (0.775;0.7999)) Then 9

ElseIf([v_PctofMaxRev] Between (0.8;0.8249)) Then 8

ElseIf([v_PctofMaxRev] Between (0.825;0.8499)) Then 7

ElseIf([v_PctofMaxRev] Between (0.85;0.8749)) Then 6

ElseIf([v_PctofMaxRev] Between (0.875;0.8999)) Then 5

ElseIf([v_PctofMaxRev] Between (0.9;0.9249)) Then 4

ElseIf([v_PctofMaxRev] Between (0.925;0.9499)) Then 3

ElseIf([v_PctofMaxRev] Between (0.95;0.9749)) Then 2

ElseIf([v_PctofMaxRev] >= 0.975) Then 1

 

    • v_PositionedCustomComments =RightPad("";[v_LinestoSkip];Char(13))+[v_TradedAsandStockPrice]

(To show Custom Comments in their accurate position)

 

Step 2:

  • Insert a Vertical table on top left of the report, with just 2 rows and update Format Table for the below options
    • Format Table -> General -> Update Name as Custom Comments Table
    • Format Table -> Layout -> Relative Position -> Horizontal as 0.2 cm from Left of Report and Vertical as 0.2 cm from Top of Report.
    • Format Table -> Appearance -> In Alternate Color, update Frequency as 0 and Color as White
    • Click Apply and OK
  • Insert Company Name in first row and v_PositionedCustomComments in 2nd row as shown below.

Snap 03.PNG

  • Select all 4 cells in the table and go to Format Cell to update below options
    • Format Cell -> General -> check Autofit Height
    • Format Cell -> Alignment -> Update Horizontal as Center and Vertical as Top (Note: This is very important.)
    • Format Cell -> Alignment -> Update Padding as 0 cm for Top, Bottom, Left and Right.
    • Format Cell -> Font -> Update Font size as 7
    • Format Cell -> Border -> Remove all borders
    • Click Apply and OK.
  • Select the 2 cells Body Cells (not header) and right click on them to go to Format Cell
    • Format Cell -> set Width as 2.12 cm
    • Click Apply and OK. This is how the table will look with data.

Snap 04.PNG

  • Select the 2 Header cells and right click on them to go to Format Cell
    • Format Cell -> Appearance -> under Background Image, update Color to No Color and Pattern to None.
    • Click Apply and OK.
  • Right Click on one of the company names from this vertical table and go to Hide -> click Hide Dimension as we want to hide the Company Name. This is how the table looks now.

Snap 05.PNG

 

Step 3:

  • Create a Column Chart with Company Name on Category Axis and Revenue on Value Axis 1 as shown below.

Snap 02.PNG

  • Go to Format Chart and update below options:
    • General -> Width = 22 cm and Height = 13.25 cm
    • General -> Data Values -> Check on Data Displaying Label Mode, update Data Postion = Inside, change Font size to 7, Font color to White and Orientation as Horizontal
    • General -> Background -> Update Background Color's Opacity to be 0%, so it becomes transparent
    • Legend -> Design -> Update Layout -> Location as Bottom
    • Legend -> Title -> Uncheck Visible
    • Category Axis -> Title -> Uncheck Visible
    • Value Axis -> Title -> Uncheck Visible
    • Value Axis -> Scaling -> Update Maximum Value to Fixed Value and use formula =[v_MaxRev]
    • Plot Area -> Background -> Update Category Axis Grid Color Opacity to 100%, so it becomes transparent.
    • Click Apply and OK.

We've created the Table and the Chart, now we need to align/place them in a way, they look like just ONE chart.

  • Right Click on the Column Chart, from Order -> select Bring to Front.
  • Go back to Format Chart and update these options
    • Format Chart -> Global -> Layout -> Relative Position -> set Horizontal as 1.35 cm from Left edge of Custom Comments Table and Vertical as 1 cm from Top edge of Custom Comments Table.
    • Click Apply and OK. This is how the Chart looks now.

Snap 07.PNG

Step 4:

  • Create the required Title and Subtitle as needed.
  • Create a conditional formatting Rule as below:
    • If [Revenue] Greater or equal to 75, then Font color should be Green
    • If [Revenue] Between 25 and 74.99, then Font color should be Blue
    • If [Revenue] Less or equal to 25, then Font color should be Red

Snap 09.PNG

  • Save this rule and apply on the Column in Custom Comments Table.

 

Voila, we did it. This is what the result looks like.

Snap 08.PNG

The result of this method may be 95% of what users/developers want. Rest 5% is just adjusting the Chart width and Column width (that shows custom comments) manually by looking at it.

 

Note:

This trick will surely help until the below BI Ideas are delivered as feature(s). I request all of you who checkout this document, please checkout and vote up on the below ideas.

comments in charts : View Idea

Webi Chart with Data Table : View Idea

 

Also, this workaround can be used with Combined Column, Stacked Column, Bar, Line... and few other types of Charts available.

 

Update from 05/04/2016 10:52 EST: Attaching the .txt file of the webi document, download it, rename it by removing .txt and/or .zip from the name.

 

Enjoy and please let me know your feedback.

Hello Everyone,

 

I found lot of queries being asked that Charts not displaying properly when have null values against then in the measure values. So thought of sharing my past experience that I had. below id the simple raw data that we will be using against the Line Chart.

 

MonthSales
Jan100
Feb
Mar250
Apr50
May75
Jun375
Jul
Aug66

 

So we can see that we have null values for the Sales in the month of Feb and Jul. Now we will create a simple line chart against the Raw data and you can spot that on below chart we have a break in the line in the month of Feb and Jul which is not what we want.


Null Values.JPG

 

So I created another measure using sales and added up 0 to it so as to handle the null value.

 

[Test]=[Sales]+0

 

The below chart is created with the newly created measure and you can see that now we are getting the chart way we want it.


Non Null Values.JPG

 

Hope I was able to explain to some extent. feedback and suggestions are always welcome.

 

Regards

Niraj

Hi

 

i have generated the Dynamic hyperlink with single parameter some thing like this

http://abc:9999/OpenDocument/opendoc/openDocument.jsp?iDocID=8080&sType=wid&sRefresh=N&sWindow=New&lsMEnter+Year%3A=([Ye…

Now i want to pass mutiple parameters dynamically Can you please let me know anyone on this

 

 

Thanks in Advance

 

 

Best Regards

Mohammed Zuber

Greetings Webi World,

 

I wanted to ensure that people were aware of this issue as it is causing a lot of incidents to come in to Product Support @ SAP.

 

It was recently discovered that a patch has introduced an issue with the calendar objects that are used by several Webi workflows.  It seems that for for months that have 30 days or less, only 27 days will show up in the Calendar picker.

 

This issue is currently being investigated by our development team and will be released in SAP Note: https://service.sap.com/sap/support/notes/2200522 (Service Market Place login required)

 

At the time of this writing, there is no solution.  This does only affect the HTML viewer and scheduling workflows so you should be able to use Webi Rich Client and the Applet Viewer  as a temporary work-around for now.

 

Thanks

Jb

Hi All,

 

I would like to show you how to implement a calendar like filter in the body of a WEBI report like the one below

calendar.png

 

This example was build on SAP BO 4.1 SP5, but since there is no dependency of any new feature, I imagine it will work on any version that supports Input Control and object linking.

 

 

 

The idea here is to build a cross-table and make it an Input Control linked to the body of the report.

 

 

To start with I have a date object , [date], coming from a calendar table.

Build the following variables  (all variables

are dimension variables, except when explicity mensioned)

 

1 - Year = Year([date];

2 - Month = Month([date])

3 -DayNumberOfWeek = DayNumberOfWeek([date])

4 - Weekday = =If([DayNumberOfWeek]=1;"Mon";If([DayNumberOfWeek]=2;"Tue";If([DayNumberOfWeek]=3;"Wed";If([DayNumberOfWeek]=4;"Thu";If([DayNumberOfWeek]=5;"Fri";If([DayNumberOfWeek]=6;"Sat";"Sun"))))))

5 - Week = NumberOfWeek([date])

6 - Week_aux = If([Week] >52;1;[Week]+1)

7 a measure variable [day] = NumberOfDayInMonth([date])

 

 

Drop a cross-table on the report and set it[s structure like below :

 

cross table.png

 

Apply a break on [Month] and set it´s property like below

 

 

break prop.jpg

 

Now, hide the [DayNumberofWeek] row (right click on the Row and choose Hide->Hide Dimension

Repeat the procedure to [Week_aux] column

 

Name the block (table) as Calendar (right click on the table, choose Format Table->General)

 

 

We will create a elements link from this cross-table to the other elements on the report.

To do so, right click on the tabel and choose Linking -> Add Element Link

 

element link.jpg

 

 

Select all objects

 

 

 

element link.png

 

Click on Nextand enter calendar as the name of the input control

In this next step you will define the elements on the report that will be affected by this input control. Check all boxes except Calendar

 

 

element link dependencies.jpg

 

Now you have a clickable calendar to filter your report.

 

This sample suposes the the report is already filtered by Year and Month


Post I will showhow to create a clickable list to select the month and year.


Regards,

Rogerio

UPDATED 03/17/2016 - Attached file 3providers.txt. This file is, in fact, a zip file containing both the wid file and the excel. Rename from 3providers.txt to 3providers.zip. This example was done in 4.1 SP5

 

 

Hi,

 

I´ve just find a way to use 3 Data Providers which are "compatible" 2 by 2. The idea came from this post Merging 3 queries using 2 merged dimensions.

In this post, I´ll use three Data providers in an Excel file.

The first  one that I´ll name Invoices has 2 fields an ID of the invoice and the date of the invoice

 

 

id

 

 

date

 

 

1

 

 

1/1/16

 

 

2

 

 

10/2/16

 

The second, Customer with invoice ID and Customer Name and parts no

 

customer name

 

 

invoice Id

 

 

parts no

 

 

Maria Cecilia Goulart

 

 

3

 

 

C

 

 

Nadia Stella

 

 

2

 

 

B

 

 

Roger Plank

 

 

1

 

 

A

 

And the last one, Items with item , parts no price and qtd (quantity)

 

item

 

 

parts no

 

 

price

 

 

qtd

 

 

111

 

 

A

 

 

3

 

 

4

 

 

111

 

 

B

 

 

3

 

 

2

 

 

222

 

 

A

 

 

3

 

 

4

 

 

333

 

 

A

 

 

4

 

 

5

 

 

444

 

 

B

 

 

7

 

 

1

 

 

555

 

 

C

 

 

2

 

 

4

 

 

666

 

 

C

 

 

8

 

 

4

 

 

777

 

 

C

 

 

1

 

 

5

 

Invoice and Customer has a common dimension Id (id from Invoice and invoice id from Customer)
In Customer and Items the common dimension is parts no.
The challenge is to Show all objects in a single table

 

Invoice Id

 

 

item

 

 

prts_det

 

 

cust_det

 

 

price

 

 

date_det

 

 

1

 

 

111

 

 

A

 

 

Roger Plank

 

 

3

 

 

1/1/16

 

 

1

 

 

222

 

 

A

 

 

Roger Plank

 

 

3

 

 

1/1/16

 

 

1

 

 

333

 

 

A

 

 

Roger Plank

 

 

4

 

 

1/1/16

 

 

2

 

 

111

 

 

B

 

 

Nadia Stella

 

 

3

 

 

10/2/16

 

 

2

 

 

444

 

 

B

 

 

Nadia Stella

 

 

7

 

 

10/2/16

 

 

3

 

 

555

 

 

C

 

 

Maria Cecilia Goulart

 

 

2

 

 

3

 

 

666

 

 

C

 

 

Maria Cecilia Goulart

 

 

8

 

 

3

 

 

777

 

 

C

 

 

Maria Cecilia Goulart

 

 

1

 

The final result is
Captura de Tela 2016-03-15 às 19.55.15.png

 

 

To achieve this I did the following
1 - Merge Invoice and Customer on [invoice Id merged]
2 - Merge Customer and Items on [parts no merged]
3 - Create a detail variable [invoice id det Customer] with associated dimension =[parts no merged] and the formula =[Customer].[invoice id]
4 - Create a dimension variable [invoice id dim]= [invoice id det Customer]
4 - Create a detail variable [invoice id det Invoice] with associated dimension = [invoice id dim]  and the formula [Invoice].[id]
5 - Create a detail variable [customer name det] with associated dimension [Customer].[invoice id] and formula = [Customer].[customer name]
6 - Create a detail variable [date det] with associated dimension [Customer].[invoice id] and formula = [Invoice].[date]
7 - Create a detail variable [item det] with associated dimension [parts no merged] and formula [item].
Remember to check the table property "Avoid duplicate line agregation"
Now I can use all objects in the same block.

I´m working on a wid file to show the final result,

Regards,

Rogerio

Problem Statement:

 

We often get a requirement to generate a report based on a given date range, a typical requirement would be to display charts based on the date range selected. if user selects date range as too big then the chart will clutter with so many points and impacts chart readability.

 

Do we have any option to change the chart axis dynamically based on the date range.

 

For example -


If user selects data range less than a month then show week wise data

If selection is more than a week and less than a month then show month wise data

If selection is more than a month and less than a quarter then show month wise data

If selection is more than a quarter and less than a year then show quarter wise data

If selection is more than a year and less then show year wise data

 

Solution:


This is how we could approach the solution, create a variable which gives the output as Date, Week, Month, Quarter and Year based on the Number of Days between the date range selected by the user, use this variable while creating the chart.

 

Below are the detailed steps for creating variables and chart.

 

Step1 : Create Report:

Create a sample report with Island Resorts marketing Universe with objects like Country, Invoice Date and Revenue

Create Range Filter on Invoice date as with prompt text as “Start Date” and “End Date” respectively.

Make these prompts as optional so that user can run the report fir entire data range

 

Query will look like below

SELECT

Resort_Country.country, Sales.invoice_date, sum(Invoice_Line.days * Invoice_Line.nb_guests * Service.price)

FROM

Country  Resort_Country,

Sales,  Invoice_Line,  Service, Service_Line,  Resort

WHERE

( Resort_Country.country_id=Resort.country_id  )

AND  ( Sales.inv_id=Invoice_Line.inv_id  )

AND  ( Invoice_Line.service_id=Service.service_id )

AND  ( Resort.resort_id=Service_Line.resort_id )

AND  ( Service.sl_id=Service_Line.sl_id  )

GROUP BY

Resort_Country.country,

Sales.invoice_date

 

 

 

Step 2: Create these variables in the report level


1. User Start Date

=If(IsPromptAnswered("Start Date") ;ToDate(UserResponse("Start Date");"INPUT_DATE_TIME"))

 

2. User End Date

=If(IsPromptAnswered("End Date") ;ToDate(UserResponse("End Date");"INPUT_DATE_TIME"))

 

3. No of Days

=DaysBetween([User Start Date];[User End Date])

 

4. Year

=FormatNumber(Year([Invoice Date]);"####")

 

5. Year_Number(YYYY)

=Year([Invoice Date])

 

6. MonthYear(Mon-YYYY)

=Left(Month([Invoice Date]);3)+" - "+[Year]

 

7. Month_Number(YYYYMM)

=[Year_Number]*100+MonthNumberOfYear(([Invoice Date]))

 

8. Quarter(YYYY-QQ)

=[Year]+"-Q"+Quarter([Invoice Date])

 

9. Quarter_Number(YYYYQ)

=[Year_Number]*10+Quarter([Invoice Date])

 

10. Date_Number(DDMMYYYY)

=(DayNumberOfMonth([Invoice Date])*100+MonthNumberOfYear([Invoice Date]))*10000+[Year_Number]

 

 

11.Week

               ="w"+Week([Invoice Date])+"-"+[MonthYear(Mon-YYYY)]

 

12.WeekNumber

               =Week([Invoice Date])*1000000+[Month_Number(YYYYMM)]

 

 

 

13. Dynamic_Period

=If([NoofDays]<=7;[Invoice Date];If([NoofDays]<=31;[Week];If([NoofDays]<=90;[MonthYear(Mon-YYYY)];If([NoofDays]>90 And [NoofDays] <=365;[Quarter(YYYY-QQ)];[Year]))))

 

14. Dynamic_Period_Number

=If([NoofDays]<=7;[Date_Number(DDMMYYYY)];If([NoofDays]<=31;[WeekNumber];If([NoofDays]<=90;[Month_Number(YYYYMM)];If([NoofDays]>90 And [NoofDays] <=360 ;[Quarter_Number(YYYYQ)]; [Year_Number]))))

 

 

Output of above variables will be like below

Dynamic
_Period

User
Start
Date

User End
Date

Noof
Days

Invoice
Date

Date_Nu
mber
(DDMMY
YYY)

Week

WeekNu
mber

MonthYear
(Mon-
YYYY)

Month_N
umber
(YYYYM
M)

Quarter
(YYYY-
QQ)

Quarter_Nu
mber
(YYYYQ)

Year

Year_Number

1,199,801

1/1/98

1/31/98

30

1/1/98

1,011,998

w1-Jan - 1998

1,199,801

Jan - 1998

199,801

1998-Q1

19,981

1998

1,998

1,199,801

1/1/98

1/31/98

30

1/2/98

2,011,998

w1-Jan - 1998

1,199,801

Jan - 1998

199,801

1998-Q1

19,981

1998

1,998

2,199,801

1/1/98

1/31/98

30

1/6/98

6,011,998

w2-Jan - 1998

2,199,801

Jan - 1998

199,801

1998-Q1

19,981

1998

1,998

2,199,801

1/1/98

1/31/98

30

1/7/98

7,011,998

w2-Jan - 1998

2,199,801

Jan - 1998

199,801

1998-Q1

19,981

1998

1,998

2,199,801

1/1/98

1/31/98

30

1/11/98

11,011,998

w2-Jan - 1998

2,199,801

Jan - 1998

199,801

1998-Q1

19,981

1998

1,998

3,199,801

1/1/98

1/31/98

30

1/12/98

12,011,998

w3-Jan - 1998

3,199,801

Jan - 1998

199,801

1998-Q1

19,981

1998

1,998

 

 



Step 3: Create Chart using Country, Dynamic_Period,Dynamic_period_Number,Revenue objects

Hiding Number Column.png

Step 4: Sort the chart on Dynamic_Period_Number variable

 

Sorting Number.png

note:

Here i have hidden the Dynamic period Number variable in the chart and sorted on it, but display variable will be Dynamic Period. This to avoid the sorting issue which you get while using the Dynamic Period Object and the explanation of the issue is at the end of this solution.


Step 5: Create chart using Country, Invoice_Date and Revenue object


Step 6: Report output with different set of date parameters


1) Date Range: Entire Date Range

No value selected for Start and End date (left them blank) as they are optional

 

 

Report output with normal date as the x-axis value

1. Default Date wise chart.jpg

 

 

 

Report output with Dynamic Period as the x-axis value shows year wise data, as the entire data is for 3 Years in the data base

 

2 Dynamic Period Chart_optional Prompt.png

 

 

 

2) Date Range:   One week

                                 Start Date: 1/1/1998

                                 End date: 7/1/1998

 

Report output with normal date as the x-axis value

13. Default Chart for 7 Days of Data.png

Dynamic Period Chart gives day wise data

 

14. Dynamic Chart for 7 Days of Data.png

 

3) Date Range:   One Month of Data

                                  Start Date: 1/1/1998

                                 End date: 31/1/1998




Report output with normal date as the x-axis value

 

11. Default hart with One Week Of Data.png

 

 

Report output with Dynamic Period as the x-axis value shows week wise data

 

12. Dynamimc chart with One Week Of Data.png

 

4) Date Range:   Three Months of Data

                                  Start Date: 1/1/1998

                                 End date: 31/3/1998


Report output with default date axis

 

3 Default chart with 3Months Of Data.png

Dynamic axis chart output shows month wise data

 

 

4 Dynamic chart with 3Months Of Data.png

 

 

5) Date Range:    One Year

                                          Start Date: 1/1/1998

End date: 12/1/1998

Report output with normal date as the x-axis value

 

5. Default chart with 12Months Of Data.png

 

 

Report output with Dynamic Period as the x-axis value shows quarter wise data

 

6 Dynamic chart with 12 Months Of Data.png

 

 

 

 

6) Date Range:   Two Years

                                       Start Date: 1/1/1998

End date: 12/1/1999

Report output with normal date as the x-axis value

 

 

7. Default chart with 2 Years Of Data.png

 

Report output with Dynamic Period as the x-axis value gives year wise data

8. Dynamic chart with 2 Years Of Data.png

 

Note:

Reason for having Number and string for every formula is to avoid sorting issue of the Date field. This is because the output of the Dynamic Period is a String. Since the if statement contains combination of Date and String data types even the date is considered as String.

=If([NoofDays]<=31;[Invoice Date];If([NoofDays]<=90;[MonthYear(Mon-YYYY)];If([NoofDays]>90 And [NoofDays] <=365;[Quarter(YYYY-QQ)];[Year])))

 

The sorting issue in chart created only Dynamic Period which is of String Data type will be like below (if you see the order of dates as 11,12 & 7)

ASCII of 12 less than 7 so it came first)

 

Date Range Selected is : 7/1/1998 to 14/1/1998

 

Sorting Dynamic PEriod Chart.png

 

Chart Created using both Dynamic Period and Dynamic Period Number will be like this

 

 

Sorting Dynamic PEriod Number Chart.png

Hi,

 

Suppose that you want to correlate the data you have in a query with some external data. For instance you´re analyzing countries sales and want to correlate it with the Gini index., which is available here GINI index (World Bank estimate) | Data | Table

This is how I would address the situation :

 

My query has the following objects  [country] , [sales], and the table of Gini Index is (for simplification purposes, I´m using just three values : Brazil, Chile and Venezuela)

 

I get

CountryGini Index
Brazil52.9
Chile50.5
Uruguay41.9

 

What I´ll do is create two "arrays" : one for the country names and the other with the values of the Gini index.

 

An array is a set elements where each element can be accessed by an index.

So, [array] =  {"Brazil", "Chile", "Venezuela"} has three elements in which the first element is "Brazil", the second "Chile" an the third "Venezuela".

As BO doesn´t have an array type, I will use the following trick . If I concatenate the values of the countries I will get the text "BrazilChileVenezuela" ([arrayind]) , in which

   "Brazil" is the first country and so on.

But I can not, at BO side get the first element because I don´t know where the first element starts and where in ends in [arrindex] .

 

If I create an array in which all elements get the same Length, I´ll be abble to index them by it´s start and end position.

 

For instance, if a Fix the Length of the elements to 10, I will get the following text [arrindex]  = "Brazil    Chile     Venezuela ", now, given the position of a element (1,2 or 3) I´m abble to get the elements text by the formula [Text] = Substr([arrIndex]; ([index]-1)*10+1;10) where index is the index of elements,

 

In the same way, If I want to find the index of an element :

 

[index] = ((Pos([arrIndex];[Element]-1)/10)+1.

 

Using this technique I´ll build two arrays with the data of Gini index, the first one is [arrIndex] which we already built, and the other [arrLookUp] which is the concatenation of the Gini index values :

[arrLookUp] = "52.950.541.9"

 

This is done externally to WEBI.

 

Now, inside WEBI, create two measure variables ([arrIndex] and [arrLookUp]) , both with value = " ".

 

Create two entry field Input Controls each of them associated to each of the variables.

 

Create another measure variable  [index] = ((Pos([arrIndex];[country])-1)/10)+1 this will give me the index of each [country] in [arrIndex], now I´ll use this index to get the value of Ginis index in [arrLookUp]

 

[Gini Index] =  ToNumber(Substr([arrLookUp]; ([index]-1)*10+1;10) ;"00.0")

 

 

Set a table with [Country], [Sales] and [Gini Index], copy and paste the text of both "arrays" into the appropriate Input Control and hit okay.

 

 

Regards,

Rogerio

Hi,

 

today there was a discussion about changing the order in which RunningSum is calculated in a table (RunningSum() sorted by Classification()).

This is the data presented. The RunningSum should be calculated in descending order by cost.

 

 

The solution I proposed was redefine the context of the calculation using the In

 

The secret here was to create a dummy dimension which should give the correct order to calculate it.

I create the dummy variable [cost_dim] = 0 - [cost].

If the table was ordered by [cost dim] (meaning descending order by [cost]), the result will be presented correctly, but another requirement was not to  alter the table´s structure.

 

So, we must find a way of calculating RunningSum as if the table was sorted by [cost dim] .

 

As a rule of thumb, when you redefine a context, the order in which you set the dimension that redefines it will give you the default sort of the calculation.

If you set the redefinition to ([cost_dim]), the calculation will happens in ascending order of [cost_dim] (descending order of [cost], as needed).

 

Using RunningSum([cost]) In ([cost_dim]) will  force RunningSum to be calculated as needed.

Regards,

Rogerio

Hi All,

I´ve been posting about the use of Javascript inserted in a WEBI document.

To insert Javascript code into a webi document just drop a blank cell onto your report, copy the code to it and set its "Read content as" property to "HTML".

 

In this  blog post I´ll show how to hide a button from the interface, in this case, the Save button.

 

I´m still working on the general guidelines of scripting WEBI through Javascript but I´m trying to publish as soon as a find out something interesting.

 

This code will hide the Save button (and its colleague, the "Save as") :

 

<script>

wi=window.top.window[2].window[0]._widgets;

for(i=0;i<wi.length;++i)

{if  ((wi[i].actionId !== null)&&(wi[i].actionId!== undefined))

     if(wi[i].actionId=='saveBtn')

       window.top.window[2].window[0].document.getElementById(wi[i].id).hidden=true;

}

</script>

 

This is , shortly how it works,

window.top.window[2].window[0] is webiViewFrame, the HTML window of the document,

 

wi=window.top.window[2].window[0]._widgets;


will get, into wi the list of the widgets of the window (the widgets are, mostly, interactive objects of the interface (like buttons, tabs, etc..)

 

next step is  to transverse the list looking for the Save button , when it´s found, we set the hidden attribute of its html element.

 

This was tested in SAP BO 4.1 SP5.

 

Regards,

Rogerio


When there is no record fetched in a WebI query, we get the message like "No data to retrieve in <Data Provider>". I have seen multiple times people asking for a way to customize this message or prevent the message from appearing. There is a blog in SCN which describes a way to do it for WebI on universe. But, for WebI on OLAP connection, the need remains.

In this blog, let me share the way I managed to do it.

 

This customization is not supported by SAP. There is no guarantee that this approach will work in future releases of the product.

 

This is going to affect all WebI reports.

 

Assumption:

 

This process has been tested in SAP BO BI Platform 4.1 SP5 installed in Windows server with Tomcat as application server. Product Locale & Preferred Viewing Locale is English (en_US).

 

Steps:


  1. Stop Tomcat.
  2. Delete the folder <Tomcat Installation Dir>\work\Catalina\localhost\BOE. This is a temporary folder and will be re-created once we start tomcat.
  3. Navigate to <Tomcat Installation Dir>\webapps\BOE\WEB-INF\eclipse\plugins\webpath.AnalyticalReporting\web\webiDHTML\viewer\language\en\scripts.
  4. Take a backup of the file viewPerspective.js and keep it in a safe location.
  5. Open the file in notepad and search for the string displayNoDataToFetch.
  6. Change the function definition to set your custom message.

     

    Original

    function displayNoDataToFetch(s,cb){

        showAlertDialog("No data to retrieve in "+s,"Retrieving Data",0,cb);

    }

    Custom message

    function displayNoDataToFetch(s,cb){

        showAlertDialog("No data to fetch in "+s,"Retrieving Data",0,cb);

    }

    Prevent the message from appearing

    function displayNoDataToFetch(s,cb){

        /* showAlertDialog("No data to retrieve in "+s,"Retrieving Data",0,cb); */

    }

  7. Save the file.
  8. Start Tomcat.
  9. Clear browser cache.
  10. Wait till the temporary BOE folder is fully re-created in <Tomcat Installation Dir>\work\Catalina\localhost folder.
  11. Test by running a WebI report from BI Launchpad in HTML view which has no data to retrieve.
  12. Here is the customized message :

Hi all

        I this post I would be sharing few facts and information related to the Webi Reports whose Sqls are migrated from Db2 to Teradata.

This Project involved multiple reporting tools (Non Sap as well).But lets discuss about  SAP Business Objects:-

 

It involved Converting the Db2 - Sql Syntaxes of the existing objects in the Universes to 'TERADATA' standards,followed by repointing the given set

of reports to the Teradata pointing universes.

                                      Then Fixing the errors caused due to it in the Webi reports.Finally checking for the "Data Validation" across the reports.

by "Manual testing"(Automated tools are also available).

 

Steps taken for the Universe related activities:

 

  1. Connection layer was repointed to Teradata
  2. Test the connection and fix the errors occured.
  3. Publish  the connection layer
  4. Repoint the data foundation layer source to the Teradata connection
  5. Refresh the table structure
  6. Identify the table level errors and fix them
  7. Check for the missing Columns  or datatype issues involved
  8. Identify the derived table
  9. Modify  the derived table queries according to the Teradata standards.
  10. Check the object definition for the DB2 syntax and convert them to TD syntax
  11. Check the integrity
  12. Identify any issues thrown on integrity check
  13. Publish the universe to the repository.


Steps taken in the Reporting side:-


  • Ensure the Report is pointed to the corresponding universe which is repointed to TD environment
  • Identify the reports having Custom SQL
  • Convert the DB2 SQL Syntaxes to Teradata standards.
  • Refresh the report and identify the errors if thrown.
  • Fix the errors if it is related to DB2 to Teradata code conversion.
  • Refresh the reports,identify the errors occured.
  • Check the data providers to know the the sources and multiple universes involved,
  • Check for report structure/Layout and ensure that basic properties of the report matches with the source.
  • Verify Break applied on columns; break properties and priorities.
  • Publish the reports to the repository


Till then Happy Learning......................


Actions

Filter Blog

By author:
By date:
By tag: