Isomorphic,
1) I just started to play around with the expression filter on the list grid.
We have complex queries tied to the listgrid.
One such sample is this:
In the above query if you take column BOOKINGS, its a field which is SUMMED using GRoupBY. Show filter expression cannot be used in this case. There would be a mismatch in the output data.
The only option I see is having another where clause outside and then manually applying conditions based on what user enters in filter editor. Please correct me if my understanding is wrong.
2) Is it possible to control expression at column level? Say, I don't want to allow expression for one particular column.
3) Whatever I put in criteria I get it set initially in the listGrid filter like:
filter above ORIG in the above query has this in the filter editor textbox: "==BOM"
How to avoid this.
4) Our use case: User fills a form and retries a report which creates a listgrid creating appropriate criteria based on user form entries. User can use filter to dive deeper into the data. We want him to dive deeper only into the initial criteria based on the form which means filter shouldn't increase the scope of filtering from the initial criteria.
How to achieve this.
Thanks.
1) I just started to play around with the expression filter on the list grid.
We have complex queries tied to the listgrid.
One such sample is this:
Code:
SELECT *
FROM
(SELECT MB.ORIGIN ORIG,
A1.CITY_NAME ORIGCITY,
A1.COUNTRY_NAME ORIGCOUNTRY,
A1.WORLD_AREA_NAME ORIGAREA,
MB.DESTINATION DEST,
A2.CITY_NAME DESTCITY,
A2.COUNTRY_NAME DESTCOUNTRY,
A2.WORLD_AREA_NAME DESTAREA ,
MB.DOMAIRL MKTAIRLINE,
CD.CARRIER_NAME MARKETINGAIRLINENAME ,
ROUND(SUM(MB.UPAX)*100/SUM(SUM(MB.UPAX)) OVER ( PARTITION BY MB.ORIGIN, A1.CITY_NAME, A1.COUNTRY_NAME, A1.WORLD_AREA_NAME , MB.DESTINATION, A2.CITY_NAME, A2.COUNTRY_NAME, A2.WORLD_AREA_NAME ,MTH.YE_ACTUAL_YEAR , MTH.MO_MONTH_OF_YEAR ), 2) AIRLINESHARE ,
MTH.YE_ACTUAL_YEAR YEAR,
MTH.MO_MONTH_OF_YEAR MONTH ,
SUM(MB.UPAX) BOOKINGS,
ROUND(SUM(REVENUE)/SUM(UPAX),2) FARE,
ROUND(SUM(REVENUE),0) REVENUE
FROM FCT_MIDT_OD_SUMMARY_MTHY MB ,
AIRPORT_DIM A1,
AIRPORT_DIM A2 ,
M_DIM_MONTH MTH ,
CARRIER_DIM CD
WHERE MB.ORIGIN = A1.AIRPORT_IATA_CODE
AND MB.DESTINATION = A2.AIRPORT_IATA_CODE
AND MB.DOMAIRL = CD.CARRIER_CODE
AND MB.MONTH_ID = MTH.MO_ID
AND MTH.MO_ID BETWEEN '2430' AND '2441'
AND ('1'='1'
AND '1' ='1'
AND '1' ='1'
AND '1' ='1'
AND '1' ='1'
AND '1' ='1'
AND '1' ='1'
AND '1' ='1'
AND '1' ='1'
AND '1' ='1'
AND (LOWER(MB.ORIGIN) LIKE LOWER('bom') ESCAPE'\'
AND MB.ORIGIN IS NOT NULL)
AND (LOWER(MB.DESTINATION) LIKE LOWER('del') ESCAPE'\'
AND MB.DESTINATION IS NOT NULL))
GROUP BY MB.ORIGIN,
A1.CITY_NAME,
A1.COUNTRY_NAME,
A1.WORLD_AREA_NAME,
MB.DESTINATION,
A2.CITY_NAME,
A2.COUNTRY_NAME,
A2.WORLD_AREA_NAME ,
MB.DOMAIRL,
CD.CARRIER_NAME ,
Mth.Ye_Actual_Year ,
Mth.Mo_Month_Of_Year
)
The only option I see is having another where clause outside and then manually applying conditions based on what user enters in filter editor. Please correct me if my understanding is wrong.
2) Is it possible to control expression at column level? Say, I don't want to allow expression for one particular column.
3) Whatever I put in criteria I get it set initially in the listGrid filter like:
filter above ORIG in the above query has this in the filter editor textbox: "==BOM"
How to avoid this.
4) Our use case: User fills a form and retries a report which creates a listgrid creating appropriate criteria based on user form entries. User can use filter to dive deeper into the data. We want him to dive deeper only into the initial criteria based on the form which means filter shouldn't increase the scope of filtering from the initial criteria.
How to achieve this.
Thanks.
Comment