Using database information to format object contents with inSight / dynaSight

Have you ever looked more closely at the possibilities of formatting numbers with inSight / dynaSight?

If you right-click an object that contains numerical information from a database, you get the pop-up displayed in Fig 1. Now if you select the “Number format…” option you are confronted with a series of tabs and options for controlling the display of the numerical information.

Fig. 1

In this tip we would like to draw your attention to an option that appears in three of the tabs of the “Number format…” window. The tabs are shown below in Figs. 2, 3 and 4. All three tabs have the option “take from database”. This is a poor translation from the original German version of the software meaning “use database settings”.

Fig. 2

Fig. 3

Fig. 4

The option actually does nothing more than what is actually described. It reads out the corresponding setting for formatting from the database and applies it to the displayed object. This is definitely not what you always want to do in your applications but it is often very useful. Furthermore, not all databases have information that can be applied as formatting in the context of the figures shown above, so those selections do not make much sense in such cases. If you are in doubt, experiment with the database you are using and you should soon be able to see if this option is supported in inSight / dynaSight or not for your database.

Trying to discuss the “take from database” option without specifying a database makes little sense, as explained above. We have thus decided to use as an example for this article, an OLAP database: Analysis Services from Microsoft SQL Server 2000. We will not go into the details of what cube we are using or anything about the relational model used to build it up, as this information is not relevant to explaining the inSight / dynaSight formatting option from the database. What we will however show, is a couple of things to look for when using the “take from database” option and thus some familiarity with the OLAP component of SQL Server 2000 is useful to understand what is done below.

The data we will be using is an OLAP measure called “Sales Dollars” from a cube derived from the sample database FoodMart 2000 in SQL Server 2000 Analysis Services. The data is displayed in Fig. 5. In Figs. 6 to 8 display the current settings for the “Number format…” options that we will be changing in the course of this article.

Fig. 5

Fig. 6

Fig. 7

Fig. 8

Fig. 9 is a view of the Analysis Services Cube Editor window displaying the defined dimensions and measures as well as the corresponding properties. The OLAP measure, Sales Dollars, is defined in the cube and selected. Note that the “Data Type” is set to “Currency” and the “Display Format” is set to “Currency”. What is important to note is that these settings are complete specific to the Analysis Services OLAP server and that, as we will show below, these settings can be read out and used by inSight / dynaSight.

Fig. 9

In Fig. 6 you can see that we have set the number of displayed decimals to 3, which is what is displayed in Fig. 5. Now we will change the setting to “take from database” as shown in Fig. 10. The result is that inSight / dynaSight reads the corresponding setting for “Data Type” in the Analysis Services OLAP server from Fig. 9 and displays only 2 decimal places (Fig. 11), as is proper for currency data.

Fig. 10

Fig. 11

Next, in Fig. 12 we change the setting for “Addition”. Basically, this is also poorly translated from the original German version of the software and means "Display Prefix / Suffix".

Fig. 12

The PC running the sample application is set to the United Kingdom as default. The OLAP Analysis Services, picks up this information and passes it on to inSight / dynaSight which then display the £ (British Pound) symbol as a suffix as seen in Fig. 13.

Fig. 13

At this point you probably get the idea and so we will leave it as an exercise to try to modify the setting shown in Fig. 3 which displays the grouping and scaling according to the database settings.

An additional point to keep in mind when using Microsoft Analysis Services is that when you change the database settings in the Cube Editor, as shown in Fig. 9 above, this will not be sufficient for inSight / dynaSight to understand the changes. You need to save and recalculate the cube and only then will inSight / dynaSight make the adjustments accordingly.

previous tip | next tip