Metric behaviour

[138006]

Hi
I have a MSTR report where I have 5 metrics. in the last 2 metrics I have a Applysimple function such that
Applysimple("case when #0='A' #1 else #2 end" , ?Prompt1,fact1,fact2)

Here Prompt1 is value prompt.

All facts are from same table and MSTR generates 1 SQL
based on the value of the prompt the SQL becomes

case when 'A'='A' then.......

The problem here is when report is run user needs to enter the value for this and client wants it to be from a drop down list.

Now,
I have created a table whose values are 'A' and 'B'. I created an attribute on it--Att1 and an attribute element list prompt Prompt2.

The metric defs I changed as:
Applysimple("case when #0='A' #1 else #2 end" , Att1@ID,fact1,fact2)

Now in the report I pulled that prompt and the metrics.

The case becomes
case when Att1='A' then....... Since the value of the prompt is A its expected that this condition will be true.

Problem is now MSTR creates 5 SQLs, 1 for the first 3 ,2nd and 3rd for the 2 new metrics. Then 4th for union of the attributes of the SQLs involving these 2 metrics. ANd then final pass where this union SQL is outer joined with the previous 3 passes.

I can check from the first 3 passes that the data matches. However the last 2 passes mess up the thing and finally display datya with all metric values as 0.0

How are all the values becoming this when they are correctly fetched in the previous passes?
And why on earth the 1 SQL change to 5 ??
Please help.
thanks

 

[Z3]

Yes - you got them this time - Log an issue with the support. I've seen this many times. You will have to forget about putting att1@ID in the applysimple and just use a column name, and use something dummy filter like attribute1@ID<>null in order to drag the dim tables in, if you need to.

 

[138006]

Hi Z3,
Can you be little more specific?
What will be my metric definition then?
And what will be the dummy filter?

Please elaborate a little on your suggestion.

 

[Z3]

Sure. Instead of using att1@ID as an argument, simply put something like "max(table_alias.column_name)" in there directly - hard coding.

If table you needed is not in the join, e.g. you need the lookup table for att1 there in the join, but because your metric is not awary of this requirement, it's not putting the tables in. You can add a dummy filter on att1, for example, att1@desc not null, so MS would include the table in the join.

 

[138006]

for this again u need to know the alias for the table. This alias may change from report to report.
This again will be an issue

 

[138006]

Now the original one was:

Sum(ApplySimple("case when #0='Local Currency' then #1 else #1 * #2 end", [Currency Description]@ID, [Suggested Retail Price], [Rate To Usd])) {~} <[Filter]>

As advises, when I replace [Currency Description]@ID by the table name.column name like this---

Sum(ApplySimple( "case when #0='Local Currency' then #1 else #1 * #2 end",New_Currency.currency_description, [Suggested Retail Price], [Rate To Usd])) {~} <[Filter]>

MSTR doesn't validate it saying that there is no match for New_Currency.currency_description .

Am I doing it correctly?
Also my question is, I have to put the alias name for table New_Currency. the alias name will change for different reports. How will I standardise it then?

Thanks

 

[Z3]

No. This is how you hard code:

Sum(ApplySimple( "case when New_Currency.currency_description ='Local Currency' then #0 else #0 * #1 end", [Suggested Retail Price], [Rate To Usd])) {~} <[Filter]>)

MS Engine does not care what you throw into the double quotes.

 

[138006]

Fine,
But Since New_Currency will be in the From Clause , MSTR will put an alias against it. Now if you write New_Currency.Currency_Description in the Pass through, it won't work since it doesn't have that table name in its Syntax Tree. Instead it has the Alias name.
So I have to hardcode the alias name. But then as I mentioned you need to make sure that the alias name is same for all reports using this --this is tough.

 

[Z3]

If you make sure the column name is unique (by using a view?), then you can forget about the table_name or alias. Just use the column name would be fine for Oracle or SQL Server.