Matrix and date colum header

Hi all,
I am trying to create a "simple" matrix.
I have:
--
| DateRange |
---
| Department | sum(value) |
---
I am giving the matrix an sql statement that returns all the values
and dates for a particular department on a YEARLY output.
So what I'm trying to do is have it so I get something like:
2006-2007 2005-2006
Department A 123 112
Department B 455 333
Department C 334 444
Department D 123 333
Department E 444 232
ie, have it sum the values on a year interval. I could do this easily
in Crystal, but am unsure as to how to do it in Reporting Services.
At present Im getting a column header for each date value resturned in
my SQL - which is not what I want!
eg:
01/01/2007 01/03/2007 04/03/2006
A 123 333
B 222
C 1222
How do I group the column header by year'
Any info would be much appreciated!!!!You can have year(date) but it gives you the year, so what you can do is use
a case statement in your query to seperate 2006-2007, 2005-2006 ans so on..
and use it in the matrix.
Amarnath
"David Conte" wrote:
> Hi all,
> I am trying to create a "simple" matrix.
> I have:
> --
> | DateRange |
> ---
> | Department | sum(value) |
> ---
> I am giving the matrix an sql statement that returns all the values
> and dates for a particular department on a YEARLY output.
> So what I'm trying to do is have it so I get something like:
>
> 2006-2007 2005-2006
> Department A 123 112
> Department B 455 333
> Department C 334 444
> Department D 123 333
> Department E 444 232
> ie, have it sum the values on a year interval. I could do this easily
> in Crystal, but am unsure as to how to do it in Reporting Services.
> At present Im getting a column header for each date value resturned in
> my SQL - which is not what I want!
> eg:
> 01/01/2007 01/03/2007 04/03/2006
> A 123 333
> B 222
> C 1222
> How do I group the column header by year'
> Any info would be much appreciated!!!!
>|||On Feb 5, 8:21 pm, Amarnath <Amarn...@.discussions.microsoft.com>
wrote:
> You can have year(date) but it gives you the year, so what you can do is use
> a case statement in your query to seperate 2006-2007, 2005-2006 ans so on..
> and use it in the matrix.
> Amarnath
>
Hi Amarnath,
Thanks for the reply. However I'm not sure how to tackle this by
using a case statement?
ie, not sure how to set it up to get it to seperate 2006-2007 etc?
Here is my sql so far:
SELECT MedicareItemKey, TestDepartments.Code as Department,
TransactionAmount, DateServiced
FROM InvoiceTransactions
INNER JOIN TestDepartments ON InvoiceTransactions.DepartmentKey =TestDepartments.[Key]
WHERE MedicareItemKey IS NOT NULL
AND ((InvoiceTransactions.[DateServiced] >= '01-
Jan-'+convert(char,year(getdate())-0))
AND (InvoiceTransactions.[DateServiced] < '01-
Jan-'+convert(char,year(getdate())+1)))
ORDER BY TestDepartments.Code, MedicareItemKey, DateServiced
As you can see above, the DateServiced column is the one I am having
issues with in my matrix.
Thanks!|||David,
I have create a small table with data
" create table INV (dept varchar(10),Amt numeric(10,2), dDate datetime)"
and tried this sql, according to your conditions you can modify.
SELECT Dept, SUM(Amt) As Amt,
(case when year(Inv.dDate) between 2004 and 2005 then '2004-2005'
when year(Inv.dDate) between 2005 and 2006 then '2005-2006'
when year(Inv.dDate) between 2006 and 2007 then '2006-2007'
end) as Dyear
FROM Inv
Group By Dept, ddate
order by Dept
Just open a report and place the matrix and drag and drop in the respective
column, you will get your desired results.
Let me know for any problems.
Amarnath
"David Conte" wrote:
> On Feb 5, 8:21 pm, Amarnath <Amarn...@.discussions.microsoft.com>
> wrote:
> > You can have year(date) but it gives you the year, so what you can do is use
> > a case statement in your query to seperate 2006-2007, 2005-2006 ans so on..
> > and use it in the matrix.
> >
> > Amarnath
> >
> Hi Amarnath,
> Thanks for the reply. However I'm not sure how to tackle this by
> using a case statement?
> ie, not sure how to set it up to get it to seperate 2006-2007 etc?
> Here is my sql so far:
> SELECT MedicareItemKey, TestDepartments.Code as Department,
> TransactionAmount, DateServiced
> FROM InvoiceTransactions
> INNER JOIN TestDepartments ON InvoiceTransactions.DepartmentKey => TestDepartments.[Key]
> WHERE MedicareItemKey IS NOT NULL
> AND ((InvoiceTransactions.[DateServiced] >= '01-
> Jan-'+convert(char,year(getdate())-0))
> AND (InvoiceTransactions.[DateServiced] < '01-
> Jan-'+convert(char,year(getdate())+1)))
> ORDER BY TestDepartments.Code, MedicareItemKey, DateServiced
> As you can see above, the DateServiced column is the one I am having
> issues with in my matrix.
> Thanks!
>|||On Feb 6, 5:14 pm, Amarnath <Amarn...@.discussions.microsoft.com>
wrote:
> David,
> I have create a small table with data
> " create table INV (dept varchar(10),Amt numeric(10,2), dDate datetime)"
> and tried this sql, according to your conditions you can modify.
> SELECT Dept, SUM(Amt) As Amt,
> (case when year(Inv.dDate) between 2004 and 2005 then '2004-2005'
> when year(Inv.dDate) between 2005 and 2006 then '2005-2006'
> when year(Inv.dDate) between 2006 and 2007 then '2006-2007'
> end) as Dyear
> FROM Inv
> Group By Dept, ddate
> order by Dept
> Just open a report and place the matrix and drag and drop in the respective
> column, you will get your desired results.
> Let me know for any problems.
> Amarnath
> "David Conte" wrote:
> > On Feb 5, 8:21 pm, Amarnath <Amarn...@.discussions.microsoft.com>
> > wrote:
> > > You can have year(date) but it gives you the year, so what you can do is use
> > > a case statement in your query to seperate 2006-2007, 2005-2006 ans so on..
> > > and use it in the matrix.
> > > Amarnath
> > Hi Amarnath,
> > Thanks for the reply. However I'm not sure how to tackle this by
> > using a case statement?
> > ie, not sure how to set it up to get it to seperate 2006-2007 etc?
> > Here is my sql so far:
> > SELECT MedicareItemKey, TestDepartments.Code as Department,
> > TransactionAmount, DateServiced
> > FROM InvoiceTransactions
> > INNER JOIN TestDepartments ON InvoiceTransactions.DepartmentKey => > TestDepartments.[Key]
> > WHERE MedicareItemKey IS NOT NULL
> > AND ((InvoiceTransactions.[DateServiced] >= '01-
> > Jan-'+convert(char,year(getdate())-0))
> > AND (InvoiceTransactions.[DateServiced] < '01-
> > Jan-'+convert(char,year(getdate())+1)))
> > ORDER BY TestDepartments.Code, MedicareItemKey, DateServiced
> > As you can see above, the DateServiced column is the one I am having
> > issues with in my matrix.
> > Thanks!
Thanks Amarnath,
Appreciate the reply.
I think that clears things up for me, I will give it a go!
Cheers!