Calculated Measure error

Hi.
I am working on a cube for schools, precisely analysing student absences.

I have two measures belonging to two measure groups where each measure group is linked to all the dimensions. These measure are : count of absences ,number of periods.

The calculated measure i'm trying to do is : (count of absences /number of periods )where number of periods != 0.

case

when [measures].[number of periods]= 0

then 0

else ([measures].[count of absences]/[measures].[number of periods])

end

in the browser with school,class,student dimensions as rows and both [absence count] and [number of periods] as measures, the data is displayed correctly.

however when i drag the calculated measure into the cube browser i get dummy data where the joins between students/classes/schools are lost...as if the 3 dimensions are cross joined...so i get all the students of the dimension student belonging to each class .

i don't know why this is happening: each measure alone is working but when i combine them both into one measure the data is mixed up.

What could be the problem?

thanks for your help

Christina

it worked..!!!

i put the [number of periods] in the non-empty behaviour field...

i didn't know that it was that important....

thanks anyway

Christina

Calculate Totals for Groups??

This is a multi-part message in MIME format.
--=_NextPart_000_015E_01C572BF.645C0780
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Hi,
I have two groupings in my table. one based of employee_id and the other = based on employee region. I am showing some values in the detail row of = that table.
Now, I need to show the total of one field in employee_id group1 footer = and employee_region group2 footer as well. the value that I display in = detail field can be type a or b. so I need to show 2 totals for both the = groups. How can I do this.
Group1:Header Employee_ID 1
Group2:Header Employee_Region Madrid
Detail Value:10 Type: B
Detail Value:10 Type: A
Detail Value:10 Type: B
Detail Value:10 Type: B
Detail Value:10 Type: A
Group2:Footer Total for Type A: 20
Group2:Footer Total for Type B: 30
Group2:Header Employee_Region Moscow
Detail Value:20 Type: B
Detail Value:20 Type: A
Detail Value:20 Type: B
Detail Value:20 Type: B
Detail Value:20 Type: A
Group2:Footer Total for Type A: 40
Group2:Footer Total for Type B: 60
Group1:Footer Total for Type A: 60
Group1:Footer Total for Type B: 90
My problem is in calculating Totals for Type A and B based on EmployeeID = and EmployeeRegion.How can I do this
Any help on this will be appreciated
Thanks in Advance
Kiran
--=_NextPart_000_015E_01C572BF.645C0780
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
Hi,
I have two groupings in my table. one based of employee_id and = the other based on employee region. I am showing some values in the detail = row of that table.

Now, I need to show the total of one field in employee_id group1 = footer and employee_region group2 footer as well. the value that I display in = detail field can be type a or b. so I need to show 2 totals for both the groups. How = can I do this.

Group1:Header Employee_ID 1

Group2:Header Employee_Region Madrid
Detail Value:10 Type: B
Detail Value:10 Type: A
Detail Value:10 Type: B
Detail Value:10 Type: B
Detail Value:10 Type: A
Group2:Footer Total for Type A: = 20
Group2:Footer Total for Type B: = 30

Group2:Header Employee_Region Moscow
Detail Value:20 Type: B
Detail Value:20 Type: A
Detail Value:20 Type: B
Detail Value:20 Type: B
Detail Value:20 Type: A
Group2:Footer Total for Type A: = 40
Group2:Footer Total for Type B: = 60

Group1:Footer Total for Type A: = 60
Group1:Footer Total for Type B: = 90

My problem is in calculating Totals for Type A and B based on EmployeeID and EmployeeRegion.How can I do this

Any help on this will be appreciated

Thanks in Advance
Kiran
=
--=_NextPart_000_015E_01C572BF.645C0780--For EmployeeID group - totals for type A
=Runningvalue(IIF(Reportitems!Type ="A",Reportitems!Detailvalue,0),SUM,employeeID groupname)
For EmployeeRegion group -
=Runningvalue(IIF(Reportitems!Type ="B",Reportitems!Detailvalue,0),SUM,employeeRegion groupname)|||Thanks Sonali.
Kiran
"Sonali" <Sonali@.discussions.microsoft.com> wrote in message
news:2694E290-3E42-4420-AD55-15D498A661EE@.microsoft.com...
> For EmployeeID group - totals for type A
> =Runningvalue(IIF(Reportitems!Type => "A",Reportitems!Detailvalue,0),SUM,employeeID groupname)
> For EmployeeRegion group -
> =Runningvalue(IIF(Reportitems!Type => "B",Reportitems!Detailvalue,0),SUM,employeeRegion groupname)

Calcualted Cells Performance Problem

Hello,

Initially the business divided its Practices into two groups, Industrial and Functional which were maintained in the relational source system as two seperate tables. The business has decided to expand the number of practices it reports on, however changes to the relational source system has not fully been implemented. We require reporting on the Budget data now so we've created a dimension named "Practices" which follows the same dimensional structure as the "Inudstry" and "Function" dimensions except that there is an extra parent level of "Practice Type". I would like to hide this new all inclusive "Practices" dimension from end users and effectively look up the corresponding value in the Industry or Function dimension from the Budget fact table.

I have the following calculated cell which achieves the desired results but has rather slow perforamce.

CREATE CELL CALCULATION CURRENTCUBE.[Budget Functional Practice]

FOR

'({[Measures].[Budget]},

[Fiscal Date].[Fiscal].[Fiscal Mo].MEMBERS,

[Function].[Func Practice].[Func Practice Def].MEMBERS)'

AS'

(StrToMember("Measures.[" + Measures.CurrentMember.Name + "_]"),

StrToMember("[Practices].[Hierarchy].[Practice Class].&[Function Practice].&[" + [Function].[Func Practice Def].CurrentMember.Name + "]"),

[Function].[Func Practice].[All])'

I was wondering if:

a) Is a calculated cell the best solution?

b) Is there a more efficient MDX which can be used?

Thank you.

If I am understanding correctly, you have a measure called Budget_ which you want to be the budget at the All functions level. If this is correct the following scope should work and perform significantly better. All of the string and currentmember references would have been slowing things down.

SCOPE ({[Measures].[Budget]},

[Fiscal Date].[Fiscal].[Fiscal Mo].MEMBERS,

[Function].[Func Practice].[Func Practice Def].MEMBERS);

(Measures.[Budget_]) = ([Function].[Func Practice].[All], [Measures].[Budget]);

END SCOPE;

|||

Thank you for the reply Darren, I think this is a step in the right direction.

The budget fact table contains one dimension which accounts for all of our practices (this dimension is hidden from the users). What I want to be able to do is, based on a selected member from either of the visible industry practice or function practice dimensions, lookup the corresponding value in the practices dimesnsion and show the budget measure.

|||

If you're using AS 2005, you might consider an alternative approach, using many-to-many dimensions. But first (if I interpreted your scenario correctly) you would need to set up 2 "Practices" dimension security roles, 1 for users of Function and the other for users of Industry (both roles would have Visual Totals enabled). Role#1 would allow access to the "Function", and Role#2 to the "Industry" member, at the "Practice Type" level - these would also be the Default Members for the respective roles. This would ensure that Function users only access Function data and Industry users likewise. Role#1 would be denied access to the Industry dimension and Role#2 to the Function dimension.

For the many-to-many dimension modelling, there would be a bridge table (could be a named query) which maps Function dimension table rows to the Practices dimension, and a separate bridge table to map Industry. Once a measure group is created for each bridge table, 1 bridging Function and Practices and the other bridging Industry and Practices, both Function and Industry can be configured as many-to-many dimensions for the Budget measure group.

Calcualted Cells Performance Problem

Hello,

Initially the business divided its Practices into two groups, Industrial and Functional which were maintained in the relational source system as two seperate tables. The business has decided to expand the number of practices it reports on, however changes to the relational source system has not fully been implemented. We require reporting on the Budget data now so we've created a dimension named "Practices" which follows the same dimensional structure as the "Inudstry" and "Function" dimensions except that there is an extra parent level of "Practice Type". I would like to hide this new all inclusive "Practices" dimension from end users and effectively look up the corresponding value in the Industry or Function dimension from the Budget fact table.

I have the following calculated cell which achieves the desired results but has rather slow perforamce.

CREATE CELL CALCULATION CURRENTCUBE.[Budget Functional Practice]

FOR

'({[Measures].[Budget]},

[Fiscal Date].[Fiscal].[Fiscal Mo].MEMBERS,

[Function].[Func Practice].[Func Practice Def].MEMBERS)'

AS'

(StrToMember("Measures.[" + Measures.CurrentMember.Name + "_]"),

StrToMember("[Practices].[Hierarchy].[Practice Class].&[Function Practice].&[" + [Function].[Func Practice Def].CurrentMember.Name + "]"),

[Function].[Func Practice].[All])'

I was wondering if:

a) Is a calculated cell the best solution?

b) Is there a more efficient MDX which can be used?

Thank you.

If I am understanding correctly, you have a measure called Budget_ which you want to be the budget at the All functions level. If this is correct the following scope should work and perform significantly better. All of the string and currentmember references would have been slowing things down.

SCOPE ({[Measures].[Budget]},

[Fiscal Date].[Fiscal].[Fiscal Mo].MEMBERS,

[Function].[Func Practice].[Func Practice Def].MEMBERS);

(Measures.[Budget_]) = ([Function].[Func Practice].[All], [Measures].[Budget]);

END SCOPE;

|||

Thank you for the reply Darren, I think this is a step in the right direction.

The budget fact table contains one dimension which accounts for all of our practices (this dimension is hidden from the users). What I want to be able to do is, based on a selected member from either of the visible industry practice or function practice dimensions, lookup the corresponding value in the practices dimesnsion and show the budget measure.

|||

If you're using AS 2005, you might consider an alternative approach, using many-to-many dimensions. But first (if I interpreted your scenario correctly) you would need to set up 2 "Practices" dimension security roles, 1 for users of Function and the other for users of Industry (both roles would have Visual Totals enabled). Role#1 would allow access to the "Function", and Role#2 to the "Industry" member, at the "Practice Type" level - these would also be the Default Members for the respective roles. This would ensure that Function users only access Function data and Industry users likewise. Role#1 would be denied access to the Industry dimension and Role#2 to the Function dimension.

For the many-to-many dimension modelling, there would be a bridge table (could be a named query) which maps Function dimension table rows to the Practices dimension, and a separate bridge table to map Industry. Once a measure group is created for each bridge table, 1 bridging Function and Practices and the other bridging Industry and Practices, both Function and Industry can be configured as many-to-many dimensions for the Budget measure group.

buy a clue as to how to "iterate sideways"

hi

i am having a hard time with two kinds of text files that have kind of 'repeating groups' in them...i want to loop it, but dont know how.

one is a text file with a record length of 1200 bytes, but all 95601records are all on one row with no lf, cr or anything else between them, so i cannot feature how to get the forEach container to chop of a Right of claimchunk of 1200 bytes at a time, then go get the next 1200 bytes, because the items aren't stacked, they are adjacent to each other, if you see what i mean.

the other text file has a record lenght of 52 bytes with 28 bytes filler, but this file also goes 'down and across', meaning that here, there are fourteen 'rows' in the file, and they have thousands of lines too, so this one also has to consume all the columns on the row before it moves to the next row.

am i making this harder than it needs to be?

thanks for any light

This file is a "fixed-width" file. Setup a flat file connection manager, set it to fixed width, and then define your columns appropriately. Or define one column, of 1200 bytes long, to read in each record as one field. Up to you. From there, you have several options.|||

thanks very much for your reply

yes, it is a fixed length file in the sense that the columns contained in each 1200 bytes occurr in the same place, but this also means that the entire 358MB of the file is on one line, so to appropriately define my columns means to create one column for each of the 96000+ records contained on that line in the file, so the structure of the data is not exactly what the fixed length file transform envisions, because in that assumption is an implicit end of line or end of record or some physical new line in the file...i dont have that.

So what i was imagining was using a ForEach loop iterator construct to get the loop to consume the line 1200 bytes at a time (how? i dont know) and push it into a variable and pipe that varialbe into a derived column (does it demand a table or can it work with a variable?) that would 'stack up' the 1200 byte package into the fixed length format described above, and then use that to finally parse the file into columns using the fixed length file as described, but my problem is how to recognize 200 bytes as a record first. if i define a 1200 byte fixed length file, i get exactly one record, the rest of the line is thrown out, because it doesnt fit into 'Column 0'.

thanks again

drew

|||

A fixed width file does not have to use row delimiter. So if I have this data:

1 2 3

1 2 3

and I defined it as a fixed width file with three columns of 1 character each, the format SSIS expects is:

123123

Fixed width with row delimiters are actually considered ragged right by SSIS.

You shouldn't have to use a ForEach to read this - the flat file connection manager set to fixed-width should work fine.

|||it worked great...please forgive me for being dense.