Skip to Content
0
Former Member
Aug 16, 2013 at 05:45 PM

Newbie with CRIX and SQL2008r2

24 Views

I'm brand new to Crystal IX. Of course, the very first report I'm trying to do isn't covered in the beginners or advanced classes. Any help would be greatly appreciated.....

I have a report I need to pull for my doctors based on our fiscal year (7/1/XXXX-ending day of the month needed). It's a huge health record system. So, I figured to have the user select the month and the year, and I'd do the selection and everything via the sql and calculate the formula fields in Crystal. But, what I can't figure out how to do is to pass the Crystal parameter to sql2008r2 (all without using a stored procedure - I don't have permissions)? For example: I'd like the doctor to select from 1-12 for the svcMonth and 2011,2012,2013 for the svcYear in the Crystal parameter and pass that to the sql command. I know how to create the sql code, the crystal command and the crystal parameter, but I need the parameter within sql to limit the dataset pull. However, I can't figure out how to do it unless I make the doctors enter the fiscal year instead of the calendar year. And, if I do that, I know they're not going to pay attention and pick the wrong one; e.g. select December (for 2012) and select 2012 (instead of fiscal year end 2013). Any ideas?

Here's the beginning of my sql and the top results of the dataset (more fields in the dataset for formula fields - just abbreviated for the sake of display):

declare @svcMonth as int, @svcYear as int, @startDate as smalldatetime, @endDate as smalldatetime
set @svcMonth=6
set @svcYear=2013

--//Figure out fiscal year start and end based on month parameter, FY=7/1/XXXX-6/30/XXXX
if @svcMonth<7
begin
set @startDate=convert(smallDateTime,'7/1/'+cast(@svcYear-1 as varchar(4)) + ' 00:00:01') --//I'll use greater than or equal to
set @endDate = convert(smalldatetime,cast(@svcMonth+1 as varchar(4)) + '/1/'+cast(@svcYear as varchar(4)) + ' 00:00:01') --//I'll use only less than
end
if @svcMonth>6
begin
set @startDate=convert(smallDateTime,'7/1/'+cast(@svcYear as varchar(4)) + ' 00:00:01') --//I'll use greater than or equal to
set @endDate = convert(smalldatetime,cast(@svcMonth+1 as varchar(4)) + '/1/'+cast(@svcYear as varchar(4)) + ' 00:00:01') --//I'll use only less than
end

select rsf.category, rsf.doctor, rsf.dnkaOB, rsf.actAmAppt, rsf.amAppt, rsf.actPmAppt, rsf.pmAppt, rsf.obrevisits, rsf.rvu,rsf.charges, rsf.svcMonth, rsf.svcYear from blah, blah, blah 129 more lines


category Doctor dnkaOB svcMo svcYear
Graduate Doctor 1 0 5 2013
Graduate Doctor 1 0 6 2013
Graduate Doctor 2 0 6 2013
Graduate Doctor 2 0 12 2012