Star79
asked on
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.
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.
ASKER
How should I handle if the parameter {PhysicianField} is static and not dynamic.The user would enter physicianid1,physicianid2, physiciani d3
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
mlmcc,
The selection formula looks:
{vwcrystal_FacilityListing GtoC.FacID } ={?Facility} and
{vwcrystal_PhTheraChangeGP ICmpListin g.Intercha ngeType} = "C" and
{vwcrystal_PhTheraChangeGP ICmpListin g.TheraTyp e} = "G" and
"*" & {vwcrystal_PhysicianFacili tyListingG toC.phnpi} & "*" LIKE {?Physician NPI}
There is no data getting returned
The selection formula looks:
{vwcrystal_FacilityListing
{vwcrystal_PhTheraChangeGP
{vwcrystal_PhTheraChangeGP
"*" & {vwcrystal_PhysicianFacili
There is no data getting returned
What are they entering?
Strings must match the case in most databases so Joe <> joe
mlmcc
Strings must match the case in most databases so Joe <> joe
mlmcc
ASKER
its usually the physcican id like 1003034034,1689755860 etc...
But this field is a varchar in the database table.
But this field is a varchar in the database table.
Comment out the first 3 lines of the filter so you can test that the physician is working
"*" & {vwcrystal_PhysicianFacili tyListingG toC.phnpi} & "*" LIKE {?Physician NPI}
mlmcc
"*" & {vwcrystal_PhysicianFacili
mlmcc
ASKER
hello mlmcc,
My selection formula has just this:
"*" & {vwcrystal_PhysicianFacili tyListingG toC.phnpi} & "*" LIKE {?Physician NPI}
I copied the show sql query and is as below and I noticed that its missing the where clause:
My selection formula has just this:
"*" & {vwcrystal_PhysicianFacili
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"
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
1962420281,1972696409.
It gives the data for the last id only.
Please advice.
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_PhysicianFacil ityListing GtoC.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
"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_PhysicianFacil
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
ASKER
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.
mlmcc should probably get some of the points. He posted the Like formula. He just had the parts around Like reversed.
James
James
ASKER
how can I reassign the points
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
James
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
mlmcc
or
{PhysicianField} = {?Parameter}
Since it is a multiple value, Crystal will translate it to multiple equals for sending to the database
mlmcc