"IN" in IDT

[former_member213259]

Hello Experts,

I have a formula in WebI: Max(Date) IN (Dimension). Is there a way to implement this at the object level in IDT Universe?

Just wondering if there is any IN or equivalent of IN function  in IDT.

Appreciate your responses.

[Former Member]

The problem is that whatever you do in the universe will only relate to a specific combination of objects in a report. You can say it's the max date in a given dimension but if someone throws in another dimension with your date object, it can get confused, messy, etc.

You might want to consider a max/over/partition by solution within a derived table (or better a materialised view for performance/maintenance purposes) as this will allow you to better segregate what you want to report on.

[Former Member]

While there is not really a direct equivalent, there may be a way depending on the context.

If, as implied, you are looking for a max value within a given dimension (no other dimensions, filters that would affect the calculation context), you could get the value using analytic functions depending on your source DB. SQL Server and Oracle would support:

MAX(Date) OVER (PARTITION BY Dimension)

Date and Dimension would need to be the database field names. Note that this dimension would not be usable in the WHERE clause and would be not be treated as a measure in webi, no projection.

The MAX value would be repeated in every row of the result set.

[Former Member]

The IN operator in WebI controls the projection of a measure within the report -- setting the context of the projection independently of the any implicit context, like context of a block -- so it doesn't really make sense to define it in the universe, even if it were possible (it isn't).

In the universe you're defining how your measures can be aggregated in your database query. This is different from projection, which happens in the report itself after the results have already been extracted, but to an end-user it pretty much looks the same much (not all) of the time.

So, if you what you want is a measure which can only be aggregated/projected (let's pretend it's the same thing for a minute) by one dimension, and you want to implement this in the universe, you should start by creating a separate fact table (or faking one with a derived table or an alias) which reflects this.  Put it in its own context, and there you go.

What you want:

Max(MyDate) In (DimensionA)

Your table has the following objects built on it:

     TABLE_A:

     MyDateMax

     DimensionA

     DimensionB

     DimensionC

Create an alias of TABLE_A, create a new measure read from this alias, "MyDateMaxByDimensionA".  You can give DimensionA an aggregate aware definition to reuse the same object with both the original table and its alias. The dimensions B and C will be ignored (and thus "aggregated" away). Don't forget the separate context, of course...