Hide matrix rows?

Hi,

can somebody help me figuring out if the following is possible?
I have a matrix creating weeks out on "x-axis" and projects at "y-axis". For each project have i specified three rows, sum(Fields!hours.Value), sum(Fields!used.Value) and a total saying (sum)Fields!used.Value - sum(Fields!hours.Value).

12 13 14 15 16
A 3 4 5 3 4
2 4 3 4 1
-1 0 -2 1 -3

B ... and so forth...

What i would like is to have only the total row displayed initially, and the through drill-down on clicking A being able to se the two columns used to calculate the total.

I can't seem to figure it out.

Hope somebody out there has an idea for this - or can say that it cant be done for sure.. :-)

Best RegardsYou need to set the inner group (in the group properties) to be initially hidden and toggle the group visibility on the textbox. Make sure you have subtotals turned on (click on the outer group and select 'display subtotals').|||Thanks for the answer, but I can't seem to get this right.

I think maybe the problem is that i don't have anything to group for. Theres nothing that i can group so that the three rows becomes one. They are all just different sums of the one row in the dataset.

Am i getting it wrong? If i select one of the three fields i cant seem to select the "toggle visibility".

Can you follow me?|||

You always have a group in the matrix. If you aren't using any grouping, then you probably should be using a table dataregion. Can you bring up the matrix properties? Also, you could give more of an idea what you are trying to do.

|||

Hi Brian,

I have a similar problem and I wandered if you answer is what I might possibly need, but I haven't been able to implement it.

This is the part of my report. I have first group row being same company branch, second group row are clients and the third one is destinations where those clients were travelling. The measurement is the amount they spent on those destinations. This is the expanded version which look as it should. But look below for not expanded version and that is the one I have problem with.

Leisure

2005

2006

2007

Al

X company

AU

751458.06

298819.34

NZ

302619.79

121497.81

0

US/CA

270860.44

218673.12

SPAC

172093.03

96573.88

ASIA

164682.67

81023.22

UK/EUR

932738.37

481519.72

OTH

54430.17

62402.62

Total

2648882.53

1360509.71

0

Living Options Charitable Trust

AU

Total

Total

2657071.43

1360509.71

0

What I would like to see in not expanded version is just totals for the group rows. But what I am getting is suppressed description of the group rows but expended amounts as in the previous version. There is a little + next to Al in my version to show it is not expended version but for some reson it didn't come in here.

Leisure

2005

2006

2007

Al

751458.06

298819.34

302619.79

121497.81

0

270860.44

218673.12

172093.03

96573.88

164682.67

81023.22

932738.37

481519.72

54430.17

62402.62

Total

2648882.53

1360509.71

0

Total

Total

2657071.43

1360509.71

0

Interesting enough is that when exported into excel it works as it should be but in to Reporting Services as well.

Thanks a lot

Cheers

Marina

|||

I think I found out what my problem is. I was setting up the visability on the field instead of on the group.

Thanks

Marina

|||

Hi!!, what happend with you repor, t could you find an answer? I have the same question.

sql

Hide matrix rows?

Hi,

can somebody help me figuring out if the following is possible?
I have a matrix creating weeks out on "x-axis" and projects at "y-axis". For each project have i specified three rows, sum(Fields!hours.Value), sum(Fields!used.Value) and a total saying (sum)Fields!used.Value - sum(Fields!hours.Value).

12 13 14 15 16
A 3 4 5 3 4
2 4 3 4 1
-1 0 -2 1 -3

B ... and so forth...

What i would like is to have only the total row displayed initially, and the through drill-down on clicking A being able to se the two columns used to calculate the total.

I can't seem to figure it out.

Hope somebody out there has an idea for this - or can say that it cant be done for sure.. :-)

Best RegardsYou need to set the inner group (in the group properties) to be initially hidden and toggle the group visibility on the textbox. Make sure you have subtotals turned on (click on the outer group and select 'display subtotals').|||Thanks for the answer, but I can't seem to get this right.

I think maybe the problem is that i don't have anything to group for. Theres nothing that i can group so that the three rows becomes one. They are all just different sums of the one row in the dataset.

Am i getting it wrong? If i select one of the three fields i cant seem to select the "toggle visibility".

Can you follow me?|||

You always have a group in the matrix. If you aren't using any grouping, then you probably should be using a table dataregion. Can you bring up the matrix properties? Also, you could give more of an idea what you are trying to do.

|||

Hi Brian,

I have a similar problem and I wandered if you answer is what I might possibly need, but I haven't been able to implement it.

This is the part of my report. I have first group row being same company branch, second group row are clients and the third one is destinations where those clients were travelling. The measurement is the amount they spent on those destinations. This is the expanded version which look as it should. But look below for not expanded version and that is the one I have problem with.

Leisure 2005 2006 2007 Al X company AU 751458.06 298819.34 NZ 302619.79 121497.81 0 US/CA 270860.44 218673.12 SPAC 172093.03 96573.88 ASIA 164682.67 81023.22 UK/EUR 932738.37 481519.72 OTH 54430.17 62402.62 Total 2648882.53 1360509.71 0 Living Options Charitable Trust AU Total Total 2657071.43 1360509.71 0

What I would like to see in not expanded version is just totals for the group rows. But what I am getting is suppressed description of the group rows but expended amounts as in the previous version. There is a little + next to Al in my version to show it is not expended version but for some reson it didn't come in here.

Leisure 2005 2006 2007 Al 751458.06 298819.34 302619.79 121497.81 0 270860.44 218673.12 172093.03 96573.88 164682.67 81023.22 932738.37 481519.72 54430.17 62402.62 Total 2648882.53 1360509.71 0 Total Total 2657071.43 1360509.71 0

Interesting enough is that when exported into excel it works as it should be but in to Reporting Services as well.

Thanks a lot

Cheers

Marina

|||

I think I found out what my problem is. I was setting up the visability on the field instead of on the group.

Thanks

Marina

|||

Hi!!, what happend with you repor, t could you find an answer? I have the same question.

Hide matrix rows?

Hi,

can somebody help me figuring out if the following is possible?
I have a matrix creating weeks out on "x-axis" and projects at "y-axis". For each project have i specified three rows, sum(Fields!hours.Value), sum(Fields!used.Value) and a total saying (sum)Fields!used.Value - sum(Fields!hours.Value).

12 13 14 15 16
A 3 4 5 3 4
2 4 3 4 1
-1 0 -2 1 -3

B ... and so forth...

What i would like is to have only the total row displayed initially, and the through drill-down on clicking A being able to se the two columns used to calculate the total.

I can't seem to figure it out.

Hope somebody out there has an idea for this - or can say that it cant be done for sure.. :-)

Best RegardsYou need to set the inner group (in the group properties) to be initially hidden and toggle the group visibility on the textbox. Make sure you have subtotals turned on (click on the outer group and select 'display subtotals').|||Thanks for the answer, but I can't seem to get this right.

I think maybe the problem is that i don't have anything to group for. Theres nothing that i can group so that the three rows becomes one. They are all just different sums of the one row in the dataset.

Am i getting it wrong? If i select one of the three fields i cant seem to select the "toggle visibility".

Can you follow me?|||

You always have a group in the matrix. If you aren't using any grouping, then you probably should be using a table dataregion. Can you bring up the matrix properties? Also, you could give more of an idea what you are trying to do.

|||

Hi Brian,

I have a similar problem and I wandered if you answer is what I might possibly need, but I haven't been able to implement it.

This is the part of my report. I have first group row being same company branch, second group row are clients and the third one is destinations where those clients were travelling. The measurement is the amount they spent on those destinations. This is the expanded version which look as it should. But look below for not expanded version and that is the one I have problem with.

Leisure 2005 2006 2007 Al X company AU 751458.06 298819.34 NZ 302619.79 121497.81 0 US/CA 270860.44 218673.12 SPAC 172093.03 96573.88 ASIA 164682.67 81023.22 UK/EUR 932738.37 481519.72 OTH 54430.17 62402.62 Total 2648882.53 1360509.71 0 Living Options Charitable Trust AU Total Total 2657071.43 1360509.71 0

What I would like to see in not expanded version is just totals for the group rows. But what I am getting is suppressed description of the group rows but expended amounts as in the previous version. There is a little + next to Al in my version to show it is not expended version but for some reson it didn't come in here.

Leisure 2005 2006 2007 Al 751458.06 298819.34 302619.79 121497.81 0 270860.44 218673.12 172093.03 96573.88 164682.67 81023.22 932738.37 481519.72 54430.17 62402.62 Total 2648882.53 1360509.71 0 Total Total 2657071.43 1360509.71 0

Interesting enough is that when exported into excel it works as it should be but in to Reporting Services as well.

Thanks a lot

Cheers

Marina

|||

I think I found out what my problem is. I was setting up the visability on the field instead of on the group.

Thanks

Marina

|||

Hi!!, what happend with you repor, t could you find an answer? I have the same question.

Hide empty rows on drill down report

Is there any way to hide the expand button (the plus sign) for rows that have
no containing rows on a drill-down report? What is happening now is that the
plus signs are populated for all rows and only a small subset of them
actually have data in the to drill into.Jason,
You can go into the toggle item's Properties and under the Visibility
Tab, set the "Initial appearance of the toggle image for this report
item:" to Expression and use a variation of the following:
=IIf(Fields!Determing_Factor.Value = "Determining_Value",true,false)
for example:
=IIf(Fields!Item_Name.Value = "",true,false)
This makes it so that if my next rows Item_Name field is blank, the
toggle will be automatically set the the - symbol so that the user
knows there is nothing past this point.
Hope that helps. If I didn't make myself understandable, let me know
and I can try again.|||That's pretty good. Is there any way to make it so that the "-" symbol cannot
be expanded? It looks weird to have a "-" symbol change to a "+" symbol and
then expand with an empty line under it.
"Mal" wrote:
> Jason,
> You can go into the toggle item's Properties and under the Visibility
> Tab, set the "Initial appearance of the toggle image for this report
> item:" to Expression and use a variation of the following:
> =IIf(Fields!Determing_Factor.Value = "Determining_Value",true,false)
> for example:
> =IIf(Fields!Item_Name.Value = "",true,false)
> This makes it so that if my next rows Item_Name field is blank, the
> toggle will be automatically set the the - symbol so that the user
> knows there is nothing past this point.
> Hope that helps. If I didn't make myself understandable, let me know
> and I can try again.
>|||Jason,
To remove the additional row, you will need to set the Hidden value, of
the Visibility properties for the row, to the same as what is in the
toggle item's Initial Appearance value.
for example:
=IIf(Fields!Item_Name.Value = "",true,false)
And here is an example of a 6 level report, that may or may not have
data at all levels:
=IIf(Fields!lev1_Name.Value="",IIf(Fields!lev2_Name.Value="",IIf(Fields!lev3_Name.Value="",IIf(Fields!lev4_Name.Value="",IIf(Fields!lev5_Name.Value
= "",IIf(Fields!lev6_Name.Value ="",true,false),false),false),false),false),false)
This would be inserted into the Hidden property of Group 1 in said
report. And would need to be placed in that property value for each of
the following groups, removing the previous row from the equation each
time. So for next row, the expression would start at lev2_Name, and the
Group 3 row would start at lev3_name, etc.
Does that help?|||Jason,
You will need to place an Expression into the Hidden field for the
Row's Visibility Properties. If you are using the above Expression on
the Initial Apperance, the Expression for the Hidden property would be
the same.
So, if it is a 3 level report and level 2 is a toggle for level 3, the
Expression would go in the toggle item on row 2 and in the Hidden
Property for Row3.

Hide Duplicates

When you hide duplicates on the report, is there any way to reclaim the space
that the duplicate is taking. I am seeing extra spaces between the rows on my
reports. I would like the rows with data to be next to each other without the
extra white space.Consider making it a group heading of the same group. Then it should only
display onse and not hold the space as a detail row would. I had the same
issue just yesterday.
Hope this helps.
"MAGrimsley" wrote:
> When you hide duplicates on the report, is there any way to reclaim the space
> that the duplicate is taking. I am seeing extra spaces between the rows on my
> reports. I would like the rows with data to be next to each other without the
> extra white space.|||Thanks that took care of the detail row, any thoughts on the white space
between groups?
"Michael Montgomery" wrote:
> Consider making it a group heading of the same group. Then it should only
> display onse and not hold the space as a detail row would. I had the same
> issue just yesterday.
> Hope this helps.
> "MAGrimsley" wrote:
> > When you hide duplicates on the report, is there any way to reclaim the space
> > that the duplicate is taking. I am seeing extra spaces between the rows on my
> > reports. I would like the rows with data to be next to each other without the
> > extra white space.|||Yes. I think.
Make sure they are the same group number. You can have multiple headers of
the same group.
"MAGrimsley" wrote:
> Thanks that took care of the detail row, any thoughts on the white space
> between groups?
> "Michael Montgomery" wrote:
> > Consider making it a group heading of the same group. Then it should only
> > display onse and not hold the space as a detail row would. I had the same
> > issue just yesterday.
> >
> > Hope this helps.
> >
> > "MAGrimsley" wrote:
> >
> > > When you hide duplicates on the report, is there any way to reclaim the space
> > > that the duplicate is taking. I am seeing extra spaces between the rows on my
> > > reports. I would like the rows with data to be next to each other without the
> > > extra white space.

Hide Duplicates

I have a query returning a parent table with mutliple child rows. I want the
report to show the info from the parent table and then show all the child
info and then show the next parent info. Setting the parent fields to Hide
Duplicates prints the information the right way, but leaves a blank area
where those fields would have printed. Then below it prints the child table
fields. How can I make it skip the repeated values entirely?Group the table with the Unique Parent field and display the parent info in
group header and child fields in the details part.
"MNicks" wrote:
> I have a query returning a parent table with mutliple child rows. I want the
> report to show the info from the parent table and then show all the child
> info and then show the next parent info. Setting the parent fields to Hide
> Duplicates prints the information the right way, but leaves a blank area
> where those fields would have printed. Then below it prints the child table
> fields. How can I make it skip the repeated values entirely?sql

Hide dimension members which have no rows in fact table

When a cube is presented to the end user (thru Excel or Reporting services), I would like to limit the available dimension members to only those which have at least one row in fact table. This is to simplify what user sees as a list of choices under a dimension attribute/hierarchy and not be overwhelmed with all the dimension members for which no data may exist in reality in the fact table. Does Analysis Services 2005 have any easy way to accomplish this as part of UDM/Cube design?

Let me give you an example. Take "Locations" role playing dimension. In ocean transportation business, "Locations" are of different types such as Port of Load (sea-port), Port of Discharge (sea-port), Inland Point of origin, Inland Point of destination etc. Sea-ports are only few (5 to 10) where as inland locations are in thousands. The entire list of locations, which includes locations which may not have had any shipment booked till date, may be even bigger. To keep the ETL simple, we want to keep one central dimension table for all location roles. However, for cube query selection purposes, users want to see a simple and compact list of Port of Load locations for which bookings have been made (i.e., data exists in fact table with those Port Of Load locations) - for example.

One possible solution is... to use one view for each location role in the UDM and setup relationships from fact table column to these individual location views, where the view joins Location dimension table with the fact table to select only those locations that exist in the fact table. Is this a good approach? Will there be any performance impact in Cube processing or end user cube query performance because of using views like this (since view is run every time it is referenced on the fly to generate the result set)?

Another solution is... to have separate dimension tables for each location role with only those members that are used in fact table records. But this complicates the ETL due to lot of redundancy. Will it give any performance advantage since it uses a permanent table versus view in UDM (data source view)?

Please share your insights on the best ways of handling this problem in SQL Server 2005. Thank you!

Hello. The standard behaviour you are looking for is the standard behaviour in a TSQL inner join. So if you make a report in SSRS2005 and use TSQL with your data mart / data warehouse as a source, you will only see connected records in the dimensions and the fact table.

A cube is more like a crossjoin in TSQL. If you crossjoin two tables in TSQL you will get all combinations of members in the tables. This is standard behaviour in SSAS2005.

But within a dimension table in SSAS2005 you have a new feature, only within the dimension, called autoexist, that will only show combinations of members like a TSQL inner join of members. Within a dimension, in the previous version of SSAS (2000), you would get a TSQL-crossjoin of all members within a dimension.

If you combine sea-ports and inland destinations, in the same dimension(in SSAS2005), you will get an inner join of dimension member combinations(autoexists). If you put them as separate dimensions you will miss autoexist.

To reduce empty cells in the grid(in a SSAS2005 cube) you will have to add NonEmpty or Non Empty in MDX in order to not show empty cells. Many clients can help help you with this by having a button to hide empty rows and columns in the grid.

There are many more superior MDX experts, on this forum, that can explain this even further.

HTH

Thomas Ivarsson

|||

Hello. Auto exist does not have anything to do with data in the fact table. Auto exist is a dimensional concept only. Auto-exist only pertains to the attributes in the same dimension. If we separate the datasources (either using view or permanent table) for each location role dimension, then with in any single location role dimension, "auto exist" is still effective across the attributes of that dimension. My problem has to do with not wanting to see the dimension members if they (dimension key) are not present in the fact table data. Thanks for the reply. Appreciate it.

|||

I have tried to tell you about the behavior of Autoexist if you read my post carefully. You have also an explanation for the dimension and fact problem.

Autoexist is not new to me either.

Regards

Thomas Ivarsson

|||

Shan,

If I understand your requirement correctly, you have one Locations dimension table, but you use that single dimension within your UDM in different roles. In each of its roles, you want the dimension to have a different set of members based on whether or not the members exist within a related fact table or not. Is that correct?

If that is the case, then I believe your best option is to create different views (or named queries in the DSV) on top fo the Locations dimension table that handle the inner join to each related fact table. Then, in the UDM, set up separate dimensions on top of each view and relate each dimension to each fact table/measure group as appropriate.

There will be some performance implications to doing this. For example, you'll be running similar (or even the exact same) queries agains the Locations dimension table as you process each of the separate dimensions in the UDM. So, if you have a lot of them and/or if the joins to the fact tables within the views or named queries are expensive, you'll be adding processing time. Once processing is complete, however, I don't think query performance against any given measure group within the UDM will be any different than if you had a single dimension that was being used as a role-playing dimenision within the cube.

HTH,

Dave Fackler

|||

Dave,

That's correct summary of my requirement. We will proceed with this recommended solution. Thank you. I was not sure whether Analysis Services has some built-in support to meet this common need which does not require separating data sources (view or separate table) since that approach takes some additional work! Trying to find an easier or better way since this question applies to many dimension tables in our data warehouse (both simple and role playing dimensions)... :-) Now I understand that there is no such easy way (out of the box support).

If I am right, the added performance time you mentioned impacts the cube processing step time during ETL job run. That's okay.

sql

Hide dimension members which have no rows in fact table

When a cube is presented to the end user (thru Excel or Reporting services), I would like to limit the available dimension members to only those which have at least one row in fact table. This is to simplify what user sees as a list of choices under a dimension attribute/hierarchy and not be overwhelmed with all the dimension members for which no data may exist in reality in the fact table. Does Analysis Services 2005 have any easy way to accomplish this as part of UDM/Cube design?

Let me give you an example. Take "Locations" role playing dimension. In ocean transportation business, "Locations" are of different types such as Port of Load (sea-port), Port of Discharge (sea-port), Inland Point of origin, Inland Point of destination etc. Sea-ports are only few (5 to 10) where as inland locations are in thousands. The entire list of locations, which includes locations which may not have had any shipment booked till date, may be even bigger. To keep the ETL simple, we want to keep one central dimension table for all location roles. However, for cube query selection purposes, users want to see a simple and compact list of Port of Load locations for which bookings have been made (i.e., data exists in fact table with those Port Of Load locations) - for example.

One possible solution is... to use one view for each location role in the UDM and setup relationships from fact table column to these individual location views, where the view joins Location dimension table with the fact table to select only those locations that exist in the fact table. Is this a good approach? Will there be any performance impact in Cube processing or end user cube query performance because of using views like this (since view is run every time it is referenced on the fly to generate the result set)?

Another solution is... to have separate dimension tables for each location role with only those members that are used in fact table records. But this complicates the ETL due to lot of redundancy. Will it give any performance advantage since it uses a permanent table versus view in UDM (data source view)?

Please share your insights on the best ways of handling this problem in SQL Server 2005. Thank you!

Hello. The standard behaviour you are looking for is the standard behaviour in a TSQL inner join. So if you make a report in SSRS2005 and use TSQL with your data mart / data warehouse as a source, you will only see connected records in the dimensions and the fact table.

A cube is more like a crossjoin in TSQL. If you crossjoin two tables in TSQL you will get all combinations of members in the tables. This is standard behaviour in SSAS2005.

But within a dimension table in SSAS2005 you have a new feature, only within the dimension, called autoexist, that will only show combinations of members like a TSQL inner join of members. Within a dimension, in the previous version of SSAS (2000), you would get a TSQL-crossjoin of all members within a dimension.

If you combine sea-ports and inland destinations, in the same dimension(in SSAS2005), you will get an inner join of dimension member combinations(autoexists). If you put them as separate dimensions you will miss autoexist.

To reduce empty cells in the grid(in a SSAS2005 cube) you will have to add NonEmpty or Non Empty in MDX in order to not show empty cells. Many clients can help help you with this by having a button to hide empty rows and columns in the grid.

There are many more superior MDX experts, on this forum, that can explain this even further.

HTH

Thomas Ivarsson

|||

Hello. Auto exist does not have anything to do with data in the fact table. Auto exist is a dimensional concept only. Auto-exist only pertains to the attributes in the same dimension. If we separate the datasources (either using view or permanent table) for each location role dimension, then with in any single location role dimension, "auto exist" is still effective across the attributes of that dimension. My problem has to do with not wanting to see the dimension members if they (dimension key) are not present in the fact table data. Thanks for the reply. Appreciate it.

|||

I have tried to tell you about the behavior of Autoexist if you read my post carefully. You have also an explanation for the dimension and fact problem.

Autoexist is not new to me either.

Regards

Thomas Ivarsson

|||

Shan,

If I understand your requirement correctly, you have one Locations dimension table, but you use that single dimension within your UDM in different roles. In each of its roles, you want the dimension to have a different set of members based on whether or not the members exist within a related fact table or not. Is that correct?

If that is the case, then I believe your best option is to create different views (or named queries in the DSV) on top fo the Locations dimension table that handle the inner join to each related fact table. Then, in the UDM, set up separate dimensions on top of each view and relate each dimension to each fact table/measure group as appropriate.

There will be some performance implications to doing this. For example, you'll be running similar (or even the exact same) queries agains the Locations dimension table as you process each of the separate dimensions in the UDM. So, if you have a lot of them and/or if the joins to the fact tables within the views or named queries are expensive, you'll be adding processing time. Once processing is complete, however, I don't think query performance against any given measure group within the UDM will be any different than if you had a single dimension that was being used as a role-playing dimenision within the cube.

HTH,

Dave Fackler

|||

Dave,

That's correct summary of my requirement. We will proceed with this recommended solution. Thank you. I was not sure whether Analysis Services has some built-in support to meet this common need which does not require separating data sources (view or separate table) since that approach takes some additional work! Trying to find an easier or better way since this question applies to many dimension tables in our data warehouse (both simple and role playing dimensions)... :-) Now I understand that there is no such easy way (out of the box support).

If I am right, the added performance time you mentioned impacts the cube processing step time during ETL job run. That's okay.