0

I am trying to create a new report with a filter based on another query. For instance I have a bunch of fields and want to add this type of filter: if place code = '22' then provider state = 'PA' else no filter.

I have tried writing to separate queries; I've tried some case if, case when scenarios that all fail when testing the code.

case when ([place_code] = '22' then [provider_state] = 'PA' ) end

There is just too many results if I don't put in this filter. I ultimately need all place_code's and the respective provider_states but when it's place_code 22 I only want to see provider_state's of 'PA'

David Redden
  • 19
  • 2

2 Answers2

0

Try something like

Create the Data Item [State Selected]
Note: the XX value is an impossible scenario on purpose

case
when ([place_code]='')Then('XX')  
when ([place_code]='22')Then('PA')
when ([place_code]='21')Then('TN')
..
END

Have a filter with an "OR" condition

([State Selected] <> 'XX' AND [provider_state] = [State Selected])
OR
([State Selected] = 'XX' AND [provider_state] <> [State Selected])

This way if there is a valid filter, it is applied

If there is no filter, then the other part of the OR statement takes place making the filter behave as if disabled

VAI Jason
  • 534
  • 4
  • 14
  • I think this method would have worked; but I believe there are permissions issues in the environment I work with. The Data Item tested find Then the filter errored out saying RQP-DEF-0103 Cross joins between query subjects ...... are not permitted for the user who has the identity '' I haven't worked on the second suggested approach; If I can figure out how to do that approach I'll give it a shot. I really appreciate your help on this. – David Redden Oct 24 '19 at 14:43
  • Can you try a hard coded filter? Just to see if the data items can be used/combined? Something like [place_code] = '22' AND [provider_state] = 'PA'. This could be a model that requires Framework Manager to address (cross join implying the tables are not connected in the model) – VAI Jason Oct 24 '19 at 14:59
0

Another approach is to use master detail Have 2 queries

Query 1 has the main content Query 2 has the [provider_state] data and is joined to Query 1

If there is a provider state, then the detail query will show the results, otherwise it will be blank

VAI Jason
  • 534
  • 4
  • 14