Avatar of Gabe Lebron
Gabe Lebron
Flag 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

 1
Microsoft SQL ServerMicrosoft SQL Server 2005SSRS

Avatar of undefined
Last Comment
Gabe Lebron

8/22/2022 - Mon
aelliso3


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

ASKER
Just a standard Bar chart ...  1
aelliso3

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)?
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
aelliso3

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

Gabe Lebron

ASKER
yes dont worry about 12 its blank
Gabe Lebron

ASKER
ok let me try and get back you
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Gabe Lebron

ASKER
Well I am not sure where i would use this

I ran into this error  1
aelliso3

What happens when you click OK on the error and then click Execute?

Are you running on SQL 2005 or 2008?
aelliso3

Sorry ... I mean "Ignore" on the errer
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
Gabe Lebron

ASKER
This is what i get if i Ignore 1
aelliso3

Which version of SQL are you using?
Gabe Lebron

ASKER
SQL 2005
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
aelliso3

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.
Gabe Lebron

ASKER
well tried to run it in stuido to see and got this

 1
Gabe Lebron

ASKER
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 started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
Gabe Lebron

ASKER
I forgot to change database before i ran it.. I get same error on Management studio  1
aelliso3

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 ...
Gabe Lebron

ASKER
sweet thanks
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
aelliso3

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

Gabe Lebron

ASKER
Got this  1
ASKER CERTIFIED SOLUTION
aelliso3

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Gabe Lebron

ASKER
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?
Your help has saved me hundreds of hours of internet surfing.
fblack61
aelliso3

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)
Gabe Lebron

ASKER
ok great .. i will check the numbers in the morning.  let you know how it works..  
Gabe Lebron

ASKER
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
1
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
aelliso3

Looks good gotti777 ...
Gabe Lebron

ASKER
I have a new question you might be able to take careof quick  

https://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/MS-SQL_Reporting/Q_27253836.html