Alert Condition Help 2

[dsmbwoy]

Looking for some help with formulating my formula please.

My report initially displays 3 fields a start date, end date and a distinct list of departments in the database. If the user selects a date range that has no data for the selected department then no results will be retrieved. What I am trying to do is display a popup to the user advising them that the selected department has no results within the date range selected.

I am a little stuck with my formula. So far my formula looks something like this

{?SelectedDepartments} not in groupName({report.Departments})

The first parameter would contain the users selected departments and the group is the returned results from the database. So for example if I selected HR, IT, Finance in my parameters for a selected period of time and only HR and IT had results then I want to code Crystal to display a popup saying no results for department Finance.

 

[lbass]

I don't think you can use the alerts feature for this (at least I can't think of a way), but you could use the following two formulas, which would result in a report footer display of the missing departments:

//{@accum} to be placed in the detail section and suppressed:
whileprintingrecords;
stringvar dept;
if instr(dept,{report.departments}) = 0 then
dept := dept + {report.departments}+", ";

//{@display} to be placed in the report footer:
whileprintingrecords;
stringvar dept;
stringvar missing;
numbervar i;
for i := 1 to ubound({?dept}) do(
if not({?dept} in dept) then
missing := missing + {?dept} + ", "
);
if len(missing) > 2 then
"No data for : " +
left(missing,len(missing)-2)

-LB

 

[IanWaterman]

You need a table with a list of depts, join the rest of your tables as a left outer from this table.

Change you dept group to use dept frm this new table.

YOu will then get a null for the dept with no results.

Split details into two sections
Display data in top section add conditional suppression
isnull(datafield)

In lower sectio add text

no results within the date range selected
add conditional suppression
not(isnull(datafield))

Ian

 

[dsmbwoy]

Thanks for the suggestions guys. I'll try playing around with both options.

 

[dsmbwoy]

LBASS. Can you please help me with validating the code that you suggested below. I get error 'A number, currency amount, booean, date, time, date-time, or string is expected here.' and the 'to' in the 'for i := to ubound({?Department})' line is highlighted.


whileprintingrecords;
stringvar dept;
stringvar missing;
numbervar i;

for i := to ubound({?Department})
do(
if not({?Department} in GroupName ({Department_report.DPT}) then
missisng := missing + {?Department} + ", "
);
if len(missing) > 2 then
"No Data for : " + left(missing, len(missing)-2)


Thanks

 

[lbass]

You didn't follow my suggestion exactly. Using your fields, the formulas should be the following--exactly:

//{@accum} to be placed in the detail section and suppressed:
whileprintingrecords;
stringvar dept;
if instr(dept,{Department_report.DPT}) = 0 then
dept := dept + {Department_report.DPT}+", ";

//{@display} to be placed in the report footer:
whileprintingrecords;
stringvar dept;
stringvar missing;
numbervar i;
for i := 1 to ubound({?Department}) do(
if not({?Department} in dept) then
missing := missing + {?Department} + ", "
);
if len(missing) > 2 then
"No data for : " +
left(missing,len(missing)-2)

I have virtually never had to use groupname() for any formula. Just use the field you are grouping on itself.

-LB

 

[dsmbwoy]

Thanks LBASS. It works now.

The function currently creates one long string of departments that contain no data. How can I start a new line for each department?

For example:
Rather than
No data for: IT, HR, Finance etc.

I'd like to display the departments in a column like below.

No data for:
IT
HR
Finance
.
.
.


Thanks

 

[dsmbwoy]

I'd also like to place the list of values in a scrollable list since there can be hundreds of values that there can potentially be no results for. Is this possible in Crystal XI?


Thanks

 

[dsmbwoy]

I found the answer to my first question. chr(10) starts a new line. My modified code is listed below for anyone else that needs help with this.

whileprintingrecords;
stringvar dept;
stringvar missing;
numbervar i;
for i := 1 to ubound({?Department}) do(
if not({?Department} in dept) then
missing := missing + {?Department} + ", " & chr(10)

);
if len(missing) > 2 then

left(missing,len(missing)-2)

No just need help with formatting the list to columns since I can possibly have a long list of departments. I checked off the can grow function but that just continues the list on multiple pages. What I am looking for is to continue the running list on multiple columns and only start a new page once the last column is fully populated. See example below.

Missing departments:

test test test test test
test test test test test
test test test test test
. . . . .
. . . . .
. . . . .
. . . . .

Is this possible?

 

[lbass]

First, you don't want to leave in the comma. If you really want a display like this, you should instead change the formula to the following:

whileprintingrecords;
stringvar dept;
stringvar missing;
numbervar i;
for i := 1 to ubound({?Department}) do(
if not({?Department} in dept) then
missing := missing + {?Department} + space(25-len({?Department})));//replace 25 with a number larger than the
//length of your longest department name plus some spaces
if len(missing) > 0 then
"Missing Departments: " + chr(13)+chr(13)+
missing

Drag the width of the formula to be the width of the page. Format the formula to 'can grow' and ALSO be sure to change the font to a nonproportional font like Courier New to get the correct alignment.

-LB

 

[dsmbwoy]

Thanks LBASS but I get an error 'The number of copies of the string is too large or not an integer.'

Also is there a way to restrict the height of the field on the report by any chance with the 'can grow' feature on? I only want the field to grow 3/4 of the page for example and then continue onto the next page. I also noticed that once the report continues onto the next page it starts right at the top. Can this be adjusted so that the reports continues at a set area of the report?


Thanks

 

[lbass]

Where are you placing each formula? Which formula is giving you this error? Why are you asking teh later questions, which would only arise if you actually got the formula to work!

-LB

 

[dsmbwoy]

I placed the first part of the formula in the detail and the second part of the formula in a report header.


Thanks

 

[lbass]

There are two separate formulas. {@accum} goes in the detail section, and {@display} goes in the report footer. It will not work properly in the report header. Also, you didn't answer my questions.

-LB

 

[dsmbwoy]

My mistake. The first part of the formula was placed in the detail and the second part was placed in the report footer.

The formulas that you provided worked up until I modified the code to format the results in columns. Once I formatted the code to display the results in columns I got the error 'The number of copies of the string is too large or not an integer.' I asked if it was possible to control where continued data starts on the next page because your formula did indeed work at a point. I just started having problems once I changed the code to format the results into columns.


Thanks,

DB

 

[lbass]

How did you format to go into columns? Did you use the method I suggested? If so, please show the exact formula you used.

-LB