Date slider and SQL report parameters
24 May, 2016
Good morning,
I have been trying without success to link a date slider to a SQL report.
The SQL report contains the following parameters:
[code]
WHERE c.company_name = {?}
AND dc.case_closed_dt BETWEEN {?} AND {?}
[/code]
The Slider report has:
- one filter for company name which is a user input
- the slider spanning across dates
Within the dashboard, the company_name is linked successfully to the SQL report, but YF does not offer me to link the date parameters (see attachment).
Is there another syntax I need to use for these params to be slider-friendly ?
The slider itself works fine, it is being used in other linked reports in the dashboard successfully.
Thank you in advance for your help,
David
I have been trying without success to link a date slider to a SQL report.
The SQL report contains the following parameters:
[code]
WHERE c.company_name = {?}
AND dc.case_closed_dt BETWEEN {?} AND {?}
[/code]
The Slider report has:
- one filter for company name which is a user input
- the slider spanning across dates
Within the dashboard, the company_name is linked successfully to the SQL report, but YF does not offer me to link the date parameters (see attachment).
Is there another syntax I need to use for these params to be slider-friendly ?
The slider itself works fine, it is being used in other linked reports in the dashboard successfully.
Thank you in advance for your help,
David
Hi David,
Thanks for sending in the question and I'm sorry to hear that you are having difficulties linking date slider to another report.
I did some testing this morning in 7.2 (version 20160428), and I can confirm that I was successfully able to connect my free hand SQL report date slider to other reports in my dashboard.
This is what I did:
1. Created the following report (similar scenario to your own)
2. This SQL produced the following report output (after configuring filter defaults & activating date slider etc.):
3. I added this report, and a second report to a dashboard for linking. I ensured both reports included the same fields as a filter.
4. Under analytic setup, I was able to link my date slider to my 'Agency Sales over Time' report:
5. When I use my date slider, the filter is successfully passed to my other dashboard report:
So as you can see, the filters are applied as expected. So, I'm wondering a couple of things here:
1. Did my scenario accurately portray your own?
2. Does the report you are linking your freehand SQL report to, include a field that should be able to be linked to the date slider?
3. Are you able to link this date slider to other reports?
4. Which version/build of Yellowfin are you using? This will allow me to test this scenario in your build to see if this is a potential bug that has since been fixed.
Anyway, lets start here...hopefully we can figure out what is going on quickly!
Kind Regards,
Dustin
Thanks for sending in the question and I'm sorry to hear that you are having difficulties linking date slider to another report.
I did some testing this morning in 7.2 (version 20160428), and I can confirm that I was successfully able to connect my free hand SQL report date slider to other reports in my dashboard.
This is what I did:
1. Created the following report (similar scenario to your own)
2. This SQL produced the following report output (after configuring filter defaults & activating date slider etc.):
3. I added this report, and a second report to a dashboard for linking. I ensured both reports included the same fields as a filter.
4. Under analytic setup, I was able to link my date slider to my 'Agency Sales over Time' report:
5. When I use my date slider, the filter is successfully passed to my other dashboard report:
So as you can see, the filters are applied as expected. So, I'm wondering a couple of things here:
1. Did my scenario accurately portray your own?
2. Does the report you are linking your freehand SQL report to, include a field that should be able to be linked to the date slider?
3. Are you able to link this date slider to other reports?
4. Which version/build of Yellowfin are you using? This will allow me to test this scenario in your build to see if this is a potential bug that has since been fixed.
Anyway, lets start here...hopefully we can figure out what is going on quickly!
Kind Regards,
Dustin
Thanks for your reply Dustin,
The situation I'm in is actually the other way around, let me try and explain it with more details:
- The slider report is not a SQL report, and has only one filter for the date, which is set as operand "BETWEEN" and values "User prompt". This field is used as the time series for the slider.
- The linked report is a SQL report, with two filters for the date as shown above.
when trying to link the slider, I do not see options in the drop down list (see attachment in first post) for my SQL dates.
Thanks to your example, I believe this is due to the fact that the SQL expects two inputs which I thought the slider would be providing behind te scenes.
I have tried setting the SQL to
[quote="">
AND dc.case_closed_dt BETWEEN {?}
[/quote]
or
[quote="">
AND dc.case_closed_dt = {?}
[/quote]
but the issue remains the same ... any idea how to solve this without having to split my single "between" filter into two single date filters ?
Cheers,
David
The situation I'm in is actually the other way around, let me try and explain it with more details:
- The slider report is not a SQL report, and has only one filter for the date, which is set as operand "BETWEEN" and values "User prompt". This field is used as the time series for the slider.
- The linked report is a SQL report, with two filters for the date as shown above.
when trying to link the slider, I do not see options in the drop down list (see attachment in first post) for my SQL dates.
Thanks to your example, I believe this is due to the fact that the SQL expects two inputs which I thought the slider would be providing behind te scenes.
I have tried setting the SQL to
[quote="">
AND dc.case_closed_dt BETWEEN {?}
[/quote]
or
[quote="">
AND dc.case_closed_dt = {?}
[/quote]
but the issue remains the same ... any idea how to solve this without having to split my single "between" filter into two single date filters ?
Cheers,
David
Hi David,
Ah thanks for clarifying (apologies for the delayed response). It turns out that what you are after just isn't possible. You can't pass a filter/date slider to a free hand SQL report.
This has been requested by other customers in the past and an enhancement request was opened as a result: Task ID# 106150.
This enhancement is still considered unplanned, so it can be hard to say when this might be implemented into a future build of Yellowfin. Sorry for the bit of inconvenience here.
I am wondering, is it possible for you to use the traditional report builder to create your report here instead?
Kind Regards,
Dustin
Ah thanks for clarifying (apologies for the delayed response). It turns out that what you are after just isn't possible. You can't pass a filter/date slider to a free hand SQL report.
This has been requested by other customers in the past and an enhancement request was opened as a result: Task ID# 106150.
This enhancement is still considered unplanned, so it can be hard to say when this might be implemented into a future build of Yellowfin. Sorry for the bit of inconvenience here.
I am wondering, is it possible for you to use the traditional report builder to create your report here instead?
Kind Regards,
Dustin