Use each row of a dataset as series in line chart

Question

Post reply

Use each row of a dataset as series in line chart

robert.wiglesworth

SSCommitted

Points: 1790
More actions
March 9, 2015 at 3:52 pm

#301932

I have a data set that returns 2 rows, one for admits, one for discharges. Each row contains the number of each (admits and discharges) each hour of the day. So the results of the data set would look something like this:
recordtype h0 h1 h2 h3 h4...
admits 2 6 4 1 3...
discharges 6 3 4 2 9...
I want to make a line chart that uses each of the rows to make a line. So there would be like a blue line representing the admits and a red line representing the discharges, showing the numbers for each hour.
Hope that makes sense and I appreciate any help.
Thanks!

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

pietlinden SSC Guru Points: 63512 More actions · Answer 1

Robert,

I'm taking a few steps backwards...

Here's your data...

CREATE TABLE AdmitDischarge(

TheHour TINYINT,

ADType VARCHAR(10),

TheCount TINYINT

CONSTRAINT AD_PK PRIMARY KEY (TheHour,ADType)

);

GO

INSERT INTO AdmitDischarge(TheHour, ADType, TheCount)

VALUES (0,'A',2),(0,'D',6),

(1,'A',6),(1,'D',3),

(2,'A',4),(2,'D',4),

(3,'A',1),(3,'D',2),

(4,'A',3),(4,'D',9);

By the looks of things, you could do the counts in SSRS. Just use a matrix, and in the RowGroup use ADType('Admit' or 'Discharge'), and then in the column, put "TheHour". You could use SUM([TheCount]) in the value cell.

If you had raw values, you could just use COUNT([AdmissionID]) in the value cell.

robert.wiglesworth SSCommitted Points: 1790 More actions · Answer 2

Thanks for the reply. Unfortunately the data in my table looks different. It is a wider table with all hours in the day in each record. A sample looks like this:

select * from tableA where dateid = 20141001

dateidrecordtypeh0h1h2h3h4h5h6h7h8h9h10h11h12h13h14h15h16

20141001Discharges2121351102151102168181235243272199

20141001Admits384436212036903344536592112132146140150

pietlinden SSC Guru Points: 63512 More actions · Answer 3

Where do you get the data from?

You could transpose it in SSIS or Excel (that's what I used) and then it would be easy to use in SSRS. Then you would end up with 4 columns (dateID, Hour, Discharges, Admits). Graphs fine in Excel. Granted, that's not SSRS, but it should work fine.