Using a Dimension/ Measure in a Where clause of Another Dimension/ Measure in IDT

[Former Member]

Hi All

I am building a Universe using Information Design Tool. In the Business Layer, I need to use a created Business Layer Dimension (not Tables) in a Where clause of another dimension as follow:

Dimension: "Current Week No"

     SELECT

          budgets.dbo.Calenderweeks.WeekNo

     WHERE

          budgets.dbo.Calenderweeks.EndDate = convert(SMALLDATETIME, fn CURDATE()})  

Dimension: "Last Week No"

     SELECT

          budgets.dbo.Calenderweeks.WeekNo

     WHERE

          budget.dbo.Calenderweeks  .WeekNo = @Select(Dimension\Current Week No) - 1

The first dimension is working well. However, the "Last Week No" shows NOTHING (I do not get any value)

Anyone can explain why I am not get any value from the second dimension, and how I can use a Dimension/ Measure in a Where clause of another Dimension/ Measure ?

Thanks

[Former Member]

Short answer:

Beware of object WHERE clauses. They are rarely (I'd almost say never) the solution to the problem you're trying to solve.

Long answer:

It doesn't work because you are inadvertently filtering the same value twice in two incompatible ways in the same SQL statement.  Both objects SELECT the same column in the database.  The first object uses a WHERE clause to set it to the current week.  The second object attempts to use a WHERE clause to set it to the previous week, but does so by selecting the first object, so you end up with both objects in your data provider.

Remember that BO is going to place all the objects in your data provider in the same SQL (except of course where your context definition has ensured otherwise.), so you have two WHERE clauses in your generated SQL which attempt to find a line equal both the current week and the previous week... and returns, naturally, no results.

Rewrite it with a derived table (as explained in the response above) and you'll get what you want.

[Former Member]

Hi,

Try below code

Derived Table 1 : "Current_Week"

SELECT  budgets.dbo.Calenderweeks.WeekNo as "Curr_Week"

FROM budget.dbo.Calenderweeks

WHERE budgets.dbo.Calenderweeks.EndDate = convert(SMALLDATETIME, fn CURDATE()})  

Derived Table 2 : "Last_Week"

SELECT  budgets.dbo.Calenderweeks.WeekNo

FROM budget.dbo.Calenderweeks

WHERE budget.dbo.Calenderweeks.WeekNo in

                               (

                                   Select Curr_Week-1 from @Derived(Current_Week)

                               )