Print Page | Close Window

How do I select multiple values based on parameter

Printed From: Crystal Reports Book
Category: Crystal Reports .NET 2003
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=14031
Printed Date: 18 May 2024 at 7:56am


Topic: How do I select multiple values based on parameter
Posted By: infinitizon
Subject: How do I select multiple values based on parameter
Date 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.



Replies:
Posted By: lockwelle
Date 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


Posted By: infinitizon
Date Posted: 11 Aug 2011 at 5:06am

@ http://www.crystalreportsbook.com/Forum/member_profile.asp?PF=3327&FID=8 - 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?



-------------
____________________
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.


Posted By: hilfy
Date 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 mailto:%7b@FilterArray - {@FilterArray } your selection criteria will then look like this:
 
{command.Job Posting Group} in mailto:%7b@FilterArray - {@FilterArray }
 
-Dell


-------------
Proviti, Data & Analytics Practice
http://www.protiviti.com/US-en/data-management-advanced-analytics - www.protiviti.com/US-en/data-management-advanced-analytics


Posted By: infinitizon
Date Posted: 11 Aug 2011 at 6:24am
@ http://www.crystalreportsbook.com/Forum/member_profile.asp?PF=28&FID=8 - 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.


Posted By: infinitizon
Date 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.


Posted By: infinitizon
Date 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 mailto:%7b@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" mailto:and%7b@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.



Print Page | Close Window