Technical Questions
 Crystal Reports Forum : Crystal Reports 9 through 2022 : Technical Questions
Message Icon Topic: Horizontal Bar Graph Help Needed - Guru Needed Post Reply Post New Topic
Author Message
JeffS23
Newbie
Newbie
Avatar

Joined: 02 Nov 2007
Location: United States
Online Status: Offline
Posts: 8
Quote JeffS23 Replybullet Topic: Horizontal Bar Graph Help Needed - Guru Needed
    Posted: 27 Dec 2008 at 6:50pm
For my report, I want 3 charts. One crosstab and 2 horizontal bar graphs.

I currently have the crosstab chart set-up with all months (12 in total) across the top horizontal axis and my codes (10 total) along my vertical axis. This report is working as I need. What I need help with is my two horizonal bar graphs. I want one to pull the following codes (99201 - 99205) for the current month only and the other chart to pull codes (99211 - 99215) for the current month.

I really hope I explained myself well. If not, please tell me and I'll be happy to explain better. Essentially, I just need the two bar graphs and one to pull one set of codes and the other to pull the other range of codes for the End date used in my SQL.

I attached the SQL to this report just in case its needed. Please note, the SQL is handled dynamically and alot in the Where clause may look odd to you.
 
My SQL:
__________________________________________________________
 
SET NOCOUNT ON
DECLARE
     @db varchar(50),
     @startdate datetime,
     @enddate datetime,
     @counterdate datetime,
     @datevar datetime,
     @counter int,
 @i datetime
SET @datevar  = ISNULL('12/01/2008','1/1/1900')
SET @counter =1
SET @DB = DB_Name()
SET @enddate = DATEADD(month , 1 , @datevar)
SET @startdate = DATEADD(month , -11 , @datevar)
DECLARE @Temp TABLE
 (
     TheMonth DATETIME,
     ProcedureCode CHAR(5)
 )
SELECT @i = @Startdate
WHILE @i <= @datevar
BEGIN
    INSERT INTO @Temp SELECT @i, '99201'
    INSERT INTO @Temp SELECT @i, '99202'
    INSERT INTO @Temp SELECT @i, '99203'
    INSERT INTO @Temp SELECT @i, '99204'
    INSERT INTO @Temp SELECT @i, '99205'
    INSERT INTO @Temp SELECT @i, '99211'
    INSERT INTO @Temp SELECT @i, '99212'
    INSERT INTO @Temp SELECT @i, '99213'
    INSERT INTO @Temp SELECT @i, '99214'
    INSERT INTO @Temp SELECT @i, '99215'
    SELECT @i = DATEADD(month, 1, @i)
END
SELECT
 a.TheMonth,
 a.ProcedureCode,
 ISNULL(b.Referrals, 0) AS Referrals
 FROM @Temp a
 LEFT OUTER JOIN
  (
   SELECT
     *
 FROM
     (
       SELECT
         CONVERT(DATETIME , CONVERT(VARCHAR , MONTH(pvp.dateofentry)) + '/1/' + CONVERT(VARCHAR , YEAR(pvp.dateofentry))) AS Month ,
         pvp.cptcode AS 'Type' ,
         SUM(CASE WHEN pvp.cptcode IN ( '99201' , '99202' , '99203' , '99204' , '99205' , '99211' , '99212' , '99213' , '99214' , '99215' ) THEN 1
                  ELSE 0
             END) AS 'Referrals'
FROM  PatientVisit pv
         JOIN doctorfacility dr ON pv.doctorid = dr.doctorfacilityid
         JOIN patientvisitprocs pvp ON pv.patientvisitid = pvp.patientvisitid
WHERE
 pvp.dateofentry >= @startdate
         AND pvp.dateofentry < @enddate
 AND  --Filter on doctor
 (
 (NULL IS NOT NULL AND pv.DoctorID IN (NULL)) OR
 (NULL IS NULL)
 )
 AND  --Filter on facility
 (
 (NULL IS NOT NULL AND pv.FacilityID IN (NULL)) OR
 (NULL IS NULL)
 )
GROUP BY
         CONVERT(DATETIME , CONVERT(VARCHAR , MONTH(pvp.dateofentry)) + '/1/' + CONVERT(VARCHAR , YEAR(pvp.dateofentry))) ,
         pvp.cptcode
     ) AS T
 WHERE
     Type IN ( '99201' , '99202' , '99203' , '99204' , '99205' , '99211' , '99212' , '99213' , '99214' , '99215')) b
 ON a.TheMonth = b.[Month] AND a.ProcedureCode = b.[Type]
IP IP Logged
Post Reply Post New Topic
Printable version Printable version

Forum Jump
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum



This page was generated in 0.016 seconds.