Dave's Adventures in Business Intelligence

One of the reasons I have been slowing down on blog posts is my virtual machine / blog development environment was still running XI 3.0. Over the past week I have managed to get my VM upgraded to XI 3.1 SP2 and that gives me quite a few new goodies to play with. I am looking forward to seeing how I can use and abuse input controls among other things.

I did encounter one slight issue. My VM host operating system is Windows Server 2003. The XI 3.1 install went fine. The SP2 install never did anything. During the install process it was complaining about a server issue and said something like, “… you may encounter isssues and if you do please read Microsoft KB article blah blah blah…” After I finally decided to pay attention and look up that KB article (and apply the fix available) everything went fine. It had to do with the .msi file (installation file) being too large for my server operating system to validate.

I took a BIAR file from my XI 3.0 virtual machine and restored it on the 3.1 system. My reports and universes all moved over just fine.

I have to say that virtual machines are AWESOME. 🙂 I now have VM images for 6.5, XI R2, XI 3, and XI 3.1 SP2. I can launch any of these as I need them. Perhaps I need to confirm that a technique that I am using in XI 3.1 is backwards compatible… I can do that. Maybe I want to test the impact of an upgrade, I can start in 6.5 and roll it forward through various versions. All it takes is disk space, and disk space is cheap today. When I was a consultant it was a great way for me to create separate environments for each client. Now I just use it to maintain different environments without having to manage multiple installations on one hardware. It’s also a great way to set up a “demo system” for use in presentations.

Oh, and blog posts. 😎

We are using the OpenDocument() function to “drill” from one document to another. In order to make report maintenance easier I have created some objects in the universe that contain the proper syntax for the URL required to access this function, as well as one that contains the report path. This way even if we change our folder names or structure I can change the universe and do not have to update every report on the project. This has worked very well for us.

Until my current project.

On this project the primary distribution channel was PDF sent via email. Our users said that the links were not working. And of course every time I tested by logging in to Infoview the links worked just fine. After further investigation by another team member, it seems that our Report Path (in the format [Folder],[Sub Folder] was being truncated at the comma. As a result, the OpenDocument() function was looking for the reports in [Folder] and ignoring the full path. That was a bit of a problem. Continue reading “Fixing Report Path For Adobe PDF Viewers”

Jason Cao, one of the Business Objects Community Evangelists at the SAP Community Network (SCN) has an interesting challenge for Xcelsius developers. He has posted some very simple data and asked for folks to jazz it up with Xcelsius.

Xcelsius Challenge for Community Poll Results

If you’re interested, you have until April 30, 2010 to enter your submission.

I’m thinking of creating a pie chart. 😛

This has to be the parameter with the worst. Name. Ever. But let me start at the beginning.

Some databases require you to use actual column names in an ORDER BY clause. Like this:

select first_name, last_name, phone
from employee
order by last_name, first_name

Other databases let you take a shorter approach and sort by the position of the column in the select clause, Like this:

select first_name, last_name, phone
from employee
order by 2, 1

To be honest, I don’t like the shortcut. I would rather see explicit column names in my order by because that way I know exactly what is being sorted without having to refer back to the select clause. Another advantage is that if the objects in my select ever change, my order by is not affected.

There is a parameter found in the .PRM file for each database named SORT_BY_NO. When you see that name, what do you think it is? Every time I see it I assume that it is used to determine whether the SQL will contain numbers in the ORDER BY clause like order by 2, 1 instead of order by last_name, first_name. But that’s not what it does at all. Instead of doing what I described above, this parameter is used to determine if a query can be sorted by a column that does not appear in the select clause. That makes sense, doesn’t it? 🙄 It should be called SORT_BY_IN_SELECT or something. But it’s not, and here’s how it works. Continue reading “SORT_BY_NO=NO? Very Confusing…”