Solved

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

Posted on 2013-06-06
17
359 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
 

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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Crystal Reports: 5 Tests for Top Performance It is complete, your masterpiece report.  Not only does it meet your customer’s expectations, it blows them out the water, all they want is beautifully summarised and displayed in a myriad of ways. …
I hate sub reports and always consider them the last resort in any reporting solution.  The negative effect on performance and maintainability is just not worth the easy ride they give the report writer.  Nine times out of ten reporting requirements…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…
A company’s greatest vulnerability is their email. CEO fraud, ransomware and spear phishing attacks are the no1 threat to a company’s security. Cybercrime is responsible for the largest loss of money to companies today with losses projected to r…

930 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

14 Experts available now in Live!

Get 1:1 Help Now