Cristal Reports for OLAP CUBE

I have created a crystal report connecting an OLAP cube .But once i
try to view a report it took around 15 minutes and finaly gave an
error like "cannot retrieve data from cube".I want to know why it is
and how can i create a crystal report for OLAP CUB."Pradeep" <aprpradeep@.hotmail.com> wrote in message
news:3f64305f.0402250014.39e80bee@.posting.google.c om...
> I have created a crystal report connecting an OLAP cube .But once i
> try to view a report it took around 15 minutes and finaly gave an
> error like "cannot retrieve data from cube".I want to know why it is
> and how can i create a crystal report for OLAP CUB.

It sounds more like a Crystal Reports question than an MSSQL one, but you
may want to try posting in microsoft.public.sqlserver.olap, to see if anyone
there has experience of this.

Simon|||"Pradeep" <aprpradeep@.hotmail.com> wrote in message
news:3f64305f.0402250014.39e80bee@.posting.google.c om...
> I have created a crystal report connecting an OLAP cube .But once i
> try to view a report it took around 15 minutes and finaly gave an
> error like "cannot retrieve data from cube".I want to know why it is
> and how can i create a crystal report for OLAP CUB.

You may want to capture a trace using the Profiler tool which should show
you what SELECT statements are being passed from Crystal. See BOL on
Profiler. Once you capture the trace you could then run the SELECT
statement(s) in Query Analyzer which may tell you if you are having an issue
with your reporting tool or if your SELECT statements cause errors or
timeouts.|||"Terri" <Terri@.spamaway.com> wrote in message news:<c1jmk1$33q$1@.reader2.nmix.net>...
> "Pradeep" <aprpradeep@.hotmail.com> wrote in message
> news:3f64305f.0402250014.39e80bee@.posting.google.c om...
> > I have created a crystal report connecting an OLAP cube .But once i
> > try to view a report it took around 15 minutes and finaly gave an
> > error like "cannot retrieve data from cube".I want to know why it is
> > and how can i create a crystal report for OLAP CUB.
> You may want to capture a trace using the Profiler tool which should show
> you what SELECT statements are being passed from Crystal. See BOL on
> Profiler. Once you capture the trace you could then run the SELECT
> statement(s) in Query Analyzer which may tell you if you are having an issue
> with your reporting tool or if your SELECT statements cause errors or
> timeouts.

Profiler probably won't help here - as far as I know, it works with
connections to SQL Server only, not to Analysis Services.

Simon

Creating two dimensions referencing the same table

Hi,

I am building a cube in the 2005 Analysis Services. I built the cube successfully when each of my dimensions referenced exactly one table. Now I want to extend that to build two dimensions based on one table. I can build the 2nd dimension but I cannot use it within the cube.

Any suggestions on how to do that?

Thanks in advance,

Aref

Any reason why you can't build 1 dimension and use it in 2 roles within your cube (eg: Date dimension in Adventure Works plays 3 roles)?

Creating report based on parent-child dimension

Hi

I have a problem to create a report based on a parent child-dimension

When I go to reporting services, and I create a new report based on a cube, I drag my parent-child dimension to my data-layout. Then the dimension is immediatily ragged down.

Anyone an idea to solve this.

You need to group the data on the UniqueName property and set the parent group expression to the ParentUniqueName property.|||Does anyone have an example to create a report based on a parent-child dimensions

Thx|||Brian

I changed the report with the grouping on uniquename and the parent group on parentuniquename

But now I got only the top level

How can I drill down?
|||I have the same problem and didnt' find a solution for it. What I really want are the little +/- signs so i can open/close the children of the parent.
Didn't find anything ... what I do now is ident the children. This can be done using an expressing in the value property and using the LevelNumber to add spaces in front of the value ...
Not quit what I want ....
If you find a solution please share it here ...|||

hello,

I have a similar problem in RS2005:

In my report I see all levels in one column (How did g4rc manage to see only top level?...). However they are shown in a way that I think that if I only could get it to toggle correctly it will work out fine...

I would like my P/C dimensionto display just as it does when I use AS. Expanding top level (by pressing +), viewing next level down where I can "open" (by pressing +) next level and so on. Does anyone know how this is done or suggestions on how to solve it?

This is the kind of display I would like:

-A 100

-B 50
D 25
E 25

+C 50

Sincerely,

|||

Unfortunately, I’ve now these problems.

To solve these problems I followed these steps:

1. Alter the mdx query

2. Define the parent group in the report layout

3. Define the toggle item

1. Alter the mdx query

a. You should add a calculated member that gets the parent of your current member. Look to the following query (the dimension “Conta Raz?o” is a parent-child dimension)

WITH

MEMBER [Measures].[Conta Raz?o Parent Label] as '[ContaRaz?o].[Default Hierarchy].CurrentMember.Parent.MEMBER_CAPTION'

SELECT

{ [Measures].[Real], [Measures].[Conta Raz?o Parent Label]} ON COLUMNS,

NON EMPTY {DESCENDANTS([Conta Raz?o].[Default Hierarchy])} ON ROWS

FROM [Controlo Or?amental]

2. Define the parent group in the report layout

a) Select the group in your table, “edit group”, and in “parent group” property select the field that contains the parent of your dimension member (in my case, I select the “=Fields!Conta_Raz?o_Parent_Label.Value”; the query above has a calculated member that gets the parent of the current member )

3. Define the toggle item

a. Select the group in your table, and the “hidden property” of “visibility” should have “=IIF(Level()=0, False,True)” – this means that only the “first parent” is visible

b. In the “toggleItem property select the name of the textbox that contains “the members of the parent-child dimension”)

I hope this is useful for you.

Telmo Moreira

|||

Telmo Moreira wrote:

Unfortunately, I’ve now these problems.

To solve these problems I follow these steps:

1. Alter the mdx query

2. Define the parent group in the report layout

3. Define the toggle item

1. Alter the mdx query

a. You should add a calculated member that gets the parent of your current member. Look to the following query (the dimension “Conta Raz?o” is a parent-child dimension)

WITH

MEMBER [Measures].[Conta Raz?o Parent Label] as '[ContaRaz?o].[Default Hierarchy].CurrentMember.Parent.MEMBER_CAPTION'

SELECT

{ [Measures].[Real} ON COLUMNS,

NON EMPTY {DESCENDANTS([Conta Raz?o].[Default Hierarchy])} ON ROWS

FROM [Controlo Or?amental]

2. Define the parent group in the report layout

a) Select the group in your table, “edit group”, and in “parent group” property select the field that contains the parent of your dimension member (in my case, I select the “=Fields!Conta_Raz?o_Parent_Label.Value”; the query above has a calculated member that gets the parent of the current member )

3. Define the toggle item

a. Select the group in your table, and in the “properties window” the “hidden property” of “visibility” should have “=IIF(Level()=0, False,True)” – this means that only the “first parent” is visible

b. In the “toggleItem property select the name of the textbox that contains “the members of the parent-child dimension”)

I hope this is useful for you.

Telmo Moreira

Yes this is realy helpfull. But next when you collapse one its straight under the parent one. i want it more to the right. How can i do that for all levels under the parent level?

Kind regards,

Rob

|||

Hi,

In my previous post, I forgot to talk about the "padding property". So, in the textbox that contains “the members of the parent-child dimension”, select the "left property" of the "padding" in the "window properties" and write :
=Level() * 15 & "pt" (Note that this is an expression.)
This means that we indent each member with 15 * LevelN spaces, where LevelN is the current level of that member.

Have a nice day ,

Telmo Moreira

|||

Hello Telmo,

Thanks for your answer!! That works great. Now i have another question. I am trying to do your example with the employees parent child hierarchy in Dim employee from the adventure work cube. And it work also with the toggling. But when a parent name is the same as a child I don’t see the child and is it is being aggregate with the parent. So when you expand one and you sum the child’s you miss some data because the child with the same name as the parent is not showing in the report. I have tried a lot but how can i solve this problem? In the dataset when i run the mdx query i see the child which i miss in the report. I hope you can help me!

Thanks,

Robbert

|||

Hello Robbert,

Try this:

Modify your mdx query and instead of getting the "member caption" of the parent you should get the "unique name". Also you need to create a calculated member that gets the unique name of the current member. Then you must edit the group of your table and set the group expression to the dataset field that has the unique name of the current member; next set "the parent group expression" to the dataset field that has the unique name of the parent.

Take a look to the following query (the modifications are shown in bold)

WITH

MEMBER [Measures].[Conta Raz?o Parent Unique Name] as '[ContaRaz?o].[Default Hierarchy].CurrentMember.Parent.UniqueName'

MEMBER [Measures].[Conta Raz?o Current Unique Name] as '[ContaRaz?o].[Default Hierarchy].CurrentMember.UniqueName'

SELECT

{ [Measures].[Real], [Measures].[Conta Raz?o Current Unique Name], [Measures].[Conta Raz?o Parent Unique Name]} ON COLUMNS,

NON EMPTY {DESCENDANTS([Conta Raz?o].[Default Hierarchy])} ON ROWS

FROM [Controlo Or?amental]

I hope this solves your problem.

Best regards,

Telmo Moreira

|||

Hello Telmo,

Your Answer solved my problem!! Your great. Thanks for your answer.

Best regards,

Robbert

|||

Hello Telmo,

Everything works fine and all children will be shown. But when a parent has a child there is a toggle item (+) before the parent name. But when a parent has no child there is no toggle item (+) before the parent name. This look weird because it is not easy to see which level is showing. So I want before all members a toggle item (+) also when there is no child. I think this looks much better. But how can I do that? I hope you will help me one more time.

Best regards,

Robbert

|||

Hello Robbert,

I think that It's not possible, because, in this case, the toggle item (+) exists if the member has any child, i.e., there is some kind of group. So, I think that It's not possible. If anyone knows how to do please let me know.

Best regards,

Telmo Moreira

|||

Hello Robbert,

I have good news for you!

You want that every member should have a toggle item (+), even that member has no child, right?

So try this:

1. Insert a new row below the table group

2. the row should have a small height (say 0,1cm)

3. Select the entire row and set the "hidden property" of "visibility" to True. The "toggleitem property" should have the name of the texbox that contains the members of the parent-child dimension.

4. Et voilá... when we expand a member that has any child, we also expand that row; if the member has no child, only the row is expanded. It means that always exist the (+) before a member.

I think this solves your problem.

Best regards,

Telmo Moreira

Creating report based on parent-child dimension

Hi

I have a problem to create a report based on a parent child-dimension

When I go to reporting services, and I create a new report based on a cube, I drag my parent-child dimension to my data-layout. Then the dimension is immediatily ragged down.

Anyone an idea to solve this.You need to group the data on the UniqueName property and set the parent group expression to the ParentUniqueName property.|||Does anyone have an example to create a report based on a parent-child dimensions

Thx|||Brian

I changed the report with the grouping on uniquename and the parent group on parentuniquename

But now I got only the top level

How can I drill down?|||I have the same problem and didnt' find a solution for it. What I really want are the little +/- signs so i can open/close the children of the parent.
Didn't find anything ... what I do now is ident the children. This can be done using an expressing in the value property and using the LevelNumber to add spaces in front of the value ...
Not quit what I want ....
If you find a solution please share it here ...|||

hello,

I have a similar problem in RS2005:

In my report I see all levels in one column (How did g4rc manage to see only top level?...). However they are shown in a way that I think that if I only could get it to toggle correctly it will work out fine...

I would like my P/C dimensionto display just as it does when I use AS. Expanding top level (by pressing +), viewing next level down where I can "open" (by pressing +) next level and so on. Does anyone know how this is done or suggestions on how to solve it?

This is the kind of display I would like:

-A 100

-B 50
D 25
E 25

+C 50

Sincerely,

|||

Unfortunately, I’ve now these problems.

To solve these problems I followed these steps:

1. Alter the mdx query

2. Define the parent group in the report layout

3. Define the toggle item

1. Alter the mdx query

a. You should add a calculated member that gets the parent of your current member. Look to the following query (the dimension “Conta Raz?o” is a parent-child dimension)

WITH

MEMBER [Measures].[Conta Raz?o Parent Label] as '[ContaRaz?o].[Default Hierarchy].CurrentMember.Parent.MEMBER_CAPTION'

SELECT

{ [Measures].[Real], [Measures].[Conta Raz?o Parent Label]} ON COLUMNS,

NON EMPTY {DESCENDANTS([Conta Raz?o].[Default Hierarchy])} ON ROWS

FROM [Controlo Or?amental]

2. Define the parent group in the report layout

a) Select the group in your table, “edit group”, and in “parent group” property select the field that contains the parent of your dimension member (in my case, I select the “=Fields!Conta_Raz?o_Parent_Label.Value”; the query above has a calculated member that gets the parent of the current member )

3. Define the toggle item

a. Select the group in your table, and the “hidden property” of “visibility” should have “=IIF(Level()=0, False,True)” – this means that only the “first parent” is visible

b. In the “toggleItem property select the name of the textbox that contains “the members of the parent-child dimension”)

I hope this is useful for you.

Telmo Moreira

|||

Telmo Moreira wrote:

Unfortunately, I’ve now these problems.

To solve these problems I follow these steps:

1. Alter the mdx query

2. Define the parent group in the report layout

3. Define the toggle item

1. Alter the mdx query

a. You should add a calculated member that gets the parent of your current member. Look to the following query (the dimension “Conta Raz?o” is a parent-child dimension)

WITH

MEMBER [Measures].[Conta Raz?o Parent Label] as '[ContaRaz?o].[Default Hierarchy].CurrentMember.Parent.MEMBER_CAPTION'

SELECT

{ [Measures].[Real} ON COLUMNS,

NON EMPTY {DESCENDANTS([Conta Raz?o].[Default Hierarchy])} ON ROWS

FROM [Controlo Or?amental]

2. Define the parent group in the report layout

a) Select the group in your table, “edit group”, and in “parent group” property select the field that contains the parent of your dimension member (in my case, I select the “=Fields!Conta_Raz?o_Parent_Label.Value”; the query above has a calculated member that gets the parent of the current member )

3. Define the toggle item

a. Select the group in your table, and in the “properties window” the “hidden property” of “visibility” should have “=IIF(Level()=0, False,True)” – this means that only the “first parent” is visible

b. In the “toggleItem property select the name of the textbox that contains “the members of the parent-child dimension”)

I hope this is useful for you.

Telmo Moreira

Yes this is realy helpfull. But next when you collapse one its straight under the parent one. i want it more to the right. How can i do that for all levels under the parent level?

Kind regards,

Rob

|||

Hi,

In my previous post, I forgot to talk about the "padding property". So, in the textbox that contains “the members of the parent-child dimension”, select the "left property" of the "padding" in the "window properties" and write :
=Level() * 15 & "pt" (Note that this is an expression.)
This means that we indent each member with 15 * LevelN spaces, where LevelN is the current level of that member.

Have a nice day ,

Telmo Moreira

|||

Hello Telmo,

Thanks for your answer!! That works great. Now i have another question. I am trying to do your example with the employees parent child hierarchy in Dim employee from the adventure work cube. And it work also with the toggling. But when a parent name is the same as a child I don’t see the child and is it is being aggregate with the parent. So when you expand one and you sum the child’s you miss some data because the child with the same name as the parent is not showing in the report. I have tried a lot but how can i solve this problem? In the dataset when i run the mdx query i see the child which i miss in the report. I hope you can help me!

Thanks,

Robbert

|||

Hello Robbert,

Try this:

Modify your mdx query and instead of getting the "member caption" of the parent you should get the "unique name". Also you need to create a calculated member that gets the unique name of the current member. Then you must edit the group of your table and set the group expression to the dataset field that has the unique name of the current member; next set "the parent group expression" to the dataset field that has the unique name of the parent.

Take a look to the following query (the modifications are shown in bold)

WITH

MEMBER [Measures].[Conta Raz?o Parent Unique Name] as '[ContaRaz?o].[Default Hierarchy].CurrentMember.Parent.UniqueName'

MEMBER [Measures].[Conta Raz?o Current Unique Name] as '[ContaRaz?o].[Default Hierarchy].CurrentMember.UniqueName'

SELECT

{ [Measures].[Real], [Measures].[Conta Raz?o Current Unique Name], [Measures].[Conta Raz?o Parent Unique Name]} ON COLUMNS,

NON EMPTY {DESCENDANTS([Conta Raz?o].[Default Hierarchy])} ON ROWS

FROM [Controlo Or?amental]

I hope this solves your problem.

Best regards,

Telmo Moreira

|||

Hello Telmo,

Your Answer solved my problem!! Your great. Thanks for your answer.

Best regards,

Robbert

|||

Hello Telmo,

Everything works fine and all children will be shown. But when a parent has a child there is a toggle item (+) before the parent name. But when a parent has no child there is no toggle item (+) before the parent name. This look weird because it is not easy to see which level is showing. So I want before all members a toggle item (+) also when there is no child. I think this looks much better. But how can I do that? I hope you will help me one more time.

Best regards,

Robbert

|||

Hello Robbert,

I think that It's not possible, because, in this case, the toggle item (+) exists if the member has any child, i.e., there is some kind of group. So, I think that It's not possible. If anyone knows how to do please let me know.

Best regards,

Telmo Moreira

|||

Hello Robbert,

I have good news for you!

You want that every member should have a toggle item (+), even that member has no child, right?

So try this:

1. Insert a new row below the table group

2. the row should have a small height (say 0,1cm)

3. Select the entire row and set the "hidden property" of "visibility" to True. The "toggleitem property" should have the name of the texbox that contains the members of the parent-child dimension.

4. Et voilá... when we expand a member that has any child, we also expand that row; if the member has no child, only the row is expanded. It means that always exist the (+) before a member.

I think this solves your problem.

Best regards,

Telmo Moreira

|||Hy everybody,

just another question...

Following your instructions it all works fine except for this:
I have a Hierarchy like
[-]1
[-]2
[+]4
5
3
Note that 4 and 5 or 2 and 3 that shuld be at the same leve appear not padded at the same distance (the problem is that [+]or[-] takes sone pixels).

Any Idea to solve!?
|||Sorry ;)

the hierarchy

[-]1
___[-]2
_______[+]4
_______ 5
___ 3

Creating report based on parent-child dimension

Hi

I have a problem to create a report based on a parent child-dimension

When I go to reporting services, and I create a new report based on a cube, I drag my parent-child dimension to my data-layout. Then the dimension is immediatily ragged down.

Anyone an idea to solve this.You need to group the data on the UniqueName property and set the parent group expression to the ParentUniqueName property.|||Does anyone have an example to create a report based on a parent-child dimensions

Thx|||Brian

I changed the report with the grouping on uniquename and the parent group on parentuniquename

But now I got only the top level

How can I drill down?|||I have the same problem and didnt' find a solution for it. What I really want are the little +/- signs so i can open/close the children of the parent.
Didn't find anything ... what I do now is ident the children. This can be done using an expressing in the value property and using the LevelNumber to add spaces in front of the value ...
Not quit what I want ....
If you find a solution please share it here ...|||

hello,

I have a similar problem in RS2005:

In my report I see all levels in one column (How did g4rc manage to see only top level?...). However they are shown in a way that I think that if I only could get it to toggle correctly it will work out fine...

I would like my P/C dimensionto display just as it does when I use AS. Expanding top level (by pressing +), viewing next level down where I can "open" (by pressing +) next level and so on. Does anyone know how this is done or suggestions on how to solve it?

This is the kind of display I would like:

-A 100

-B 50
D 25
E 25

+C 50

Sincerely,

|||

Unfortunately, I’ve now these problems.

To solve these problems I followed these steps:

1. Alter the mdx query

2. Define the parent group in the report layout

3. Define the toggle item

1. Alter the mdx query

a. You should add a calculated member that gets the parent of your current member. Look to the following query (the dimension “Conta Raz?o” is a parent-child dimension)

WITH

MEMBER [Measures].[Conta Raz?o Parent Label] as '[ContaRaz?o].[Default Hierarchy].CurrentMember.Parent.MEMBER_CAPTION'

SELECT

{ [Measures].[Real], [Measures].[Conta Raz?o Parent Label]} ON COLUMNS,

NON EMPTY {DESCENDANTS([Conta Raz?o].[Default Hierarchy])} ON ROWS

FROM [Controlo Or?amental]

2. Define the parent group in the report layout

a) Select the group in your table, “edit group”, and in “parent group” property select the field that contains the parent of your dimension member (in my case, I select the “=Fields!Conta_Raz?o_Parent_Label.Value”; the query above has a calculated member that gets the parent of the current member )

3. Define the toggle item

a. Select the group in your table, and the “hidden property” of “visibility” should have “=IIF(Level()=0, False,True)” – this means that only the “first parent” is visible

b. In the “toggleItem property select the name of the textbox that contains “the members of the parent-child dimension”)

I hope this is useful for you.

Telmo Moreira

|||

Telmo Moreira wrote:

Unfortunately, I’ve now these problems.

To solve these problems I follow these steps:

1. Alter the mdx query

2. Define the parent group in the report layout

3. Define the toggle item

1. Alter the mdx query

a. You should add a calculated member that gets the parent of your current member. Look to the following query (the dimension “Conta Raz?o” is a parent-child dimension)

WITH

MEMBER [Measures].[Conta Raz?o Parent Label] as '[ContaRaz?o].[Default Hierarchy].CurrentMember.Parent.MEMBER_CAPTION'

SELECT

{ [Measures].[Real} ON COLUMNS,

NON EMPTY {DESCENDANTS([Conta Raz?o].[Default Hierarchy])} ON ROWS

FROM [Controlo Or?amental]

2. Define the parent group in the report layout

a) Select the group in your table, “edit group”, and in “parent group” property select the field that contains the parent of your dimension member (in my case, I select the “=Fields!Conta_Raz?o_Parent_Label.Value”; the query above has a calculated member that gets the parent of the current member )

3. Define the toggle item

a. Select the group in your table, and in the “properties window” the “hidden property” of “visibility” should have “=IIF(Level()=0, False,True)” – this means that only the “first parent” is visible

b. In the “toggleItem property select the name of the textbox that contains “the members of the parent-child dimension”)

I hope this is useful for you.

Telmo Moreira

Yes this is realy helpfull. But next when you collapse one its straight under the parent one. i want it more to the right. How can i do that for all levels under the parent level?

Kind regards,

Rob

|||

Hi,

In my previous post, I forgot to talk about the "padding property". So, in the textbox that contains “the members of the parent-child dimension”, select the "left property" of the "padding" in the "window properties" and write :
=Level() * 15 & "pt" (Note that this is an expression.)
This means that we indent each member with 15 * LevelN spaces, where LevelN is the current level of that member.

Have a nice day ,

Telmo Moreira

|||

Hello Telmo,

Thanks for your answer!! That works great. Now i have another question. I am trying to do your example with the employees parent child hierarchy in Dim employee from the adventure work cube. And it work also with the toggling. But when a parent name is the same as a child I don’t see the child and is it is being aggregate with the parent. So when you expand one and you sum the child’s you miss some data because the child with the same name as the parent is not showing in the report. I have tried a lot but how can i solve this problem? In the dataset when i run the mdx query i see the child which i miss in the report. I hope you can help me!

Thanks,

Robbert

|||

Hello Robbert,

Try this:

Modify your mdx query and instead of getting the "member caption" of the parent you should get the "unique name". Also you need to create a calculated member that gets the unique name of the current member. Then you must edit the group of your table and set the group expression to the dataset field that has the unique name of the current member; next set "the parent group expression" to the dataset field that has the unique name of the parent.

Take a look to the following query (the modifications are shown in bold)

WITH

MEMBER [Measures].[Conta Raz?o Parent Unique Name] as '[ContaRaz?o].[Default Hierarchy].CurrentMember.Parent.UniqueName'

MEMBER [Measures].[Conta Raz?o Current Unique Name] as '[ContaRaz?o].[Default Hierarchy].CurrentMember.UniqueName'

SELECT

{ [Measures].[Real], [Measures].[Conta Raz?o Current Unique Name], [Measures].[Conta Raz?o Parent Unique Name]} ON COLUMNS,

NON EMPTY {DESCENDANTS([Conta Raz?o].[Default Hierarchy])} ON ROWS

FROM [Controlo Or?amental]

I hope this solves your problem.

Best regards,

Telmo Moreira

|||

Hello Telmo,

Your Answer solved my problem!! Your great. Thanks for your answer.

Best regards,

Robbert

|||

Hello Telmo,

Everything works fine and all children will be shown. But when a parent has a child there is a toggle item (+) before the parent name. But when a parent has no child there is no toggle item (+) before the parent name. This look weird because it is not easy to see which level is showing. So I want before all members a toggle item (+) also when there is no child. I think this looks much better. But how can I do that? I hope you will help me one more time.

Best regards,

Robbert

|||

Hello Robbert,

I think that It's not possible, because, in this case, the toggle item (+) exists if the member has any child, i.e., there is some kind of group. So, I think that It's not possible. If anyone knows how to do please let me know.

Best regards,

Telmo Moreira

|||

Hello Robbert,

I have good news for you!

You want that every member should have a toggle item (+), even that member has no child, right?

So try this:

1. Insert a new row below the table group

2. the row should have a small height (say 0,1cm)

3. Select the entire row and set the "hidden property" of "visibility" to True. The "toggleitem property" should have the name of the texbox that contains the members of the parent-child dimension.

4. Et voilá... when we expand a member that has any child, we also expand that row; if the member has no child, only the row is expanded. It means that always exist the (+) before a member.

I think this solves your problem.

Best regards,

Telmo Moreira

Creating report based on parent-child dimension

Hi

I have a problem to create a report based on a parent child-dimension

When I go to reporting services, and I create a new report based on a cube, I drag my parent-child dimension to my data-layout. Then the dimension is immediatily ragged down.

Anyone an idea to solve this.

You need to group the data on the UniqueName property and set the parent group expression to the ParentUniqueName property.|||Does anyone have an example to create a report based on a parent-child dimensions

Thx|||Brian

I changed the report with the grouping on uniquename and the parent group on parentuniquename

But now I got only the top level

How can I drill down?
|||I have the same problem and didnt' find a solution for it. What I really want are the little +/- signs so i can open/close the children of the parent.
Didn't find anything ... what I do now is ident the children. This can be done using an expressing in the value property and using the LevelNumber to add spaces in front of the value ...
Not quit what I want ....
If you find a solution please share it here ...|||

hello,

I have a similar problem in RS2005:

In my report I see all levels in one column (How did g4rc manage to see only top level?...). However they are shown in a way that I think that if I only could get it to toggle correctly it will work out fine...

I would like my P/C dimensionto display just as it does when I use AS. Expanding top level (by pressing +), viewing next level down where I can "open" (by pressing +) next level and so on. Does anyone know how this is done or suggestions on how to solve it?

This is the kind of display I would like:

-A 100

-B 50
D 25
E 25

+C 50

Sincerely,

|||

Unfortunately, I’ve now these problems.

To solve these problems I followed these steps:

1. Alter the mdx query

2. Define the parent group in the report layout

3. Define the toggle item

1. Alter the mdx query

a. You should add a calculated member that gets the parent of your current member. Look to the following query (the dimension “Conta Raz?o” is a parent-child dimension)

WITH

MEMBER [Measures].[Conta Raz?o Parent Label] as '[ContaRaz?o].[Default Hierarchy].CurrentMember.Parent.MEMBER_CAPTION'

SELECT

{ [Measures].[Real], [Measures].[Conta Raz?o Parent Label]} ON COLUMNS,

NON EMPTY {DESCENDANTS([Conta Raz?o].[Default Hierarchy])} ON ROWS

FROM [Controlo Or?amental]

2. Define the parent group in the report layout

a) Select the group in your table, “edit group”, and in “parent group” property select the field that contains the parent of your dimension member (in my case, I select the “=Fields!Conta_Raz?o_Parent_Label.Value”; the query above has a calculated member that gets the parent of the current member )

3. Define the toggle item

a. Select the group in your table, and the “hidden property” of “visibility” should have “=IIF(Level()=0, False,True)” – this means that only the “first parent” is visible

b. In the “toggleItem property select the name of the textbox that contains “the members of the parent-child dimension”)

I hope this is useful for you.

Telmo Moreira

|||

Telmo Moreira wrote:

Unfortunately, I’ve now these problems.

To solve these problems I follow these steps:

1. Alter the mdx query

2. Define the parent group in the report layout

3. Define the toggle item

1. Alter the mdx query

a. You should add a calculated member that gets the parent of your current member. Look to the following query (the dimension “Conta Raz?o” is a parent-child dimension)

WITH

MEMBER [Measures].[Conta Raz?o Parent Label] as '[ContaRaz?o].[Default Hierarchy].CurrentMember.Parent.MEMBER_CAPTION'

SELECT

{ [Measures].[Real} ON COLUMNS,

NON EMPTY {DESCENDANTS([Conta Raz?o].[Default Hierarchy])} ON ROWS

FROM [Controlo Or?amental]

2. Define the parent group in the report layout

a) Select the group in your table, “edit group”, and in “parent group” property select the field that contains the parent of your dimension member (in my case, I select the “=Fields!Conta_Raz?o_Parent_Label.Value”; the query above has a calculated member that gets the parent of the current member )

3. Define the toggle item

a. Select the group in your table, and in the “properties window” the “hidden property” of “visibility” should have “=IIF(Level()=0, False,True)” – this means that only the “first parent” is visible

b. In the “toggleItem property select the name of the textbox that contains “the members of the parent-child dimension”)

I hope this is useful for you.

Telmo Moreira

Yes this is realy helpfull. But next when you collapse one its straight under the parent one. i want it more to the right. How can i do that for all levels under the parent level?

Kind regards,

Rob

|||

Hi,

In my previous post, I forgot to talk about the "padding property". So, in the textbox that contains “the members of the parent-child dimension”, select the "left property" of the "padding" in the "window properties" and write :
=Level() * 15 & "pt" (Note that this is an expression.)
This means that we indent each member with 15 * LevelN spaces, where LevelN is the current level of that member.

Have a nice day ,

Telmo Moreira

|||

Hello Telmo,

Thanks for your answer!! That works great. Now i have another question. I am trying to do your example with the employees parent child hierarchy in Dim employee from the adventure work cube. And it work also with the toggling. But when a parent name is the same as a child I don’t see the child and is it is being aggregate with the parent. So when you expand one and you sum the child’s you miss some data because the child with the same name as the parent is not showing in the report. I have tried a lot but how can i solve this problem? In the dataset when i run the mdx query i see the child which i miss in the report. I hope you can help me!

Thanks,

Robbert

|||

Hello Robbert,

Try this:

Modify your mdx query and instead of getting the "member caption" of the parent you should get the "unique name". Also you need to create a calculated member that gets the unique name of the current member. Then you must edit the group of your table and set the group expression to the dataset field that has the unique name of the current member; next set "the parent group expression" to the dataset field that has the unique name of the parent.

Take a look to the following query (the modifications are shown in bold)

WITH

MEMBER [Measures].[Conta Raz?o Parent Unique Name] as '[ContaRaz?o].[Default Hierarchy].CurrentMember.Parent.UniqueName'

MEMBER [Measures].[Conta Raz?o Current Unique Name] as '[ContaRaz?o].[Default Hierarchy].CurrentMember.UniqueName'

SELECT

{ [Measures].[Real], [Measures].[Conta Raz?o Current Unique Name], [Measures].[Conta Raz?o Parent Unique Name]} ON COLUMNS,

NON EMPTY {DESCENDANTS([Conta Raz?o].[Default Hierarchy])} ON ROWS

FROM [Controlo Or?amental]

I hope this solves your problem.

Best regards,

Telmo Moreira

|||

Hello Telmo,

Your Answer solved my problem!! Your great. Thanks for your answer.

Best regards,

Robbert

|||

Hello Telmo,

Everything works fine and all children will be shown. But when a parent has a child there is a toggle item (+) before the parent name. But when a parent has no child there is no toggle item (+) before the parent name. This look weird because it is not easy to see which level is showing. So I want before all members a toggle item (+) also when there is no child. I think this looks much better. But how can I do that? I hope you will help me one more time.

Best regards,

Robbert

|||

Hello Robbert,

I think that It's not possible, because, in this case, the toggle item (+) exists if the member has any child, i.e., there is some kind of group. So, I think that It's not possible. If anyone knows how to do please let me know.

Best regards,

Telmo Moreira

|||

Hello Robbert,

I have good news for you!

You want that every member should have a toggle item (+), even that member has no child, right?

So try this:

1. Insert a new row below the table group

2. the row should have a small height (say 0,1cm)

3. Select the entire row and set the "hidden property" of "visibility" to True. The "toggleitem property" should have the name of the texbox that contains the members of the parent-child dimension.

4. Et voilá... when we expand a member that has any child, we also expand that row; if the member has no child, only the row is expanded. It means that always exist the (+) before a member.

I think this solves your problem.

Best regards,

Telmo Moreira

Creating report based on parent-child dimension

Hi

I have a problem to create a report based on a parent child-dimension

When I go to reporting services, and I create a new report based on a cube, I drag my parent-child dimension to my data-layout. Then the dimension is immediatily ragged down.

Anyone an idea to solve this.

You need to group the data on the UniqueName property and set the parent group expression to the ParentUniqueName property.|||Does anyone have an example to create a report based on a parent-child dimensions

Thx|||Brian

I changed the report with the grouping on uniquename and the parent group on parentuniquename

But now I got only the top level

How can I drill down?
|||I have the same problem and didnt' find a solution for it. What I really want are the little +/- signs so i can open/close the children of the parent.
Didn't find anything ... what I do now is ident the children. This can be done using an expressing in the value property and using the LevelNumber to add spaces in front of the value ...
Not quit what I want ....
If you find a solution please share it here ...|||

hello,

I have a similar problem in RS2005:

In my report I see all levels in one column (How did g4rc manage to see only top level?...). However they are shown in a way that I think that if I only could get it to toggle correctly it will work out fine...

I would like my P/C dimensionto display just as it does when I use AS. Expanding top level (by pressing +), viewing next level down where I can "open" (by pressing +) next level and so on. Does anyone know how this is done or suggestions on how to solve it?

This is the kind of display I would like:

-A 100

-B 50
D 25
E 25

+C 50

Sincerely,

|||

Unfortunately, I’ve now these problems.

To solve these problems I followed these steps:

1. Alter the mdx query

2. Define the parent group in the report layout

3. Define the toggle item

1. Alter the mdx query

a. You should add a calculated member that gets the parent of your current member. Look to the following query (the dimension “Conta Raz?o” is a parent-child dimension)

WITH

MEMBER [Measures].[Conta Raz?o Parent Label]as'[ContaRaz?o].[Default Hierarchy].CurrentMember.Parent.MEMBER_CAPTION'

SELECT

{ [Measures].[Real], [Measures].[Conta Raz?o Parent Label]}ONCOLUMNS,

NONEMPTY {DESCENDANTS([Conta Raz?o].[Default Hierarchy])}ONROWS

FROM [Controlo Or?amental]

2. Define the parent group in the report layout

a) Select the group in your table, “edit group”, and in “parent group” property select the field that contains the parent of your dimension member (in my case, I select the “=Fields!Conta_Raz?o_Parent_Label.Value”; the query above has a calculated member that gets the parent of the current member )

3. Define the toggle item

a. Select the group in your table, and the “hidden property” of “visibility” should have “=IIF(Level()=0, False,True)” – this means that only the “first parent” is visible

b. In the “toggleItem property select the name of the textbox that contains “the members of the parent-child dimension”)

I hope this is useful for you.

Telmo Moreira

|||

Telmo Moreira wrote:

Unfortunately, I’ve now these problems.

To solve these problems I follow these steps:

1. Alter the mdx query

2. Define the parent group in the report layout

3. Define the toggle item

1. Alter the mdx query

a. You should add a calculated member that gets the parent of your current member. Look to the following query (the dimension “Conta Raz?o” is a parent-child dimension)

WITH

MEMBER [Measures].[Conta Raz?o Parent Label]as'[ContaRaz?o].[Default Hierarchy].CurrentMember.Parent.MEMBER_CAPTION'

SELECT

{ [Measures].[Real}ONCOLUMNS,

NONEMPTY {DESCENDANTS([Conta Raz?o].[Default Hierarchy])}ONROWS

FROM [Controlo Or?amental]

2. Define the parent group in the report layout

a) Select the group in your table, “edit group”, and in “parent group” property select the field that contains the parent of your dimension member (in my case, I select the “=Fields!Conta_Raz?o_Parent_Label.Value”; the query above has a calculated member that gets the parent of the current member )

3. Define the toggle item

a. Select the group in your table, and in the “properties window” the “hidden property” of “visibility” should have “=IIF(Level()=0, False,True)” – this means that only the “first parent” is visible

b. In the “toggleItem property select the name of the textbox that contains “the members of the parent-child dimension”)

I hope this is useful for you.

Telmo Moreira

Yes this is realy helpfull. But next when you collapse one its straight under the parent one. i want it more to the right. How can i do that for all levels under the parent level?

Kind regards,

Rob

|||

Hi,

In my previous post, I forgot to talk about the "padding property". So, in the textbox that contains “the members of the parent-child dimension”, select the "left property" of the "padding" in the "window properties" and write :
=Level() * 15 & "pt" (Note that this is an expression.)
This means that we indent each member with 15 * LevelN spaces, where LevelN is the current level of that member.

Have a nice day ,

Telmo Moreira

|||

Hello Telmo,

Thanks for your answer!! That works great. Now i have another question. I am trying to do your example with the employees parent child hierarchy in Dim employee from the adventure work cube. And it work also with the toggling. But when a parent name is the same as a child I don’t see the child and is it is being aggregate with the parent. So when you expand one and you sum the child’s you miss some data because the child with the same name as the parent is not showing in the report. I have tried a lot but how can i solve this problem? In the dataset when i run the mdx query i see the child which i miss in the report. I hope you can help me!

Thanks,

Robbert

|||

HelloRobbert,

Try this:

Modify your mdx query and instead of getting the "member caption" of the parent you should get the "unique name". Also you need to create a calculated member that gets the unique name of the current member. Then you must edit the group of your table and set the group expression to the dataset field that has the unique name of the current member; next set "the parent group expression" to the dataset field that has the unique name of the parent.

Take a look to the following query (the modifications are shown in bold)

WITH

MEMBER [Measures].[Conta Raz?o Parent Unique Name]as'[ContaRaz?o].[Default Hierarchy].CurrentMember.Parent.UniqueName'

MEMBER [Measures].[Conta Raz?o Current Unique Name]as'[ContaRaz?o].[Default Hierarchy].CurrentMember.UniqueName'

SELECT

{ [Measures].[Real], [Measures].[Conta Raz?o Current Unique Name], [Measures].[Conta Raz?o Parent Unique Name]} ONCOLUMNS,

NONEMPTY {DESCENDANTS([Conta Raz?o].[Default Hierarchy])}ONROWS

FROM [Controlo Or?amental]

I hope this solves your problem.

Best regards,

Telmo Moreira

|||

Hello Telmo,

Your Answer solved my problem!! Your great. Thanks for your answer.

Best regards,

Robbert

|||

Hello Telmo,

Everything works fine and all children will be shown. But when a parent has a child there is a toggle item (+) before the parent name. But when a parent has no child there is no toggle item (+) before the parent name. This look weird because it is not easy to see which level is showing. So I want before all members a toggle item (+) also when there is no child. I think this looks much better. But how can I do that? I hope you will help me one more time.

Best regards,

Robbert

|||

Hello Robbert,

I think that It's not possible, because, in this case, the toggle item (+) exists if the member has any child, i.e., there is some kind of group. So, I think that It's not possible. If anyone knows how to do please let me know.

Best regards,

Telmo Moreira

|||

Hello Robbert,

I have good news for you!

You want that every member should have a toggle item (+), even that member has no child, right?

So try this:

1. Insert a new row below the table group

2. the row should have a small height (say 0,1cm)

3. Select the entire row and set the "hidden property" of "visibility" to True. The "toggleitem property" should have the name of the texbox that contains the members of the parent-child dimension.

4. Et voilá... when we expand a member that has any child, we also expand that row; if the member has no child, only the row is expanded. It means that always exist the (+) before a member.

I think this solves your problem.

Best regards,

Telmo Moreira

Creating report based on parent-child dimension

Hi

I have a problem to create a report based on a parent child-dimension

When I go to reporting services, and I create a new report based on a cube, I drag my parent-child dimension to my data-layout. Then the dimension is immediatily ragged down.

Anyone an idea to solve this.

You need to group the data on the UniqueName property and set the parent group expression to the ParentUniqueName property.|||Does anyone have an example to create a report based on a parent-child dimensions

Thx|||Brian

I changed the report with the grouping on uniquename and the parent group on parentuniquename

But now I got only the top level

How can I drill down?
|||I have the same problem and didnt' find a solution for it. What I really want are the little +/- signs so i can open/close the children of the parent.
Didn't find anything ... what I do now is ident the children. This can be done using an expressing in the value property and using the LevelNumber to add spaces in front of the value ...
Not quit what I want ....
If you find a solution please share it here ...|||

hello,

I have a similar problem in RS2005:

In my report I see all levels in one column (How did g4rc manage to see only top level?...). However they are shown in a way that I think that if I only could get it to toggle correctly it will work out fine...

I would like my P/C dimensionto display just as it does when I use AS. Expanding top level (by pressing +), viewing next level down where I can "open" (by pressing +) next level and so on. Does anyone know how this is done or suggestions on how to solve it?

This is the kind of display I would like:

-A 100

-B 50
D 25
E 25

+C 50

Sincerely,

|||

Unfortunately, I’ve now these problems.

To solve these problems I followed these steps:

1. Alter the mdx query

2. Define the parent group in the report layout

3. Define the toggle item

1. Alter the mdx query

a. You should add a calculated member that gets the parent of your current member. Look to the following query (the dimension “Conta Raz?o” is a parent-child dimension)

WITH

MEMBER [Measures].[Conta Raz?o Parent Label] as '[ContaRaz?o].[Default Hierarchy].CurrentMember.Parent.MEMBER_CAPTION'

SELECT

{ [Measures].[Real], [Measures].[Conta Raz?o Parent Label]} ON COLUMNS,

NON EMPTY {DESCENDANTS([Conta Raz?o].[Default Hierarchy])} ON ROWS

FROM [Controlo Or?amental]

2. Define the parent group in the report layout

a) Select the group in your table, “edit group”, and in “parent group” property select the field that contains the parent of your dimension member (in my case, I select the “=Fields!Conta_Raz?o_Parent_Label.Value”; the query above has a calculated member that gets the parent of the current member )

3. Define the toggle item

a. Select the group in your table, and the “hidden property” of “visibility” should have “=IIF(Level()=0, False,True)” – this means that only the “first parent” is visible

b. In the “toggleItem property select the name of the textbox that contains “the members of the parent-child dimension”)

I hope this is useful for you.

Telmo Moreira

|||

Telmo Moreira wrote:

Unfortunately, I’ve now these problems.

To solve these problems I follow these steps:

1. Alter the mdx query

2. Define the parent group in the report layout

3. Define the toggle item

1. Alter the mdx query

a. You should add a calculated member that gets the parent of your current member. Look to the following query (the dimension “Conta Raz?o” is a parent-child dimension)

WITH

MEMBER [Measures].[Conta Raz?o Parent Label] as '[ContaRaz?o].[Default Hierarchy].CurrentMember.Parent.MEMBER_CAPTION'

SELECT

{ [Measures].[Real} ON COLUMNS,

NON EMPTY {DESCENDANTS([Conta Raz?o].[Default Hierarchy])} ON ROWS

FROM [Controlo Or?amental]

2. Define the parent group in the report layout

a) Select the group in your table, “edit group”, and in “parent group” property select the field that contains the parent of your dimension member (in my case, I select the “=Fields!Conta_Raz?o_Parent_Label.Value”; the query above has a calculated member that gets the parent of the current member )

3. Define the toggle item

a. Select the group in your table, and in the “properties window” the “hidden property” of “visibility” should have “=IIF(Level()=0, False,True)” – this means that only the “first parent” is visible

b. In the “toggleItem property select the name of the textbox that contains “the members of the parent-child dimension”)

I hope this is useful for you.

Telmo Moreira

Yes this is realy helpfull. But next when you collapse one its straight under the parent one. i want it more to the right. How can i do that for all levels under the parent level?

Kind regards,

Rob

|||

Hi,

In my previous post, I forgot to talk about the "padding property". So, in the textbox that contains “the members of the parent-child dimension”, select the "left property" of the "padding" in the "window properties" and write :
=Level() * 15 & "pt" (Note that this is an expression.)
This means that we indent each member with 15 * LevelN spaces, where LevelN is the current level of that member.

Have a nice day ,

Telmo Moreira

|||

Hello Telmo,

Thanks for your answer!! That works great. Now i have another question. I am trying to do your example with the employees parent child hierarchy in Dim employee from the adventure work cube. And it work also with the toggling. But when a parent name is the same as a child I don’t see the child and is it is being aggregate with the parent. So when you expand one and you sum the child’s you miss some data because the child with the same name as the parent is not showing in the report. I have tried a lot but how can i solve this problem? In the dataset when i run the mdx query i see the child which i miss in the report. I hope you can help me!

Thanks,

Robbert

|||

Hello Robbert,

Try this:

Modify your mdx query and instead of getting the "member caption" of the parent you should get the "unique name". Also you need to create a calculated member that gets the unique name of the current member. Then you must edit the group of your table and set the group expression to the dataset field that has the unique name of the current member; next set "the parent group expression" to the dataset field that has the unique name of the parent.

Take a look to the following query (the modifications are shown in bold)

WITH

MEMBER [Measures].[Conta Raz?o Parent Unique Name] as '[ContaRaz?o].[Default Hierarchy].CurrentMember.Parent.UniqueName'

MEMBER [Measures].[Conta Raz?o Current Unique Name] as '[ContaRaz?o].[Default Hierarchy].CurrentMember.UniqueName'

SELECT

{ [Measures].[Real], [Measures].[Conta Raz?o Current Unique Name], [Measures].[Conta Raz?o Parent Unique Name]} ON COLUMNS,

NON EMPTY {DESCENDANTS([Conta Raz?o].[Default Hierarchy])} ON ROWS

FROM [Controlo Or?amental]

I hope this solves your problem.

Best regards,

Telmo Moreira

|||

Hello Telmo,

Your Answer solved my problem!! Your great. Thanks for your answer.

Best regards,

Robbert

|||

Hello Telmo,

Everything works fine and all children will be shown. But when a parent has a child there is a toggle item (+) before the parent name. But when a parent has no child there is no toggle item (+) before the parent name. This look weird because it is not easy to see which level is showing. So I want before all members a toggle item (+) also when there is no child. I think this looks much better. But how can I do that? I hope you will help me one more time.

Best regards,

Robbert

|||

Hello Robbert,

I think that It's not possible, because, in this case, the toggle item (+) exists if the member has any child, i.e., there is some kind of group. So, I think that It's not possible. If anyone knows how to do please let me know.

Best regards,

Telmo Moreira

|||

Hello Robbert,

I have good news for you!

You want that every member should have a toggle item (+), even that member has no child, right?

So try this:

1. Insert a new row below the table group

2. the row should have a small height (say 0,1cm)

3. Select the entire row and set the "hidden property" of "visibility" to True. The "toggleitem property" should have the name of the texbox that contains the members of the parent-child dimension.

4. Et voilá... when we expand a member that has any child, we also expand that row; if the member has no child, only the row is expanded. It means that always exist the (+) before a member.

I think this solves your problem.

Best regards,

Telmo Moreira

Creating Partions on Developer Edition

Hi,

I was trying to partition my cube built in MSAS 2005 Developer Edition based on time dimension, however I was running into a lot of issues.

I just read an article which states that cube partition is only possible on Enterprise Edition of MSAS 2005 can someone verify if it is true and if thats the case then why do they provide an option to partition the cube in the developer edition.

Thanks

You can create partitions on developer edition of SQL Server 2005/SSAS2005. Build partitions in BI-Developer Studio, process and check the cube in Management Studio after. Under the Cubes-folder, The cube and the measure group for that cube you can see the partitions.

Regards

Thomas Ivarsson

|||

Thomas,

I tried doing that, however if I try to create a partition using query binding and specify a select statement with a where clause. The SQL that MSAS 2005 generates when it builds the cube has a bunch of sub-selects and it fails to validate and so I am unble to build the cube with any new partitions

Thanks

|||

Developer Edition supports the functionallity of Enterprise Edition but with licensing limitations, so the problem is not related to the edition you are using. (Please see the licensing terms for details.) A more likely culprit is the relational provider you are using as it sounds like the provider does not support sub-selects. See http://msdn2.microsoft.com/en-us/library/ms175608.aspx for a list of supported relational providers.

|||

Ok. It works with the Dev Edition so your problem is probably not about the version of SQL Server. It can be as simple as to check if a key is text, integer or any other datatype. IN 2005 you can build SQL statements for partitions in the same way you write a where clause in TSQL.

Run the new profiler that catches all statements from SSAS2005 and see if any strange behavior appears.

Regards

Thomas Ivarsson

|||

Hi,

Thanks for the response guys.

Matt I had one quick question if I am able to process a cube using table binding as opposed to query binding to create partitions and the sql generated during processing of the cube does have sub selects and it does build the cube sucessfully I dont think I would have an issue with my provider correct?

Thomas : I am not quite clear about the part where you say " It can be as simple as to check if a key is text, integer or any other datatype" can you please expand on this

|||

I do these errors myself. A key is an integer but I write with a ' ' around it(or reverse). I do not think that my query bound partitions do subselects. It is a normal select query, with a where clause added for you, when I have tried it.

Regards

Thomas Ivarsson

|||If you are using one of the supported providers you should not have any problem. If you are not, it may be that some things are supported in sub-selects but other things are not so some queries may succeed while others fail. You should be able to see the queries the server is generating them and then you can check and see if they are supported by the provider.

Creating Partions on Developer Edition

Hi,

I was trying to partition my cube built in MSAS 2005 Developer Edition based on time dimension, however I was running into a lot of issues.

I just read an article which states that cube partition is only possible on Enterprise Edition of MSAS 2005 can someone verify if it is true and if thats the case then why do they provide an option to partition the cube in the developer edition.

Thanks

You can create partitions on developer edition of SQL Server 2005/SSAS2005. Build partitions in BI-Developer Studio, process and check the cube in Management Studio after. Under the Cubes-folder, The cube and the measure group for that cube you can see the partitions.

Regards

Thomas Ivarsson

|||

Thomas,

I tried doing that, however if I try to create a partition using query binding and specify a select statement with a where clause. The SQL that MSAS 2005 generates when it builds the cube has a bunch of sub-selects and it fails to validate and so I am unble to build the cube with any new partitions

Thanks

|||

Developer Edition supports the functionallity of Enterprise Edition but with licensing limitations, so the problem is not related to the edition you are using. (Please see the licensing terms for details.) A more likely culprit is the relational provider you are using as it sounds like the provider does not support sub-selects. See http://msdn2.microsoft.com/en-us/library/ms175608.aspx for a list of supported relational providers.

|||

Ok. It works with the Dev Edition so your problem is probably not about the version of SQL Server. It can be as simple as to check if a key is text, integer or any other datatype. IN 2005 you can build SQL statements for partitions in the same way you write a where clause in TSQL.

Run the new profiler that catches all statements from SSAS2005 and see if any strange behavior appears.

Regards

Thomas Ivarsson

|||

Hi,

Thanks for the response guys.

Matt I had one quick question if I am able to process a cube using table binding as opposed to query binding to create partitions and the sql generated during processing of the cube does have sub selects and it does build the cube sucessfully I dont think I would have an issue with my provider correct?

Thomas : I am not quite clear about the part where you say " It can be as simple as to check if a key is text, integer or any other datatype" can you please expand on this

|||

I do these errors myself. A key is an integer but I write with a ' ' around it(or reverse). I do not think that my query bound partitions do subselects. It is a normal select query, with a where clause added for you, when I have tried it.

Regards

Thomas Ivarsson

|||If you are using one of the supported providers you should not have any problem. If you are not, it may be that some things are supported in sub-selects but other things are not so some queries may succeed while others fail. You should be able to see the queries the server is generating them and then you can check and see if they are supported by the provider.

Creating Partions on Developer Edition

Hi,

I was trying to partition my cube built in MSAS 2005 Developer Edition based on time dimension, however I was running into a lot of issues.

I just read an article which states that cube partition is only possible on Enterprise Edition of MSAS 2005 can someone verify if it is true and if thats the case then why do they provide an option to partition the cube in the developer edition.

Thanks

You can create partitions on developer edition of SQL Server 2005/SSAS2005. Build partitions in BI-Developer Studio, process and check the cube in Management Studio after. Under the Cubes-folder, The cube and the measure group for that cube you can see the partitions.

Regards

Thomas Ivarsson

|||

Thomas,

I tried doing that, however if I try to create a partition using query binding and specify a select statement with a where clause. The SQL that MSAS 2005 generates when it builds the cube has a bunch of sub-selects and it fails to validate and so I am unble to build the cube with any new partitions

Thanks

|||

Developer Edition supports the functionallity of Enterprise Edition but with licensing limitations, so the problem is not related to the edition you are using. (Please see the licensing terms for details.) A more likely culprit is the relational provider you are using as it sounds like the provider does not support sub-selects. See http://msdn2.microsoft.com/en-us/library/ms175608.aspx for a list of supported relational providers.

|||

Ok. It works with the Dev Edition so your problem is probably not about the version of SQL Server. It can be as simple as to check if a key is text, integer or any other datatype. IN 2005 you can build SQL statements for partitions in the same way you write a where clause in TSQL.

Run the new profiler that catches all statements from SSAS2005 and see if any strange behavior appears.

Regards

Thomas Ivarsson

|||

Hi,

Thanks for the response guys.

Matt I had one quick question if I am able to process a cube using table binding as opposed to query binding to create partitions and the sql generated during processing of the cube does have sub selects and it does build the cube sucessfully I dont think I would have an issue with my provider correct?

Thomas : I am not quite clear about the part where you say " It can be as simple as to check if a key is text, integer or any other datatype" can you please expand on this

|||

I do these errors myself. A key is an integer but I write with a ' ' around it(or reverse). I do not think that my query bound partitions do subselects. It is a normal select query, with a where clause added for you, when I have tried it.

Regards

Thomas Ivarsson

|||If you are using one of the supported providers you should not have any problem. If you are not, it may be that some things are supported in sub-selects but other things are not so some queries may succeed while others fail. You should be able to see the queries the server is generating them and then you can check and see if they are supported by the provider.

Creating Local cube Using SQL Server / Analysis Services 2005

I am creating a local cube through a web page and everything is working properly when I am debugging but if I am not debugging, the following exception is returned:

x= Exception:File system error: Error opening file; \\?\C:\Program Files\Common Files\System\Ole DB\MDTempStore_1088_1_fm3by.tmp is not a disk file or file is not accessible.

StackTrace: at Microsoft.AnalysisServices.AdomdClient.AdomdConnection.XmlaClientProvider.HandleCreateLocalCube(AdomdErrorResponseException ex) at Microsoft.AnalysisServices.AdomdClient.AdomdConnection.XmlaClientProvider.Microsoft.AnalysisServices.AdomdClient.IExecuteProvider.Execute(ICommandContentProvider contentProvider, AdomdPropertyCollection commandProperties, IDataParameterCollection parameters) at Microsoft.AnalysisServices.AdomdClient.AdomdCommand.Execute()

The role defined for the cube is:

General tab: Read definition

Cubes tab: Access: read

Cubes: Local Cube/Drillthrough Access

Thanks,

Yones

Looks like a problem with security. Make sure the process you running within IIS has sufficient privileges to create a files local cube files.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Hi Edward,

Yes, it seems like it is a security problem. I have just found that it is working properly when using AS 2005 installed with (local) SQL server 2005 but it does not work with a named instance such as (ServerName\SQLServerInstanceName)

Could please be more specific about what privileges should be given?

Thanks,

Yones

|||

Not sure how named instance plays the role here.

Are you talking about SQL Server relational engine named instance or about Analysis Server named instance here?

If it is working with default instance, try and see what are the differences in the way named instance is setup.

There should be a little difference in the way you work against named instance.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Hi Edward,

Concerning named instance (MachineName\InstanceName) and default instance ((local)), I think if we have a named instance, it is for both SQL Server relational engine and Analysis Services. I do not think we can have a named instance for SQL Server relational engine and a default instance for Analysis Services (Please correct me if I am wrong)

It is working for the default instance (local) because the Web Server (IIS) and Analysis Services are on the same machine but for a named instance (MachineName\InstanceName), it is not working even the “MachineName” is the same for the Web Server (IIS)

Thanks,

Yones

|||

Here is a similar thread :

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

Edward Melomed.
--
This posting is provided "AS IS" with no warranties, and confers no rights.