For most of us the @Prompt function syntax can be a bit confusing at first. Sure we catch on quickly, however, we often need to review the business objects @Prompt syntax available online or refer to a previously created @Prompt. After we get comfortable with the @Prompt and the available options such as custom list of values, constrained/free, mono/multi, and default/purge values (link to article), many of us are ready to take our @Prompts to the next level.
Prompt Conditions – Fancy Lines in a SQL WHERE Clause
Of course, if we are discussing @Prompt then we are discussing work done in Business Objects’ Designer. I suppose you could hard-code one in the SQL of a WebI report, but if you are doing that then I don’t think you are ready for the next level. Anyway, back to Designer, @Prompts are placed in condition objects (the little filters). Conditions become part of the WHERE clause of a report’s SQL. Therefore, much of what you can do with the WHERE clause in SQL can be replicated in Designer’s condition objects. This means that you can include AND and OR logic, for starters. This is where the magic starts, because “OR” logic is the drive behind optional prompt conditions. Anyway yo get the technical discussion started here is an example of basic business objects @prompt syntax:
Products.product_num IN @Prompt('Enter Product Number(s)','A',,MULTI,FREE)
Multiple @Prompt Applications Using @Variable
Let me take one step back and discuss the BO syntax briefly. @Prompts are translated into SQL by BO with the values submitted to them. Sometimes this translation is frustrating (dates date types for example). The interesting thing is that this translation can occur for each instance of the @Prompt in the report’s SQL. So you could place it in various parts of your report’s WHERE clause and each would be executed and evaluated. HOWEVER, the prompt values are all consolidated under a single @Prompt text string. This because the unique identifier of the @Prompt is the text displayed to the end user, you know the first string following the “@Prompt(“. Another way to reuse a prompt in the SQL of a report is to use the @Variable function with the exact same prompt text string. This allows you to use the input from the user without replicating all of the rest of the syntax in the @Prompt. Using @Variable is preferable where possible because it will avoid having to make changes to an @Prompt in too many locations (when changes are inevitably needed).
Creating a True Condition
SQL WHERE clauses function much like most logical programming; they evaluate to “True” or “False”. I don’t really want to get into a tutorial on logic, but it is important that you understand a bit about it. In short, this is what you need to know:
true and true = true
true and false = false
true or false = true
false or false = false
In order to create an optional prompt you will need to create a clause that has the ability to evaluate true based on a valid parameter value or a submitted keyword that you have configured for the prompt. Therefore you want an OR clause. Here is the key to the whole thing and it looks like this:
Employee.employee_id = @Prompt('Enter a value (* to bypass filter)','A','',MONO,FREE)
OR
'*' = @Variable('Enter a value (* to bypass filter)')
The keyword that is pre-configured in this prompt is: * (the asterisk character). So do you see it? If I enter a valid value for Employee.employee_id then my optional prompt clause will return those employees. If I enter a “*” character (minus the quote characters) the prompt clause will evaluate “TRUE” for all records and this will make as though the enter Prompt clause were not even present. It has been bypassed.
Standard Practices with Business Objects Optional Prompts
I have seen various keywords used over the years. The “*” (asterisk) is the most common. The word “All” is used by some, but when using letters you have to be prepared to handle or prevent mixed case responses. I have also seen the keyword added to a List of Values and the Business Objects prompt set to constrained to prevent upper/lower-case issues. This would for MONO or MULTI prompts, but please know that putting multiple values together with the keyword will still result in a complete TRUE clause/statement (or bypassing of it). Often the keyword is included in the prompt text or at least in any auxiliary training documentation. Remember, the keyword doesn’t help anyone who is not aware of it or how to use it. Finally, the keyword should not be similar, close to, or the same as any possible valid value for the prompt condition (nothing close to Employee.employee_id in this case); this would REALLY confuse the end-users of the BO prompt.
Optional Prompts in Business Objects XI 3
BO finally caught up with its competitors with the release of BO XI 3.0. In this release it began to include the ability to make report-level prompts optional through a check box. The unfortunate part is that this requires the user to create his or her prompts in the report and not follow the best practice of creating them in the universe. I haven’t yet seen any way to make a universe-level @Prompt optional based on configuration initiated at the report level. Perhaps this is by design; it allows the universe author to perfectly control both the composition and the optional nature of the prompt.
If you haven’t yet seen how to make a report level prompt option, please take a look at the image below. To get to this point you need (1) open the report or create a new one, (2) click on “Edit Query”, (3) locate a report-level prompt or create one, (4) click on the prompt properties button, it looks like a question mark in a blue circle over-lapping a small window with an “X”, (5) click on the “Optional prompt” check box, (6) click “OK”, (7) switch to the “Edit Report”, (8) test the prompt and save the report.
Modify Report > Edit Query > Prompt Properties > Optional Prompt
Hi Preeti, an @Prompt() function with the type of ‘D’ will always attempt to convert the submitted value to date data type. As you know a null value or rather in your case and empty string cannot be converted to any date. This is the source of the problem you are experiencing. There is a work around though; use a different @Prompt data type.
For example, you could use the data type of ‘A’ and then put a function around the submitted value to detect if submitted value is a blank string or if it is a non-blank string. Of course, if you are comparing this to a date objects you will need to have your function convert a detected non-blank string to a date data type. Also you need to be sure that your report users know which date edit mask you are expecting.
The drawbacks to this are 2:
1) Performance will degrade, but maybe your blank date value already was going to cause this
2) You do not get to use the calendar tool in the prompt.
In BO XI 3.X you could just set the prompt to optional and this would work much better and cleanly.
Hi Julian,
I have requirements to create a webi report that allows user to generate a report by district or region. If user select value(s) for district, it should by pass the region prompt and generate a report based on the selected district(s). The same thing if user select value(s)for region. How can I achieve this requirement? Reading your response to Preeti question, it seems like setting the prompt to optional is what I need. Would you please tell me how I can set the prompt to optional in BO XI 3.X.
Thanks for your help!
Hi Cait, please have a look above at the article, I added a new section on Business Objects XI 3.X Optional Report-Level Prompts. The workflow is: Modify Report > Edit Query > Prompt Properties > Optional Prompt
Thanks Julian. I am able to set the prompt optional at the report level and it works great.
Another requirement that I have to meet is allowing the user to select ‘*’ for both prompts. I was successfully implementing this requirement as pre-condition in the universe. However, with the new requirement of setting the prompts optionally as it can only be done at the report level, is there a way for me to achieve both?
Thank you so much for your help!
Cait, I can’t see any reason both could not be set to optional.
Hi Julian,
My requirement is to have two prompts on the report; one for district and the other is for region. If the user selects district then the region is by pass and vice verse. Also, user should be allowed to select ‘*’ for all values in either district or region.
You have shown me how to set the prompt optional from the report level and I was able to implement the ‘*’ requirement by creating filters for district and region in the universe. My question is how can I achieve both requirements since one is being implemented in the report and the other in the universe. Would you please show me how to do?
Thank you!
Hi Cait, that info helps me understand better. The truth is that an optional prompt and selecting all values is the same things logically. Think of a SQL WHERE clause as a complicated Boolean variable. For a SQL statement to return any record the WHERE clause must evaluate entirely to true for that record. So if you add prompts to the WHERE clause you added opportunities for false resolutions of the clause, or in more simple terms you add filters. However, if you allow a user to bypass a prompt then they can remove that filter from the statement.
WebI report-level prompts can be set to optional and in effect this means that if a user provides no value for the prompt then the entire prompt condition is removed from the SQL statement.
The universe-level optional prompt method in the article allows you to define a keyword or key character, such as “*”, that when submitted by the user makes it so that the entire prompt condition always evaluates to true, the condition resolves to something like “
field = '*' or '*' = '*'“. If a clause evaluates to true for any and all records then this has the same effect as though the condition is not present.So selecting what may be called “all values” for a prompt and having a report-level prompt be optional are really the same thing, or rather they have the same effect on the SQL.
Hi julian,
I have a requirement where I want to use a date prompt and if a user doesn’t select the prompt it should default to current date, it would be good if we can show current date by default in prompt.I tried the way you explained above but somehow it’s not parsing properly.
Thanks,
Vivek
Hi Vivek, I like this requirement; it is challenging, but it is completely justifiable. I am going to have to think about this one, get back to you soon.
Thank you for your explanation. I kind of understand what you are saying but having problems implementing it. Would you please show me how to do it?
Thank you for your help!
Hi Julian,
I’m trying to get the folowing optional prompt to work but I get a parsing error….
@Select(Resources\Manager Name) in @Prompt(‘Enter a value (* to bypass filter)’,’A’,”,MULTI,FREE) OR
‘*’ in @Variable(‘Enter a value (* to bypass filter)’)
What am I doing wrong ? On on a SQL Server DB. Does that make a differnce ?
It will parse if I replace the ” with ‘Resources\Manager Name’ but then the filter does not return the expected results…. I get no data.
Here is the SQL that is genrated. Sorry for the complexity as manager name is built from the first Middle and last.
SELECT
CASE WHEN RES_GSE_RESOURCE_MANAGER.FIRST_NAME IS NOT NULL THEN RES_GSE_RESOURCE_MANAGER.LAST_NAME + ‘, ‘ + RES_GSE_RESOURCE_MANAGER.FIRST_NAME ELSE RES_GSE_RESOURCE_MANAGER.LAST_NAME END
FROM
CMN_SEC_USERS RES_GSE_RESOURCE_MANAGER
WHERE
( CASE WHEN RES_GSE_RESOURCE_MANAGER.FIRST_NAME IS NOT NULL THEN RES_GSE_RESOURCE_MANAGER.LAST_NAME + ‘, ‘ + RES_GSE_RESOURCE_MANAGER.FIRST_NAME ELSE RES_GSE_RESOURCE_MANAGER.LAST_NAME END ) In ( ” )
AND (( CASE WHEN RES_GSE_RESOURCE_MANAGER.FIRST_NAME IS NOT NULL THEN RES_GSE_RESOURCE_MANAGER.LAST_NAME + ‘, ‘ + RES_GSE_RESOURCE_MANAGER.FIRST_NAME ELSE RES_GSE_RESOURCE_MANAGER.LAST_NAME END ) in @Prompt(‘Enter a value (* to bypass filter)’,’A’,’Resources\Manager Name’,MULTI,FREE) OR
‘*’ in @Variable(‘Enter a value (* to bypass filter)’))
Thanks,
Ben
Hi Ben, instead of replacing the
''with anything, try just removing it. That is to say:@Select(Resources\Manager Name) in @Prompt('Enter a value (* to bypass filter)','A',,MULTI,FREE) OR
'*' in @Variable('Enter a value (* to bypass filter)')
Please report back how this change helped or didn’t.
BTW, in answer to the comment left by “Hi Julian”, please see Ben’s comment for a good example.
Hi Vivek, there is a way to meet your requirement:
I would do this by defining a keyword and a replacement of that keyword using SQL logic. In other words, there is no BO solution for this as far as I know, but with some SQL logic you might be able to get something close to what you want. Here is what I have doen in the past.
order_date = TO_DATE( (DECODE( @Prompt('Enter order date as YYYY-MM-DD or the words TODAY or YESTERDAY','A',,MULTI,FREE,Not_Persistent,{'TODAY'},User:0), 'TODAY', TO_CHAR(SYSDATE,'YYYY-MM-DD'), @Variable('Enter order date as YYYY-MM-DD or the words TODAY or YESTERDAY')), 'YYYY-MM-DD')
The syntax is probably not 100% correct, but the logic is what I wanted to show you. Please post your results with using this; however, consider that it will reduce the performance of your query as it will make it less likely that your query hits any index on “order_date” (for example).
Julian,
Thanks for the help. I found a few things… my first mistake was that I was putting the code in the where of an Object… when I put it in a Condition it worked with either using ‘Resources\Manager Name’ or using no ” at all.
I’m new to Universe design and BO with no formal training so a lot of this is trial and error for me. Sites like yours are a lot of help. Thanks so much and keep up the good work !!!
Ben
Hi Julian,
Thanks a lot I’ll try it out and post the results.
Regards,
Vivek.
Hi there,
I have an unusual request where by the user wants to be able to type in information into a prompt and pass it through to the report header.
NOTE: he is not prompting a field, he just wants a required blank text box to type in information, make a selection on the other 5 prompts and pass “All” the information to the report.
(XIr2 Deski)
Regards..
Regis
Hi Regis, I have come across this one before. You need to create a condition with a prompt that will always evaluate to true. What does this look like:
('DUMMY' = @Prompt('Enter Desired Header Text','A',,MONO,FREE) OR 1 = 1)
Then you need to use the “UserResponse()” function in the report to pull up the value and use it anywhere you want.
One great application is to use this to allow a user to set the parameter for an alerter, so that they can decide through a prompt what the alerter trigger value(s) should be.
Julian,
Works Perfect, thank you sooo much. One more thing, how do I make it a required field meaning the report can not ran without anything in that prompt??
Kind regards
R
Jilian,
Disregard my pervious question, thank you for all your help..
Regards
R.
I am creating a webi report that will have a date and time input prompt (right now I am keeping them separate but that is subject to change). I also have to include a prompt for time zone. The way I want it to work is the user enters date and time and their time zone(I haven’t decided the prompt order yet, as I am sure it will play a key role in making this query work). The filter has to be smart enough to convert the time they are entering into EST and run the Query against that. Meaning if they select PST as the time zone I have to convert the time and date to EST and have the Query run against the EST time and date. I say date because if I pick 6/10/2010 and 12:00 AM I need the query to run against 6/9/2010 9:00 AM.
I know I will have to use the following oracle function
SELECT FROM_TZ(CAST(TO_DATE(‘1999-12-01 11:00:00’,
‘YYYY-MM-DD HH:MI:SS’) AS TIMESTAMP), ‘America/New_York’)
AT TIME ZONE ‘America/Los_Angeles’ “West Coast Time”
FROM DUAL
But if anyone can give me some advise on this it would be really helpful
Thanks so much
Amol
Hi Amol, This is mostly a SQL solution that you need. It looks like you are on the right path, but you need to build that “1999-12-01 11:00:00” part. I think you will only be able to do this if you set those prompts to be type “A” (string) and then you concatinate all of those @Prompt together. It will look messy, but it might just work.
Julian, in the @prompt, how do you present a description & code to the user but only pass the code in the SQL? In the example below, the Camp_LOV has only codes in it which works. I’d like to concatenate the code + Desc and present that in the prompt but only pass the code in the SQL. Does Primary_Key and Index Awareness need to be used? Any ideas?
Where Cmpcd in @Prompt(‘Choose a Campus’,’A’,’Camp_Lov\Campus_CD)’,multi,free)
Thanks, Keith
Julian, I see that Pat asked the same question about ID & Desc on December 1st, 2009 at 7:23 pm. Can you expand on how to achieve this. I’m using Designer 12.1.
Thanks
Hi Keith,
Sorry for the delay, got busy and needed to reconfigure my sandbox environment. I checked in 12.3 (a.k.a. XI 3.1, SP3) and this still works as it did in previous versions. I will write an article on the topic this week, but for now I can tell you:
1) Open Object
2) Click on Properties tab
3) Click on “Edit” button in lower LOV section
4) Add any combinable objects you want to the LOV query, making sure that the original desired object is first
5) Click “Save and Close”
6) Click OK
7) Create a prompt on the object (report or universe level). For report level be sure that the list of values option is selected. For universe level you will need to add the object to the 3rd parameter with single quotes.
8) Test the report and remember what you see in Java Report Panel will not be exactly what you see in normal WebI.
I will try to get an article together with screenshots ASAP.
Wow! This does work in Webi as you suggessted. Unfortunetly for me, I’m using Crystal for reporting and it does not work. The code is displayed but not any other objects. I’ve opened a ticket with BO hopeing it’s something simple. Thanks Julian!!
Hi Keith, there is an alternative in BO and that is to set the the key on the description object so that it is set to the ID. But this would only allow you to them prompt for the description and have the query use the key for sake of query efficiency and data integrity. Therefore it is a limited workaround.
Hi,
1). I have a prompt at the universe level for the Fiscal Year and the Year.
2). At the report level I have to display 12 columns for 12 Periods.
3). While refreshing the report, the period selected in the prompt shows up the correct values in the reprot for the period selected.
And shows up 0 values for all other 11 periods. I want to show up the values in the previous columns based on their period respectively. Unable to do this at report.
An immediate help would be highly appreciated.
Thanks,
Anushka
Hi Anushka, you have a query problem, not a BO problem. Your query is probably not pulling in the data that you want. I suspect you have your prompts set to only return data for the prompt response value. What you need is a condition that will give you all of the data for the prompt response value and 12 period prior to it. To accomplish this you will need to use a date function to calculate the date of 12 periods back dynamically and I would use it on the prompt response using a @Variable(‘Fiscal Year’).
Hi Julian,
Thanks for the help.
Yes the query written is not pulling the required data. Let me explain you my requirements here.
I have objects in universe as 01-Act Amt, 02-Act Amt … 12-Act Amt
Each object represent each month.
The Query for these objects is as
case when “Ledger”.LedgerTypeCode = ‘AA’ and “FiscalYear”.FiscalYearChar= @Select(Prompts and Filters\CFY) and @Select(Prompts and Filters\CFM) = ’01’ then ( “Facts”.NetPosting01) ELSE 0
END
the query is same for all the 12 objects, the only change in the query is the month.
So, now while I am refreshing my report for the Period 10 and Fiscal year 2009, I get perfect values for period 10 for the year 2009.
Where as for the period 01 to 09 for the fiscal year show up as 0.
My requirement is, I need to even show up the values for period 01 to 09 for the year 2009, and not just show 0 values in the column.
I know, my query is not proper.. but I am unable to modify my query such that it even pulls the values for pervious period for the same year.
Hope you are able to understand my requirement here.
Thanks,
Anushka
Hi Anushka, I have seen something like this before. We ended up with 12 queries. You could either do a union, or use separate queries linked by merged dimensions. To make it dynamic for all queries your prompts must have identical display text and you must figure out the date function (or possibly some other function) that you need to be able to convert the prompt response into values that will give you each of the 12 previous periods. I would need to see your prompts exactly as they are to be able to provide more details.
I noticed that when implemented report level prompts option, I have to click “Refresh Values” for the LOV to show up.
Is there a way for LOV to show up instead of requiring the user to click on”Refresh Values” everytime?
Thanks,
Caitlin
Hi Caitlin, perhaps I misunderstand, but I think you just need to set your report to refresh on open. When the report opens it will automatically show the prompt(s). Also, if you LOV is blank and you want it to have values then you will need to edit it in the universe. There you can choose to always refresh the LOV each time it is accessed.
Hi Julian, I did set my report to refresh on open. I also checked the “Automatic refresh before use” option in the designer but the LOV still did not shown. When I run a webi, the prompt is shown but it is empty and I have to click “Refresh Values” for the LOV to appear. It seems to only happened when using option prompt.
This LOV is static so user should not have to click on “refresh values” everytime. Is there a way for me to accomplish that? Thanks.
This i strange. How about you go back to the LOV in Designer. Remove the auto-refresh setting. Then open the LOV in Designer so that you can see the values. Then close it. Then save and export the universe. Then test the report again. If this does nothing, you might try putting something in the universe so that you can be sure it is being updated on the server. Please report back when you can.
Hi Julian, I have tried as you had suggested which is removing the auto-refresh setting and still the values is empty. I tried putting back the auto-refresh setting and still nothing changed. As far as to make sure the univers is updated, I have added new objects so I think we can ensure that the universe is updated on the server.
FYI, I removed the auto-refresh setting and set the export LOV option. The first time I ran the webi report the LOV is still empty. After clicked “Refresh Values”, save and close the report. When I open the webi this time the list will appear. However, when we migrate the same report to different environments (DEV,QA,PROD). The LOV might appear for the same report in DEV might not appear in QA or PROD. This is so strange. Please let me know if have any suggestions. Thanks.
Hi Caitlin, you solved it. Good job. Are you sure that you generated the LOV in Designer before you exported? If so, you did all I can think of.
Many thanks for help! I’m so grateful!
“Show Values” button in WEBI wasn’t visible and I was desperate. . . till I bumped into that useful site!
Cheers,
Ivo
Hi, can you help me.I have created an oracle function to get an account name for a account number. I need to be able to run this function on the Webi report for the UserResponse and display it at the top of my report.
In other words I need to have the account name for the account entered at the prompt request in webi.
Hi Nanaz, I would need to see more, but essentially you need to collect the prompt value and then you can access it in the query by placing an @Variable(‘Same-text-as-prompt’) in the function. If you just want the function to execute and nothing else then I would recommend using a dummy table or even “Dual” (Oracle only). Again, I am not sure I understand how you want to use this, but basically if you can get a SQL statement mock-up of what you want to work then you only need to take that and put it in a view, substitute in the @Prompt where you want it (in the function parameter), drop the view in your universe.
HI,
I have one issue with Condition prompt.
I have a condition with following syntax. When i parse, it say
Parse failed: Exception: DBD, ORA-00933: SQL command not properly ended State: N/A
@Select(Hotel Contact\Hotel Contact Fax Primary Ind Y/N) (+) = ‘Y’ and @Select(Hotel Contact\Hotel Contact Fax Type Code) (+) = ‘PFAX’.
Is there any mistake in the prompt.
object defination is
Hotel Contact Fax Primary Ind Y/N —object name with following defination.
“htl_cont_INDVL_fax_alias”.PRIM_IND
and
Hotel Contact Fax Type Code–object defination
“htl_cont_INDVL_fax_alias”.PHONE_TYP_CD
I have tried option by changing ANSI also. Does not help.
help me please
Hi,
I have an issue with cascading prompts. The 1st prompt is against say company therefore the filter is created as @Select(Company\Company) in @Prompt(’01. Company,’A’,Company\Company,multi,free)
The next level is the Division and there is a filter set as @Select(Division\Division Code) in @Prompt(’02. Division’,’A’,’Division\Division Code’,multi,free)
Finally the 3rd level is for section as @Select(Section Class\Section Class) in @Prompt(’03. Section Class’,’A’,’Section Class\Section Class’,multi,free)
The problem is that the 3rd level is returning Sections that do not exist in the 1st level, i.e.:
Company A contains sections X,Y and Z
Company B contains sections S,T and U
Company A has Divisions called D, E & F
Company B has Divisions called G, E & H
In Company a section Y is in Division E, whereas in Company B section U is in Division E.
The issue is that when we select the 1st prompt A, the 2nd as E we get both Y and U returned in the list of values where it should only be Y.
I have tried re-using the 1st prompt in the condition of the 3rd but this results in a repeat of the prompt in the 2nd level.
Is there a way to limit the 3rd prompt based not only on the 2nd but the 1st in cascading prompts?
Many thanks in advance
David
Hi again.. Further to the above… what appears to happen is that in cascading prompts the next level only sees what was entered in the previous level. I cannot get the 3rd level to limit what was entered in the 1st. If I use the @prompt or @variable in the condition of the 3rd level, the query will prompt for this to be entered again at whatever level I put this in. thanks again… David
Hi Julian
I have been working on your optional prompts tip over the last day or so with Crystal reports 2008(long standing business view/crystal reports users here sorry – please forgive me!). The @prompt syntax works just dandy. Unfortunately, I am getting a SQL 42000 “Must declare the scalar variable” error when inserting the OR clause to the condition. DETAILS:
I created a quick and nasty Universe off the Audit DB using just the EVENT_TYPE table. The following condition parses and works fine in WebI:
EVENT_TYPE.Event_Type_Description = @Prompt(‘enter’,’A’,’Qhers Audit Dbo Event Type\Event Type Description’,mono,free,Not_Persistent,{‘*’},User:0) OR ‘*’ = @Variable(‘enter’)
Do you know if anyone has this working in Crystal reports?
Hi Glenn, sorry for the delay. Perhaps Crystal Reports doesn’t implement the @Variable the same way as Business Objects Enterprise. Could you perhaps just repeat the entire @Prompt, so it would look like this:
( EVENT_TYPE.Event_Type_Description = @Prompt(‘enter’,'A’,'Qhers Audit Dbo Event Type\Event Type Description’,mono,free,Not_Persistent,{‘*’},User:0) OR ‘*’ = @Prompt(‘enter’,'A’,'Qhers Audit Dbo Event Type\Event Type Description’,mono,free,Not_Persistent,{‘*’},User:0) )Maybe that will fly. Please let us know.
Thank you Julian. I had concluded this may be the only way, but thought I’d ask anyway. It is more code and far less elegant, which always leaves plenty of room for mistakes. I will be rigorous with my copy and paste and in ensuring the prompt name is always the same in both places. Thanks again.
Hi Glenn, one more thing. You might want to check to see if you can get an answer at Ken’s Crystal Reports website (http://www.kenhamady.com/).
Can I do like this?
@Select(Resources\Manager Name) in @Prompt(‘param_resource_id’,’A’,,MULTI,FREE) OR
‘0’ = @Variable(‘param_resource_id’)
@Prompt is multiselect but I want to check value of the parameter using “=”.
Hi JustStartedUsingBO, what you show is a valid SQL statement only when single values are submitted to the prompt. I suggest you try the following:
@Select(Resources\Manager Name) IN @Prompt(‘param_resource_id’,'A’,,MULTI,FREE) OR’0? IN @Prompt(‘param_resource_id’,'A’,,MULTI,FREE))
I know this departs from using the @Variable where it would be clean, but I am not sure how @Variable works with an array (multi) or values. Also use “IN” twice, it is cleaner and should give you what you want.
Thanks for responding.
I don’t think what you suggested will solve my problem.
We use Clarity tool which points to BO. We can not do like manupilating parameter values after resport request is submitted. In the report parameter we send All(which is code 0) as default. But if user selects something from multi-select and do not remove ‘All’ entry from multiselect the BO report is going to return all query result due to or condition in where clause. We have all the parameters in report as optional.
So here is I am looking for. If user keep 0 (All)entry and selects other codes like 1,2,3 I want BO to return records for 1,2,3 only.(We will rename All to -Select-). But if user keeps only ‘All’ or ‘-Select-‘ I want query to ignore that parameter in where condition.
How you can do this?