Use of XFBR in the SQL query of a Bound List parameter

[AndreaF]

Hi,

I have a Bound List parameter with Command type SQL.

1. I have first tried to use an XFBR rule within the string of the Like condition. This works fine.

2. I have then tried to use the XFBR to return the full condition "cvi.Name Like..." (see below), but it looks like the XBFR is passed as a String to the SQL statement in this case, without processing the XFBR and returning its output (see error in the last picture).

How do I force OneStream to process the XFBR before passing it to the SQL query? What is the right syntax?

Note: things are commented out in the code below, but I have of course uncommented the lines as needed during the testing.

Thank you

[phollander]

Hi Andrea,

You can put the XFBR in a literal value parameter and then include that parameter as |!parameter!| in the query. Alternatively, you can move the SQL query and XFBR logic to a Dataset BR.

Hope this helps.

Regards,
Paul

[AndreaF]

Hi @phollander ,

I have tried putting the XFBR in a literal value parameter, and then include that parameter as |!parameter!|, but I get exactly the same behaviour as before, with:

>and cvi.Name like '%|!parameter!|%' -> working fine
>and |!parameter!| -> giving the 'XFBR' not a recognised function error

[JackLacava · Accepted solution]

I know I am being "that guy", but I don't think stretching XFBR hacks is the right strategy here... Once you start messing with structural query conditions, spreading your logic between the adapter and XFBRs is harder to maintain, and you hit these unknown walls determined by internal OS behaviour.

IMHO, at that point it is cleaner to jump in the world of Dashboard DataSets. You get full control of your query, in code, and all the related logic is in one place. There are a couple of blog posts that describe them in decent detail: one about basic structure, and two about using SQL in it. The initial investment in learning the technique will likely pay off multiple times going forward.