Solved

Crystal Reports record selection formula issue

Posted on 2015-02-13
34
281 Views
Last Modified: 2015-02-17
I have a crystal report to pull efficiency data from my database.  I have it working to display data for all employees in the database.  I added a Parameter Field to allow multiple group selections.  In the database expert I added multiple commands where I have a select query to get employee numbers for employees on particular shifts in certain area's.  I configured my report formula for the first selection, tested it and it worked fine.  When I tried to add the option for the second selection my  report goes blank.  Below is the working code I have.

{LB_DC.ATP} = 2 and
{LB_DC.ADCCOUNT} <> 0 and
{LB_DC.STATUS} <> 300 and
{OR_ORDER.ARTNO} <> 'P04' and {OR_ORDER.ARTNO} <> 'P05' and
{LB_DC.MSTIME} in {?Start Date} to {?End Date}
and
if {?Select Work Area}="Cutting Products 1st" then 
{LB_DC.PERSNO} = {CuttingProductsFirst.PERSNO}

Open in new window


When I try to add the 2nd shift option everything breaks.  I have tried putting in 'and', 'or', 'else if' in there but as soon as I have more than one if statement in the formula I see no data.  I have 4 departments with 3 shifts each and I need to be able to pull data for 1 to all of the area's.
if {?Select Work Area}="Cutting Products 2nd" then
{LB_DC.PERSNO} = {CuttingProductsSecond.PERSNO}

Open in new window


Any idea what I need to do to get this working?
0
Comment
Question by:dustock
  • 18
  • 8
  • 8
34 Comments
 
LVL 100

Expert Comment

by:mlmcc
Comment Utility
I think the issue is if the IF test is FALSE you don't give a value.  In the first case (checking 1 shift) it is not a problem since you don't want that record.  When you and a test for the second shift it need to be TRUE from the first or you need to use and OR.

Try one of these

{LB_DC.ATP} = 2 and
{LB_DC.ADCCOUNT} <> 0 and
{LB_DC.STATUS} <> 300 and
{OR_ORDER.ARTNO} <> 'P04' and {OR_ORDER.ARTNO} <> 'P05' and
{LB_DC.MSTIME} in {?Start Date} to {?End Date}
and
(
if {?Select Work Area}="Cutting Products 1st" then 
     {LB_DC.PERSNO} = {CuttingProductsFirst.PERSNO}
Else 
    TRUE
)
AND 
(
if {?Select Work Area}="Cutting Products 2nd" then
     {LB_DC.PERSNO} = {CuttingProductsSecond.PERSNO}
else
    TRUE
)

Open in new window


To use an OR try it this way
{LB_DC.ATP} = 2 and
{LB_DC.ADCCOUNT} <> 0 and
{LB_DC.STATUS} <> 300 and
{OR_ORDER.ARTNO} <> 'P04' and {OR_ORDER.ARTNO} <> 'P05' and
{LB_DC.MSTIME} in {?Start Date} to {?End Date}
and
(
  (
    if   {?Select Work Area}="Cutting Products 1st" then 
       {LB_DC.PERSNO} = {CuttingProductsFirst.PERSNO}
    Else
       FALSE
  )
OR
   (
      if {?Select Work Area}="Cutting Products 2nd" then
         {LB_DC.PERSNO} = {CuttingProductsSecond.PERSNO}
     else
        False
   )
)

Open in new window


You could also do this as
{LB_DC.ATP} = 2 and
{LB_DC.ADCCOUNT} <> 0 and
{LB_DC.STATUS} <> 300 and
{OR_ORDER.ARTNO} <> 'P04' and {OR_ORDER.ARTNO} <> 'P05' and
{LB_DC.MSTIME} in {?Start Date} to {?End Date}
and
if {?Select Work Area}="Cutting Products 1st" then 
    {LB_DC.PERSNO} = {CuttingProductsFirst.PERSNO}
Else  if {?Select Work Area}="Cutting Products 2nd" then
    {LB_DC.PERSNO} = {CuttingProductsSecond.PERSNO}
else
    False

Open in new window


mlmcc
0
 
LVL 9

Author Comment

by:dustock
Comment Utility
mlmcc

Thanks for the response.  I tried all 3 of your suggestions and unfortunately they don't work.
I modified my formula to match all 3 of your suggestions and then when I preview the report I tried selecting just the first item in the list, just the second item in the list and I tried both.  None of them deliver results

Enter Values
I can see when I have both selected that both 1st and 2nd shift show a check mark next to the PERSNO
DB Fields
In the end I want people to be able to select as many options as possible from the work area selection list and get data from that area.  When I comment out everything after the date selections I get all the data.  And as before if I only put the if statement in for Cutting Products 1st I get that data.  If I comment out cutting products first and have it look for cutting products second I get that data.  Once there are multiple if statements it just shows a blank report.
0
 
LVL 9

Author Comment

by:dustock
Comment Utility
We have other reports that hard code values for machine reports (below), and those work just fine.  My goal is to write this report and not hard code any data so that as we change employees shift and work area in the database the report continues to work without having to search and modify hard coded values.

if {?Select Work Area(s)}="Cutting Products" then
{WP_MA1.NAME} = "00288"
or
{WP_MA1.NAME} = "01091"
or
{WP_MA1.NAME} = "01092"

Open in new window

0
 
LVL 100

Expert Comment

by:mlmcc
Comment Utility
ARe those in different tables or queries?

mlmcc
0
 
LVL 100

Expert Comment

by:mlmcc
Comment Utility
If so can you show the joins?

mlmcc
0
 
LVL 9

Author Comment

by:dustock
Comment Utility
The second message I sent was from another report.  Just trying to show where I got the idea from.

There are 3 tables I am pulling the data from, and then I added 12 additional 'commands' via the 'Add Command' option in the database expert.  And I linked each of those 12 commands back to the table where the same field is.  I would have linked the employee's table directly to the 1 table, but the developer of the application we use has the persno field as Decimal and nvarchar in the 2 tables so I could not link them.  Hope that's what your asking.

Linked Tables
0
 
LVL 100

Assisted Solution

by:mlmcc
mlmcc earned 334 total points
Comment Utility
Change the joins to the 12 tables to LEFT OUTER

Right click the join (line)
Click LINK OPTIONS
Select LEFT OUTER

mlmcc
0
 
LVL 9

Author Comment

by:dustock
Comment Utility
As soon as I leave the bar I'll try that!
0
 
LVL 9

Author Comment

by:dustock
Comment Utility
Thanks again for getting back to me.  I changed the 2 selections I am testing with to a Left Outer.  If I try Formula 1 below I get about 7000 records that completely ignore the date ranges.  If I use formula number 2 (the second one you gave me) I only get the data for 1st shift, the 2nd shift data doesn't appear.  So I am a little closer!  Your other formula's either give me First shift data, or no data.

//Formula 1
{LB_DC.ATP} = 2 and
{LB_DC.ADCCOUNT} <> 0 and
{LB_DC.STATUS} <> 300 and
{OR_ORDER.ARTNO} <> 'P04' and {OR_ORDER.ARTNO} <> 'P05' and
{LB_DC.MSTIME} in {?Start Date} to {?End Date}

and


  (
    if   {?Select Work Area}="Cutting Products 1st" then 
       {LB_DC.PERSNO} = {CuttingProductsFirst.PERSNO}
    Else
       TRUE
  )
OR
   (
      if {?Select Work Area}="Cutting Products 2nd" then
         {LB_DC.PERSNO} = {CuttingProductsSecond.PERSNO}
     else
        TRUE
   )

Open in new window


//Formula 2
{LB_DC.ATP} = 2 and
{LB_DC.ADCCOUNT} <> 0 and
{LB_DC.STATUS} <> 300 and
{OR_ORDER.ARTNO} <> 'P04' and {OR_ORDER.ARTNO} <> 'P05' and
{LB_DC.MSTIME} in {?Start Date} to {?End Date}

and


[FIRSTNAME]
      ,[SURNAME]

Open in new window

0
 
LVL 100

Expert Comment

by:mlmcc
Comment Utility
CHange the TRUEs in the first formula to FALSE

mlmcc
0
 
LVL 9

Author Comment

by:dustock
Comment Utility
Just tried it.  It takes forever to run and ignores the date range.  I stopped it after a minute because I was up to 7000 records for 1 day when I should have around 200 for the day between the 2 shifts.
0
 
LVL 34

Assisted Solution

by:James0628
James0628 earned 166 total points
Comment Utility
Some questions and observations:

 First thing:
 You need another set of () in the first formula in your last post.  In simplified terms, you have:
test1 and test2 and ...
and
test8
or
test9

 AND normally takes precedence over OR, so that equates to:
(
test1 and test2 and ...
and
test8
)
or
test9

 You need () around the OR'd conditions:

//Formula 1
{LB_DC.ATP} = 2 and
{LB_DC.ADCCOUNT} <> 0 and
{LB_DC.STATUS} <> 300 and
{OR_ORDER.ARTNO} <> 'P04' and {OR_ORDER.ARTNO} <> 'P05' and
{LB_DC.MSTIME} in {?Start Date} to {?End Date}

and
(
  (
    if   {?Select Work Area}="Cutting Products 1st" then 
       {LB_DC.PERSNO} = {CuttingProductsFirst.PERSNO}
    Else
       TRUE
  )
OR
   (
      if {?Select Work Area}="Cutting Products 2nd" then
         {LB_DC.PERSNO} = {CuttingProductsSecond.PERSNO}
     else
        TRUE
   )
)

Open in new window


 The rest of the logic may or may not be correct, but you need those ().


 It appears that you're allowing the user to select more than one value for {?Select Work Area}, so they could select 1st, 2nd _and_ 3rd, or any combination of the three.  Correct?


 I assume that LB_DC.PERSNO is linked to PERSNO in the First, Second and Third tables in the link expert?  Your screenshot doesn't show which LB_DC field you're using.

 Assuming that the link is from LB_DC.PERSNO ...

 Will every LB_DC.PERSNO _always_ have a corresponding record in all of the First, Second and Third tables?  If not, then, as already mentioned, you would need Outer Joins for the First/Second/Third tables that might not have matching PERSNO records.  But if there will always be a record for each PERSNO in each First/Second/Third table, Inner Joins are fine.


 In your screenshot showing the links, you have the links from LB_DC to the First/Second/Third tables going in the "wrong" direction.  You have them going from the other tables to LB_DC, but it should be the other way around.  And if you changed those links to Left Outer, the Outer condition would be working in the wrong direction too.  You can right-click on each of those links and reverse it, so that they go from LB_DC to the First/Second/Third tables.


  What are you really trying to select with your record selection formula?

 What you're basically saying is if {?Select Work Area} is "Cutting Products 1st", then look for a matching CuttingProductsFirst.PERSNO; or if it's "Cutting Products 2nd", then look for a matching CuttingProductsSecond.PERSNO; and so on.

 But if you're using LB_DC.PERSNO for the link, then PERSNO has to match, or be null (if there is no matching record, and you use an Outer Join from LB_DC to the other table).  Assuming that you're just trying to make sure that there was a matching record in the corresponding table (CuttingProductsFirst, or CuttingProductsSecond, or CuttingProductsThird), then your test would probably work, but when there is no matching record, the First/Second/Third field will default to being null, and nulls can cause problems in CR.  It would probably be safer to test the First/Second/Third field to see if it's null.  For example:

(
    if   {?Select Work Area}="Cutting Products 1st" then
       not IsNull ({CuttingProductsFirst.PERSNO})
    Else
       TRUE
)



 Also, just from a logical/structural POV, you don't need the if-else statements.  For example, you could replace the tests above with:

({?Select Work Area}="Cutting Products 1st" and not IsNull ({CuttingProductsFirst.PERSNO}))


 But I'm still not sure what you're really trying to select.  You're not really selecting records in the usual sense.  Instead of something like {LB_DC.PERSNO} = {?some parameter}, you're linking to several different sets of tables, and checking a linked field in each of those tables.  Every record in the report will include data from all of the First, Second and Third tables, if there is any.  Presumably the only thing the record selection formula might do is eliminate some records because you're looking for First, Second or Third, and the corresponding table(s) doesn't have a matching PERSNO.  But the report is still going to have to pick which fields it actually wants to use.  For example, if the user selects "Cutting Products 1st", only use the fields in CuttingProductsFirst, and ignore CuttingProductsSecond and CuttingProductsThird.

 Whether or not it's even worth the trouble to try to select records will depend on your data.  In particular, if every LB_DC.PERSNO always has records in all 3 sets of tables, those tests in the record selection formula won't eliminate anything.  And if most LB_DC.PERSNO values have matching records in all 3 sets of tables, it may not be worth the trouble trying to eliminate the relative few that don't, since the report should be ignoring the undesired tables anyway.

 James
0
 
LVL 9

Author Comment

by:dustock
Comment Utility
James,

Thanks for the response and explanation!

What I am looking for is to have a report that can pull efficiency data for a selected date range and a work area/shift.  So one person might want to just look at Cutting Product's 1st, while another might want to look at Cutting Products 1st, 2nd and 3rd.  I also have 3 other work area's with 3 shifts each so there will be times were we want to run the report will all 12 conditions.  My original plan was to link the employee's table to the LB_DC table, but the developers of the software I am reporting from made PERSNO a nvarchar in one table and a decimal in another table and I was not able to link those two.  So my next idea was to go into the database expert and make 12 different 'commands' for each set of work area/shift data (we move people around the shop floor so I always want supervisors to have the most up to date data and I don't want to have to  hard code PERSNO values for each work area/shift).  Each of the 12 'commands' relates back to LB_DC.PERSNO.

For my testing I am only trying to grab Cutting Products First and Second shift to make sure this is working.

I updated my formula to use the parenthesis as you suggested and I also reversed the direction of my links.  This did not help (Only 1st shift shows up).  There might be a few PERSNO's that do not have a corresponding record in the LB_DC table.  If it is a problem, I can easily change their record to not show up in my queries.

I tried your suggestion to eliminate the if-else blocks, and it's a lot closer you what I am looking for (I'm getting first and second shift).  My formula now looks like this.

{LB_DC.ATP} = 2 and
{LB_DC.ADCCOUNT} <> 0 and
{LB_DC.STATUS} <> 300 and
{OR_ORDER.ARTNO} <> 'P04' and {OR_ORDER.ARTNO} <> 'P05' and
{LB_DC.MSTIME} in {?Start Date} to {?End Date}

and
({?Select Work Area}="Cutting Products 1st" and not IsNull ({CuttingProductsFirst.PERSNO}))
OR
({?Select Work Area}="Cutting Products 2nd" and not IsNull ({CuttingProductsSecond.PERSNO}))

Open in new window


Two issues, the formula seems to take a long time to run, and my data for first shift is fine, but the second shift data is ignoring the date range.  I even tried to run the report to just look at second shift and it is still ignoring the date range.  The date range I have set is February 9th and February 10th.  I should see 70 records for 1st shift and 30 records for second.  I do get my 70 records for first shift, but I get 14598 records for second shift.  Hopefully this is enough information to go off of.  I feel like I am much closer to getting this working the way I want!
0
 
LVL 100

Accepted Solution

by:
mlmcc earned 334 total points
Comment Utility
You need ( ) around the FULL OR clause.  AND and OR are like multiplication and addition for precedence.  The AND s are done first then the OR so in your case the first 8 lines are evaluated then OR'd to the last line

{LB_DC.ATP} = 2 and
{LB_DC.ADCCOUNT} <> 0 and
{LB_DC.STATUS} <> 300 and
{OR_ORDER.ARTNO} <> 'P04' and {OR_ORDER.ARTNO} <> 'P05' and
{LB_DC.MSTIME} in {?Start Date} to {?End Date}

and
(
({?Select Work Area}="Cutting Products 1st" and not IsNull ({CuttingProductsFirst.PERSNO}))
OR
({?Select Work Area}="Cutting Products 2nd" and not IsNull ({CuttingProductsSecond.PERSNO}))
)

Open in new window


mlmcc
0
 
LVL 9

Author Comment

by:dustock
Comment Utility
Ah, yes, that makes sense!
0
 
LVL 9

Author Comment

by:dustock
Comment Utility
Thanks to both James and mlmcc for the help on this!
0
 
LVL 34

Expert Comment

by:James0628
Comment Utility
You're welcome.  Glad I could help.

 Is the report still slow?  It wouldn't surprise me.

 One possible issue is that you're linking to 3 different sets of files, which could be bringing in a lot of extra data that you're not using (unless the user actually asks for all 3 sets).

 But the bigger issue may be Commands.  You said that you created 12 different Commands.  I haven't used them much, but I think if you're using multiple Commands in the report, CR may be executing each one separately and then linking the results "locally", rather than all of the work being done on the server.  If so, it would probably be _much_ more efficient if you could combine all of those queries in one Command.

 James
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 9

Author Comment

by:dustock
Comment Utility
James,

The report actually run's a lot quicker now.  But I will keep this in mind as I continue to use crystal.  Aside from a report I built 5 years ago with the Visual Studio Crystal Reports add-on, this is the first time I have really gotten into Crystal so there is a lot of trial and error.  I might need to invest in a class on Crystal if my company wants me to continue to build reports.
0
 
LVL 34

Expert Comment

by:James0628
Comment Utility
Glad to hear that it's running OK.

 James
0
 
LVL 9

Author Comment

by:dustock
Comment Utility
James,

I may have spoke to soon.  I had everything working for 2 of the area's on all 3 shifts.  Keeping the same format I added the last two area's with their shift.  Below is the code that works.  If I un-comment anymore lines the report returns 0 results no matter what selection I make,

{LB_DC.ATP} = 2 and
{LB_DC.ADCCOUNT} <> 0 and
{LB_DC.STATUS} <> 300 and
{OR_ORDER.ARTNO} <> 'P04' and {OR_ORDER.ARTNO} <> 'P05' and
{LB_DC.MSTIME} in {?Start Date} to {?End Date}

and
(
({?Select Work Area}="Cutting Products 1st" and not IsNull ({CuttingProductsFirst.PERSNO}))
OR
({?Select Work Area}="Cutting Products 2nd" and not IsNull ({CuttingProductsSecond.PERSNO}))
OR
({?Select Work Area}="Cutting Products 3rd" and not IsNull ({CuttingProductsThird.PERSNO}))
OR
({?Select Work Area}="Machine Components 1st" and not IsNull ({MachineComponentsFirst.PERSNO}))
OR
({?Select Work Area}="Machine Components 2nd" and not IsNull ({MachineComponentsSecond.PERSNO}))
OR
({?Select Work Area}="Machine Components 3rd" and not IsNull ({MachineComponentsThird.PERSNO}))
OR
({?Select Work Area}="Metal Fabrication 1st" and not IsNull ({MetalFabFirst.PERSNO}))
OR
({?Select Work Area}="Metal Fabrication 2nd" and not IsNull ({MetalFabSecond.PERSNO}))
//OR
//({?Select Work Area}="Metal Fabrication 3rd" and not IsNull ({MetalFabThird.PERSNO}))
//OR
//({?Select Work Area}="Tool Stamping 1st" and not IsNull ({ToolStampingFirst.PERSNO}))
//OR
//({?Select Work Area}="Tool Stamping 2nd" and not IsNull ({ToolStampingSecond.PERSNO}))
//OR
//({?Select Work Area}="Tool Stamping 3rd" and not IsNull ({ToolStampingThird.PERSNO}))
)

Open in new window


Is this a limitation of Crystal Reports?
0
 
LVL 9

Author Comment

by:dustock
Comment Utility
I just tried to un-comment the the lines and I ran it to select all the data (I hadn't tested that before) and I get records for 6 people.  If I comment out the same lines as above and refresh the data I get records for 30 people.  So it works sometimes...
0
 
LVL 34

Expert Comment

by:James0628
Comment Utility
I don't see anything wrong with the code, so I guess it could be some kind of limitation.  I would uncomment the lines, try a selection that does not work, and then go to Database > "Show SQL Query" and see what it says.  If you're using Commands, I really don't know what you'll see, but look for your record selection formula and, if you can find it, see if it looks OK.

 I don't know what's in your Commands or how they're all related, but my impression is that your record selection formula is separate from them.  You might be able to incorporate the parameters into the Commands, which might help.  If your {?Select Work Area} parameter still allows multiple values, which version of CR are you using?  I think they added support for multi-value parameters in Commands in the more recent versions.

 James
0
 
LVL 100

Expert Comment

by:mlmcc
Comment Utility
WHat version of Crystal?

I recall running into so length limitations on queries but that was in CR8

mlmcc
0
 
LVL 9

Author Comment

by:dustock
Comment Utility
I am using Crystal Reports 2013.  I did the show SQL and I can see the selection and then it lists external joins for each of the commands I created, whether or not I selected them in Crystal.
0
 
LVL 9

Author Comment

by:dustock
Comment Utility
The whole reason I went to commands was to get around the LB_DC.PERSNO being a decimal while the PE_WORK.PERSNO is an nvarchar.  Is there a way I can link those two together and then go back to using if statements or something along those lines?  The only thing the command does is selects the PE_WORK.PERSNO where the department equals one of the 4 work area's and were shift is equal to the shifts I want.  Or do you think one command selecting all of the area's and shifts would work then I can use a formula to select only the data I want?
0
 
LVL 34

Expert Comment

by:James0628
Comment Utility
So, when you look at "Show SQL Query", the record selection formula looks OK, but the report isn't showing the right data?  I never used CR Commands in my reports, much less more than 1 in the same report, but I guess the problem could have something to do with how CR is connecting/processing them.  I'm guessing that the external links are just how CR connects them (I suspect that it's running each Command separately, getting the output from them, and then trying to link all of the results).

 When you say "and then it lists external joins for each of the commands I created, whether or not I selected them in Crystal", what do you mean by "whether or not I selected them"?  Do you have a separate Command for each of your *First, *Second and *Third tables, and you're talking about whether or not you selected those in the {?Select Work Area} parameter?  If so, it would probably make sense for CR to include all of them in the query.  CR presumably doesn't know that your Commands work like that, so it just runs them all, links the results, and then applies your record selection formula to filter the data.

 All of your Commands pull data from the same db, right?  You could probably combine all of those in one Command, and if the report is also reading some other tables, I'd try to include those in the same Command, so that everything is in one place, instead of trying to link several different queries.  That should be much more efficient and "cleaner".  I can't really say much more without knowing what's actually in the Commands, etc.

 James
0
 
LVL 9

Author Comment

by:dustock
Comment Utility
The SQL looked like it was trying to grab everything, and it wouldn't run in SSMS without some modification.  I decided to scrap this original report, and start over.

For the new concept I created 1 command and pulled in all the columns I need from the 4 tables, and did all the necessary joins (including the 1 that Crystal would not let me do).  Then I went back to my original idea of using if statements to sift through the data.  The report takes a little longer to run because its pulling all the records for a date range and then only displaying the ones I want, but it's giving me the data I want.  Below is how my report formula is now.  and it all works exactly as I would expect.

{Efficieny.MSTIME} in {?Start Date} to {?End Date}
and
(
(if{?Select Work Area} = "Cutting Products 1st" then
{Efficieny.MODEM} = "Cutting Products" and {Efficieny.FAX} = "1"
else
false)
OR
(if{?Select Work Area} = "Cutting Products 2nd" then
{Efficieny.MODEM} = "Cutting Products" and {Efficieny.FAX} = "2"
else
false)
OR
(if{?Select Work Area} = "Cutting Products 3rd" then
{Efficieny.MODEM} = "Cutting Products" and {Efficieny.FAX} = "3"
else
false)
OR
(if{?Select Work Area} = "Machine Components 1st" then
{Efficieny.MODEM} = "Machine Components" and {Efficieny.FAX} = "1"
else
false)
OR
(if{?Select Work Area} = "Machine Components 2nd" then
{Efficieny.MODEM} = "Machine Components" and {Efficieny.FAX} = "2"
else
false)
OR
(if{?Select Work Area} = "Machine Components 3rd" then
{Efficieny.MODEM} = "Machine Components" and {Efficieny.FAX} = "3"
else
false)
OR
(if{?Select Work Area} = "Metal Fabrication 1st" then
{Efficieny.MODEM} = "Metal Fabrication" and {Efficieny.FAX} = "1"
else
false)
OR
(if{?Select Work Area} = "Metal Fabrication 2nd" then
{Efficieny.MODEM} = "Metal Fabrication" and {Efficieny.FAX} = "2"
else
false)
OR
(if{?Select Work Area} = "Metal Fabrication 3rd" then
{Efficieny.MODEM} = "Metal Fabrication" and {Efficieny.FAX} = "3"
else
false)
OR
(if{?Select Work Area} = "Tool Stamping 1st" then
{Efficieny.MODEM} = "Tool Stamping" and {Efficieny.FAX} = "1"
else
false)
OR
(if{?Select Work Area} = "Tool Stamping 2nd" then
{Efficieny.MODEM} = "Tool Stamping" and {Efficieny.FAX} = "2"
else
false)
OR
(if{?Select Work Area} = "Tool Stamping 3rd" then
{Efficieny.MODEM} = "Tool Stamping" and {Efficieny.FAX} = "3"
else
false)
)

Open in new window


I'm happy its working, but not happy with the approach.  When I am in the 'Add Command' menu I see the parameters listed.  Is that something I could utilize and add parameters for Modem and Fax?  I know its odd, but Modem is the department and Fax is the shift.  So something like:

Select * from table 1....
From...
WHERE MODEM in (@Dept) AND FAX in (@Shift)

Or something along those lines?  I would lose the ability to do something like 'Cutting Products 1st shift' and 'Metal Fabrication 3rd shift', but I can't think of a reason I would want to do that in the first place.
0
 
LVL 100

Expert Comment

by:mlmcc
Comment Utility
You certainly could do that.

As you say so long as the MODEM (department) is a single value then you can choose multiple shifts.
By the same token you could have multiple departments with a single shift or so long as you understand that if you choose multiple departments and shifts that you will get all the shifts for all the departments that will also work.

A little more complicated for the user to ensure they get the data they want but easier for the report

mlmcc
0
 
LVL 9

Author Comment

by:dustock
Comment Utility
I'm thinking that's probably the best approach.  If anything people will be looking for all departments on 1st shift, or single departments on multiple shifts or just all the data.  Now I just need to figure out how to do this.

Again, thanks to both mlmcc and James for all your help, even after I accepted a solution!  As I keep building reports in CR I know the two people to ask the tough questions too!
0
 
LVL 34

Expert Comment

by:James0628
Comment Utility
To start with, you could add the {?Start Date} and {?End Date} parameters to the Command.  It sounds like you're not using them in the Command now, in which case the Command may be pulling all dates, and then CR is selecting the dates you want from that data.  Or I could be wrong.  :-)

 As for the other parameters, like I said, I think CR added support for using multi-value parameters in a Command at some point, so you should be OK as far as that goes.  And, assuming that's true, I don't see why you'd need to change the parameter.  Instead of
{?Select Work Area} = "Cutting Products 1st"
  the Command might need to use
"Cutting Products 1st" IN ({?Select Work Area})

 If it's the if-else statements that bother you, like I said before, you don't need those.  For example:

(if{?Select Work Area} = "Cutting Products 1st" then
{Efficieny.MODEM} = "Cutting Products" and {Efficieny.FAX} = "1"
else
false)

 could be written as

({?Select Work Area} = "Cutting Products 1st" and {Efficieny.MODEM} = "Cutting Products" and
{Efficieny.FAX} = "1")

 or, using IN,

("Cutting Products 1st" IN ({?Select Work Area}) and {Efficieny.MODEM} = "Cutting Products" and
{Efficieny.FAX} = "1")

 James

 PS:
 I just noticed that "Efficieny" seems to be missing a "c".  :-)
0
 
LVL 9

Author Comment

by:dustock
Comment Utility
James,

I edited my command to use four parameters.  Shift and Dept are multi value fields, sDate and eDate are Date fields.  Then I modified my the SQL in the command to do

where PE_WORK.MODEM in {?Dept} and PE_WORK.FAX in {?Shift} and CAST(MSTIME AS DATE) between {?sDate} and {?eDate}

Then I went into my formula and commented out everything.  Now the report runs super fast, and gives me only the data I want instead of grabbing a whole bunch of data and then sifting through it.

My last pain point is how big the parameter list has gotten.  I wish I could modify the format of it, but that's minimal.  Plus I learned how many different ways I can build a report and which ways are better than others.
0
 
LVL 34

Expert Comment

by:James0628
Comment Utility
What's "big" about the parameter list?  Are you talking about {?Dept} and {?Shift} ?

 Like I said before, I think you could keep the old {?Select Work Area} parameter with values like "Cutting Products 1st".

 James
0
 
LVL 9

Author Comment

by:dustock
Comment Utility
Big as in I have to scroll on the box now.  It's a minor inconvenience.  In the end I like how this report runs now, it's much faster and I have completely eliminated the formula's section.  Again I really appreciate all the help!  I took the knowledge learned from this reports trial and error how to effectively write a formula.  Which will help a lot as I continue to develop and fix other reports.
0
 
LVL 34

Expert Comment

by:James0628
Comment Utility
As long as you're happy with it.

 James
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Suggested Solutions

Hot fix for .Net Crystal Reports 10.2.3600.0 to fix problems with sub reports running on 64 bit operating systems ISSUE: Reports which contain subreports fail with error "Missing Parameter Value" DEPLOYMENT SERVER OS: Windows 2008 with 64 bi…
Hello everyone, Hope you find this as helpful as we did. We have on the company I work for an application built in Delphi V with Crystal Reports 8. We all know that Crystal & Delphi can be temperamental sometimes and the worst thing is, nearly…
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now