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.
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
Would love to see how it visually looks when you run the report.
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.
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”.
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, 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)