Drag and Drop filter function in Freehand SQL Reports

Bharath Kumar shared this idea 43 days ago
Not Planned

Hi Team,

We are creating a report using advanced authoring and we are adding sub-query to it. The sub query has filters and can be added to the report output. But we also want to have filters added from the master report, is it possible.

Here the master query is built using Advanced authoring report and then we added sub query to it. When I add field from master query to the filter window, it says filter not allowed.

Comments (12)

photo
1

Hi Bharath,

Thanks for reaching out. You cannot drag-and-drop fields into a filter list in Freehand SQL Reports irrespective of any sub-queries subsequently added to your report. If you reference our Freehand SQL Report Wiki entry, you'll see the following:

/56f1c4b49b514a0ab08aeaa994a861b4

The article then explains how to add filtering in your SQL just below this portion (last header on the page).

Please take a look at this documentation and let me know if you have any questions.

Regards,

Mike

photo
1

Hi Mike,

Thanks for that, I know this is working as designed. But is it possible to raise an idea to allow filters in free hand sql reports.

Regards,

Bharath

photo
1

Hi Bharath,

Allowing this functionality is at odds with what Freehand SQL Reports are intended to do. What happens with a Freehand SQL Report is that the SQL is used to generate the report, so once you have your report you can play with formatting, but nothing that changes the underlying SQL. It's not just filters, you also can't create a freehand SQL report then remove columns from the report by dragging either.

What you're looking for here may be possible using a Freehand SQL View instead, although I'm not entirely sure how it ties with sub-queries - an especially tricky question to answer since I don't really know what you're trying to accomplish here, or even why Freehand SQL would be required in the first place. I can't really tell based on the supplied screenshot.

Hopefully this helps though. Please take a look at the Freehand SQL View option and play around with that functionality, and let me know if you have any further questions.

Regards,

Mike

photo
1

Hi Bharath,

I just wanted to check in and see if you had any further questions on this, or are we okay to close this case out for now.

Thanks,

Mike

photo
1

Hi Bharath,

I'm going to go ahead and mark this one as Answered since I haven't heard back from you, but if you have further questions or concerns on this, if you respond, it will re-open the case and put it back in my queue and I'll be happy to help.

Regards,

Mike

photo
1

Hi Mike,

We would like to raise an enhancement request to have filter window in the UI for free hand sql reports as well. This is because we are using some complex queries to build the report and the same is not possible using virtual tables.

It is hard for users to edit the sql and add where clause. I also see this is partially possible from below link, we want this to work fully:

https://community.yellowfinbi.com/topic/sql-report-no-filters

Can you please raise an idea for this request please?

Regards,

Bharath

photo
1

Hi Bharath,


To clarify, I wanted to check what kind of complex queries you were having difficulty building in virtual tables? Fundamentally, virtual tables and Freehand SQL reports should work in the same way. Another suggestion, in the Advanced Authored report, have you tried creating filters in the SQL step by using "select * from client where country in ({?})" syntax for example, creating the sub queries in the SQL step instead of the "Data" step?


In the meantime, I've gone ahead and logged a Drag and Drop Filter function in Freehand SQL reports as an Idea for an Enhancement Request. Before Enhancements are actioned, they are reviewed by our Product Team based on a set of criteria. I will make sure to update this post with any future updates.


Thanks,

Simon

photo
1

Hi Simon,

Basically, All the users want to perform their own selection, whenever they want. Users do not have permissions to edit sql and add filters.

We also have queries which include both append and union queries together. Some tables have names like "BMC.CORE.CONFIG:BMC_CIToConfigBaseRelationship" which do not work in Virtual table. There is a defect in place with YF, but I dont have those details.

Since such tables do not work in advanced authoring, we are using free hand sql report for them.

Regards,

Bharath

photo
1

Hi Bharath,


Thank you for clarifying.


I have added you to the ticket regarding the defect with Virtual Tables you mentioned for visibility, this will be fixed in the upcoming 8.0.4 update expected in the next couple of weeks and will solve your issue.


Regarding drag and drop filter functionality for SQL reports, this has been reviewed and unfortunately do not have any immediate plans to implement this feature. This doesn't mean the item has been closed forever, just not on our current road map.


I'll be sure to update you with any changes in the future if anything changes. If there was anything else you were after in the meantime, please let me know.


Regards,

Simon

photo
1

Hi Simon,

Thank you for the help. Could you please share the RFE ID for us.

regards,

Bharath

photo
1

Hi Bharath,


YFN-17227 is the ID.


In the meantime, please don't hesitate to get in contact if you have any other questions.


Regards,

Simon

photo
1

Thank you Simon