Skip to main content
A
AndreaF
Expert
December 12, 2023
Solved

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

  • December 12, 2023
  • 2 replies
  • 0 views

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.

AndreaF_0-1702382687003.png

AndreaF_1-1702382959069.png

AndreaF_2-1702383089594.png

Thank you

Best answer by JackLacava

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.

2 replies

P
phollander
Newcomer
December 12, 2023

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

A
AndreaFAuthor
Expert
December 12, 2023

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

J
OneStream Employee
JackLacavaOneStream EmployeeAnswer
OneStream Employee
December 12, 2023

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.

    Terms & ConditionsAccessibility statement