Matrix "Group" and RunningValue Problems

I have 2 problems at the moment and hope I'll be able to get some help
here
My first problem:
I need some help on presenting data in a matrix.
I have data like below :
Amt
1-Jan 1
2-Jan 2
3-Jan 3
1-Feb 1
2-Feb 2
3-Feb 3
Is it possible for me to "group" my data to be like the following:
Amt
Jan 6
Feb 6
My Second problem is with runningvalue.
My data has multiple records for one ID. But I only want to have a
runningtotal based on one value from each ID and grouping by area.
To illustrate my problem,
Area 1
ID 1 4
ID 1 4
ID 1 4
ID 1 4
ID 1 4
ID 2 1
ID 2 1
ID 2 1
ID 2 1
I would like to have the runningtotal to be equal to 5 and not 24.
If I use =RunningValue(Fields!Amt.Value, sum, "Area"), I will be
getting 24.
Currently I'm using a custom code to get the value but I can't pass
the value to the "Area" group footer.
I tried using ReportItems to pass the value out but received an error.
The error message is given:
"The Value expression for the textbox 'textbox100' refers to the
report item 'textbox28'. Report item expressions can only refer to
other report items within the same grouping scope or a containing
grouping scope."
Can anybody help me?Can you use RunningValue(Fields!Amt.Value, MAX, "Area") instead of sum?
"mirioki02@.gmail.com" wrote:
> I have 2 problems at the moment and hope I'll be able to get some help
> here
> My first problem:
> I need some help on presenting data in a matrix.
> I have data like below :
> Amt
> 1-Jan 1
> 2-Jan 2
> 3-Jan 3
> 1-Feb 1
> 2-Feb 2
> 3-Feb 3
> Is it possible for me to "group" my data to be like the following:
> Amt
> Jan 6
> Feb 6
>
> My Second problem is with runningvalue.
> My data has multiple records for one ID. But I only want to have a
> runningtotal based on one value from each ID and grouping by area.
> To illustrate my problem,
> Area 1
> ID 1 4
> ID 1 4
> ID 1 4
> ID 1 4
> ID 1 4
> ID 2 1
> ID 2 1
> ID 2 1
> ID 2 1
> I would like to have the runningtotal to be equal to 5 and not 24.
> If I use =RunningValue(Fields!Amt.Value, sum, "Area"), I will be
> getting 24.
> Currently I'm using a custom code to get the value but I can't pass
> the value to the "Area" group footer.
> I tried using ReportItems to pass the value out but received an error.
> The error message is given:
> "The Value expression for the textbox 'textbox100' refers to the
> report item 'textbox28'. Report item expressions can only refer to
> other report items within the same grouping scope or a containing
> grouping scope."
> Can anybody help me?
>|||On Nov 9, 5:38 am, "miriok...@.gmail.com" <miriok...@.gmail.com> wrote:
> My first problem:
> Is it possible for me to "group" my data to be like the following...
Drag the Amt column into the Details of the Matrix, and make sure the
expression is
= Sum( Fields!Amt.Value )
Drag the Date column into the Row of the Matrix. Now, right click the
cell and Edit Group. Change the expression to something like
= DateAdd( "d", 1-Day(Fields!Date.Value), Fields!Date.Value )
so that the expression always resolves to the first day of the month.
Hit OK, then go back to the Expression of the cell, and change it to
= Format( Fields!Date.Value, "MMM")
> My Second problem is with runningvalue.
Try using
=RunningValue(Fields!Amt.Value, Count, "Area")
If the function is properly returning the Sum when you ask for Sum,
try asking for Count.
The error message has to do with the Scope of aggregation. You cannot
create a group ("Area") and then reference the group at a higher
grouping level
Group 1: Items
Group 2: Area
You cannot do a Count of rows in "Area" and put the total in the
header of "Items". But you can hide the header of Group Items, and
put the name of the Item and the Count of Area into the header of
group "Area".
-- Scott|||Thank you Jimbo and Scott for your suggestions...
The method Scott gave for my first problem worked perfectly.
But I'm still having trouble with the second problem.
I tried RunningValue(Fields!Amt.Value, MAX, "Area"), it only gives me
the maximum value in the Area group, not the running total of all the
maximum values.
As for RunningValue(Fields!Amt.Value, Count, "Area"), it gives me the
total rows in the Area group.
Is there a way to sum on distinct ID in the Area group? Or inserting
an iif condition in the runningvalue? I tried
Runningvalue(iif(First(Fields!ID.value), Fields!Amt.Value, Nothing),
sum, "Area") since I wanted the first value of each ID but it says it
cannot have an aggregate function within an aggregate.
Is there a method to write something along the line of >> iif(Fields!
ID.Value = distinct, Fields!Amt.Value, Nothing).
On Nov 9, 10:07 pm, Orne <polysilly...@.yahoo.com> wrote:
> On Nov 9, 5:38 am, "miriok...@.gmail.com" <miriok...@.gmail.com> wrote:
> > My first problem:
> > Is it possible for me to "group" my data to be like the following...
> Drag the Amt column into the Details of the Matrix, and make sure the
> expression is
> = Sum( Fields!Amt.Value )
> Drag the Date column into the Row of the Matrix. Now, right click the
> cell and Edit Group. Change the expression to something like
> = DateAdd( "d", 1-Day(Fields!Date.Value), Fields!Date.Value )
> so that the expression always resolves to the first day of the month.
> Hit OK, then go back to the Expression of the cell, and change it to
> = Format( Fields!Date.Value, "MMM")
> > My Second problem is with runningvalue.
> Try using
> =RunningValue(Fields!Amt.Value, Count, "Area")
> If the function is properly returning the Sum when you ask for Sum,
> try asking for Count.
> The error message has to do with the Scope of aggregation. You cannot
> create a group ("Area") and then reference the group at a higher
> grouping level
> Group 1: Items
> Group 2: Area
> You cannot do a Count of rows in "Area" and put the total in the
> header of "Items". But you can hide the header of Group Items, and
> put the name of the Item and the Count of Area into the header of
> group "Area".
> -- Scott