Report Design
 Crystal Reports Forum : Crystal Reports 9 through 2020 : Report Design
Message Icon Topic: Hiding grouped data based on specific criteria Post Reply Post New Topic
Author Message
gsaunders
Newbie
Newbie


Joined: 09 Apr 2012
Online Status: Offline
Posts: 20
Quote gsaunders Replybullet Topic: Hiding grouped data based on specific criteria
    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!!!


Edited by gsaunders - 19 Aug 2016 at 10:53am
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 22 Aug 2016 at 3:58am
can you make changes to the stored proc?
IP IP Logged
gsaunders
Newbie
Newbie


Joined: 09 Apr 2012
Online Status: Offline
Posts: 20
Quote gsaunders Replybullet 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.
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet 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.
IP IP Logged
gsaunders
Newbie
Newbie


Joined: 09 Apr 2012
Online Status: Offline
Posts: 20
Quote gsaunders Replybullet 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?
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet 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.

Edited by DBlank - 24 Aug 2016 at 6:34am
IP IP Logged
Post Reply Post New Topic
Printable version Printable version

Forum Jump
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum



This page was generated in 0.031 seconds.