matrix runningavalue

hi all,
i have a matrix within my report. the column's group has inside one cell
with a table with 4 groups. one of groups have an expresion with the
runningvalue function. the problem came when i've applicated a subtotal at
this column group.
to be more clear, my columns describe the year's month. when i use the
runningvalue and the subtotal, at the preview the first column (january)
gives me the right value, after that everything is wrong in that row.
it could be another functions alternative that may help me?
thank youthat runningvalue it's not much compatible with the matrix. because with or
without subtotal it doesn't work right. it is something i could put in its
place, with the same role?
thank you
"Mirela" wrote:
> hi all,
> i have a matrix within my report. the column's group has inside one cell
> with a table with 4 groups. one of groups have an expresion with the
> runningvalue function. the problem came when i've applicated a subtotal at
> this column group.
> to be more clear, my columns describe the year's month. when i use the
> runningvalue and the subtotal, at the preview the first column (january)
> gives me the right value, after that everything is wrong in that row.
> it could be another functions alternative that may help me?
> thank yousql

Matrix question

I have a matrix issue with the colum ordering. What I want is for the
month-yr columns to be in order, starting with 1-2006 --> 12-2006 but I
cannot determine how to order them correctly. can someone point me in the
right direction?Brian,
In a matrix you probably have the month set up as a group. In the properties
of this group there is a sorting tap, I dont have a full picture of how you
set things up but I think choosing it to order by MonthNr should be the
thing to do.
regards
Perry
"Brian L" <BrianL@.discussions.microsoft.com> wrote in message
news:FF93F19C-A0A4-4BF6-98B9-482B0396F37C@.microsoft.com...
>I have a matrix issue with the colum ordering. What I want is for the
> month-yr columns to be in order, starting with 1-2006 --> 12-2006 but I
> cannot determine how to order them correctly. can someone point me in the
> right direction?|||thanks perry, i noticed that too. Problem is I want to arrange the columns
horizontally, not the values vertically. I'm still searching technet and BOL
for an answer. Thanks again.
"Perry" wrote:
> Brian,
> In a matrix you probably have the month set up as a group. In the properties
> of this group there is a sorting tap, I dont have a full picture of how you
> set things up but I think choosing it to order by MonthNr should be the
> thing to do.
> regards
> Perry
>
> "Brian L" <BrianL@.discussions.microsoft.com> wrote in message
> news:FF93F19C-A0A4-4BF6-98B9-482B0396F37C@.microsoft.com...
> >I have a matrix issue with the colum ordering. What I want is for the
> > month-yr columns to be in order, starting with 1-2006 --> 12-2006 but I
> > cannot determine how to order them correctly. can someone point me in the
> > right direction?
>
>|||Perry - Ignore my last post. What I wrote didn't make sense. You were on the
money. Question is, how do you write the expression to hard code the sort?
Guess I'll keep reading. Thanks.
"Perry" wrote:
> Brian,
> In a matrix you probably have the month set up as a group. In the properties
> of this group there is a sorting tap, I dont have a full picture of how you
> set things up but I think choosing it to order by MonthNr should be the
> thing to do.
> regards
> Perry
>
> "Brian L" <BrianL@.discussions.microsoft.com> wrote in message
> news:FF93F19C-A0A4-4BF6-98B9-482B0396F37C@.microsoft.com...
> >I have a matrix issue with the colum ordering. What I want is for the
> > month-yr columns to be in order, starting with 1-2006 --> 12-2006 but I
> > cannot determine how to order them correctly. can someone point me in the
> > right direction?
>
>|||Brian,
in reply to -- how do you write the expression to hard code the sort?
(I have 7 months working experience with RS2000 and RS2005, I am guessing we
are/were both confronted with the same issues to solve one way or the other,
you can decide if this means enough about knowledge :-) )
To my knowledge you can not hardcode into the group expression itself how to
sort anything (i never to that there). This can only be done in the sorting
tab, for me this does the trick in all cases.
Sometimes I may use something like fiscalyear, left(fiscalmonthname,3) in
the columns. In my query I also select the fiscalmonthnr. In the matrix I
choose to show the fiscalyear, fiscalmonthname (first 3 characters, this you
can solve either in de query, or in the report). The fiscalmonthname I sort
on the fiscalmonthnr ascending, which is selected in the dataset but not
shown in the report (just for sorting purposes).
If this reply misses your point (then i misunderstood something), then which
expression you mean and where do you set it?
Perry
"Brian L" <BrianL@.discussions.microsoft.com> wrote in message
news:5ABD05FF-2F13-4F0B-A5EC-18C949D0A915@.microsoft.com...
> Perry - Ignore my last post. What I wrote didn't make sense. You were on
> the
> money. Question is, how do you write the expression to hard code the sort?
> Guess I'll keep reading. Thanks.
> "Perry" wrote:
>> Brian,
>> In a matrix you probably have the month set up as a group. In the
>> properties
>> of this group there is a sorting tap, I dont have a full picture of how
>> you
>> set things up but I think choosing it to order by MonthNr should be the
>> thing to do.
>> regards
>> Perry
>>
>> "Brian L" <BrianL@.discussions.microsoft.com> wrote in message
>> news:FF93F19C-A0A4-4BF6-98B9-482B0396F37C@.microsoft.com...
>> >I have a matrix issue with the colum ordering. What I want is for the
>> > month-yr columns to be in order, starting with 1-2006 --> 12-2006 but I
>> > cannot determine how to order them correctly. can someone point me in
>> > the
>> > right direction?
>>|||Thanks Perry.
"Perry" wrote:
> Brian,
> in reply to -- how do you write the expression to hard code the sort?
> (I have 7 months working experience with RS2000 and RS2005, I am guessing we
> are/were both confronted with the same issues to solve one way or the other,
> you can decide if this means enough about knowledge :-) )
> To my knowledge you can not hardcode into the group expression itself how to
> sort anything (i never to that there). This can only be done in the sorting
> tab, for me this does the trick in all cases.
> Sometimes I may use something like fiscalyear, left(fiscalmonthname,3) in
> the columns. In my query I also select the fiscalmonthnr. In the matrix I
> choose to show the fiscalyear, fiscalmonthname (first 3 characters, this you
> can solve either in de query, or in the report). The fiscalmonthname I sort
> on the fiscalmonthnr ascending, which is selected in the dataset but not
> shown in the report (just for sorting purposes).
> If this reply misses your point (then i misunderstood something), then which
> expression you mean and where do you set it?
> Perry
>
> "Brian L" <BrianL@.discussions.microsoft.com> wrote in message
> news:5ABD05FF-2F13-4F0B-A5EC-18C949D0A915@.microsoft.com...
> > Perry - Ignore my last post. What I wrote didn't make sense. You were on
> > the
> > money. Question is, how do you write the expression to hard code the sort?
> > Guess I'll keep reading. Thanks.
> >
> > "Perry" wrote:
> >
> >> Brian,
> >>
> >> In a matrix you probably have the month set up as a group. In the
> >> properties
> >> of this group there is a sorting tap, I dont have a full picture of how
> >> you
> >> set things up but I think choosing it to order by MonthNr should be the
> >> thing to do.
> >>
> >> regards
> >>
> >> Perry
> >>
> >>
> >> "Brian L" <BrianL@.discussions.microsoft.com> wrote in message
> >> news:FF93F19C-A0A4-4BF6-98B9-482B0396F37C@.microsoft.com...
> >> >I have a matrix issue with the colum ordering. What I want is for the
> >> > month-yr columns to be in order, starting with 1-2006 --> 12-2006 but I
> >> > cannot determine how to order them correctly. can someone point me in
> >> > the
> >> > right direction?
> >>
> >>
> >>
>
>|||Seems like I made a simple problem into a difficult one. To fix my problem, I
added another criteria to my report. Month(Date) as MonthNum. Then used that
in the matrix and sorted ascending rather than using the month name ( Jan,
Feb. ) Worked like a charm.
"Perry" wrote:
> Brian,
> in reply to -- how do you write the expression to hard code the sort?
> (I have 7 months working experience with RS2000 and RS2005, I am guessing we
> are/were both confronted with the same issues to solve one way or the other,
> you can decide if this means enough about knowledge :-) )
> To my knowledge you can not hardcode into the group expression itself how to
> sort anything (i never to that there). This can only be done in the sorting
> tab, for me this does the trick in all cases.
> Sometimes I may use something like fiscalyear, left(fiscalmonthname,3) in
> the columns. In my query I also select the fiscalmonthnr. In the matrix I
> choose to show the fiscalyear, fiscalmonthname (first 3 characters, this you
> can solve either in de query, or in the report). The fiscalmonthname I sort
> on the fiscalmonthnr ascending, which is selected in the dataset but not
> shown in the report (just for sorting purposes).
> If this reply misses your point (then i misunderstood something), then which
> expression you mean and where do you set it?
> Perry
>
> "Brian L" <BrianL@.discussions.microsoft.com> wrote in message
> news:5ABD05FF-2F13-4F0B-A5EC-18C949D0A915@.microsoft.com...
> > Perry - Ignore my last post. What I wrote didn't make sense. You were on
> > the
> > money. Question is, how do you write the expression to hard code the sort?
> > Guess I'll keep reading. Thanks.
> >
> > "Perry" wrote:
> >
> >> Brian,
> >>
> >> In a matrix you probably have the month set up as a group. In the
> >> properties
> >> of this group there is a sorting tap, I dont have a full picture of how
> >> you
> >> set things up but I think choosing it to order by MonthNr should be the
> >> thing to do.
> >>
> >> regards
> >>
> >> Perry
> >>
> >>
> >> "Brian L" <BrianL@.discussions.microsoft.com> wrote in message
> >> news:FF93F19C-A0A4-4BF6-98B9-482B0396F37C@.microsoft.com...
> >> >I have a matrix issue with the colum ordering. What I want is for the
> >> > month-yr columns to be in order, starting with 1-2006 --> 12-2006 but I
> >> > cannot determine how to order them correctly. can someone point me in
> >> > the
> >> > right direction?
> >>
> >>
> >>
>
>

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!

Matrix - Subtotal Conditional Formatting - How do you do it?

My subtotal column's background color is dark gray and the text is bold
when the row group is collapsed, but I don't see this in other similar
matrix reports without a row group. This report was inherited from a
previous developer.
How did they do it? Even if I set the subtotal cell's colors to be
different from the entire column, they synchronize. I want to do this,
but can't figure out whether it's a setting or not.
Thanks!
MIkeThe subtotal heading should have a little green triangle on it. If you click
on that triangle, the VS properties window should show style settings that
specifically apply to subtotals row/columns.
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Bassist695" <Michael.EJ.Reynolds@.gmail.com> wrote in message
news:1128365078.625735.314000@.g49g2000cwa.googlegroups.com...
> My subtotal column's background color is dark gray and the text is bold
> when the row group is collapsed, but I don't see this in other similar
> matrix reports without a row group. This report was inherited from a
> previous developer.
> How did they do it? Even if I set the subtotal cell's colors to be
> different from the entire column, they synchronize. I want to do this,
> but can't figure out whether it's a setting or not.
> Thanks!
> MIke
>