Complex Query's

A participant of one of my courses provided me with the following intriguing question. For 1 given year, you need to know which stores both sold product A and B.
When you create the query where you choose both stores and the product and create a crosstab, you will also get results where one store sold something, and the other did not.


This picture shows what happens. There are occurences where one store sold something, and the other did not. Business Objects then shows “discontinued”.






The solution I found, is situated at the query level, and includes a combination of subquery’s and union query’s.
First, I created a query where I specify my first source :


it includes the name of the stores, the number of the product and the sales revenue. All of this is done using the eFashion universe, interesting for demonstration purposes only. The real work goes on in the conditions.
Storename and year are filtered as usual.
SKU number is filtered using a subquery. in this case, listing only the SKU numbers that are the result of that subquery.



The subquery looks like this :


The result of this query is a list of all SKUs sold in the other store. So we only get products in store A that have been sold in store B.
It is now possible to select both stores, but if we do that, then we will get some products for store B that were not sold in store A.. so not a good idea.
Next, we need to create a Union query, to also show data about the second store.



Click the  icon. It will add a second query, and you repeat the previous steps, but now for the other store. This means : the first query is then for Boston, the subquery for austin. The result looks like this :

Toilets Brussels

Teaching with a clean desktop

Simple trick I learned today :

Right-mouse click on the desktop > Arrange Icons By > uncheck show desktop icons. All the icons on the desktop are now hidden… cleaner to teach programs. To get them back — do this procedure again.

cheers

Data Integrator (1)

I took a nosedive into Data Integrator a couple of weeks ago.

The first thing I discovered was : they went to a lot of trouble trying to make an interface as un-intuitive as possible. The first two encounters with the product resulted in shouting from my part -- and just not working on the side of Data Integrator.

So -- I persisted.

A couple of weeks later, the product is becoming clearer. Projects contain Jobs (check) -- Jobs contain WorkFlows (check) WorkFlows contain DataFlows (check).

My conclusion so far — Data Integrator is not the easiest of products to use – but luckily, I succeeded in putting my hands on a good “core” tutorial pdf file…

Small Bug in BO XI R2

Strangely enough, the small bug I talked about in the previous article, persists in BO XI R2..

Small BO 6.5 Bug

A program always show more bugs when released and shown to the public.

Here is a strange one.

1) create a ranking on any field. check the “top” checkbox and a textbox appears where you can enter the top-howmany you want… it says 3 by default.

2) put the cursor next to the 3 (left) and enter the number 2 … next, use the delete-key to remove the 3…

what remains after clicking ok… is a top-23…

strangely enough, BO does not really remove characters removed from that textbox by means of the delete-key.

.NET on Linux

At a certain point I was reading an article about .NET – and they stated that .NET was meant to be “cross-platform” and I thought “yeah right”.

Some two weeks ago, I stumbled across the “Mono project”. It is a .NET framework, sponsored by Novell that runs on Linux, Solaris, Mac OS X, Windows and Unix. And I thouht “yeah right”. I seem to think that too much.

So I installed it on my Ubuntu machine and stumbled from one amazement to the other. Currently, I’m learning C# on my Ubuntu machine. The book I use is entirely based on Windows, but all the examples seem to work on Mono — all except the ones that address the graphical interface — it should work, but I’m not that far in the book yet.

Anyway. This is something to keep an eye on. Pretty soon the only thing needed to have an app on Windows Mac and Linux is a recompile.

cheers

Ubuntu upgrade to Breezy

Today, I upgraded my Ubuntu system to Breezy -- as I said before, I'm not a Linux knowitall, so I heavily depend on Google and thelike -- again today. I had no clue where to start, to upgrade Hoary to Breezy -- but I really wanted to -- I saw the installation of Breezy in a Virtual Machine, and it looked even more sophisticated and complete than the previous version.
Luckily, I found an easy to follow guide to upgrading my Ubuntu Linux machine :

http://www.ubuntuforums.org/archive/index.php/t-83123.html

I must say -- I didn't follow every step in that article -- the fellow who explains it seems rather squeemish (is that how you write it ?) anyway -- I did do the part where he mounts an iso file -- then I used Synaptic Package manager to upgrade my system -- and in fact -- you do have to remove all packages that have to do with Open Office to get the new version to install. After a first install, I switched on the original repositories and performed another upgrade - and the installation of Open Office 2 of course. I must say -- it looks very 'complete' -- all my powerpoints work perfectly in it -- but it does not translate Word documents very well. Anyway -- back to playing with my Linux machine.

Expand/Collapse (Business Objects)

This is the place where I store the things I tried once, found interesting and choose to forget again… to avoid loosing the info altogether, I write it down. Here is a perfect example of such a fact.

At a certain point, I was wondering what “expand” and “collapse” in the analysis menu were used for. I figured it out — but at the time I didn’t have this Blog yet — so I forgot all about it pretty soon after.

Let’s assume you have a table with Year and Average Revenue (which is a variable, calculated from “Sales Revenue” and “Quantity Sold”). Next, you enter drill-mode. You can now drill on the year and look at the quarter, to month etc… but what about the individual figures which were used to calculate the average revenue… well — there you go. Still in Drill Mode, you go to the Analysis menu and click “expand”.

Automatically, both measures are shown in colums next to the average Revenue. When you choose collapse, they disappear again.

Nifty little trick.

Couldn’t find this in the Core Reporting, the Intermediate or the Advanced Reporting courseware. But it is in the product-documentation — unlike some other stuff.

Filters in Business Objects

It keeps amazing me just how many types of filters there are in Business Objects.

• First : there are the Query filters. The most limiting filters, which have an effect on the datacube - everyone knows (I hope) about these filters, and uses them. They depend a lot on the database and have a big influence on the speed of the report (depending on indexing in the database).
• Second : there is the report-filter. Few documented, easy to use, a filter set on a report only applies on the report and on all tables in it, but not on the other reports.
• Third : the table-filters. Commonly used to filter inside a table, each table in a report can have its own filters. (first and second can be found in Format>Filters

These are the ones you see in a basic BO class (2 days) and continue with in the advanced class (1 day) But there is a fourth (little known) way to filter, and I found it most useful in some situations.

That fourth filter is what I would call : the object-filter -- it means that you can filter out a certain object based on a given condition. How :

In the properties of a table, a chart, a section and some other stuff, you can activate the "Hide Block" checkbox. If you do so, and confirm with ok, then the block is gone, and you can only get it back if you switch BO into structure view (View > Structure) -- so don't do that. But you might have asked yourself.. what is that box underneath ? well, here you can enter a condition - but you have to enter it manually in this format : =function(<object>) operator value. e.g. =Sum(<sales>) < 8000000

The result of the comparison has to be a boolean value (true/false). e.g. on a chart you could count how many years you have available and only show the chart if you had more than 1 year. e.g. =count(<year>) > 1. The chart will remain hidden as long as you don't have more than 1 year. When you refresh and an extra year was added to the result of the query, then the chart would show up. Pretty nice trick.

No File menu in Business Objects

This is a bug I came across some time ago.

Here is the solution :

Follow these steps:
       1. Go to Microsoft Visual studio in Business objects
       2. Press Ctrl + g
       3. copy paste this command
"Application.CmdBars.ActiveMenuBar.Visible = True" in newly opened window
i.e. immediate window
       4. Press 'Enter' button
found this on ittoolbox website

Customised List of Values

Business Objects has many features. In previous articles I already explained some of them — but this next one is quite interesting and well hidden.

Imagine the following situation. You create a query on a database using Business Object. You are an account manager for a certain number of customers, and you only draw information from the database for those customers. But – the list of customers is long. Each time, you have to select YOUR customers from that list. A lot of work. You can create your own list of values, containing only your own customers, so you don’t have to select them from the endless list. Here is how :

Let’s say you had this kind of List of Values(LOV) :

It is a long list, and you only use specific products. You could then create your own list of values, to display only the products YOU work with. First of all, you would need to put those names or numbers in a textfile.

The first line of this file is the title. (If you don’t put anything here, then BO will call the list F1. Not very nice.) Once the file is ready, (containing the customers/products/customercodes), you can then link it to the universe you use by opening Business Objects. Go to the “Tools” menu and choose “Universes”. From the list of universes, select your own Universe.

.
And click the button “Lists of Values”

Of course, you need to select :“Personal Data”, which automaticaly puts you in the “file selection” dialog :

here, you browse to the right place on your computer, choose the right delimiter (I used an Enter..so I chose character) and don’t forget to select “First row contains column names”. Confirm with OK. Done.

From now on, when you ask for a list of values on categories, you get :

 

Easy — no ?