Matrix SubTotal

Hi,
Brief Explanation:
I need 2 fields (say, A and B) in the Matrix Data Region with their SubTotals shown (sum(A), sum(B)). Now, in the matrix Data Region, values for only A should be displayed but both for subtotals both (sum(A), sum(B)) should be displayed.
Detail Explanation, If required:
I am working with a matrix and require 2 fields to be shown in the Data Region against 1 row and 1 column groups. To display 2 fields in the Data Region, i kept a rectangle in the Data Region and placed 2 textboxes inside it with each displaying it's own respective field values.
Now when i add a subtotal to the report, it displays 2 rows for subtotals for each of the Data fields. So far so good.
My requirement is to suppress One of the Data Fields in the Data Region section and LET only it's the Subtotal get displayed. To achieve this, when i keep one of the textboxes hidden, the subtotal also gets hidden.
How can i achieve this ? Even a workwround suggested would be appreciated.
Crystal does this by allowing to access subtotals fields seperately than the data section fields which RS does not.
ThanksYou could conditionally hide the textbox based on the InScope function (e.g.
<Hidden>=InScope("RowGroup") and InScope("ColumnGroup")</Hidden>)
However, that won't collapse out the space occupied by the hidden textbox.
The size of the subtotal cell (in the current version) must be the same as
the size of a detail cell. If you're willing to let your subtotals be a
little cramped, you could do a variation of this with three textboxes. Two
small textboxes (one for A and one for B) which take up half of the
rectangle each and one large textbox (for just A) which takes up the entire
rectangle (overlapping with the other textboxes). You could then hide the
small textboxes when in the detail cell and hide the large textbox when in a
subtotal cell.
--
My employer's lawyers require me to say:
"This posting is provided 'AS IS' with no warranties, and confers no
rights."
"push" <push@.discussions.microsoft.com> wrote in message
news:E185ABCD-EE32-4E81-A735-BCBA236262C8@.microsoft.com...
> Hi,
> Brief Explanation:
> I need 2 fields (say, A and B) in the Matrix Data Region with their
SubTotals shown (sum(A), sum(B)). Now, in the matrix Data Region, values for
only A should be displayed but both for subtotals both (sum(A), sum(B))
should be displayed.
> Detail Explanation, If required:
> I am working with a matrix and require 2 fields to be shown in the Data
Region against 1 row and 1 column groups. To display 2 fields in the Data
Region, i kept a rectangle in the Data Region and placed 2 textboxes inside
it with each displaying it's own respective field values.
> Now when i add a subtotal to the report, it displays 2 rows for subtotals
for each of the Data fields. So far so good.
> My requirement is to suppress One of the Data Fields in the Data Region
section and LET only it's the Subtotal get displayed. To achieve this, when
i keep one of the textboxes hidden, the subtotal also gets hidden.
> How can i achieve this ? Even a workwround suggested would be appreciated.
> Crystal does this by allowing to access subtotals fields seperately than
the data section fields which RS does not.
> Thanks
>|||This worked in the preview. Thanks. Now there is another issue. When i give a border for the Subtotals fields (A, B) (from Right Clink of that green corner), while previewing it says "Object Reference not set to an instance of an Object".
How can i place a border on the subtotals as a whole (i.e. for both A and B) ? Also, if, possible, how can i conditionally place the border for only one of the subtotals (i.e. only for subtotal A)?
Thanks,
"Chris Hays [MSFT]" wrote:
> You could conditionally hide the textbox based on the InScope function (e.g.
> <Hidden>=InScope("RowGroup") and InScope("ColumnGroup")</Hidden>)
> However, that won't collapse out the space occupied by the hidden textbox.
> The size of the subtotal cell (in the current version) must be the same as
> the size of a detail cell. If you're willing to let your subtotals be a
> little cramped, you could do a variation of this with three textboxes. Two
> small textboxes (one for A and one for B) which take up half of the
> rectangle each and one large textbox (for just A) which takes up the entire
> rectangle (overlapping with the other textboxes). You could then hide the
> small textboxes when in the detail cell and hide the large textbox when in a
> subtotal cell.
> --
> My employer's lawyers require me to say:
> "This posting is provided 'AS IS' with no warranties, and confers no
> rights."
> "push" <push@.discussions.microsoft.com> wrote in message
> news:E185ABCD-EE32-4E81-A735-BCBA236262C8@.microsoft.com...
> > Hi,
> >
> > Brief Explanation:
> > I need 2 fields (say, A and B) in the Matrix Data Region with their
> SubTotals shown (sum(A), sum(B)). Now, in the matrix Data Region, values for
> only A should be displayed but both for subtotals both (sum(A), sum(B))
> should be displayed.
> >
> > Detail Explanation, If required:
> >
> > I am working with a matrix and require 2 fields to be shown in the Data
> Region against 1 row and 1 column groups. To display 2 fields in the Data
> Region, i kept a rectangle in the Data Region and placed 2 textboxes inside
> it with each displaying it's own respective field values.
> > Now when i add a subtotal to the report, it displays 2 rows for subtotals
> for each of the Data fields. So far so good.
> > My requirement is to suppress One of the Data Fields in the Data Region
> section and LET only it's the Subtotal get displayed. To achieve this, when
> i keep one of the textboxes hidden, the subtotal also gets hidden.
> > How can i achieve this ? Even a workwround suggested would be appreciated.
> > Crystal does this by allowing to access subtotals fields seperately than
> the data section fields which RS does not.
> >
> > Thanks
> >
>
>|||You're setting the border properties the right way. I don't know why you're
getting that error. (I tried the same thing here and it works, so I'd need
your report and the associated data file to try to reproduce the problem
here). A workaround in the mean time: Just set the borders on one or both
of the small textboxes directly. Since they'll be hidden unless you're in a
subtotal cell, you don't actually need to use the subtotal-specific style
properties (the green-triangle ones).
--
This post is provided 'AS IS' with no warranties, and confers no rights. All
rights reserved. Some assembly required. Batteries not included. Your
mileage may vary. Objects in mirror may be closer than they appear. No user
serviceable parts inside. Opening cover voids warranty. Keep out of reach of
children under 3.
"push" <push@.discussions.microsoft.com> wrote in message
news:027F0F5F-1971-4DEF-B118-7615AF3C988B@.microsoft.com...
> This worked in the preview. Thanks. Now there is another issue. When i
give a border for the Subtotals fields (A, B) (from Right Clink of that
green corner), while previewing it says "Object Reference not set to an
instance of an Object".
> How can i place a border on the subtotals as a whole (i.e. for both A and
B) ? Also, if, possible, how can i conditionally place the border for only
one of the subtotals (i.e. only for subtotal A)?
> Thanks,
>
> "Chris Hays [MSFT]" wrote:
> > You could conditionally hide the textbox based on the InScope function
(e.g.
> > <Hidden>=InScope("RowGroup") and InScope("ColumnGroup")</Hidden>)
> > However, that won't collapse out the space occupied by the hidden
textbox.
> > The size of the subtotal cell (in the current version) must be the same
as
> > the size of a detail cell. If you're willing to let your subtotals be a
> > little cramped, you could do a variation of this with three textboxes.
Two
> > small textboxes (one for A and one for B) which take up half of the
> > rectangle each and one large textbox (for just A) which takes up the
entire
> > rectangle (overlapping with the other textboxes). You could then hide
the
> > small textboxes when in the detail cell and hide the large textbox when
in a
> > subtotal cell.
> >
> > --
> > My employer's lawyers require me to say:
> > "This posting is provided 'AS IS' with no warranties, and confers no
> > rights."
> >
> > "push" <push@.discussions.microsoft.com> wrote in message
> > news:E185ABCD-EE32-4E81-A735-BCBA236262C8@.microsoft.com...
> > > Hi,
> > >
> > > Brief Explanation:
> > > I need 2 fields (say, A and B) in the Matrix Data Region with their
> > SubTotals shown (sum(A), sum(B)). Now, in the matrix Data Region, values
for
> > only A should be displayed but both for subtotals both (sum(A), sum(B))
> > should be displayed.
> > >
> > > Detail Explanation, If required:
> > >
> > > I am working with a matrix and require 2 fields to be shown in the
Data
> > Region against 1 row and 1 column groups. To display 2 fields in the
Data
> > Region, i kept a rectangle in the Data Region and placed 2 textboxes
inside
> > it with each displaying it's own respective field values.
> > > Now when i add a subtotal to the report, it displays 2 rows for
subtotals
> > for each of the Data fields. So far so good.
> > > My requirement is to suppress One of the Data Fields in the Data
Region
> > section and LET only it's the Subtotal get displayed. To achieve this,
when
> > i keep one of the textboxes hidden, the subtotal also gets hidden.
> > > How can i achieve this ? Even a workwround suggested would be
appreciated.
> > > Crystal does this by allowing to access subtotals fields seperately
than
> > the data section fields which RS does not.
> > >
> > > Thanks
> > >
> >
> >
> >

Matrix region formulas

Hi,
I would like a Matrix report with
Jan, Feb, Mar, Total on columns
where total sums up all the month values in that row.
And something like
2003, 2004, Difference2003-2004 on columns
AddColumn does't seem to do the trick
Thanks!Sorry, the first one I see I can do with the "Subtotal" option but what
about "average for the subtotal
"BoruRR" wrote:
> Hi,
> I would like a Matrix report with
> Jan, Feb, Mar, Total on columns
> where total sums up all the month values in that row.
> And something like
> 2003, 2004, Difference2003-2004 on columns
> AddColumn does't seem to do the trick
> Thanks!

Matrix Drill down Value not Correct

I am making a report that has column groups Day of Week and then "late".
(early late ontime) Row groups consist of region,district,store,dept. The
value is the sum(Fields!Measures_Rec_Count.Value) and reflects the correct
numbers throughout the drilldown. I am having problems with the Ratio value
calculated as format(sum( Fields!Measures_Rec_Count.Value
,"matrix1_late_Late")/sum(Fields!Measures_Rec_Count.Value,
"matrix1_dow_Dow"),"#.#%")...this calculates the correct ratio for the top
level but remains the same throughout the drilldown...I have tried using the
InScope() function but not getting any positive results....
Any Ideas?
Sunday Monday
...etc
Early ontime late Early ontime
late
region 10(83%) 2(17%) 10(25%) 25(64%) 4(10%)
district 1 3(83%) 2(17%)
district 2 4(83%) (17%)
district 3 3(83%) (17%)still could not get dynamicly grouped columns and rows to drill down with the
correct numbers....altered my MDX to create a matrix with one dynamic column
and 3 static columns and the drill down works now.....If anyone knows of a
way to have multiple dynamic columns where calculated cells drill down with
correct values please let me know.
Thanks.
"Raz" wrote:
> I am making a report that has column groups Day of Week and then "late".
> (early late ontime) Row groups consist of region,district,store,dept. The
> value is the sum(Fields!Measures_Rec_Count.Value) and reflects the correct
> numbers throughout the drilldown. I am having problems with the Ratio value
> calculated as format(sum( Fields!Measures_Rec_Count.Value
> ,"matrix1_late_Late")/sum(Fields!Measures_Rec_Count.Value,
> "matrix1_dow_Dow"),"#.#%")...this calculates the correct ratio for the top
> level but remains the same throughout the drilldown...I have tried using the
> InScope() function but not getting any positive results....
> Any Ideas?
> Sunday Monday
> ...etc
> Early ontime late Early ontime
> late
> region 10(83%) 2(17%) 10(25%) 25(64%) 4(10%)
> district 1 3(83%) 2(17%)
> district 2 4(83%) (17%)
> district 3 3(83%) (17%)
>