TM1 Forum

Hi all,

I was fiddling around with TI/SQL interaction, resulting in a TI process which loads a .txt file into an SQL table with the use of ODBCOutput. However, a question came up as i was writing this process:

Is it possible to fire a SELECT query at an SQL server, and then to return the output on screen? I am thinking of the same output you get with a DRILL (the screen with the header 'relational drill through'). I used to think this was triggered by the RETURNSQLTABLEHANDLE statement in the epilog of a drill process, but running this at itself does not generate any on-screen output.

Does anybody know a way to accomplish this? Other suggestions are welcome, too. I have googled my *ss off but couldn't find a satisfying solution.

Regarding setup: we're running TM1 9.5.2 together with SQL Server 2008 Express edition.

Thanks in advance!

How about just building a temp cube within the same TI to show the results of the query?

declanr wrote:How about just building a temp cube within the same TI to show the results of the query?

declanr,

Thanks for the suggestion and that would be an option if i could bring a view on screen, so if there is a way to open a cube-view from within a process, that would be a solution as well.

What i was hoping to avoid is the additional action to actually 'open' a view or a text file, instead seeing the output directly.

Reasons being (obviously) avoiding the extra step in the first place, but also 'not forgetting' to view this query. The output would require a follow-up, before being able to continue.

MadWombat wrote:

declanr wrote:How about just building a temp cube within the same TI to show the results of the query?

declanr,

Thanks for the suggestion and that would be an option if i could bring a view on screen, so if there is a way to open a cube-view from within a process, that would be a solution as well.

What i was hoping to avoid is the additional action to actually 'open' a view or a text file, instead seeing the output directly.

Reasons being (obviously) avoiding the extra step in the first place, but also 'not forgetting' to view this query. The output would require a follow-up, before being able to continue.

Well, you could do it all "in one screen" without needing to open another view or report depending on how you define "mimic drill-through".
If the "drill-through" is triggered from an action button then the TI process could first load the text file or SQL query output to the temp cube then navigate to a workbook containing a view of the temp cube. Provided the temp cube is provisioned with some parameters for user ID then I think this could work pretty nice.

lotsaram wrote: Well, you could do it all "in one screen" without needing to open another view or report depending on how you define "mimic drill-through".
If the "drill-through" is triggered from an action button then the TI process could first load the text file or SQL query output to the temp cube then navigate to a workbook containing a view of the temp cube. Provided the temp cube is provisioned with some parameters for user ID then I think this could work pretty nice.

lotsaram,

That is a possibility i would consider, but this would mean the process has to be triggered from a workbook with an action-button. However, I am used to run processes from within the Server Explorer so i was hoping for a way to achieve this within (1 or more) TI processes itself.

What i mean with 'to mimic drill through' is that basically i would like to get the result of a 'right-click on data-point within cube-view'-> drill, only then triggered from a TI process. In the process i would build the SELECT statement, throw it at SQL and view the outcome. I thought i could copy the }DRILL_cubexxx but running this doesn't give the desired results.

I would even settle for writing the output to a text-file and auto-open this (eg with ExecuteCommand), but can't get this to work so far. Besides, this option does not seem to be the most efficient solution to me.

Any other suggestions?

MadWombat wrote:I am used to run processes from within the Server Explorer so i was hoping for a way to achieve this within (1 or more) TI processes itself.

What i mean with 'to mimic drill through' is that basically i would like to get the result of a 'right-click on data-point within cube-view'-> drill, only then triggered from a TI process. In the process i would build the SELECT statement, throw it at SQL and view the outcome. I thought i could copy the }DRILL_cubexxx but running this doesn't give the desired results.

I would even settle for writing the output to a text-file and auto-open this (eg with ExecuteCommand), but can't get this to work so far. Besides, this option does not seem to be the most efficient solution to me.

Any other suggestions?

There is no function to call a view or otherwise open a window and give it focus with TI. Remember TI is designed primarily as a background processing script without user interface. If you call from an AB then you are in the UI and so can solve the navigation and activation issue (at least if the target is a workbook or websheet).

With regards to your proposed approach of a "real" right-click drill through I can't see any reason why this shouldn't work provided that everything is set up correctly with the correct paramatized SQL SELECT statement. (Note often it is just easier to set the Query as a single string variable rather than messing around with individual params). If you need to do ODBC inserts then this might be best in a separate process called from the prolog of the drill process. But again in principle I don't think there's any reason why what you want to do shouldn't work.

As for your option 3 of using executecommand, this won't work, or at least it will only work on the server and then only if the windows account matched the account used for the TM1 service.

lotsaram wrote:There is no function to call a view or otherwise open a window and give it focus with TI. Remember TI is designed primarily as a background processing script without user interface.

I was afraid this would be the case, though with my limited experience i was hoping there would be some workaround i was not aware of yet. Unless there are any other options i will either go for your 'action button' approach or simply take the extra effort and take a look at the query directly in SQL.

Thanks @lotsaram and declanr sofar. Possible other suggestions are still welcome.