Creating a Calculated Member Based on a Range

Is there a way to create a calculated member based on a range of values?

For example, I have two measures in my cube: ResponseTime and TransactionCount.

What I want to do is create a calculated member that returns the total number of Transactions where the ResponseTime is between a certain range, i.e. between 1 and 2 seconds.

Here is what I've tried to do:

IIF( [Measures].[ResponseTime] > 1 And [Measures].[ResponseTime] < 2, [Measures].[TransactionCount], 0)

However, when I browse the cube, all the values for this calculated member are blank. Any ideas?

This problem can easily can generalized. My main problem is just finding a way to group values based on a certain range of values.

Thanks!!

Joel,

Given that you want to look at the "response time" for each individual transaction in your fact table and the cube is going to show you values aggregated based on selections that the end user makes, you may want to consider the following solution:

In your data source view:

Add a name calculation to your fact table named "ResponseTimeRangeKey" using a CASE statement:

CASE

WHEN ResponseTime <= 1 THEN 1

WHEN ResponseTime > 1 AND ResponseTime < 2 THEN 2

ELSE 3

END CASE

Next you can create a dimension table that can be used to build a cube dimension for your response time ranges using either a database view or a named query with a simple SQL statement something like this:

SELECT

1 AS ResponseTimeRangeKey,

'1 sec or less' AS ResponseTimeRangeName

UNION

SELECT

2 AS ResponseTimeRangeKey,

'1-2 sec' AS ResponseTimeRangeName

UNION

SELECT

3 AS ResponseTimeRangeKey,

'more than 2 sec' AS ResponseTimeRangeName

You can then relate your fact table to the new dimension in the data source view. Next you can create your response time range dimension and add it to your cube.

This solution has the benefit of allowing users to look at any measure in your cube using the response time ranges and will accurately aggregate your values for all dimensions.

HTH,

Steve

|||

Thanks for the help. I tried it out, and it worked brilliantly.

|||

I have somewhat the same scenario, I need to find a way to group values based on certain range of values. I have a calculated member, YTD investment. From my fact table I created a named calculation field for SegmentID with initial value of -1 to relate it with Segment Dimension. The key -1 has "Unknown" as the caption. This SegmentID is my dimension key on the fact table and the key of Segment Dimension. The Segment Dimension has the Min and Max attributes where the minimum and maximum range of values for a particular dimension member is defined.

Is there a way for me to populate or assign values to the named calculation field SegmentID by getting the dimension key of segment dimension where the YTD Investment falls on segment dim's min and max attributes/range?

Thanks in advance,
May Lanie

|||

May,

This is can be tricky depending on the dimensionality of your cube and how you want the YTD to roll up. I have done this type of "dynamic" bucketing before using a separate measure group that only contains the row count for the fact table. You can then use the SCOPE function to allocate values along a pre-defined range of buckets according to the value you want to use for distribution purposes. Here is an example of what I am talking about:

Scope([Measures].[Customer Count by Bucket]);

([Bucket].[Bucket Key].[< 50K]) =

Filter(

Existing Customer.[Customer Name].[Customer Name].Members,

([Measures].[Sales Amt],[Bucket].[Bucket Key].[All Bucket]) <= 50000).Count;

([Bucket].[Bucket Key].[50K - 100K]) =

Filter(

Existing Customer.[Customer Name].[Customer Name].Members,

([Measures].[Sales Amt],[Bucket].[Bucket Key].[All Bucket]) > 50000 AND

([Measures].[Sales Amt],[Bucket].[Bucket Key].[All Bucket]) <= 100000).Count;

....

End Scope;

The "Measures.[Customer Count by Bucket]" is the fact table row count measure. There is a predefined "Bucket" dimension that has key values indicating a range, but you could just as easily use your "low" and "high" member values. The "Filter" statements are used to count the number of customers that fall into the buckets using "Sales Amt". You would need to substitute your "YTD Amount" calculation.

HTH,

Steve

Creating a calculated measure that filters

Hi,

Can I create a calculated member that will filter out based on a certain dimension? eg, I have a customer dimension and a sales cube. The sales cube has the sales count measure. Can I have a calculated measure that will give me the sales count for all customers that are flagged as 'New'? That way, I have a total count and a New customer count.

Thanks,

Brian

This should be very easy to do in MDX if you have attribute NewCustomer in the Customer dimension. The expression then would be something like

(Measures.[Sales Count], Customer.NewCustomer.[true])

|||

Thanks Mosha, indeed it is easy! I'd like to expand on this.

So, by adding a new calculated member, (Measures.[Sales Count], Customer.NewCustomer.[true]), it will give me the counts of new customers. Is is possible to now also filter on, say, customerStatus as well? eg.

My Sales cube also has a CustomerType dimension. How can I expand the filter above to also filter on the CustomerType dimension (say, customertype = E).

I'm still trying to get my head around MDX :)

Thanks!

Brian

|||I wouldn't call this "expansion" of the filter, it is more of "restricting" the filter further. Yes, you can add as many hierarchy members to the filter as you want. I recommend going over first few chapters of my "Fast Track to MDX" book to get yourself on the MDX track fast...

Creating a Calculated Field

I want to create a calculated field, but I can't get the expression to
work. All I want to do is the sum of field 1 and field 2. This must
be easy, but I just can't see it?On Mar 20, 5:58 am, "Andy" <andywilliams1...@.msn.com> wrote:
> I want to create a calculated field, but I can't get the expression to
> work. All I want to do is the sum of field 1 and field 2. This must
> be easy, but I just can't see it?
You should be able to use something like this:
=Sum(Fields!Field1Name.Value, "DataSetName") + Sum(Fields!
Field2Name.Value, "DataSetName")
Regards,
Enrique Martinez
Sr. SQL Server Developer|||On 20 Mar, 11:13, "EMartinez" <emartinez...@.gmail.com> wrote:
> On Mar 20, 5:58 am, "Andy" <andywilliams1...@.msn.com> wrote:
> > I want to create a calculated field, but I can't get the expression to
> > work. All I want to do is the sum of field 1 and field 2. This must
> > be easy, but I just can't see it?
> You should be able to use something like this:
> =Sum(Fields!Field1Name.Value, "DataSetName") + Sum(Fields!
> Field2Name.Value, "DataSetName")
> Regards,
> Enrique Martinez
> Sr. SQL Server Developer
I entered that into the expression of the calculated field, but I get
the following error message.
A value expression used for the report parameter '=sum( Fields!
converganceValue.Value, "CapmLOB")
+ sum(Fields!nonConverganceValue.Value, "CapmLOB")' includes an
aggregate function. Aggregate functions cannot be used in report
parameter expressions.|||Why don't you do it in your SQL query instead of doing it in the textbox
formula ?
SELECT (SUM(fld1)+SUM(fld2)) AS fld3 FROM table ...
And then put the following in your tb formula.
=SUM(Fields!fld3.Value)
This is how I work and I think it's the real way to do it.
Good Luck
Julien
"Andy" <andywilliams1971@.msn.com> wrote in message
news:1174391075.416295.213680@.n76g2000hsh.googlegroups.com...
> On 20 Mar, 11:13, "EMartinez" <emartinez...@.gmail.com> wrote:
>> On Mar 20, 5:58 am, "Andy" <andywilliams1...@.msn.com> wrote:
>> > I want to create a calculated field, but I can't get the expression to
>> > work. All I want to do is the sum of field 1 and field 2. This must
>> > be easy, but I just can't see it?
>> You should be able to use something like this:
>> =Sum(Fields!Field1Name.Value, "DataSetName") + Sum(Fields!
>> Field2Name.Value, "DataSetName")
>> Regards,
>> Enrique Martinez
>> Sr. SQL Server Developer
> I entered that into the expression of the calculated field, but I get
> the following error message.
> A value expression used for the report parameter '=sum( Fields!
> converganceValue.Value, "CapmLOB")
> + sum(Fields!nonConverganceValue.Value, "CapmLOB")' includes an
> aggregate function. Aggregate functions cannot be used in report
> parameter expressions.
>|||On Mar 20, 3:01 pm, "Julien Bonnier" <jul...@.m0851.com> wrote:
> Why don't you do it in your SQL query instead of doing it in the textbox
> formula ?
> SELECT (SUM(fld1)+SUM(fld2)) AS fld3 FROM table ...
> And then put the following in your tb formula.
> =SUM(Fields!fld3.Value)
> This is how I work and I think it's the real way to do it.
> Good Luck
> Julien
> "Andy" <andywilliams1...@.msn.com> wrote in message
> news:1174391075.416295.213680@.n76g2000hsh.googlegroups.com...
> > On 20 Mar, 11:13, "EMartinez" <emartinez...@.gmail.com> wrote:
> >> On Mar 20, 5:58 am, "Andy" <andywilliams1...@.msn.com> wrote:
> >> > I want to create a calculated field, but I can't get the expression to
> >> > work. All I want to do is the sum of field 1 and field 2. This must
> >> > be easy, but I just can't see it?
> >> You should be able to use something like this:
> >> =Sum(Fields!Field1Name.Value, "DataSetName") + Sum(Fields!
> >> Field2Name.Value, "DataSetName")
> >> Regards,
> >> Enrique Martinez
> >> Sr. SQL Server Developer
> > I entered that into the expression of the calculated field, but I get
> > the following error message.
> > A value expression used for the report parameter '=sum( Fields!
> > converganceValue.Value, "CapmLOB")
> > + sum(Fields!nonConverganceValue.Value, "CapmLOB")' includes an
> > aggregate function. Aggregate functions cannot be used in report
> > parameter expressions.
Julien has a valid point.
If you are using the calculated field inside a table and there is only
one cell/value per item, you can most likely avoid using the aggregate
(i.e., =(Fields!
converganceValue.Value + Fields!nonConverganceValue.Value)); however,
if the calculated field is outside the table, etc then you will most
likely need to create the calculated field in the query or stored
procedure that is sourcing the report. Hope this helps.
Regards,
Enrique Martinez
Sr. SQL Server Developer|||On 21 Mar, 01:44, "EMartinez" <emartinez...@.gmail.com> wrote:
> On Mar 20, 3:01 pm, "Julien Bonnier" <jul...@.m0851.com> wrote:
>
>
> > Why don't you do it in your SQL query instead of doing it in the textbox
> > formula ?
> > SELECT (SUM(fld1)+SUM(fld2)) AS fld3 FROM table ...
> > And then put the following in your tb formula.
> > =SUM(Fields!fld3.Value)
> > This is how I work and I think it's the real way to do it.
> > Good Luck
> > Julien
> > "Andy" <andywilliams1...@.msn.com> wrote in message
> >news:1174391075.416295.213680@.n76g2000hsh.googlegroups.com...
> > > On 20 Mar, 11:13, "EMartinez" <emartinez...@.gmail.com> wrote:
> > >> On Mar 20, 5:58 am, "Andy" <andywilliams1...@.msn.com> wrote:
> > >> > I want to create a calculated field, but I can't get the expression to
> > >> > work. All I want to do is the sum of field 1 and field 2. This must
> > >> > be easy, but I just can't see it?
> > >> You should be able to use something like this:
> > >> =Sum(Fields!Field1Name.Value, "DataSetName") + Sum(Fields!
> > >> Field2Name.Value, "DataSetName")
> > >> Regards,
> > >> Enrique Martinez
> > >> Sr. SQL Server Developer
> > > I entered that into the expression of the calculated field, but I get
> > > the following error message.
> > > A value expression used for the report parameter '=sum( Fields!
> > > converganceValue.Value, "CapmLOB")
> > > + sum(Fields!nonConverganceValue.Value, "CapmLOB")' includes an
> > > aggregate function. Aggregate functions cannot be used in report
> > > parameter expressions.
> Julien has a valid point.
> If you are using the calculated field inside a table and there is only
> one cell/value per item, you can most likely avoid using the aggregate
> (i.e., =(Fields!
> converganceValue.Value + Fields!nonConverganceValue.Value)); however,
> if the calculated field is outside the table, etc then you will most
> likely need to create the calculated field in the query or stored
> procedure that is sourcing the report. Hope this helps.
> Regards,
> Enrique Martinez
> Sr. SQL Server Developer- Hide quoted text -
> - Show quoted text -
Thanks. I put it in the SQL code and it worked fine. Don't know why
I didn't think of that earlier.|||On Mar 21, 4:01 am, "Andy" <andywilliams1...@.msn.com> wrote:
> On 21 Mar, 01:44, "EMartinez" <emartinez...@.gmail.com> wrote:
>
> > On Mar 20, 3:01 pm, "Julien Bonnier" <jul...@.m0851.com> wrote:
> > > Why don't you do it in your SQL query instead of doing it in the textbox
> > > formula ?
> > > SELECT (SUM(fld1)+SUM(fld2)) AS fld3 FROM table ...
> > > And then put the following in your tb formula.
> > > =SUM(Fields!fld3.Value)
> > > This is how I work and I think it's the real way to do it.
> > > Good Luck
> > > Julien
> > > "Andy" <andywilliams1...@.msn.com> wrote in message
> > >news:1174391075.416295.213680@.n76g2000hsh.googlegroups.com...
> > > > On 20 Mar, 11:13, "EMartinez" <emartinez...@.gmail.com> wrote:
> > > >> On Mar 20, 5:58 am, "Andy" <andywilliams1...@.msn.com> wrote:
> > > >> > I want to create a calculated field, but I can't get the expression to
> > > >> > work. All I want to do is the sum of field 1 and field 2. This must
> > > >> > be easy, but I just can't see it?
> > > >> You should be able to use something like this:
> > > >> =Sum(Fields!Field1Name.Value, "DataSetName") + Sum(Fields!
> > > >> Field2Name.Value, "DataSetName")
> > > >> Regards,
> > > >> Enrique Martinez
> > > >> Sr. SQL Server Developer
> > > > I entered that into the expression of the calculated field, but I get
> > > > the following error message.
> > > > A value expression used for the report parameter '=sum( Fields!
> > > > converganceValue.Value, "CapmLOB")
> > > > + sum(Fields!nonConverganceValue.Value, "CapmLOB")' includes an
> > > > aggregate function. Aggregate functions cannot be used in report
> > > > parameter expressions.
> > Julien has a valid point.
> > If you are using the calculated field inside a table and there is only
> > one cell/value per item, you can most likely avoid using the aggregate
> > (i.e., =(Fields!
> > converganceValue.Value + Fields!nonConverganceValue.Value)); however,
> > if the calculated field is outside the table, etc then you will most
> > likely need to create the calculated field in the query or stored
> > procedure that is sourcing the report. Hope this helps.
> > Regards,
> > Enrique Martinez
> > Sr. SQL Server Developer- Hide quoted text -
> > - Show quoted text -
> Thanks. I put it in the SQL code and it worked fine. Don't know why
> I didn't think of that earlier.
You're welcome. Glad it worked.
Regards,
Enrique Martinez
Sr. SQL Server Developer