Notifications
Clear all
Topic starter
I've created a crystal report that works perfectly in the crystal environment. There is a parameter for "StartDate" that I put into the SQL Command as {?StartDate} and defined it as a date field in the paramater list. When previewing from the crystal environment it pops up the classic calendar bar and you can put a date in, hit OK and it refreshes. When the .RPT file is saved to the CRWRPT folder and it is invoked in CPS, I get the same date prompt window, put in a date, click OK and I get "No value given for one or more required parameters". I vaguely remember fighting this once before (clearly Crystal Reports is NOT my specialty) but can't for the life of me remember how to fix this. Any help?
Greg
Posted : September 11, 2015 7:53 am
I'm not quite sure what you mean when you say you put it into the SQL Command...? I'm well-versed in Crystal, so pretty much everything else you said made sense to me except that.
Posted : September 11, 2015 4:02 pm
he means, when he run it from crystal report it work.
but when he import the report into CPS/EMR and run it form the EMR interface, it doesn't.
are you sure the field you are trying to pull data from as date, does not need a date conversion formula?
Posted : September 14, 2015 10:21 am
I understand that part of it.
As I said, I'm confused about this: "There is a parameter for “StartDate” that I put into the SQL Command as {?StartDate}..." What does the phrase "I put [the parameter] into the SQL Command" mean in this context? What SQL Command? Where, in Crystal? By what means?
Posted : September 15, 2015 6:51 am
Topic starter
Sorry all, I wasn't getting the notifications to this thread for some reason. ronmoses, I based my crystal report on an SQL statement. so under the database manager I use the option for using an SQL statement instead of tables. In that statement, if you put {?variable} it will show up as an established parameter. Sorry to be vague. My system completely died on Friday so I'm building a new machine and don't have Crystal installed yet. Let me get my environment back up and I'll give you all some more specifics.
Posted : September 15, 2015 12:38 pm
Ah, that's interesting. I've never used that in 25 years! I may investigate that option a bit.
Posted : September 16, 2015 1:36 am
Topic starter
🙂 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
That's odd, and not having worked that way I don't have a definitive answer. But what I would try is replacing that parameter with a formula. Call it StartDate and replace {?StartDate} with {@StartDate} in your query. Put some dummy date like #1/1/2105# in the formula. Delete the StartDate parameter.
Then when you add the report to CPS, set up a Report Parameter with a Formula Name of StartDate. CPS will provide the calendar control and populate the date to the formula. Now the report won't be looking for a parameter anymore.
I have no idea if that will work. But if it does I'm taking the rest of the afternoon off.
By the way, if you name your custom objects (views, stored procs, etc.) with a prefix of cus_, GE shouldn't touch them on upgrade.
Posted : September 16, 2015 8:00 am
