Solved

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

Posted on 2013-06-06
17
363 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
[Webinar] Code, Load, and Grow

Managing multiple websites, servers, applications, and security on a daily basis? Join us for a webinar on May 25th to learn how to simplify administration and management of virtual hosts for IT admins, create a secure environment, and deploy code more effectively and frequently.

 

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 35

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 35

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 35

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 35

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

Enroll in May's Course of the Month

May’s Course of the Month is now available! Experts Exchange’s Premium Members and Team Accounts have access to a complimentary course each month as part of their membership—an extra way to increase training and boost professional development.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Crystal Reports with Yardi error 800a004b 2 165
Need to learn crystal reports 7 94
Crystal Reports - group header 2 66
Crystal Reports Date Calculation 10 52
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. …
There have always been a lot of questions related to when Crystal Reports evaluates report components (such as formulas, summaries, cross-tabs, charts, to name a few examples). Crystal Reports uses a two-pass reporting process to provide greater …
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…
Are you ready to implement Active Directory best practices without reading 300+ pages? You're in luck. In this webinar hosted by Skyport Systems, you gain insight into Microsoft's latest comprehensive guide, with tips on the best and easiest way…

734 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