How to Run Report for All Departments?

[zeekay]

I have Profit & Loss Report in Crystal. I have a parameter field for department. I can select one department at a time to run the report for that department or I can select 9999 department to get a consolidated Profit & Loss report for all departments.

I have 100 departments. I want to run the report for individual departments which means that I will need to run the report 100 times. Is there any way that I run the report just once and get the output for 100 departments?

Thanks,
Zeekay

 

[jdemmi]

Unless I am not following you it sounds like your 9999 value is running for all departments, but it's "concatenating" the data in a single report.

If that is the case, it seems to me like you could make use of the "NEW PAGE AFTER" and "NEW PAGE BEFORE" options in the section expert and the "REPEAT GROUP HEADER ON EACH PAGE" option to force the report output to be formatted in a manner similar to individual report executions for each department.

-- Jason
"It's Just Ones and Zeros

 

[IdoMillet]

Group the report by department and remove the parameter.

If you need to generate a separate export file or email for each department, this is called "bursting." At least one of the 3rd-party Crystal report schedulers listed at

http://www.kenhamady.com/bookmarks.html

provides report bursting functionality.

hth,
- Ido

view, email, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[jdemmi]

Yes, I assumed the report was already grouped (since the report already provides the ability to run for 1 or All departments).

But Ido, no need to remove the parameter, I think that zeekay still wants the ability to run for 1 OR for all.....

Yes?

-- Jason
"It's Just Ones and Zeros

 

[zeekay]

My report is based on SQL Command. In WHERE clause of my SQL, I am using DECODE function as follows:

WHERE deptno = DECODE('{?P_DEPTNO}','9999', deptno, '{?P_DEPTNO}')

In this way, if I select any individual department, I get result for that. But if I select 9999 then it results the consolidated numbers (summary report) for all departments.
I have not grouped my report on department, if I do so, then I won't be able to get summary report if I select department 9999.

I can get the report for individual departments by selecting departments one by one. But in this
way, I will need to run the report 100 times for running it for 100 departments.
I want to get the report for 100 individual departments without running the report 100 times.

If there is no other way then I can create another version of the report and add grouping at department level. So that, if I select department 9999, it will give me department wise report instead of summary report.

Any comments please?

 

[jdemmi]

You don't have to use the SQL command to handle what you want.

You can:

1. Use the select expert with your "9999 = all departments" approach: if {?P_DEPTNO} = "9999" then deptno like '*' else deptno = {?P_DEPTNO}

OR

2. Use the select expert like this:

(If (not isnull({?P_DEPTNO}))
then
deptno = {?P_DEPTNO}
else True)

You can then group the report on deptno


-- Jason
"It's Just Ones and Zeros

 

[lbass]

I think you should stick with the command, and have the user choose '9999' if they want results for all departments. Add a second parameter {?GrouporNot} in the main report with options: 'Group' or 'Do Not Group'. Then create a formula like this:

if {?GrouporNot} = "Do Not Group" then
"" else
{table.dept}

Insert a group on this. Format the group sections to "suppress blank section." Insert summaries at the group level and format to new page after on the group footer with a conditional formula:

not onlastrecord

-LB