Skip to Content
0
Aug 01, 2016 at 05:56 AM

Subquery on 2 fields?

119 Views

Hi WebI Gurus,

Is it possible in WebI, based on a universe, not using FHSQL, nor Derived tables, to get the following SQL syntax in a WebI Query:

select UserAccountId,sum(t.PaymentAmount)

from Fact_Transactions t

inner join

(

select

AUserAccountId,

max (ActivityDateTime) maxliqDate

FROM Fact_Activities

where ActivityType = 3

group by AUserAccountId]) ppp

on ppp.AUserAccountId=t.UserAccountId

and t.TransactionTime>=ppp.maxliqDate

group by t.UserAccountId


This should get the sum of payments issued to an account from the last activity date of each user.


Assuming the universe does not have an object for each calculation, for example - max (ActivityDateTime), since these dates are determined at the report level, can this be achieved with just the WebI query panel?


Thanks in advance for any comments