Solved

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

Posted on 2013-06-06
17
358 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
Comment Utility
{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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
What are they entering?

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

mlmcc
0
 

Author Comment

by:Star79
Comment Utility
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
Comment Utility
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
Comment Utility
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
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
LVL 34

Accepted Solution

by:
James0628 earned 300 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
how can I reassign the points
0
 
LVL 34

Expert Comment

by:James0628
Comment Utility
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
Comment Utility
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

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

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…
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 …
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…

763 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

9 Experts available now in Live!

Get 1:1 Help Now