SSRS show trend chart by up and down arrow over column chart per series groups

Question

SSRS show trend chart by up and down arrow over column chart per series groups

joydev.dey

SSC Enthusiast

Points: 179
More actions
June 7, 2016 at 3:22 am

#311047

chart data
Values- No of Itemsold
Group-Product(ProductA,ProductB,ProductC...)
Series -Year(2013,2014,2015,2016)
I have to show the trend of the item sold for each group by a up and down arrow.
Let say item sold for ProductA in year 2013 is 15,2014 is 18,2015 is 20 and 2016 is 23.So I have to display a up arrow for this case.
Similarly I have to do the same for all the products.

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

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

Thom A SSC Guru Points: 99009 More actions · Answer 1

How are you going to show arrows in a chart? Visually this should be pretty apparently if you are showing it in a chart, as the line/bar will be higher up the axis to the previous record is the value is up.

Do you actually mean to display this in a table? Will you always be comparing the year to the previous year?

Without seeing how your data is formatted, it's difficult for me to really comment much further. Are all years provided in a single row, or does each item have a separate entry for each year? If there is a single row, you can simply use an expression statement using the IIF to display your arrows.

if each year has a seperate row entry, and you have access to the SQL (running on SQL 2012+) I would suggest using Window functions (LEAD/LAG) to get the prior year's sales. If you don't, then SSRS has a Previous() function in expressions. Just make sure your data is sorted correctly first (you'll also need to include logic to ensure you don't grab a different item's sales figures). Then you can use the same process to create your IIF.

Hope that puts you on the right path.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

joydev.dey 30829 Mr or Mrs. 500 Points: 546 More actions · Answer 2

It is a column chart.

I have to display the trend for each group.Years can be multiple.For each group I have to display the arrow (up or down) depending upon the noofitemsold from 1st year to Last Year.

Thom A SSC Guru Points: 99009 More actions · Answer 3

CAn you supply some opf the folloowing, so I can help further, your previous comment didn't really build on it.

Do you have access to the SQL serv Query?

What version of SQL Server are you running

A sample table create statement with some example data inserted.

Sample select statement from your sample table

Example of how you want your chart/table to look (this can be in Excel, Paint, just something visual).

Please remember to include the SQL in brackets. It makes everyone's life a lot easier.

Thanks.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

joydev.dey 30829 Mr or Mrs. 500 Points: 546 More actions · Answer 4

Year Product Itemsold

2012 ProductA 2

2013 ProductA 3

2014 ProductA 4

2012 ProductB 8

2013 ProductB 6

2014 ProductB 5

2012 ProductC 7

2013 ProductC 8

2014 ProductC 9

My table will return data like above.This is a dummy data.So in my column chart

Value - Itemsold (vertical axis)

Category Group-Product(horizontal axis)

Series- Year

for ProductA item sold increases from 2 to 4 so it display up arrow for the ProductA.

for ProductB item sold decreases from 8 to 5 so it display down arrow for the ProductB.

for ProductC item sold increases from 7 to 9 so it display up arrow for the ProductC.

Thom A SSC Guru Points: 99009 More actions · Answer 5

You only answered one of my questions there, and didn't really, as there's no create statement, select statement, etc. To help you, you need to help me. Please answer my previous questions.

Thanks.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

joydev.dey 30829 Mr or Mrs. 500 Points: 546 More actions · Answer 6

Yes I have access to write query.

Sql server 2012.

Table script:

select Year,Product,count(itemsold)

from sales

group by Product,year

create table sales(

[productID] [int] IDENTITY(1,1) NOT NULL,

[Year] [int] NULL,

[Product] [nvarchar](50) NULL,

[itemsold] [int] NULL,

)

pietlinden SSC Guru Points: 63198 More actions · Answer 7

Welcome to SSC!

Since you're new and haven't quite figured out how to ask a good question, please help us to help you by reading this article[/url] and following the instructions outlined in it. If you want really good help, you need to provide more information - the article explains how to do all that. Once you've read the article, everything should become clear.

Thanks!