As on Date Based Report

Nathan Schroeder ●

Hi Karthik,

I think that using a free-hand SQL report would be your best option here. Freehand SQL allows you to create filters by using defined parameters a={?}

You can then have one column return your equation, with the parameter as filter date.

The best way to start a free-hand SQL report is to create everything you can within the drag-and drop builder, and then extract the generated SQL statement by clicking the "view SQL" button in the lower right hand corner of the "Data" tab.

Let me know if this makes sense, or if you have any questions.

Regards,

Nathan

Reply URL

1

Karthik Kannan ●

Hi Nathan,

Thanks for your reply.

If you provide steps to solve the above mentioned situation, then it will be more useful.

Thanks in Advance,

Karthik K

URL

1

Nathan Schroeder ●

Hi Karthik,

I would recommend starting here (using parameters is covered at the bottom):

http://wiki.yellowfin.com.au/display/USER73/Freehand+SQL+Reports

I am a little busy at the moment to put together a full demonstration, but if you are still having trouble, I should have some time early next week.

Regards,

Nathan

Reply URL

1

Karthik Kannan ●

Hi Nathan,

Thanks for your reply.

It works for me. But i am using same parameters repeatedly in my queries, i need to display only one Filter for a parameter. i verified the following page, but it also showing errors. Please guide me to achieve this.

https://www.yellowfinbi.com/resources/forum/yfforum-freehand-sql-parameter-linking-thread-106150

Thanks in Advance,

Karthik K

URL

1

Karthik Kannan ●

Hi Nathan,

Thanks for your reply.

Please ignore the previous message. It is working for me.

In filters only ËQUAL TO operator is working (not able to give IN operator). we are not able to use other operators. For Filter Values, we used Custom Query to generate list of values.

How to achieve Filtering reports using all other operators including EUQAL TO.

Thanks in Advance,

Karthik K.

URL

1

Nathan Schroeder ●

Hi Karthik,

The only operator you cannot use here is the IN LIST, which is a limitation that unfortunately will not be overcome in the near future (though there is an enhancement for this). The rest of the operators will be possible by including this in your SQL statement. For example:

SELECT * FROM table
WHERE my_date > {?}

While the filter will still show as "equals" in the report, the actual logic will be greater than. Another idea here is to create a row in report that returns "Max/min __". This will return the database value closest to your filter rather than the exact filter value, but may meet your needs.

In the end this will be difficult to optimize, but I will continue to think of ways around this.

Regards,

Nathan

Hi Karthik,

The only operator you cannot use here is the IN LIST, which is a limitation that unfortunately will not be overcome in the near future (though there is an enhancement for this). The rest of the operators will be possible by including this in your SQL statement. For example:

SELECT * FROM table
WHERE my_date > {?}

While the filter will still show as "equals" in the report, the actual logic will be greater than. Another idea here is to create a row in report that returns "Max/min __". This will return the database value closest to your filter rather than the exact filter value, but may meet your needs.

In the end this will be difficult to optimize, but I will continue to think of ways around this.

Regards,

Nathan

URL

1

Nathan Schroeder ●

Hi Karthik,

I am going to set this ticket to closed for now, but if there is anything else I can do to help here, please just let me know and the case will be re-opened!

Regards,

Nathan

Reply URL

As on Date Based Report

Replies (3)