New Topic

Community Topic Question Report Builder

Nathan Schroeder

NA Business Team

Assigned

Category Report Builder

One Filter for Unions

Larry Beasley shared this question ago

Answered

I would like to know if there is a way to provide the user the ability to set one filter for a report I created using the Union in the report builder.

The same question

Best Answer

Larry Beasley ●

Hi Peter! I'm not with YellowfinBI but that sounds like it could be resolved by choosing 'cache on demand' for the value list setup. If not, try a custom query maybe like

;WITH n(n) AS

(

SELECT 1

UNION ALL

SELECT n+1 FROM n WHERE n < 1000

)

SELECT dateadd(month, n, '2015-01-01') FROM n where dateadd(month, n, '2015-01-01') <= getdate() ORDER BY n

OPTION (MAXRECURSION 1000);

Replies (5)

Larry Beasley ●

Version 7.1... sorry

Reply URL

Nathan Schroeder ●

HI Larry,

Thanks for getting in touch. If you include a filter in the primary query, you can then link a filter defined in the union sub-query to this:

10ed8a2e8fb836a96273b5fbfda35ecb

Within the union sub-query set-up:

e405150c92bb509ad9627144ee239d74

Does this seem like what you are looking for? If you have already considered this option and ruled against it, I would be interested in knowing a bit more about what your specific goals are so that I can provide a more relevant suggestion.

Regards,

Nathan

Reply URL

Larry Beasley ●

This is exactly what I am looking for. I have several unions due to the way the data is stored. I initially had three filters separately defaulted to a value and the user had to change all three. This solution works perfectly. Thanks!

Reply URL

Nathan Schroeder ●

No problem, glad to hear it worked!

Reply URL

Peter Michael Sørensen ●

Hi Ntahan I have a similar problem with union, but not completely the same.

I have 2 queries in a report from 2 different views put together with union. I need 1 filter as a prompt with "year-month" with all values from both queries together. I cant find a solution for that...

I have put the filter into both queries where the "union-filter" links to the master query, but it is only showing the filter values from the master query and I need both.

I could of course make the view on database level or write some custom SQL , but if there is an easy solution in YF I would like to know and use that :-).

Regards Peter

Reply URL

Larry Beasley ●

Hi Peter! I'm not with YellowfinBI but that sounds like it could be resolved by choosing 'cache on demand' for the value list setup. If not, try a custom query maybe like

;WITH n(n) AS

(

SELECT 1

UNION ALL

SELECT n+1 FROM n WHERE n < 1000

)

SELECT dateadd(month, n, '2015-01-01') FROM n where dateadd(month, n, '2015-01-01') <= getdate() ORDER BY n

OPTION (MAXRECURSION 1000);

URL

Peter Michael Sørensen ●

Thanks a lot Larry - with a few modifications, I used the custom query solution in the master query, linking to the sub-union queries and it works perfectly in my report :)

The final code was:

;WITH n(n) AS

(

SELECT 1

UNION ALL

SELECT n+1 FROM n WHERE n < 2000

)

, filterdays as (

SELECT cast(dateadd(day,-1,dateadd(month, n, '2014-01-01')) as date) as dato FROM n where dateadd(month, n, '2014-01-01') <= getdate()-1

)

SELECT cast(dateadd(day,-1,getdate()) as date) dato

union

select dato from filterdays ORDER BY 1 OPTION (MAXRECURSION 2000)

URL

Larry Beasley ●

I love it when a plan comes together! Have a good one!

URL

Larry Beasley ●

You could also use min(date_field) from your table if you also wanted to.

One last caveat, I use unions and sometimes I have fiscal week and fiscal month in the same column and then I place that column in the 'Section' section. :-) That way on part of my report is based on weeks and the other is based on months. I'll use both columns as their own filter for the main report but if I'm doing a drill-through, I use the column data rather than the filter for the drill through report. That way it sends both 'Jun 25 2017 - July 1 2017' and 'July 2017' to my drill through report. In your case, this bit of information I just shared doesn't matter because your data are the same. I just thought I'd share in case you find yourself wanting to do the same.

URL