creating temp table inside SP

I am doing a code review of a stored procedure.
What the procedure does is to create a temp table and then based on parameters sent to
the procedure, call different stored procedures which inserts into the temp table just
created.
Are their any issues with procedures creating temp table on the fly. Any scalability
issues?
There is a good explanation in the BOL. See "DBCC DBREINDEX".
AMB
"rkusenet" wrote:

> I am doing a code review of a stored procedure.
> What the procedure does is to create a temp table and then based on parameters sent to
> the procedure, call different stored procedures which inserts into the temp table just
> created.
> Are their any issues with procedures creating temp table on the fly. Any scalability
> issues?
>
|||Sorry, wrong place.
AMB
"Alejandro Mesa" wrote:
[vbcol=seagreen]
> There is a good explanation in the BOL. See "DBCC DBREINDEX".
>
> AMB
> "rkusenet" wrote:
|||Here's a short article - posted previously by Aaron - on the subject.
http://www.aspfaq.com/show.asp?id=2475
Enjoy.
"rkusenet" <usenet.rk@.gmail.com> wrote in message
news:3cstc2F6q880iU1@.individual.net...
>I am doing a code review of a stored procedure. What the procedure does is
>to create a temp table and then based on parameters sent to
> the procedure, call different stored procedures which inserts into the
> temp table just
> created.
> Are their any issues with procedures creating temp table on the fly. Any
> scalability
> issues?
>
|||"Michael C#" <howsa@.boutdat.com> wrote in message
news:ewjaY42RFHA.1348@.TK2MSFTNGP15.phx.gbl...
> Here's a short article - posted previously by Aaron - on the subject.
> http://www.aspfaq.com/show.asp?id=2475
>
excellent information there.
thanks a lot.

creating temp table inside SP

I am doing a code review of a stored procedure.
What the procedure does is to create a temp table and then based on parameters sent to
the procedure, call different stored procedures which inserts into the temp table just
created.
Are their any issues with procedures creating temp table on the fly. Any scalability
issues?There is a good explanation in the BOL. See "DBCC DBREINDEX".
AMB
"rkusenet" wrote:
> I am doing a code review of a stored procedure.
> What the procedure does is to create a temp table and then based on parameters sent to
> the procedure, call different stored procedures which inserts into the temp table just
> created.
> Are their any issues with procedures creating temp table on the fly. Any scalability
> issues?
>|||Sorry, wrong place.
AMB
"Alejandro Mesa" wrote:
> There is a good explanation in the BOL. See "DBCC DBREINDEX".
>
> AMB
> "rkusenet" wrote:
> > I am doing a code review of a stored procedure.
> > What the procedure does is to create a temp table and then based on parameters sent to
> > the procedure, call different stored procedures which inserts into the temp table just
> > created.
> > Are their any issues with procedures creating temp table on the fly. Any scalability
> > issues?
> >
> >|||Here's a short article - posted previously by Aaron - on the subject.
http://www.aspfaq.com/show.asp?id=2475
Enjoy.
"rkusenet" <usenet.rk@.gmail.com> wrote in message
news:3cstc2F6q880iU1@.individual.net...
>I am doing a code review of a stored procedure. What the procedure does is
>to create a temp table and then based on parameters sent to
> the procedure, call different stored procedures which inserts into the
> temp table just
> created.
> Are their any issues with procedures creating temp table on the fly. Any
> scalability
> issues?
>|||"Michael C#" <howsa@.boutdat.com> wrote in message
news:ewjaY42RFHA.1348@.TK2MSFTNGP15.phx.gbl...
> Here's a short article - posted previously by Aaron - on the subject.
> http://www.aspfaq.com/show.asp?id=2475
>
excellent information there.
thanks a lot.sql

creating temp table inside SP

I am doing a code review of a stored procedure.
What the procedure does is to create a temp table and then based on paramete
rs sent to
the procedure, call different stored procedures which inserts into the temp
table just
created.
Are their any issues with procedures creating temp table on the fly. Any sca
lability
issues?There is a good explanation in the BOL. See "DBCC DBREINDEX".
AMB
"rkusenet" wrote:

> I am doing a code review of a stored procedure.
> What the procedure does is to create a temp table and then based on parame
ters sent to
> the procedure, call different stored procedures which inserts into the tem
p table just
> created.
> Are their any issues with procedures creating temp table on the fly. Any s
calability
> issues?
>|||Sorry, wrong place.
AMB
"Alejandro Mesa" wrote:
[vbcol=seagreen]
> There is a good explanation in the BOL. See "DBCC DBREINDEX".
>
> AMB
> "rkusenet" wrote:
>|||Here's a short article - posted previously by Aaron - on the subject.
http://www.aspfaq.com/show.asp?id=2475
Enjoy.
"rkusenet" <usenet.rk@.gmail.com> wrote in message
news:3cstc2F6q880iU1@.individual.net...
>I am doing a code review of a stored procedure. What the procedure does is
>to create a temp table and then based on parameters sent to
> the procedure, call different stored procedures which inserts into the
> temp table just
> created.
> Are their any issues with procedures creating temp table on the fly. Any
> scalability
> issues?
>|||"Michael C#" <howsa@.boutdat.com> wrote in message
news:ewjaY42RFHA.1348@.TK2MSFTNGP15.phx.gbl...
> Here's a short article - posted previously by Aaron - on the subject.
> http://www.aspfaq.com/show.asp?id=2475
>
excellent information there.
thanks a lot.

Creating Stored Procedures Based On Variables

Hi There,

I would like to know if for example i have some tables in my DataBase and i need from each table to get for instance, the Name by the ID, how can i make only one procedure which use parameters and variables so i can use this SP to get the Name By ID for all the Tables?

Thanks

You would have to use dynamic SQL in the stored procedure. Assuming all you tables had a unique column named ID and an associated column named NAME, you could create a simple procedure like:
Create Procedure GetNameByID(

@.tableName varchar(100),

@.ID int

)

AS

Begin

Declare @.sql nvarchar(1000)

Set @.sql = N'Select "NAME" From ' + @.tableName + N' Where ID = ' + Convert( varchar(10), @.ID )

exec( @.sql )

End

go

exec GetNameByID 'sysobjects', 1

exec GetNameByID 'syscolumns', 1

And you could get more complicated from there.

Creating SQL statements programmatically from listbox (ADVANCED)

I am creating a page that creates a report based on a dynamically created SQL statement which is created by user input.

Everything is good except for the WHERE section, which is created from values in a list box.

For Example:
lstCriteria.items(1).value = "COMPANY = 'foo'"
lstCriteria.items(2).value = "DAY= 2"

I build my SQL statement with these values like so:
SELECT * FROM POO WHERE COMPANY = 'foo' AND DAY = 2

The problem I am having is when there are multiple values of the same type in the list box. Say:
lstCriteria.items(1).value = "COMPANY = 'foo'"
lstCriteria.items(2).value = "DAY= 2"
lstCriteria.items(1).value = "COMPANY = 'moo'"

My employer wants this to be valid, but I am having a tough time coming up with a solution.

I know that my SQL statement needs to now read:
SELECT * FROM POO WHERE COMPANY = 'foo' AND DAY = 2 OR COMPANY = 'poo' AND DAY = 2

I have code set up to read the values of each list box item up to the "=". And I know that I need to compair this value with the others in the list box...but I am not running into any good solutions.

Any HELP?How about OR like values together?

SELECT * FROM POO WHERE (COMPANY = 'foo' OR COMPANY = 'poo') AND DAY = 2

|||Yes, that would work. But I am looking more at how to extract this data from the listbox to a usable format. I am working on setting the first value (the left(N) characters of the listbox item, which is also a column name) in an array, then inserting the secondvalue, checking if it is in the array, if it is, adding it to the array. If it is not, then adding it to a new array. This way I could loop thought the arrays and create my where statement...but It's just a thought on a whiteboard now.

Any Other suggestions?

Creating rows based on date range from another table

I wish to build a table based on values from another table.
I need to populate a table between two dates from another table. Using
the START_DT and END_DT, create records between those dates.
I need a new column that is the days between the date and the MID_DT
The data I wish to end with would look something like this:

PERIOD DATE DAY_NO
200602 2005-07-06 -89
200602 2005-07-07 -88
200602 2005-07-08 -87
<...>
200602 2005-10-02 -2
200602 2005-10-03 -1
200602 2005-10-04 0
200602 2005-10-05 1
<...>
200602 2005-12-18 75

CREATE TABLE "dbo"."tblDates"
("PERIOD" CHAR(6) NOT NULL,
"START_DT" DATETIME NULL,
"MID_DT" DATETIME NULL,
"END_DT" DATETIME NOT NULL)

INSERT INTO tblDates VALUES('200505',2005-04-12,2005-07-05,2005-09-12)
INSERT INTO tblDates VALUES('200602',2005-07-06,2005-10-03,2005-12-18)
INSERT INTO tblDates VALUES('200603',2005-10-04,2006-01-17,2006-03-27)
INSERT INTO tblDates VALUES('200604',2006-01-18,2006-04-10,2006-06-19)
INSERT INTO tblDates VALUES('200605',2006-04-11,2006-07-04,2006-09-11)
INSERT INTO tblDates VALUES('200702',2006-07-05,2006-10-02,2006-12-18)rcamarda (robc390@.hotmail.com) writes:

Quote:

Originally Posted by

I wish to build a table based on values from another table.
I need to populate a table between two dates from another table. Using
the START_DT and END_DT, create records between those dates.
I need a new column that is the days between the date and the MID_DT
The data I wish to end with would look something like this:
>
PERIOD DATE DAY_NO
200602 2005-07-06 -89
200602 2005-07-07 -88
200602 2005-07-08 -87
><...>
200602 2005-10-02 -2
200602 2005-10-03 -1
200602 2005-10-04 0
200602 2005-10-05 1
><...>
200602 2005-12-18 75
>
CREATE TABLE "dbo"."tblDates"
("PERIOD" CHAR(6) NOT NULL,
"START_DT" DATETIME NULL,
"MID_DT" DATETIME NULL,
"END_DT" DATETIME NOT NULL)
>
INSERT INTO tblDates VALUES('200505',2005-04-12,2005-07-05,2005-09-12)
INSERT INTO tblDates VALUES('200602',2005-07-06,2005-10-03,2005-12-18)
INSERT INTO tblDates VALUES('200603',2005-10-04,2006-01-17,2006-03-27)
INSERT INTO tblDates VALUES('200604',2006-01-18,2006-04-10,2006-06-19)
INSERT INTO tblDates VALUES('200605',2006-04-11,2006-07-04,2006-09-11)
INSERT INTO tblDates VALUES('200702',2006-07-05,2006-10-02,2006-12-18)

Thanks for posting table definition and data. However, I would appreciate
if you also tested your repro script before you post. I was puzzled not
getting any rows back first from my query, but then I realised that
2005-04-12 2005-09-12. (Run the above folliwed by a SELECT on the
table to see why.)

Anyway, as I said in another newsgroup, you need a table of numbers. Here
is a way to create such a table with a million numbers:

CREATE TABLE Numbers (Number int NOT NULL PRIMARY KEY);
WITH digits (d) AS (
SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION
SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION
SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION
SELECT 0)
INSERT Numbers (Number)
SELECT Number
FROM (SELECT i.d + ii.d * 10 + iii.d * 100 + iv.d * 1000 +
v.d * 10000 + vi.d * 100000 AS Number
FROM digits i
CROSS JOIN digits ii
CROSS JOIN digits iii
CROSS JOIN digits iv
CROSS JOIN digits v
CROSS JOIN digits vi) AS Numbers
WHERE Number 0

Given this table, we can write this query:

SELECT d.PERIOD, dateadd(DAY, n.Number - 1, d.START_DT),
datediff(DAY, d.MID_DT, dateadd(DAY, n.Number - 1, d.START_DT))
FROM tblDates d
CROSS JOIN Numbers n
WHERE dateadd(DAY, n.Number - 1, d.START_DT)
BETWEEN d.START_DT AND d.END_DT
ORDER BY d.PERIOD, 2

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Erland,
Sorry about the insert, i see that its returning what I did not expect.
Originally I had the dates quoted, but sql bawked at that. I've been
trying to fix the insert, but after trying cast and convert, it still
wont go.
This gives error about conversion:
INSERT INTO tblDates VALUES ( '200602',cast('2005-07-06' AS
DATETIME),CAST('2005-10-03' AS DATETIME), CAST('2005-12-18' AS
DATETIME))
AH! Finally got this to work:
INSERT INTO tblDates VALUES ('200505' ,convert(datetime,
'2005-04-12'),convert(datetime,'2005-07-05'),
convert(datetime,'2005-09-12' ))

Im still trying to grasp the use of the numbers table. I have a D_Day
table that is the days from 1900 - 2100. Could that be used somehow?
(1900-01-01 has a surrogate key of 1 and 1900-01-02 is 2 and so forth)
You solution works, which I am appreciative of, tho it will take me
working with the code to figure out why :)
Thanks for teaching me something new!
Rob

Erland Sommarskog wrote:

Quote:

Originally Posted by

rcamarda (robc390@.hotmail.com) writes:

Quote:

Originally Posted by

I wish to build a table based on values from another table.
I need to populate a table between two dates from another table. Using
the START_DT and END_DT, create records between those dates.
I need a new column that is the days between the date and the MID_DT
The data I wish to end with would look something like this:

PERIOD DATE DAY_NO
200602 2005-07-06 -89
200602 2005-07-07 -88
200602 2005-07-08 -87
<...>
200602 2005-10-02 -2
200602 2005-10-03 -1
200602 2005-10-04 0
200602 2005-10-05 1
<...>
200602 2005-12-18 75

CREATE TABLE "dbo"."tblDates"
("PERIOD" CHAR(6) NOT NULL,
"START_DT" DATETIME NULL,
"MID_DT" DATETIME NULL,
"END_DT" DATETIME NOT NULL)

INSERT INTO tblDates VALUES('200505',2005-04-12,2005-07-05,2005-09-12)
INSERT INTO tblDates VALUES('200602',2005-07-06,2005-10-03,2005-12-18)
INSERT INTO tblDates VALUES('200603',2005-10-04,2006-01-17,2006-03-27)
INSERT INTO tblDates VALUES('200604',2006-01-18,2006-04-10,2006-06-19)
INSERT INTO tblDates VALUES('200605',2006-04-11,2006-07-04,2006-09-11)
INSERT INTO tblDates VALUES('200702',2006-07-05,2006-10-02,2006-12-18)

>
Thanks for posting table definition and data. However, I would appreciate
if you also tested your repro script before you post. I was puzzled not
getting any rows back first from my query, but then I realised that
2005-04-12 2005-09-12. (Run the above folliwed by a SELECT on the
table to see why.)
>
Anyway, as I said in another newsgroup, you need a table of numbers. Here
is a way to create such a table with a million numbers:
>
CREATE TABLE Numbers (Number int NOT NULL PRIMARY KEY);
WITH digits (d) AS (
SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION
SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION
SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION
SELECT 0)
INSERT Numbers (Number)
SELECT Number
FROM (SELECT i.d + ii.d * 10 + iii.d * 100 + iv.d * 1000 +
v.d * 10000 + vi.d * 100000 AS Number
FROM digits i
CROSS JOIN digits ii
CROSS JOIN digits iii
CROSS JOIN digits iv
CROSS JOIN digits v
CROSS JOIN digits vi) AS Numbers
WHERE Number 0
>
Given this table, we can write this query:
>
SELECT d.PERIOD, dateadd(DAY, n.Number - 1, d.START_DT),
datediff(DAY, d.MID_DT, dateadd(DAY, n.Number - 1, d.START_DT))
FROM tblDates d
CROSS JOIN Numbers n
WHERE dateadd(DAY, n.Number - 1, d.START_DT)
BETWEEN d.START_DT AND d.END_DT
ORDER BY d.PERIOD, 2
>
>
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
>
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

|||rcamarda (robc390@.hotmail.com) writes:

Quote:

Originally Posted by

Sorry about the insert, i see that its returning what I did not expect.
Originally I had the dates quoted, but sql bawked at that. I've been
trying to fix the insert, but after trying cast and convert, it still
wont go.
This gives error about conversion:
INSERT INTO tblDates VALUES ( '200602',cast('2005-07-06' AS

Yes, the above format could fail. There are three date formats in SQL
Server that are safe:

YYYYMMDD
YYYYMMDDTHH:MM:SS[.fff]
YYYY-MM-DDZ

Here T and Z stand for themselves.

Other formats are interpretated depending on DATEFORMAT and LANGUAGE
setting, and can fail or produced unexpected results if you don't know
what is going on.

Quote:

Originally Posted by

Im still trying to grasp the use of the numbers table. I have a D_Day
table that is the days from 1900 - 2100. Could that be used somehow?
(1900-01-01 has a surrogate key of 1 and 1900-01-02 is 2 and so forth)

Yes, that dates table is essentially a table of numbers with a different
names. In fact, it appears that it has all the numbers as well!

I used a table of numbers, as numbers is the more general concept and
can be used in more places. But in fact, I added a table of dates to
our system before I added a table of numbers.

I leave it as an exercise to you how to use the dates table instead.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||On Sun, 20 Aug 2006 12:38:11 +0000 (UTC), Erland Sommarskog
<esquel@.sommarskog.sewrote:

Quote:

Originally Posted by

>rcamarda (robc390@.hotmail.com) writes:

Quote:

Originally Posted by

Quote:

Originally Posted by

>I wish to build a table based on values from another table.
>I need to populate a table between two dates from another table. Using
>the START_DT and END_DT, create records between those dates.
>I need a new column that is the days between the date and the MID_DT
>The data I wish to end with would look something like this:
>>
>PERIOD DATE DAY_NO
>200602 2005-07-06 -89
>200602 2005-07-07 -88
>200602 2005-07-08 -87
>><...>
>200602 2005-10-02 -2
>200602 2005-10-03 -1
>200602 2005-10-04 0
>200602 2005-10-05 1
>><...>
>200602 2005-12-18 75

[snip]

Quote:

Originally Posted by

Quote:

Originally Posted by

>INSERT INTO tblDates VALUES('200602',2005-07-06,2005-10-03,2005-12-18)

Quote:

Originally Posted by

>Anyway, as I said in another newsgroup, you need a table of numbers. Here
>is a way to create such a table with a million numbers:

What are the pros and cons of relying on such a table vs. using a
WHILE loop? Based on Rob's context of student registrations, let's
assume we're talking about a maximum of 300 iterations per row in
the original tblDates table.|||Ed Murphy (emurphy42@.socal.rr.com) writes:

Quote:

Originally Posted by

What are the pros and cons of relying on such a table vs. using a
WHILE loop? Based on Rob's context of student registrations, let's
assume we're talking about a maximum of 300 iterations per row in
the original tblDates table.

The one risk with a table of numbers is that if you run of numbers, you
will get an incorrect result. That is one reason why I'm reluctant to
use it, if there are alternative solutions. But for a case like this,
when you need to fill up a space, a table of numbers - or dates - is what
you need.

A loop is more complex to program, and easier go wrong. And as a generic
solution, you face scalability problems.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||On Mon, 21 Aug 2006 08:05:49 +0000 (UTC), Erland Sommarskog
<esquel@.sommarskog.sewrote:

Quote:

Originally Posted by

>Ed Murphy (emurphy42@.socal.rr.com) writes:

Quote:

Originally Posted by

Quote:

Originally Posted by

>What are the pros and cons of relying on such a table vs. using a
>WHILE loop? Based on Rob's context of student registrations, let's
>assume we're talking about a maximum of 300 iterations per row in
>the original tblDates table.

>
>The one risk with a table of numbers is that if you run of numbers, you
>will get an incorrect result. That is one reason why I'm reluctant to
>use it, if there are alternative solutions. But for a case like this,
>when you need to fill up a space, a table of numbers - or dates - is what
>you need.
>
>A loop is more complex to program, and easier go wrong.

I disagree, but then I have somewhat more experience with imperative
than functional programming. Consider:

x = first_date
while x <= last_date
insert x, datediff(x, mid_date) into <table>
x = dateadd(x, 1)
end while

versus

select dateadd(first_date, n), n - datediff(mid_date, first_date)
into <table>
from numbers
where n between 0 and datediff(end_date, first_date)

Okay, "where n between <limits>" makes sense as an analogue to a while
loop, but that stuff in line 1 looks like the stuff of headaches.

Quote:

Originally Posted by

And as a generic
>solution, you face scalability problems.

I kind of figured. The query seems easy to get wrong, though, if
you're not familiar with the pattern; I first wrote it as "where
dateadd(first_date, n) between first_date and last_date", but that
seems like it'd be a good bit slower.|||Ed Murphy (emurphy42@.socal.rr.com) writes:

Quote:

Originally Posted by

I disagree, but then I have somewhat more experience with imperative
than functional programming. Consider:
>
x = first_date
while x <= last_date
insert x, datediff(x, mid_date) into <table>
x = dateadd(x, 1)
end while
>
versus
>
select dateadd(first_date, n), n - datediff(mid_date, first_date)
into <table>
from numbers
where n between 0 and datediff(end_date, first_date)
>
Okay, "where n between <limits>" makes sense as an analogue to a while
loop, but that stuff in line 1 looks like the stuff of headaches.

Loops are particularly prone to two sorts of errors:

* They goes on forever, could be because of a sloppy mistake, of because the
logic is complicated.
* One-off errors because of incorrect loop conditions.

One-off errors are easy to make with set-based queries as well, but the
risk of infinite loops is nothing you have to lose sleep over.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Creating Reports at Runtime

Is it possible to create a report programmatically at runtime using Visual Studio 2005 based upon say a report layout specification file?There is a nifty tutorial on this I found.|||

Can you direct me to that tutorial please? It might help me in my situation.

Thanks!

|||

http://msdn2.microsoft.com/en-us/library/ms170667.aspx

It's beautiful!

|||

Hi,

Can you give link for example report preview by RDL-file ?

|||

proki wrote:

Hi,

Can you give link for example report preview by RDL-file ?

You can create any report preview that you would normally create using a report server project. I'm not sure what type of link you are wanting. RDL generator offers full flexibility for creating reports. It's just a different way to go about creating the report. The same reports can be created by using either the RDL generator method or the report server project.

There's nothing new and magical looking if that's what you're asking.

Creating Reports at Runtime

Is it possible to create a report programmatically at runtime using Visual Studio 2005 based upon say a report layout specification file?There is a nifty tutorial on this I found.|||

Can you direct me to that tutorial please? It might help me in my situation.

Thanks!

|||

http://msdn2.microsoft.com/en-us/library/ms170667.aspx

It's beautiful!

|||

Hi,

Can you give link for example report preview by RDL-file ?

|||

proki wrote:

Hi,

Can you give link for example report preview by RDL-file ?

You can create any report preview that you would normally create using a report server project. I'm not sure what type of link you are wanting. RDL generator offers full flexibility for creating reports. It's just a different way to go about creating the report. The same reports can be created by using either the RDL generator method or the report server project.

There's nothing new and magical looking if that's what you're asking.

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