Hyperlink on Oracle Answers or Interactive Dashboard values

This is both an example on how to add custom hyperlinks to Oracle BI dashboards' values and on the importance that context information from the Web has for BI systems.

So let's get to it: create a new Answers request and include the columns you desire. Go to the Criteria tab and, for the column on which you want to create the hyperlink, click on the "Column Properties" button. Click the "Data Format", enable "Override Default Data Format" and on "Treat Text As" choose "Custom Text Format".

On the text box below, you'll have to enter the expression to create the link - the syntax isn't quite well documented, although this is where you usually insert ActionLinks to interact with Siebel. To create a standard hyperlink, the syntax is:

@[html]"<a href=\"http://codingassistance.blogspot.com \">"@"</a>"

Trying it out, it's easy to determine that the "@" (quotation marks are required) stands for the row value, so this hyperlink will be something like <a href="website.com">RowValue</a>.

If you want to kick it up a notch, you can pass the row value itself as an argument for the URL; for example, the expression

@[html]"<a href=\"http://maps.google.com?q="@"\" \" target="_blank">"@"</a>"

passes the row value to (the q parameter of) maps.google.com and opens a new window pointing to the map of the row value. On a dashboard, the row values are hyperlinks to, for example, "http://maps.google.com/?q=SANTA%20CRUZ%20DAS%20FLORES" where "SANTA%20CRUZ%20DAS%20FLORES" is one of the row values. In this particular case, the row value is a geographical location, hence the choice of Google Maps.

We can embelish it a bit by adding a title to the anchor, like this:

@[html]"<a href=\"http://maps.google.com?q="@"\" \" target="_blank" title=\"Click to view this location on a map\">"@"</a>"

(don't forget to escape your quotation marks!)

So, bringing it all together, we'll have a dashboard like this:



After clicking, a new window opens with a map for the location:

Describe a table in SQL Server 2005

Here's a quick snippet of SQL that allows you to describe a table in a SQL Server 2005 database, similar to the DESC or DESCRIBE command in Oracle and MySQL:

SELECT column_name, data_type,column_default, is_nullable, character_maximum_lenght, numeric_precision, datetime_precision
FROM INFORMATION_SCHEMA.COLUMNS
WHERE table_name='MyTableName'

Detailed info on INFORMATION_SCHEMA: http://msdn.microsoft.com/en-us/library/ms186778.aspx

Error 26 connecting to SQL Server 2005

While connecting to a remote SQL Server 2005 instance, you get an error like this:

SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified

If you are sure that there are no firewalls blocking connections between the two machines, and that the server is configured to accept remote TCP connections, then the likely solution is to start the SQL Server Browser service on the remote machine.


More information:
SQL Server Browser service: http://msdn.microsoft.com/en-us/library/ms181087.aspx
Configure remote connections on SQL Server 2005: http://support.microsoft.com/kb/914277

Oracle BI default password

If you are having trouble (rejected password) logging in to Oracle Answers or Oracle BI Publisher, be aware that both have a default password that differs from the password you were asked upon installation of the Oracle BI suite. The default account credentials for these services are:
Username: Administrator
Password: Administrator

(remember that the password is case sensitive)

How to create folders programatically in Business Objects XI repository

This is an example of how to create folders in the Business Objects XI InfoStore using VB6 and the COM API. This code creates a folder called "MyNewFolder" beneath "TestFolder", which is created at the folder root.



Dim sessionMgr, entSession, infoStore, pluginMgr, folderPlugin

'create a session and connect to the InfoStore
Set sessionMgr = CreateObject("CrystalEnterprise.SessionMgr")
Set entSession = sessionMgr.Logon("Administrator", "myPassword", "myBoServer", "secEnterprise")
Set infoStore = entSession.Service("", "InfoStore")
Set pluginMgr = infoStore.PluginManager
Set folderPlugin = pluginMgr.PluginInfo("Folder")

Dim infoObjectCollection, newFolder

Set infoObjectCollection = infoStore.NewInfoObjectCollection()
Set newFolder = infoObjectCollection.Add(folderPlugin)

newFolder.Title = "MyNewFolder"
newFolder.Description = "This is the folder we're creating in the example"

Dim infoObjectCollectionAux 'used to help determine the parent folder id
Dim queryStr As String
Dim queryStr1 As String
Dim queryStr2 As String

'build a query to get the parent folder id
'you can test this using the Query Builder in the Admin Launchpad
queryStr1 = "SELECT * FROM CI_INFOOBJECTS WHERE SI_NAME='"
queryStr2 = "' AND SI_KIND='Folder' AND SI_PARENTID=0" 'the root folder has id=0
queryStr = queryStr1 & "TestFolder" & queryStr2

'execute the query and get the parent folder id
'if "TestFolder" doesn't exist in the root, the infoObjectCollectionAux will be empty
Set infoObjectCollectionAux = infoStore.Query(queryStr)
Dim parentFolderId As Long
'only get the first item, for example simplicity sake
parentFolderId = infoObjectCollectionAux.Item(1).Id

'set the new folder's parent id and commit the changes
newFolder.ParentID = parentFolderId
infoStore.Commit (infoObjectCollection)

WIS 00504 error on Business Objects XI

If you get a WIS 00504 error message when trying to perform an action that requires access to a Universe (e.g. new WebI or DeskI report), this probably means that you don't have enough security privileges to acess the Universe's data. You need to have "View on Demand" rights over the Universe and "View on Demand" rights over the corresponding Universe Connection.

SSIS Script Component throws "Object reference not set to an instance of an object"

In Integration Services 2005, if you get an Object reference not set to an instance of an object exception when trying to manipulate rows from a Script Component, even after you have guaranteed that the column is set as an input for the script, try checking if the column you're accessing is not null, using the automatically available "_IsNull" property. For example, if the input column is called myInputCol, you can try something like this:

If Not Row.myInputCol_IsNull Then
'code that access the column here
End If