Forum

This content is now out of date.

Visit Our Community

Freehand SQL Parameter linking

Hi,

I have a freehand sql query which I am running and I want to use parameters with it.

Here is the code for the query:
[code]
select
(select sum(budgeted_income)
from jc_view_quotes
where won = 1
and requested_fin_years_ago = 0
and department_name = {?})
/
(select sum(budgeted_income)
from jc_view_quotes
where (lost = 1 or won = 1)
and requested_fin_years_ago = 0
and department_name = {?})
* 100 AS 'Ratio';
[/code]

I want to do two things here:
1. Link the two parameters together so that the user only has to populate one filter
2. Have the filter operate as a cached dropdown.

Are either of these possible?

Many thanks

Brendon
Hi Brendon,

1. This currently cannot be done, an enhancement request has been created [TASK ID = 106152], this should be implemented in a future build.

2. Cached drop down filters are available via selecting custom query for the entry style of that filter.


Forum image


Regards,
Steven
Thanks for that Steven.

Does this mean I will still be able to link the parameter to a filter in another report in an analytic dashboard tab or do I have the same restrictions as trying to link the two filters together at the report level?

Thanks

Brendon
Hi Brendon,

You should be able to link the parameter to a filter in another report in an analytic dashboard tab without any restrictions.

Regards,
Steven
Hi Brendon.

Actually, there is a tricky way to achieve #1, just use local variables.

[code]
DECLARE @department_name varchar(50);

SET @department_name = {?};

select
(select sum(budgeted_income)
from jc_view_quotes
where won = 1
and requested_fin_years_ago = 0
and department_name = @department_name)
/
(select sum(budgeted_income)
from jc_view_quotes
where (lost = 1 or won = 1)
and requested_fin_years_ago = 0
and department_name = @department_name)
* 100 AS 'Ratio';
[/code]

Cheers,

Tof
Thanks for that Tof.

Now I have a tricky question for your tricky answer:

How can I populate the @department_name so that it can be used as a cached dropdown list for the user?

Normally I would be looking dorectly at the field which would prompt the user with the existing values. Can this be done via the @department_name mechanism?

This same question is going to apply for date parameters - I now need to use a between statement to get results between user specified dates:

[code]
DECLARE @from_date date;
DECLARE @to_date date;
DECLARE @department_name varchar(30);

SET @from_date = {?};
SET @to_date = {?};
SET @department_name = {?};

select
(select sum(budgeted_income)
from jc_view_quotes
where won = 1
and requested_date between @from_date and @to_date
and department_name = @department_name
)
/
(select sum(budgeted_income)
from jc_view_quotes
where (lost = 1 or won = 1)
and requested_date between @from_date and @to_date
and department_name = @department_name
)
* 100 AS 'Ratio';
[/code]

And I now get the following error:
[code]
SQL statement is invalid.
The error message returned from the database was:
You have an error in your SQL syntax; check the manual that corresponds
to your MySQL server version for the right syntax to use near 'DECLARE @from_date date;
DECLARE @to_date date;
DECLARE @depar' at line 1
[/code]

Of course, if there is an easier way to produce this sort of ratio without resorting to a freehand query I would be keen to hear about that as well.

Thanks

Brendon
Hi mate,

My example was a SQL server one, that's what I use on a day to day basis. I just assumed it would work on whatever dbms you are using as it is well defined in the SQL-92 standard and should be implemented identically across the board.

Other than that, your query looks fine. Does it run ok outside of yellowfin (replacing the {?} with actual values, of course)?

Also one more thing... Not sure about MySQL, but in SQL server, I can tell you for a fact that the ratio would always return 0 or 100 unless you cast both sums as numeric fields. that's because running, say 40/100 will return 0.4, which will be rounded to the nearest integer before being multiplied by 100

As for a creating a cached dropdown, I really can't see how. All yellowfin does is create a UI on the fly for your bound params. Since those are not actually linked to any field in your database, but are free input instead, there's not much they can do.

Sorry I couldn't help more.

Cheers,

Tof.

Thanks Tof,

That's pretty much what I was expecting re: the caching of values - gotta ask the question though. I'll just have to rely on the users being able to spell consistantly ;-)

MySQL doesn't do any of the pre-rounding so I'm getting good results returned - if you want to round you have to tell it to :-)

I have to admit I'm not as familiar with the SQL-92 standard as I possibly could be but I'll see if there is something odd in the way the variables are passing through - I wouldn't have though declaring as a date type should cause an issue.

As for not helping more, you've already been a huge help - I wasn't aware I could declare variables inside the freehand sql workspace so I've learnt that at least.

So thanks heaps.

Cheers

Brendon

Thanks for trying Yellowfin

Please complete the form below to request your copy of Yellowfin today.