[Closed] More macro prompt problems

[DaveC]

Hi, right with one macro prompt problem solved another pops up.

I have a tabular SQL as a sub query from the main report. I need the date associated with the item in the main query to feed through as a where variable to the tabular SQL,sub query.

any ideas ?

e.g.
SubQuery
Select ex_rate From bob where date = #sq(prompt('Main_query_date','date'))

Main Query
Tabular model with amount in local currency and date of trade

Thanks

p.s. The tabular SQl is much more complex than that, just using a simple example.

[wink]

Hi,

Not sure if I understand you correctly..... but having the sub query as an tabular model of the tabular model of your query model: set allow cross products in the query properties and define youre relation between the 2 tabular models as a filter in the tabular model..... So:

Cube model - set query properties to allow cross products
   Tabular model - add filter to set relationship to second tabular model.
      Tabular model sub query

Regards, wink

[DaveC]

Wink,

yes thats pretty much the set up. The problem I am having is the lowest level sub query is actually a tabular SQL. That Tabular SQL needs to have a variable passed in from the parent tabular model.

e.g

Tabular model - All transactions with fund currency item
   SQL Tabular Model Sub Query - Get exchange rate to USD based on the parent tabular 
                                                     models currency.

Regards
Dave

[sir_jeroen]

Little reminder: close the topic if it's been solved/closed (see board rules)