Hold group constant across time?

Hi eveyone,

My users asked me a question today that I wasn't sure of so I was hoping you guys could give me some advice.

Is it possible to make the cube hold a group constant across time? For example, I want to take all of the customers that were here in December 2006 and see how they performed during the year. Is that something I can do in a cube? Obviously, I could create a seperate cube with only those customers in it, but I'd like to avoid that if possible.

Thanks!

Yes, you should be able to accomplish this. You'd likely want to create a named set in your Customers dimension that defines the group of customers in Dec 2006 (however you want to define that -- they had a value for a given measure, they have an attribute that defines their current status, etc.) Then, you (or your users) should be able to put that named set of customers into a pivot table and see measures across time just for that set of customers.

HTH,

Dave Fackler

|||

Thanks for your response! I guess I'm confused how to define the group of customers in 2006 and show them across time since 'time' is what defines them. If I limit the query in the where statement to those in 2006 I don't get data for anything but 2006. How do I write the query so it keeps 2006 customers and then shows those customers over time?

|||

You'll need to define the set of 2006 customers as a named set and then use that named set in your query...

For example, the following query shows sales to customers in the US for 2004 from the Adventure Works cube:

select

{[Measures].[Internet Sales Amount]} on columns,

[Customer].[Customer Geography].[United States].Children on rows

from

[Adventure Works]

where

([Date].[Calendar Year].[CY 2004])

That is likley similar to the query you are using -- it only shows 2004 sales and the customers associated with those sales since the where clause includes 2004 as a slicer. However, this slightly different query shows all sales over time for the customers who had sales in 2004:

with set [CY 2004 Customers] as

'filter([Customer].[Customer Geography].[United States].Children, ([Measures].[Internet Sales Amount], [Date].[Calendar Year].[CY 2004]) > 0)'

select

[Date].[Calendar Year].Members on columns,

[CY 2004 Customers] on rows

from

[Adventure Works]

where

([Measures].[Internet Sales Amount])

The key here is that the named set returns the set of US customers who had sales in 2004. This set is then used to define the rows in the subsequent query, but the query returns sales across time for that set of customers.

HTH,

Dave Fackler

|||

Ahh Dave, you're excellent!! And completely right.. the first query is exactly what I had. I know SQL pretty well so I knew that wasn't going to work but I'm a novice at MDX. So I guess the named set is basically a SQL subquery?

Your example makes sense but now how do I apply this as a named set in my cube through Visual Studio?

Thanks for you patience! I'm reading several MDX books as quickly as possible!

EDIT:

Well I spent my entire day at work reading up on MDX and trying to apply your example, but no luck I just can't seem to get the filter expression correct. I'm assuming when I apply this code to my cube as a named set I need to put the filter expression in the 'expression' box on the calculations tab? How do I modify the filter statement to show measures no matter how they're sliced?

Hold group constant across time?

Hi eveyone,

My users asked me a question today that I wasn't sure of so I was hoping you guys could give me some advice.

Is it possible to make the cube hold a group constant across time? For example, I want to take all of the customers that were here in December 2006 and see how they performed during the year. Is that something I can do in a cube? Obviously, I could create a seperate cube with only those customers in it, but I'd like to avoid that if possible.

Thanks!

Yes, you should be able to accomplish this. You'd likely want to create a named set in your Customers dimension that defines the group of customers in Dec 2006 (however you want to define that -- they had a value for a given measure, they have an attribute that defines their current status, etc.) Then, you (or your users) should be able to put that named set of customers into a pivot table and see measures across time just for that set of customers.

HTH,

Dave Fackler

|||

Thanks for your response! I guess I'm confused how to define the group of customers in 2006 and show them across time since 'time' is what defines them. If I limit the query in the where statement to those in 2006 I don't get data for anything but 2006. How do I write the query so it keeps 2006 customers and then shows those customers over time?

|||

You'll need to define the set of 2006 customers as a named set and then use that named set in your query...

For example, the following query shows sales to customers in the US for 2004 from the Adventure Works cube:

select

{[Measures].[Internet Sales Amount]} on columns,

[Customer].[Customer Geography].[United States].Children on rows

from

[Adventure Works]

where

([Date].[Calendar Year].[CY 2004])

That is likley similar to the query you are using -- it only shows 2004 sales and the customers associated with those sales since the where clause includes 2004 as a slicer. However, this slightly different query shows all sales over time for the customers who had sales in 2004:

with set [CY 2004 Customers] as

'filter([Customer].[Customer Geography].[United States].Children, ([Measures].[Internet Sales Amount], [Date].[Calendar Year].[CY 2004]) > 0)'

select

[Date].[Calendar Year].Members on columns,

[CY 2004 Customers] on rows

from

[Adventure Works]

where

([Measures].[Internet Sales Amount])

The key here is that the named set returns the set of US customers who had sales in 2004. This set is then used to define the rows in the subsequent query, but the query returns sales across time for that set of customers.

HTH,

Dave Fackler

|||

Ahh Dave, you're excellent!! And completely right.. the first query is exactly what I had. I know SQL pretty well so I knew that wasn't going to work but I'm a novice at MDX. So I guess the named set is basically a SQL subquery?

Your example makes sense but now how do I apply this as a named set in my cube through Visual Studio?

Thanks for you patience! I'm reading several MDX books as quickly as possible!

EDIT:

Well I spent my entire day at work reading up on MDX and trying to apply your example, but no luck I just can't seem to get the filter expression correct. I'm assuming when I apply this code to my cube as a named set I need to put the filter expression in the 'expression' box on the calculations tab? How do I modify the filter statement to show measures no matter how they're sliced?

Hierarchal data reports

I have set up a hierarchal report, which is based on one data set using a table and grouping. I have a group header, a details, and a group footer section in the table. The details is filtered based on one of the columns, and hidden with the ability for the user to expand and collapse the row based on one of the fields in the group header. My problem is, I am trying to set up a report which gets scheduled to run weekly, and emailed out to its users, in PDF format. The PDF format will not allow the user to expand and collapse the rows. I would just send the users a link to the report, and the can expand / collapse the rows they want to see, if they didn't mind having to be connected to set up their report, and having the extra step of exporting to PDF.

Is there a way to export to Excel and have the expanders still be there?

In the mean time, I have set up the report to always show the details rows, but since I am filtering out based on a certain criteria, the rows that do not have any data, show a blank line, which gives the report a confusing look. The empty rows never showed up when I have drill-down enabled, because if it was hidden, and no data, there is no way to expand the hidden row.

Any suggestions on what I can do will be VERY helpful. Thanks!

Brandon.

hi,

Try using MS reporting services

Follow this link: http://www.c-sharpcorner.com/UploadFile/asif.blog/RecursiveData06132006230750PM/RecursiveData.aspx?ArticleID=fdc78120-bf77-4fb2-9dc5-bb84d734c792

-Ajeeth

|||

I am using MS Reporting services. Why would I be posting to this forum if I were not? Did you even read my question/problem?

My problem is that I can get drill-down working, but it shows a space if I show all the rows because I have a filter which takes out rows which do not meet requirements. Or, I need a report that can be sent through RS scheduler via email and act as an offline report with drill-down abilities.

Thank you.

|||

Oh, did you mean report viewer?

If so, I am sorry. I will look into report viewer. I must have completely misunderstood your response...

Brandon.

|||Are you actually filtering out the groups or the rows? Filtering groups should contract the space for missing rows.|||

Well, I tried filtering on the row at first, the details row.. It would not keep my filter options for whatever reason. I would hit OK, then go back to filters on that row, and it would be gone. So, I changed the details row to a group row, and applied the filter, which filters out, but still shows a space. I did not mention above, but I guess I am using two grouppings, one for the header row, the other for the details, which I am filtering out data.

I am now probably confusing the problem, but now I am trying to allow visibility on the row based on its cell property in one of the text boxes but I can not figure out how to pull the data from the table, for each row. I just need to look up how to reference the text box inside a table, and hope it will calculate based on that text box, for each row in the table.

Thanks

Hiding the Database list from a certain user\group

Is there a way to limit what user will see in Enterprise Manager? I
would like them to only see there db's and not all them.On Jun 18, 12:59 pm, OVDaddy <d...@.wemhoff.comwrote:

Quote:

Originally Posted by

Is there a way to limit what user will see in Enterprise Manager? I
would like them to only see there db's and not all them.

I have read and see that I am not able to do this.|||OVDaddy (dan@.wemhoff.com) writes:

Quote:

Originally Posted by

On Jun 18, 12:59 pm, OVDaddy <d...@.wemhoff.comwrote:

Quote:

Originally Posted by

>Is there a way to limit what user will see in Enterprise Manager? I
>would like them to only see there db's and not all them.

>
I have read and see that I am not able to do this.

I believe that in EM, you will see only the databases you have access to.

In SQL Server Management Studio that comes with SQL 2005, it's a different
story. Here you cannot hide databases.

--
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|||By default users see all databases in Enterprise Manager. The following KB
shows a method that you can use so users can see only databases that they
have access to (the title of the article is a bit off topic but in fact it
does show a good method):
http://support.microsoft.com/default.aspx/kb/889696
For SQL Server Management Studio this is much easier, you can just deny a
login the VIEW ANY DATABASE permission and that will do it.

HTH,

Plamen Ratchev
http://www.SQLStudio.com|||Plamen Ratchev (Plamen@.SQLStudio.com) writes:

Quote:

Originally Posted by

For SQL Server Management Studio this is much easier, you can just deny a
login the VIEW ANY DATABASE permission and that will do it.

Arguably. The users will only see the databases they *own*. They could
have access to other databases, but they will not show up.

--
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 Sommarskog" <esquel@.sommarskog.sewrote in message
news:Xns9954ECC668758Yazorman@.127.0.0.1...

Quote:

Originally Posted by

>
Arguably. The users will only see the databases they *own*. They could
have access to other databases, but they will not show up.
>

Yes, but my understanding of the original post is that it asked for
configuration where users can see only their own databases, so this applies
here. This is what I meant. :)

Plamen Ratchev
http://www.SQLStudio.com

Hiding subtotal rows when there is only 1 row in the group

How could one do this? I understand you could use the COUNT() function, but I'm not sure which object's visibility would best support this. All that I've tried (subtotal area, group visibility) do not seem to work.

If you change the visible property on the subtotal textbox that RS adds, it will only 'blank out' the area where the subtotal row would have been - this doesn't achieve the desired effect of saving space.

Try this:

Click on the whole row for your group footer
Go to the properties
Put this in the 'Visibility - Hidden' expression.

=IIf(CountRows("GroupName") > 1, False, True)

I tried this on one of my reports and it removed the space used by the group footer, it didn't just blank it out. If there was only one row in that group, the footer wasn't shown, but if there were more than 1 row, it would. Just as a test, you might want to create a new row below your group footer and just add some text in there so that it will show below your subtotals (if you have any). In my case, the row below my group footer was 'moved up' to be directly below the details if there was only one row displayed, otherwise, it was displayed directly below the subtotals.

Hope this helps.

Jarret

|||

It's in a matrix, so group headers and footers aren't apparent options. :(

I could see how that would work in a table though.

|||

When you choose the subtotal option for a group in a matrix, a row does get added. To affect only the subtotal cell in a matrix you need to use the InScope() function. The main thing to understand in the logic is that the subtotal cell for a group is not in scope of that group and hence the function return false for the cell.

For example, say on your rows you have 2 groups called region_group and country_group. You right-click the country textbox and select Subtotal. This adds an additional row containing just the header textbos for the subtotal. You now 3 stages for hiding the subtotal.

1.You now need to add an expression to the details cell for the Visibility -> Hidden property. The expression should be:

=Not InScope("country_group")

This should evaluate to Hidden = True for the total row as it is not in scope of the country_group. If you run this you will probably find that the details cell disappears but the heading remains.

2.Now if you try applying the same expression for Visibility to the subtotal header textbox it should also disappear but will probably leave a blank gap in it's place.

3.If you can apply this same expression to the entire subtotal row (by clicking on the row header) then this should also remove the visible gap.

I'm not sure if the last step is possible as I am unable to test this at the moment (on client site), the first 2 steps should work though.

Hope this helps. Please post the results of your attempts.

|||

Those are great suggestions, but there are no header or footer rows in a matrix.
If I select the entire row that contains the subtotal, a visible property is not exposed.

There are also column groupings after the one I'm mentioning - and if I mess with the group visibility, the successive columns are hidden or blanked out.
I'll see what else can be done to acheive the row hiding.

|||Try taking a look at Actions

Hiding Subtotal in Matrix

Hi,
I have one column group and 3 columns under it in a matrix. I added subtotal
to that column group and now all the 3 columns are summarized and shown. Now
I want to hide one column summary(The other 2 column summary should be
shown). How to do that?
TIA,
SamYou will need to control the Visibility of the textboxes that makeup the
column by using an expression similar to
=iif(InScope("MatrixColumnGroupName"), false, true).
The scope portion of the Inscope() can be the name of a DatasSet, Grouping,
or DataRegion.
--
Bruce Johnson [MSFT]
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Samuel" <samuel@.photoninfotech.com> wrote in message
news:ergsc6maEHA.2792@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I have one column group and 3 columns under it in a matrix. I added
subtotal
> to that column group and now all the 3 columns are summarized and shown.
Now
> I want to hide one column summary(The other 2 column summary should be
> shown). How to do that?
> TIA,
> Sam
>|||There's no good way to do this in the current version.
But for a sleazy hack workaround, take a look at my reply on the thread from
yesterday titled "Matrix SubTotal"
--
This post is provided 'AS IS' with no warranties, and confers no rights. All
rights reserved. Some assembly required. Batteries not included. Your
mileage may vary. Objects in mirror may be closer than they appear. No user
serviceable parts inside. Opening cover voids warranty. Keep out of reach of
children under 3.
"Samuel" <samuel@.photoninfotech.com> wrote in message
news:ergsc6maEHA.2792@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I have one column group and 3 columns under it in a matrix. I added
subtotal
> to that column group and now all the 3 columns are summarized and shown.
Now
> I want to hide one column summary(The other 2 column summary should be
> shown). How to do that?
> TIA,
> Sam
>|||Hi Chris and Bruce,
Thanks - It works
Samuel
"Bruce Johnson [MSFT]" <brucejoh@.online.microsoft.com> wrote in message
news:%23TEUIwpaEHA.2812@.tk2msftngp13.phx.gbl...
> You will need to control the Visibility of the textboxes that makeup the
> column by using an expression similar to
> =iif(InScope("MatrixColumnGroupName"), false, true).
> The scope portion of the Inscope() can be the name of a DatasSet,
Grouping,
> or DataRegion.
> --
> Bruce Johnson [MSFT]
> Microsoft SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no
rights.
>
> "Samuel" <samuel@.photoninfotech.com> wrote in message
> news:ergsc6maEHA.2792@.TK2MSFTNGP09.phx.gbl...
> > Hi,
> >
> > I have one column group and 3 columns under it in a matrix. I added
> subtotal
> > to that column group and now all the 3 columns are summarized and shown.
> Now
> > I want to hide one column summary(The other 2 column summary should be
> > shown). How to do that?
> >
> > TIA,
> >
> > Sam
> >
> >
>

Hiding measure group

Is there any way to hide a measure group, e.g. factless measure group which doesn't make sense to expose to end users?If you set the visible property of all the measures in the mg to false, it will be hidden.
C|||Thank you. It will be but a Dimension Data security filter, e.g. EXISTS (Customer.Customer.Customer.Members, Username, "Hidden Measure Group Name") will effectively ignore the hidden measure group as well and return nothing. I wonder if this is a bug.|||

measure group is not an object in browsing model.

the measures from all measure groups of a cube are in flat measure collection of the cube.

you can made such measures invisible.

Hiding Main Report Items based on SubReport Value

Hi Group,
How do i Conditionally Hide the Main Report Iitem based on the SubReport
Value.
My Main Report Looks Like This
Task Id Description Effort
1020 Project Plan 100(Subreport Item)
What i want is if the effort is Zero i Should hide 1020,Project Plan and My
Subreport Value,Items How do i do
Respond ASAPThe main report cannot reach inside the subreport(s) to extract values. You
would need to make sure the relevant value is available in your main query.
--
This post is provided 'AS IS' with no warranties, and confers no rights. All
rights reserved. Some assembly required. Batteries not included. Your
mileage may vary. Objects in mirror may be closer than they appear. No user
serviceable parts inside. Opening cover voids warranty. Keep out of reach of
children under 3.
"Manoj.Pasumarthi" <ManojPasumarthi@.discussions.microsoft.com> wrote in
message news:D0A47874-C1C6-4D0A-88AA-A96BE0E27094@.microsoft.com...
> Hi Group,
> How do i Conditionally Hide the Main Report Iitem based on the SubReport
> Value.
> My Main Report Looks Like This
> Task Id Description Effort
> 1020 Project Plan 100(Subreport Item)
> What i want is if the effort is Zero i Should hide 1020,Project Plan and
My
> Subreport Value,Items How do i do
> Respond ASAP
>|||Hi Chris,
Thank u
"Chris Hays [MSFT]" wrote:
> The main report cannot reach inside the subreport(s) to extract values. You
> would need to make sure the relevant value is available in your main query.
> --
> This post is provided 'AS IS' with no warranties, and confers no rights. All
> rights reserved. Some assembly required. Batteries not included. Your
> mileage may vary. Objects in mirror may be closer than they appear. No user
> serviceable parts inside. Opening cover voids warranty. Keep out of reach of
> children under 3.
> "Manoj.Pasumarthi" <ManojPasumarthi@.discussions.microsoft.com> wrote in
> message news:D0A47874-C1C6-4D0A-88AA-A96BE0E27094@.microsoft.com...
> > Hi Group,
> >
> > How do i Conditionally Hide the Main Report Iitem based on the SubReport
> > Value.
> > My Main Report Looks Like This
> >
> > Task Id Description Effort
> > 1020 Project Plan 100(Subreport Item)
> >
> > What i want is if the effort is Zero i Should hide 1020,Project Plan and
> My
> > Subreport Value,Items How do i do
> >
> > Respond ASAP
> >
> >
>
>|||"Chris Hays [MSFT]" wrote:
> The main report cannot reach inside the subreport(s) to extract values. You
> would need to make sure the relevant value is available in your main query.
> --
> This post is provided 'AS IS' with no warranties, and confers no rights. All
> rights reserved. Some assembly required. Batteries not included. Your
> mileage may vary. Objects in mirror may be closer than they appear. No user
> serviceable parts inside. Opening cover voids warranty. Keep out of reach of
> children under 3.
> "Manoj.Pasumarthi" <ManojPasumarthi@.discussions.microsoft.com> wrote in
> message news:D0A47874-C1C6-4D0A-88AA-A96BE0E27094@.microsoft.com...
> > Hi Group,
> >
> > How do i Conditionally Hide the Main Report Iitem based on the SubReport
> > Value.
> > My Main Report Looks Like This
> >
> > Task Id Description Effort
> > 1020 Project Plan 100(Subreport Item)
> >
> > What i want is if the effort is Zero i Should hide 1020,Project Plan and
> My
> > Subreport Value,Items How do i do
> >
> > Respond ASAP
> >
> >
>
>|||I'm guessing the original question was in regards to RS 2000. Has the answer
changed with RS 2005? Can subreport values be referenced from the master
report in RS 2005
"Chris Hays [MSFT]" wrote:
> The main report cannot reach inside the subreport(s) to extract values. You
> would need to make sure the relevant value is available in your main query.
> --
> This post is provided 'AS IS' with no warranties, and confers no rights. All
> rights reserved. Some assembly required. Batteries not included. Your
> mileage may vary. Objects in mirror may be closer than they appear. No user
> serviceable parts inside. Opening cover voids warranty. Keep out of reach of
> children under 3.
> "Manoj.Pasumarthi" <ManojPasumarthi@.discussions.microsoft.com> wrote in
> message news:D0A47874-C1C6-4D0A-88AA-A96BE0E27094@.microsoft.com...
> > Hi Group,
> >
> > How do i Conditionally Hide the Main Report Iitem based on the SubReport
> > Value.
> > My Main Report Looks Like This
> >
> > Task Id Description Effort
> > 1020 Project Plan 100(Subreport Item)
> >
> > What i want is if the effort is Zero i Should hide 1020,Project Plan and
> My
> > Subreport Value,Items How do i do
> >
> > Respond ASAP
> >
> >
>
>

hiding header row in a group

I have a table with a group. I have one row of the group that is the header for the detail section. How can I suppress the row header in the group if there is no data in the detail section for a group value? I was thinking something along the line of setting the visibilty of the row header to an expression based on the existence of data in the detail, but don't know how to go about this.

Thanks.

Hi steve,

You can try using the Count function for any fields.

The expression in the HIdden property of the table header would be
=IIF(Count(Fields![WhateverField]) = 0, True, False)

I tried it out and it seemed to work on my test.
Hope this works !

BErnard Ong

|||

To check for the existence of records in a given group, you can use count function with scope parameter like this in the visibility expression of the group header row:

=IIf(Count(Fields!YourField.Value, "Group1")=0, true, false)

Where Group1 is the name of the group that you have in your table.

Shyam

Hiding group header/footer lines

Is there any way to hide header/footer group lines in a table without displaying the white space? For Example:

Group 1 Label1

Group 2 Label2

Group 3 Label3

Detail

If I put an expression in the visibility property of Group 2 to make Group 2 invisible for certain groups, it also makes Group 3 and the detail invisible as well. If I set the visibility to Label2 to hide the text for that group header, I still have the white space on the report. Is there a good way to hide (or shrink) the header/footer line of a group dynamically on a group by group basis?

Jim

I figured it out, I was setting the visibility on the group instead of the row. Setting the visibility on the row worked great.

hiding group header conditionally

Hi,
I have an issue with reporting services report. I am including the rdl code
at the bottom of this email. In that report there are two levels of data
shown. The top level is manager id and the level below that is some info
about employees belonging to this manager id. The initial visibility of top
level is visible and second level is hidden with manager id as toggle item.
Now my question comes.
If for some reason in the database all entries for managerid column is null,
then the above mentioned report will have one plus sign alone and all the
employees will show up if we click on that plus sign. (This situation can be
achieved by changing our query to have all nulls for manager id column). Now
Please tell me if such a situation occurs how should I write the conditonal
expression such that the top level row is totally invisible(i.e plus sign
should not be shown as I want the group table1_group1 to be hidden under this
situation) and all employees info should show up directly when the report is
rendered.
Is this possible? if so how it should be done?
Thanks.
RDL File
--
<?xml version="1.0" encoding="utf-8"?>
<Report
xmlns="http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition"
xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
<DataSources>
<DataSource Name="dsrcEmployee">
<DataSourceReference>dsrcEmployee</DataSourceReference>
<rd:DataSourceID>c497ccbe-747c-4454-8e32-bb33161f9921</rd:DataSourceID>
</DataSource>
</DataSources>
<BottomMargin>1in</BottomMargin>
<RightMargin>1in</RightMargin>
<rd:DrawGrid>true</rd:DrawGrid>
<InteractiveWidth>8.5in</InteractiveWidth>
<rd:SnapToGrid>true</rd:SnapToGrid>
<Body>
<ReportItems>
<Table Name="table1">
<Footer>
<TableRows>
<TableRow>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox7">
<rd:DefaultName>textbox7</rd:DefaultName>
<ZIndex>7</ZIndex>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox8">
<rd:DefaultName>textbox8</rd:DefaultName>
<ZIndex>6</ZIndex>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox9">
<rd:DefaultName>textbox9</rd:DefaultName>
<ZIndex>5</ZIndex>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox6">
<rd:DefaultName>textbox6</rd:DefaultName>
<ZIndex>4</ZIndex>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
<Height>0.33333in</Height>
</TableRow>
</TableRows>
</Footer>
<Left>0.5in</Left>
<DataSetName>DataSet1</DataSetName>
<Top>0.375in</Top>
<TableGroups>
<TableGroup>
<Header>
<TableRows>
<TableRow>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="ManagerID">
<rd:DefaultName>ManagerID</rd:DefaultName>
<ZIndex>11</ZIndex>
<Style>
<TextAlign>Left</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Fields!ManagerID.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox10">
<rd:DefaultName>textbox10</rd:DefaultName>
<ZIndex>10</ZIndex>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox11">
<rd:DefaultName>textbox11</rd:DefaultName>
<ZIndex>9</ZIndex>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox12">
<rd:DefaultName>textbox12</rd:DefaultName>
<ZIndex>8</ZIndex>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
<Height>0.25in</Height>
</TableRow>
</TableRows>
</Header>
<Grouping Name="table1_Group1">
<GroupExpressions>
<GroupExpression>=Fields!ManagerID.Value</GroupExpression>
</GroupExpressions>
</Grouping>
</TableGroup>
</TableGroups>
<Details>
<TableRows>
<TableRow>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="EmployeeName">
<rd:DefaultName>EmployeeName</rd:DefaultName>
<ZIndex>3</ZIndex>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Fields!EmployeeName.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="EmailAddress">
<rd:DefaultName>EmailAddress</rd:DefaultName>
<ZIndex>2</ZIndex>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Fields!EmailAddress.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="Phone">
<rd:DefaultName>Phone</rd:DefaultName>
<ZIndex>1</ZIndex>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Fields!Phone.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="Gender">
<rd:DefaultName>Gender</rd:DefaultName>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Fields!Gender.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
<Height>0.33333in</Height>
</TableRow>
</TableRows>
<Visibility>
<ToggleItem>ManagerID</ToggleItem>
<Hidden>true</Hidden>
</Visibility>
</Details>
<Header>
<TableRows>
<TableRow>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox1">
<rd:DefaultName>textbox1</rd:DefaultName>
<ZIndex>15</ZIndex>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>Employee Name</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox2">
<rd:DefaultName>textbox2</rd:DefaultName>
<ZIndex>14</ZIndex>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>Email Address</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox3">
<rd:DefaultName>textbox3</rd:DefaultName>
<ZIndex>13</ZIndex>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>Phone</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox4">
<rd:DefaultName>textbox4</rd:DefaultName>
<ZIndex>12</ZIndex>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>Gender</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
<Height>0.33333in</Height>
</TableRow>
</TableRows>
</Header>
<TableColumns>
<TableColumn>
<Width>1.66667in</Width>
</TableColumn>
<TableColumn>
<Width>1.66667in</Width>
</TableColumn>
<TableColumn>
<Width>1.66667in</Width>
</TableColumn>
<TableColumn>
<Width>1.66667in</Width>
</TableColumn>
</TableColumns>
<Height>1.25in</Height>
</Table>
</ReportItems>
<Height>2in</Height>
</Body>
<rd:ReportID>3b3ab633-ae51-4bcf-8abd-bf6113721db1</rd:ReportID>
<LeftMargin>1in</LeftMargin>
<DataSets>
<DataSet Name="DataSet1">
<Query>
<rd:UseGenericDesigner>true</rd:UseGenericDesigner>
<CommandText>select ManagerID,isnull(ct.Title,' ') + ' ' +
isnull(ct.FirstName,' ') + ' ' + isnull(ct.MiddleName,' ') + ' ' +
isnull(ct.LastName,' ') as EmployeeName,ct.EmailAddress,ct.Phone,Gender=case
emp.Gender when 'M' then 'Male' when 'F' then 'Female' else 'Unknown' end
from HumanResources.Employee emp inner join Person.Contact ct on
emp.ContactID = ct.ContactID</CommandText>
<DataSourceName>dsrcEmployee</DataSourceName>
</Query>
<Fields>
<Field Name="ManagerID">
<rd:TypeName>System.Int32</rd:TypeName>
<DataField>ManagerID</DataField>
</Field>
<Field Name="EmployeeName">
<rd:TypeName>System.String</rd:TypeName>
<DataField>EmployeeName</DataField>
</Field>
<Field Name="EmailAddress">
<rd:TypeName>System.String</rd:TypeName>
<DataField>EmailAddress</DataField>
</Field>
<Field Name="Phone">
<rd:TypeName>System.String</rd:TypeName>
<DataField>Phone</DataField>
</Field>
<Field Name="Gender">
<rd:TypeName>System.String</rd:TypeName>
<DataField>Gender</DataField>
</Field>
</Fields>
</DataSet>
</DataSets>
<Width>7.16667in</Width>
<InteractiveHeight>11in</InteractiveHeight>
<Language>en-US</Language>
<TopMargin>1in</TopMargin>
</Report>I don't think you can toggle the expand/collapse for each toggle item,
in this case the manager ID. Since it is basically working for you,
except when there isn't a manger ID, why don't you modify your dataset
so that a no manger ID message is outputed instead of nulls?
On Mon, 5 Jun 2006 08:47:01 -0700, msdnuser <ringt@.nospam.nospam>
wrote:
>Hi,
>I have an issue with reporting services report. I am including the rdl code
>at the bottom of this email. In that report there are two levels of data
>shown. The top level is manager id and the level below that is some info
>about employees belonging to this manager id. The initial visibility of top
>level is visible and second level is hidden with manager id as toggle item.
>Now my question comes.
>If for some reason in the database all entries for managerid column is null,
>then the above mentioned report will have one plus sign alone and all the
>employees will show up if we click on that plus sign. (This situation can be
>achieved by changing our query to have all nulls for manager id column). Now
>Please tell me if such a situation occurs how should I write the conditonal
>expression such that the top level row is totally invisible(i.e plus sign
>should not be shown as I want the group table1_group1 to be hidden under this
>situation) and all employees info should show up directly when the report is
>rendered.
>Is this possible? if so how it should be done?
>Thanks.
>RDL File (snipped by responder)|||Hello Ringt,
You can try selecting the certain table row (which contains the group
header ) and right click it and choose "Edit Group" menu item. In the
displayed dialog, we choose the "visibility" tab, we can find that we can
define some expression to control the visibility of it.
Regards,
Steven Cheng
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)

hiding columns in a matrix

I am building a matrix report that has fiscal quarters as the column group. I
have multiple data columns within the group. I am trying to allow the report
user to choose which data is displayed by using a parameter. I am able to
hide the columns of data that are not selected but the fiscal quarter labels
remain. Is there a way to limit the column group headers to only the visible
columns?You can hide the controls by setting the Visible option for that control. You
may have to use an expression so that the control is visible when it
satisfies the condition. See how 'iif' can be used in report programming MSDN
documentation.
HTH
Rajesh
MCSD.NET
http://meenrajan.blogspot.com
"KenBo" wrote:
> I am building a matrix report that has fiscal quarters as the column group. I
> have multiple data columns within the group. I am trying to allow the report
> user to choose which data is displayed by using a parameter. I am able to
> hide the columns of data that are not selected but the fiscal quarter labels
> remain. Is there a way to limit the column group headers to only the visible
> columns?

Hiding column heading

I have a report that includes a group that I have hidden. the group becomes
visible using a toggle on a textbox in the group header. I would like to
place a column heading in the header row for the group detail. I want this
header to only appear when the detail is visible. I can use the visibility
property of a text box but if I do that then that affect any color or lines
on the group header row
SO
My question is : Is there a way to use the IIF function to evaluate whether
a particular textbox is visible?I don't think you can access the properties on the fly... At least I haven't
seen an exmple of how to do it ...
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Mardy" <Mardy@.discussions.microsoft.com> wrote in message
news:2424391D-05DD-46F7-A424-B1D965219647@.microsoft.com...
>I have a report that includes a group that I have hidden. the group becomes
> visible using a toggle on a textbox in the group header. I would like to
> place a column heading in the header row for the group detail. I want this
> header to only appear when the detail is visible. I can use the
> visibility
> property of a text box but if I do that then that affect any color or
> lines
> on the group header row
> SO
> My question is : Is there a way to use the IIF function to evaluate
> whether
> a particular textbox is visible?

Hiding a group also hides all nested groups

I have several nested sub-total groups. Depending on a report parameter, I may want to suppress one of the sub-totals. This one group is not the lowest group in the hierarchy and, when I hide it, all the groups "below" it become hidden too. I only want the one group to "disappear", not any others. Is there a way to accomplish this other than creating two versions of the report?

Instead of giving visibility condition to the group (group row), try using the visibility condition on all textboxes (or any other elements) in that group individually. This way, you are hiding only those individual items instead of the group as a whole.

Shyam

Hiding

Hi,
I don't know if this is possible.
I have a matrix with one row group and one column group. Within the column group are three static columns.The rowgroup has subtotals enabled. What I want is that one of the three static columns is hidden in the report but shown in the subtotal. Can this be done?
Thanks,
QIf you're just trying to hide it (leaving a blank space/without rearranging
the layout), you can do that easily using the InScope property.
Set the Hidden property of the textbox to: =InScope("myrowgroupname")
(Typically, the row group name would be something like matrix1_fieldname
unless you renamed it)
--
This post is provided 'AS IS' with no warranties, and confers no rights. All
rights reserved. Some assembly required. Batteries not included. Your
mileage may vary. Objects in mirror may be closer than they appear. No user
serviceable parts inside. Opening cover voids warranty. Keep out of reach of
children under 3.
"Qbee" <Qbee@.discussions.microsoft.com> wrote in message
news:450CA2CE-0554-4D11-A295-72877EEC95AE@.microsoft.com...
> Hi,
> I don't know if this is possible.
> I have a matrix with one row group and one column group. Within the column
group are three static columns.The rowgroup has subtotals enabled. What I
want is that one of the three static columns is hidden in the report but
shown in the subtotal. Can this be done?
> Thanks,
> Q|||Hi Chris,
your solution works great but the textbox which is hidden leaves a blank
column in the report which doesn't look nice.
I am currently converting a lot of Business Objects reports and I notice
that there are table structures which are difficult to convert in reporting
services.
Q
"Chris Hays [MSFT]" wrote:
> If you're just trying to hide it (leaving a blank space/without rearranging
> the layout), you can do that easily using the InScope property.
> Set the Hidden property of the textbox to: =InScope("myrowgroupname")
> (Typically, the row group name would be something like matrix1_fieldname
> unless you renamed it)
> --
> This post is provided 'AS IS' with no warranties, and confers no rights. All
> rights reserved. Some assembly required. Batteries not included. Your
> mileage may vary. Objects in mirror may be closer than they appear. No user
> serviceable parts inside. Opening cover voids warranty. Keep out of reach of
> children under 3.
> "Qbee" <Qbee@.discussions.microsoft.com> wrote in message
> news:450CA2CE-0554-4D11-A295-72877EEC95AE@.microsoft.com...
> > Hi,
> >
> > I don't know if this is possible.
> >
> > I have a matrix with one row group and one column group. Within the column
> group are three static columns.The rowgroup has subtotals enabled. What I
> want is that one of the three static columns is hidden in the report but
> shown in the subtotal. Can this be done?
> >
> > Thanks,
> >
> > Q
>
>|||Generally, tables and matrixes are straightforward. It's when you're trying
to hybridize the two (like you're doing) that we run into trouble.
It's a common enough thing to do that it's high on our future features
wishlist (but pretty expensive to do it right, so probably won't make it in
to SQL 2005).
In the mean time, you can simulate some of it (but not all of it, I'm
afraid) via controlling visibility. In your case, you might be able to do
something wherein you have several overlapping textboxes in a rectangle,
some of which are hidden while in the subtotal and the others of which are
hidden when in the details. Tricky, but possible. I think brucejoh posted
an example of this on the newsgroup a while back... You might want to search
for that.
--
This post is provided 'AS IS' with no warranties, and confers no rights. All
rights reserved. Some assembly required. Batteries not included. Your
mileage may vary. Objects in mirror may be closer than they appear. No user
serviceable parts inside. Opening cover voids warranty. Keep out of reach of
children under 3.
"Qbee" <Qbee@.discussions.microsoft.com> wrote in message
news:B97F4CDF-F084-4C91-8429-D893DC6158EE@.microsoft.com...
> Hi Chris,
> your solution works great but the textbox which is hidden leaves a blank
> column in the report which doesn't look nice.
> I am currently converting a lot of Business Objects reports and I notice
> that there are table structures which are difficult to convert in
reporting
> services.
> Q
> "Chris Hays [MSFT]" wrote:
> > If you're just trying to hide it (leaving a blank space/without
rearranging
> > the layout), you can do that easily using the InScope property.
> >
> > Set the Hidden property of the textbox to: =InScope("myrowgroupname")
> >
> > (Typically, the row group name would be something like matrix1_fieldname
> > unless you renamed it)
> >
> > --
> > This post is provided 'AS IS' with no warranties, and confers no rights.
All
> > rights reserved. Some assembly required. Batteries not included. Your
> > mileage may vary. Objects in mirror may be closer than they appear. No
user
> > serviceable parts inside. Opening cover voids warranty. Keep out of
reach of
> > children under 3.
> > "Qbee" <Qbee@.discussions.microsoft.com> wrote in message
> > news:450CA2CE-0554-4D11-A295-72877EEC95AE@.microsoft.com...
> > > Hi,
> > >
> > > I don't know if this is possible.
> > >
> > > I have a matrix with one row group and one column group. Within the
column
> > group are three static columns.The rowgroup has subtotals enabled. What
I
> > want is that one of the three static columns is hidden in the report but
> > shown in the subtotal. Can this be done?
> > >
> > > Thanks,
> > >
> > > Q
> >
> >
> >