I have a pair of dates, DATEOPENED and DATECLOSED, which determine selection criteria (along with several other items).
The DATEOPENED is almost always populated when the case is opened. For purposes of this discussion, presume that DATEOPENED is populated for all cases.
If the DATECLOSED is null, then the case is considered to be open. If the DATECLOSED is not null, then the case is considered to be closed.
For certain reports, the users want to be able choose whether to look at open cases, closed cases, or all cases. The “superuser” is completely comfortable using “is null”, “is not null”, and the other hard-coded conditions which have been built.
However, the other users in her department are neophyte BusObj users, and they are not very computer-literate, bordering even on computer-hostile. So, she would like to put a prompt in her reports which would allow others to make the appropriate choices.
My thoughts are that the prompt should be created at the universe level for consistency and ease of use. Unfortunately, I’m not sure how to do this. Can someone help me, please?
Database is SQLBase (it’s not pretty…).
BusObj is v.5.1.6.
If you have ideas for an Oracle database, give me those and I’ll see if I can translate.
It appears to me that each of the examples draws on only one database column (or object). In what I’m looking for, I have two distinctly different database columns.
If I want all cases, the DATEOPENED is not null and it doesn’t matter what DATECLOSED contains.
If I want only open cases, then DATEOPENED is not null and DATECLOSED is null.
If I want only closed cases, then DATEOPENED is not null and DATECLOSED is not null.
If I build a prompt to look at DATECLOSED, I only want to look at it in one of two states - null or not null (I don’t care want the actual date is, yet).
If I remember right, I can’t put a prompt on a report which is left blank. Maybe what I need is a cascading prompt? The first question asks which criteria (only opened, only closed, all), then it asks for dates?
Another solution to this problem is to have another column in the database to indicate status. This could be populated with a trigger or a batch process (I don’t know how to do this in SQLBase).
You could then build predefined conditions for open and closed and show both in the same query result. I prefer this method because it reduces the overhead in end user queries.
Thanks for the suggestion - unfortunately, it isn’t a viable solution.
The problem is that we’re reporting over data entered through a proprietary software which uses SQLBase. We have no abilities to modify the underlying database. Ugh … I wish we had converted them to Oracle when we had the chance about 3 years ago. Now they’re saying it’s too expensive.
It seems to me that Open cases are those that aren’t closed
So create an object which returns Open if the DateClosed field is null and returns Closed if the DateClosed field is NOT null.
This will provide an LOV with Open & Closed values. To provide the ‘ALL’ value, create a customised LOV by editing the SQL and UNION ‘ALL’ to the other two values.
Then create a Condition object which prompts the user to pick a value from the LOV and include an 'OR ALL in \LOV ’ in the condition definition. See this forum for the exact syntax.
When the user runs the query the prompt values are ‘Open’,‘Closed’,‘ALL’: Choosing ‘Open’ gets all records with a null
DateClosed field. Choosing 'Closed’gets all records with a NOT null DateClosed field. Choosing ‘ALL’… well you get the picture.
This forum provides many, many examples of how to implement the unioned ‘ALL’ and the prompt syntax to cope with ‘ALL’
Of course, IF you don’t actually need to have an ‘ALL’ you could skip the UNION/custom LOV and 'OR’d prompt steps, and just have your Users use an “Inlist” prompt from which they would pick ‘OPEN’ or ‘CLOSED’ or ‘OPEN’;‘CLOSED’ i.e. both values.
(BOB member since 2002-07-05)
(BOB member since 2002-08-15)
(BOB member since 2003-04-22)
(BOB member since 2002-06-20)
