ODBC Drill - Formating

Post Reply
User avatar
Eric
MVP
Posts: 373
Joined: Wed May 14, 2008 1:21 pm
OLAP Product: TM1
Version: 9.4
Excel Version: 2003
Location: Chicago, IL USA

ODBC Drill - Formating

Post by Eric » Wed Mar 11, 2009 3:58 pm

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
Regards,
Eric
Blog: http://tm1-tipz.blogspot.com
Articles: http://www.google.com/reader/shared/use ... /label/TM1


Production: 32 bit 9.0 SP2, Windows 2000 Advanced Server. Web: 32 bit 9.0 SP2, Windows 2000 Server. Excel 2003

User avatar
Mike Cowie
Site Admin
Posts: 455
Joined: Sun May 11, 2008 7:07 pm
OLAP Product: TM1, MSAS
Version: Anything thru 11.x
Excel Version: 2003 - 2016
Location: Alabama, USA
Contact:

Re: ODBC Drill - Formating

Post by Mike Cowie » Wed Mar 11, 2009 4:26 pm

Eric wrote: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

User avatar
Eric
MVP
Posts: 373
Joined: Wed May 14, 2008 1:21 pm
OLAP Product: TM1
Version: 9.4
Excel Version: 2003
Location: Chicago, IL USA

Re: ODBC Drill - Formating

Post by Eric » Wed Mar 11, 2009 7:46 pm

I am drilling into Access for the short term, eventually oracle; however, that might be a year or two away.
Regards,
Eric
Blog: http://tm1-tipz.blogspot.com
Articles: http://www.google.com/reader/shared/use ... /label/TM1


Production: 32 bit 9.0 SP2, Windows 2000 Advanced Server. Web: 32 bit 9.0 SP2, Windows 2000 Server. Excel 2003

ScottW
Regular Participant
Posts: 152
Joined: Fri May 23, 2008 12:08 am
OLAP Product: TM1 CX
Version: 9.5 9.4.1 9.1.4 9.0 8.4
Excel Version: 2003 2007
Location: Melbourne, Australia
Contact:

Re: ODBC Drill - Formating

Post by ScottW » Wed Mar 11, 2009 10:10 pm

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.
Cheers,
Scott W
Cubewise
www.cubewise.com

User avatar
Eric
MVP
Posts: 373
Joined: Wed May 14, 2008 1:21 pm
OLAP Product: TM1
Version: 9.4
Excel Version: 2003
Location: Chicago, IL USA

Re: ODBC Drill - Formating

Post by Eric » Wed Mar 11, 2009 11:20 pm

Drill is in TM1 server explorer & excel
Regards,
Eric
Blog: http://tm1-tipz.blogspot.com
Articles: http://www.google.com/reader/shared/use ... /label/TM1


Production: 32 bit 9.0 SP2, Windows 2000 Advanced Server. Web: 32 bit 9.0 SP2, Windows 2000 Server. Excel 2003

David Usherwood
Site Admin
Posts: 1412
Joined: Wed May 28, 2008 9:09 am

Re: ODBC Drill - Formating

Post by David Usherwood » Fri Apr 17, 2009 8:42 am

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?

User avatar
paulsimon
MVP
Posts: 755
Joined: Sat Sep 03, 2011 11:10 pm
OLAP Product: TM1
Version: PA 2.0.5
Excel Version: 2016
Contact:

Re: ODBC Drill - Formating

Post by paulsimon » Mon Apr 20, 2009 10:40 pm

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

Post Reply