Median Function

I am trying to write a function to give me a median value. I have a matrix.
There is a column group/text box called loanbalance. Displayed in that
field is the SUM of all loan balances for the month.
I have another textbox called "Median". This textbox I want to display the
median value for the values in the "Loanbalance" field/textbox.
I don't have detail rows showing in the report, I am grouping.
I went out to the menu and selected: report, report properties, code, and
tried to write a function MEDIAN(reportitems!loanbalance.value), end
function. I know pretty basic, but I am not an advanced user.
Then in the "median" textbox, expression I have = code.MEDIAN(ReportItems!loanbalance.value).
It does not work. I don't get errors, I just don't get anything back for a
value.
Could someone help me out with this? Am I going about this the right way?
Thanks,Susan,
I have a report where I needed to get the Median time for documents
processed and ended up writing a routine from within a stored procedure to
perform this operation. As of yet I have not found a way to do it from w/in
RS - if anybody out there knows of way to accomplish this your help would be
appreciated.
Basically what I did was to get the row number corresponding to the number
of documents processed and divide that number by 2. I then took the
resulting number as parameter for my where clause-
Ex:
Set @.RowNumber = @.RowNumber / 2
Select @.MedianTime = DocTime From @.ReportDataTable
Where RowNumber = @.RowNumber
I know it's rudimentary but it does work.
Hope this helps.
Bill Youngman
Anexinet, Inc.
"Susan" <Susan@.discussions.microsoft.com> wrote in message
news:F2635C55-394A-44FD-A8C0-934C42260417@.microsoft.com...
> I am trying to write a function to give me a median value. I have a
matrix.
> There is a column group/text box called loanbalance. Displayed in that
> field is the SUM of all loan balances for the month.
> I have another textbox called "Median". This textbox I want to display
the
> median value for the values in the "Loanbalance" field/textbox.
> I don't have detail rows showing in the report, I am grouping.
> I went out to the menu and selected: report, report properties, code, and
> tried to write a function MEDIAN(reportitems!loanbalance.value), end
> function. I know pretty basic, but I am not an advanced user.
> Then in the "median" textbox, expression I have => code.MEDIAN(ReportItems!loanbalance.value).
> It does not work. I don't get errors, I just don't get anything back for
a
> value.
> Could someone help me out with this? Am I going about this the right way?
> Thanks,
>

Me!Value in matrix subtotal

G'day all,
Trying to set a property, for instance foreground color, based on Me!Value expression in a matric subtotal with an expression like:
=IIf(Me.Value < 0, "Red", "Black")
produces the following error:
The color expression for the matrix â'matrix1â' contains an error: [BC30456] 'Value' is not a member of 'ReportExprHostImpl.EH_matrix1.EH_MatrixDynamicGroup_...
jez_UK posted a similar question which is unanswered on July 14th, but my question is on Me!Value was different enough to warrant a new post.
Thanks in advance,
Thomas WilliamsI assume you clicked on the green triangle for the subtotal properties, and
modified the color expression for the subtotal. This won't work, because
every matrix cell is just a "container" for multiple reportitems (in your
case the matrix cell probably just contains one textbox). Only textboxes
would support Me.Value but not rectangles, etc. Therefore the compilation of
the expression results in an error.
You have to do this using an expression in textboxes of the matrix data
cells (assuming you have one column/row grouping called matrix1_ProdCat):
=iif(InScope("matrix1_ProdCat"), "Green", iif(Me.Value < 0, "Red", "Black"))
The expression above would do two things:
* regular matrix cell (which are in the scope of the matrix1_ProdCat
grouping) will have a green color
* subtotal matrix cells (which are not in the scope of the grouping) will
have red or black color based on the current textbox value.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Thomas Williams" <thomasswilliams@.hotmail.com> wrote in message
news:AE48347D-440C-4E91-97EC-234F464F871B@.microsoft.com...
> G'day all,
> Trying to set a property, for instance foreground color, based on Me!Value
expression in a matric subtotal with an expression like:
> =IIf(Me.Value < 0, "Red", "Black")
> produces the following error:
> The color expression for the matrix 'matrix1' contains an error: [BC30456]
'Value' is not a member of
'ReportExprHostImpl.EH_matrix1.EH_MatrixDynamicGroup_...
> jez_UK posted a similar question which is unanswered on July 14th, but my
question is on Me!Value was different enough to warrant a new post.
> Thanks in advance,
> Thomas Williams|||Hi Robert, thanks for the reply, now I've got to figure out the scope of my groupings (3 row groups, 1 column group) and create an "IIf" statement to cover them.
Thanks again mate!
"Robert Bruckner [MSFT]" wrote:
> I assume you clicked on the green triangle for the subtotal properties, and
> modified the color expression for the subtotal. This won't work, because
> every matrix cell is just a "container" for multiple reportitems (in your
> case the matrix cell probably just contains one textbox). Only textboxes
> would support Me.Value but not rectangles, etc. Therefore the compilation of
> the expression results in an error.
> You have to do this using an expression in textboxes of the matrix data
> cells (assuming you have one column/row grouping called matrix1_ProdCat):
> =iif(InScope("matrix1_ProdCat"), "Green", iif(Me.Value < 0, "Red", "Black"))
> The expression above would do two things:
> * regular matrix cell (which are in the scope of the matrix1_ProdCat
> grouping) will have a green color
> * subtotal matrix cells (which are not in the scope of the grouping) will
> have red or black color based on the current textbox value.
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Thomas Williams" <thomasswilliams@.hotmail.com> wrote in message
> news:AE48347D-440C-4E91-97EC-234F464F871B@.microsoft.com...
> > G'day all,
> >
> > Trying to set a property, for instance foreground color, based on Me!Value
> expression in a matric subtotal with an expression like:
> > =IIf(Me.Value < 0, "Red", "Black")
> >
> > produces the following error:
> > The color expression for the matrix 'matrix1' contains an error: [BC30456]
> 'Value' is not a member of
> 'ReportExprHostImpl.EH_matrix1.EH_MatrixDynamicGroup_...
> >
> > jez_UK posted a similar question which is unanswered on July 14th, but my
> question is on Me!Value was different enough to warrant a new post.
> >
> > Thanks in advance,
> >
> > Thomas Williams
>
>|||I was redirected to this post from another. I am still having problems, though this post has clarified the Why and the What. I am at a loss for How.
What does "You have to do this using an expression in textboxes of the matrix data cells" mean? I take that to mean I need to make a textbox somewhere (I cannot use the provided subtotal matrix cell). Do you mean I have to delete the subtotal (and corresponding text box) and somehow manually add a textbox to the group and write an expression to get the value(the subtotal) and an expression to change the text color? Any help is greatly appreciated - I am spinning on this right now.
Thanks.
"Robert Bruckner [MSFT]" wrote:
> I assume you clicked on the green triangle for the subtotal properties, and
> modified the color expression for the subtotal. This won't work, because
> every matrix cell is just a "container" for multiple reportitems (in your
> case the matrix cell probably just contains one textbox). Only textboxes
> would support Me.Value but not rectangles, etc. Therefore the compilation of
> the expression results in an error.
> You have to do this using an expression in textboxes of the matrix data
> cells (assuming you have one column/row grouping called matrix1_ProdCat):
> =iif(InScope("matrix1_ProdCat"), "Green", iif(Me.Value < 0, "Red", "Black"))
> The expression above would do two things:
> * regular matrix cell (which are in the scope of the matrix1_ProdCat
> grouping) will have a green color
> * subtotal matrix cells (which are not in the scope of the grouping) will
> have red or black color based on the current textbox value.
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Thomas Williams" <thomasswilliams@.hotmail.com> wrote in message
> news:AE48347D-440C-4E91-97EC-234F464F871B@.microsoft.com...
> > G'day all,
> >
> > Trying to set a property, for instance foreground color, based on Me!Value
> expression in a matric subtotal with an expression like:
> > =IIf(Me.Value < 0, "Red", "Black")
> >
> > produces the following error:
> > The color expression for the matrix 'matrix1' contains an error: [BC30456]
> 'Value' is not a member of
> 'ReportExprHostImpl.EH_matrix1.EH_MatrixDynamicGroup_...
> >
> > jez_UK posted a similar question which is unanswered on July 14th, but my
> question is on Me!Value was different enough to warrant a new post.
> >
> > Thanks in advance,
> >
> > Thomas Williams
>
>|||Don't delete the subtotal. However, instead of clicking on the green
triangle in the matrix heading, you click on the actual matrix data cell
which will show your data (most likely the matrix cell contains a textbox in
your case). The matrix cell probably contains an expression like
=Sum(Fields!Sales.Value)
For the conditional formatting of the cell content you will then use an
expression as outlined below:
=iif(InScope("matrix1_ProdCat"), "Green", iif(Me.Value < 0, "Red", "Black"))
If you have multiple row/column groupings in the matrix you will need to
take them into account when using the InScope function to determine if the
cell at runtime is a "subtotal cell" or a regular "data cell".
More information on InScope is available at:
http://msdn.microsoft.com/library/en-us/RSCREATE/htm/rcr_creating_expressions_v1_0jmt.asp
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Getting started and liking it" <Getting started and liking
it@.discussions.microsoft.com> wrote in message
news:6A6F92A4-59AA-4B90-8A5A-433E85B3A8B6@.microsoft.com...
> I was redirected to this post from another. I am still having problems,
though this post has clarified the Why and the What. I am at a loss for How.
> What does "You have to do this using an expression in textboxes of the
matrix data cells" mean? I take that to mean I need to make a textbox
somewhere (I cannot use the provided subtotal matrix cell). Do you mean I
have to delete the subtotal (and corresponding text box) and somehow
manually add a textbox to the group and write an expression to get the
value(the subtotal) and an expression to change the text color? Any help is
greatly appreciated - I am spinning on this right now.
> Thanks.
> "Robert Bruckner [MSFT]" wrote:
> > I assume you clicked on the green triangle for the subtotal properties,
and
> > modified the color expression for the subtotal. This won't work, because
> > every matrix cell is just a "container" for multiple reportitems (in
your
> > case the matrix cell probably just contains one textbox). Only textboxes
> > would support Me.Value but not rectangles, etc. Therefore the
compilation of
> > the expression results in an error.
> >
> > You have to do this using an expression in textboxes of the matrix data
> > cells (assuming you have one column/row grouping called
matrix1_ProdCat):
> > =iif(InScope("matrix1_ProdCat"), "Green", iif(Me.Value < 0, "Red",
"Black"))
> >
> > The expression above would do two things:
> > * regular matrix cell (which are in the scope of the matrix1_ProdCat
> > grouping) will have a green color
> > * subtotal matrix cells (which are not in the scope of the grouping)
will
> > have red or black color based on the current textbox value.
> >
> > --
> > This posting is provided "AS IS" with no warranties, and confers no
rights.
> >
> >
> > "Thomas Williams" <thomasswilliams@.hotmail.com> wrote in message
> > news:AE48347D-440C-4E91-97EC-234F464F871B@.microsoft.com...
> > > G'day all,
> > >
> > > Trying to set a property, for instance foreground color, based on
Me!Value
> > expression in a matric subtotal with an expression like:
> > > =IIf(Me.Value < 0, "Red", "Black")
> > >
> > > produces the following error:
> > > The color expression for the matrix 'matrix1' contains an error:
[BC30456]
> > 'Value' is not a member of
> > 'ReportExprHostImpl.EH_matrix1.EH_MatrixDynamicGroup_...
> > >
> > > jez_UK posted a similar question which is unanswered on July 14th, but
my
> > question is on Me!Value was different enough to warrant a new post.
> > >
> > > Thanks in advance,
> > >
> > > Thomas Williams
> >
> >
> >

MDX, Matrix and Aggregate()

All,
I keep reading that the Matrix is the perfect tool to use with OLAP data, but I am very confused about the efficacy of such a notion. As we know, OLAP is about precalculated aggregations, and the noticeable performance improvements that such an analytical database yields.
But RS answer to all of this is to calculate the aggregations at run-time,they recommend to bring in the leaf level data from the cubes, and then let RS aggregate the results at runtime.
This may an acceptable approach for simple MDX queries, but for advanced analysis -- it just does not make good sense. 
We desperately want to use RS for our Enterprise Reporting solution, but we are hard pressed to justify the performance issues (Cellset flattening + run-time aggregations) and the complexity of a MDX/ Matrix solution (if one exists)
Any examples of a multiple group (rows and columns) Matrix using an MDX 
datasource would be appreciated!!!
Thanks,
Jim

Are you asking about RS 2000 or RS 2005?

MDX queries designed in graphical query designer of RS 2005 can retrieve AS server aggregates directly within the query. You would then use the =Aggregate(...) function within the matrix cells instead of using regular aggregations. Based on the matrix cells' scopes, the Aggregate(...) function will determine the correct aggregate row from the flattened rowset. However, this means that the aggregate rows must be present and retrieved by the MDX query in the first place. Adjusting the MDX query based on the usage of the Aggregate function within the report is taken care of automatically by report designer if you designed the MDX query with the graphical MDX query designer of RS 2005.

-- Robert

|||

Robert,

Thanks for getting back to me -- I'm using RS 2000, so the good news is that it sounds like RS 2005 has addressed the issue of the MDX / Matrix complexity.

Any ideas on RS 2000? We won't be going to 2005 until Q1 2007.

Thanks,

Jim

|||

Technically, you could implement a full custom data extension for RS 2000. That data extension would need to implement the IDataReaderExtension interface (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSPROG/htm/rsp_ref_clr_dataproc_8je0.asp) among other interfaces and identify aggregate rows within the flattened rowset returned from a AS 2000 server.

However, this is a non-trivial task and requires a lot of effort to get everything working. Btw, RS 2000 already supports the Aggregate function - so you would then use it as described in my previous posting. Anyway, the Aggregate function is only useful if with a data extension that implements the IDataReaderExtension interface. None of the RS 2000 data extensions implement that functionality however.

Specific support for AS server aggregates is a feature that was added in RS 2005 in combination with a new RS 2005 Analysis Services data extension that works on top of the new AS 2005 AdoMd data provider.

-- Robert

|||

When I use the Aggregate() function instead of the default Sum() function in subtotals RS2005 returns nothing, i.e. it doesn't work.

The problem in all its simplicity: I have two measures in my MDX query:

Sales in dollars (can use Sum for subtotals)