BusinessObjects Board

Prompt with Case Statement and Default

building semantic layer
#1

I have a derived table in Universe Designer:
SELECT CASE rowno

      WHEN 1 THEN 'ALL'
      WHEN 2 THEN 'Office Summary Report'
      WHEN 3 THEN 'Office Recap Report'
      WHEN 4 THEN 'Examiner Recap Report'
      WHEN 5 THEN 'Financial Report'
      WHEN 6 THEN 'Rolling 12 Months Report'
   END
      report_name,
   rowno AS report_number

FROM ( SELECT ROWNUM rowno
FROM DUAL
CONNECT BY LEVEL <= 6)

The prompt is:
cursor(select distinct report_number from @DerivedTable(REPORT_SELECTION)
where report_number in @prompt(‘06. Select Report:’,‘A’, ‘List of Values\Report Name LOV’,multi,primary_key,not_persistent)),
@prompt(‘07. Report Period Ending:’,‘A’,‘List of Values\Report End Date Lov’,mono,primary_key,not_persistent,{‘ALL’})

The word ALL does display as the default in the Webi report; however, you do not get data returned. If you remove the default {‘ALL’} from the prompt, and simply select ALL from the LOV in the report, you get data. Does anyone know why the default will not work and if there is another way to accomplish this?
Thank you in advance for your help.

rholt (BOB member since 2013-03-04)

#2

[Moderator Note: Moved from WebIntelligence XI to Semantic Layer / Universe Designer]

And welcome to B:bob:B!

Marek Chladny :slovakia: (BOB member since 2003-11-27)

#3

Thank you :smiley: I sure hope I get a resolution to this issue!

rholt (BOB member since 2013-03-04)

#4

See FAQ: Designer and FAQ: Designer

It looks like you are missing “ALL” in the LOV.

BO)_User :us: (BOB member since 2010-03-01)

#5

I think you’re mixing prompts here and perhaps drillfilters?

The ‘ALL’ in the webi-report is only there, it makes it possible for you to drill to a one thing or show all. It’s not in you database and there is no logic anywhere that links ‘ALL’ in a report to an ‘ALL’ from the query.

And if i look at your prompt i see the ‘ALL’ referencing the end date and there ‘ALL’ means nothing its not a date.
As BO)_users says, you need to add ‘ALL’ there aswell.

But a love the little bit of coding, stored it in my archive thanks :smiley:
Would love to see how it visually looks when you run the report.

good luck

IngeH :netherlands: (BOB member since 2004-06-22)

#6

Is your prompt the actual code?

I don’t see how you’ll get it to work if it is.

#7

I added:

Union
Select ‘ALL’,1 from dual

But, I still have the same issue–ALL displays in the prompt as a defualt, but I do not get data. If I remove the default from the Universe, and select ALL from the list, I get data.

rholt (BOB member since 2013-03-04)

#8

That is just a ‘chunk’ of the code. What is throwing people off is that I accidently included the next prompt–the date prompt. Please ignore “prompt 07.” for this issue. FYI… I have several other prompts in this report with a default value of ALL or ALL-ALL (used for Client Name - Client ID, for example). The difference between the other prompts that have a default value and this one, is that this prompt is using a case statement and the other ones are not. As you can see from the case statement “1 = ALL”.

rholt (BOB member since 2013-03-04)

#9

Here is the actual prompt with the case statement:

where report_number in @prompt(‘06. Select Report:’,‘A’, ‘List of Values\Report Name LOV’,multi,primary_key,not_persistent,{‘ALL’})),

Sorry…I had accidently added {‘ALL’} to the wrong prompt in my original post. There is no {‘ALL’} in the date prompt!

rholt (BOB member since 2013-03-04)

#10

OK, so what are you trying to achieve?

#11

The issue has been respolved. The answer is:

cursor(select distinct report_number from @DerivedTable(REPORT_SELECTION)
where report_number in @prompt(‘06. Select Report:’,‘A:N’, {‘ALL’:‘1’,‘Office Summary Report’:‘2’,‘Office Recap Report’:‘3’,‘Examiner Recap Report’:‘4’,‘Financial Report’:‘5’,‘Rolling 12 Months Report’:‘6’},multi,primary_key,not_persistent,{‘ALL’:‘1’})),

It appears that you have to repeat your case statement in the prompt.

rholt (BOB member since 2013-03-04)

#12

Nice.
Could it be that the LOV which had report name and then number, only returned the name which was then compared with a number.
Defining it like this the relation number-name is clear in the prompt itself.

IngeH :netherlands: (BOB member since 2004-06-22)

#13

IngeH, per your earlier request, attached is a screenshot of the Report Selection portion of the prompt.

In answer to: Could it be that the LOV which had report name and then number, only returned the name which was then compared with a number. Defining it like this the relation number-name is clear in the prompt itself.

Yes, that is my thought, but I wasn’t able to find a workaround until now. After seeing the reslolution, I remember having to use A:N in a different report prompt (that didn’t have a case statement), but I was baffled on this prompt because I set the primary_key to number and have a case statement to define the relation, so I thought that would take care of it. I’m very new to BO development, so I’m still learning (a lot with this report)!
Select Report Prompt.doc (51.0 KB)

rholt (BOB member since 2013-03-04)

#14

Thanks for the screenshot, looks very nice.
This little bit of coding is definitely in my favorites now.

I’ve been doing BO for years now and still learn new tricks every time i use it and read questions here :wink:

IngeH :netherlands: (BOB member since 2004-06-22)