Time in Reporting Services

Putting time in a table is easy... format as hh:MM -- but ! This time I had to put time in a correct format (hours:minutes) and the given data was in minutes. Maybe there is an easy way, but I haven't found it yet - this is my solution :

function convertTime(intMinutes as int32)
'prepare variables
dim arrValues, numMinutes, strNumHours, strNumMinutes
dim numHours, numValue, strTime,strIntMinutes
strNumHours = ""
strNumMinutes=""

'get the time
if intMinutes > 0 then
strIntMinutes = cstr(intMinutes/60)
if instr(strIntMinutes,",") > 0 then
arrValues = split(strIntMinutes ,",")
strNumHours = cstr(arrValues(0))
strNumMinutes = cstr(cint(cint(left(arrValues(1),2))/100*60))
else strNumHours = cstr(strIntMinutes)
strNumMinutes = "00"
end if
else
strNumHours = "00"
strNumMinutes = "00"
end if
if len(strNumHours) = 1 then
strNumHours = "0" & strNumHours
end if
if len(strNumMinutes) = 1 then
strNumMinutes = strNumMinutes & "0"
end if
strTime = strNumHours & ":" & strNumMinutes
convertTime = strTime
end function

put this in the code (properties of the report) and call it with
code.convertTime(Field!whateveritmaybe)

cheers

The kind of stuff people want in RS

What if the date is stored as date/time and you want to offer a prompt that allows a user to select a date. But!! The date has to be equal to the date in the database.

select * from Orders where cast(CONVERT(varchar(8), OrderDate, 112) AS datetime) = '2004-08-25'

this is how.

Reporting services has problems showing the data in the data-tab though.. it only works in the preview.

Dates in Reporting Services

A colleague of mine asked for a parameter on a report. The report had to run from the first of the previous month, until the last day of the previous month. I solved this with this function :

function fnGetLastDayOfMonth(datum as date)
fnGetLastDayOfMonth = Day(DateSerial(Year(datum), Month(datum) + 1, 0))
end function

I then called this function in this way :

startdate

=cdate(iif(month(now())=1,year(now())-1,year(now())) & "/" & iif(month(now())=1,12,month(now())-1) & "/01")

enddate

=cdate(iif(month(now()) = 1,year(now())-1,year(now())) & "/" & iif(month(now()) > 1,month(now())-1,12) & "/" & code.GetLastDayOfMonth(cdate(iif(month(now()) = 1,year(now())-1,year(now())) & "/" & iif(month(now()) > 1,month(now())-1,1) & "/" & "1")))

can’t believe I wrote that function – this will do the trick just fine.

=dateadd(“d”, –1, dateadd(“m”, +1, cdate(year(today()) & “/” & month(today()) & “/1”)))

from the parameter. It would then feed the first day of the previous month to that function and there you have it.. the last day. Cool.

Linking universes

A customer asked me : how can I link two dataproviders if the common field has an un-equal number of digits.
In one universe, the customernumber is 6 digits, in the other 8 digits - the two digits are two leading zeroes, so the zeroes can be removed without problem.

The formula I used was :
=if(Length(<customernumber>8) then SubStr(<customernumber>,3,Length<customernumber>)) Else <customernumber>

inserted into the field where the customer number was, the leading zeroes would be removed and BO would automatically agregate the data.

there is an other solution, which would involve creating a user defined object on the universe, but this would render the documents unusable to colleagues on different machines.

SSRS MultiValue and Nulls

When you create a multivalue parameter and you also want to include the null values when doing a filter (a select all), you got two options 1) create a union query to include null. Or (like me) -- you can modify the parameter with code. In the report properties, on the code tab, add the code below (it is probably not the most efficient code, but it works):

function addnulltoparameter(ByVal param as object) as object
dim strNewParam() as string
dim countery as int32
dim x as object
for each x in param
countery = countery + 1
next
redim strNewparam(countery + 1)
dim counterx as int32
counterx = 0
for each x in param
strNewParam(counterx) = x
counterx = counterx + 1
next
strNewParam(counterx) = ""
return strNewParam
end function

next, in the properties of you dataset, click the tab filters, create a filter on a field, choose in for the operator and in the value type :
code.addnulltoparameter(Parameters!name_of_the_parameter.Value) -- make sure you don't leave (0) at the end or you will only send the first element of the array.

The result is that a null value is concatenated into the parameter. If you want, you can also create a Boolean parameter, that allows you the choice to include nulls.

then the code for the filter would be
=iif(parameters!includenull=True,code.addnulltoparamater(Parameters!someparameter.Value),Parameters!someparameter.Value)

Complex tables do not export

At the moment, I'm creating some complex tables in Reporting services (2005).

In fact, the only two tables you can have are vertical tables and matrix tables. It is possible to create a horizontal table, but you have to rework a matrix to get to it.

The table I'm currently building in Reporting services involves nesting a vertical table inside a matrix.

As it seems, the table works fine, it exports well to pdf -- but excel won't work. It seems to be impossible to export complex nested tables to Excel.

Date input selectors in Reporting Services

When you create a parameter in Reporting Services which has Date/Time as a format, it uses the a date format which is based on the settings of internet explorer. Go to tools / internet options to set the language. If you set the language to English US, then the format will be : mm/dd/yyyy, if you set it to dutch/belgium it will default to dd/mm/yyyy

Printing in Reporting Services

In the Report Properties dialog if I set Page width to 8.27in and Page height to 11.69in then the Page Setup dialog of ReportViewer defaults to A4.

otherwise, it defaults to letter. This has to be in inches.

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