1 2 3 12 Previous Next

SAP BusinessObjects Web Intelligence

167 Posts

Hi,

 

Sometimes we face a requirement of concatenating values from an object., like below screen :

 

concatenate1.png

 

This can be achieved with 3 variables :

 

[conc] , a dim variable

[conc] = ([State]+","+Previous(Self));

[final] a measure variable

 

[final] =Last ([conc] ForEach ([State])) In Report

 

[No_ending_comma] a measure variable

 

[No_Ending_Comma] = Left([Final];Length([Final])-1)

 

How does it work :

 

The [conc] variable, will concatenate the value of [State] and the previous value of [conc], since the Previous value of 'California' doesn´t exist, it returns null and the the first row of [conc] is justa California. Refer to the above image, column [conc] , to see the values of [conc] as [State] is shown.

 

The  second variable [final] is where the 'trick' works :

 

It takes the last values of [conc] in the report calculated in the context of [State]. I use the ForEach operator so I can get the last value independent of the context used.

 

This example shows the values concatenated in descending order. To get it in ascending order, use [conc_asc] = (Previous(Self)+","+[State])

[final_asc] = =Last ([conc] ForEach ([State])) In Report

[No_ending_comma_asc  =  Rigth([final];Length([final]-1)

 

And what about the following cenario :

 

 

The requirement is to concatenate the stores name for each state

 

 

Here, I queried e-fashion for [State] and [Store Name]

 

concatenate2.png

 

 

 

I altered [conc] to :

 

=(Previous(Self;([State]))  ForEach([State];[Store name]) +","+([Store name] ForEach([State];[Store name])))

 

and

 

[final] to :

 

=Last ([conc]ForEach([State];[Store name])) In ([State])

 

Cheers,

Rogerio

 

 

the stores name for each state

Hello Everyone,

 

In my last blog, I mentioned about the supported input formats for selection option BW variable for the newly introduced feature of manually entering BW prompts.

 

I tested the new feature further in webi rich client and after some effort was able to make it work.

 

Here is the step by step instruction on how to make this work.

 

 

  1. I started testing this feature by first creating a BW Query with a Selection option variable prompt as shown in the screenshot below:

 

 

Image 1.PNGImage 2.PNG

 

 

 

 

 

2. This is the most important step. Add a registry key for a client machine running webi rich client at the location below:

 

 

Image 6.PNG

 

The Value Name is the next number in sequence. Depending on your configuration, it may be different then what you see below. The Value Data for the key is “–Dsap.sl.bics.variableComplexSelectionMapping=multivalue”.

 

Image 7.PNG

 

3. I then created a webi report based off the BW query using Webi rich client. I tried using the input formats as mentioned in the last blog (intervals, Expression with operator and so on).

 

Image 3.PNG

 

4.  I added multiple prompt values for Country Key  as shown below:

 

 

 

 

Image 4.PNG

5. Ran the report and checked the results:

Image 5.PNG

 

 

Above dataset in the report shows that data brought back consist of countries with key value 1,2,3,4,6,25 and 33 excluding 5 (as NOT INCLUDING 5 was one of the conditions.)

 

I hope this is helpful.

 

Regards,

Ejaz

We often get questions regarding strange calculation results when using decimal numbers in Web Intelligence.

 

Typically:

  • Why don't I get 0 when I do A + B - C, although C is supposed to be equal to A + B?
  • Why does the result of this running sum is different when I change the sorting of my table?
  • Etc.

 

These strange calculation results are due to the fact that, internally, Web Intelligence represents decimal numbers using the 64-bit precision version of the Floating Point data type defined by the IEEE 754 standard.

 

A detailed explanation of that standard can be found in the following Wikipedia page: http://en.wikipedia.org/wiki/IEEE_floating_point.

 

With the IEEE 754 standard on floating point arithmetic, decimal numbers cannot be perfectly represented in binary form, since not all their digits can be trusted. In fact, the number of digits which can be trusted actually depends on the size of the representation.

 

With the 64-bit representation (also called "double precision") used in Web Intelligence, the number of bits used for stocking the data is 53 and, as explained in the above Wikipedia page, the number of digits which can be trusted is: log(2^53), rounded to 15.

 

IMPORTANT TO REMEMBER: In Web Intelligence, the maximum number of digits which can be trusted in a decimal number is 15 and that takes into account digits both before and after the decimal mark.


Example:

  • 100,000,000,000 + 0.001 will correctly result in 100,000,000,000.0010 because this decimal number only requires 15 trusted digits to be represented (12 digits before the decimal mark and 3 after).
  • 100,000,000,000 + 0.0001 will wrongly result in 100,000,000,000.0000 because the correct result would require 16 trusted digits to be represented (12 before the decimal mark and 4 after)


With this limitation in mind, summing decimal numbers might not always give the expected result, especially if the expected result is 0...!

 

For example, summing 19000.2, 0.123, -9100.3, -0.000000000002543, etc. will allow for only 10 digits after the decimal mark to be trusted. Indeed, 5 digits before the decimal mark are already used by the largest number 19000.2. Consequently, if the result of that sum is something like: -0.000000000013327, it will be indistinguishable from 0, by the IEEE 754 standard.

 

Therefore, if a 0 result from a decimal numbers calculation is used as a condition for further processing in a Web Intelligence document, it is highly recommended to convert the decimal numbers into integers before that condition is evaluated. This rounding operation can be done with the Round(number; round_level) formula, using 0 for the round_level parameter.

 

Now, why is changing the sorting of a table might also change a decimal calculation result?

 

Well... Another major limitation of the IEEE 754 floating point format is that the integer part of decimal numbers is represented by a fraction and is therefore approximated. As a consequence, there will be a rounding error which will be propagated along the calculation, leading to different results according to the way the values are sorted.

 

Example:

Say, we have A = 1,000,000, B = 1.2 and C = -1,000,000

  • A + B + C will result in 1.19999999995343E0
  • A + C + B will result in 1.2E0

This is because the rounding error does not propagate the same way through A + B + C, than it does through A + C + B.

 

I hope these "strange calculation results" will be clearer now!

 

If not, please do not hesitate to ask questions below.

 

Best regards,

 

Pascal.

Hi Team ,

It's simple trick to display totals of stacked column like below

 

On top

 

at bottom (along with category axis)

 

How to do !

On Top

I have Business area and fiscal period wise Revenue stocked column chart.

1.Create a stacked column chart with Business Area in category axis ,Revenue in Values axis  and Region Type : Fiscal Period

2.Now we need totals to display on top .Create a detail variable to get Business are wise totals .

Total =FormatNumber([Capital  Cost FC] In ([Business area]);"#")

Note : If you have big numbers ,then it is better to show them in Millions (if Value)/MT (if Quantity) .So that we can adjust totals to look like for each column.

 

3.Create a variable with dummy measure .This is to have measure against Detail total variable

Dummy =100

 

4.Create a column chart (any chart ) with Total variable in X- axis , Dummy measure variable in Y-axis.Fiscal period in region type to have context (if any to measure)

5.Show only category axis only .Unchecked value axis, title,legend ,..

6.select palette for chart : create a custom palette with all white colors

This is to don't show columns in chart.Format column chart category axis to show numbers in bold color .Decrease height of column chart to get the category axis and some space for columns .By doing this we should get values like below.

7.This is important part .Align this column chart on top of stacked chart so that it will appear as total of the column.

 

Cons :

* we need to have another chart , it will heavy our report .

* we should be very concise on formatting and aligning.

* It is not possible to show very outside of each column .All totals are at same height.

 

at bottom

Assign Total -detailed variable in category axis .Insert a blank cell , name it Totals and align it as to represent category axis total .

Cons:

Totals are shown at category axis itself .We should inform to user .

 

Hope somebody will find it helpful.

Ever try to design a report using a large dataset?  Doing this can be a performance nightmare as you may not have applied the filters you wanted or the query to the database may take a long time to complete.  So how do we improve this?  One feature that exists in Web Intelligence to help with this is called Data Sampling.  This feature already existed in the product for many relational databases through the unx format and in SP05 support for this feature was added for relational HANA connectivity.

 

I had never used this functionality, so the first question for me was, where do I go to turn it on.  The answer is, within the query panel by pressing on the Query Properties button as seen below:

QueryPropertiesButton.png

Pressing this button brings up the Query Properties screen where there is a section called "Sample" which has two properties, "Sample Result Set" and "Fixed" as seen here:

QueryProperties.png

You may notice that the "Fixed" property is greyed out, this is because currently this functionality is not available for HANA.  What the fixed functionality provides is so that each refresh will return the same data each time.


My next question was, what does this sample result set actually do and how does it work.  I referred to the Web Intelligence User Guide which in section 2.1.3.7.3 tells us that it applies a database level restriction on the query.  So what does this really mean.  The first thought was, this must mean it is part of the SQL that is being generated, so I clicked on the view SQL button within the Query Panel, as seen here:

ViewSQLButton.png

This brings up the SQL Statement that will be run.  In my case, this is what I saw:

ViewSQL.JPG

What we see is that part of the SELECT statement, we have added TOP 500, as that was my Sample Result Size I had set earlier.  Additionally, at the end of the SQL statement, we see ORDER BY RAND().  If you set this property when you first see the query panel when creating a new document, you never need to wait for a large query to return, and you can come back anytime to uncheck or recheck this property as needed.


How does this differ from the "Max rows retrieved" setting?  This setting queries the database for all values, but then truncates the result that is actually displayed and is when you will see the partial results warning next to both the refresh status at the bottom of the report, as well as in the data tab.  To access the data tab, you click the button at the top right of the viewer as seen here:
DataView.png


On this screen we can see the following warnings related to these two settings which let you know if there are partial results, sampled results, or both.  The both scenario is when the sampled result size is greater than the max rows size.  Below are images for what you see for each of these scenarios.


Partial Results:

MaxRows.JPG

Sample Results:

DataSample.JPG

Both:

SampleMaxRows.JPG

I personally see this as a simple and great way to speed up the initial designing of reports.  Of course we would want to remove this setting before doing any final testing to make sure our system would be sized to handle the actual data being returned as well as verifying the calculations with the larger data are what you expect.

 

I hope that this blog was helpful and that you will try this functionality out when next designing a document.

 

Also, please bookmark our Webi Bulletin as it is a great page that is updated regularly with information on the Web Intelligence product.

Hello Everyone,

 

Today, when a webi report based off BEX Query is refreshed, the only way to enter the prompt is to select it from the List of values.  This has changed as of last week with the release of BI 4.1 SP 05.

 

I am delighted to share with you all that a new manual entry field has been added in the WebI prompt UI. This new functionality will allow users to enter the prompt value manually instead of having to select it from the LOV.  This functionality is in addition to the already existing option where users can select the value from LOV.

Image 1.PNG

The below screen shows the multiple values selected for the prompt (key values entered manually):

 

  Image 2.PNG

 

The keys can be entered in different ways. The list of supported input formats are as follow:

 

  1. Single Member: 1

 

When using Selection Option Variable based prompts, the prompt can be shown as multi-value prompt instead of an interval prompt by making the following entry in the configuration.  The input format would be as follow:

 

  1. Interval: 1-5 (do include the spaces)
  2. Expression with operators: >4, >=4, <4, <=4
  3. Exclusions: !5 (any key but 5)
  4. Combinations: 1-10;!5 (all the keys from 1 to 10 except 5).

 

You can also use pattern matching feature to speed up the selection of a set of keys. Supported pattern matching could be something similar to:



  • *1 : Retrieve all keys that end in 1 ( for example:  01, 11, 21)
  • *1* : Retrieve all keys that include 1 (for example: 01, 10, 11, 12, 13, 21, 100)

 

In the next blog, I will update you with the steps on how to configure settings in order to enable multi value for Selection Option Variable based prompts. 

 

Hope this helps.

 

Regards,

 

Ejaz

Hi Everyone,

In my first blog post I´ll like to share a trick to set a page break into a report.

Sometimes, in order to better format a Report, one needs to add a page break on it.

Consider a requirement that is to render a report with charts and tables but the user wants the tables and charts to be shown in different pages. Firstly the tables should be shown and in a following page, the charts.

 

Create your table and give it a name, lets say MainTable

 

MainTable.jpg

 

 

Create a blank cell, named PageBreak. After that, on Cell Properties (Format Cell->Layout), check Vertical -> Start on a new page) like the picture below.

 

blank cell.jpg

 

 

 

 

Align PageBreakl by the bottom of MainTable

 

 

 

blank cell.jpg

 

After that, align the charts by the bottom of BlankCell

 

 

blog post 1_2.JPG

 

 

 

Align all the charts based on this cell, Page Break.

 

Hope this is clear, otherwise, feel free to contact me throug rogerplank1234@yahoo.com    

 

Cheers,

Rogerio

Charlotte from the Documentation team sent word a few weeks ago she's been working on a new doc piece for Web Intelligence - a Getting Started Guide.  I think it's pretty nifty and extremely useful.

 

If you belong to an organization that uses Web Intelligence, keep a reference to the new doc handy, cause you'll use it.

 

One of the compelling reasons that keeps Web Intelligence an extremely popular reporting tool is how quickly users can create compelling reports.  Way faster than other tools (a quote heard at last year's ASUG BI User Conference: "Hey, I can use WebI and go for lunch, or use another tool and try and eat at my desk").


How to get the users started?

 

SAP provides training and courses (https://training.sap.com) for the beginner to advanced.  But at times what's needed is a quick intro that covers the major features - creating a query, table and charts - is what fits the bill.

 

 

The Getting Started Guide is just that:

 

getting_started_doc_title_page.JPG

 

To find it, go to SAP BusinessObjects Web Intelligence 4.1 – SAP Help Portal Page and look for the Getting Started guide in the End Users Guide section.

 

It walks the user through the creation of a "real" report using the eFashion sample Universe we provide with the product.  It's been targeted for BI 4.1 SP05 Product release, so it's now public, but I'll comment that the walkthrough applies even for older versions of BI 4.x.

 

What makes the new documentation that much more compelling is that Leah created a companion video for it:

 

getting_started_video_title_page.JPG

(click above image or go directly to Scenario - Creating Reports with Tables, Charts and Images: Web Intelligence 4.1 SP5 - YouTube)

 

If you have any users who want to get started on Web Intelligence, do foward them the link to the Getting Started guide - it will walk the user through the creation of their very first WebI doc.

 

While you're at SAP BusinessObjects Web Intelligence 4.1 – SAP Help Portal Page, check out the new docs for BI 4.1 SP05!  You'll notice that our Documentation team has made changes with newer SP's to make it easier for users and administrators to find the right info.

 

Previously, the docs were fragmented.  We had one Web Intelligence Rich Client User Guide, and another for using WebI with BW/BEx queries.  There were considerable overlap in content, so a decision was made to unify the documentation in one Web Intelligence User Guide (the admin information remains in the BI Platform Admin Guide).

 

We also have a new initiative going where we can foward any feedback you have concerning the BI documentation to our documentation authors.  So if you find anything amiss with our BI docs, please do forward the comment if you happen to have a Support ticket open with SAP.

In my previous blog post, I talked about my experience with converting free-hand SQL Deski reports in BI4.1 SP05. The resulting Webi reports worked flawlessly. That’s not to say I didn’t run into any issues.

 

The fact that this feature is available is great. There are, however, a few limitations.

 

We’ll start with what the feature allows you to do:

  1. Using Report Conversion Tool, you can now convert a Deski report created using FHSQL into a Webi report.
  2. The FHSQL data provider can contain items like prompts, variables, etc… There aren’t any restrictions.
  3. The resulting Webi report can be refreshed successfully.

 

Here are a few limitations and gotchas:

  1. The FHSQL feature allows you to convert a FHSQL Deski report to Webi, but you CANNOT edit the SQL in Webi.
  2. At the moment, without the extension point, you cannot create new FHSQL Webi reports.
  3. Please make sure that the database drivers used to create the FHSQL connection in XI3.x are installed (and supported) in the BI4.1.5 environment.
  4. If you're using the Report Conversion Tool on a client machine, please make sure that machine is upgraded to SP05 as well.

 

Troubleshooting Tips:

 

During my initial tests, I ran into a major snag. My Deski reports just would not convert. They would fail with an error: “Failed: Error on Migration Engine” & “Not Converted : FHSQL1 - .wid document could not be generated due to unknown reason”. Here are things I tried to resolve my issue.

 

  1. Client side tracing. I followed KB: 1586166 - How to enable tracing for BI4.x client applications to turn on client side tracing while trying to convert the reports.
  2. I tried using Conversion Audit option in RCT
  3. I thought I needed other things in the biar file, so imported things like Repository Objects, Universes, etc…
  4. I played around with the settings in RCT like Read Cell as Text
  5. I retraced my steps from creating the report in XI3.1 to converting it in BI4.1.5

 

Client Side Tracing:

 

This KB 1586166 - How to enable tracing for BI4.x client applications is very useful. I copied the BO_Trace.ini file from the dir: <\SAP BusinessObjects\SAP BusinessObjects Enterprise XI 4.0\conf>

 

I followed Option 2: Tracing via environment variable. I initially just enabled regular tracing:

 

//==========================================================

//Trace Syntax and Setting

//

//uncomment the lines below to enable tracing via ini file.

//This will override the trace setting via command line

//

//==========================================================

sap_trace_level = trace_error;

sap_log_level = log_error;

//keep = false;

//size = 100 * 1000;

 

But I found that this didn’t get me the information I wanted. So I changed the following lines:

 

sap_trace_level = trace_error;

sap_log_level = log_error;

 

to

 

sap_trace_level = trace_debug;

sap_log_level = log_info;

 

This gave me verbose trace logs. Now don’t be surprised if there are multiple files generated. I saw the following:

 

pic1.png

 

 

All these files are important. The Report Conversion Tool calls are in the TraceLog file, but it’s a java process hence the java logs, and it also invokes the Universe Designer Tool, we believe, for connectivity information. If you run into any problems with RCT, and the issue isn’t obvious, then I highly recommend verbose tracing and collecting all these logs.

 

Now at the time of writing this blog, the reason for my issue was not apparent in the logs. We saw error messages, but it didn’t scream “I did it”. We have an open dialog with our development team about possibly improving our error messages.

 

Just recently we also discovered that the RCT might also be requesting information from the APS. I figured out the reason why my reports were not converting, which I will talk about, but I will update this blog after I run a new trace and this time include the APS in the tracing process. Stay tuned!

 

Conversion Tool Audit

 

Since I was not having any luck with trace logs, and my patience was wearing thin, I decided to try a different avenue of troubleshooting. My server has Auditing enabled so I thought I’d audit the RCT activity to see if it clearly states why my reports were not converting.

 

This KB is what I used as a guide: 1846268 - How to enable Report Conversion Tool Audit in BI 4.0

 

Things to keep in mind:

  1. You need to enable auditing and have an auditing db.
  2. The KB talks about using Universe Design Tool, so make sure you have 32bit drivers for your DB installed.
  3. If you update the universe before you run the RCT, you will not see the appropriate table. Run RCT once with Auditing enabled(image below) and then you will see the table in UDT.
  4. Don’t forget to enable the Audit settings in RCT as shown below every time you run RCT. I wish it stayed on all the time, but I can see how that’s not always appropriate or necessary.

Picture2.png

 

 

Here is what the Report Conversion Tool Report looks like after converting 2 bad reports and 2 good reports.

Picture3.png

Let's take a look at the "Not Converted" report tab.

Picture4.png

Picture5.png

 

While the report gives good information, it didn't tell me exactly why my reports were failing to convert.

 

Migrating other objects

 

I tried migrating other objects like Repository Objects, Universes, Rights, etc... Because my test was really simple, I really didn't need anything but the reports. I can understand that as a customer, more likely than not, you would do a full system migration. Please note that while that's perfectly fine, if you're testing reports, using the method I mentioned in my previous blog is just fine.

 

Changing the settings in RCT


At this point I was grasping at straws, so I thought I'd change the settings in RCT. This was pointless as all I really needed was to check the check box that read: Convert documents containing free-hand SQL / stored procedures.

 

Retraced my steps

Finally I decided to retrace my steps. In doing so, I realized my mistake. I had created the FHSQL Deski report using a MS Access 2003 ODBC driver. In my BI4.1.5 environment, I didn't have that driver installed. So I created a new FHSQL Deski report (although updating the connection would have worked too) using MS Access 2007 ODBC driver, migrated the reports and converted them SUCCESSFULLY!

 

 

Knowledge Base Articles

I will try to update this Blog with any helpful KBAs I come across.

  1. 2101036 - BI 4.1 SP05- A Free-hand SQL based WebI report converted from DeskI fails to refresh with "login failed for user" error

 

It took some trial and error, but we figured it out. In the process we were able to identify areas that need improvement, we defined a few possible troubleshooting techniques, and actually celebrated the fact that once it worked, it was awesome.

 

First look at FHSQL reports in BI4.1

 

For the latest information about Web Intelligence, check out the WebI Bulletin

Have you ever used multiple selection type Input Controls in your Web Intelligence report?

 

Did you ever find it frustrating that WebI refreshes the data view before you were done selecting all of the values you wanted in that input control?

 

Starting in SP05 for BI 4.1, WebI has a new feature to add an "OK" button for "Multiple Selections" type Input Controls for Check Box and List Box.

 

It can be enabled or disabled in existing Check Box Input Controls by using the edit option in Design Mode or when first adding the Input Control.

 

The OK button is enabled automatically for the List Box Input Control and cannot be disabled.

 

How does it work?


Let's take a look at an example using the eFashion sample data source.

 

We've added an Input Control of Check Box type for Stores to allow users to narrow down the Sales Revenue they see for the selected stores only.

IC.png

 

The existing behavior is that WebI will refresh the view of the report to only show the data of the selected check box AS IT IS SELECTED.  If you want to select 4 stores, the view will update 4 times after each check is made.


With the new addition of the OK button, the view will not update until the user clicks the OK button.  This way, your user can select all 4 stores, then click "OK" to have the view refreshed.

 

This can be a huge time saver when your report contains a lot of data.

 

 

What does it look like?

 

List Box Control Example:

LBIC.png

 

 

Check Box Control Example:

CBIC.png

 


How do you enable the OK button for the Check Box Input Control?


Existing Input Control:

  1. Open the report in Edit/Design Mode
  2. Select the Input Control menu on the left hand side
    • ICMenu.png
  3. Hover over the input control to display the interactive icons
    • ICEditWrench.png
  4. Click the wrench icon to edit the Input Control
  5. Click "Yes" next to "Display selection OK button"
    • OKButtonYes.png

 

New Input Control

  1. When adding a New Input Control, only the "Multiple Selections" types will have the OK button and only the Check box type will allow you to enable or disable the display of the OK button
  2. The option to enable it is the same as above.  Under the Input Control Properties, you will check the box next to "Yes" for "Display selection OK button":
    • OKButtonYes.png

 

 

How do I Enable the OK Button for ALL Reports?

 

Follow this KBA for instructions on enabling the OK button server wide for the Check Box input control.  This will enable it for ALL of your reports for HTML:

 

KBA 2090250: How can I force the display of the OK button in a "Check box" Input Control within Web Intelligence reports?

 

Currently, for JAVA clients, the setting needs to be enabled for each client machine.  A server-side setting to enable the functionality for all client machines is planned for a future release

 

 

Let us know what you think!

 


REFERENCES:

 

For more information on what's new in BI 4.1 SP05, check out the SP05 What's New Guide found on the SAP Help Portal.

 

Mike Neville's KBA 2090250: How can I force the display of the OK button in a "Check box" Input Control within Web Intelligence reports?

Hi everyone!

 

Some of you may have seen my wiki on Web Intelligence GUI Customization that came out with BI 4.1.

 

In BI 4.1 SP05 we have added onto this functionality by allowing you to do this customization on a per folder basis instead of just globally.

 

Customizing the interface is still done at the group level in the CMC by just right clicking on the group name and select Customization.

 

CMC_interface.png

 

You will now see the following screen:

 

FolderandGroupPanel.PNG

 

The basics for setting up the User Interface Elements, Features, and Extension are still the same as they have been.  You can click the link above to view the wiki that explains the features.

 

The new functionality is being able to set this up on a per folder basis.

 

The Customization folders section contains a folder named Default Folders, which is used to define default customization.

 

You can choose folders for which you want to apply specific customization by clicking the Add Folder button.

 

AddFolder.PNG

 

 

You can avoid redefining the same customization for other folders by copying customization from one folder to another by using Duplicate Customization and Paste Customization options from the drop-down list. If you want to remove customization for a specific folder you can click on the Remove folder option from the drop-down list.

 

DropDownList.PNG

The following rules are used to define customizations to apply to a user:

  • If the user belongs to different groups, only the customization defined to the group whose ID is lower applies. The customization defined for the other groups containing the user does not apply.
  • For nested folder structure, the immediate parent folder of the document that has been added in the list of customized folders defines customizations for the document for user interface elements, features, and extensions.
  • The customization defined for Default Folders apply for the documents stored in Personal Documents and Inboxes, and for documents for which the parent folder is not customized.
  • The customization defined for user interface elements have priority over customization defined for features as features is only a shortcut to enable all user interface elements.

 

I hope this helps!

 

Please leave any comments or suggestions below.

 

Best Regards,

Jason

As many have heard, free-hand SQL is making its long awaited debut in BI4.1, in the form of SP05. As an engineer in the Webi Product Support team, I was pretty excited to have access to an early release of the product, so I decided to test it out.

 

I've been with the company since the BO6.5 days and like many of our loyal customers, I liked Deski. It was feature rich and many times, for whatever reason, worked faster than Webi. One of the features it had was the ability to create reports using free-hand SQL.

 

One of the many benefits of using free-hand SQL was the fact that a Universe wasn't needed. All a report designer needed was a connection to the reporting database and their SQL skills. Custom SQL was directly entered into a dialog box in Deski and data was returned. No muss, no fuss.

 

Because of the deprecation of Deski in BI4.0, and free-hand SQL not yet a part of the features in Webi, many of our customers refrained from upgrading. Now I know upgrading isn't fun, especially a robust product like SAP BusinessObjects Enterprise, but at least we can cross off the inability to use reports created using free-hand SQL as a reason. I had limited time with the pre-release of Support Package 05, so I was able to do a simple test. I've documented my experience and recommend extensive testing for anyone interested.

 

*Note* You can't actually refresh a migrated Deski report in BI4.1. I just want to make it clear that Deski isn't back. However, with Support Package 05, we now have the ability to convert FHSQL Deski reports into Webi reports.

 

Step 1: Create a Deski report in XI3.1 with FHSQL. Ahh the good 'ol days. Unfortunately, I realized my SQL skills are a little rusty. So I created a very simple one. I chose Free-hand SQL as my datasource.

Picture1.png

Step 2: Create a connection to the reporting DB.

Picture2.pngPicture3.png

 

Step 3: Enter the custom SQL in the dialog box and click Run. Voila! Save and export the report to the repository. I created a second report with an @variable defined. In my case a prompt for Country.

Picture4.pngPicture5.png

 

Step 4: Create a .biar file with my reports. The Source system is XI3.1 and the destination is a .biar file. Because this was a simple test, the things I brought over are just the 2 reports.

 

Picture6.png

Picture7.png

Picture8.png

Picture9.png

 

Step 5: Bring the *.biar file to the BI4.1.5 environment and use Upgrade Management Tool to import the FHSQL reports. I'm sure Promotion Management Tool would work too, but because this was a simple test with just 2 reports, UMT was used.

Picture10.png

Picture11.png

Picture12.png

Picture13.png

Picture14.png

Step 6: Once the Deski FHSQL reports are imported, use the Report Conversion Tool to convert them to Webi

Picture15.png

Picture17.pngPicture16.png

Picture18.png

Picture19.png

Step 7: Confirm that the Webi reports have been created and published.

Picture20.png

Step 8: Moment of truth! I refreshed the Webi report with the prompt to make sure it worked. I initially created the report with "US" as a prompt value (shown). I changed it to France to make sure that it without a doubt refreshed!

Picture21.png

Picture22.png

 

I was quite happy and impressed with how well it worked. Don't get me wrong, I ran into a few issues which I will talk about in a separate blog, but once I overcame those challenges, things worked beautifully. Back in the Deski days, Report Conversion wasn't a trouble-free task. Regular reports didn't convert for a number or reasons let alone FHSQL reports. SAP has brilliant customers that do amazing things with our product. Things we maybe didn't think of doing, or testing. I remind you again that even though the above test worked awesomely, they really were simple reports. So if you're considering upgrading now that Support Package 05 is available, TEST, TEST, and TEST some more. Good luck!

 

My next blog in this series: FHSQL in BI4.1.5 - Limitations, Gotchas, and Troubleshooting

 

For the latest information about Web Intelligence, check out the WebI Bulletin

 

 

In support, we get a lot of questions and feature requests.

One request that has passed my desk a few times is the ability for a webi document to use an unmanaged Excel spreadsheet as a data source.

 

You might not be familiar with the term "Unmanaged" so I'll explain a little.

 

BI 4.1 SP02 introduced a new feature that allowed you to base a Webi Document off of an Excel spreadsheet that was published/saved to the BI Launchpad environment. This is referred to as a "Managed" object within the BI Platform.  A Managed object is something that is imported into the BI repository and that is managed by the CMS.   This is great if your data is static in the Excel sheet but lacks functionality if the data is being updated frequently.

 

So, the question has come to me a few times now:  Can we have a Webi document use an Excel data source that is "Unmanaged"?  Meaning it is not published within the BI Platform and exists on a network share.

 

I didn't know if this was possible at first but after about 10 minutes of playing around, I was easily able to accomplish this using the below steps.

 

Keep in mind this is just a simple walk-through and doesn't cover all scenarios, but this should give you the basics on creating a Webi document that will refresh against an Excel data source that exists outside the BI Platform environment.

 

So, here we go:

 

  1. First, place your Excel file on a network share.  You can see below, mine is on a share called:  \\bipw08r2\dataSource\data.xlsx
    networkshare.png
  2. The data is simple in this example.  3 columns and 4 rows of data to start. 
    exceldata.png
  3. Open Webi Rich Client (WRC) and create a new doc.  Choose Excel as the data source
    chooseExcelDS.png
  4. Type in your network share name in the File Name box.  Select your xlsx sheet as I did below
    loadfromnetwork.png
  5. The Custom Data Provider - Excel dialog will allow you to choose a source path, sheet, range, etc... 
    CustomDS.png
  6. After you select your options, you will see the Query Panel.  Note the Source Path shows the network share and the Result Objects/Data Sample reflect what is in my sample xlsx.
    querypanel.png
  7. I click Run Query and see my Webi document with the data from my spreadsheet.
  8. To ensure my Webi Doc is refreshing, I am add a new row to my spreadsheet and save it to my network share location
    add-data.png
  9. I then refresh my Webi Document and see the new row appear.
    refresh-wrc.png
  10. I save my Document out to my BI Platform using the "Save to Enterprise" option under the Save icon in WRC.
  11. Then I open up a browser and login to BI Launchpad.  I navigate to the folder where I saved my document.Save to BIP.png
  12. To test that my BI Launchpad report is refreshing against my network data source, I update the Excel spreadsheet and add a row. 
    add-data-2.png
  13. Then I refresh my document in BI Launchpad to ensure the new row is there
    refresh-bilp-newdata.png
  14. I now have a report that will use an Excel sheet off of a network share to get data.   This gives me some more flexibility around my data for my report.

 

 

There are a few things that you will want to watch out for with this option.  I've documented the ones that come to mind below:

  • In most cases, the SIA (Server Intelligence Agent) will need to run as a network account that has rights to this network location.
  • You will need to ensure you have an Adaptive Processing Server that has the Excel Data Access Service running on it (and enabled)
  • Only the Applet Interface and WRC interface support modifying/creating Excel data sources for Webi Docs
  • The "refreshable" option will need to be selected for the Data Provider in order to refresh a document
  • The "editable" option has been removed in later versions of BI 4.1 because it was not needed for Excel data sources and did not do anything except cause confusion to end users.

 

That's it!  I'd love to hear from anyone using this in the real world.  Are there any gotchas?

 

Thanks!

Jb


Rakesh Pattani

Merging Charts in WebI

Posted by Rakesh Pattani Nov 16, 2014

Even though the title tells about merging the two charts, in reality this is not possible in Webi . The idea is to trick WebI by putting the chart on top of each other in such a way that they appear to be merged . The idea for this blog came from the following query posted in the forum.

http://scn.sap.com/thread/3652142

 

Requirement

To plot a Cross tab into a Bar/Line chart in such a way that Measure 2 forms a bar chart for all Zones for a particular Date and Measure 1 forms a Line chart for each Zones for a particular Date. This is the tricky part because we need 2 measures to behave in 2 different way in the same chart. To my knowledge this cannot be achieved in a single chart and hence we will plot them separately and then merge them.  Pic1.JPG

 

Preparing the Dataset - First we will create a data set for plotting our graph


Below is a Crosstab table which has Date along the Vertical Axis and Zone along the Horizontal Axis and a Measure Amount on the Body Axis

 

Pic2.JPG

 

Also will create a variable Average which will be Measure 2 and add a column at the end of the table. So, that’s how the table and our data objects looks like

 

Pic3.JPG

Plotting the Chart


Now we will try to plot this data into a Bar/Line. We will select the Combined Column Line chart. X Axis will be the Date. Value Axis will be the Average(Bar) and Amount(Line). And since we need the Line chart for each Zone, we will select the Region Color as Zone.Pic4.JPG

Right Click Chart -> Format Chart and make sure you select the below option from the Measure properties. So that Average is Bar and Amount is Line.

Pic5.JPG

And we get the below Chart. The issue with this is; the Average also get separated for each Zone which is not something that we want. We want the Region Color to be applied only on Amount and not on Average.

 

Pic6.JPG

Workaround - Following are the steps taken for the workaround.


       1. Create 2 separate charts

       2. Format the charts

       3. Place the chart on top of each other

 

Creating a Bar Chart

 

Create a bar chart for the Average as shown below. We will not select Region Color since we don’t need this to separate into the Zones.

 

Pic7.JPG

This is how the chart looks like and exactly what we want.

 

Pic8.JPG

Creating a Line Chart


Create the Line Chart. Here we will select the Region Color since we want to separate the Amount into zones

 

Pic9.JPG

This is how the chart looks like and exactly what we want

 

Pic10.JPG


Formatting the Bar Chart


First we need to format the base chart (BAR) as shown below.Right Click on the Bar Chart - > Select Chart Properties

 

Goto the Value Axis tab and select the Design option. This is to fix the Y-Axis. Make sure to select the Maximum value in such a way that it is higher than all measure values in the table


Pic11.JPG


Scroll down below and hide the text of the Value Axis by selecting the color as white. Don’t uncheck the Visible checkbox else the chart will get dislocated.

 

Pic12.JPG

Similarly goto the Title option and hide the text as well

 

Pic13.JPG

 

Repeat the same for Category Axis and hide the text of the Title and Design Option as shown below

 

Pic14.JPG

Pic15.JPG

 

Hide the legend Title

 

Pic16.JPG

 

Change the position of the Legend value

 

Pic17.JPG

 

And the Bar Chart will look like as below


Pic18.JPG


Formatting the Line Chart


The first and most important thing is the make the Y Axis of line chart same as Bar Chart. This is because when we put the chart on top of each other, we want the axis to be same. For formatting right click on the Line chart -> Select Chart Formatting

 

Pic19.JPG

 

Next we remove the legend title from the Legend -> Title

 

Pic20.JPG

Goto the Value Axis -> Title and give a custom title name

 

Pic21.JPG

The next important part, is to make Line Chart transparent so that when it is placed on top of the Bar Chart, we can see both the chart. For this reduce the RGBA Color to 46%.

 

Pic22.JPG

We won’t touch the Category Axis because we want to see the Axis details when we plot the charts. One more setting that we would do is make the background white for the line chart.

 

Pic23.JPG

 

Now our Line chart will look like as below


Pic24.JPG

Merging the Charts


Now the last part of this trick, make the relative position of the Line Chart same as that of the Bar chart

 

Pic25.JPG

 

And we get the Chart as shown below which matches with our requirementPic27.JPG

Hi,

 

You might be interested to know that several SAP BusinessObjects development guys (Product owners, Domain Architects, Product Experts) are taking part in SAP TechEd && d-code in Berlin next week. This will be a great opportunity to exchange ideas, best practices and roadmaps with our customer, partners and internal colleagues.

 

My colleague Bruno Louifi, Christian Ah-Soon and myself will be presenting a hands-on session on a fresh Web Intelligence feature called "Extension Points" (session ID: EA267 - Tuesday 11th Nov at 16.45 H3 Hall 6.2).


The idea behind Extensions Points is to "programmatically" extend Web Intelligence 4.1 capabilities in order to enable new business scenarios that are not available out of the box. Examples of extensions include connecting a WebI report to Google Maps, Predictive Analytics, creating annotations, writing back to a database...All using Javascript goodness and the fresh Web Intelligence RESTFul SDK.


Bruno, Christian and I will be there to guide you through the exercises and to assess the feasibility of your project ideas.


In summary, during this hands-on you will:

- Configure the Eclipse environment and create your WebI Extension OSGI bundle (e.g., creating hyperlinks on the left panel to interact with the report)

- Configure Deploy Test and debug your extension in order to make it available in your Webi Report viewer.

- Deploy some samples for you to get a taste of this powerful feature.


The target audience would be customer/partners/consultants with some dev skills (Java, JS) and with some basic knowledge on Web Intelligence workflows.


We had some positive feedback on this session which was first ran in Las Vegas, hope you'll agree and share the excitement, which proves that we are still continuously innovating in our good old Webi !


tweet EP.jpg

See you in Berlin!

Actions

Filter Blog

By author:
By date:
By tag: