Print Page | Close Window

Hiding grouped data based on specific criteria

Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Report Design
Forum Discription: The best way to design a report and problems you have encountered
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=22062
Printed Date: 29 Apr 2024 at 7:24pm


Topic: Hiding grouped data based on specific criteria
Posted By: gsaunders
Subject: Hiding grouped data based on specific criteria
Date Posted: 19 Aug 2016 at 4:04am
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!!!


Replies:
Posted By: DBlank
Date Posted: 22 Aug 2016 at 3:58am
can you make changes to the stored proc?

Posted By: gsaunders
Date Posted: 23 Aug 2016 at 9:18am
Originally posted by DBlank

can you make changes to the stored proc?


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.

Posted By: DBlank
Date Posted: 23 Aug 2016 at 9:55am
Altering the stored proc does not mean you have to exclude the data, you can just add meta data to the output, such as the group level sum which you can then use to hide or show inside the report without actually excluding it.
Much easier than sub reports and more flexible.

Posted By: gsaunders
Date Posted: 24 Aug 2016 at 6:21am
Originally posted by DBlank

Altering the stored proc does not mean you have to exclude the data, you can just add meta data to the output, such as the group level sum which you can then use to hide or show inside the report without actually excluding it.
Much easier than sub reports and more flexible.

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

Posted By: DBlank
Date Posted: 24 Aug 2016 at 6:33am
I am suggesting something like using a sub query in the sp that sums the cost at the phase and join the result back to each relevant row as the 'TotalPhaseCost' and include this in your data set.
Now you have a quick and easy way to hide or show entire groups based on the value being< or > your run time parameter.


Print Page | Close Window