ODBC Drill - Formating

[Eric]

I have a Drill setup to retrieve transactional detail relating to a cell in TM1. It works great;however, I am not a fan of how the data is displayed. Can this information be formatted before it is displayed?

Example
Dates display as 2009-01-01 00:00:00. I would like to format it to 01/01/2009
Numbers display as 2110.100000. I would like to format it to 2,110

TIA

[Mike Cowie]

I have a Drill setup to retrieve transactional detail relating to a cell in TM1. It works great;however, I am not a fan of how the data is displayed. Can this information be formatted before it is displayed?

Example
Dates display as 2009-01-01 00:00:00. I would like to format it to 01/01/2009
Numbers display as 2110.100000. I would like to format it to 2,110

TIA

Hi Eric,

What database are you drilling into via ODBC? I think the only way you can get the formatting you like is through the SQL query and the functions that can help you do this may vary by database. For example, you may be able to use DATENAME or CONVERT to help with the date formats (there are probably others but I'm going from memory) in SQL Server. So, instead of using the raw field in your query you could build your drill-through query to use some of these functions wrapped around the raw fields to get the format you like.

Regards,
Mike

[Eric]

I am drilling into Access for the short term, eventually oracle; however, that might be a year or two away.

[ScottW]

Also where is the drill being performed? If it is on the web then the formatting has to come from the source. If you are drilling in Excel then there are other VBA driven options. If you have some code to take over the New Workbook event you can check to see if it is the result of a drill and then apply some formatting. Someone out there will have some sample code for sure.

[Eric]

Drill is in TM1 server explorer & excel

[David Usherwood]

Finally got around to trying this. Found this code at http://www.cpearson.com/excel/AppEvent.aspx

Private WithEvents App As Application

Private Sub Workbook_Open()
Set App = Application
End Sub

'******************************************************

Private Sub App_NewWorkbook(ByVal Wb As Workbook)
MsgBox "New Workbook: " & Wb.Name
End Sub

However....
If you add a new workbook from the menu, the event fires. If you drill, it doesn't. Presumably the Drill code interferes with the event.
Anybody have any ideas?

[paulsimon]

Hi

I came across the same problem. There is inconsistency in TM1 here. If you drill from an In-Spreadsheet Browser View, it adds another sheet to the current workbook and you can trap that event. However, as the ISB is on the way out, we all need to use DBRWs and, if you drill from a DBRW, then it creates another workbook, and it does not fire the new workbook event.

My solution came from my user requirements. I was replacing an older design that used a double click to do the drill thru, and the users were used to this and did not like the idea of having to right click and then select Drill from a menu list of 15 or so items. So I trapped the double click event on the cell, and converted this to a call to the menu item that would be invoked by right clicking and selecting Drill. The following code sample does the trick.

Code: Select all

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

    If bCellIsInsideRange(Target, Range("DrillThruPoss")) Then
        If Not (Application.CommandBars("cell").Controls("Drill").Enabled) Then
            MsgBox ("Sorry, you cannot drill down on this cell")
        Else
            Call StartOfLongUpdate
            Application.CommandBars("cell").Controls("Drill").Execute
            If ActiveSheet.Range("A1").Value = "MyDrillThru" Then
                Call DrillThruFormatSheet
            End If
            Call FinishedLongUpdate
        End If
    End If
End Sub

The check on the Range of Cells "DrillThruPoss" is not strictly necessary but is more efficient than examining the cell to see if it has an enabled Drill Thru menu straight away.

The next step accesses the CommandBars (Microsoftese for Menus), for the Cell, ie the Context Sensitive Menu you get when you Right Click. If the Drill option on the menu is not enabled, it puts up a message box and exits.

If drilling is enabled it executes the drill option on the menu. The SQL that retrieves the transactional data has a dummy first column, eg

Select 'MyDrillThru' as MyDrillThru, CostCentre, Account, etc

If the Drill was successful then a new workbook will have been opened, and this will change the ActiveSheet. The code checks the ActiveSheet to see if the first cell has MyDrillThru, and if so, then it knows that the Drill Thru was successful. It then calls a routine DrillThruFormatSheet, which does all the formatting on the ActiveSheet, which it knows will contain Drill Thru data. You can generally just record the formatting you want as a macro. I also added a bit that converted a HTML link retrieved by the SQL to a HyperText link so that the user could jump right in to the source system screen.

Using this approach gives a lot more control over what happens when you drill. The resulting report format was judged to be reasonably good.

(The StartOfLongUpdate and FinishedLongUpdate just change the cursor to an hour glass.)

Hope this helps

Regards


Paul Simon