Oracle Analytics Cloud and Server

Products Banner

How to filter by starts-with for 3 RTF columns?

Received Response
228
Views
11
Comments

Summary

How to filter by starts-with for 3 RTF columns?

Content

Hi All,

I have a list of results that I need to evenly show across 3 columns to look like this:

src.PNG

I'm not exactly sure how to do this. One idea I have is to just add a filter in each of the 3 columns for Starts-with 'A to E' for Column 1, ''G to P" for Column 2, "R" for Column 3.

Template.PNG

I have the 3 columns built but I'm not sure how to filter the content in each of them.

Template-OutPut.PNG

The group row by code is:

<?for-each-group:row;./FIELDVALUE?><?sort:current-group()/FIELDVALUE;'ascending';data-type='text'?>

Any advice?

Thanks,

John

Answers

  • John Bruso1
    John Bruso1 ✭✭✭✭✭

    Ahhh, that makes a lot of sense, Venkat. Thank you!

  • John Bruso1
    John Bruso1 ✭✭✭✭✭

    Venkat, I'm having trouble with the for-each-group section.

    This is what I currently have to get a list of all results.

    <?for-each-group:row;./FIELDVALUE?><?sort:current-group()/FIELDVALUE;'ascending';data-type='text'?>

    How do I add the filter to this? (BTW, my custom column name is ColumnVal and it is numerical.)

    This doesn't work...

    <?for-each-group:row;./FIELDVALUE[ColumnVal=1]?><?sort:current-group()/FIELDVALUE;'ascending';data-type='text'?>

    I've attached the RTF template and the xml to this post above.

    Thanks,

    John

  • Venkat Thota - BIP
    Venkat Thota - BIP ✭✭✭✭✭

    for filter use  for-each-group:G_1[custom_column='value1']

  • Venkat Thota - BIP
    Venkat Thota - BIP ✭✭✭✭✭

    My suggestion is apply all logic in data source and filter in RTF template example : create a custom column in sql query , logic for this column is if column value start with A, B,C,D,E,F then name it as value1 , for G TO P name it as value2 and for R name it as value 3 , once you are created column , the column will have only 3 values (value1,value2,value3) select case when substr(facility_name,1,1) in('A','B','C') THEN 'value1'             when substr(facility_name,1,1) in('D','E','F') THEN 'value2'             when substr(facility_name,1,1) in('R') THEN 'value3' end rtf_column tablename the above logic will create single custom column catgegorized by values , as you duplicated 3 tables one by one in rtf template , in the beginning of each group use filter condition like for table1

    , table2

    , Table3

    let me know if you have any questions.

  • John Bruso1
    John Bruso1 ✭✭✭✭✭

    Woohoo!

    This works nicely! Thank you Venkat! I was pulling out my hair!

    <?for-each-group:row[COLUMNVAL=1];./FIELDVALUE?><?sort:current-group()/FIELDVALUE;'ascending';data-type='text'?>

    John

  • Venkat Thota - BIP
    Venkat Thota - BIP ✭✭✭✭✭

    sent  , please check

  • Venkat Thota - BIP
    Venkat Thota - BIP ✭✭✭✭✭

    modified Template working as expected , i have issue in uploading dcoument , please share your email , i will farward.  Thanks

  • Venkat Thota - BIP
    Venkat Thota - BIP ✭✭✭✭✭

    might be due to syntax errors please upload RTF template with sample xml data. will send you updated RTF . Thanks

  • John Bruso1
    John Bruso1 ✭✭✭✭✭

    It's attached above.

  • John Bruso1
    John Bruso1 ✭✭✭✭✭

    Venkat,

    Thank you for your help. I'm still not getting a result though... This is what is in my 'group row by FIELDVALUE' field in the first column:

    <?for-each-group:G_1[ColumnVal=1];./FIELDVALUE?><?sort:current-group()/FIELDVALUE;'ascending';data-type='text'?>

    It just produces a blank column. I also removed the sorting just in case and it is still blank.

    ??

  • Venkat Thota - BIP
    Venkat Thota - BIP ✭✭✭✭✭

    for-each-group:G_1[columnvalue=1];./FIELDVALUE first use filter followed by grouping