dynamic row formatting based on dataset field values

HI y'all.
PROBLEM. Would like to change row color based on a calculated field.
I.E. need the row to highlight when an expiration date is within 14 days of
expiring.
I can use the row expression panel to alternate row color, but cant quite
find the magic to make it happen with data as the driver. I can also use the
report code panel which can be accessed with an =Code.bgcolor in the row
background settings.
(sample http://sqlservercentral.com/cs/blogs/aaron_myers/default.aspx)
But yet the report code requires objects references and I can find no
information how this is done in reporting services.
While I am here: What is the process sequence at run time? Does the
report, Table and row formatting have a view of the fields in a defined
dataset?
Thanks in advance.From Properties/BackgroundColor, select Expression.
If you are using a field as the driver, cope and paste the following code
and make sure to change YourFieldName with the actual name.
=IIf(Fields!YourFieldName.Value < 14, "Red", "White")
If you are using a parameter field as the driver, use this instead.
=IIf(Parameters!YourParameterName.Value < 14, "Red", "White")
"MKTapps" wrote:
> HI y'all.
> PROBLEM. Would like to change row color based on a calculated field.
> I.E. need the row to highlight when an expiration date is within 14 days of
> expiring.
> I can use the row expression panel to alternate row color, but cant quite
> find the magic to make it happen with data as the driver. I can also use the
> report code panel which can be accessed with an =Code.bgcolor in the row
> background settings.
> (sample http://sqlservercentral.com/cs/blogs/aaron_myers/default.aspx)
> But yet the report code requires objects references and I can find no
> information how this is done in reporting services.
> While I am here: What is the process sequence at run time? Does the
> report, Table and row formatting have a view of the fields in a defined
> dataset?
> Thanks in advance.

Dynamic Period Over Period Growth Without Hierarchy in Time/Date Dimension?

The time dimension in our AS2005 cube is not hierarchical. Therefore, how can I dynamically do a period over period growth calculated member? I'd like it to determine the growth whether the user is viewing it yearly, quarterly or monthly.

If our time dimension was indeed hierarchical, I would define the metric as follows:

([Time].[Currentmember], [Measures].[Sales] - ([Time].[CurrentMember].[PrevMember], [Measures].[Sales])

Right now, I have only been able to do it for a specific level whether it's year, quarter or month. For example, for a year over year growth, I've defined it as follows:

([Date].[Year].CURRENTMEMBER, [Measures].[Sales USD]) -
([Date].[Year].CURRENTMEMBER.PREVMEMBER, [Measures].[Sales USD])

If I wanted to do the same for months, I'd replace "Year" with "Month" as follows:

([Date].[Month].CURRENTMEMBER, [Measures].[Sales USD]) -
([Date].[Month].CURRENTMEMBER.PREVMEMBER, [Measures].[Sales USD])

Is it possible to do a dynamic period over period growth in 1 calculated member based on how our Date dimension is setup?

Could you explain what attribute relations exist in your time dimension - are quarters related to months, and years to quarters? Or is the [Month] like a "month-of-year" and quarter like a "quarter-of-year"? In that case, you could try a scoped assignment like:

Create [Measures].[SalesGrowth];

Scope([Measures].[SalesGrowth]);

Scope([Date].[Year].[Year]);

this = [Measures].[Sales USD] - ([Date].[Year].PREVMEMBER, [Measures].[Sales USD]);

Scope([Date].[Quarter].[Quarter]);

this = [Measures].[Sales USD] - ([Date].[Quarter].PREVMEMBER, [Measures].[Sales USD]);

Scope([Date].[Month].[Month]);

this = [Measures].[Sales USD] - ([Date].[Month].PREVMEMBER, [Measures].[Sales USD]);

End Scope;

End Scope;

End Scope;

End Scope;

|||Deepak,

I've never used SCOPE before, but this looks like it could work. Where do I use SCOPE? In the definition of the [SalesGrowth] calculated member? Or do I have to somehow make use of a new SCRIPT command? I've never done this either. The only Script Command I have is at the default "CALCULATE". that goes before all of my calculated members.

Thanks!
|||The code above already includes a statement to create [SalesGrowth], at the beginning - you could append this snippet to your existing script, after the other calculated members.|||Deepak,

Thanks for exposing me to SCOPE! I was able to acheive what I wanted with just a few tweaks.

Dynamic ParallelPeriode

Hi,

with this MDX expression as a calculated member in my cube

100 / [Measures].[Sales Volume KG] * (parallelperiod([Date].[Month], 12), [Measures].[Sales Volume KG])

I get the difference of the Sales Volume from this month and this month last year. But how can I make it dynamic for drilldown in the Time Dimension, so that also year, semester, Quarters and so on are supported? I will compare this year - last year, this semester - same semester last year, this quarter - same quarter last year and so on.

Thanks

Hans

Hi Hans,

Why doesn't ParallelPeriod(Year ..) meet your needs - could you explain in the context of this Adventure Works query?

>>

select

{[Measures].[Sales Amount]} on 0,

Generate(Ascendants([Date].[Calendar].[Date].&[800]),

{[Date].[Calendar].CurrentMember,

ParallelPeriod([Date].[Calendar].[Calendar Year],

1, [Date].[Calendar].CurrentMember)}) on 1

from [Adventure Works]

-

Sales Amount
September 8, 2003 $36,027.71
September 8, 2002 $18,755.92
September 2003 $5,057,832.17
September 2002 $3,235,826.19
Q3 CY 2003 $13,670,536.57
Q3 CY 2002 $10,277,073.06
H2 CY 2003 $26,955,981.04
H2 CY 2002 $18,646,056.13
CY 2003 $41,993,729.72
CY 2002 $30,674,773.18
All Periods $109,809,274.20

>>

|||

Hi Deepak,

Thanks for your suggestions. Your code gave me the idea to specify the Dimension in full, so I name it [Date].[Periode - Year].[Year] and not only [Date].[Year] and now it works. But it works only with the [Periode - Year] Hierarchy. How can I change it, to work with all my Time hierarchies ([Date].[Periode - Year], [Date].[Periode - Week], [Date].[Periode - Reporting])? I tried

IIF( [Measures].[Sales Volume KG] = 0, 0, 100 - (100 / [Measures].[Sales Volume KG] * (parallelperiod([Date].[Periode - Year].[Year], 1) * parallelperiod([Date].[Periode - Week].[Year], 1) * parallelperiod([Date].[Periode - Reporting].[Year], 1), [Measures].[Sales Volume KG])) )

but that doesn't work. You gave me this approach just a year ago for builing YTD sums just like

IIF( sum(YTD([Date].[Periode - Year].CurrentMember) * YTD([Date].[Periode - Week].CurrentMember) * YTD([Date].[Periode - Reporting].CurrentMember),[Measures].[Sales Volume M2]) = 0, 0, sum(YTD([Date].[Periode - Year].CurrentMember) * YTD([Date].[Periode - Week].CurrentMember) * YTD([Date].[Periode - Reporting].CurrentMember),[Measures].[Commission To Market]) / sum(YTD([Date].[Periode - Year].CurrentMember) * YTD([Date].[Periode - Week].CurrentMember) * YTD([Date].[Periode - Reporting].CurrentMember),[Measures].[Sales Volume M2]))

which work fine, but this doesn't work here. How can I do it here?

Thanks

Hans

|||

Hi Hans,

With ParallelPeriod() a different approach is needed, which would depend on the attributes in the 3 hierarchies and the attribute relations (an entry in Mosha's blog shows some examples). If all the hierarchies align with the beginning of the time dimension (which isn't true for Adventure Works Calendar hierarchy), then a cube MDX script assignment like this might work - otherwise, you could describe the attributes and hierarchies in more detail:

([Measure].[SalesGrowth], [Date].[Date].Members) =

IIF( [Measures].[Sales Volume KG] = 0, 0, 100 - (100 / [Measures].[Sales Volume KG]

* (parallelperiod([Date].[Periode - Year].[Year]), [Measures].[Sales Volume KG])) );

dynamic mdx

Hello i'm new user,

i have a calculated member

the function LINREGSLOPE juste calculate the slope ( ie: a) of the equation Y= a *X+b

calculated member Name= Gain

LINREGSLOPE([TIME].[Month].&[2006-02-01T00:00:00]:[TIME].[Month].&[2006-06-01T00:00:00],[Mesures].[NumofCustomer],[TIME].[Month], [TIME].[Rank])

i write

the formula do the job veru well. What i want to do now is to

transform the expression to be dynamic with two parameter

I hope this will help.

I seen in this forum that this is possble. how to extract

this information. How can i concat the parametre!month.value with the

string ?

i see idea of solution here but i dont know how to exploit it

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=443431&SiteID=1

how can i do this please help

thanks

hi,

If I am not mistaken, I think you want to restrict the query based on static value instead of dynamic parameters. Right? If that is the case you can just include a where condition to your query like this:

where [Dimension].[Heirarchy].&[Member1]:[Dimension].[Heirarchy].&[Member2]

Note: Dimesion is your dimension name

Heirarchy is your heirachy name.

Member1 is the start date

Member 2 is the end date

I hope this will help.

Sincerely,

Amde

|||thanks amde,

no it's the opposite from static de dynamique.

if the user change the parametre the query will change too.i want to write my expresion with a parameter.

i want somethink like this:

linregslope([Time].[month].&"param1":[Time].[month].&"param2",[measures].[numberCustomer],[Time].[Month].[MonthNumber]).

if you have others questions feel free to ask me.

visite the link in my first post.

I hope this will help.

any idea is welcome

casagrandi

|||

ok thanks

i found the solution

this isi the code:

WITH

set [RangeMonth] as LastPeriods(STRTOMEMBER (@.FromTIMEMonth,CONSTRAINED),STRTOMEMBER (@.ToTIMEMonth,CONSTRAINED))

MEMBER [Measures].[GAIN PER MONTH] AS 'LINREGSLOPE([RangeMonth],[Measures].[mymeasure],Rank([TIME].[Month], [TIME].[Month].Members))'

,format="percent"

SELECT NON EMPTY {[Measures].[GAIN PER MONTH],[Measures] ..........

sql

Dynamic Expression

I would like to display calculated field on a group header.
The nature of the calculation must be passed as a parameter to the report.
For example lets assume that my report lists X,Y pairs.
On the break I would like to display the value of
Sum(X)/Sum(Y) or Sum(X/Y) or Sum(X*Y) or Sum(X)*SUM(Y).
It is not practical to prepare an expression for every possible option and
use a selector as X,Y are also dynamically selected values from the query.
So I need a dynamic way to define the expression based on input parameters.
Is Custom code is the way to go ? How do I access the Parameters collection,
and the Report's data from Custom code ?
Any Ideas ?
Thanks.I think that custom code will be needed. You can pass the values you need
(Parameters, Report Data) in as parameters to a function.
"NL" wrote:
> I would like to display calculated field on a group header.
> The nature of the calculation must be passed as a parameter to the report.
> For example lets assume that my report lists X,Y pairs.
> On the break I would like to display the value of
> Sum(X)/Sum(Y) or Sum(X/Y) or Sum(X*Y) or Sum(X)*SUM(Y).
> It is not practical to prepare an expression for every possible option and
> use a selector as X,Y are also dynamically selected values from the query.
> So I need a dynamic way to define the expression based on input parameters.
> Is Custom code is the way to go ? How do I access the Parameters collection,
> and the Report's data from Custom code ?
> Any Ideas ?
> Thanks.
>
>
>
>
>
>|||How does one create a custom function that manipulates the headers of a
report based on parameter values?
"John W" wrote:
> I think that custom code will be needed. You can pass the values you need
> (Parameters, Report Data) in as parameters to a function.
> "NL" wrote:
> > I would like to display calculated field on a group header.
> > The nature of the calculation must be passed as a parameter to the report.
> >
> > For example lets assume that my report lists X,Y pairs.
> > On the break I would like to display the value of
> > Sum(X)/Sum(Y) or Sum(X/Y) or Sum(X*Y) or Sum(X)*SUM(Y).
> >
> > It is not practical to prepare an expression for every possible option and
> > use a selector as X,Y are also dynamically selected values from the query.
> > So I need a dynamic way to define the expression based on input parameters.
> >
> > Is Custom code is the way to go ? How do I access the Parameters collection,
> > and the Report's data from Custom code ?
> >
> > Any Ideas ?
> >
> > Thanks.
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >|||The previous question was asking about display a calculated field on a group
header. You can do this by adding a field to the group header that references
a function defined in VB.NET code in the Report Properties Code tab.
Code sample:
Public Function GetDoubledValue(ByVal NumToDouble As Int) As String
Return CStr(NumToDouble * 2)
End Function
Field Reference:
=Code.GetDoubledValue(4)
"Leneise44" wrote:
> How does one create a custom function that manipulates the headers of a
> report based on parameter values?
> "John W" wrote:
> > I think that custom code will be needed. You can pass the values you need
> > (Parameters, Report Data) in as parameters to a function.
> >
> > "NL" wrote:
> >
> > > I would like to display calculated field on a group header.
> > > The nature of the calculation must be passed as a parameter to the report.
> > >
> > > For example lets assume that my report lists X,Y pairs.
> > > On the break I would like to display the value of
> > > Sum(X)/Sum(Y) or Sum(X/Y) or Sum(X*Y) or Sum(X)*SUM(Y).
> > >
> > > It is not practical to prepare an expression for every possible option and
> > > use a selector as X,Y are also dynamically selected values from the query.
> > > So I need a dynamic way to define the expression based on input parameters.
> > >
> > > Is Custom code is the way to go ? How do I access the Parameters collection,
> > > and the Report's data from Custom code ?
> > >
> > > Any Ideas ?
> > >
> > > Thanks.
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > >