🙂 Yeah, I'm just not a crystal user more than as an activeX add on to visual programming environments. My brain understands how SQL thinks more than Crystal Logic. I used to create custom views in the SQL database then use Crystal to pull from those but every time we update GE wipes them out and I have to run a script to resurrect the views we need for some performance metric reports we run. This particular project returns a table of all clinicians, a numerator and denominator for each and the percentage of the two of anyone that was notified for their annual visit. This work-around will be submitted in place of the printed notification GE tracks for MU that nobody does. The query in "Command" looks like this:
-------------------- START QUERY-----------------
select a.*,b.denom
,Round(CAST(cast(a.numerator as decimal)/cast(b.denom as decimal) * 100 as decimal), 2) PCT
from(
select num.listname, count(num.listname) numerator from
(select obs.pid,df.listname
from obs
join patientprofile pp on pp.pid = obs.pid
join doctorfacility df on df.doctorfacilityid = pp.doctorid
where hdid = '6666' and (obsvalue = '1st Recall Done' or obsvalue = '2nd Recall Done' or obsvalue = 'Recall Pending')
and obs.pid in (
select document.pid from document where db_create_date >dateadd(yy,-2,{?StartDate}) and doctype = '1'
group by document.pid having count(pid)>'2'
)) Num
where num.listname not in ('fishback, greg','Juleeanna Andreoni','kelli carner', 'lindsay field')
group by num.listname
) A
join (
select denom.listname, count(denom.listname) denom from (
select num.pid,df.listname from (
select pid from document where db_create_date > '01-01-2014 00:00:00.000' and doctype = '1'
group by pid having count(pid)>2
) Num
join patientprofile pp on pp.pid = num.pid
join doctorfacility df on df.doctorfacilityid = pp.doctorid
)denom
group by denom.listname
) B on B.listname = A.listname
---------------- END QUERY -------------------
Note the {?StartDate} in bold. if you type a date in there the query runs perfectly in any sql editor, in Crystal, when you do that it automatically generates the StartDate under the Parameter Field in teh Field Explorer and when you refresh it from within the Crystal environment it prompts you for said StartDate with a popup calendar field, you pick a date and it returns a perfect report. Save the RPT file and run it from inside of CPS and you still get the exact same popup calendar. You select a date, click OK and you get that "Could not run the query, No value given for one or more required parameters". I swear I remember seeing the problem before and resolving it but I'm totally drawing a blank.
Posted : September 16, 2015 7:26 am
