I’m attempting to build “one size fits all” reports to satisfy a range of differing requirements, each containing prompts for the relevant criteria AND a means of using wildcards for criteria that will not need to be filtered.
e.g. I have “Trading Area Code” matches pattern… and the user(or my Excel VBA app) can input “Y%” for codes prefixed by “Y” or simply “%” to show all area codes.
What I need is a similar wildcard for “In List”
Does this exist???
i.e. Customer Account number in list prompt - input = “1234;3456;4567” where the user needs to restrict the report for a list of known customers, and input = “%” where they want to bring everything back
I’d really appreciate any help - or an alternative solution. I’m sure it must be something simple (like me!)
(@Select(Sales Geography\Country Group) IN @Prompt('Select Country Group or
enter * for all groups','a','Sales Geography\Country Group',multi,free) ) OR
('*' in @Prompt('Select Country Group or enter * for all groups','a','Sales
Geography\Country Group',multi,free))
I’m trying something VERY simple to start with so I can see how this works.
I’ve created a very simple report, gone into “Edit Data Provider” and clicked the “SQL” button at the top.
The SQL is as follows:
SELECT
STATS.AREAS.AREA
FROM
STATS.AREAS
WHERE
(
STATS.AREAS.AREA IN @variable('Enter Area Codes separated by ;')
)
The Report therefore contains one object from my company’s “STATS” Universe and an in list prompt. I can’t see how the above could be tweaked… or am I completely off track???
After some umming and aargh! ing I found the “Do not generate SQL before running” tickbox…
Thanks for your help - I’m getting nowhere with this - I can only assume I don’t have the “Designer Solution” you’re referring to and am completely off track. Someone in our IT dpt probably has it, but if that’s the case I’ll never see it and they probably won’t action my requests; certainly not this year!
I’m therefore going to have to look at a VBA solution - which is what I should have been concentrating on in the first instance really.
I can already launch reports, save, export, burst, and populate prompt inputs from macros in Excel - what I’ll therefore need to do is to somehow remove conditions from within the report prior to refresh if my filter range = “” or “%”. Nobody in the business uses B.O. as a GUI; everything either ends up in Excel or PDF - I’m ending up having to create more and more Excel based interactions with the BO object model in vba.