1 2 3 8 Previous Next

SAP BusinessObjects Web Intelligence

116 Posts

In a recent BO upgrade project, I did face a requirement of displaying custom message in BI Launchpad login page. The upgrade happened from BOXI3.1 to BO 4.0. Earlier, they used to edit the logon.jsp file inside InfoViewApp web service folder to display their custom messages. But the same trick did not work in BO 4.0.

 

In this blog, let me describe how I managed to display a custom message in BI Launchpad login page for SAP BusinessObjects Business Intelligence Platform 4.0 SP6.

 

Requirement

Let the requirement be as below:

  1. To show a pop-up message when the user enters the URL for BI Launchpad.
  2. In the log-in screen we need to show some custom message.

 

 

Assumption

Let's assume that SAP BO BI Platform 4.0 has been installed and configured with default Tomcat web application server.

 

 

Steps

Here are the steps to achieve the requirement:

  1. Stop Tomcat in the BO server.
  2. Delete the folder <Tomcat Installation directory>\work\Catalina\localhost\BOE. This is a temporary folder and will be re-created when tomcat will be started.
  3. Go to the folder <Tomcat Installation directory>\webapps\BOE\WEB-INF\eclipse\plugins\webpath.InfoView\web.
  4. Take a backup of the file custom.jsp and keep it in a safe location.
  5. Create a copy of the file logon.jsp in the same folder and rename it as custom.jsp.
  6. Open newly created custom.jsp file in notepad for editing.
  7. To create a pop-up message, append the following line just before the <title> tag:

    <scriptlanguage=JavaScript> alert('Custom Pop-Up Message');</script>

     

    Example

    <script language=JavaScript>

    alert('Information: \n Any Report / UserID not used in last 2 months will be cleaned up as part of Regular System maintenance.');

    </script>

     

  8. Add the custom message you want to display in the login screen just after the line :

    <divclass="logonIFrame">


    Example

    <font color="Mediumblue">For logon credentials, kindly contact

    <a href=mailto:ari007.cse@gmail.com"><font color="red"><span style="text-decoration:underline;">Arijit Das</span></font></a>

    </font>

     

  9. Save and close the file.
  10. Go to folder <Tomcat Installation directory>\webapps\BOE\WEB-INF\internal.
  11. Take a backup of the file BIlaunchpad.properties and keep it in a safe location.
  12. Open existing BIlaunchpad.properties file in notepad for editing.
  13. Change the line

    redirection.iframe.1.redirectto.url=/logon.jsp

    to

    redirection.iframe.1.redirectto.url=/custom.jsp

  14. Save and close the file.
  15. Start Tomcat from Central Configuration Manager. It will take some time to re-generate the BOE temporary folder which we deleted in step 2.
  16. Clear browser cache and java temporary files.
  17. Now open the BI Launchpad URL: http://boserver:port/BOE/BI in the browser.
  18. First the pop-up message will appear as we configured in step 7.

     

  19. Once you click OK, the logon screen will appear with custom message that we configured in step 8.

     

 

How to do it for BO 4.1

The process is similar for BO 4.1 also but with some minor differences :


Steps 1-7 are same as we did for BO 4.0.


Step 8: Add the custom message to be displayed just before the tag <h:form>.

 

Steps 9-12 are again same as before.

 

Step 13: Change the line

          redirection.iframe.1.redirectto.url=/logon.faces

          to

          redirection.iframe.1.redirectto.url=/custom.faces


Steps 14-19 are again the same as before.

For those of you who have been around in the SAP BusinessObjects ecosystem long enough, you may remember an awesome labs feature for Webi called Extension Points. Now it is back in a slightly different form as an officially supported Extension Points SDK. I have been fantasizing about integrating our location intelligence solution into Web Intelligence for years so Evan Delodder (wrote the book on SAP Dashboards SDK) and I dove in head first to give Extension Points a shot. The goal was to integrate SAP Dashboards and CMaps Analytics, which uncovered Extension Points as a hidden jewel inside of the BI4.1 platform!

 

Untitled-12.jpgWhat is Extension Points?

Extension Points is a brand new SDK that allows developers to create and inject custom visual controls via HTML/Javascript and even custom Functions for processing data. Extension points SDK comes with basic documentation and a sample project. Though it was first supported with BI4.1 SP02 newer versions add more APIs and hooks to accomplish new experiences not possible before with Webi.

 

What do you need to get started with Extension Points?

Approaching Extension Points is similar to any SAP BusinessObjects SDK project. First and fore most, you need patience. There is not a development community, massive forum with lots of developers, or library of existing examples to work from. The test cycles are fairly slow but the upside and potential once you get through your first extension is well worth the effort. Extension Points is a development SDK, so you need a Javascript developer to bind exposed functions through the SDK to your desired features.

 

What is possible?

Armed with the right resources, the results can be extremely rewarding for your business users. Extension Points treats a panel as a empty shell, where you can code just about anything you can imagine. This level of control and flexibility is exactly what you want from an SDK. While our initial goals to utilize a narrow span of functionality, we found that with Extension Points SDK you can:

 

  • Embed new input controls
  • Consume data from Webi report parts into new embedded tools
  • Change the structure and layout of your report document
  • Create new visualizations
  • Integrate third party tools and applications
  • Dynamically control various report behaviors

 

What did we accomplish?

Rather than re-building 150 input properties for our CMaps Analytics tool in Webi, we wanted to use SAP Dashboards as our initial vehicle for delivering maps to Webi. However the end result needed to look and feel as seamless as possible for end user. In short, we built the beginnings of a deeply integrated bridge between SAP Dashboards and Webi. Currently our CMaps Analytics Extension Point consumes data from a single WebI report part, and then refresh itself as you drill or filter. With a solid foundation in place, we are onto exposing prompts, filters, and refined workflows to create an integrated experience that customers have always asked for.

 

webicmaps.jpg

 

I am happy to take your feedback or questions as you approach Extension Points, and will certainly report tricks and tips as we move forward.

ryan@centigonsolutions.com.

Below are the list of Formulae used to display the Calendar Day Prompt values in the Webi Report Headings or Header Labels.

 

1. Getting Start date from Date prompt: Start Date=FormatDate(ToDate(Replace(Left(Replace(UserResponse("Calendar Day");" ";"  ");10);" ";"");"MM/dd/yyyy");"dd.MM.yyyy")

 

 

2. Getting End date from Date prompt: End Date=FormatDate(ToDate(Replace(Substr(Replace(UserResponse("Calendar Day");" ";"  ");Pos(Replace(UserResponse("Calendar Day");" ";"  ");";")+1;10);" ";"");"mm/dd/yyyy");"dd.mm.yyyy")

 

 

3. Getting Month Name from Date prompt: Month Name=FormatDate(ToDate(Replace(Substr(Replace(UserResponse("Calendar Day");" ";"  ");Pos(Replace(UserResponse("Calendar Day");" ";"  ");";")+1;10);" ";"");"MM/dd/yyyy");"MMMM")

 

 

4. Getting Month Value from Date prompt: Month Number=FormatDate(ToDate(Replace(Substr(Replace(UserResponse("Calendar Day");" ";"  ");Pos(Replace(UserResponse("Calendar Day");" ";"  ");";")+1;10);" ";"");"MM/dd/yyyy");"MM")

 

 

5. Getting Year Value from Date prompt: Year=FormatDate(ToDate(Replace(Substr(Replace(UserResponse("Calendar Day");" ";"  ");Pos(Replace(UserResponse("Calendar Day");" ";"  ");";")+1;10);" ";"");"MM/dd/yyyy");"yyyy")

 

 

6. Getting Last Year from above formula: LY=FormatNumber((ToNumber([Year])-1);"####")

 

 

7. Getting Before Last Year from above year formula: BLY=FormatNumber((ToNumber([Year])-2);"####")

 

 

8. Getting Start Date from Month prompt: Start Date=FormatDate(ToDate(Replace(Replace(Left(UserResponse("Calendar Day");9);" 1";"");" ";"");"MM/dd/yyyy");"dd.MM.yyyy")

 

 

9. Getting Month Value from Month prompt: Month Number=Left(UserResponse("ZMONTH_VAR");2)

 

 

10. Getting Year Value from month prompt: Year=UserResponse("ZMONTH_VAR")

 

 

11. Getting Fiscal Year Value from above formula: Fiscal Year=If [month] inlist("January";"February";March) Then formatnumber((ToNumber([Year])-1);"##") Else [Year]

 

 

12. Getting Last Day of Month: Last Day Of Month=If [Month Number] InList ("01";"03";"05";"07";"08";"10";"12") Then "31" ElseIf [Month Number] InList ("02") Then "28" Else "30"

Mettre de la cartographie dans un document WebI en BI4 pour ensuite le visualiser sous iPad, c’est aujourd’hui possible !

 

Cet article vous présente la façon d’obtenir cet effet, sans autre outil que WebI …

1)    Création du tableau Web Intelligence (WebI)

 

Pour arriver à obtenir de la cartographie sous SAP BI (l’application SAP BI Mobile sous iPad version 5.0.5.9) à partir d’un fichier Web Intelligence (WebI), il faut tout d’abord créer un tableau dans un document WebI.

 

Nous allons prendre un exemple avec des « Clients de la Grande distribution », qui souhaitent connaitre la quantité commandée, le montant de leur marge et le CA commandé par ville.

 

Dans Web Intelligence, créez donc un tableau standard avec le nom des différents magasins et les 3 indicateurs (la quantité commandée, le montant de leur marge et le CA commandé par ville).

 

Qui dit Cartographie, dit Ville et points géographiques ! Il faut donc ajouter avec une colonne « Ville ».

BO utilise la latitude et la longitude de chaque ville pour les représenter. Il faut donc ajouter une colonne « Latitude » ainsi qu’une colonne « Longitude ».

 

Pour cela,  je vous propose le lien d’un site qui permet de récupérer gratuitement la latitude et la longitude de chaque ville. (http://www.batchgeocodeur.mapjmz.com/). Vos villes seront donc ainsi géocodées gratuitement.

 

Vous obtenez donc un tableau avec les colonnes : Nom, Villes, Latitudes, Longitudes, (Vous pouvez rajouter des colonnes avec la région, département permettant la navigation dans les hiérarchies …).

 

Pour que SAP BI Mobile interprète ce tableau en carte, il faut lui donner un nom qu’il va pouvoir interpréter. Pour cela, sélectionnez le tableau, clic droit, puis cliquez sur « Format du tableau », pour enfin changer le nom du tableau et le renommer comme suit :

map_lt2_lo3_poi1

lt : numéro de la colonne ou se trouve la latitude (dans notre exemple ci-dessus : 2)

lo : numéro de la colonne ou se trouve la longitude (dans notre exemple ci-dessus :3)

poi : numéro de la colonne ou se trouve le nom de ce que l’on souhaite afficher (dans notre exemple ci-dessus : 1)

Il faut savoir que certaines informations sont obligatoires comme le lt, lat, poi… (il en existe des facultatives)

 

Il ne reste plus qu’à enregistrer votre document dans les dossiers publics et de l’associer à la Catégorie permettant d’afficher les documents dans l’application SAP BI Mobile (préalablement paramétrée lors de la mise en place de la mobilité).

Depuis votre IPad, ouvrez SAP BI, connectez-vous, puis sélectionnez le document que vous souhaitez afficher.

L’application mobile interprète automatiquement le tableau comme étant un composant à afficher sous la forme d’une carte et les indicateurs sont projetés par défaut sur les Villes du tableau.

2)    Pour aller plus loin !

Si vous souhaitez ajouter de la couleur pour représenter vos indicateurs, il suffit simplement de changer la couleur du titre de la colonne représentant un indicateur.

Vous pouvez ajouter une fenêtre PopUp à votre carte, vous pourrez ainsi ajouter des informations supplémentaires en cliquant sur un POI.

Pour cela, il faut créer une colonne PopUp dans votre tableau. Cela permet par exemple d’ajouter un graphe lorsque vous sélectionnez une ville :

Par exemple, dans la cellule de la colonne POI, il faut mettre le lien du graphe que vous souhaitez afficher :
iDocID=<identifierValue>&sIDType=CUID&sType=wid&sReportName=<ReportName>&sRefresh=Y&sReportPart=<ReportPartName>&lsS[Name]= »<value> »

Si vous le souhaitez, vous pouvez mettre plusieurs documents dans la fenêtre PopUp. Pour cela, il faut mettre dans la colonne plusieurs liens. (il faut les séparer par ||) :

iDocID=<identifierValue>&sIDType=CUID&sType=wid&sReportName=<ReportName>&sRefresh=Y&sReportPart=<ReportPartName>&lsS[Name]= »<value> »||iDocID=<identifierValue>&sIDType=CUID&sType=wid&sReportName=<ReportName>&sRefresh=Y&sReportPart=<ReportPartName>&lsS[Name]= »<value> »

IdentifierValue : c’est le numéro CUID de votre document.

ReportName : c’est le nom de l’onglet de votre Rapport.

ReportPartName : c’est le nom du graphe ou tableau que vous souhaitez afficher.

 

Vous devez également renommer votre tableau ainsi :

map_lt2_lo3_poi1_od11_gp1

od : numéro de la colonne ou se trouve le lien pour la fenêtre PopUp (dans notre exemple ci-dessus :11)

gp : numéro de la colonne ou se trouve le POI sur lequel on va afficher tous les indicateurs (dans notre exemple ci-dessus :1)

 

Ce procédé n’est en rien officiel chez SAP, nous ne sommes pas sûr qu’il sera maintenu dans les prochaines versions, mais est néanmoins … Magique !

 

Olivier Hébert

DeciVision

http://www.decivision.com

http://www.decivision.com/blog

Business requirement: Display 3-month running average for sales revenue in a 12-month timing window for each year, like the one below

0.png

This chart helps the business understand how well they perform in term of sales revenue in each year after slightly adjusting data for seasonality.

 

Here’s how we can achieve that.

 

Step 1: Build a report using eFashion Universe

1.png

 

Step 2: Create “3-month Running Average” variable for sales revenue

=RunningSum(Previous([Sales revenue];3))/3

 

Step 3: Extract “3-month Running Average” for each year (2005 & 2006)

2.png

And

3.png

 

If you add these 3 variables in the original table, you can see that the running average of sales revenue is extracted from the first calculation for each year.

4.png

 

We still need one more step to display data correctly. We cannot use 2005 RA and 2006 RA because WebI may get confused between 2005 and 2006 data. You can add the [Year] dimension to the chart, but you may not have a clear look for trending performance in each year.

 

Step 4: Clean running average data for each year so that they can be displayed on the chart separately.

Create 2005 variable

=Sum([2005 RA] In([Year];[Month]))

 

Create 2006 variable

=Sum([2006 RA] In([Year];[Month]))

 

Now we should be able to assign data to the chart as requested

5.png

 

And then we have the final result:

0.png

 

Huu Nguyen

Traditionally we have setup a separate publication for each report selection, i.e. A publication for Sales Manager 1, another for Sales Manager 2, another for Sales Manager 3 and so on.  As our publications grew it became obvious that this was no longer an efficient mechanism to manage our report distribution. We were changing eMail addresses as new people joined the organization and removing eMail addresses as other collegaues moved to different roles.

 

Each time this happened, you had to replace the existing scheduled job with a new one which we found tedious.  The Instance Manager view was also becoming very busy and it was getting difficult to see the wood through the trees. So we needed some way of thining out the number of publication we were producing and I read about the method of an Excel file with Dynamic Recipients.

 

I have now started to rollout the use of an Excel file containing the variable and the eMail address for report distribution and its been really useful.  I don't see us using this scenario for every single WebI report that we publish, but we will use where we can.

 

 

There is no right way of doing this or wrong way, it’s just an option that might help you if you have similar challenges to those that we did.  Here are some of my thoughts on the process.

 

 

Pro’s :

 

  • Adding or removing an email address to a scheduled publication is so much easier. The alternative was to change the Publication and then to remember to reschedule or delete/recreate the schedule.
  • Managing one scheduled job rather than one per selection (For one sales report we had 17 publications). On the instance manager section in SCM, having fewer jobs in that list is a lot easier!
  • We have setup Notifications for each publication to ensure that they were sent.  We received an eMail for a Successful or a Failed publication.  One eMail notification per publication was becoming a huge number of eMails to receive and check to be sure all was OK.  Using an Excel file we now get one confirmation email when the entire recipients list get their reports (or if it failed but that hasn't happened yet ).

   

Con’s :

 

  • Individual publications means individual notification of success or fail – if the job fails you might know where the root cause is immediately. (May have to resend the entire publication).
  • You lose the ability for individual customization in the Subject line of the eMail / File Name.

 

 

Things that I don't like  :

 

  • Not being able to pass more than one variable in a dimension.  For example if we have Cost Center report and I have one person responsible for 5 Cost Centers. I want to send them one eMail with all 5 of their Cost Centers combined in one report. Using this method, if Cost Center was the value I was passing to the WebI Document, the person would receive five separate reports.
  • Not being able to pass values for two different dimensions to the publication run .For instance, we might have an Area Manager who manage a few different Sales regions, it would be nice to have a report for Area Manager 1 – Region A, Area Manager 1 – Region B, Area Manager 2, Region C, Area Manager 2, Region D and so on.  One for the ideas panel! 
    So I would want to pass different values for Area Manager AND Region in the Excel file.
  • The NOFILTER command doesn't work the way you would expect it. While I have a workaround, it means adding an extra query to your WebI Document and I don't think this is good practice in the long run.
  • If the WebI publication content is empty – then I don’t want the file to be published. We have this option on Crystal but not in WebI.
  • Why do we need a WebI document created from the Excel file which then becomes the source for your dynamic recipients.  We need to eliminate this step!

 

 

One general suggestions :

 

  • Create an ad hoc publication for the odd time that you need it. This won't use the Dynamic Recipients method and it allows for once off runs of a publication. Sometimes people can’t find the eMail that was sent so it’s easy with an Ad Hoc version to facilitate their request for a rerun. Needless to say you will have to change the prompts, recipient address and eMail Subject line each time.

Hi All,

 

Business always interested in Trend/Arrows in tables instead of numbers .In 4.0 IPAD we can get this by writing some code.

we can use images and hyperlinks to get the same .But it needs some maintenance.

 

End Result

 

This blog gives you simple trick to show Trend/Arrows in your webi report as per condition.

We know that we can use ascii values in webi with CHAR() function.

CHAR(9660 ) - DOWN ARROW

CHAR(9650 ) - UP ARROW

CHAR(9679 ) - CIRCLE

 

> Insert new column to show your Arrows

>Write alerter with sub alter to show your Icons with different colors per condition.

 

>write your condition : Sales Qty less 10

Format your alerter as per req. In my case DOWN Arrow

 

Set color for ARROW :RED and width of arrow (you can change it by increasing font size and style as BOLD)

 

Now you need to create sub alert for the Sales Qty greater than 10

create alert

 

Format :

Display : =CHAR(9650)

Format : GREEN color

 

Now apply Alert in your blank column. That's it

 

Similarly you can create Alerters for ICONS also.

You can get ascii char values for different shapes from here

http://www.nwmtwd.com/misc_character_sets.html

Note : You can use all the ascii codes. Neglect &# and use the number .Some of them may give (rectangle box) in rich client /rich internet mode ,but you'll get the symbol in html mode (reading)

There are some discussions about selecting top N data recently. I have been haunted by the idea. In this post, I will show you how to choose top N data using input controls without building extra tables. In addition, you can specify any integer value for N to see the top N data.

 

I utilize eFashion Universe for demonstration purposes. I also am assuming that you are somewhat familiar with Webi 4.0 – Rich Internet Application Viewing Mode.

 

Step 1: Build a Webi report using eFashion Universe

step1.png


Step 2: Create a new variable (Rank) to show the ranking of sales revenue in the table

step2.png


Step 3: Sort sales revenue in the table from the highest to the lowest (descending)

step3.png


Step 4: Create a new input control on variable Rank to show top N Sales revenue

Choose variable Rank as an object to filter data. Then click Next.

step4.png


Define the input control as shown in the picture below. Click Next.

step4-1.png


Select the report element to use with the input control. Click Finish.

step4-2.png


Now enjoy the result.


Top 3 sales revenue by SKU desc

e1.png


Top 5 sales revenue by SKU desc

e2.png


Top 10 sales revenue by SKU desc

e3.png

 

Thanks,

Huu Nguyen

Currently, there is no option to draw a linear or polynomial trend line in a webi chart. However, we can use mathematical calculations to overcome the challenge.

In this post, I utilize eFashion Universe for demonstration purposes. I am assuming that you are somewhat familiar with regression analysis and Webi 4.0 – Rich Internet Application Viewing Mode.

 

Warm-up reminders:

A linear trend line is defined by this equation: Y= a0 + b*X1 , in which we are assuming that

  • variable X is a timing factor (day, month, year etc..) and can be used to explain the fluctuation of the output Y;
  • a0 & b are the best estimators of the model and can be calculated using the ordinary least squares (OLS) method.

 

We define: x1=X1-Average[X1] and y=Y-Average[Y] then

  • b = Sum[x1*y]/Sum[x1*x1]
  • a0 = Average[Y] - b*Average[X1]

 

Similarly, a polynomial trend line can be defined by this equation: Y=a + b1*X1 + b2*X2, in which:

  • variable X1, X2 are timing factor (day, month, year etc..) and can be used to explain the fluctuation of the output Y;
  • X2 = X1 * X1
  • a, b1 & b2 are the best estimators of the model and can be calculated using the ordinary least squares (OLS) method.


We also define x2=X2-Average[X2] then

  • b1 = {Sum[x2*x2] * Sum[x1*y] – Sum[x1*x2] * Sum[x2*y]}/ {Sum[x1*x1] * Sum[x2*x2] – Sum[x1*x2] * Sum[x1*x2]}
  • b2 = {Sum[x1*x1] * Sum[x2*y] – Sum[x1*x2] * Sum[x1*y]}/ {Sum[x1*x1] * Sum[x2*x2] – Sum[x1*x2] * Sum[x1*x2]}
  • a = Average[Y] – b1*Average[X1] – b2*Average[X2]

 

Create a linear trend line in Webi 4.0

Step 1: Build a Webi report using eFashion Universe.

step1.png

Step 2: Create new variables for those in the warm-up reminders Section. Note that we don’t have to create a new variable for each of them.

 

Create X1 (assuming we are showing trend lines by month)

step2.png

Similarly, create x1y

=([X1]-(Average([X1]) In Block))*([Sales revenue]-(Average([Sales revenue]) In Block))

 

Create x1x1

=([X1]-(Average([X1]) In Block))*([X1]-(Average([X1]) In Block))

 

Create b

=(Sum([x1y]) In Block)/(Sum([x1x1]) In Block)

 

Create a0

=Average([Sales revenue]) In Block - [b]*(Average([X1]) In Block)

 

Create Linear Trend

=[a0]+[b]*[X1]

 

Step 3: Insert a webi chart with the linear trend line we have created:

Go to Report Element \ Chart \ Line

step3-1.png


Assign data to the new chart

Step3-2.png


Enjoy the result. The image below shows linear trend line and Sales revenue in DC only

Step3-3.png

Below is the Sales revenue Report for California

Step3-4.png


Create a polynomial trend line in Webi 4.0

Assuming we continue to use some of the work we have done in the Linear Trend Line section.


Step 4: Create additional variables for the polynomial trend line

Create X2

=[X1]*[X1]

 

Create x2x2

=([X2]-(Average([X2]) In Block))*([X2]-(Average([X2]) In Block))

 

Create x2y

=([X2]-(Average([X2]) In Block))*([Sales revenue]-(Average([Sales revenue]) In Block))

 

Create x1x2

=([X1]-(Average([X1]) In Block))*([X2]-(Average([X2]) In Block))

 

Create b1

=((Sum([x2x2]) In Block)*(Sum([x1y]) In Block)-(Sum([x1x2]) In Block)*(Sum([x2y]) In Block))/((Sum([x2x2]) In Block)*(Sum([x1x1]) In Block)-(Sum([x1x2]) In Block)*(Sum([x1x2]) In Block))

 

Create b2

=((Sum([x1x1]) In Block)*(Sum([x2y]) In Block)-(Sum([x1x2]) In Block)*(Sum([x1y]) In Block))/((Sum([x2x2]) In Block)*(Sum([x1x1]) In Block)-(Sum([x1x2]) In Block)*(Sum([x1x2]) In Block))


Create a

=(Average([Sales revenue]) In Block)-[b1]*(Average([X1]) In Block)-[b2]*(Average([X2]) In Block)

 

Create Poly Trend

=[a]+[b1]*[X1]+[b2]*[X2]

 

Step 5: Add the polynomial trend line in the current chart

Right-click on the chart then choose Assign Data…

Step5.png

Click on the plus sign in the Value Axis 1 Section, then choose Poly Trend.

step5-2.png

Enjoy the result.

step5-3.png

 

If you have any questions, please leave a comment below and I will try to answer them as soon as I can.

 

Happy Valentine!

Huu Nguyen


Here’s a little trick I use for establishing matches in merged dimensions. I’m using the Island Resorts Universe to demonstrate.

 

Let’s say I have the Sales Data for 2006 to establish which customers bought holidays in 2006, and I want to compare this to reservations in 2007 to see which of these customers made reservations for the following year as well.

 

We all know the easy way to do this is via a Combined (intersection) query:

Cquery1.JPGCquery2.JPG

..which gives us the following (correct) output:

CombinedOutput.JPG

..but if we want to show this in the context of our Sales 2006 data, what can we do?

 

I have my combined query already (as above), and I've added a query returning just the 2006 Sales customers (essentially this is the "2006 Sales" half of the combined query). For full disclosure, I’ve also added a query to return the 2007 reservations data (which is the other half of the combined query) so you can check the results – which for all three of these is:

Alloutput.JPG

…but really the 2007 data isn’t necessary – all we need is the combined query and the 2006 Sales Data. I'm merging the dimensions in the report.

 

So, I want to show all of my 2006 Customers and highlight those who have reserved in 2007.

 

The first thing you might think of doing is something like this:

=If [Sales].[Customer]=[Reservations].[Customer] Then 1 Else 0

 

..which doesn't quite hit the spot. Try it yourself.

 

Next, maybe:

=Match(ReportFilter([Sales].[Customer]);"*"+[Reservations].[Customer]+"*")

 

..which also doesn't quite cut the mustard.

 

So what can we do?

 

Here’s my solution, which like most things I try to do, is low maintenance and involves no universe work in the background.

 

Create another query containing just the Customer object, and in the query filter section, use the same object but set it to return values from another query. The query to return values from is our first query – the combined one – which is the intersection between 2006 Sales and 2007 Reservations:

query4.jpg

I’m calling this query [Filter Query]. Then back in the report, we need to create a couple of objects. Firstly, this one which I will call [SQL]:

 

=DataProviderSQL([Filter Query])

 

This object returns the SQL statement from the query, which is this:

SQL.JPG

As you can see, it contains a list of our Customers from the Combined Query. We can now use this as a basis to match against our results from the 2006 Sales Customers query. We do this using our second object:

 

=Match([SQL];"*"+[Sales].[Customer]+"*")

 

When we apply this object to our 2006 Sales Customers, we can see that those who have reserved in 2007 have a “1” next to them, indicating a true result for the match function:

Matched 1.JPG

It’s not hard to turn this into an alerter instead:

Matched 2.JPG

One caveat: This mechanism is limited by the number of results you can return using query on query, which I believe is set by default to 1000.

 

Like many things in Business Objects, there's often more than one way to accomplish something. This was my way of performing matches with merged dimensions, there may well be other easier ways....let me know if there are!

 

Either way, I hope you find this useful!

     There has always been requirement wherein webi reports in BO are scheduled to run periodically for dynamic prompt values.

For e.g. We have reports which are scheduled to run monthly with cal.year month input as last month.

(P.S.  This document considers only universe based on bex queries for using this method.)

 

This obviously is easily achieved by using customer exit variables in BI. However, the only issue in using such variables is that there is no prompt for such variables in BO reports. Due to which such reports can by default be executed only for the values that are passed in the variables from the exit.

 

      But, many a time we have requirements wherein we want a report to by default get executed for the dynamic value passed through the variable when it is scheduled on periodic basis and also have a prompt option so that user can execute it for any value he/she requires. Since, there is no such option in BO ( as of now atleast ) to have a prompt with default values, and after looking out for a work around everywhere and failing to find any thing which could suffice such a requirement, I have found a work around which has helped to overcome this difficulty.

    

       To explain the method used, I would take a simple scenario where every month a BO report which is based on a bex query is scheduled to execute for last month i.e. Cal.year month(0calmonth) is the input for the query and the report should get executed by default for last month.

The idea is, whenever  ‘# ‘(not assigned ) value is passed to the query, the query would by default pick last month as value for the 0calmonth characteristic. However, if there is any other value passed as input other than  ” #” then the input value is to be considered as value for 0calmonth. To achieve this we would require two variables :

1. Manual Input variable (Based on 0Calmonth). 2. Customer exit variable (Based on 0Calmonth).

 

CV_CMON.jpgCV_MONDEF.png

 

Here, the manual input variable (i.e. CV_CMON) would contain the value which would be provided as input in the query

and the customer exit variable (i.e. CV_MONDEF) would be filled in exit with the desired value at I_Step = “2” (Call after variable entry ).

Below is the screenshot of the logic used to fill the variable CV_MONDEF.

 

EXIT.png

 

Next, while creating the query on which the universe would be built, restrict the 0calmonth characteristic to both the variables.

As shown in below screenshot.

 

Bexquery.png

 

On execution of query as shown in below screenshots, if ” #”  value is entered in variable CV_CMON then CV_MONDEF is filled with last month value

whereas if any other value is provided as input then CV_MONDEF is filled with value which is provided as input.

 

Defl_Input.pngDefl_Op.png

 

 

Input.pngOutput.png

 

 

So as shown in the screenshots above, based on the input, the variable is restricted to the desired value and hence the desired output is achieved.

Now, using this a publication of the Webi report in BO can be created and scheduled with the Cal.year month as “#”.

 

Webi.png

 

So this way the report will be scheduled to run monthly based on the default value of last month and also at the same time

if user wants to change the cal.year month and execute the report the same can be done. Both the things can be achieved with the same webi report.

 

Hope this helps.

Sometimes you want your users to open the latest instance of a report and not the report itself.

 

Sadly, this option is set as a global default across Infoview - when you click on a report you can either open the report itself OR the latest instance.

 

CMS>MANAGE>APPLICATIONS>INFOVIEW>PREFERENCES

Default Viewing Action.JPG

Luckily there's an easy workaround.

 

Create an OpenDocument link for your report, and include the &sInstance=Last parameter.

 

Then, create a new hyperlink in Infoview:

 

NEW>HYPERLINK

 

Insert your OpenDocument link into the URL and title as per the report.

 

Finally, use this hyperlink in place of the report (e.g move the report somewhere else and use the link in its place).

 

Easy huh? Hope you find useful

 

(note: may also work for 4.x, don't have it to test, apologies.)

Dear All,

 

Here in SAP BusinessObjects Web Intelligence Space, it is noted that many of us are searching/asking questions on Date/Time Dimensions.

I am submitting all necessary formulas for the same. Hope it is useful to all.

 

We need daily,monthly,quarterly & yearly date variables.

 

Before making any date variable please make a variable which holds current date.

 

Reason to make another variable for Current Date is, for validation purpose you can change date manually and then check it whether all other date variables are working properly or not.

 

(1) Current Date = CurrentDate()

 

(2.1) Current Year =If(Month([Current Date]) InList("January")) Then(FormatNumber(Year([Current Date])-1;"###")) Else (FormatNumber(Year([Current Date]);"###"))

 

if you want to use Current Year for YTD variable then please use 2.2 formula or use 2.1

 

(2.2) Current Year for YTD=If(Month([Current Date]) InList("January";"February";"March")) Then(FormatNumber(Year([Current Date])-1;"###")) Else (FormatNumber(Year([Current Date]);"###"))

 

(3) Current Fiscal Year =FormatNumber([Year];"####")  Where ([Year]=Year([Current Date]) And [Month] InList (1;2;3) And MonthNumberOfYear([Current Date]) InList (1;2;3;4) Or  [Year]=Year([Current Date])-1 And [Month] InList (4;5;6;7;8;9;10;11;12) And MonthNumberOfYear([Current Date]) InList (1;2;3;4)

Or [Year]= Year([Current Date])And MonthNumberOfYear([Current Date]) InList (5;6;7;8;9;10;11;12))

where [Year]= Year of Your Transaction Date, and it is for Indian Fiscal Year

 

(4.1) Last Year =If(Month([Current Date]) InList("January")) Then(FormatNumber(Year([Current Date])-2;"###")) Else (FormatNumber(Year([Current Date])-1;"###"))

 

if you want to use Current Year for LYTD variable then please use 4.2 formula or use 4.1

 

(4.2) Last Year for LYTD =If(Month([Current Date]) InList("January";"February";"March")) Then(FormatNumber(Year([Current Date])-2;"###")) Else (FormatNumber(Year([Current Date])-1;"###"))

 

(5) Last Fiscal Year =FormatNumber([Year];"####")  Where ([Year]=Year([Current Date])-1 And [Month] InList (1;2;3) And MonthNumberOfYear([Current Date]) InList (1;2;3;4)  Or [Year]=Year([Current Date])-2 And [Month] InList (4;5;6;7;8;9;10;11;12) And MonthNumberOfYear([Current Date]) InList (1;2;3;4) Or [Year]= Year([Current Date])-1 And MonthNumberOfYear([Current Date]) InList (5;6;7;8;9;10;11;12))

where [Year]= Year of Your Transaction Date, and it is for Indian Fiscal Year

 

(6) CYCM=Concatenation(Left(Month(RelativeDate([Current Date];-DayNumberOfMonth([Current Date])));3);Concatenation("-";[Current Year]))

*Current Year Completed Month

 

(7) CYLM=Concatenation((Concatenation(Left(Month(RelativeDate(RelativeDate([Current Date];-DayNumberOfMonth([Current Date]));-DayNumberOfMonth(RelativeDate([Current Date];-DayNumberOfMonth([Current Date])))));3);"-"));If(MonthNumberOfYear([Current Date])=1 Or MonthNumberOfYear([Current Date])=2) Then (Right(FormatNumber(Year([Current Date])-1;"####");4)) Else (Right(FormatNumber(Year([Current Date]);"####");4)))

*Current Year Last Month

 

(8) LYCM=Concatenation(Left(Month(RelativeDate([Current Date];-DayNumberOfMonth([Current Date])));3);Concatenation("-";[Last Year]))

*Last Year Completed Month

 

(9) YTM=Concatenation(If(MonthNumberOfYear([Current Date]) Between (5;12)) Then (Concatenation((Concatenation("Apr";"-"));Right(FormatNumber((Year([Current Date]));"####");4))) Else(Concatenation(Concatenation("Apr";"-");Right(FormatNumber((Year([Current Date])-1);"####");4)));If(MonthNumberOfYear([Current Date])=5)Then("")Else(Concatenation(" to ";(Concatenation((Concatenation(Left(Month(RelativeDate([Current Date];-DayNumberOfMonth([Current Date])));3);"-"));(If(MonthNumberOfYear([Current Date])=1) Then(Right(FormatNumber(Year([Current Date])-1;"####");4))Else(Right(FormatNumber(Year([Current Date]);"####");4))))))))

*Year Till Month

 

(10) LYTM=Concatenation(If(MonthNumberOfYear([Current Date]) Between (5;12)) Then (Concatenation((Concatenation("Apr";"-"));Right(FormatNumber((Year([Current Date])-1);"####");4))) Else(Concatenation(Concatenation("Apr";"-");Right(FormatNumber((Year([Current Date])-2);"####");4)));If(MonthNumberOfYear([Current Date])=5)Then("")Else(Concatenation(" to ";(Concatenation((Concatenation(Left(Month(RelativeDate([Current Date];-DayNumberOfMonth([Current Date])));3);"-"));(If(MonthNumberOfYear([Current Date])=1) Then(Right(FormatNumber(Year([Current Date])-2;"####");4))Else(Right(FormatNumber(Year([Current Date])-1;"####");4))))))))

*Last Year Till Month

 

(11) MTD=Concatenation(Left(FormatDate([Current Date];"dd/MM/yyyy");2) ;Concatenation("-";Concatenation(Concatenation(Left(Month([Current Date]);3);"-");Right(FormatNumber(Year(CurrentDate());"####");4))))

*Month Till Date

 

(12) YTD=Concatenation(Concatenation("Apr-";[Current Year for YTD]);If(MonthNumberOfYear([Current Date])=4)Then("") Else(Concatenation(" to ";Concatenation(Concatenation(Left(Month([Current Date]);3);"-");Right(FormatNumber(Year([Current Date]);"####");4)))))

* Year Till Date

 

(13) Current Quarter

=If(Quarter(RelativeDate([Current Date];-DayNumberOfMonth([Current Date])))=1) Then(Concatenation(Concatenation("Q4:Jan-";[Current Year]);Concatenation(" to Mar-";[Current Year]))) ElseIf(Quarter(RelativeDate([Current Date];-DayNumberOfMonth([Current Date])))=2) Then(Concatenation(Concatenation("Q1:Apr-";[Current Year]);Concatenation("to Jun-";[Current Year]))) ElseIf(Quarter(RelativeDate([Current Date];-DayNumberOfMonth([Current Date])))=3) Then(Concatenation(Concatenation("Q2:Jul-";[Current Year]);Concatenation("to Sep-";[Current Year]))) ElseIf(Quarter(RelativeDate([Current Date];-DayNumberOfMonth([Current Date])))=4) Then(Concatenation(Concatenation("Q3:Oct-";[Current Year]);Concatenation("to Dec-";[Current Year])))

*based on Indian Fiscal Year

 

(14) Last Quarter

=If(Quarter(RelativeDate([Current Date];-DayNumberOfMonth([Current Date])))=1)

Then(Concatenation(Concatenation("Q3:Oct-";[Last Year]);Concatenation(" to Dec-";[Last Year])))

ElseIf(Quarter(RelativeDate([Current Date];-DayNumberOfMonth([Current Date])))=2)

Then(Concatenation(Concatenation("Q4:Jan-";[Current Year]);Concatenation(" to Mar-";[Current Year])))

ElseIf(Quarter(RelativeDate([Current Date];-DayNumberOfMonth([Current Date])))=3)

Then(Concatenation(Concatenation("Q1:Apr-";[Current Year]);Concatenation("to Jun-";[Current Year])))

*based on Indian Fiscal Year

 

(15) Last to Last Quarter

=If(Quarter(RelativeDate([Current Date];-DayNumberOfMonth([Current Date])))=1)

Then(Concatenation(Concatenation("Q2:Jul-";[Last Year]);Concatenation(" to Sep-";[Last Year])))

ElseIf(Quarter(RelativeDate([Current Date];-DayNumberOfMonth([Current Date])))=2)

Then(Concatenation(Concatenation("Q3:Oct-";[Last Year]);Concatenation(" to Dec-";[Last Year])))

ElseIf(Quarter(RelativeDate([Current Date];-DayNumberOfMonth([Current Date])))=3)

Then(Concatenation(Concatenation("Q4:Jan-";[Current Year]);Concatenation("to Mar-";[Current Year])))

ElseIf(Quarter(RelativeDate([Current Date];-DayNumberOfMonth([Current Date])))=4)

Then(Concatenation(Concatenation("Q1:Apr-";[Current Year]);Concatenation("to Jun-";[Current Year])))

 

(16) Last Year Current Quarter

=If(Quarter(RelativeDate([Current Date];-DayNumberOfMonth([Current Date])))=1) Then(Concatenation(Concatenation("Q4:Jan-";[Last Year]);Concatenation(" to Mar-";[Last Year])))

ElseIf(Quarter(RelativeDate([Current Date];-DayNumberOfMonth([Current Date])))=2)

Then(Concatenation(Concatenation("Q1:Apr-";[Last Year]);Concatenation("to Jun-";[Last Year])))

ElseIf(Quarter(RelativeDate([Current Date];-DayNumberOfMonth([Current Date])))=3)

Then(Concatenation(Concatenation("Q2:Jul-";[Last Year]);Concatenation("to Sep-";[Last Year])))

ElseIf(Quarter(RelativeDate([Current Date];-DayNumberOfMonth([Current Date])))=4)

Then(Concatenation(Concatenation("Q3:Oct-";[Last Year]);Concatenation("to Dec-";[Last Year])))

 

 

Best Regards,

-Harshil J Joshi

 


A lot of the support calls I get on result in a short tutorial on how the web browser is utilizing the Java Runtime Engine (JRE) to run the Web Intelligence Rich Internet Applet (RIA) aka Java Report Panel or Java Viewer for those that have been with Webi for a while.  Below I will discuss some of the common scenarios I have seen on support calls lately.  I included a top level summary for those that want the quick and dirty and a longer more detailed version for those that want more info.

 

SUMMARY

  • BI 4.x supports 32-bit browsers only (in most cases, read the PAM for more info)
  • You need to make sure that you setup the 32-bit JRE settings instead of the 64-bit ones.
  • Control Panel "Java" icon launches the 64-bit Java Control Panel in most cases so best to launch javacpl.exe from the C:\Program Files (x86)\Java\... directory
  • Read my blog for more details on some recommended Java settings.

 

DETAILS

Oracle's latest JRE releases have included a number of security enhancements and changes to the default configuration options of the local JRE installs that can drastically reduce performance of the RIA and any Java applet that you may be running.  The release notes for JRE 1.7 Update 25, 45, and 51 are listed below for your reference on the changes I am about to talk about:

 

 

Most notably, the latest versions require applets to include some new manifest file attributes in their JAR files.  If these attributes are missing or set incorrectly, end users will start getting security pop-ups and warnings that are quite annoying and can cause our applet to misbehave if answered incorrectly.  SAP is working to update the JAR files in Patch and Support Package releases but I won't get into that in this post as I have another topic in mind.  It's just important to note this as its common with the latest JREs to see these warnings.

 

Another major change that I will mention is an option called "Online Certificate Status Protocol" that is now enabled by default.  What this option does is forces the JRE to go out to internet servers (verisign) to check the validity of a certificate.  Since our applet has over 60 signed JAR files, this check is done over 60 times and often adds anywhere from 30-90 seconds on to the load time of the applet.  If you are having Performance issues with the load time of the applet, this is the first thing to check!

 

For more details on evaluating Performance issues, visit my blog and wiki posts here:

Performance tuning tips for BI 4.0 Web Intelligence (Chapter 1 - Client Tier)

 

Ok, on to the subject that I wanted to blog about.  Ensuring that you are actually setting up the JRE that Webi RIA is using.

 

So, first things first.  The Webi RIA uses either a 32-bit or a 64-bit JRE, depending on your browser bits, so you need to know which one you are using first.

Another interesting fact is that BI 4.0 only supports a 32-bit browser in most cases.  So those of you that are using 64-bit browsers, you may not be running in a supported scenario.  Be sure to reference the Product Availabiltiy Matrix that matches your version to see what browsers are supported.

 

Once you know what bit browser you are using, I'll assume 32-bit in these steps since that is what we support, you can go ahead and open the correct Java Control Panel (CP)

 

The best way that I have found to open the Java CP is from Windows Explorer.  This way you can ensure that you are opening the correct bit CP.  For example, to open the 32-bit Java Control Panel, I follow these steps:

  1. Open Windows Explorer
  2. Navigate to:  C:\Program Files (x86)\Java\jre7\bin  (This is the 32-bit JRE directory because it's in the c:\Program Files (x86)\ directory)
  3. Open the javacpl.exe from that directory.
  4. Set my JRE options. 

 

My blog post and the wiki it refers to above will go over some of the options you need to check in the Java Control Panel. 

 

The "Java" icon in Windows Control Panel is almost always opening the 64-bit Java Control Panel when clicked.  I have had countless calls from customers saying that they have set the options I recommended but it still wasn't working and the root cause is almost always that they set the options for the *wrong bit* Java.

 

Hope you got some use out of this.  If it prevents even 1 support call then it has saved me some time!

Feel free to let me know of any other tips, tricks or gotchas that you have found with the RIA and Java Runtime Engine.

Jb

Often when you schedule a report, you’ll use a “Current date” object to ensure the data returned is from the date the report was scheduled.

But sometimes you want your end users to be able to refresh this report and enter their own date parameters – but it is setup to use a current date object so that you don’t need to enter a date each time you schedule the report. How can we get the best of both worlds?

 

There are many solutions which usually involve some kind of universe work in the back end, but here’s a nice easy one which requires no universe work at all.

 

I'm using Island Resorts Marketing as an example, but you can use anything that includes a date object.

 

Create a query just returning the Reservation Date:

1.jpg

Then, in the query filters section, drag the same object in three times, and arrange it like so:

2.jpg

Make sure you setup the two prompts as Optional. This is very important or this process will not work – they have to be optional prompts! In the above example I’m using a hard coded date in the middle field, but this can just as easily be a Current Date object (there isn’t one in Island Resorts Marketing, and I’m too lazy to add one – sorry).

 

Now, test your report. Leave your prompt blank and you will return the hard coded date (or object if you have it setup):

3.jpg4.jpg

Now, do it again but this time enter a date into your optional prompt:

5.jpg6.jpg

There you have it – when you don’t enter a value it will use whatever you have in the middle date filter.

2.jpg

…and when you do, it will use whatever you enter into the prompt.

 

Therefore, when you schedule the report, you just leave the optional prompt blank to use the default from the middle filter. When users refresh the report, they can enter a date and it will return data for the date they enter.

 

Simple eh? You can apply this mechanism to anything, it doesn't have to be dates.

 

Hope you find useful

Actions

Filter Blog

By author:
By date:
By tag: