SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


What is the best way to create a SSRS 2005 Line Chart Report for a 12 month period?


What is the best way to create a SSRS 2005 Line Chart Report for a 12 month period?

Author
Message
Amedeo Feroce
Amedeo Feroce
SSC Rookie
SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)

Group: General Forum Members
Points: 45 Visits: 36
I'm looking for advice on how to create a SQL Server 2005 query and line chart report for SSRS 2005.

I need to display the peak number of patients assigned to a medical practice each month for a 12 month period based on the end-user selecting a single month and year.

I've previously created a report that displays all patients assigned to the practice for any single month but I’m looking for advice on how to how to produce a resultset that shows the peak number of patients each month for a 12 month period. I thought about creating a query that returns the peak count for each month (based on my previously created report which displays all patients assigned to the practice for any single month) and then use a UNION statement to join all 12 months but I'm sure that isn't the most efficient way to do this. The other challenge with this approach (twelve resultsets combined via a UNION) is that the end-user needs to be able to select any month and year for the parameter and the report needs to display the 12 month period based on the month selected (the month selected would be the last month of the 12 month period).

For the report I’ve previously created that displays all patients assigned to the practice for any single month, the WHERE statement filters the resultset on two fields:
•Start Date - The date the patient was assigned to the practice. This field is never null or blank.
•End Date - The date the patient left the practice. This field can be null or blank as active patients assigned to the practice do not have an End Date. When the patient leaves the practice, the date the patient left is populated in this field.

Using these two fields I can return all patients assigned to the practice during Nov 2012 by looking for patients that meet the following criteria:
•start date prior to 11/30/2012 (using the last day of the month selected ensures patients added mid-month would be included)

AND
•end date is null or blank (indicates the patient is active) OR the end date is between 11/1/2012 -11/30/2012 (returns patients that leave during the month selected)



Regarding the query I need to create for the report that displays the peak count each month for 12 months, I'm looking for advice on how to count patients for each month the patient is assigned to the practice if the patient has been assigned for several months (which applies to most patients). Examples are:
•John Doe has a start date of 6/01/2012 and an End Date of 10/07/2012
•Sally Doe has a start date of 8/4/2012 and no End Date (the patient is still active)
•Jimmy Doe has a start of 7/3/2012 and an End Date of 9/2/2012

Given these examples how would I include John Doe in the peak monthly count each month for May - October, Sally Doe in the peak monthly count for August - December and Jimmy Doe in the peak monthly count for July – Sept if the end-user running the report selected December 2012 as the parameter?

Given the example above and the fact I'm creating a line chart I think the best way to create this report would be a resultset that looks like this:

Patient Name Months Assigned

John Doe June 2012

John Doe July012

John Doe Aug 2012

John Doe Sept 2012

John Doe Oct 2012

Sally Doe Aug 2012

Sally Doe Sept 2012

Sally Doe Oct 2012

Sally Doe Nov 2012

Sally Doe Dec 2012

Jimmy Doe July 2012

Jimmy Doe Aug 2012

Jimmy Doe Sept 2012


From the resultset above I could create another resultset that would count\group on month and year to return the peak count for each month:

June 2012 - 1

July 2012 – 2

Aug 2012 - 3

Sept 2012 - 3

Oct 2012 - 2

Nov 2012 - 1

Dec 2012 - 1


The resultset that displays the peak count for each month would be used to create the line chart (month would be the X axis and the count would be the y axis).


Does this sound like the best approach?

If so, any advice on how to create the resultset that lists each patient and each month they were assigned to the practice would be greatly appreciated. I do not have permissions to create SPs or Functions within the database but I can create temp tables.

I know how to create the peak monthly count query (derived from the query that lists each patient and month assigned) as well as the line chart.

Any advice or help is greatly appreciated.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search