drill through question

[shockwave]

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

[stex2727]

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

[Michel Zijlema]

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

[shockwave]

Fantastic lads! Love this forum!