Input Control to filter Multiple Queries

[Former Member]

I have a report that has multiple cells placed on it with data metrics.  (Number of Loans, Balance of Loans).  I have an Input Control that filters these cells based off of their State (California, Colorado, etc).  I have an an additional cell on the report that has a couple different filters for it's query that makes it different from the other two cells (Number of Loans and Balance of Loans), but I want the same Input Control to filter them both even though they have different queries that create them.  Is this possible??

[Former Member]

Plz try this if you have two block in report tab and also you do not merge the query

In the Edit Input Control box, go to the Dependencies tab.

You see Block 1 is selected. Select Block 2 as well.

Click OK. Now, try it out. As you select values in the Input Control, Block 1 & Block 2 continues to show the filtered data,

Regards,

Kannan Karthikeyan..

[Former Member]

Sorry small correction

if you have merged the  two query  instead of do not merge query

Regards,

Kannan Karthikeyan

[Former Member]

The queries are not merged and under the "Dependencies", I only have one option (Page Body).

[Former Member]

Hi Dylon,

The queries does not need to be merged which the approach I suggested earlier..

[Former Member]

Finally, everything works just as you suggested.  Thank you very much for your time!

[Former Member]

Glad to know if fixed it...

[Former Member]

You can retrieve the value selected in the input control using ReportFilter([state]) .

Now create another formula if ReportFilter([object]) = [Query 2 State] then 1 else 0

Then just create a regular block level filter using that new object for the block with set value as 1. You might have to do some additional steps here to make to compatible for a cell which I am sure you can easily figure it out.

That should do the trick for you. Let me know if you face any issue with the approach. I will try it out in my machine when I get some free time.

[Former Member]

Thanks for the suggestion but still having a bit of trouble.  So far I've created a cell that retreives the value selected in the input control.  When I try to create another formula that references a different query, it isn't liking it and giving me an error. 

[Former Member]

This is the "if" formula I have:  =If ReportFilter([Bank Scoreboard].[Market Name])=([YTD New Loans].[Market Name]) then 1 else 0.

"Bank Scoreboard" is the query that is driving all of my other fields in the report and "YTD New Loans" is the query for the one field that I want the filter in "Bank Scoreboard" to control too.

[Former Member]

Hi Dylon,

I checked in my machine and the approach I suggested works. However it does not work for ALL value selection in input control. For which the formula needs to be modified a little bit.

Step 1 : Get the input slection from Query1 lets say [INPUT]

=ReportFilter([Fiscal Year Id])

Step 2: Create a formula useing query 2 Object like  [Query 2 Filter]

= If Pos( [Input] ;[ Query 2 Fiscal Year Name] ) >= 1 Then 1 Else 0

Step 3: Create a filter on the Block or cell fetched by query 2 as

[Query 2 Filter] = 1

This should solve the issue . Check these screenshots . In my example one of my query based of HANA (Left most block) the second query is based on Oracle (Middle) and right most block is to show the selection in input control

Also there is one more way you might achieve the same thing(I have not tried it)  that is based on element linking.

[Former Member]

Can you show me the formulas you're using in step 2 and 3?  Below is my formula for step 2 but is obviously erroneous.

=If Pos([Bank Scoreboard].[Market Name];[YTD New Loans].[Market Name])>=1 Then 1 Else 0

[Former Member]

I have put the formulas already in the post

First one variable name X_var

= reportfilter([Bank Scoreboard].[Market Name])

Second one

=If Pos([X_var];[YTD New Loans].[Market Name])>=1 Then 1 Else 0

[Former Member]

I think we're close but I must still be missing some small step.  Currently, I have one Variable created called

"XVAR" and the formula is:  =ReportFilter([Bank Scoreboard].[Market Name])

The next Varialbe is:

"Query 2 Filter" and the formula is:  =If Pos([XVar];[YTD New Loans].[Market Name])>=1 Then 1 Else 0

I then set a block filter on "Query 2 Filter" to 1.  The "Market Name" Input Control still does not filter the cell running off of my secondary query.  What am I missing??

[sateesh_kumar1]

Hi Daylon,

did you merged your queries ?

[Former Member]

The queries are not merged.  Does there need to be a report tab that exists for the second query or can there just be a second query that exists for the report filter in tab one to query off of?