According to the requirement, i have 4 prompts, user can enter or select the values in all the prompts
or some time they want only on select one prompt and leave the other blanks.
It is not possible. The only thing you could do would be to add an all option in your LOV for each object. The users would still have to enter it.
Its the basic working methodology of SQL that any condition should have a value to it. Similarly all prompts will have to be filled.
my requirement is:
i need to create 4 diff prompts with default value , ( means select * from table)
when i follow below code, it is prompting two prompts, i dont want two prompts at run time.
i want user to select All or single value or multiple value
ex:
SELECT DISTINCT
( ECH_ARCHIVE.REFCOUNTRY.COUNTRY_NAME )
FROM
ECH_ARCHIVE.REFCOUNTRY
WHERE
( (( ECH_ARCHIVE.REFCOUNTRY.COUNTRY_NAME ) IN @Prompt(‘Select Country Name
or
enter * for all groups’,‘a’,‘Region\Country Name’,multi,free)) and
(’*’ in @Prompt(‘Select Country Name or enter * for all groups’,‘a’,‘Region\Country Name’,multi,free))
)
whenever user refresh the report, the default value should be *.
as i am using below code which was said by BOB group.
(@Select(Prompts\Sub Region Name) in @Prompt(‘4. Select Sub Region Name or Enter * for all groups’,‘a’,‘Prompts\Sub Region Name’,multi,free)) OR
(’*’ in @Prompt(‘4. Select Sub Region Name or Enter * for all groups’,‘a’,‘Prompts\Sub Region Name’,multi,free)) AND @Select(Ech Archive Refsubregion\Region Code)=2
what modification to be done to the existing code to get the default value as * when user refresh the report.
BusObj Classic has no built-in functionality to specify default prompt values (like in Web Intelligence). It can be done with a small VBA macro. Use the following in the ThisDocument module:
Private Sub Document_BeforeRefresh(Cancel As Boolean)
Dim Var As Variable
Set Var = ThisDocument.Variables("4. Select Sub Region Name or Enter * for all groups")
If Var.Value = "" Then
Var.Value = "*"
End If
End Sub
I prefer to set the default only if there is no current value for the prompt. If the user has previously chosen something other than *, it should be retained between refreshes.
It really is a shame that you can’t do it. I built a reporting system a few years that dynamically wrote the SQL (much like BO does) and it had optional criteria as a feature. Simply if you did not put a value in that field then it did not add that criteria to the final where clause.
When we converted my system to BO (for company wide standardization reasons) we had to replace the 5 reports in the system with over 50 reports in BO. It is a shame that such a simple feature still does not exist.
i went to tools–> macros----> macros
i did give macroname as region and pasted the given code
Private Sub Document_BeforeRefresh(Cancel As Boolean)
Dim Var As Variable
Set Var = ThisDocument.Variables("4. Select Sub Region Name or Enter * for all groups")
If Var.Value = "" Then
Var.Value = "*"
End If
End Sub
i could get the results. my requirements were, when user refresh report either prompt combo box should be empty or filled with ALL or *
if user doesnt pass value to the prompt it means select all rows.
Select one, All, Multiple values for the prompt.
(@Select(Prompts\Sub Region Name) in @Prompt(‘4. Select Sub Region Name or Enter * for all groups’,‘a’,‘Prompts\Sub Region Name’,multi,free)) OR
(‘ALL’ in @Prompt(‘4. Select Sub Region Name or Enter * for all groups’,‘a’,‘Prompts\Sub Region Name’,multi,free)) AND @Select(Ech Archive Refsubregion\Region Code)=2
VB macros- to display ALL when ever user refresh report.
Private Sub Document_BeforeRefresh(Cancel As Boolean)
Dim SubRegion, Country, P2P, ASC As Variable
Dim CStatus As Variable
Set SubRegion = ThisDocument.Variables("4. Select Sub Region Name or Enter * for all groups")
Set Country = ThisDocument.Variables("5. Select Country Name or Enter * for all groups")
Set P2P = ThisDocument.Variables("6. Enter Pay To Party Number or * for all Pay To Party Number")
Set ASC = ThisDocument.Variables("7. Enter Asc Number or * for all Asc Number")
Set CStatus = ThisDocument.Variables("3. Select Claim Status")
If SubRegion.Value <> "" Then
SubRegion.Value = "ALL"
Country.Value = "ALL"
P2P.Value = "ALL"
ASC.Value = "ALL"
CStatus.Value = "ALL"
End If
End Sub
thanks every one who has given support.
{moderator edit … added code formatting to improve readability}
Just a point of information. In the above Dim statement, only the last one (ASC) is being dimensioned as Variable. All the others on that line will be the default Variant. By “chaining” them together, you really only save the Dim statement itself, because you need to write it as follows:
Dim SubRegion As Variable, Country As Variable, P2P As Variable, ASC As Variable
With this code can i go for cascading subregion and country?
whenever user select subregion, display countries belongs to selected subregion.
I also have date range in the prompt list.
when user refresh the report, default values must be system date.
Can we change the font of report and also the prompt window. in prompt window itself we can visualize 3 different fonts. how to make uniform font type.