I have a problem that I have an imp

[agray123]

I have a problem that I have an imperfect solution for...
Maybe some of you can help.

I am producing a Complement Report that lists positions allocated grouped by Division and then by Dept ID within each division.

I have a query that generated this, plus a COUNT of filled positions from the PS_EMPLOYEES table and simply use a formula to subtract filled from allocated to generate vacant.

The problem is that I need a DISTINCT Count of the Positions filled as some position numbers are shared.

If I change my query to just list positions numbers and then create a distinct count in crystal I run into major problems in creating subtotals.

I have to hide the details and then have a hard time creating a subtotal for vacant positions (as they are already a formula to begin with).

If I can get this solved, then generating a subtotal of positions allocated will not be too bad.

 

[paulosinuk]

If you are saying that you need a distinct sub total I'd be inclined to use a global variable to maintain the count.

In this sort of situation I create a conditional formula to sit in the details section that evaluates the current record against the previous records. In you circumstance I guessing that if the previous position <> current position and the status of the current position is vacant then update count.

You can then display the variable as a running count in your groups.

There are a couple of FAQ's in this forum that can help you with variable declaration.

Thanks

Paulos