BusinessObjects Board

BO 5.1.6 Prompt for "In List = All/Null" wildcard?

Hi,

I’m hoping someone can help me.

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

Many thanks


Penfold :uk: (BOB member since 2008-05-09)

It is simple, although you need a Designer solution:-

https://bobj-board.org/t/15227


Mak 1 :uk: (BOB member since 2005-01-06)

You’ve lost me! :slight_smile:

OK so I’m no SQL expert…

Extract from the link:

(@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…

As mentioned before… I’m completely lost

Thanks :slight_smile:


Penfold :uk: (BOB member since 2008-05-09)

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.


Penfold :uk: (BOB member since 2008-05-09)

Yes, you need Designer for this solution.

Its a shame your IT dept cannot respond to your request, it seems that they will be making life harder than it needs to be…


Mak 1 :uk: (BOB member since 2005-01-06)

Thanks for your help.

If anyone’s interested, the new VBA thread is here:


https://bobj-board.org/t/114908

I will endeavour to post whatever solution is found (or will let you know if I give up! :))


Penfold :uk: (BOB member since 2008-05-09)