Excel export drill-down problem (sp2)

Hi all,
I have a relatively straight forward report with a Table and a grouping on
the details group. I have the the ToggleItem property of the details group
set to the first cell of the detail row, and it renders as expected in HTML.
(An expand/collapse graphic appears to the left of the cell and dynamically
shows/hides the child rows when clicked.) However, when I export to Excel
the groupings are lost and I see only the root rows and none of the children.
From my understanding the export should render the child rows using the
Outline feature of Excel, but for the life of me I can't get this to work.
Any suggestions on what I'm doing wrong here?
Thanks
BillHello Bill,
If you have drill down reports then you could use show-and-hide toggle
<different from hidden items> and if rendering is done in HTML then it will
show collapsed items so that users can click on the toggle to view hidden
groups.
Using other rendering extension like Excel then the drill down effect is
not supported.
Please see the following topic in Books On-Line for Reporting Services:
Drill Down Reports and Hidden Items.
<http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSCREATE/h
tm/rcr_creating_interactive_v1_8bg4.asp>
Based on my test, Toggleitem feature seems to work for Matrix after
exporting to Excel. However, it does not work properly for table after
exporting to Excel. I think it needs to contain all of the data and then
provide the ability to toggle but apprarently these data is not included in
this situation.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner 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.
| Thread-Topic: Excel export drill-down problem (sp2)
| thread-index: AcWMm3svM+OGxhhzSJqE5HRfXsUjew==| X-WBNR-Posting-Host: 156.153.255.243
| From: =?Utf-8?B?QmlsbCBNZXJz?= <billmers@.newsgroup.nospam>
| Subject: Excel export drill-down problem (sp2)
| Date: Tue, 19 Jul 2005 12:53:06 -0700
| Lines: 15
| Message-ID: <452B550C-8D48-4B08-9805-D213BE226172@.microsoft.com>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="Utf-8"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Content-Class: urn:content-classes:message
| Importance: normal
| Priority: normal
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| Newsgroups: microsoft.public.sqlserver.reportingsvcs
| NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
| Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGXA03.phx.gbl
| Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.reportingsvcs:48405
| X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
|
| Hi all,
|
| I have a relatively straight forward report with a Table and a grouping
on
| the details group. I have the the ToggleItem property of the details
group
| set to the first cell of the detail row, and it renders as expected in
HTML.
| (An expand/collapse graphic appears to the left of the cell and
dynamically
| shows/hides the child rows when clicked.) However, when I export to
Excel
| the groupings are lost and I see only the root rows and none of the
children.
| From my understanding the export should render the child rows using the
| Outline feature of Excel, but for the life of me I can't get this to work.
|
| Any suggestions on what I'm doing wrong here?
|
| Thanks
| Bill
||||Hi Peter,
I've seen quite a few posts on this newsgroup from other people who have
gotten the "Outlining" feature of Excel to work when rendering from Reporting
Services. How is this done?
If it's only supported through a Matrix report that's ok, however I was
unable to get that working either. I saw the same behavior as before, where
the Matrix renders with expand/collapse columns in HTML and they disappear in
Excel. If I have to write an Excel-specific version of the report that's ok,
but I can't get the Outlining feature to work at all.
I have a hard (non-negotiable) requirement to get this feature working, so
any help would be greatly appreciated!
Thanks
"Peter Yang [MSFT]" wrote:
> Hello Bill,
> If you have drill down reports then you could use show-and-hide toggle
> <different from hidden items> and if rendering is done in HTML then it will
> show collapsed items so that users can click on the toggle to view hidden
> groups.
> Using other rendering extension like Excel then the drill down effect is
> not supported.
> Please see the following topic in Books On-Line for Reporting Services:
> Drill Down Reports and Hidden Items.
> <http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSCREATE/h
> tm/rcr_creating_interactive_v1_8bg4.asp>
> Based on my test, Toggleitem feature seems to work for Matrix after
> exporting to Excel. However, it does not work properly for table after
> exporting to Excel. I think it needs to contain all of the data and then
> provide the ability to toggle but apprarently these data is not included in
> this situation.
> Best Regards,
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Partner 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.
>
>
> --
> | Thread-Topic: Excel export drill-down problem (sp2)
> | thread-index: AcWMm3svM+OGxhhzSJqE5HRfXsUjew==> | X-WBNR-Posting-Host: 156.153.255.243
> | From: =?Utf-8?B?QmlsbCBNZXJz?= <billmers@.newsgroup.nospam>
> | Subject: Excel export drill-down problem (sp2)
> | Date: Tue, 19 Jul 2005 12:53:06 -0700
> | Lines: 15
> | Message-ID: <452B550C-8D48-4B08-9805-D213BE226172@.microsoft.com>
> | MIME-Version: 1.0
> | Content-Type: text/plain;
> | charset="Utf-8"
> | Content-Transfer-Encoding: 7bit
> | X-Newsreader: Microsoft CDO for Windows 2000
> | Content-Class: urn:content-classes:message
> | Importance: normal
> | Priority: normal
> | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
> | Newsgroups: microsoft.public.sqlserver.reportingsvcs
> | NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
> | Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGXA03.phx.gbl
> | Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.reportingsvcs:48405
> | X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
> |
> | Hi all,
> |
> | I have a relatively straight forward report with a Table and a grouping
> on
> | the details group. I have the the ToggleItem property of the details
> group
> | set to the first cell of the detail row, and it renders as expected in
> HTML.
> | (An expand/collapse graphic appears to the left of the cell and
> dynamically
> | shows/hides the child rows when clicked.) However, when I export to
> Excel
> | the groupings are lost and I see only the root rows and none of the
> children.
> | From my understanding the export should render the child rows using the
> | Outline feature of Excel, but for the life of me I can't get this to work.
> |
> | Any suggestions on what I'm doing wrong here?
> |
> | Thanks
> | Bill
> |
>|||I have table (not Matrix) where it does use the outlining feature. I created
a drill down report and everything worked as advertised in Excel. I'm a loss
at why it isn't for you but it did for me. I only have a single level of
drill down and haven't tried multiple levels (just trying to think what
might be different for you). Create a simple report with a single drilldown,
does it work or not for you?
Anyway, just thought I would let you know that it does work with tables.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Bill Mers" <billmers@.newsgroup.nospam> wrote in message
news:E2AA4A4B-09D6-400C-AF2A-657366E71870@.microsoft.com...
> Hi Peter,
> I've seen quite a few posts on this newsgroup from other people who have
> gotten the "Outlining" feature of Excel to work when rendering from
> Reporting
> Services. How is this done?
> If it's only supported through a Matrix report that's ok, however I was
> unable to get that working either. I saw the same behavior as before,
> where
> the Matrix renders with expand/collapse columns in HTML and they disappear
> in
> Excel. If I have to write an Excel-specific version of the report that's
> ok,
> but I can't get the Outlining feature to work at all.
> I have a hard (non-negotiable) requirement to get this feature working, so
> any help would be greatly appreciated!
> Thanks
> "Peter Yang [MSFT]" wrote:
>> Hello Bill,
>> If you have drill down reports then you could use show-and-hide toggle
>> <different from hidden items> and if rendering is done in HTML then it
>> will
>> show collapsed items so that users can click on the toggle to view hidden
>> groups.
>> Using other rendering extension like Excel then the drill down effect is
>> not supported.
>> Please see the following topic in Books On-Line for Reporting Services:
>> Drill Down Reports and Hidden Items.
>> <http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSCREATE/h
>> tm/rcr_creating_interactive_v1_8bg4.asp>
>> Based on my test, Toggleitem feature seems to work for Matrix after
>> exporting to Excel. However, it does not work properly for table after
>> exporting to Excel. I think it needs to contain all of the data and then
>> provide the ability to toggle but apprarently these data is not included
>> in
>> this situation.
>> Best Regards,
>> Peter Yang
>> MCSE2000/2003, MCSA, MCDBA
>> Microsoft Online Partner 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.
>>
>>
>> --
>> | Thread-Topic: Excel export drill-down problem (sp2)
>> | thread-index: AcWMm3svM+OGxhhzSJqE5HRfXsUjew==>> | X-WBNR-Posting-Host: 156.153.255.243
>> | From: =?Utf-8?B?QmlsbCBNZXJz?= <billmers@.newsgroup.nospam>
>> | Subject: Excel export drill-down problem (sp2)
>> | Date: Tue, 19 Jul 2005 12:53:06 -0700
>> | Lines: 15
>> | Message-ID: <452B550C-8D48-4B08-9805-D213BE226172@.microsoft.com>
>> | MIME-Version: 1.0
>> | Content-Type: text/plain;
>> | charset="Utf-8"
>> | Content-Transfer-Encoding: 7bit
>> | X-Newsreader: Microsoft CDO for Windows 2000
>> | Content-Class: urn:content-classes:message
>> | Importance: normal
>> | Priority: normal
>> | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
>> | Newsgroups: microsoft.public.sqlserver.reportingsvcs
>> | NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
>> | Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGXA03.phx.gbl
>> | Xref: TK2MSFTNGXA01.phx.gbl
>> microsoft.public.sqlserver.reportingsvcs:48405
>> | X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
>> |
>> | Hi all,
>> |
>> | I have a relatively straight forward report with a Table and a grouping
>> on
>> | the details group. I have the the ToggleItem property of the details
>> group
>> | set to the first cell of the detail row, and it renders as expected in
>> HTML.
>> | (An expand/collapse graphic appears to the left of the cell and
>> dynamically
>> | shows/hides the child rows when clicked.) However, when I export to
>> Excel
>> | the groupings are lost and I see only the root rows and none of the
>> children.
>> | From my understanding the export should render the child rows using
>> the
>> | Outline feature of Excel, but for the life of me I can't get this to
>> work.
>> |
>> | Any suggestions on what I'm doing wrong here?
>> |
>> | Thanks
>> | Bill
>> |
>>|||Hi Bruce,
Thanks for the confirmation that the Excel outling feature does work with
just a straight table. Somebody on my team was able to get it to work as
well, however we still can't get it working with my particular report. What
I suspect might be making the difference is that my report has both the
grouping and toggleItem set on the same row. The table has only two rows in
it: the header row and the details row, and the first column in the detail
row is the toggle for the whole row. This renders fine in HTML but doesn't
export to Excel correctly.
Anybody have any ideas on how to get this to work? I've opened a trouble
ticket with Microsoft but so far that hasn't solved the issue. Any
alternatives/workarounds would be much appreciated.
Thanks
Bill
"Bruce L-C [MVP]" wrote:
> I have table (not Matrix) where it does use the outlining feature. I created
> a drill down report and everything worked as advertised in Excel. I'm a loss
> at why it isn't for you but it did for me. I only have a single level of
> drill down and haven't tried multiple levels (just trying to think what
> might be different for you). Create a simple report with a single drilldown,
> does it work or not for you?
> Anyway, just thought I would let you know that it does work with tables.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>
> "Bill Mers" <billmers@.newsgroup.nospam> wrote in message
> news:E2AA4A4B-09D6-400C-AF2A-657366E71870@.microsoft.com...
> > Hi Peter,
> >
> > I've seen quite a few posts on this newsgroup from other people who have
> > gotten the "Outlining" feature of Excel to work when rendering from
> > Reporting
> > Services. How is this done?
> >
> > If it's only supported through a Matrix report that's ok, however I was
> > unable to get that working either. I saw the same behavior as before,
> > where
> > the Matrix renders with expand/collapse columns in HTML and they disappear
> > in
> > Excel. If I have to write an Excel-specific version of the report that's
> > ok,
> > but I can't get the Outlining feature to work at all.
> >
> > I have a hard (non-negotiable) requirement to get this feature working, so
> > any help would be greatly appreciated!
> >
> > Thanks
> >
> > "Peter Yang [MSFT]" wrote:
> >
> >> Hello Bill,
> >>
> >> If you have drill down reports then you could use show-and-hide toggle
> >> <different from hidden items> and if rendering is done in HTML then it
> >> will
> >> show collapsed items so that users can click on the toggle to view hidden
> >> groups.
> >>
> >> Using other rendering extension like Excel then the drill down effect is
> >> not supported.
> >>
> >> Please see the following topic in Books On-Line for Reporting Services:
> >> Drill Down Reports and Hidden Items.
> >> <http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSCREATE/h
> >> tm/rcr_creating_interactive_v1_8bg4.asp>
> >>
> >> Based on my test, Toggleitem feature seems to work for Matrix after
> >> exporting to Excel. However, it does not work properly for table after
> >> exporting to Excel. I think it needs to contain all of the data and then
> >> provide the ability to toggle but apprarently these data is not included
> >> in
> >> this situation.
> >>
> >> Best Regards,
> >>
> >> Peter Yang
> >> MCSE2000/2003, MCSA, MCDBA
> >> Microsoft Online Partner 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.
> >>
> >>
> >>
> >>
> >> --
> >> | Thread-Topic: Excel export drill-down problem (sp2)
> >> | thread-index: AcWMm3svM+OGxhhzSJqE5HRfXsUjew==> >> | X-WBNR-Posting-Host: 156.153.255.243
> >> | From: =?Utf-8?B?QmlsbCBNZXJz?= <billmers@.newsgroup.nospam>
> >> | Subject: Excel export drill-down problem (sp2)
> >> | Date: Tue, 19 Jul 2005 12:53:06 -0700
> >> | Lines: 15
> >> | Message-ID: <452B550C-8D48-4B08-9805-D213BE226172@.microsoft.com>
> >> | MIME-Version: 1.0
> >> | Content-Type: text/plain;
> >> | charset="Utf-8"
> >> | Content-Transfer-Encoding: 7bit
> >> | X-Newsreader: Microsoft CDO for Windows 2000
> >> | Content-Class: urn:content-classes:message
> >> | Importance: normal
> >> | Priority: normal
> >> | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
> >> | Newsgroups: microsoft.public.sqlserver.reportingsvcs
> >> | NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
> >> | Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGXA03.phx.gbl
> >> | Xref: TK2MSFTNGXA01.phx.gbl
> >> microsoft.public.sqlserver.reportingsvcs:48405
> >> | X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
> >> |
> >> | Hi all,
> >> |
> >> | I have a relatively straight forward report with a Table and a grouping
> >> on
> >> | the details group. I have the the ToggleItem property of the details
> >> group
> >> | set to the first cell of the detail row, and it renders as expected in
> >> HTML.
> >> | (An expand/collapse graphic appears to the left of the cell and
> >> dynamically
> >> | shows/hides the child rows when clicked.) However, when I export to
> >> Excel
> >> | the groupings are lost and I see only the root rows and none of the
> >> children.
> >> | From my understanding the export should render the child rows using
> >> the
> >> | Outline feature of Excel, but for the life of me I can't get this to
> >> work.
> >> |
> >> | Any suggestions on what I'm doing wrong here?
> >> |
> >> | Thanks
> >> | Bill
> >> |
> >>
> >>
>
>|||Hello Bill,
Based on my further test, I found if I use the following method, I could
export the Excel as expected:
1. In Layout view, click the table so that column and row handles appear
above and next to the table or matrix.
2. Right-click the corner handle of the table or matrix, and then click
Properties.
3. On the Groups tab, select the group to edit, and then click Edit.
4. On the Visibility tab, do the following:
5. For Initial visibility, select Hidden.
6. Select Visibility can be toggled by another report item.
7. In Report item, type or select the name of the text box that users click
to show the selected item. I selected Textbox1.
Note: The value for Report item must be the name of a text box that is
either in the same group as the item that is being hidden or in another
group or item in the same container hierarchy (up to and including the
report body).
By using the following method, I reproduced the issue you encountered:
1. In Layout view, click the table so that column and row handles appear
above and next to the table or matrix.
2. Right-click the detail handle of the table or matrix, and then click
Edit Group
3. On the Visibility tab, do the following:
4. For Initial visibility, select Hidden.
5. Select Visibility can be toggled by another report item.
6. In Report item, type or select the name of the text box that users click
to show the selected item. I selected Textbox1.
Though in HTML rendering, above methods have the same behavior, they are
different in Excel rendering.
By checking the RDL code, I found the differenences between them.
1. For the 1st method, the <visibility> entry is included in <Tablegroup>
item. The data of the table is included in this report even the report is
collapsed.
2. For the 2nd method, the <visibility> entry is in <details> item, the
data that is necessary to toggle is not actaully included in this
situation.
Hope this information is helpful.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================
Business-Critical Phone Support (BCPS) provides you with technical phone
support at no charge during critical LAN outages or "business down"
situations. This benefit is available 24 hours a day, 7 days a week to all
Microsoft technology partners in the United States and Canada.
This and other support options are available here:
BCPS:
https://partner.microsoft.com/US/technicalsupport/supportoverview/40010469
Others: https://partner.microsoft.com/US/technicalsupport/supportoverview/
If you are outside the United States, please visit our International
Support page:
http://support.microsoft.com/default.aspx?scid=%2finternational.aspx.
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
| Thread-Topic: Excel export drill-down problem (sp2)
| thread-index: AcWRO/qqj6RePYO2SkSf0MlULM92LQ==| X-WBNR-Posting-Host: 161.114.64.75
| From: =?Utf-8?B?QmlsbCBNZXJz?= <billmers@.newsgroup.nospam>
| References: <452B550C-8D48-4B08-9805-D213BE226172@.microsoft.com>
<V8UcOnQjFHA.3120@.TK2MSFTNGXA01.phx.gbl>
<E2AA4A4B-09D6-400C-AF2A-657366E71870@.microsoft.com>
<OK###2TjFHA.3568@.tk2msftngp13.phx.gbl>
| Subject: Re: Excel export drill-down problem (sp2)
| Date: Mon, 25 Jul 2005 10:12:04 -0700
| Lines: 154
| Message-ID: <3C668BC0-EF6A-4453-AC4E-FB10A1EE5F68@.microsoft.com>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="Utf-8"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Content-Class: urn:content-classes:message
| Importance: normal
| Priority: normal
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| Newsgroups: microsoft.public.sqlserver.reportingsvcs
| NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
| Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGXA03.phx.gbl
| Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.reportingsvcs:48828
| X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
|
| Hi Bruce,
|
| Thanks for the confirmation that the Excel outling feature does work with
| just a straight table. Somebody on my team was able to get it to work as
| well, however we still can't get it working with my particular report.
What
| I suspect might be making the difference is that my report has both the
| grouping and toggleItem set on the same row. The table has only two rows
in
| it: the header row and the details row, and the first column in the
detail
| row is the toggle for the whole row. This renders fine in HTML but
doesn't
| export to Excel correctly.
|
| Anybody have any ideas on how to get this to work? I've opened a trouble
| ticket with Microsoft but so far that hasn't solved the issue. Any
| alternatives/workarounds would be much appreciated.
|
| Thanks
| Bill
|
| "Bruce L-C [MVP]" wrote:
|
| > I have table (not Matrix) where it does use the outlining feature. I
created
| > a drill down report and everything worked as advertised in Excel. I'm a
loss
| > at why it isn't for you but it did for me. I only have a single level
of
| > drill down and haven't tried multiple levels (just trying to think what
| > might be different for you). Create a simple report with a single
drilldown,
| > does it work or not for you?
| >
| > Anyway, just thought I would let you know that it does work with tables.
| >
| >
| > --
| > Bruce Loehle-Conger
| > MVP SQL Server Reporting Services
| >
| >
| > "Bill Mers" <billmers@.newsgroup.nospam> wrote in message
| > news:E2AA4A4B-09D6-400C-AF2A-657366E71870@.microsoft.com...
| > > Hi Peter,
| > >
| > > I've seen quite a few posts on this newsgroup from other people who
have
| > > gotten the "Outlining" feature of Excel to work when rendering from
| > > Reporting
| > > Services. How is this done?
| > >
| > > If it's only supported through a Matrix report that's ok, however I
was
| > > unable to get that working either. I saw the same behavior as
before,
| > > where
| > > the Matrix renders with expand/collapse columns in HTML and they
disappear
| > > in
| > > Excel. If I have to write an Excel-specific version of the report
that's
| > > ok,
| > > but I can't get the Outlining feature to work at all.
| > >
| > > I have a hard (non-negotiable) requirement to get this feature
working, so
| > > any help would be greatly appreciated!
| > >
| > > Thanks
| > >
| > > "Peter Yang [MSFT]" wrote:
| > >
| > >> Hello Bill,
| > >>
| > >> If you have drill down reports then you could use show-and-hide
toggle
| > >> <different from hidden items> and if rendering is done in HTML then
it
| > >> will
| > >> show collapsed items so that users can click on the toggle to view
hidden
| > >> groups.
| > >>
| > >> Using other rendering extension like Excel then the drill down
effect is
| > >> not supported.
| > >>
| > >> Please see the following topic in Books On-Line for Reporting
Services:
| > >> Drill Down Reports and Hidden Items.
| > >>
<http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSCREATE/h
| > >> tm/rcr_creating_interactive_v1_8bg4.asp>
| > >>
| > >> Based on my test, Toggleitem feature seems to work for Matrix after
| > >> exporting to Excel. However, it does not work properly for table
after
| > >> exporting to Excel. I think it needs to contain all of the data and
then
| > >> provide the ability to toggle but apprarently these data is not
included
| > >> in
| > >> this situation.
| > >>
| > >> Best Regards,
| > >>
| > >> Peter Yang
| > >> MCSE2000/2003, MCSA, MCDBA
| > >> Microsoft Online Partner 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.
| > >>
| > >>
| > >>
| > >>
| > >> --
| > >> | Thread-Topic: Excel export drill-down problem (sp2)
| > >> | thread-index: AcWMm3svM+OGxhhzSJqE5HRfXsUjew==| > >> | X-WBNR-Posting-Host: 156.153.255.243
| > >> | From: =?Utf-8?B?QmlsbCBNZXJz?= <billmers@.newsgroup.nospam>
| > >> | Subject: Excel export drill-down problem (sp2)
| > >> | Date: Tue, 19 Jul 2005 12:53:06 -0700
| > >> | Lines: 15
| > >> | Message-ID: <452B550C-8D48-4B08-9805-D213BE226172@.microsoft.com>
| > >> | MIME-Version: 1.0
| > >> | Content-Type: text/plain;
| > >> | charset="Utf-8"
| > >> | Content-Transfer-Encoding: 7bit
| > >> | X-Newsreader: Microsoft CDO for Windows 2000
| > >> | Content-Class: urn:content-classes:message
| > >> | Importance: normal
| > >> | Priority: normal
| > >> | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| > >> | Newsgroups: microsoft.public.sqlserver.reportingsvcs
| > >> | NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
| > >> | Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGXA03.phx.gbl
| > >> | Xref: TK2MSFTNGXA01.phx.gbl
| > >> microsoft.public.sqlserver.reportingsvcs:48405
| > >> | X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
| > >> |
| > >> | Hi all,
| > >> |
| > >> | I have a relatively straight forward report with a Table and a
grouping
| > >> on
| > >> | the details group. I have the the ToggleItem property of the
details
| > >> group
| > >> | set to the first cell of the detail row, and it renders as
expected in
| > >> HTML.
| > >> | (An expand/collapse graphic appears to the left of the cell and
| > >> dynamically
| > >> | shows/hides the child rows when clicked.) However, when I export
to
| > >> Excel
| > >> | the groupings are lost and I see only the root rows and none of the
| > >> children.
| > >> | From my understanding the export should render the child rows
using
| > >> the
| > >> | Outline feature of Excel, but for the life of me I can't get this
to
| > >> work.
| > >> |
| > >> | Any suggestions on what I'm doing wrong here?
| > >> |
| > >> | Thanks
| > >> | Bill
| > >> |
| > >>
| > >>
| >
| >
| >
||||Did anyone resolve this? I have rendered a report into Excel but only one
user out of 3 (includes me) gets the Excel doc with expand/collapse auto
format. I can't figure out why only she gets the correct format. Any ideas as
to what I need specifiically to check for in Excel?
There are 8 groups with details below each. When she gets report in Excel,
she sees outline symbols (plus , minus, etc.) and it looks great. When I get
the report in Excel, it shows all info but not in outline format. HELP!
thanks
"Peter Yang [MSFT]" wrote:
> Hello Bill,
> Based on my further test, I found if I use the following method, I could
> export the Excel as expected:
> 1. In Layout view, click the table so that column and row handles appear
> above and next to the table or matrix.
> 2. Right-click the corner handle of the table or matrix, and then click
> Properties.
> 3. On the Groups tab, select the group to edit, and then click Edit.
> 4. On the Visibility tab, do the following:
> 5. For Initial visibility, select Hidden.
> 6. Select Visibility can be toggled by another report item.
> 7. In Report item, type or select the name of the text box that users click
> to show the selected item. I selected Textbox1.
> Note: The value for Report item must be the name of a text box that is
> either in the same group as the item that is being hidden or in another
> group or item in the same container hierarchy (up to and including the
> report body).
> By using the following method, I reproduced the issue you encountered:
> 1. In Layout view, click the table so that column and row handles appear
> above and next to the table or matrix.
> 2. Right-click the detail handle of the table or matrix, and then click
> Edit Group
> 3. On the Visibility tab, do the following:
> 4. For Initial visibility, select Hidden.
> 5. Select Visibility can be toggled by another report item.
> 6. In Report item, type or select the name of the text box that users click
> to show the selected item. I selected Textbox1.
> Though in HTML rendering, above methods have the same behavior, they are
> different in Excel rendering.
> By checking the RDL code, I found the differenences between them.
> 1. For the 1st method, the <visibility> entry is included in <Tablegroup>
> item. The data of the table is included in this report even the report is
> collapsed.
> 2. For the 2nd method, the <visibility> entry is in <details> item, the
> data that is necessary to toggle is not actaully included in this
> situation.
> Hope this information is helpful.
> Best Regards,
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> =====================================================> Business-Critical Phone Support (BCPS) provides you with technical phone
> support at no charge during critical LAN outages or "business down"
> situations. This benefit is available 24 hours a day, 7 days a week to all
> Microsoft technology partners in the United States and Canada.
> This and other support options are available here:
> BCPS:
> https://partner.microsoft.com/US/technicalsupport/supportoverview/40010469
> Others: https://partner.microsoft.com/US/technicalsupport/supportoverview/
> If you are outside the United States, please visit our International
> Support page:
> http://support.microsoft.com/default.aspx?scid=%2finternational.aspx.
> =====================================================> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> --
> | Thread-Topic: Excel export drill-down problem (sp2)
> | thread-index: AcWRO/qqj6RePYO2SkSf0MlULM92LQ==> | X-WBNR-Posting-Host: 161.114.64.75
> | From: =?Utf-8?B?QmlsbCBNZXJz?= <billmers@.newsgroup.nospam>
> | References: <452B550C-8D48-4B08-9805-D213BE226172@.microsoft.com>
> <V8UcOnQjFHA.3120@.TK2MSFTNGXA01.phx.gbl>
> <E2AA4A4B-09D6-400C-AF2A-657366E71870@.microsoft.com>
> <OK###2TjFHA.3568@.tk2msftngp13.phx.gbl>
> | Subject: Re: Excel export drill-down problem (sp2)
> | Date: Mon, 25 Jul 2005 10:12:04 -0700
> | Lines: 154
> | Message-ID: <3C668BC0-EF6A-4453-AC4E-FB10A1EE5F68@.microsoft.com>
> | MIME-Version: 1.0
> | Content-Type: text/plain;
> | charset="Utf-8"
> | Content-Transfer-Encoding: 7bit
> | X-Newsreader: Microsoft CDO for Windows 2000
> | Content-Class: urn:content-classes:message
> | Importance: normal
> | Priority: normal
> | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
> | Newsgroups: microsoft.public.sqlserver.reportingsvcs
> | NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
> | Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGXA03.phx.gbl
> | Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.reportingsvcs:48828
> | X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
> |
> | Hi Bruce,
> |
> | Thanks for the confirmation that the Excel outling feature does work with
> | just a straight table. Somebody on my team was able to get it to work as
> | well, however we still can't get it working with my particular report.
> What
> | I suspect might be making the difference is that my report has both the
> | grouping and toggleItem set on the same row. The table has only two rows
> in
> | it: the header row and the details row, and the first column in the
> detail
> | row is the toggle for the whole row. This renders fine in HTML but
> doesn't
> | export to Excel correctly.
> |
> | Anybody have any ideas on how to get this to work? I've opened a trouble
> | ticket with Microsoft but so far that hasn't solved the issue. Any
> | alternatives/workarounds would be much appreciated.
> |
> | Thanks
> | Bill
> |
> | "Bruce L-C [MVP]" wrote:
> |
> | > I have table (not Matrix) where it does use the outlining feature. I
> created
> | > a drill down report and everything worked as advertised in Excel. I'm a
> loss
> | > at why it isn't for you but it did for me. I only have a single level
> of
> | > drill down and haven't tried multiple levels (just trying to think what
> | > might be different for you). Create a simple report with a single
> drilldown,
> | > does it work or not for you?
> | >
> | > Anyway, just thought I would let you know that it does work with tables.
> | >
> | >
> | > --
> | > Bruce Loehle-Conger
> | > MVP SQL Server Reporting Services
> | >
> | >
> | > "Bill Mers" <billmers@.newsgroup.nospam> wrote in message
> | > news:E2AA4A4B-09D6-400C-AF2A-657366E71870@.microsoft.com...
> | > > Hi Peter,
> | > >
> | > > I've seen quite a few posts on this newsgroup from other people who
> have
> | > > gotten the "Outlining" feature of Excel to work when rendering from
> | > > Reporting
> | > > Services. How is this done?
> | > >
> | > > If it's only supported through a Matrix report that's ok, however I
> was
> | > > unable to get that working either. I saw the same behavior as
> before,
> | > > where
> | > > the Matrix renders with expand/collapse columns in HTML and they
> disappear
> | > > in
> | > > Excel. If I have to write an Excel-specific version of the report
> that's
> | > > ok,
> | > > but I can't get the Outlining feature to work at all.
> | > >
> | > > I have a hard (non-negotiable) requirement to get this feature
> working, so
> | > > any help would be greatly appreciated!
> | > >
> | > > Thanks
> | > >
> | > > "Peter Yang [MSFT]" wrote:
> | > >
> | > >> Hello Bill,
> | > >>
> | > >> If you have drill down reports then you could use show-and-hide
> toggle
> | > >> <different from hidden items> and if rendering is done in HTML then
> it
> | > >> will
> | > >> show collapsed items so that users can click on the toggle to view
> hidden
> | > >> groups.
> | > >>
> | > >> Using other rendering extension like Excel then the drill down
> effect is
> | > >> not supported.
> | > >>
> | > >> Please see the following topic in Books On-Line for Reporting
> Services:
> | > >> Drill Down Reports and Hidden Items.
> | > >>
> <http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSCREATE/h
> | > >> tm/rcr_creating_interactive_v1_8bg4.asp>
> | > >>
> | > >> Based on my test, Toggleitem feature seems to work for Matrix after
> | > >> exporting to Excel. However, it does not work properly for table
> after
> | > >> exporting to Excel. I think it needs to contain all of the data and
> then
> | > >> provide the ability to toggle but apprarently these data is not
> included
> | > >> in
> | > >> this situation.
> | > >>
> | > >> Best Regards,
> | > >>
> | > >> Peter Yang
> | > >> MCSE2000/2003, MCSA, MCDBA
> | > >> Microsoft Online Partner 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.
> | > >>
> | > >>
> | > >>
> | > >>
> | > >> --
> | > >> | Thread-Topic: Excel export drill-down problem (sp2)
> | > >> | thread-index: AcWMm3svM+OGxhhzSJqE5HRfXsUjew==> | > >> | X-WBNR-Posting-Host: 156.153.255.243
> | > >> | From: =?Utf-8?B?QmlsbCBNZXJz?= <billmers@.newsgroup.nospam>
> | > >> | Subject: Excel export drill-down problem (sp2)
> | > >> | Date: Tue, 19 Jul 2005 12:53:06 -0700
> | > >> | Lines: 15
> | > >> | Message-ID: <452B550C-8D48-4B08-9805-D213BE226172@.microsoft.com>
> | > >> | MIME-Version: 1.0
> | > >> | Content-Type: text/plain;
> | > >> | charset="Utf-8"
> | > >> | Content-Transfer-Encoding: 7bit
> | > >> | X-Newsreader: Microsoft CDO for Windows 2000
> | > >> | Content-Class: urn:content-classes:message
> | > >> | Importance: normal
> | > >> | Priority: normal
> | > >> | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
> | > >> | Newsgroups: microsoft.public.sqlserver.reportingsvcs
> | > >> | NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
> | > >> | Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGXA03.phx.gbl
> | > >> | Xref: TK2MSFTNGXA01.phx.gbl
> | > >> microsoft.public.sqlserver.reportingsvcs:48405
> | > >> | X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
> | > >> |
> | > >> | Hi all,
> | > >> |
> | > >> | I have a relatively straight forward report with a Table and a
> grouping
> | > >> on
> | > >> | the details group. I have the the ToggleItem property of the
> details
> | > >> group
> | > >> | set to the first cell of the detail row, and it renders as
> expected in
> | > >> HTML.
> | > >> | (An expand/collapse graphic appears to the left of the cell and
> | > >> dynamically
> | > >> | shows/hides the child rows when clicked.) However, when I export
> to
> | > >> Excel
> | > >> | the groupings are lost and I see only the root rows and none of the
> | > >> children.
> | > >> | From my understanding the export should render the child rows
> using
> | > >> the
> | > >> | Outline feature of Excel, but for the life of me I can't get this
> to
> | > >> work.
> | > >> |
> | > >> | Any suggestions on what I'm doing wrong here?
> | > >> |
> | > >> | Thanks
> | > >> | Bill
> | > >> |
> | > >>
> | > >>
> | >
> | >
> | >
> |
>|||I have a table with one group ('table1_state') and a detail part.
What i made now is a dummy group ('table1_dummy') only for handling the
visibility. This dummy group is a child of the table1_state group and
has no footer and no header. Its visibility is set to hidden and the
toggle item is set to the first textbox of table1_state group (in my
case 'txtState').
Check that no other visibility configurations are set.
<TableGroup>
<Grouping Name="table1_dummy">
<GroupExpressions>
<GroupExpression>=Fields!PrimaryKey.Value</GroupExpression>
</GroupExpressions>
</Grouping>
<Visibility>
<ToggleItem>txtState</ToggleItem>
<Hidden>true</Hidden>
</Visibility>
</TableGroup>
</TableGroups>|||Bill,
I have the same problem. What is even stranger is that there is an example
sql report that comes with Northwind called "Northwind Simple report", which
also has a single collapible row. This report will export to excel with the
children data while my report with similar settings looses this data.
I have found no reason for this.
Have you had any luck with working out a solution ?