Let me see if I can explain this...

I have a report that is grouped in the following way:

- Company
- Job
- Phase
- Cost Type

Data looks like this:

- Company: 20 (Integer)
- Job: '1000' (String)
- Phase: '1234567.123456' (String)
- Cost Type: 1 (Integer)

The report is calling a stored procedure so the report is not creating a long SQL command with a bunch of conditionals in a where statement.  It is just executing a stored procedure passing in the parameters.

The report has a parameter @JobActivity which prompts the user with:
- Include Jobs with No Activity?

So if the user select 'N' meaning NO then the Group Selection there is a formula as follows:

({?@JobActivity} = 'N' AND Sum ({vrptJCUnitCostCT;1.PerActualCostDetail}, {vrptJCUnitCostCT;1.Job}) <> 0)
OR
({?@JobActivity} = 'Y')

So essentially if the person selects 'N' meaning do NOT include jobs with no activity (meaning no cost incurred) then if the cost summed up at the Job level <> 0 it will show it and if it is 0 it will not.  And of course if they say to include it with a 'Y' it will show it.

We have an additional criteria that I am not sure how to handle.

The user also has a parameter asking if they want to ONLY show phases with a minimum cost of X.  So these 2 parameters are:

- @UseMinPhaseCost : This is a Y or N
- @MinPhaseCost : This is the dollar value they only want to report on at the phase level.

So if they select Y and have a minimum phase cost of $10,000 the report will NOT display phases that do NOT have a $10,000 or greater value.

Here is the problem.  Since this is a stored procedure which currently are not taking these two values the report itself is handling the showing or hiding at the PHASE level.  BUT what we need to happen is have the JOB level hidden if there were NO Phases with a $10,000 or higher value.

It is possible there are X number of phases that do exist with values less that $10,000 and the report simply hides those phases in a suppress formula at the phase section level.

So a job may have 10 phases with values less that $10,000 so they are not visible and have NO phases with $10,000 or greater then we do NOT want the Job to display.  But how to make this happen.  Technically the job does have data (hidden at section level) so the group level formula earlier doesn't solve this since the summed cost at the job level is actually a value.  So something else has to be incorporated to catch this.

I was thinking of some running total (count) that kept track of the number of phases meeting the criteria and if that count is greater than 0 for a job then to hide the entire job.  I did try using a running total, but that running total was not visible in the group selection formula editor, so I am guessing you can't use running totals there.  The only way I can see putting something in the group selection would be a formula or variable that is fully calculated and then used in the group selection. Just not sure how to do this or if it is possible.

I know the other option is to alter my stored procedure to accept those 2 parameters and have it filter out the data completely.  But if they decide they still want to see summary totals at the bottom at a later time doing it via the SP to completely preventing the data from coming to the report may not be the easy answer.

I hope this makes sense.

Thanks in advance for your help!!!


Edited by gsaunders - 19 Aug 2016 at 10:53am

As noted in my original post... I could change the stored procedure and pass those in as parameters and have the data completely restricted, but there may be a time where they want to have the summary data (totals) shown at the bottom of the report, but hide the jobs... meaning we would still need the data so the report could report summary totals at bottom of report.  BUT I guess I could make the summary a subreport.

Still would like to know how this could be done with the report itself and without altering the stored procedure.

Could you explain this with a little more detail just to make sure I am following you?