UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> How To Get A Group Without Specified Order?    
 
   
amalfaro
post Aug 25 2013, 04:09 PM
Post#1



Posts: 104
Joined: 26-February 09
From: Texas


Hi again,
orgive me if I mess up the description of my situation, but I know even less about Crystal & MS SQL Server than Access or Excel and you've seen how those can turn out.
I have a view in SQL that's set up to filter for jobs with certain prefixes and it's report is in Crystal (which just gets exported to Excel, formatted and posted). Everything was fine until someone realized one of the jobs wasn't like the others. However, since one group handles it and already goes to this report for those metrics, they want to keep it in there. Fine, I'll create a subreport for it and link it in; done that for another one.
The problem is that means the job prefix filter has to stay in the view (well, for efficiency I'd like it to). And for efficiency I'd like to NOT have to create a specified jobname group for each job. The mgrs like to change the names & create new ones without telling me but they always keep the same prefixes. Creating a group for each name means I'll be grumping at them everytime they forget to tell me and we're changing two weeks or more worth of reports.
I found this forum article and tried to modify it. I skipped the bit about the dates because I don't think it's relevant (could be wrong; I do filter by date ranges). I created this:
CODE
if {VIEW.jobName}="TheJobname" then 1 else 0

and zJobName
CODE
if {@nuFormula}=0 then {VIEW.jobName}

and grouped on zJobName. Then I tried two things. A) Inserted into each formula "if nuFormula = 0 then do whatever" and that resulted in zeros for the job I wanted out. B)nothing and that resulted in calculations for the job but no jobname on it's row. A bit better because that can just be ignored. I'll live with it. What I can't live with is that in either scenario the rollup formulas still included the job I wanted out. I really don't know Crystal. I learned while creating these reports so maybe I'm ignoring some helpful feature. I did try inserting summary but that created a second grouping and the numbers were all wrong.
Maybe get it to sum the job and subtract that total from the overall? Or switch the zero & one (shouldn't matter tho, right?)? Something to try tomorrow unless y'all have a better idea? I've 15 mins left today. It's kinda urgent because they'd like it for the new fiscal month next week.
TIA,
ama
Go to the top of the page
 
amalfaro
post Aug 28 2013, 11:53 AM
Post#2



Posts: 104
Joined: 26-February 09
From: Texas


I figured it out with a second google search using the SUM keyword and found: sum should exclude conditional records. So I'm going into each base fields, like connects or hours, and adding
CODE
if NOT(table.jobname in ["myjobName"]) then table.value
and using those new bases for all the formulas including the summaries. I still have the row but now it's all zeroes and the job name is blanked. Not pretty but Goal Achieved and deadline well-met.
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    11th December 2017 - 11:33 PM