Report Design
 Crystal Reports Forum : Crystal Reports .NET 2003 : Report Design
Message Icon Topic: How do I select multiple values based on parameter Post Reply Post New Topic
Author Message
infinitizon
Newbie
Newbie
Avatar

Joined: 27 Jul 2011
Location: Nigeria
Online Status: Offline
Posts: 12
Quote infinitizon Replybullet Topic: How do I select multiple values based on parameter
    Posted: 11 Aug 2011 at 12:09am
Hi all,
I have a little challenge. I have a report that I declared a parameter called Department that has default values like this:
All Departments
Project
Field Service
Workshop
Warranty
Marine
Contract
Manufacturing

I have also enabled the "Allow Multiple Values" check box. In my select expert, I have:
{command.Job Posting Group} in
switch(
{?Department} = "All Departments",["","JOB - EXT", "JOB-AFMEXP", "JOB-LUB/S", "JOB-NBCMTU","JOB-ABB-AS","JOB-CANPJB",
"JOB-INSTAL","JOB-CUMNS", "JOB-W/SHOP", "JOB-WS/GEN","JOB-WTY","JOB-MAR","JOB-S/AGR", "RENTAL"],
{?Department} = "Project",["JOB-INSTAL"],
{?Department} = "Field Service", ["JOB - EXT", "JOB-AFMEXP", "JOB-LUB/S", "JOB-NBCMTU","JOB-ABB-AS"],
{?Department} = "Workshop", ["JOB-CUMNS", "JOB-W/SHOP", "JOB-WS/GEN"],
{?Department} = "Warranty",["JOB-WTY"],
{?Department} = "Marine",["JOB-MAR"],
{?Department} = "Contract",["JOB-S/AGR"],
{?Department} = "Manufacturing",["JOB-CANPJB"]
)
The problem is that when I test this (with CR10 or on web), and I select multiple values, the thing does not show the values for the multiple values. It only works when I select a single department.
What should I do? 
____________________
Men put up a strong face just to cover their weaknesses...good!
But a smile makes them live longer....
Which would you choose?

Think about it.
IP IP Logged
lockwelle
Moderator
Moderator


Joined: 21 Dec 2007
Online Status: Offline
Posts: 4372
Quote lockwelle Replybullet Posted: 11 Aug 2011 at 3:47am
multiple values are stored in an array, so to see if the desired option was selected you would use IN
 
if 'xyz' in {?parameter} then ...
 
at least I believe that is the syntax
 
HTH
IP IP Logged
infinitizon
Newbie
Newbie
Avatar

Joined: 27 Jul 2011
Location: Nigeria
Online Status: Offline
Posts: 12
Quote infinitizon Replybullet Posted: 11 Aug 2011 at 5:06am

@lockwelle: thanks, but can you explain a bit more. I dont seem to get you too well. Where do I then store the parameter? and do I call it in the select expert?



Edited by infinitizon - 11 Aug 2011 at 5:11am
____________________
Men put up a strong face just to cover their weaknesses...good!
But a smile makes them live longer....
Which would you choose?

Think about it.
IP IP Logged
hilfy
Admin Group
Admin Group
Avatar

Joined: 20 Nov 2006
Online Status: Offline
Posts: 3702
Quote hilfy Replybullet Posted: 11 Aug 2011 at 5:13am

Multi-valued parameters return arrays, not strings, so you can't compare them to strings.  Instead, you have to use some of the array functions.  Also, your Switch statement is not going to get you all of the values - it only evaluates until the first time it hits a "True" expression.

So, what you need to do is create an array in one formula and then use it in your filter.  It will look something like this:
Stringvar Array filter := ['default'];
local Numbervar j;
local Numbervar items := 0;
local Booleanvar isAll := false;
for j := 1 to Ubound({?Department} Do
(
  if not isAll then
  (
    if {?Department}[j] = 'All Departments' then
    (
      isAll := true;
      filter := ['', 'JOB - EXT', 'JOB-AFMEXP', 'JOB-LUB/S', 'JOB-NBCMTU',
      'JOB_ABB_AS', 'JOB-CANPJB', 'JOB_INSTAL', 'JOB_CUMNS',
      'JOB-W/SHOP', 'JOB-WS/GEN', 'JOB-WTY', 'JOB-MAR', 'JOB-S/AGR',
      'RENTAL'];
    )
    if {?Department}[j] = 'Project' then
    (
      items := items + 1;
      redim filter [items];
      filter[items] := 'JOB-INSTAL';
    )
    if {?Department}[j] = 'Field Service' then
    (
      items := items + 5;
      redim filter[items];
      filter[items - 4] := 'JOB-EXT';
      filter[items - 3] := 'JOB-AFMEXP';
      filter[items - 2] := 'JOB-LUB/S';
      filter[items - 1] := 'JOB-NBCMTU';
      filter[items] := 'JOB-ABB-AS';
    )
     <etc... through all of your department values>
  )    
 )
filter
This will build the array that you want to use to filter your data.  If I call this formula {@FilterArray} your selection criteria will then look like this:
 
{command.Job Posting Group} in {@FilterArray}
 
-Dell


Edited by hilfy - 11 Aug 2011 at 5:14am
IP IP Logged
infinitizon
Newbie
Newbie
Avatar

Joined: 27 Jul 2011
Location: Nigeria
Online Status: Offline
Posts: 12
Quote infinitizon Replybullet Posted: 11 Aug 2011 at 6:24am
@hilfy: Whoops!! Thanks, I've never been a heavy user of crystal reports and thus never seen such an enormous code....
I'd try it, thanks
____________________
Men put up a strong face just to cover their weaknesses...good!
But a smile makes them live longer....
Which would you choose?

Think about it.
IP IP Logged
infinitizon
Newbie
Newbie
Avatar

Joined: 27 Jul 2011
Location: Nigeria
Online Status: Offline
Posts: 12
Quote infinitizon Replybullet Posted: 11 Aug 2011 at 10:34pm
@hilfy: Thanks again, I've just tried your trick completed the list as below and made corrections to the parts in red. I also used thesame array name as you suggested(FilterArray).
When I however tried it try to save the array, it pops up an error saying 'the ) is missing' on the if statement in green.
Stringvar Array filter := ['default'];
local Numbervar j;
local Numbervar items := 0;
local Booleanvar isAll := false;
for j := 1 to Ubound({?Department}) Do(
  if not isAll then
  (
    if {?Department}[j] = 'All Departments' then
    (
      isAll := true;
      filter := ['', 'JOB - EXT', 'JOB-AFMEXP', 'JOB-LUB/S', 'JOB-NBCMTU', 'JOB_ABB_AS', 'JOB-CANPJB', 'JOB_INSTAL',
                 'JOB_CUMNS', 'JOB-W/SHOP', 'JOB-WS/GEN', 'JOB-WTY', 'JOB-MAR', 'JOB-S/AGR', 'RENTAL'];
    )
    if {?Department}[j] = 'Project' then
    (
      items := items + 1;
      redim filter [items];
      filter[items] := 'JOB-INSTAL';
    )
    if {?Department}[j] = 'Field Service' then
    (
      items := items + 5;
      redim filter[items];
      filter[items - 4] := 'JOB-EXT';
      filter[items - 3] := 'JOB-AFMEXP';
      filter[items - 2] := 'JOB-LUB/S';
      filter[items - 1] := 'JOB-NBCMTU';
      filter[items] := 'JOB-ABB-AS';
    )
    if {?Department}[j] = 'Workshop' then
    (
      items := items + 3;
      redim filter[items];
      filter[items - 2] := 'JOB-CUMNS';
      filter[items - 1] := 'JOB-W/SHOP';
      filter[items] := 'JOB-WS/GEN';
    )
    if {?Department}[j] = 'Warranty' then
    (
      items := items + 1;
      redim filter [items];
      filter[items] := 'JOB-WTY';
    )
 if {?Department}[j] = 'Marine' then
    (
      items := items + 1;
      redim filter [items];
      filter[items] := 'JOB-MAR';
    )
 if {?Department}[j] = 'Contract' then
    (
      items := items + 1;
      redim filter [items];
      filter[items] := 'JOB-S/AGR';
    )
 if {?Department}[j] = 'Manufacturing' then
    (
      items := items + 1;
      redim filter [items];
      filter[items] := 'JOB-CANPJB';
    )
 if {?Department}[j] = 'Rental' then
    (
      items := items + 1;
      redim filter [items];
      filter[items] := 'RENTAL';
    )
  )
)
filter
What could be wrong?
____________________
Men put up a strong face just to cover their weaknesses...good!
But a smile makes them live longer....
Which would you choose?

Think about it.
IP IP Logged
infinitizon
Newbie
Newbie
Avatar

Joined: 27 Jul 2011
Location: Nigeria
Online Status: Offline
Posts: 12
Quote infinitizon Replybullet Posted: 12 Aug 2011 at 2:12am
@hilfy: Well, I tried your method, but it didnt work. and I eventually tried something interesting that seemed to work. Here's what I did:
Step 1: Create a formular called {@Dept}
if {command.Job Posting Group} in ["JOB - EXT", "JOB-AFMEXP", "JOB-LUB/S", "JOB-NBCMTU","JOB-ABB-AS","JOB-NBCLUB" ] then
    "Field Service"
 else
if {command.Job Posting Group} in ["JOB-CUMNS", "JOB-W/SHOP", "JOB-WS/GEN"] then
    "Workshop"
else
if {command.Job Posting Group} = "JOB-WTY" then
    "Warranty"
 else
if {command.Job Posting Group} = "JOB-MAR" then
    "Marine"
 else
if {command.Job Posting Group} = "JOB-INSTAL" then
    "Project"
 else
if {command.Job Posting Group} = "JOB- S/AGR" then
    "Contract"
else
if {command.Job Posting Group} = ["JOB-CANPJB"] then
 "Manufacturing"
else
if {command.Job Posting Group} = "" or {command.Job Posting Group} = " " then
 "Others"
then created a selection formular {?Department} thus:
(
    (
        {?Department} <> "All Departments" and{@Dept} = {?Department}
    ) or
    {?Department} = "All Departments"
)
 
It all worked like magic.
 
But thanks for your response. It pointed me in the right direction.
____________________
Men put up a strong face just to cover their weaknesses...good!
But a smile makes them live longer....
Which would you choose?

Think about it.
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.