Anyone know of any easy solution to enable/apply embedded filters based on a multiple packages? We have an FM model which supports multiple business areas and would like to apply filters based on a number of packages. For instance, Table A would have a certain Filter applied for package A and a different filter for package B. We don't want to create standalone filters and put them in the reports because it's a series of filters and could be a little tedious and complex to recreate for each report.
I can think of a few possible ways of doing this.
First thing to check, create a new report on one of the models and add the following macro to a query:
#sq(substitute(''']','',substitute('package\[\@name=''','',join('',grep ( 'package' , split('/',$modelPath)) ) )))#
This should return the model name. For some reason the $modelPath parameter seems to work in some places and not others.
If that does work create a parameter map with the filters you need. Then use the macro to perform a lookup:
#filterLookup{substitute(''']','',substitute('package\[\@name=''','',join('',grep ( 'package' , split('/',$modelPath)) ) ))}#
Stick that into whichever query subject you want filtered, publish and test. It won't work from inside framework.
Thanks for your response Paul.
I was able to return the model name in the report using the macro you provided. I was a little unsure of how to proceed w/ the next steps.
As for creating the parameter map with the filters I need, let's say I have a filter on a customer table where active_ind = y. Would I create an entry in a parameter map with a key of 1 and Value of y?
How would I then proceed w/ the macro in the customer query subject. Let's assume my package name is test.
Let's say you have two packages, test and set. Test you want the filter to be [Namespace].[tableName].[active_ind] = 'y' and in set you want the filter to be [Namespace].[tableName].[active_ind] = 'n'.
Create a parameter map with static values. Set the default to 1=0 to prevent any shenanigans. Let's call it packageFilterLookup.
The parameters should appear like this:
| Key | Value |
| test | [Namespace].[tableName].[active_ind] = 'y' |
| set | [Namespace].[tableName].[active_ind] = 'n' |
Your filter expression should now be:
#
$packageFilterLookup{substitute(''']','',substitute('package\[\@name=''','',join('',grep ( 'package' , split('/',$modelPath)) ) ))}
#
Paul...
Thanks again for your explanation. It worked perfectly!!
Any suggestions if each of the packages were to have multiple filters (either in the same table or different table). I ask because the key in the parameter map is the package name and we can't have multiple lines w/ the same key.
Thanks again!
An interesting thing to note, standalone filters created in Framework can be nested into other filters.
Create standalone filters for each package. Use the path to those filters as the use for the parameter map.