Drill Through to ODBC - how to format output table?

Post Reply
jcr55
Posts: 54
Joined: Tue May 08, 2012 3:58 pm
OLAP Product: TM1
Version: 9.5.2 FP2
Excel Version: Excel 2007

Drill Through to ODBC - how to format output table?

Post by jcr55 »

Using TM1 10.2.2 FP3
In our development environment, I am working on Drill Through to show GL transactions (TM1 cube only stores GL balances).
I have a Cube that contains GL monthly balances. For cells at all N-levels, the Drill option works and returns a new window that is the result set of an SQL query which are the transactions that are for the Cell's period, account, company, etc.
The output table has all the columns the same width. Some columns are too narrow, and only the first characters of the Journal Entry Description are shown. Is there any way in the TI Drill Process to set column width?
Wim Gielis
MVP
Posts: 3105
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1, Jedox
Version: PAL 2.0.9.18
Excel Version: Microsoft 365
Location: Brussels, Belgium
Contact:

Re: Drill Through to ODBC - how to format output table?

Post by Wim Gielis »

Not sure it's possible. Here is the link to another topic on how to work around shortcomings of the drill-through: http://www.tm1forum.com/viewtopic.php?f=3&t=12375
Best regards,

Wim Gielis

IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
User avatar
paulsimon
MVP
Posts: 808
Joined: Sat Sep 03, 2011 11:10 pm
OLAP Product: TM1
Version: PA 2.0.5
Excel Version: 2016
Contact:

Re: Drill Through to ODBC - how to format output table?

Post by paulsimon »

Hi

You don't say whether you want this to work in Excel or TM1 Web?

If it is Excel, then search this forum. I posted a way in the past to make VBA trigger the drill thru, and then you can use VBA to format the result.

In TM1 Web it is more difficult. However, what you can do, is, instead of just using a SELECT statement, you can write a SQL Stored Procedure and do some formatting within that to affect the column width, and in particular the headings. So long as the last statement in the SQL Server Stored Procedure is a SELECT that will return the formatted data, TM1 will still be happy.

Regards

Paul Simon
jcr55
Posts: 54
Joined: Tue May 08, 2012 3:58 pm
OLAP Product: TM1
Version: 9.5.2 FP2
Excel Version: Excel 2007

Re: Drill Through to ODBC - how to format output table?

Post by jcr55 »

Wim Gielis wrote:Not sure it's possible. Here is the link to another topic on how to work around shortcomings of the drill-through: http://www.tm1forum.com/viewtopic.php?f=3&t=12375
Yes, I have read through that topic. I do have an ORDER BY in the SQL, so the rows do come back in the correct sequence.
Just trying to see if there is a way to format the column widths of the drill through results grid.

Using TM1 Cube Viewer (via Perspectives), and also a TM1 Excel DBRW report.
We do not use TM1 Web at all.
Post Reply