TM1 Forum

I am trying to drill through an ODBC where the key data is NUMERIC - it is a Data in my measure.

For example: in my measure I have an element called codecombination, and it is numeric value 1246
I want my SQL for the odbc to say :
select * from abc
WHERE code_combination_id = mesure:codecombination

any help?
Thanks A MILLION

You can use parameters in the SQL e.g

select * from abc
WHERE code_combination_id = '?codecombination?'

rkaif wrote:You can use parameters in the SQL e.g

select * from abc
WHERE code_combination_id = '?codecombination?'

As I understand mesure:codecombination in the example is the value against the codecombination element in the measures dimension, so not an element in itself. Based on this the setup will be slightly more complicated than mentioned by Rizwan.

In the drill-through process parameters have been created for every dimension in the cube. Assuming (based on the example query) that you're not selecting on any dimension element entries, you could use the 'mesure' (if that's the correct name) parameter and assign the value of the codecombination value to this parameter on the Prolog part of your drill-through process. Please note that the parameter is setup to hold a string value, so you need to do something like mesure = Trim(Str(CellGetN(cube, dim, dim, ..., 'codecombination'), 10, 0))) to convert the numeric code value to a string value (exact conversion code depeding on f.i. the length of the code).
In your drill-through query you can use this parameter by passing it between question marks, like this:

select * from abc
WHERE code_combination_id = ?measure?

Depending on the database you're using it could be that you need to convert the parameter value back to numeric in your query. I haven't tested this, but I expect this setup to work like this.

Michel

Thank you Michel and Rizwan-
IN fact, Michel is right- It is not a dimension but a cell- value of a dimension and yes it was numeric.
I changed it to ALpha- but I still cannot get there.

I dont seem to know exactly what to put in the prolog- From what you suggested
mesure = Trim(Str(CellGetN(cube, dim, dim, ..., 'codecombination'), 10, 0)))

my problem is the dim ,dim part of the cellget -
It does not want to work with the real dim name, nor the '?dim?' part.
I will keep trying though.
thanks

mnasra,
just to clarify,
are you trying to drillthrough based on the value in a cube?

For example you want to return all orders that have a value of 1246, regardless of what product they are or which country they were sold in?

Given that in TM1 the values are quite often a sum of underlying values, I'm not sure that the problem makes sense?

P

(Although it's more than likely I've not understood what you are trying to do )

Yes, it is exactly what I am trying to do. IN fact, I am trying to read all the transactions records of a specific account-
But I do not have the account number on the transaction record, I have a numeric_ID (which is NOt a dimension in my cube, because it means nothing to my users).
So I put this id in a cell in my cube.
I am trying to read all the records with that specific number.

Thanks
Micheline

If you are interested, I followed exactly what Michel had suggested- and yes it worked.

THe drill creates parameters for you- so I just had to use them as is.
I added in the prolog a CELLGETS command, and it worked.
Thanks
Micheline