As on Date Based Report

Karthik Kannan shared this problem 2 years ago
Resolved

Hi Yellowfin Team,

.

We need to create a as on date based ageing report


Our requirements are,

1. we will pass a single date filter to fetch the data <= Filter Date

2. need to calculate the ageing in days by comparing the Filter Date and need to calculate the Ageing Days like the following to show it in our reports.

Example : Ageing (Days) = Filter Date - Transaction Date.


How to create calculation fields based on Filter Date Value ?

How to achieve this in yellowfin ?


Thanks in Advance,

Karthik K

Comments (3)

photo
1

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

photo
1

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

photo
photo
1

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

photo
1

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

photo
1

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.

photo
1

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

photo
photo
1

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