User Prompt Filter at View Level

Amulya Sharma shared this question ago

Answered

I have a Yellowfin View which has just the one virtual table VT The code for VT is:

SELECT 
ID1 
,ID2 
,Name1
,Date1
,DATEDIFF(day, LAG(Date1,1) OVER (PARTITION BY ID1,ID2), Date1)) AS DayDiff 
FROM T1

This basically finds the number of days between subsequent Date1 values for ID1, ID2 combinations. My report based off this view would be to find the Average of this DayDifference. Date1 needs to be a user prompt filter so that averages are calculated based on the date range entered. The auto-generated report code is:

SELECT  
 ,VT.ID1
 ,VT.ID2
 ,AVG(VT.DayDifference) As AvgDayDifference
FROM
(
SELECT
  ID1
 ,ID2
 ,Name1
 ,DATEDIFF(day, LAG(Date1,1) OVER (PARTITION BY ID1,ID2), Date1)) AS DayDiff
FROM T1
) VT
WHERE VT.Date1 BETWEEN ' ' AND ' '
GROUP BY
    VT.ID1
   ,VT.ID2

Since the Date1 Filter is not inside the subquery but outside it, the Average is calculated for all Date1's and not for the Date's entered in the UPF

How can I do this using the Drag&Drop Builder? I don't believe there's a UPF for Views which can be linked to Report UPF.

The same question

Replies (2)

Amulya Sharma ●

I suppose using Freehand SQL seems like the only viable option

Reply URL

Guest ●

Hi Amulya,

yes I agree with you, I've been thinking this requirement through and that is the only solution I can think of.

Don't forget the special syntax for user prompt filters in Freehand SQL: WHERE VT.Date1 BETWEEN {?} AND {?}

And also be aware that this is only available for Freehand SQL Reports, not Freehand SQL Views.

regards,

David

Reply URL