TM1 Forum

Hi Guys,

Testing connectivitly to a drill through source from an access database. I am trying to drill through to the following. However the TM1 view I have has alias's turned on for the "coo_auext" dimension to show
desriptions. I thought it may be possible to try:

[Profit ctr1] = '?SUBST(?coo_ausext?, 1, 10)?'

to replace line 5 in the following drill TI query:

SELECT *
from [CJ74 Transactions]
WHERE
[RefDocNo1] = '?capex_reference_doc_num?'
AND [Profit ctr1] = '?coo_ausext?'

The only work around I have is to populate an access field with both the "coo_auext" cost centre account with the name tacked on the end ie "0000008210" to "0000008210 - my descripton"
so the drill will work. Can someone kindly provide the proper syntax to make this work, my knowledge is a little lacking in this area.

Cheers

Shock

Are you adjusting the drill process in the Prolog command? The drill will evaluate this section first then perform the query.

in the datasource query window
SELECT *
from [CJ74 Transactions]
WHERE
[RefDocNo1] = ?capex_reference_doc_num?
AND [Profit ctr1] = ?coo_ausext?

In the prolog tab (place the following)

coo_ausext = SUBST(coo_ausext, 1, 10);
or
coo_ausext = DimensionElementPrincipalName( DimName, ElName );

I thing I did notice is that the drill thru tends give errors (parameter syntax error or parameter not found) when saving but the drill thru works fine when you're using it.

Steve

While Steve's solution is fine, I would also like to mention that (as you're querying MS Access here) you should be able to use the Left function in your query:

SELECT *
from [CJ74 Transactions]
WHERE
[RefDocNo1] = ?capex_reference_doc_num?
AND [Profit ctr1] = Left('?coo_ausext?', 10)

Michel

Fantastic lads! Love this forum!