Building reports

Currently, I'm quite busy writing reports for a customer. The tool to use is Business Objects Version 5. The reports are predefined by the user in an excel format, which simplifies things, and an even greater luxury is the fact that he has provided previous reports (in excel), to verify the results. The result is a very stimmulating project.

At this time, I find myself quite stuck at a problem.

The customer has defined groups of data which do not occur in the database. say : groups of products. group 1, group 2 and group 3. Each product has a target to attain, for a given period of the year, say q1, q2,q3 and q4. The total of all groups of products, again has a specific target. Each of those targets has to be formatted according to a set of conditions. a less or equal to t : green a larger than t and a less than t*2 : blue a larger than or equal to t * 2 : red.

The targets do not exist in a database and so I coded them in an excel sheet.

So, the database contains a unique id and name for each product, but not the groups, and the targets are set by group.

Still thinking on it since linking does not seem to work.

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 ?

Scripting Word

Sometimes you write a simple piece of code, and you just look at it… and it is beautiful ;-) Here is how to use a template word document and modify it on-the-fly from ASP.. problem is you need the Word object on the Server, which is why I didn’t use it, but in stead.. I used an even greater tool — but I didn’t write that one myself.. more about that later.

 

<%
const wdReplaceAll = 2
Set objWord = server.CreateObject("Word.Application")
Set objDoc = objWord.Documents.Open(server.mappath("/files") & "/inputfile.doc")
Set objSelection = objWord.Selection
objSelection.Find.Text = "strname"
objSelection.Find.Forward = TRUE
objSelection.Find.MatchWholeWord = TRUE
objSelection.Find.Replacement.Text = "Peter De Rop"
objSelection.Find.Execute ,,,,,,,,,,wdReplaceAll
objSelection.Find.Text = "strtitle"
objSelection.Find.Replacement.Text = "Modifying Word"
objSelection.Find.Execute ,,,,,,,,,,wdReplaceAll
objSelection.Find.Text = "strdate"
objSelection.Find.Replacement.Text = date
objSelection.Find.Execute ,,,,,,,,,,wdReplaceAll
objDoc.SaveAs server.mappath("/files") & "/outputfile.doc"
objWord.application.Quit False
%>

Context operators in Business Objects (part 4)

Foreach and Forall revisited
=Average(<Sales revenue> ForEach <Quarter>) ForAll <Year>
What does this formula do ?
To understand it, first consider the following tables :




Imagine, we want to show the number 2,023,954 in the first table  (so we can compare the Sales revenue per year to the Average Sales Revenue per quarter for that year. With the standard calculations in Business Objects, such is impossible. This is the situation where you need Context Operators. When adding the formula =Average(<Sales revenue) to the table to the left, the result would be the same number as the column with the Sales Revenue itself, since in that table Business Objects can only consider that value to calculate the average. So we say the context of the average in that case is just the “sales revenue”. In case we wish to include the <Quarter> also into that calculation, we would need to include an input context : Foreach or In. Both will do the same in this case, the only difference is the way they work. Foreach is able to add dimensions to the calculation (key word is ADD) This means that in a Foreach, the dimensions already in the table are always taken into account. The formula would then become : =Average(<Sales revenue> foreach <Quarter>) This tells the average function to include the Quarter in the calculation together with Year which is already in the table. The “In” Operator can do the same, but then the function would have to be : =Average(<Sales Revenue> in (<Year>,<Quarter>)) this needs to be done because In ignores the dimensions already in the table. The resulting table :
This is what we call an Input Context. This kind of context operator is added inside the brackets.
If we needed to show the number at the bottom  of the above table, (3,032,267) then we would need an output context, since we want to show the average for the entire Year. the formula would then be the one I stated at the beginning of this article : =Average(<Sales revenue> ForEach <Quarter>) ForAll <Year>
This second operator is used in this case as the Output Context operator The same is possible with “In Report” as the output context. The resulting table :


That should be the end of this topic.

Did this information help you ? Look.. a 'Donate' button on the right side :)

Dreamweaver, Ubuntu PHP and MySQL

First, let me remind you that I’m not an expert at Ubuntu, so the following is again, the result of a couple of buckets of sweat !

Have been fiddling around with PHP and MySQL for a while — didn’t work out at first.

then I found why I couldn’t get them to work properly…

1) MySQL is setup in such a way, you cannot connect to it from the outside.

solution : change the my.cnf file (on my system, it was located in /etc/mysql/)

find a line that says “skip-networking” (without the quotes) and put a # in front of it.
there, now you can connect to MySQL from a graphical shell like MySQL Administrator on a Windows machine ;-) of course, MySqlCC on Linux is just as good.

2) PHP is set up to ignore MySQL completely

solution : un comment two lines in php.ini (on my system it was located in /etc/php4/apache2/) watch out though … they give some sample lines, but one of them has
a spelling mistake in it… it should be extension=mysql.so and extension=gd.so for some reason, one of those lines is written msql.so.

3) rights have to be set correctly

I used these pages as a reference : sourceforge and my favorite dev.mysql.com

4) Dreamweaver functions have to be installed on the server to be able to test your connection

somewhere on your windows computer, you should be able to find a folder called _mmServerScripts. This folder needs to be copied into the folder where your PHP application will be created (I don’t remember if DWMX2004 did it automatically — but I remember copying it manually).

Some useful commands :

to restart mysql (you need to restart it when adding or changing userrights)
sudo killall mysqld  (to stop it)
sudo mysqld start (to start it)

when changing ini files and conf files you need to restart apache … without restarting linux, this is done with :
sudo /etc/init.d/apache2 restart

 

 

 

The movie copi�r

I buy a lot of movies. For years, I bought tons of VCR cassettes. Being a real collector, I watched them each over a dozen times over. And then disaster struck. My VCR would destroy a video when you watched it and during the time you were watching pressed rewind. I do that all the time — missed something, so a quick rewind to rehear what that person was saying. of course — you don’t know the video was destroyed until you watch it a second time.

I found myself in the DVD Era pretty soon after that hideous mishap.

In the mean time, my DVD collection is growing — I buy a lot of DVD’s. And again, as before, I watch them regularly. I salvaged some of my VCR’s as DIVX some time ago, but I never got round to putting them on a DVD — so I was happy to find that it is quite easy to do. I found the procedure to convert DIVX into DVD on Afterdawn. (You will need a powerful machine — and a lot of time to get the job done though)

After the mishap with my VCR collection, I have grown somewhat paranoid.. So for the moment I’m creating backup DVD’s of some of my most loved DVD’s… I didn’t know copying a DVD was so simple.

For those who want to know :

download two software packages :

DVDdecryptor : which reads and writes DVD’s (free)
DVDShrink : which allows you to throw away the things you don’t need (subtitles and audio in albanian or Arabic or any other language you don’t understand) (free)

First use DVD decryptor in “File” Mode to get all of the files from the DVD decrypted and zone removed onto the HD. Next use DVD Shrink to backup or reauthor the files on your disk … last, write the ISO file which is the result of DVD Shrink back to a DVD… using DVD decryptor in Write ISO mode…

easy as 123 

 

SQL Reporting Services

Last week, my coach told me, it would be a good idea to have a look at SQL Reporting Services. The Microsoft equivalent (as he called it) of Business Objects.

First thing I did was install a VMWare machine with SQL and the Reporting Services. Compared to installing Business Objects, this is quite straightforward. The installation procedure is very much like every installation procedure by Microsoft. The result was, that I had reporting services up and running in about 5 minutes. During this installation, the system asks you if you want to use the demo database.

Visual Studio.NET is required to create the reports — a definite difference with Business Objects.

My Conclusions. SQL Reporting Services has nothing to do with Business Objects. They do somewhat the same, but their target audience is very different. In business Objects, an end user can build a report
and use it as he or she wishes. In SQL Reporting Services, all a user can do with a report is set some filters and drill down on data. Building a report is done by a specialised service — or some
well trained people who design the RDL files. Thorough knowledge of SQL queries is a must, thorough knowledge of the database is a must — knowledge of Visual Studio.NET helps out a lot.
In business objects, the designer of a BO Universe gives objects logical names and groups objects together — in these RDL files, the designer has to choose all of the fields from the database — fields can be located anywhere.

To me it looks like SQL Reporting Services is the kind of thing where a central design team designs all of the reports.. users have to nag them to get reports changed — BO is the kind of
program where users can create their own reports and work a lot more flexibly/dangerously(they can make their own analysis errors) — but at a cost. Business Objects is not exactly a cheap tool.

anyway, learning more about SQL Reporting services as we speak.

SubQuery's in Business Objects

The title may seem complex, but the issue is simple.

Lets say you wanted to see the Sales Revenue per Store, but only for Stores located in a State that had a Sales Revenue over 7.000.000 $.

In that case, you would have to produce a query that would return all of the States over 7.000.000 $, and based on that query, obtain all of the stores you needed. The Stores by themselves don’t have such a high Sales Revenue, so you can’t get this in one go.

Here is how :

This would return us the Stores and their Sales Revenue (all stores). So next, we need to perform a Filter on the State, setting a Sub-query as a source.

The result is as follows :

The stores listed only come from states with a Sales Revenue over 7000000$ — just as required.

Great stuff isn’t it ?

Beware : Universes have a parameter that limit the maximum inlist values. Furthermore, some databases have limits on the maximum inlist values.

The most common number is 999. The parameter on the universe that sets this is : MAX_INLIST_VALUES and is set, by default to 256.

topcom wireless lan on Linux

Here is how to install a topcom wireless lan module under Ubuntu Linux (Hoary)
I’m not a Linux specialist, so this is the result of a couple of buckets of sweat.

Installation succeeded on Hoary (Ubuntu Linux) 2.6.10-5-386
First make sure you have all necessary packages.
Under Ubuntu Linux, use Synaptic, to install :
linux-wlan-ng
wireless-tools
linux-386
linux-kernel-headers (for the version of kernel you have -- mine was 2.6.10-5-386)
gcc
gcc-3.3
gcc-3.3-base
libgcc1

yeah -- it's a long list ... but you will need it to compile...

next, surf to :
http://linux-lc100020.sourceforge.net/
to get it working, I downloaded version 0.13 -- I also tried
it with version 0.14, but to no avail
once you got the tar.gz file into a folder, unpack it (tar zxvf zd1201-0.13.tar.gz)
don't compile yet, it won't work... you need more.

Next... tricky one...

download ipw2100-1.1.0.tgz from http://sourceforge.net (go through search and type ipw2100)
untar it.. (tar zxvf ipw2100-1.1.0.tgz
copy the files
ieee80211.h
and
ieee80211_crypt.h
into the headerfolder : /usr/src/linux-headers-2.6.10-5/drivers/net/wireless
(this step is required for this specific wireless module)
remember the file you untarred... zd1201-0.13.tar.gz ?
go back to it and modify zd1201.c (yeah you really have to)
replace every instance of ieee802_11 with ieee80211 -- shitty job...
run make
run make install
copy the zd1201.fw and zd1201-ap.fw files into the
/lib/hotplug/firmware folder and
the /usr/lib/hotplug/firmware folders...
run depmod -a
run insmod zd1201.ko

(added thanks to Stephen Pike)

or -- if that gives an error on symbols
use modprobe -v zd1201 instead

I then rebooted the system (yeah -- not really required)
System>Administration>Networking should now list your wireless connection.
change the properties to make it active and choose the right accesspoint
use the iwconfig, dmesg and ifconfig commands to check your configuration..
ifup wlan0 and ifdown wlan0 should allow you to start and stop the wlan module..

lots of luck !