Link to home
Start Free TrialLog in
Avatar of Gabe Lebron
Gabe LebronFlag for United States of America

asked on

Creating a chart in SSRS

I am trying to create a Chart In ssrs the Shows my end balance each month for the last 12
I asume i need to tweak my code  right now its pulling everything back to 2002 when we started this data base.   I only want to pull last 12 months.  Then all i want to show are 12 bars showing last 12 months of my ending balance.

00 being jan  01 being feb ans so on
   
SELECT     AcctHist.Acct, AcctHist.FiscYr, AcctHist.YtdBal00, AcctHist.YtdBal01, AcctHist.YtdBal02, AcctHist.YtdBal03, AcctHist.YtdBal04, AcctHist.YtdBal05, 
                      AcctHist.YtdBal06, AcctHist.YtdBal07, AcctHist.YtdBal08, AcctHist.YtdBal09, AcctHist.YtdBal10, AcctHist.YtdBal11, AcctHist.YtdBal12
FROM         Account AS Account INNER JOIN
                      AcctHist AS AcctHist ON Account.Acct = AcctHist.Acct
WHERE     (Account.Acct LIKE '10%') AND (AcctHist.LedgerID = 'ACTUAL') AND (AcctHist.CpnyID = 'INTEGRA')

Open in new window


from what I Know is FiscYr   Is my year column   and 00 is jan 01 is Feb 02 is march and so on.. So iwant ti to only go back 12 months give me the total for those months

 User generated image
Avatar of aelliso3
aelliso3
Flag of United States of America image


What kind of chart are you looking for ... Bar Chart, Line, etc?
Avatar of Gabe Lebron

ASKER

Just a standard Bar chart ...  User generated image
I think I have a pretty simple solution, but I ran into a bit of a snag of the field names:

If YtdBal00 = January ... what is YtdBal12  (December would be YtdBal11)?
Here is the thought ... You can UNPIVOT the columns, then use the last 2 digits to determine the month.

Test the query below and see if we're on the same page ...
SELECT FiscYr
	, RIGHT([YtdBalName],2) AS BalanceMonth
	, CONVERT(DATETIME, CONVERT(VARCHAR(2),RIGHT([YtdBalName],2) + 1) + '/01/' + CONVERT(VARCHAR(4),FiscYr)) AS MonthDate
	, [YtdBal]
FROM( 
	SELECT     
		AcctHist.Acct
		, AcctHist.FiscYr
		, AcctHist.YtdBal00
		, AcctHist.YtdBal01
		, AcctHist.YtdBal02
		, AcctHist.YtdBal03
		, AcctHist.YtdBal04
		, AcctHist.YtdBal05
		, AcctHist.YtdBal06
		, AcctHist.YtdBal07
		, AcctHist.YtdBal08
		, AcctHist.YtdBal09
		, AcctHist.YtdBal10
		, AcctHist.YtdBal11
		--, AcctHist.YtdBal12
	FROM Account AS Account 
		INNER JOIN AcctHist AS AcctHist 
			ON Account.Acct = AcctHist.Acct
	WHERE     (Account.Acct LIKE '10%') 
		AND (AcctHist.LedgerID = 'ACTUAL') 
		AND (AcctHist.CpnyID = 'INTEGRA')
) p
UNPIVOT
(YtdBal FOR YtdBalName IN
	(	YtdBal00
		, YtdBal01
		, YtdBal02
		, YtdBal03
		, YtdBal04
		, YtdBal05
		, YtdBal06
		, YtdBal07
		, YtdBal08
		, YtdBal09
		, YtdBal10
		, YtdBal11
		--, YtdBal12
	)
) as unpvt
WHERE CONVERT(DATETIME, CONVERT(VARCHAR(2),RIGHT([YtdBalName],2) + 1) + '/01/' + CONVERT(VARCHAR(4),FiscYr))
	BETWEEN DATEADD(month, -12, GETDATE()) AND GETDATE()
ORDER BY FiscYr 
	, RIGHT([YtdBalName],2)

Open in new window

yes dont worry about 12 its blank
ok let me try and get back you
Well I am not sure where i would use this

I ran into this error  User generated image
What happens when you click OK on the error and then click Execute?

Are you running on SQL 2005 or 2008?
Sorry ... I mean "Ignore" on the errer
This is what i get if i Ignore User generated image
Which version of SQL are you using?
SQL 2005
Do you have Management Studio to try it. SQL 2005 does have the Unpivot command, so it must just be the graphical part of visual studios. You can also try just pasting it into the Dataset and see if you get an error.
well tried to run it in stuido to see and got this

 User generated image
I am not sure Unpivot does .. But dont i do the graphical part in the layout once the data is how i want it?
I forgot to change database before i ran it.. I get same error on Management studio  User generated image
OK ... you must have the compatibility level for that DB set to SQL 2000. That's ok ... give me just a few minutes and we can do it without using unpivot ...
sweet thanks
Try this ...
SELECT Acct, FiscYr, 1 AS MonthNum, '01/01/' + CONVERT(CHAR(4), FiscYr) AS MonthStartDate, YtdBal00 AS MonthValue
INTO #TempTable
FROM AcctHist AS AcctHist 
WHERE FiscYr BETWEEN YEAR(DATEADD(MONTH,-12,GETDATE())) AND YEAR(GETDATE())
	AND (AcctHist.LedgerID = 'ACTUAL') 
	AND (AcctHist.CpnyID = 'INTEGRA')
UNION
SELECT Acct, FiscYr, 2 AS MonthNum, '02/01/' + CONVERT(CHAR(4), FiscYr) AS MonthStartDate, YtdBal01 AS MonthValue
FROM AcctHist AS AcctHist 
WHERE FiscYr BETWEEN YEAR(DATEADD(MONTH,-12,GETDATE())) AND YEAR(GETDATE())
	AND (AcctHist.LedgerID = 'ACTUAL') 
	AND (AcctHist.CpnyID = 'INTEGRA')
UNION
SELECT Acct, FiscYr, 3 AS MonthNum, '03/01/' + CONVERT(CHAR(4), FiscYr) AS MonthStartDate, YtdBal02 AS MonthValue
FROM AcctHist AS AcctHist
WHERE FiscYr BETWEEN YEAR(DATEADD(MONTH,-12,GETDATE())) AND YEAR(GETDATE())
	AND (AcctHist.LedgerID = 'ACTUAL') 
	AND (AcctHist.CpnyID = 'INTEGRA')
UNION
SELECT Acct, FiscYr, 4 AS MonthNum, '04/01/' + CONVERT(CHAR(4), FiscYr) AS MonthStartDate, YtdBal03 AS MonthValue
FROM AcctHist AS AcctHist
WHERE FiscYr BETWEEN YEAR(DATEADD(MONTH,-12,GETDATE())) AND YEAR(GETDATE())
	AND (AcctHist.LedgerID = 'ACTUAL') 
	AND (AcctHist.CpnyID = 'INTEGRA')
UNION
SELECT Acct, FiscYr, 5 AS MonthNum, '05/01/' + CONVERT(CHAR(4), FiscYr) AS MonthStartDate, YtdBal04 AS MonthValue
FROM AcctHist AS AcctHist
WHERE FiscYr BETWEEN YEAR(DATEADD(MONTH,-12,GETDATE())) AND YEAR(GETDATE())
	AND (AcctHist.LedgerID = 'ACTUAL') 
	AND (AcctHist.CpnyID = 'INTEGRA')
UNION
SELECT Acct, FiscYr, 6 AS MonthNum, '06/01/' + CONVERT(CHAR(4), FiscYr) AS MonthStartDate, YtdBal05 AS MonthValue
FROM AcctHist AS AcctHist
WHERE FiscYr BETWEEN YEAR(DATEADD(MONTH,-12,GETDATE())) AND YEAR(GETDATE())
	AND (AcctHist.LedgerID = 'ACTUAL') 
	AND (AcctHist.CpnyID = 'INTEGRA')
UNION
SELECT Acct, FiscYr, 7 AS MonthNum, '07/01/' + CONVERT(CHAR(4), FiscYr) AS MonthStartDate, YtdBal06 AS MonthValue
FROM AcctHist AS AcctHist 
WHERE FiscYr BETWEEN YEAR(DATEADD(MONTH,-12,GETDATE())) AND YEAR(GETDATE())
	AND (AcctHist.LedgerID = 'ACTUAL') 
	AND (AcctHist.CpnyID = 'INTEGRA')
UNION
SELECT Acct, FiscYr, 8 AS MonthNum, '08/01/' + CONVERT(CHAR(4), FiscYr) AS MonthStartDate, YtdBal07 AS MonthValue
FROM AcctHist AS AcctHist 
WHERE FiscYr BETWEEN YEAR(DATEADD(MONTH,-12,GETDATE())) AND YEAR(GETDATE())
	AND (AcctHist.LedgerID = 'ACTUAL') 
	AND (AcctHist.CpnyID = 'INTEGRA')
UNION
SELECT Acct, FiscYr, 9 AS MonthNum, '09/01/' + CONVERT(CHAR(4), FiscYr) AS MonthStartDate, YtdBal08 AS MonthValue
FROM AcctHist AS AcctHist 
WHERE FiscYr BETWEEN YEAR(DATEADD(MONTH,-12,GETDATE())) AND YEAR(GETDATE())
	AND (AcctHist.LedgerID = 'ACTUAL') 
	AND (AcctHist.CpnyID = 'INTEGRA')
UNION
SELECT Acct, FiscYr, 10 AS MonthNum, '10/01/' + CONVERT(CHAR(4), FiscYr) AS MonthStartDate, YtdBal09 AS MonthValue
FROM AcctHist AS AcctHist 
WHERE FiscYr BETWEEN YEAR(DATEADD(MONTH,-12,GETDATE())) AND YEAR(GETDATE())
	AND (AcctHist.LedgerID = 'ACTUAL') 
	AND (AcctHist.CpnyID = 'INTEGRA')
UNION
SELECT Acct, FiscYr, 11 AS MonthNum, '11/01/' + CONVERT(CHAR(4), FiscYr) AS MonthStartDate, YtdBal10 AS MonthValue
FROM AcctHist AS AcctHist
WHERE FiscYr BETWEEN YEAR(DATEADD(MONTH,-12,GETDATE())) AND YEAR(GETDATE())
	AND (AcctHist.LedgerID = 'ACTUAL') 
	AND (AcctHist.CpnyID = 'INTEGRA')
UNION
SELECT Acct, FiscYr, 12 AS MonthNum, '12/01/' + CONVERT(CHAR(4), FiscYr) AS MonthStartDate, YtdBal11 AS MonthValue
FROM AcctHist AS AcctHist
WHERE FiscYr BETWEEN YEAR(DATEADD(MONTH,-12,GETDATE())) AND YEAR(GETDATE())
	AND (AcctHist.LedgerID = 'ACTUAL') 
	AND (AcctHist.CpnyID = 'INTEGRA')


SELECT * 
FROM Account AS Account 
	INNER JOIN #TempTable AS AcctHist
		ON Account.Acct = AcctHist.Acct
WHERE   (Account.Acct LIKE '10%') 
	AND (AcctHist.LedgerID = 'ACTUAL') 
	AND (AcctHist.CpnyID = 'INTEGRA')
	AND MonthStartDate BETWEEN DATEADD(MONTH,-12,GETDATE()) AND GETDATE()



DROP TABLE #TempTable

Open in new window

Got this  User generated image
ASKER CERTIFIED SOLUTION
Avatar of aelliso3
aelliso3
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Ok that seem to work as in no errors..  So I guess I am using Monthvalue   as my vaule and MonthNum as my categories   I need to run the numbers by my cfo to make sure they are right.  

This will update to next month and drop a month when sept hit?
also i can just change the number to the month i asume?
Yes ... it is based on GETDATE() which is SQL code for the current date and will adjust accordingly.

As far as the numbers, you can use the numbers, or  you can use the MonthStartDate column and set the formatting to yyyy-mm or just month, or month name, etc. (endless options)
ok great .. i will check the numbers in the morning.  let you know how it works..  
Looks Great   Numbers look good also thanks.. heres is a Pic of my chart
Now I am working on captial i will open up a new tread would mind if you helped me on that
User generated image
Looks good gotti777 ...
I have a new question you might be able to take careof quick  

https://www.experts-exchange.com/questions/27253836/Complex-SSRS-Chart.html