Non mandatory user prompt filters in a Freehand SQL report?

Guest ●

Hi Alex,

no that is not currently possible, however, it sounds like a good enhancement request to raise as an Idea in the Yellowfin Community.

All I can think of as a way around this, is that you could set your Freehand SQL report to be a Co-Display report, and then create a 2nd Freehand SQL report as the Co-Display report, but of course making sure that when you paste in the SQL you remove whichever WHERE clause you want to be optional, and then label the 2 reports so that the user is aware that the 2nd one has no filter.

regards,

David

Reply URL

1

Alex Karr ●

Thanks Dave!

Reply URL

1

Guest ●

you're welcome Alex!

Reply URL

1

Larry Beasley ●

I have an issue with filtering in freehand SQL and I thought that my logic would work but I'm guessing that if the parameter is blank then it will not execute the code. I'm handling the NULL/blank filter or at least I thought. Is the value actually '--Omit--'? Perhaps a quick solution would be to allow the query execution if the filter is declared?

declare @filter2 as varchar(50) = {?};
select * 
from my_table
where my_table.data = ISNULL(NULLIF(@filter2, ''), my_table.data)

I have an issue with filtering in freehand SQL and I thought that my logic would work but I'm guessing that if the parameter is blank then it will not execute the code. I'm handling the NULL/blank filter or at least I thought. Is the value actually '--Omit--'? Perhaps a quick solution would be to allow the query execution if the filter is declared?

declare @filter2 as varchar(50) = {?};
select * 
from my_table
where my_table.data = ISNULL(NULLIF(@filter2, ''), my_table.data)

Reply URL

2

Guest ●

Hi Larry,

this is a very good Idea to have in our Community, so I have raised it as such for you in your name:

https://community.yellowfinbi.com/agent/object/6738

In the future please feel free to raise any ideas you have as Yellowfin Community Ideas, you are certainly allowed to do so, and all client input is welcomed.

regards,

David

Reply URL

1

Larry Beasley ●

Actually, I just resolved my issue. I'll post it here in case others search and find this helpful in the future. In my freehand SQL I did something like this:

declare @var1 as varchar(50) = {?}; 
select *  from my_table 
where my_table.data = ISNULL(NULLIF(@var1, 'ANY VALUE'), my_table.data)

Then in my filter query I created a union:

select 'ANY VALUE' union select  colB from tblA

Then set the default to 'ANY VALUE' not the '- - Omit - -' that is defaulted. You can make 'ANY VALUE' anything you wish including '- - Omit - -'. In that case there will be two, just make sure to not choose the one yellowfibi creates which will line up first.

Lastly, this only works for text and any other data type would have to be cast. You can use dynamic/default dates

Actually, I just resolved my issue. I'll post it here in case others search and find this helpful in the future. In my freehand SQL I did something like this:

declare @var1 as varchar(50) = {?}; 
select *  from my_table 
where my_table.data = ISNULL(NULLIF(@var1, 'ANY VALUE'), my_table.data)

Then in my filter query I created a union:

select 'ANY VALUE' union select  colB from tblA

Then set the default to 'ANY VALUE' not the '- - Omit - -' that is defaulted. You can make 'ANY VALUE' anything you wish including '- - Omit - -'. In that case there will be two, just make sure to not choose the one yellowfibi creates which will line up first.

Lastly, this only works for text and any other data type would have to be cast. You can use dynamic/default dates

Reply URL

2

Guest ●

wow, that's very clever indeed! Thanks very much Larry for coming up with that and sharing it with the Yellowfin Community!

And the other idea (allowing Freehand SQL report parameters to have an optional/mandatory setting like in Drag and Drop reports) is still a good one so let's leave that up.

regards,

David

Reply URL

Non mandatory user prompt filters in a Freehand SQL report?

Replies (7)