Chart Q?

Question

Chart Q?

SQL_path

SSCrazy

Points: 2810
More actions
May 15, 2012 at 6:10 am

#274078

Hi,
I need to draw line chart in my report and the values look like below in my dataset. Everything is fine except for date 2/3/2012,
where there are 3 different values on the same day. rightnow my chart only shows only one value on date 2/3/2012.
Looking for creative ways to show other 2 values as well. Thanks for your help.

create table #ITEM
(
Dates datetime ,Amount decimal(18,5))
INSERT INTO #ITEM (Dates, Amount)
SELECT '1/1/2012' , 123
UNION ALL
SELECT '2/3/2012' , 115.5
UNION ALL
SELECT '2/3/2012' , 234.2
UNION ALL
SELECT '2/3/2012' , 6678.6
UNION ALL
SELECT '5/4/2012' , 1145
select * from #ITEM
drop table #ITEM

Viewing 7 posts - 1 through 7 (of 7 total)

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

Ant-Green SSC Guru Points: 113563 More actions · Answer 1

The only way I can think off is to do series break downs, but that wont be pretty as if multiple dates have multiple values you will get multiple lines.

You could change the grouping properties of the category but then you get funny shapes (see Fig1)

Series would give you something like that in Fig2

The thing with line charts is that it calculates an aggregated value to where the points of the line should go, so will be tricky unless you use something like a scatter chart and try and add in a best fit line.

SQL_path SSCrazy Points: 2810 More actions · Answer 2

Thank you Anthony.

I think the series option second graph might work for me. How should I achieve that. I dragged and dropped amount field in Series area but my graph does not look like yours.

Ant-Green SSC Guru Points: 113563 More actions · Answer 3

Sorry I didnt add in my code for that

You will need to add in a ROW_NUMBER() clause to generate a number then use the rownumber output as the series.

create table #ITEM

(

Dates datetime ,Amount decimal(18,5))

INSERT INTO #ITEM (Dates, Amount)

SELECT '1/1/2012' , 123

UNION ALL

SELECT '2/3/2012' , 115.5

UNION ALL

SELECT '2/3/2012' , 234.2

UNION ALL

SELECT '2/3/2012' , 6678.6

UNION ALL

SELECT '5/4/2012' , 1145

select dates, amount, row_number() over( partition by dates order by dates, amount desc) as series from #ITEM

drop table #ITEM

The only problem with this is that if you have multiple dates with multiple values, you will start to get more than 1 line as it joins the lines based on that series

For example, the data below will give you multiple lines on the graph. Which is why it might be best on a scatter chart with a best fit line

SELECT '1/1/2012' , 123

UNION ALL

SELECT '1/1/2012' , 17654

UNION ALL

SELECT '1/1/2012' , 15654

UNION ALL

SELECT '2/3/2012' , 115.5

UNION ALL

SELECT '2/3/2012' , 234.2

UNION ALL

SELECT '2/3/2012' , 6678.6

UNION ALL

SELECT '5/4/2012' , 1145

SQL_path SSCrazy Points: 2810 More actions · Answer 4

Thank you Anthony,

I just tried that and saw what you are talking about

Actually there are some more details to the dataset. Please see below. there are multiple charts in my report for each item description, for instance price , shape, size...all will have different charts date being x axis and amount being y axis. I have been able to draw scattered graph for price chart however for the best fit line, I do not have a clue how to draw that.

How can I draw a best fit line for instance.. price

create table #ITEM

(

Itemid int, Item_Desc varchar(50),Dates datetime ,Amount decimal(18,5))

INSERT INTO #ITEM (Itemid , Item_Desc, Dates, Amount)

SELECT 1,'price','1/1/2012' , 123

UNION ALL

SELECT 2,'shape','2/3/2012' , 115.5

UNION ALL

SELECT 3,'size','2/3/2012' , 234.2

UNION ALL

SELECT 4,'type','2/3/2012' , 6678.6

UNION ALL

SELECT 5,'volume','5/4/2012' , 1145

union all

SELECT 1,'price','2/3/2012' , 124

UNION ALL

SELECT 2,'shape','2/13/2012' , 118.5

UNION ALL

SELECT 3,'size','2/13/2012' , 564.2

UNION ALL

SELECT 4,'type','2/13/2012' , 678.6

UNION ALL

SELECT 5,'volume','5/14/2012' , 145

union all

SELECT 1,'price','2/3/2012' , 224

UNION ALL

SELECT 2,'shape','2/14/2012' , 218.5

UNION ALL

SELECT 3,'size','2/15/2012' , 574.2

UNION ALL

SELECT 4,'type','2/16/2012' , 648.6

UNION ALL

SELECT 5,'volume','5/17/2012' , 745

select * from #ITEM

drop table #ITEM

Ant-Green SSC Guru Points: 113563 More actions · Answer 5

Well thats the bit I am struggling with, as I thought of adding an extra chart area which is of a line chart type which does an average on the data to get a best fit but not been able to get it to work right just yet. Hopefully you will have a bit more time on it than I have to hopefully get it working, if not as and when I get a spare moment I will keep digging.

SQL_path SSCrazy Points: 2810 More actions · Answer 6

Thank you Anthony. Greatly appreciate your help on this.