One Filter for Unions

Larry Beasley shared this question 3 years 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.

Best Answer
photo

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);

Comments (5)

photo
1

Version 7.1... sorry

photo
3

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

photo
1

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!

photo
1

No problem, glad to hear it worked!

photo
1

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

photo
4

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);

photo
2

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)

photo
2

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

photo
1

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.

photo