Solved

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

Posted on 2013-06-06
17
362 Views
1 Endorsement
Last Modified: 2013-06-10
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.
1
Comment
Question by:Star79
  • 7
  • 5
  • 4
17 Comments
 
LVL 100

Expert Comment

by:mlmcc
ID: 39227270
{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
 

Author Comment

by:Star79
ID: 39227289
How should I handle if the parameter {PhysicianField} is static and not dynamic.The user would enter physicianid1,physicianid2,physicianid3
0
 
LVL 100

Assisted Solution

by:mlmcc
mlmcc earned 200 total points
ID: 39227399
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
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 

Author Comment

by:Star79
ID: 39227624
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
 
LVL 100

Expert Comment

by:mlmcc
ID: 39227827
What are they entering?

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

mlmcc
0
 

Author Comment

by:Star79
ID: 39229029
its usually the physcican id like 1003034034,1689755860 etc...
But this field is a varchar in the database table.
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 39229170
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
 

Author Comment

by:Star79
ID: 39229246
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
 
LVL 34

Accepted Solution

by:
James0628 earned 300 total points
ID: 39229248
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
 

Author Comment

by:Star79
ID: 39229304
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
 
LVL 34

Expert Comment

by:James0628
ID: 39229915
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
 

Author Comment

by:Star79
ID: 39230234
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
 
LVL 34

Expert Comment

by:James0628
ID: 39230348
mlmcc should probably get some of the points.  He posted the Like formula.  He just had the parts around Like reversed.

 James
0
 

Author Comment

by:Star79
ID: 39230365
how can I reassign the points
0
 
LVL 34

Expert Comment

by:James0628
ID: 39230386
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
 
LVL 100

Expert Comment

by:mlmcc
ID: 39230445
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

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

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…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

820 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