I'm not able to get WHERE EmployeeID = ({?}); to work... I'm never able to select more than one option. This was true in 7.1 and now that my license provider has upgraded to 7.3 the same is still true. Perhaps this option is deactivated? I do not have access to the database and some admin access is restricted. Has the syntax changed and never been updated or is perhaps the syntax in the WIKI incorrect?:
with multiple possible input parameters use: select * from client where country in ({?}) (Note the additional Brackets)
I'm not able to get WHERE EmployeeID = ({?}); to work... I'm never able to select more than one option. This was true in 7.1 and now that my license provider has upgraded to 7.3 the same is still true. Perhaps this option is deactivated? I do not have access to the database and some admin access is restricted. Has the syntax changed and never been updated or is perhaps the syntax in the WIKI incorrect?:
with multiple possible input parameters use: select * from client where country in ({?}) (Note the additional Brackets)
"If inserting a source filter with multiple possible input parameters use: select * from client where country in ({?}) (Note the additional Brackets)"
So if you want to get this working you must firstly have a Source Filter set up (a.k.a. Access Filter)
Then you follow the syntax as shown in the example, and then in the Data screen of the Report Builder click the filter's Advanced Settings button which will bring up a Configure Filters screen where if you click the User Prompt button you will then have a choice to change the User Prompt filter to a Source Filter (a.k.a. Access Filter) as shown below:
I hope that makes sense, if not then show me what steps you've done and where it's going wrong and we'll work it out.
regards,
David
Hi Larry,
please take note that the documentation says
"If inserting a source filter with multiple possible input parameters use: select * from client where country in ({?}) (Note the additional Brackets)"
So if you want to get this working you must firstly have a Source Filter set up (a.k.a. Access Filter)
Then you follow the syntax as shown in the example, and then in the Data screen of the Report Builder click the filter's Advanced Settings button which will bring up a Configure Filters screen where if you click the User Prompt button you will then have a choice to change the User Prompt filter to a Source Filter (a.k.a. Access Filter) as shown below:
I hope that makes sense, if not then show me what steps you've done and where it's going wrong and we'll work it out.
Hi Dave, how do I go about asking for Access Filter capabilities? I have a 3rd party license through Credible Inc and this is not an option for us. Is this even available for third party licensees? I'm asking in this forum because I've been told by my superiors that we are charged for every question we submit to them and I want to make the correct request in order to avoid unnecessary charges. Thank you!
Hi Dave, how do I go about asking for Access Filter capabilities? I have a 3rd party license through Credible Inc and this is not an option for us. Is this even available for third party licensees? I'm asking in this forum because I've been told by my superiors that we are charged for every question we submit to them and I want to make the correct request in order to avoid unnecessary charges. Thank you!
I've uploaded a video that demonstrates how to set up a Freehand SQL Report with a Source Filter just in case that is what you are after. However, if there is something else then just create a Ticket here in the Yellowfin Community. Tickets are private whereas Questions, Problems, Ideas are public.
regards,
David
Hi Larry,
I've uploaded a video that demonstrates how to set up a Freehand SQL Report with a Source Filter just in case that is what you are after. However, if there is something else then just create a Ticket here in the Yellowfin Community. Tickets are private whereas Questions, Problems, Ideas are public.
Essentially, I want to be able to execute a Stored Proc that already takes a set of parameters. These are parameters that are selected based on user input.
Can I setup a a freehand SQL in which I can invoke a Stored Procedure? Something like "EXECUTE <Stored Proc Name (Parameter1, paramter2, ...)>
Here I intend to pass access (or source) parameters to the report. I want to invoke the report via link on my site.
I see here that a URL can be used to invoke a report with using the parameters as well.
Can this idea then work?:
1. Setup a freehand sql report that simply calls a Stored Procedure by running "EXECUTE <Stored Proc name (param1, param2, ...)>
2. Invoke the report via a url; in this url I pass the parameters that are needed by the stored proc. (of course I will be logged into YellowFin at this point, so the report should render for me
3. Once (and if) the report runs, build some visual with it ( eg: use a bar graph or pie chart to display the information.
4. Allow "drill-through" on the information by invoking another freehand sql?
PS: I have seen the FreehandSQLReport.mp4. Big thanks for that!!!
hi dave,
Same issue, but a different set of questions.
Essentially, I want to be able to execute a Stored Proc that already takes a set of parameters. These are parameters that are selected based on user input.
Can I setup a a freehand SQL in which I can invoke a Stored Procedure? Something like "EXECUTE <Stored Proc Name (Parameter1, paramter2, ...)>
Here I intend to pass access (or source) parameters to the report. I want to invoke the report via link on my site.
I see here that a URL can be used to invoke a report with using the parameters as well.
Can this idea then work?:
1. Setup a freehand sql report that simply calls a Stored Procedure by running "EXECUTE <Stored Proc name (param1, param2, ...)>
2. Invoke the report via a url; in this url I pass the parameters that are needed by the stored proc. (of course I will be logged into YellowFin at this point, so the report should render for me
3. Once (and if) the report runs, build some visual with it ( eg: use a bar graph or pie chart to display the information.
4. Allow "drill-through" on the information by invoking another freehand sql?
PS: I have seen the FreehandSQLReport.mp4. Big thanks for that!!!
Fantastic! Verifies exactly what I was thinking to do. Shared this with my team. Essentially we did not want to throw away the work we had already done on existing reports if we choose to use YellowFin going forward. This should work fine.
Regards,
Andy
Hi Big Dave,
Fantastic! Verifies exactly what I was thinking to do. Shared this with my team. Essentially we did not want to throw away the work we had already done on existing reports if we choose to use YellowFin going forward. This should work fine.
that's great! And in fact, that is one of the main reasons why Yellowfin has the Freehand SQL Reports and Views features (and also Views created from stored procedures) - to enable porting of pre-existing reports into Yellowfin.
If any other questions at all should arise please don't hesitate to ask!
regards,
David
Hi Aniruddha,
that's great! And in fact, that is one of the main reasons why Yellowfin has the Freehand SQL Reports and Views features (and also Views created from stored procedures) - to enable porting of pre-existing reports into Yellowfin.
If any other questions at all should arise please don't hesitate to ask!
What you have demoed was great on yellowfin 7.1. I've got different results when using yellowfin 7.4.
#1. Access Filter which was used as security filter must be created following a certain requirements and formats as shown in the attached screenshot. The one you demoed in 7.1 (Filter 1) was more like query parameter with a selected data type. But 7.4 wouldn't allow you to do so.
#2. The Freehand SQL like the one below will pop up an error:
SELECT * FROM trn.tbl_D_ITRNTransaction WHERE [kITRNTransaction] = {?}
Also have tried different format from {?} to '?', "?", {'?'} so on so forth, none of them will be working. It may be related back to #1 as I failed to create an Access Filter as if it was a query parameter.
It would be great if you could give a complete demo on yellowfin 7.4 from creating a general purpose Access Filter (instead of a security purpose to retain a user's limit) to creating a freehand SQL query.
Regards,
Alan
Hi Big Dave,
What you have demoed was great on yellowfin 7.1. I've got different results when using yellowfin 7.4.
#1. Access Filter which was used as security filter must be created following a certain requirements and formats as shown in the attached screenshot. The one you demoed in 7.1 (Filter 1) was more like query parameter with a selected data type. But 7.4 wouldn't allow you to do so.
#2. The Freehand SQL like the one below will pop up an error:
SELECT * FROM trn.tbl_D_ITRNTransaction WHERE [kITRNTransaction] = {?}
Also have tried different format from {?} to '?', "?", {'?'} so on so forth, none of them will be working. It may be related back to #1 as I failed to create an Access Filter as if it was a query parameter.
It would be great if you could give a complete demo on yellowfin 7.4 from creating a general purpose Access Filter (instead of a security purpose to retain a user's limit) to creating a freehand SQL query.
regarding point #1. I've tested the Access Filter functionality using ({?}) in a FreehandSQL Report in the latest 7.4 (7.4.7) and it worked for me. I've attached a short video to demonstrate this to you ("FreehandSQL_AccessFilter_in_747.mp4")
And regarding point #2. I've just tested out a similar query with a parameter {?} in a Freehand SQL Report in 7.4.7 and it also worked fine for me!
I've attached a short video to demonstrate this to you ("FreehandSQL_param_in_747.mp4")
So I'm not sure why these functionalities aren't working for you - it would be great if you could watch these 2 videos and see if the steps you are doing are the same and then let me know. Also, please let me know which build of 7.4 you are using.
thanks,
David
Hi Alan,
regarding point #1. I've tested the Access Filter functionality using ({?}) in a FreehandSQL Report in the latest 7.4 (7.4.7) and it worked for me. I've attached a short video to demonstrate this to you ("FreehandSQL_AccessFilter_in_747.mp4")
And regarding point #2. I've just tested out a similar query with a parameter {?} in a Freehand SQL Report in 7.4.7 and it also worked fine for me!
I've attached a short video to demonstrate this to you ("FreehandSQL_param_in_747.mp4")
So I'm not sure why these functionalities aren't working for you - it would be great if you could watch these 2 videos and see if the steps you are doing are the same and then let me know. Also, please let me know which build of 7.4 you are using.
I've taken another approach by using a proc and pass in 2 query params. These 2 query params will take values from report params which were configured as shown in attached doc 10_02_Format Report Params.PNG. All wired up well. That was great. This feature might have beaten Power BI. As far as I know of that PBI couldn't pass report params back to query params. Nice talking to you, mate!
Cheers,
Alan
Thanks BD, that's awesome!
I've taken another approach by using a proc and pass in 2 query params. These 2 query params will take values from report params which were configured as shown in attached doc 10_02_Format Report Params.PNG. All wired up well. That was great. This feature might have beaten Power BI. As far as I know of that PBI couldn't pass report params back to query params. Nice talking to you, mate!
from your previous response, it was great to hear that you got the parameters and access filters working in 7.4 Freehand SQL Reports, and now in your latest response I see you have switched to using the Stored Procedure view and it has helped you to meet your reporting requirements! This is great news indeed, thanks for letting us know, and also it is good to hear that we might have beaten Power BI ;-)
And good on you for trying the many different features that Yellowfin has to offer!
regards,
David
Hi Alan,
from your previous response, it was great to hear that you got the parameters and access filters working in 7.4 Freehand SQL Reports, and now in your latest response I see you have switched to using the Stored Procedure view and it has helped you to meet your reporting requirements! This is great news indeed, thanks for letting us know, and also it is good to hear that we might have beaten Power BI ;-)
And good on you for trying the many different features that Yellowfin has to offer!
Thanks BD for your encouraging comment. So far NULL param is unable to pass in to a proc. Hope in next version it can do so. Stmt like this is more often:
WHERE (kClass = @kClass or @kClass is null)
Cheers,
Alan
Thanks BD for your encouraging comment. So far NULL param is unable to pass in to a proc. Hope in next version it can do so. Stmt like this is more often:
The same question
Yes, you can include a parameter into a Freehand SQL Report (but not a Freehand SQL View) by using a question mark surrounded by braces: {?}
SELECT EmployeeName FROM Employee WHERE EmployeeID = {?};
After you Validate the SQL code then Yellowfin will ask you to specify the data type of your parameter. (Video attached)
Yes, you can include a parameter into a Freehand SQL Report (but not a Freehand SQL View) by using a question mark surrounded by braces: {?}
SELECT EmployeeName FROM Employee WHERE EmployeeID = {?};
After you Validate the SQL code then Yellowfin will ask you to specify the data type of your parameter. (Video attached)
Hi Larry,
please take note that the documentation says
"If inserting a source filter with multiple possible input parameters use: select * from client where country in ({?}) (Note the additional Brackets)"
So if you want to get this working you must firstly have a Source Filter set up (a.k.a. Access Filter)
Then you follow the syntax as shown in the example, and then in the Data screen of the Report Builder click the filter's Advanced Settings button which will bring up a Configure Filters screen where if you click the User Prompt button you will then have a choice to change the User Prompt filter to a Source Filter (a.k.a. Access Filter) as shown below:
I hope that makes sense, if not then show me what steps you've done and where it's going wrong and we'll work it out.
regards,
David
Hi Larry,
please take note that the documentation says
"If inserting a source filter with multiple possible input parameters use: select * from client where country in ({?}) (Note the additional Brackets)"
So if you want to get this working you must firstly have a Source Filter set up (a.k.a. Access Filter)
Then you follow the syntax as shown in the example, and then in the Data screen of the Report Builder click the filter's Advanced Settings button which will bring up a Configure Filters screen where if you click the User Prompt button you will then have a choice to change the User Prompt filter to a Source Filter (a.k.a. Access Filter) as shown below:
I hope that makes sense, if not then show me what steps you've done and where it's going wrong and we'll work it out.
regards,
David
Hi Dave,
Thanks for the clarification. Can you send me a message so that we can take this discussion offline?
From: Yellowfin Support <support@yellowfin.bi>
Sent: Monday, November 20, 2017 5:36:02 PM
To: Larry Beasley
Subject: New Comment in "Parameters in Freehand SQL Reports"
Hi Dave,
Thanks for the clarification. Can you send me a message so that we can take this discussion offline?
From: Yellowfin Support <support@yellowfin.bi>
Sent: Monday, November 20, 2017 5:36:02 PM
To: Larry Beasley
Subject: New Comment in "Parameters in Freehand SQL Reports"
Hi Larry,
I've uploaded a video that demonstrates how to set up a Freehand SQL Report with a Source Filter just in case that is what you are after. However, if there is something else then just create a Ticket here in the Yellowfin Community. Tickets are private whereas Questions, Problems, Ideas are public.
regards,
David
Hi Larry,
I've uploaded a video that demonstrates how to set up a Freehand SQL Report with a Source Filter just in case that is what you are after. However, if there is something else then just create a Ticket here in the Yellowfin Community. Tickets are private whereas Questions, Problems, Ideas are public.
regards,
David
hi dave,
Same issue, but a different set of questions.
Essentially, I want to be able to execute a Stored Proc that already takes a set of parameters. These are parameters that are selected based on user input.
Can I setup a a freehand SQL in which I can invoke a Stored Procedure? Something like "EXECUTE <Stored Proc Name (Parameter1, paramter2, ...)>
Here I intend to pass access (or source) parameters to the report. I want to invoke the report via link on my site.
I see here that a URL can be used to invoke a report with using the parameters as well.
Can this idea then work?:
1. Setup a freehand sql report that simply calls a Stored Procedure by running "EXECUTE <Stored Proc name (param1, param2, ...)>
2. Invoke the report via a url; in this url I pass the parameters that are needed by the stored proc. (of course I will be logged into YellowFin at this point, so the report should render for me
3. Once (and if) the report runs, build some visual with it ( eg: use a bar graph or pie chart to display the information.
4. Allow "drill-through" on the information by invoking another freehand sql?
PS: I have seen the FreehandSQLReport.mp4. Big thanks for that!!!
hi dave,
Same issue, but a different set of questions.
Essentially, I want to be able to execute a Stored Proc that already takes a set of parameters. These are parameters that are selected based on user input.
Can I setup a a freehand SQL in which I can invoke a Stored Procedure? Something like "EXECUTE <Stored Proc Name (Parameter1, paramter2, ...)>
Here I intend to pass access (or source) parameters to the report. I want to invoke the report via link on my site.
I see here that a URL can be used to invoke a report with using the parameters as well.
Can this idea then work?:
1. Setup a freehand sql report that simply calls a Stored Procedure by running "EXECUTE <Stored Proc name (param1, param2, ...)>
2. Invoke the report via a url; in this url I pass the parameters that are needed by the stored proc. (of course I will be logged into YellowFin at this point, so the report should render for me
3. Once (and if) the report runs, build some visual with it ( eg: use a bar graph or pie chart to display the information.
4. Allow "drill-through" on the information by invoking another freehand sql?
PS: I have seen the FreehandSQLReport.mp4. Big thanks for that!!!
Hi Aniruddha,
I hadn't tried such a combination of steps previously so I quickly tested all of the steps and it all seemed to work.
So I made a quick video of these steps for you (see attached mp4), please excuse my poor test data and reports - obviously it was just a quick test.
Please let me know if you have any further questions on the matter.
regards,
David
Hi Aniruddha,
I hadn't tried such a combination of steps previously so I quickly tested all of the steps and it all seemed to work.
So I made a quick video of these steps for you (see attached mp4), please excuse my poor test data and reports - obviously it was just a quick test.
Please let me know if you have any further questions on the matter.
regards,
David
Hi Big Dave,
What you have demoed was great on yellowfin 7.1. I've got different results when using yellowfin 7.4.
#1. Access Filter which was used as security filter must be created following a certain requirements and formats as shown in the attached screenshot. The one you demoed in 7.1 (Filter 1) was more like query parameter with a selected data type. But 7.4 wouldn't allow you to do so.
#2. The Freehand SQL like the one below will pop up an error:
SELECT * FROM trn.tbl_D_ITRNTransaction WHERE [kITRNTransaction] = {?}
Also have tried different format from {?} to '?', "?", {'?'} so on so forth, none of them will be working. It may be related back to #1 as I failed to create an Access Filter as if it was a query parameter.
It would be great if you could give a complete demo on yellowfin 7.4 from creating a general purpose Access Filter (instead of a security purpose to retain a user's limit) to creating a freehand SQL query.
Regards,
Alan
Hi Big Dave,
What you have demoed was great on yellowfin 7.1. I've got different results when using yellowfin 7.4.
#1. Access Filter which was used as security filter must be created following a certain requirements and formats as shown in the attached screenshot. The one you demoed in 7.1 (Filter 1) was more like query parameter with a selected data type. But 7.4 wouldn't allow you to do so.
#2. The Freehand SQL like the one below will pop up an error:
SELECT * FROM trn.tbl_D_ITRNTransaction WHERE [kITRNTransaction] = {?}
Also have tried different format from {?} to '?', "?", {'?'} so on so forth, none of them will be working. It may be related back to #1 as I failed to create an Access Filter as if it was a query parameter.
It would be great if you could give a complete demo on yellowfin 7.4 from creating a general purpose Access Filter (instead of a security purpose to retain a user's limit) to creating a freehand SQL query.
Regards,
Alan
Hi Alan,
regarding point #1. I've tested the Access Filter functionality using ({?}) in a FreehandSQL Report in the latest 7.4 (7.4.7) and it worked for me. I've attached a short video to demonstrate this to you ("FreehandSQL_AccessFilter_in_747.mp4")
And regarding point #2. I've just tested out a similar query with a parameter {?} in a Freehand SQL Report in 7.4.7 and it also worked fine for me!
I've attached a short video to demonstrate this to you ("FreehandSQL_param_in_747.mp4")
So I'm not sure why these functionalities aren't working for you - it would be great if you could watch these 2 videos and see if the steps you are doing are the same and then let me know. Also, please let me know which build of 7.4 you are using.
thanks,
David
Hi Alan,
regarding point #1. I've tested the Access Filter functionality using ({?}) in a FreehandSQL Report in the latest 7.4 (7.4.7) and it worked for me. I've attached a short video to demonstrate this to you ("FreehandSQL_AccessFilter_in_747.mp4")
And regarding point #2. I've just tested out a similar query with a parameter {?} in a Freehand SQL Report in 7.4.7 and it also worked fine for me!
I've attached a short video to demonstrate this to you ("FreehandSQL_param_in_747.mp4")
So I'm not sure why these functionalities aren't working for you - it would be great if you could watch these 2 videos and see if the steps you are doing are the same and then let me know. Also, please let me know which build of 7.4 you are using.
thanks,
David
Thanks BD, that's awesome!
I've taken another approach by using a proc and pass in 2 query params. These 2 query params will take values from report params which were configured as shown in attached doc 10_02_Format Report Params.PNG. All wired up well. That was great. This feature might have beaten Power BI. As far as I know of that PBI couldn't pass report params back to query params. Nice talking to you, mate!
Cheers,
Alan
Thanks BD, that's awesome!
I've taken another approach by using a proc and pass in 2 query params. These 2 query params will take values from report params which were configured as shown in attached doc 10_02_Format Report Params.PNG. All wired up well. That was great. This feature might have beaten Power BI. As far as I know of that PBI couldn't pass report params back to query params. Nice talking to you, mate!
Cheers,
Alan
Hi Alan,
from your previous response, it was great to hear that you got the parameters and access filters working in 7.4 Freehand SQL Reports, and now in your latest response I see you have switched to using the Stored Procedure view and it has helped you to meet your reporting requirements! This is great news indeed, thanks for letting us know, and also it is good to hear that we might have beaten Power BI ;-)
And good on you for trying the many different features that Yellowfin has to offer!
regards,
David
Hi Alan,
from your previous response, it was great to hear that you got the parameters and access filters working in 7.4 Freehand SQL Reports, and now in your latest response I see you have switched to using the Stored Procedure view and it has helped you to meet your reporting requirements! This is great news indeed, thanks for letting us know, and also it is good to hear that we might have beaten Power BI ;-)
And good on you for trying the many different features that Yellowfin has to offer!
regards,
David
Thanks BD for your encouraging comment. So far NULL param is unable to pass in to a proc. Hope in next version it can do so. Stmt like this is more often:
WHERE (kClass = @kClass or @kClass is null)
Cheers,
Alan
Thanks BD for your encouraging comment. So far NULL param is unable to pass in to a proc. Hope in next version it can do so. Stmt like this is more often:
WHERE (kClass = @kClass or @kClass is null)
Cheers,
Alan
Hi Alan,
I see what you mean! I have tried this out and I agree with you that a NULL value can't be passed into the stored procedure view.
So I have raised a new Idea in the Yellowfin Community (https://community.yellowfinbi.com/agent/object/11264) under your name to request this functionality.
Thanks for your input!
regards,
David
Hi Alan,
I see what you mean! I have tried this out and I agree with you that a NULL value can't be passed into the stored procedure view.
So I have raised a new Idea in the Yellowfin Community (https://community.yellowfinbi.com/agent/object/11264) under your name to request this functionality.
Thanks for your input!
regards,
David
Replies have been locked on this page!