Custom code scoping

I have a report where I have to do the same expression over and over with the
only change being the day of week that the data exists in. What I wanted to
do was to create a single code block that I could call:
Code.dataByDay(1)
That would do the work of checking my Fields and doing all of the
calculations, this would be much cleaner and easier to debug than putting in
a huge expression into every field that needs it and changing the specific
day number. But when I tried it my code doesn't have access to the Fields
object. Do I need to pass this into my code also? If so what type is it? I
was thinking that my code would have the same scoping access as any
expression.
Thanks for your helpNot sure I completely understand your question but...
You can return the string name of a field from a piece of code and use it in
an expression ie when you want the value to com efrom different fields.
in a text field =Fields(code.decideandreturnthefeildname(params)).Value
You could also pass in the field name and the func returns the appropriate
value. In several text fields, the expression is
=Code.dataByDay("sunday", 1) "sunday is the field name which is used in
the func to determine which value to return
=Code.dataByDay("Monday",1)...etc
--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
(Please respond only to the newsgroup.)
I support the Professional Association for SQL Server ( PASS) and it's
community of SQL Professionals.
"eoghain" <eoghain@.discussions.microsoft.com> wrote in message
news:87794252-9FD2-4972-B113-77D2AE34784A@.microsoft.com...
> I have a report where I have to do the same expression over and over with
the
> only change being the day of week that the data exists in. What I wanted
to
> do was to create a single code block that I could call:
> Code.dataByDay(1)
> That would do the work of checking my Fields and doing all of the
> calculations, this would be much cleaner and easier to debug than putting
in
> a huge expression into every field that needs it and changing the specific
> day number. But when I tried it my code doesn't have access to the Fields
> object. Do I need to pass this into my code also? If so what type is it?
I
> was thinking that my code would have the same scoping access as any
> expression.
> Thanks for your help|||Wayne, thanks for the information here is what I'm trying to do:
I have a report that divides data in to daily totals grouped by a user
defined increment. So I select all of my data and group it by the users
increment (15 min, 30 min, 1 hr, etc..). Then in each day column (Sun - Sat)
I have an expression that looks like this:
=Sum(IIF(DATEPART("w", Fields!EntryDate.Value) = 1,
IIF(Fields!Duration.Value > 0, 1, 0),0))
Where I change the DATEPART function to test for each specific day. What
I'd rather do is call a piece of code that walks through the Fields!EntryDate
values (that are in scope) to generate my data, since I have a second column
that is using this expression:
=IIF((Sum(IIF(DATEPART("w", Fields!EntryDate.Value) = 1, 1, 0))) > 0,
(Round(Sum(IIF(DATEPART("w", Fields!EntryDate.Value) = 1,
Fields!Duration.Value, 0)) / Sum(IIF(DATEPART("w", Fields!EntryDate.Value) =1, 1, 0)))), 0)
And there are just too many places where a simple little mistake could get
added.
"Wayne Snyder" wrote:
> Not sure I completely understand your question but...
> You can return the string name of a field from a piece of code and use it in
> an expression ie when you want the value to com efrom different fields.
> in a text field =Fields(code.decideandreturnthefeildname(params)).Value
> You could also pass in the field name and the func returns the appropriate
> value. In several text fields, the expression is
> =Code.dataByDay("sunday", 1) "sunday is the field name which is used in
> the func to determine which value to return
> =Code.dataByDay("Monday",1)...etc
> --
> Wayne Snyder MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> (Please respond only to the newsgroup.)
> I support the Professional Association for SQL Server ( PASS) and it's
> community of SQL Professionals.
> "eoghain" <eoghain@.discussions.microsoft.com> wrote in message
> news:87794252-9FD2-4972-B113-77D2AE34784A@.microsoft.com...
> > I have a report where I have to do the same expression over and over with
> the
> > only change being the day of week that the data exists in. What I wanted
> to
> > do was to create a single code block that I could call:
> >
> > Code.dataByDay(1)
> >
> > That would do the work of checking my Fields and doing all of the
> > calculations, this would be much cleaner and easier to debug than putting
> in
> > a huge expression into every field that needs it and changing the specific
> > day number. But when I tried it my code doesn't have access to the Fields
> > object. Do I need to pass this into my code also? If so what type is it?
> I
> > was thinking that my code would have the same scoping access as any
> > expression.
> >
> > Thanks for your help
>
>

Custom Aggregation Functions like SUM, AVERAGE etc.

Hi,

I want to write a custom aggregation function called PRODUCT (as it exists in Excel) to be used with [Measures].[Monthly Return] column. I could not see such function in AggregateFunction attribute of the mesaure in the Cube explorer.

What are the different ways I can write my own Aggregation functions? OR Achieve similar functionality with any alternative approach?

Appreciate your response.. let me know if further information is required.

-Ashish

You can't write your own Aggregation functions but you can certainly achieve the same result using MDX Script assignments to control how measures roll up. Regarding your specific problem, there is a function in the Analysis Services Stored Procedure project which does exactly what you're looking for:

http://www.codeplex.com/Wiki/View.aspx?ProjectName=ASStoredProcedures&title=Multiplication

...although my understanding is that using a sproc in your MDX Script could have an adverse effect on caching, so you might want to test out using the sproc and the technique using logs which is described by Darren Gosbell here:

http://geekswithblogs.net/darrengosbell/archive/2006/07/18/85539.aspx

HTH,

Chris