企业绩效管理网

 找回密码
 立即注册

QQ登录

只需一步,快速开始

查看: 728|回复: 6

Mimic Drill Through

[复制链接]

81

主题

411

帖子

598

积分

高级会员

Rank: 4

积分
598
QQ
发表于 2014-3-15 07:54:39 | 显示全部楼层 |阅读模式
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!
回复

使用道具 举报

86

主题

402

帖子

589

积分

高级会员

Rank: 4

积分
589
QQ
发表于 2014-3-15 09:03:03 | 显示全部楼层
How about just building a temp cube within the same TI to show the results of the query?
回复 支持 反对

使用道具 举报

79

主题

384

帖子

573

积分

高级会员

Rank: 4

积分
573
QQ
发表于 2014-3-15 09:32:55 | 显示全部楼层
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.
回复 支持 反对

使用道具 举报

77

主题

412

帖子

594

积分

高级会员

Rank: 4

积分
594
QQ
发表于 2014-3-15 09:45:41 | 显示全部楼层
MadWombat wrote:[quote]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.
[/quote]
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.
回复 支持 反对

使用道具 举报

86

主题

396

帖子

584

积分

高级会员

Rank: 4

积分
584
QQ
发表于 2014-3-15 10:00:14 | 显示全部楼层
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?
回复 支持 反对

使用道具 举报

76

主题

403

帖子

586

积分

高级会员

Rank: 4

积分
586
QQ
发表于 2014-3-15 10:51:28 | 显示全部楼层
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.
回复 支持 反对

使用道具 举报

81

主题

410

帖子

598

积分

高级会员

Rank: 4

积分
598
QQ
发表于 2014-3-15 11:49:01 | 显示全部楼层
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.
回复 支持 反对

使用道具 举报

您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

QQ|手机版|小黑屋|企业绩效管理网 ( 京ICP备14007298号   

GMT+8, 2021-6-18 20:16 , Processed in 0.176375 second(s), 13 queries , Memcache On.

Powered by Discuz! X3.1 Licensed

© 2001-2013 Comsenz Inc.

快速回复 返回顶部 返回列表