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.





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

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