asked on

Complex SSRS Chart

I have this Query That gives me the my total by month. based on
WHERE (Account.Acct LIKE '10%')
      AND (AcctHist.LedgerID = 'ACTUAL')
      AND (AcctHist.CpnyID = 'INTEGRA')
      AND MonthStartDate BETWEEN DATEADD(MONTH,-12,GETDATE()) AND GETDATE()

For this i need account.acct to be the sum of 1000 to 1499 but minus the account.acct 2000 to 2399

SELECT Acct, LedgerID, CpnyID, 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, LedgerID, CpnyID, 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, LedgerID, CpnyID, 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, LedgerID, CpnyID, 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, LedgerID, CpnyID, 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, LedgerID, CpnyID, 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, LedgerID, CpnyID, 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, LedgerID, CpnyID, 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, LedgerID, CpnyID, 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, LedgerID, CpnyID, 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, LedgerID, CpnyID, 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, LedgerID, CpnyID, 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

Nico Bontenbal

I'm not sure I understand your question. Do you need the sum of MonthValue where Acct between 1000 and 1499 minus the sum of MonthValue where Acct between 2000 to 2399?
If so you could use something like this:

Select
MonthNum,
(select sum(t2.MonthValue) from #TempTable t2 where  t2.Acct between 1000 and 1499 and t2.MonthNum = t1.MonthNum)
-(select sum(t2.MonthValue) from #TempTable t2 where t2.Acct between 2000 to 2399 and t2.MonthNum = t1.MonthNum) as TheSum
from
#TempTable t1

Open in new window

(haven't tested it because I might be answering the wrong question)

Gabe Lebron

ASKER

yes thats what i need .. but where do i add that in the query

Nico Bontenbal

If you replace line 75-82 with this code:

Select
	AcctHist.MonthNum,
	AcctHist.CpnyID,
	AcctHist.FiscYr,
	(select sum(tmp.MonthValue) from #TempTable tmp where  tmp.Acct between 1000 and 1499 and tmp.MonthNum = AcctHist.MonthNum and tmp.CpnyID = AcctHist.CpnyID and tmp.FiscYr = AcctHist.FiscYr)
	-(select sum(tmp.MonthValue) from #TempTable tmp where tmp.Acct between 2000 and 2399 and tmp.MonthNum = AcctHist.MonthNum and tmp.CpnyID = AcctHist.CpnyID and tmp.FiscYr = AcctHist.FiscYr) as TheSum
from
	#TempTable AcctHist
group by
	AcctHist.MonthNum,
	AcctHist.CpnyID,
	AcctHist.FiscYr

Open in new window

does that come close to what you need?
If not can you explain to me what you need. In your original question you say "the sum of 1000 to 1499 but minus the account.acct 2000 to 2399" but I suppose you want this grouped by Month/Cpny/FiscYr (or some other combination). From the query I can see what data is in the #TempTable. But I'm not sure what result you want from the data in this #TempTable.

Gabe Lebron

ASKER

Just looking at hte code .. dont i need line 82 so i can chart by month?

Nico Bontenbal

Since I don't know exactly what result you are looking for I can't answer this question. But if you don't get the right result with my query, but you do get the right result when you add a where clause and line 82 to the query then adding it seems the better option.

Gabe Lebron

ASKER

Ok this is how i made the orginal Query from this post . look at the end and the chart i created. I used Account.acct LIKE10% for this chart

I want to create the same chart only this time I want to use column Account.ACCT like you said (sum of MonthValue where Acct between 1000 and 1499 minus the sum of MonthValue where Acct between 2000 to 2399)

So i can use the MonthStartdate column and Month vaule column to graph my chart in the same manner

https://www.experts-exchange.com/questions/27250755/Creating-a-chart-in-SSRS.html

Nico Bontenbal

There is a different way of doing this. Maybe you are more comfortable with this solution. When you use this query:

SELECT *,
	case when Account.Acct between 1000 and 1499 then MonthValue else 0 end as Val1000,
	case when Account.Acct between 2000 and 2399 then MonthValue else 0 end as Val2000
FROM Account AS Account 
	INNER JOIN #TempTable AS AcctHist
		ON Account.Acct = AcctHist.Acct
WHERE  (AcctHist.LedgerID = 'ACTUAL') 
	AND (AcctHist.CpnyID = 'INTEGRA')
	AND MonthStartDate BETWEEN DATEADD(MONTH,-12,GETDATE()) AND GETDATE()

Open in new window

You'll get two extra columns Val1000 and Val2000. In SSRS you can base the value of your chart on Val1000-Val2000.

Or you can use this as your query:

SELECT *,
	case when Account.Acct between 1000 and 1499 then MonthValue when Account.Acct between 2000 and 2399 then MonthValue*-1 else 0 end as ChartVal
FROM Account AS Account 
	INNER JOIN #TempTable AS AcctHist
		ON Account.Acct = AcctHist.Acct
WHERE  (AcctHist.LedgerID = 'ACTUAL') 
	AND (AcctHist.CpnyID = 'INTEGRA')
	AND MonthStartDate BETWEEN DATEADD(MONTH,-12,GETDATE()) AND GETDATE()

Open in new window

And use the sum of ChartVal as the value of your chart.

If this doesn't help, I'm out of options. Maybe one of the experts of the other question will have an answer on Monday.

Gabe Lebron

ASKER

I tried both as get this error when i run

ASKER CERTIFIED SOLUTION

Nico Bontenbal

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Gabe Lebron

ASKER

Got it.. that seem to work. I have to check the numbers i will let you know how it works.

Gabe Lebron

ASKER

they worked like a charm thank you