question

sanjeevarora avatar image
sanjeevarora asked

Line Graph in VS2010

I am getting the Pivot Table with following query. screen capture of data is attached and desired graph (which I am not getting thru report viewer in VS2010. SELECT div_name = ( SELECT div_name FROM Div_Master d WHERE d.Div_Id = x.Div_Code ) , sbu_name = ( SELECT sbu_name FROM Mst_SBUMaster s WHERE s.SBU_Id = x.SBU_Code ) , * FROM ( SELECT Div_Code , SBU_Code , DATENAME(MONTH, Years) AS mon , Amount FROM trn_Expense WHERE SBU_Code LIKE '%' AND Div_Code LIKE '%' AND [dbo].[fnGetFinYear](Years) LIKE '2015-16' ) AS s PIVOT ( SUM(amount) FOR mon IN ( April, May, June, July, August, September, October, November, December, January, February, March ) ) AS x Please advise how to plot line graph for this table in C#.![alt text][1] [1]: /storage/temp/2684-line-graph-help.jpg
report-viewer
line-graph-help.jpg (164.7 KiB)
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
GPO avatar image
GPO answered
Your query will be a lot simpler, faster and more readable if you do the following: - Let SSRS do the pivoting (if you add the data to the chart the way I suggested in your previous post, SSRS will take care of that for you). - Use a calendar table (google it if you don't know what it is, it's called a "date dimension" in data warehousing). - Don't use scalar functions in your WHERE clause if you have better alternatives (and you usually will). Your query might then look a bit like this (obviously this code is untested): DECLARE @fin_year AS CHAR(7); SET @fin_year = '2015-16'; SELECT cal.fin_year , cal.month_name , div.div_name , sbu.sbu_name , SUM(ex.amount) AS Amount FROM dbo.trn_Expense ex JOIN dbo.calendar cal ON ex.my_date = cal.census_date LEFT JOIN dbo.Div_Master div ON ex.Div_Code = div.Div_Id LEFT JOIN Mst_SBUMaster sbu ON ex.SBU_Code = sbu.SBU_Id WHERE cal.fin_year = @fin_year GROUP BY cal.fin_year , cal.month_name , div.div_name , sbu.sbu_name; Then add the fields to the chart the way I suggested in your previous post.
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Sorry, I didn't see the bit about c#. Hopefully the principal remains the same.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.