Non mandatory user prompt filters in a Freehand SQL report?

Alex Karr shared this question 3 years ago
Answered

I have some complicated queries that I only how to do in a Freehand SQL report - such as a "SELECT * FROM table WHERE column IN (SELECT column FROM B WHERE blah)"


The problem I have with Freehand SQL reports are the filtering. I have several non-mandatory user prompt filters, some equal to and some in list. Is there a way to create a filter like that in a Freehand SQL report, where it doesn't have to be filled out? Everything is all from the same table, so it wouldn't require joining to any new tables if a filter was or wasn't filled out.

Comments (7)

photo
1

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

photo
1

Thanks Dave!

photo
1

you're welcome Alex!

photo
1

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)

photo
2

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

photo
1

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

photo
2

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