Different operations along dimensions

I am having a stupid problem with my cube...

The data I have is a floating point number between 0 and 1, representing the utilization of an item (machine, production line), in percent for a time period. Let's say, it was 0.5 for Monday and 0.1 for Tuesday.

Not really the 99.9% reliability we usually look for, hehe - but that is another story. For the examples these low numbers are better and easier.

Anyway, if I want to see the average for Monday and Tuesday I use exactly that function: AVG as aggregation in my cube, and I am done. With the example above, I would get a 30% usage of my production machine. (0,5 + 0,1) / 2 in math speak.

My question now is this: I have not one machine, but several. Along this "machine number" dimension, I do NOT want to average, but really sum up the values: Let's say my 2nd machine did 0,8 for Monday, together with the #1 machine that did 0,5 I'd like to see the Monday really as 1.3, or 130% - the actual display of the number is no issue.

Sounds easy, but somehow I have no idea how to do that. Can I have different functions for each dimension that aggregates a value? Or is that a custom MDX script?

Have you tried changing your measure to use the AverageOfChildren aggregate function (instead of the Sum function)? (This is a measure property, not something in the calc script.) What you have is a semi-additive measure... it should be summed across all dimensions except for the Timem dimension. That's what the AverageOfChildren, LastNonEmpty, etc. aggregate functions are for.|||

Spot on! THANKS!

Defining the dimension as Time and using AverageOfChildren did solve it.

But...

What if I have several time dimensions? I might be doing something wrong, but it seems the AverageOfChildren does the Avg only on the FIRST time dimension in the cube - the other time dimensions are treated as the regular ones... does that make sense?

In my case, having date (year/date) and time (hour/minutes) in separate dimension I think I can solve by cross joining the tables and use that as source for one dimension. But what in a case where you have truly different dates in your cube, say an order date, one delivery date and maybe one invoice date, yet you need your counter to be "avg" or "last" or so along EACH of these datetime dimensions?

|||

You're correct that semi-additive measures sum across all dimensions except the first time dimension. Usually this is what you want... for instance, take an Inventory Status measure group which is a daily snapshot of all your inventory. Besides your main Date dimension, you might also have a Received Date dimension. If you sliced by Received Date, you're wanting to slice down to inventory which was received on that date, but the semi-additive behavior should operate on the main Date dimension not the Received Date dimension.

Your situation is probably not the common one, so you may have to resort to using the calc script to detect which date dimension you have sliced by.

I would avoid putting the days and time of day within the same dimension if you can. It's a simple dimension size concern... a normal Date dimension which goes down to day has 365 members per year. But if you go down to second, it has 31,536,000 members.

different dataset inside table header

Hi, I need to access a secondary dataset inside a table header. I have a List
item defined, and a TextBox inside it. When I try to access a field in the
secondary dataset, it says that field is outside of scope. How can I
accomplish this? If it's any help, here is the RDL:
<TableCell>
<ReportItems>
<List Name="list1">
<Style />
<ZIndex>7</ZIndex>
<DataSetName>TOC_HEADER</DataSetName>
<ReportItems>
<Textbox Name="textbox3">
<Style>
<PaddingLeft>1pt</PaddingLeft>
<FontFamily>Courier New</FontFamily>
<FontSize>=Iif(RowNumber("TOC_HEADER") > 1,
"10pt", "12pt")</FontSize>
<TextAlign>Center</TextAlign>
<PaddingRight>1pt</PaddingRight>
<FontWeight>700</FontWeight>
</Style>
<CanGrow>true</CanGrow>
<Value>=Fields!titleString.Value</Value>
</Textbox>
</ReportItems>
</List>
</ReportItems>
</TableCell>
Thanks in advance,
Peter L.You can only access fields through aggregate functions in other datasets.
E.g. =First(Fields!xyz.Value, "OtherDataSetName")
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"plandry@.newsgroups.nospam"
<plandrynewsgroupsnospam@.discussions.microsoft.com> wrote in message
news:8158CB33-7651-4492-884D-0B98E7F1B590@.microsoft.com...
> Hi, I need to access a secondary dataset inside a table header. I have a
List
> item defined, and a TextBox inside it. When I try to access a field in the
> secondary dataset, it says that field is outside of scope. How can I
> accomplish this? If it's any help, here is the RDL:
> <TableCell>
> <ReportItems>
> <List Name="list1">
> <Style />
> <ZIndex>7</ZIndex>
> <DataSetName>TOC_HEADER</DataSetName>
> <ReportItems>
> <Textbox Name="textbox3">
> <Style>
> <PaddingLeft>1pt</PaddingLeft>
> <FontFamily>Courier New</FontFamily>
> <FontSize>=Iif(RowNumber("TOC_HEADER") > 1,
> "10pt", "12pt")</FontSize>
> <TextAlign>Center</TextAlign>
> <PaddingRight>1pt</PaddingRight>
> <FontWeight>700</FontWeight>
> </Style>
> <CanGrow>true</CanGrow>
> <Value>=Fields!titleString.Value</Value>
> </Textbox>
> </ReportItems>
> </List>
> </ReportItems>
> </TableCell>
> Thanks in advance,
> Peter L.|||> Hi, I need to access a secondary dataset inside a table header.
Wherefore?|||Here's my problem:
I need a multi-line header on every page, that is populated through a
dataset. Since I can't access a dataset from inside the page header (which I
think is a pretty serious shortcoming), I thought maybe I could put a header
on the table that would appear on every page. This header is populated from
its own dataset. I'm open to other ways of accomplishing this -- but
basically I need information from a dataset to appear at the top of every
page.
"Sergey Fedorenko" wrote:
> > Hi, I need to access a secondary dataset inside a table header.
> Wherefore?
>
>|||Try dropping a subreport inside your header. I know it's not elegant, but
it's a way to get around the nested datasets issue.
--
Cheers,
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"plandry@.newsgroups.nospam"
<plandrynewsgroupsnospam@.discussions.microsoft.com> wrote in message
news:24B7CB1C-2F21-46AB-9664-AA95EFAAF46E@.microsoft.com...
> Here's my problem:
> I need a multi-line header on every page, that is populated through a
> dataset. Since I can't access a dataset from inside the page header (which
> I
> think is a pretty serious shortcoming), I thought maybe I could put a
> header
> on the table that would appear on every page. This header is populated
> from
> its own dataset. I'm open to other ways of accomplishing this -- but
> basically I need information from a dataset to appear at the top of every
> page.
> "Sergey Fedorenko" wrote:
>> > Hi, I need to access a secondary dataset inside a table header.
>> Wherefore?
>>