| |
| Desktop intelligence: Designing a winning Excel report
Author:
Lluís Aspachs
Scope of this article
Microsoft®
Excel® is a tool that is always present throughout the various reporting layers in an organization. This article aims to outline some tips and tricks to help you design an effective yet simple reporting environment using Desktop Intelligence technology from BO XI R2.
Applicability
Most corporate users have the need to receive the latest information in a familiar format in which they can quickly do modifications and communicate with the rest of the company.
For these users Business Objects Desktop Intelligence provides the chance to create useful Excel reports from its powerful query & analysis engine, with the same advantages and limitations that Excel brings.
Considerations and best practices
- Information volume: One of the key pre-requisites to take into account is that the traditional maximum number of rows and columns is: 65535 x 255.
- General layout: A common best-practice is having a title box with some additional information – glossary terms and execution date. Also make sure to not overload the report tab with too many tables.
If it is necessary to include several tables, all columns and rows must have the same height and width, so that you can create virtual auxiliary cells with the same sizes to avoid undesired cell merging (see figure 1).
Figure 1 Title box and auxiliary cell with same size on top of column
Also, when using graphs and tables in the same layout, you will need to take into consideration that graphs need to be sized differently than tables using a multiplication factor (see figure 2).
Figure 2 Table is exactly 4 times smaller in design mode. The result when exporting is the desired one
- Column and Row widths: As a general rule, always keep the same width and height for the whole report. Between tables and/or graphs, the design proportions are often not respected. In this case, do not leave spaces or adjust the height or width especially for those.
- Positioning o bjects : To avoid having a first row or column to appear in blank, we recommend to adjust the title cell to the top and left margins. All other objects you can then refer to this title cell or to other objects as preferred.
- Colors: Generally speaking, for graphs and tables colors are fully respected in the exported file. A particularity is, that the color palette that the xls file inherits may differ from one Excel to another – the same thing happens with certain editors from Microsoft. Also some color differences may appear if trying to merge different xls manually. If you wish to minimize this effect, use robust colors like black, grey and white (see figure 3).
Figure 3 Recommended color look for operative Excel reports
- Graphs: In certain occasions, it is difficult to hide the undesired descriptions of metrics or dimension items. In these cases, you can use the following strategies: force the description to appear and hide it, or turn its size to zero (see figure 4). As a last resource, a post export macro process could delete them.
Figure 4 Graph with minimized x-axis description
- Format type cells: Formatting of cells are normally respected. In the special case of the Date format, it is recommended to convert it to a character before exporting, otherwise the Date appears to be an illegible code (the Excel standard for dates). You can use the following formula for example:
=FormatDate(<Close Day> ,"m/d/yyyy")
Some of your reports' cells may contain long texts. It is though useful to align left align these and use the automatic .xls behaviour of showing long texts beyond cells (see figure 5) by leaving empty cells to the right of these.
Figure 5 Visualization of texts in Desk. I. / Excel
- Hyperlinks: Using the native Hyperlink function is very helpful for having links to intra- or extranet locations. It is possible to use a list of hyperlinks directly with the help of a formula. In the case that the hyperlinks show an empty value, for this special case, adjust the width of the title so it creates a merged cell (see figure 6). Links to other tabs or cells are not possible without using ad-hoc programming within Excel.
Figure 6a: BO Desk.I. / Excel.: Same width cells
Figure 6b: BO Desk.I. / Excel:: Different width cells
- Other considerations: Some requirements are not directly feasible in the Excel export, like page layout settings or special objects, links to other documents or images. To embed special objects and images into your excel file, you may need to execute post export processes directly in Excel.
Conclusion
With Desktop Intelligence technology, Excel lovers still have the ability to directly design simple yet effective reports. If you apply these guidelines and you have a good distribution resource such as ReportMessenger, users will be able to build a powerful set of reports in Microsoft Excel for refresh and distribution throughout your organization.
Go
to top
| Take your Business Objects reports to the next level with HTML drill-down
Author:
Victor Cutilla
Although drilling down is easy to do with BusinessObjects web intelligence, there are times when this is almost a mission impossible. Do you find yourself facing one of these challenges:
1. Web intelligence is not available in your BusinessObjects environment - and you have only the full client to work with.
2. Your business users require a more complex report than they can handle themselves in BO full client.
3. Hierarchies are not present in the universe from where the data is extracted.
4. You want to publish your reports on an independent web environment outside of BusinessObjects.
If you face a situation like any of the ones above, then it might be interesting to read on.
This article explains the process of easily building drill down BO reports with BO full client and publish them either in a web environment or in a local machine.
An easy example will be used in order to better explain the process. The “Island Resorts Marketing” universe will be used for this example.
A company has spent a huge amount of money in several marketing campaigns in order to improve their performance in Germany. The Sales manager would like to get an overview of the revenue in Germany in comparison to other countries where the company is doing business. He also wants to know how this revenue is split between the different regions within the country. Finally, he would like to embed this report on his personal web page so it is easily accessible for him and his team.
Now that we know the business requirements we find after analyzing the BI environment in the company that only the BO full client is available for reporting purposes. The company also has an intranet running on Internet Information Services (IIS) and would like to publish the report in this environment.
In the following figures we see an example of how the actual report would look like. By clicking on Germany in the first figure you are being directed to the second figure and by clicking on ‘World Wide View” we would then go back to the first figure.
Figure 1
Figure 2
With clear requirements from the manager’s side, let’s go on to a “step by step” process in order to show you how to create this report.
1. Of course, the first thing we need here is to retrieve the data from of the “Island Resorts” universe. For this purpose we will take the “country of origin”, the “region” and finally the “revenue” objects.
2. Now we need to think of how we want to organize the data so that the sales manager can easily navigate from a worldwide view with a split by country (so he can compare Germany’s revenue against other countries’) to a detailed country view (so he can analyze the German revenue split between the different regions inside Germany). The best and easiest way to show this would be through an HTML report so we can drill from a worldwide view (we could have a table showing the revenue per country) to a country view by clicking on any country available in that table. This would lead us to a country view that will show us a table with the different regions inside that specific country together with their revenue. We would also have a link to go back from this country view to the worldwide view.
3. Let’s try to organize the data in the report. We will first have a tab called “worldwide” with a table showing the countries together with their revenue figure.
4. After this we will create a new tab called “country” showing a table with the regions inside the countries together with their revenue figures.
5. In this fifth step we have a view with the regions but we need to show these regions grouped by country. For that, we will create a section using the variable “Country of origin”.
We have now the two views that we want to show in our report. A worldwide view showing revenue by country and another view showing revenue by region. Now we need to link both views so that users can navigate from one to another. For this purpose, we will create a hyperlink on the Country field in the “worldwide” view that will lead the user to the data of the country on which the user has clicked.
How BO exports HTML
However, before going to the creation of the hyperlinks let’s briefly explain what’s the structure of a BO report when it is exported to html. For this, let’s have these two tabs exported to html and let’s analyze that structure. In order to export the report to html:
1. Go to “save as html” submenu in the “file” menu. On the “HTML option” form, select the following options: 1. select the “worldwide” and the “country” tabs in the “save” section;
2. clear the “Business Objects document” in this same “save” section;
3. and finally, check the “section by section” checkbox in the “generate html” section. The form should look like the following picture:
Note: the reason of having the “section by section” selected is that when exporting to html BO will create as many html pages as sections we may have in a tab (i.e. in the country tab we will have as many html pages as countries available)… and that is exactly what we need.
The folder structure will consist of one folder with the name of the first tab (worldwide) and another folder with the name of the second tab (country).
Inside the worldwide folder we will have only one html document since we do not have sections on that tab (0.htm). And inside the country folder we will have “0.htm” for Germany, “1.htm” for Japan and “2.htm” for USA (note that we do not need the country.htm nor the seclinks.htm documents).
So now, let’s keep in mind that when clicking on Germany in the first tab we will need to go from “../worldwide/0.htm” to “../country/1.htm”. Let’s now go to the creation of the hyperlinks:
1. Duplicate the country field as in the figure 7 and let’s recreate how a normal html hyperlink would look like in order to go from the first tab to the specific country page in the second tab.
Achieving drill down in BO Full Client
To drill down in BO you need to use the following formula syntax. Using our example, to drill from anywhere to Germany’s detail (remember: this was in folder “country” the file “0.htm”) the formula would be:
<a href=../country/0.htm > Germany </a>
1. So, now we know how to drill down on a static link, but we have a table with different countries and their revenue. How do we achieve a drill down on these individual countries dynamically? Read on…
The key thing here is getting the “0.htm”, “1.htm” and “2.htm” dynamically. For that we use the “RunningCount() - 1” formula, so we get “0” for Germany, “1” for Japan and “2” for USA. We then use the FormatNumber() function to pass the numeric value to a string format and being then able to concatenate it to the rest of the formula.
With that our HTML syntax in BO will now be:
="<a href=../country/"&FormatNumber (RunningCount(<Country of origin>) - 1 , "0")&".htm >"&<Country of origin>&"</a>"
2. This formula must now be pasted into a cell and subsequently that cell must be formatted “Read as HTML”. To do this, right click on the cell and click the appropriate field.
3. If we remove the country column with the variable “Country of origin” you will see that you get a “COMPUTATION” error on the newly created column for the country. To avoid this, we need to go through the following trick: duplicate again the “Country of origin” column and differentiate it from the original “Country of origin” by concatenating it to any string (i.e. “hhh”). We will get something like the following figure.
4. The original column “country of origin” should be hidden by going to “Table Format” --> “Hide”.
5. And now remove the new column “country of origin” & “hhh”.
6. We have created the table with the html link for the country and its revenue. To finalize this tab, let’s format the country column to make it “html like”.
7. If we export this tab at this moment to html, we will have something like the following figure.
8. The next step is to work on the “country” tab. We can keep the table we already have since we do not need to drill down further but we need a link that can take us back to the worldwide view. Since we only have one “worldwide” document “../worldwide/0.htm”, this link will be a fixed link and not a dynamic one. We can then add a field to the report and have there the following formula: “<a href=../worldwide/0htm > World Wide View</a>”. Finally, we format it as html and we get the following figure.
9. Finally let’s export this tab again to html.
10. If you go to “../worldwide/0.htm” and you open it with Internet Explorer you will get the table with the countries together with their revenues and you will notice that by clicking on Germany you will go to the Germany view with its regions and revenues, and also, you will find the link “World Wide View” to go back to the world wide view.
11. The last step is to publish this in the company intranet (i.e. create a new virtual directory in IIS called World Sales and have the two folders copied there) and have this report available for the Sales Manager so that he can analyze the data at different levels by just a few clicks.
Go
to top
|
Is secure data access a concern? Applying visibility rules in BusinessObjects XI Release 2
Author:Moïse Kameni
Introduction
Your organization wants to roll out a Business Objects XI reporting environment, but you need security so that your users will only access the data where they have the rights for? This article is the follow up to the white paper that was published in last year’s eZine where we focused on Business Objects 5.x/6.x. In this article we will demonstrate how this is being done with the new release of BusinessObjects XI R2.
This white paper is based on the “Island Resort Marketing” universe, a common universe that is available for all Business Objects users.
We assume that a repository is available with the “Island Resort Marketing” universe exported into the universe domain. Also, a new secured connection has been made pointing to the demo “Island Resort Marketing” database.
Scenario
There is a security requirement to make sure the country or regional managers using “Island Resort Marketing” only have access to the data that is related to their area of supervision.
Right now, anyone with access to Business Objects can report on data from all regions, countries (USA & France) and effectively view all the details about sales and reservation activities.
The approach
There are different steps to take to approach this. Follow the steps below.
CMC:
- Logical groups and end-user accounts creation
- Set adequate sub-group/group and user/group memberships
Designer:
- Universe’s objects setup
- Groups and user’s restrictions restriction creation/application
Detailed description
A. Logical reporting groups and end-user accounts creation
User and group creation tasks are performed via the Central Management Console (CMC) – the new BO XI administration tool.
Create the following groups and End-user accounts:
- Groups: France, USA and “Island Resort Marketing”
- USA and France subgroups of Island Resort Marketing
- End-user accounts: frcountrymgr (France), uscountrymgr (USA) and generalmgr (Island Resort Marketing).
Island Resort Marketing Group creation via the CMC (Central Management Console). Please make sure that the top group “Island Resort Marketing” has adequate rights on Desktop Intelligence application.
USA and France subgroups of Island Resort Marketing
“generalmgr” account creation
Set “generalmgr” as member of Island Resort Marketing
uscountrymgr account creation
Set the membership to USA group
“frcountrymgr” account creation
Set membership to France group
B. Objects settings
The key table in this universe for our exercise is “Resort_Country”. This is the table where all the countries associated to the resorts are stored.
The idea is to make sure all the objects available in this universe are linked to that table. As a result of this, additional SQL will be added automatically to any query made in BusinessObjects.
How is this done?
- Select any object in the universe and go to its properties
- Click on the “Tables” button below and additionally select the “Resort_Country” table, so that two tables are selected.
- Repeat this step for all the objects in the universe, save and Export to the repository.
Key Table: Resort_Country
Adding that key table to all objects:
More objects:
C. Access Restrictions settings
The universe settings you have just performed will now force the automatic addition of the table “Resort_Country” to any query created in BusinessObjects.
In Business Objects XI, the actual data restriction is now applied directly via the Designer module in contrary to version 5.x/6x (Supervisor module required).
How is this done?
- Create restrictions in Designer
- Apply restrictions on groups & users
Restrictions creation: French Resort Records & US Resort Records
- Log in Designer (Go to Start > Programs> Business Objects XI R2 > BusinessObjects Enterprise > Designer)
- Import the “Island Resorts Marketing” universe from the Repository
- Go to Tools > Manage security > Manage Access Restrictions
Click New, and fill in the Restriction Name field. (French Resort Records)
From the Rows tab, click Add and select the required Table: “Resort_Country”
Set the Where Clause Definition as needed. In our case Resort_Country.country = 'France'
Click OK
Click OK and Check All in order to confirm the “OK” Status
Create the “US Resort Records” restriction following the same procedure as described above.
Table: “Resort_Country” and
Where Clause Definition: Resort_Country.country = 'France'
Groups’ restrictions application:
- Click Add user or group.
- Move the USA, France and “Island Resort Marketing2 groups from the Available groups and users dialog box to the Selected groups and users dialog box.
- Click OK
Highlight the “French Resort Records” restriction and the France group. Then click >> Apply
Highlight the “US Resort Records” restriction and the USA group. Then click >> Apply
Note that there is no restriction applied on the top group called “Island Resort Marketing” (Hence the user “generalmgr” has no data restriction applied to his profile as it inherits its rights from “Island Resort Marketing”)
Save the universe locally and export to BusinessObjects XI Release 2, otherwise the system will not reflect the changes.
You can now test if the restrictions apply to the users when refreshing a document in the Desktop Intelligence application.
Results in BusinessObjects report
Users: frcountrymgr, uscountrymgr and generalmgr
- frcountrymgr logs into Desktop Intelligence and refreshes the report retrieves following results:
frcountrymgr: Another query with Resort & Service Line and service objects displays the following:
Note that the SQL generated by BusinessObjects Enterprise Desktop Intelligence includes the following condition:
AND Resort_Country.country='France'
User “uscountrymgr” logs into Desktop Intelligence and refreshes the report. The following data is displayed:
And for the second query:
Note that the SQL generated by BusinessObjects Enterprise Desktop Intelligence includes the following condition”
AND Resort_Country.country='US'
User “generalmgr”logs into Desktop Intelligence and refreshes the report: both US and French data will be retrieved
And for the second query
Note that the SQL generated by BusinessObjects Enterprise Desktop Intelligence does not include the following conditions:
AND Resort_Country.country='France'
Nor
AND Resort_Country.country='US'
Conclusion
This article has provided a demonstration of how to implement security in a Business Objects XI reporting environment, so that users will only access data they have the rights for to view.
For instance country or regional managers using “Island Resort Marketing” only have access to the data that is related to their area of supervision.
Note that in Business Objects XI R2, the Central Management Console carries out most Supervisor (version 5.x/6x) tasks, allowing the creation of logical groups and end-user accounts. Whereby Designer now allows the setup of connections overloads, row restrictions and size restrictions previously applied on Supervisor module (version 5.x/6x).
Go
to top
|
|
