How to use multi-value parameters in selection formula in the report

Hello All,
I have a report parameter PhysicanID and it accepts multiple values.How can I implement the selection of multiple values in the selection formula?
Thanks.
Star79Asked:
Who is Participating?
 
James0628Commented:
I finally figured out why that was bothering me.  The part with the wildcards needs to be _after_ Like.  :-)

{?Physician NPI} LIKE "*" & {vwcrystal_PhysicianFacilityListingGtoC.phnpi}  & "*"


 James
0
 
mlmccCommented:
{PhysicianField} IN {?Parameter}

or

{PhysicianField}  = {?Parameter}

Since it is a multiple value, Crystal will translate it to multiple equals for sending to the database

mlmcc
0
 
Star79Author Commented:
How should I handle if the parameter {PhysicianField} is static and not dynamic.The user would enter physicianid1,physicianid2,physicianid3
0
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

 
mlmccCommented:
Multiple value parameters are the same either way.

A static parameter just means you don't see updates based on the field.

If the user is entering a comma separated list, that isn't a multiple value parameter but rather a single value.

In that case you will have to use LIKE

"*" & {PhysicianField}  & "*" LIKE  {?Parameter}

mlmcc
0
 
Star79Author Commented:
mlmcc,
The selection formula looks:

{vwcrystal_FacilityListingGtoC.FacID} ={?Facility} and
{vwcrystal_PhTheraChangeGPICmpListing.InterchangeType} = "C" and
{vwcrystal_PhTheraChangeGPICmpListing.TheraType} = "G" and
"*" & {vwcrystal_PhysicianFacilityListingGtoC.phnpi}  & "*" LIKE  {?Physician NPI}
There is no data getting returned
0
 
mlmccCommented:
What are they entering?

Strings must match the case in most databases so Joe <> joe

mlmcc
0
 
Star79Author Commented:
its usually the physcican id like 1003034034,1689755860 etc...
But this field is a varchar in the database table.
0
 
mlmccCommented:
Comment out the first 3 lines of the filter so you can test that the physician is working


"*" & {vwcrystal_PhysicianFacilityListingGtoC.phnpi}  & "*" LIKE  {?Physician NPI}

mlmcc
0
 
Star79Author Commented:
hello mlmcc,
My selection formula has just this:
 "*" & {vwcrystal_PhysicianFacilityListingGtoC.phnpi}  & "*" LIKE  {?Physician NPI}
I copied the show sql query and is as below and I noticed that its missing the where clause:

 SELECT "vwcrystal_FacilityListingGtoC"."FacID", "vwcrystal_PhTheraChangeGPICmpListing"."TimesPerDay", "vwcrystal_PhysicianFacilityListingGtoC"."name", "vwcrystal_PhysicianFacilityListingGtoC"."phnpi", "Compounds"."Name", "vw_crystalITSFGPIMANUFNAMES"."drug", "vwcrystal_FacilityListingGtoC"."FacName", "vwcrystal_PhTheraChangeGPICmpListing"."Sig"
 FROM   ((("FwReports"."dbo"."vwcrystal_PhTheraChangeGPICmpListing" "vwcrystal_PhTheraChangeGPICmpListing" INNER JOIN "FwReports"."dbo"."vwcrystal_PhysicianFacilityListingGtoC" "vwcrystal_PhysicianFacilityListingGtoC" ON ("vwcrystal_PhTheraChangeGPICmpListing"."PhNPI"="vwcrystal_PhysicianFacilityListingGtoC"."phnpi") AND ("vwcrystal_PhTheraChangeGPICmpListing"."FacID"="vwcrystal_PhysicianFacilityListingGtoC"."FacID")) LEFT OUTER JOIN "FwReports"."dbo"."Compounds" "Compounds" ON "vwcrystal_PhTheraChangeGPICmpListing"."CCID"="Compounds"."CCID") INNER JOIN "FwReports"."dbo"."vw_crystalITSFGPIMANUFNAMES" "vw_crystalITSFGPIMANUFNAMES" ON (("vwcrystal_PhTheraChangeGPICmpListing"."code"="vw_crystalITSFGPIMANUFNAMES"."code") AND ("vwcrystal_PhTheraChangeGPICmpListing"."PhNPI"="vw_crystalITSFGPIMANUFNAMES"."phnpi")) AND ("vwcrystal_PhTheraChangeGPICmpListing"."FacID"="vw_crystalITSFGPIMANUFNAMES"."facid")) INNER JOIN "FwReports"."dbo"."vwcrystal_FacilityListingGtoC" "vwcrystal_FacilityListingGtoC" ON "vwcrystal_PhysicianFacilityListingGtoC"."FacID"="vwcrystal_FacilityListingGtoC"."FacID"
 ORDER BY "vwcrystal_FacilityListingGtoC"."FacID", "vw_crystalITSFGPIMANUFNAMES"."drug"

Open in new window

0
 
Star79Author Commented:
ok james,The data is coming for only one physicianID but when I enter multiple values like
1962420281,1972696409.
It gives the data for the last id only.
Please advice.
0
 
James0628Commented:
In theory, it should work.  Using your example, both of the following produce a True result:

"1962420281,1972696409" like "*" & "1962420281" & "*"

"1962420281,1972696409" like "*" & "1972696409" & "*"

 So, if phnpi is either value, it should be included.

 If the field could include trailing (or leading) spaces, those would probably be a problem.  Try this:

{?Physician NPI} LIKE "*" & Trim ({vwcrystal_PhysicianFacilityListingGtoC.phnpi})  & "*"


 If that doesn't work, does your record selection formula include the other conditions that you posted (TheraType = "G", etc.)?  If so, you could try removing those conditions again.

 If you still don't get the other ID's, then maybe there's a problem with the values.  You said that if you enter "1962420281,1972696409" for the parameter, you get data for the last value ("1972696409" in this case).  What do you get if you swap the values and enter "1972696409,1962420281"?

 James
0
 
Star79Author Commented:
I was able to produce the data with james formula.Un knowingly I had a condition on the details section which said @recfinder>1 where it checks for multiple values.
0
 
James0628Commented:
mlmcc should probably get some of the points.  He posted the Like formula.  He just had the parts around Like reversed.

 James
0
 
Star79Author Commented:
how can I reassign the points
0
 
James0628Commented:
You can use the "Request Attention" link below your first post to ask to have the question re-opened (assuming that mlmcc doesn't do it for you) and then accept multiple posts as the solution and split the points between them.

 James
0
 
mlmccCommented:
Use the REQUEST ATTENTION link.  I can do it but since I will benefit, my ethics (and the unwritten rules of EE) say I need to allow another admin to do it.

mlmcc
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.