"Could not find installable ISAM"

Hi, when l use the ADO.NET with the following query,
SELECT * INTO XLImport5 FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\test\xltest.xls', 'SELECT * FROM [Customers$]')

i get the following error, i.e.

Error: 0xC002F210 at Execute SQL Task, Execute SQL Task: Executing the query "SELECT * INTO XLImport5 FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\test\xltest.xls', 'SELECT * FROM [Customers$]')" failed with the following error: "Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".
OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" returned message "Could not find installable ISAM.".". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Task failed: Execute SQL Task

Any idea, what's wrong?

Regards,
YB Lim

I read before... a fix for that could be to reinstall MDAC.

http://support.microsoft.com/default.aspx/kb/283881

http://support.microsoft.com/default.aspx/kb/209805

http://support.microsoft.com/default.aspx/kb/90111

"#Value!" error for calculated cells

I have designed an AS 2000 cube with quite complex calculated cells, which work fine in cube browser or with MDX. The moment I use Excel 2007 Pivot table (both in default or compatibility mode), it shows #Value! instead of the calculations. I thought GetPivotData simply returned values from the cube and did not do any calculations of its own... I spent a lot of time to design the cube, but now it cannot be used from Excel, which it needs to be. Any help would be greatly appreciated.

Best regards.

Have you tried creating a copy of your cube, removing all the calculated cells and then connecting to it?

This might help isolate if it is just an Excel connectivity issue or an problem to do with the calculations. Unfortunately you cannot use profiler to trace AS 2000 like you can with AS 2005 otherwise we could trace the MDX that Excel is sending to the server.

|||

Thank you Darren,

It is definitely not an Excel connectivity issue, since everything else shows on the cube. It seems to fall over when it comes across calculated cells which are defined on the cube. And it does seem to be specific to Excel only. I remember the was an issue with pivot tables when the was a limitation on the cell definition MDX length, but it is unlikely to be that (I used the very minimun number of dimensions). Maybe there is known issue with the display of certain calculated cells in Excel ...

|||I think I managed to locat ethe actual problem area - those caclulated cells use a User Defined Function. When I remove the function, the "#Value!" message disappears . It is strange, because the function is not called by excel, it should be resolved on the cube where it works 100%...|||

The problem in fact looks similar to one described in

http://support.microsoft.com/kb/238306

But that was valid for much older versions of AS and was supposed to be fixed a while ago. (I am using up to date version of AS 2000).

|||

it should be resolved on the cube where it works 100%...

I am just wondering if this is not the case. AS2k used to do a fair bit of the query resolution on the client. If you run ProcessMonitor while Excel is trying to execute the query you might be able to see if it is trying to execute the UDF on the client (although it might be hard as ProcessMonitor catches a lot of information)|||Actually an easier way might be to try installing your UDF on a client machine and see if that fixes the problem.|||

I tried that and it di dnot work. One thing I noticed Excel looked in C:\Documents and Settings\f2978326\Application Data\Microsoft\Template for UserDefined.UDF file and UserDefined is the name of the library while UDF is the name of the class that calculations use ..... Does anyone actually have the information on how PivotTable service handles cells which reference UDFs from analysis server? So far I have not been able to get it anywhere...

<DeviceInfo>

Hello,
Is it possible to set a default for an export <DeviceInfo> setting. I'd
like to set some defaults for Excel, but haven't seen any documentation on
how to do this in RSReportServer.config (I would presume?).
Here's what I have so far, but to no avail:
<Extension Name="EXCEL"
Type="Microsoft.ReportingServices.Rendering.ExcelRenderer.ExcelRenderer,Micr
osoft.ReportingServices.ExcelRendering">
<DeviceInfo>
<SimplePageHeaders>True</SimplePageHeaders>
</DeviceInfo>
</Extension>
Any ideas would be greatly appreciated.
Best Regards,
Benjamin PierceThis is on the wish-list for a future release. Currently, the only way is to
construct the URL or use SOAP API.
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Benjamin Pierce" <bpierce@.opentext.com> wrote in message
news:OCtCh4bbEHA.2572@.tk2msftngp13.phx.gbl...
> Hello,
> Is it possible to set a default for an export <DeviceInfo> setting. I'd
> like to set some defaults for Excel, but haven't seen any documentation on
> how to do this in RSReportServer.config (I would presume?).
> Here's what I have so far, but to no avail:
> <Extension Name="EXCEL"
>
Type="Microsoft.ReportingServices.Rendering.ExcelRenderer.ExcelRenderer,Micr
> osoft.ReportingServices.ExcelRendering">
> <DeviceInfo>
> <SimplePageHeaders>True</SimplePageHeaders>
> </DeviceInfo>
> </Extension>
> Any ideas would be greatly appreciated.
>
> Best Regards,
> Benjamin Pierce
>|||To eliminate the merged cells I built an extract utilizing a
webreference to the reporting websevice and then passing the
deviceinfo as such in the Render call.
If format.Extension = ".xls" Then deviceInfo ="<DeviceInfo><SimplePageHeaders>True</SimplePageHeaders></DeviceInfo>"
Dim data() As Byte
data = _rs.Render(reportItem.Path, format.Name, Nothing,
deviceInfo, parameters, Nothing, Nothing, _
encoding, mimeType, parametersUsed, warnings, streamIDs)
It does return the excel report with no headers and no merged cells.
All of this is in the Wrox book - Professional SQL Server Reporting
Services.
Chapter 9 on programmatic rendering.
--John
"Benjamin Pierce" <bpierce@.opentext.com> wrote in message news:<OCtCh4bbEHA.2572@.tk2msftngp13.phx.gbl>...
> Hello,
> Is it possible to set a default for an export <DeviceInfo> setting. I'd
> like to set some defaults for Excel, but haven't seen any documentation on
> how to do this in RSReportServer.config (I would presume?).
> Here's what I have so far, but to no avail:
> <Extension Name="EXCEL"
> Type="Microsoft.ReportingServices.Rendering.ExcelRenderer.ExcelRenderer,Micr
> osoft.ReportingServices.ExcelRendering">
> <DeviceInfo>
> <SimplePageHeaders>True</SimplePageHeaders>
> </DeviceInfo>
> </Extension>
> Any ideas would be greatly appreciated.
>
> Best Regards,
> Benjamin Pierce|||John:
According to the WroxBook, I need to buid an Application Interface to do this.
Is there a way that I can programatically do this inside of Reporting
Services.
My problem is with the csv export, I need to eliminate the hearders in the
output file.
Ravi Mumulla at Microsoft suggested the append method in the URL -- my users
are not computer friendly and therefore this task is not going to be easy for
them.
PLEASE HELP!
Thanks,
john jr.
"John" wrote:
> To eliminate the merged cells I built an extract utilizing a
> webreference to the reporting websevice and then passing the
> deviceinfo as such in the Render call.
> If format.Extension = ".xls" Then deviceInfo => "<DeviceInfo><SimplePageHeaders>True</SimplePageHeaders></DeviceInfo>"
> Dim data() As Byte
> data = _rs.Render(reportItem.Path, format.Name, Nothing,
> deviceInfo, parameters, Nothing, Nothing, _
> encoding, mimeType, parametersUsed, warnings, streamIDs)
> It does return the excel report with no headers and no merged cells.
> All of this is in the Wrox book - Professional SQL Server Reporting
> Services.
> Chapter 9 on programmatic rendering.
> --John
>
> "Benjamin Pierce" <bpierce@.opentext.com> wrote in message news:<OCtCh4bbEHA.2572@.tk2msftngp13.phx.gbl>...
> > Hello,
> >
> > Is it possible to set a default for an export <DeviceInfo> setting. I'd
> > like to set some defaults for Excel, but haven't seen any documentation on
> > how to do this in RSReportServer.config (I would presume?).
> >
> > Here's what I have so far, but to no avail:
> >
> > <Extension Name="EXCEL"
> > Type="Microsoft.ReportingServices.Rendering.ExcelRenderer.ExcelRenderer,Micr
> > osoft.ReportingServices.ExcelRendering">
> > <DeviceInfo>
> > <SimplePageHeaders>True</SimplePageHeaders>
> > </DeviceInfo>
> > </Extension>
> >
> > Any ideas would be greatly appreciated.
> >
> >
> > Best Regards,
> >
> > Benjamin Pierce
>|||URL Access is one option. You can also use the SOAP API Render() method as
suggested eariler on this thread. Check
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rsprog/htm/rsp_ref_soapapi_service_lz_6x0z.asp
for sample code. Use appropriate device info setting,
<NoHeader>true</NoHeader> in your case.
Check the sample code in the following links on how to create simple
winforms/web applications for your end users:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rsprog/htm/rsp_prog_intro_35pi.asp
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rsprog/htm/rsp_prog_intro_6f6q.asp
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rsprog/htm/rsp_prog_intro_7vqa.asp
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rsprog/htm/rsp_prog_intro_0bs6.asp
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"John" <John@.discussions.microsoft.com> wrote in message
news:7F394F59-D1E7-4759-A6A3-E6DAC5E0CD25@.microsoft.com...
> John:
> According to the WroxBook, I need to buid an Application Interface to do
this.
> Is there a way that I can programatically do this inside of Reporting
> Services.
> My problem is with the csv export, I need to eliminate the hearders in the
> output file.
> Ravi Mumulla at Microsoft suggested the append method in the URL -- my
users
> are not computer friendly and therefore this task is not going to be easy
for
> them.
> PLEASE HELP!
> Thanks,
> john jr.
>
> "John" wrote:
> > To eliminate the merged cells I built an extract utilizing a
> > webreference to the reporting websevice and then passing the
> > deviceinfo as such in the Render call.
> >
> > If format.Extension = ".xls" Then deviceInfo => > "<DeviceInfo><SimplePageHeaders>True</SimplePageHeaders></DeviceInfo>"
> >
> > Dim data() As Byte
> > data = _rs.Render(reportItem.Path, format.Name, Nothing,
> > deviceInfo, parameters, Nothing, Nothing, _
> > encoding, mimeType, parametersUsed, warnings, streamIDs)
> >
> > It does return the excel report with no headers and no merged cells.
> > All of this is in the Wrox book - Professional SQL Server Reporting
> > Services.
> > Chapter 9 on programmatic rendering.
> >
> > --John
> >
> >
> > "Benjamin Pierce" <bpierce@.opentext.com> wrote in message
news:<OCtCh4bbEHA.2572@.tk2msftngp13.phx.gbl>...
> > > Hello,
> > >
> > > Is it possible to set a default for an export <DeviceInfo> setting.
I'd
> > > like to set some defaults for Excel, but haven't seen any
documentation on
> > > how to do this in RSReportServer.config (I would presume?).
> > >
> > > Here's what I have so far, but to no avail:
> > >
> > > <Extension Name="EXCEL"
> > >
Type="Microsoft.ReportingServices.Rendering.ExcelRenderer.ExcelRenderer,Micr
> > > osoft.ReportingServices.ExcelRendering">
> > > <DeviceInfo>
> > > <SimplePageHeaders>True</SimplePageHeaders>
> > > </DeviceInfo>
> > > </Extension>
> > >
> > > Any ideas would be greatly appreciated.
> > >
> > >
> > > Best Regards,
> > >
> > > Benjamin Pierce
> >

#N/A in measures - Excel 2003 client

After giving some users read access to the cube, by using a readonly role -
The [Measures] are displayed with #N/A in Excel 2003. Only users with loal administrator previledge can see measures.

Please advise.

What I encountered with AS 2005 was that Cell Security defaulted to denying access to a reader role, resulting in #N/A. So I simply entered "1" as the MDX expression for "allow reading of cube content" - not sure if this is the issue for you as well:

http://msdn2.microsoft.com/en-us/library/ms174847(SQL.90).aspx

>>

Granting Custom Access to Cell Data

After you grant a database role in Microsoft SQL Server 2005 Analysis Services (SSAS) read or read/write permissions to a cube, you have to grant to this database role the permission to view some or all cell data. Only granting cube access to a database role does not give that database role access to specific cube cells.

...

>>

|||Can you please give more details about when AS2005 by default disables cell security ? Creating cell security, even as trivial as constant 1 may have very bad effect on performance, since there are many important optimizations which are disabled in the presence of cell security...|||

Hi Mosha,

From what I can tell (based on a simple prototype AS 2005 RTM cube), cell read is disabled by default, unless "Full control (Administrator)" is selected on this page:

http://msdn2.microsoft.com/en-us/library/ms188508(SQL.90).aspx

>>

General (Role Designer) (SSAS)

>>

And that is regardless of "Enable read permissions" being checked on this page (unless "1" is entered for the "Allow reading of cube content" MDX expression):

http://msdn2.microsoft.com/en-us/library/ms177279(SQL.90).aspx

>>

Cell Data (Role Designer) (SSAS)

>>

Is there perhaps a better way to configure cell read access for a non-admin role?

|||

Dear ASMK,

Please check your cell security MDX, the measure you are viewing might be in the

denied member list. This might be helpfull.

#N/A in measures - Excel 2003 client

After giving some users read access to the cube, by using a readonly role -
The [Measures] are displayed with #N/A in Excel 2003. Only users with loal administrator previledge can see measures.

Please advise.

What I encountered with AS 2005 was that Cell Security defaulted to denying access to a reader role, resulting in #N/A. So I simply entered "1" as the MDX expression for "allow reading of cube content" - not sure if this is the issue for you as well:

http://msdn2.microsoft.com/en-us/library/ms174847(SQL.90).aspx

>>

Granting Custom Access to Cell Data

After you grant a database role in Microsoft SQL Server 2005 Analysis Services (SSAS) read or read/write permissions to a cube, you have to grant to this database role the permission to view some or all cell data. Only granting cube access to a database role does not give that database role access to specific cube cells.

...

>>

|||Can you please give more details about when AS2005 by default disables cell security ? Creating cell security, even as trivial as constant 1 may have very bad effect on performance, since there are many important optimizations which are disabled in the presence of cell security...|||

Hi Mosha,

From what I can tell (based on a simple prototype AS 2005 RTM cube), cell read is disabled by default, unless "Full control (Administrator)" is selected on this page:

http://msdn2.microsoft.com/en-us/library/ms188508(SQL.90).aspx

>>

General (Role Designer) (SSAS)

>>

And that is regardless of "Enable read permissions" being checked on this page (unless "1" is entered for the "Allow reading of cube content" MDX expression):

http://msdn2.microsoft.com/en-us/library/ms177279(SQL.90).aspx

>>

Cell Data (Role Designer) (SSAS)

>>

Is there perhaps a better way to configure cell read access for a non-admin role?

|||

Dear ASMK,

Please check your cell security MDX, the measure you are viewing might be in the

denied member list. This might be helpfull.

### I think its a bug ### - Export to excel

Hi:
I have a report with 2 tables in it, both have drillthroughs. I need to
toggle the visibility between the two tables based on the report parameters,
and hence have an expression in the visibility of the tables properties.
1) Loss of data when exported to excel (sublevels not exported)
The problem caused with this is that when I export to excel the
drillthroughs whihc usually get exported as levels in excel are not being
exported. I remove the visibility expression in the table it works fine.
2) When the visibilty function is present even though only one table is
visible in the report, when i export it i see both the tables. This is a
little inconvenient but fine because it was excess data. But definetly 1 is
an issue that needs to be dealt with.
For now I'll just create two separate reports, or have the second table in a
subreport.
ThanksDo you really have toggles in your report or you just conditinally hide
items based on report parameters?
Could you send us the report, please?
--
Nico Cristache [MSFT]
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"NI" <NI@.discussions.microsoft.com> wrote in message
news:3DE5068D-E7F9-41A3-8CC1-1405F20B6277@.microsoft.com...
> Hi:
> I have a report with 2 tables in it, both have drillthroughs. I need to
> toggle the visibility between the two tables based on the report
parameters,
> and hence have an expression in the visibility of the tables properties.
> 1) Loss of data when exported to excel (sublevels not exported)
> The problem caused with this is that when I export to excel the
> drillthroughs whihc usually get exported as levels in excel are not being
> exported. I remove the visibility expression in the table it works fine.
> 2) When the visibilty function is present even though only one table is
> visible in the report, when i export it i see both the tables. This is a
> little inconvenient but fine because it was excess data. But definetly 1
is
> an issue that needs to be dealt with.
> For now I'll just create two separate reports, or have the second table in
a
> subreport.
> Thanks
>
>|||Hi Nico:
I conditionally hide the 2 tables, based on a parameter. This puts in this
section of code under
<Table>
<Visibility>
<Hidden>=IIF(Parameters!RP_SELECT.Value=2,false,true)</Hidden>
</Visibility>
</Table>
If I remove that visibility section they export fine to excel. If it remains
the sub levels do not export.
Thanks
PS: Couldnt find an option to attach here, and hence I inlined only a part
of the report. Hope this helps.
--
<Table Name="table1">
<Height>1.19669in</Height>
<ZIndex>2</ZIndex>
<Style>
<BackgroundColor>White</BackgroundColor>
<BorderStyle>
<Top>None</Top>
</BorderStyle>
</Style>
<Header>
<TableRows>
<TableRow>
<Height>0.19669in</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox7">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<BackgroundColor>LightGrey</BackgroundColor>
<BorderWidth>
<Bottom>2pt</Bottom>
<Top>2pt</Top>
</BorderWidth>
<BorderColor>
<Bottom>DarkRed</Bottom>
<Top>Black</Top>
</BorderColor>
<BorderStyle>
<Bottom>Solid</Bottom>
<Top>Solid</Top>
</BorderStyle>
<TextAlign>Left</TextAlign>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
<FontWeight>700</FontWeight>
</Style>
<ZIndex>19</ZIndex>
<CanGrow>true</CanGrow>
<Value>PROJECT</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox8">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<BackgroundColor>LightGrey</BackgroundColor>
<BorderWidth>
<Bottom>2pt</Bottom>
<Top>2pt</Top>
</BorderWidth>
<BorderColor>
<Bottom>DarkRed</Bottom>
<Top>Black</Top>
</BorderColor>
<BorderStyle>
<Bottom>Solid</Bottom>
<Top>Solid</Top>
</BorderStyle>
<TextAlign>Left</TextAlign>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
<FontWeight>700</FontWeight>
</Style>
<ZIndex>18</ZIndex>
<CanGrow>true</CanGrow>
<Value>ACTIVITY HOURS</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox10">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<BackgroundColor>LightGrey</BackgroundColor>
<BorderWidth>
<Bottom>2pt</Bottom>
<Top>2pt</Top>
</BorderWidth>
<BorderColor>
<Bottom>DarkRed</Bottom>
<Top>Black</Top>
</BorderColor>
<BorderStyle>
<Bottom>Solid</Bottom>
<Top>Solid</Top>
</BorderStyle>
<TextAlign>Left</TextAlign>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
<FontWeight>700</FontWeight>
</Style>
<ZIndex>17</ZIndex>
<CanGrow>true</CanGrow>
<Value>GROUP</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox12">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<BackgroundColor>LightGrey</BackgroundColor>
<BorderWidth>
<Bottom>2pt</Bottom>
<Top>2pt</Top>
</BorderWidth>
<BorderColor>
<Bottom>DarkRed</Bottom>
<Top>Black</Top>
</BorderColor>
<BorderStyle>
<Bottom>Solid</Bottom>
<Top>Solid</Top>
</BorderStyle>
<TextAlign>Center</TextAlign>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
<FontWeight>900</FontWeight>
</Style>
<ZIndex>16</ZIndex>
<CanGrow>true</CanGrow>
<Value>TOTAL HOURS</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
<TableRow>
<Height>0.25in</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox18">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<BackgroundColor>LightCyan</BackgroundColor>
<BorderWidth>
<Bottom>2pt</Bottom>
<Top>2pt</Top>
</BorderWidth>
<BorderColor>
<Bottom>DarkRed</Bottom>
<Top>Black</Top>
</BorderColor>
<BorderStyle>
<Bottom>Solid</Bottom>
<Top>Solid</Top>
</BorderStyle>
<TextAlign>Left</TextAlign>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
<FontWeight>700</FontWeight>
</Style>
<ZIndex>15</ZIndex>
<CanGrow>true</CanGrow>
<Value>GRANDTOTAL</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox23">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<BackgroundColor>LightCyan</BackgroundColor>
<BorderWidth>
<Bottom>2pt</Bottom>
<Top>2pt</Top>
</BorderWidth>
<BorderColor>
<Bottom>DarkRed</Bottom>
<Top>Black</Top>
</BorderColor>
<BorderStyle>
<Bottom>Solid</Bottom>
<Top>Solid</Top>
</BorderStyle>
<TextAlign>Left</TextAlign>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
<FontWeight>700</FontWeight>
</Style>
<ZIndex>14</ZIndex>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox25">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<BackgroundColor>LightCyan</BackgroundColor>
<BorderWidth>
<Bottom>2pt</Bottom>
<Top>2pt</Top>
</BorderWidth>
<BorderColor>
<Bottom>DarkRed</Bottom>
<Top>Black</Top>
</BorderColor>
<BorderStyle>
<Bottom>Solid</Bottom>
<Top>Solid</Top>
</BorderStyle>
<TextAlign>Left</TextAlign>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
<FontWeight>700</FontWeight>
</Style>
<ZIndex>13</ZIndex>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox26">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<Format>N</Format>
<BackgroundColor>LightCyan</BackgroundColor>
<BorderWidth>
<Bottom>2pt</Bottom>
<Top>2pt</Top>
</BorderWidth>
<BorderColor>
<Bottom>DarkRed</Bottom>
<Top>Black</Top>
</BorderColor>
<BorderStyle>
<Bottom>Solid</Bottom>
<Top>Solid</Top>
</BorderStyle>
<TextAlign>Center</TextAlign>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
<FontWeight>900</FontWeight>
</Style>
<ZIndex>12</ZIndex>
<CanGrow>true</CanGrow>
<Value>= RunningValue(Fields!GroupHours.Value,Sum,
"GetProjectResourceHours")</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
</TableRows>
<RepeatOnNewPage>true</RepeatOnNewPage>
</Header>
<DataSetName>GetProjectResourceHours</DataSetName>
<Top>2.25in</Top>
<TableGroups>
<TableGroup>
<Header>
<TableRows>
<TableRow>
<Height>0.25in</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox28">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<BackgroundColor>=iif(RunningValue(Fields!ProjectName.Value,CountDistinct,Nothing) Mod 2, "Cornsilk", "White")</BackgroundColor>
<FontSize>8pt</FontSize>
<TextAlign>Left</TextAlign>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>11</ZIndex>
<CanGrow>true</CanGrow>
<Value>= Fields!ProjectName.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox29">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<BackgroundColor>=iif(RunningValue(Fields!ProjectName.Value,CountDistinct,Nothing) Mod 2, "Cornsilk", "White")</BackgroundColor>
<FontSize>8pt</FontSize>
<TextAlign>Left</TextAlign>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
<FontWeight>700</FontWeight>
</Style>
<ZIndex>10</ZIndex>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox30">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<BackgroundColor>=iif(RunningValue(Fields!ProjectName.Value,CountDistinct,Nothing) Mod 2, "Cornsilk", "White")</BackgroundColor>
<FontSize>8pt</FontSize>
<TextAlign>Left</TextAlign>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
<FontWeight>700</FontWeight>
</Style>
<ZIndex>9</ZIndex>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox31">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<Format>N</Format>
<BackgroundColor>=iif(RunningValue(Fields!ProjectName.Value,CountDistinct,Nothing) Mod 2, "Cornsilk", "White")</BackgroundColor>
<TextAlign>Center</TextAlign>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
<FontWeight>700</FontWeight>
</Style>
<ZIndex>8</ZIndex>
<CanGrow>true</CanGrow>
<Value>=SUM( Fields!GroupHours.Value)</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
</TableRows>
</Header>
<Grouping Name="table1_Group1">
<GroupExpressions>
<GroupExpression>=Fields!ProjectName.Value</GroupExpression>
</GroupExpressions>
</Grouping>
</TableGroup>
<TableGroup>
<Header>
<TableRows>
<TableRow>
<Height>0.25in</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox32">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<BackgroundColor>=iif(RunningValue(Fields!ActivityCode.Value,CountDistinct,Nothing) Mod 2, "LavenderBlush", "White")</BackgroundColor>
<BorderStyle>
<Bottom>None</Bottom>
</BorderStyle>
<FontSize>8pt</FontSize>
<TextAlign>Left</TextAlign>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>7</ZIndex>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox33">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<BackgroundColor>=iif(RunningValue(Fields!ActivityCode.Value,CountDistinct,Nothing) Mod 2, "LavenderBlush", "White")</BackgroundColor>
<BorderStyle>
<Bottom>None</Bottom>
</BorderStyle>
<FontSize>8pt</FontSize>
<TextAlign>Left</TextAlign>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>6</ZIndex>
<CanGrow>true</CanGrow>
<Value>= Fields!ActivityCode.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox34">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<BackgroundColor>=iif(RunningValue(Fields!ActivityCode.Value,CountDistinct,Nothing) Mod 2, "LavenderBlush", "White")</BackgroundColor>
<BorderStyle>
<Bottom>None</Bottom>
</BorderStyle>
<FontSize>8pt</FontSize>
<TextAlign>Left</TextAlign>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>5</ZIndex>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox35">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<Format>N</Format>
<BackgroundColor>=iif(RunningValue(Fields!ActivityCode.Value,CountDistinct,Nothing) Mod 2, "LavenderBlush", "White")</BackgroundColor>
<BorderStyle>
<Bottom>None</Bottom>
</BorderStyle>
<FontSize>9pt</FontSize>
<TextAlign>Center</TextAlign>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
<FontWeight>700</FontWeight>
</Style>
<ZIndex>4</ZIndex>
<CanGrow>true</CanGrow>
<Value>=SUM(Fields!GroupHours.Value)</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
</TableRows>
</Header>
<Grouping Name="table1_Group2">
<GroupExpressions>
<GroupExpression>=Fields!ActivityCode.Value</GroupExpression>
</GroupExpressions>
</Grouping>
<Visibility>
<ToggleItem>textbox28</ToggleItem>
<Hidden>true</Hidden>
</Visibility>
</TableGroup>
<TableGroup>
<Header>
<TableRows>
<TableRow>
<Height>0.25in</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox36">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<BackgroundColor>=iif(RunningValue(Fields!GroupName.Value,CountDistinct,Nothing) Mod 2, "LightYellow", "White")</BackgroundColor>
<TextAlign>Left</TextAlign>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>3</ZIndex>
<rd:DefaultName>textbox36</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox37">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<BackgroundColor>=iif(RunningValue(Fields!GroupName.Value,CountDistinct,Nothing) Mod 2, "LightYellow", "White")</BackgroundColor>
<TextAlign>Left</TextAlign>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>2</ZIndex>
<rd:DefaultName>textbox37</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox38">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<BackgroundColor>=iif(RunningValue(Fields!GroupName.Value,CountDistinct,Nothing) Mod 2, "LightYellow", "White")</BackgroundColor>
<FontSize>8pt</FontSize>
<TextAlign>Left</TextAlign>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>1</ZIndex>
<rd:DefaultName>textbox38</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>= Fields!GroupName.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox39">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<Format>N</Format>
<BackgroundColor>=iif(RunningValue(Fields!GroupName.Value,CountDistinct,Nothing) Mod 2, "LightYellow", "White")</BackgroundColor>
<FontSize>8pt</FontSize>
<TextAlign>Center</TextAlign>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<rd:DefaultName>textbox39</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=(Fields!GroupHours.Value)</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
</TableRows>
</Header>
<Grouping Name="table1_Group4">
<GroupExpressions>
<GroupExpression>=Fields!GroupName.Value</GroupExpression>
</GroupExpressions>
</Grouping>
<Visibility>
<ToggleItem>textbox33</ToggleItem>
<Hidden>true</Hidden>
</Visibility>
</TableGroup>
<TableGroup>
<Grouping Name="table1_Group3">
<GroupExpressions>
<GroupExpression>=Fields!ActivityCode.Value</GroupExpression>
</GroupExpressions>
</Grouping>
<Visibility>
<ToggleItem>textbox33</ToggleItem>
<Hidden>true</Hidden>
</Visibility>
</TableGroup>
</TableGroups>
<Visibility>
<Hidden>=IIF(Parameters!RP_SELECT.Value=2,false,true)</Hidden>
</Visibility>
<TableColumns>
<TableColumn>
<Width>2.625in</Width>
</TableColumn>
<TableColumn>
<Width>1.875in</Width>
</TableColumn>
<TableColumn>
<Width>2.5in</Width>
</TableColumn>
<TableColumn>
<Width>1.75in</Width>
</TableColumn>
</TableColumns>
</Table>
--
"Nico Cristache [MSFT]" wrote:
> Do you really have toggles in your report or you just conditinally hide
> items based on report parameters?
> Could you send us the report, please?
> --
> Nico Cristache [MSFT]
> Microsoft SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "NI" <NI@.discussions.microsoft.com> wrote in message
> news:3DE5068D-E7F9-41A3-8CC1-1405F20B6277@.microsoft.com...
> > Hi:
> >
> > I have a report with 2 tables in it, both have drillthroughs. I need to
> > toggle the visibility between the two tables based on the report
> parameters,
> > and hence have an expression in the visibility of the tables properties.
> >
> > 1) Loss of data when exported to excel (sublevels not exported)
> > The problem caused with this is that when I export to excel the
> > drillthroughs whihc usually get exported as levels in excel are not being
> > exported. I remove the visibility expression in the table it works fine.
> >
> > 2) When the visibilty function is present even though only one table is
> > visible in the report, when i export it i see both the tables. This is a
> > little inconvenient but fine because it was excess data. But definetly 1
> is
> > an issue that needs to be dealt with.
> >
> > For now I'll just create two separate reports, or have the second table in
> a
> > subreport.
> >
> > Thanks
> >
> >
> >
>
>|||Will this be fixed in SP2? I have the same problem with toggeling visibility
on 2 tables and exporting to excel. I have tryed to install the latest SP2
beta but it didn't seem to fix the problem.
"NI" wrote:
> Hi Nico:
> I conditionally hide the 2 tables, based on a parameter. This puts in this
> section of code under
> <Table>
> <Visibility>
> <Hidden>=IIF(Parameters!RP_SELECT.Value=2,false,true)</Hidden>
> </Visibility>
> </Table>
> If I remove that visibility section they export fine to excel. If it remains
> the sub levels do not export.
> Thanks
> PS: Couldnt find an option to attach here, and hence I inlined only a part
> of the report. Hope this helps.
> -- Had to remove the report becouse my post was too long... --
>
> "Nico Cristache [MSFT]" wrote:
> > Do you really have toggles in your report or you just conditinally hide
> > items based on report parameters?
> > Could you send us the report, please?
> >
> > --
> > Nico Cristache [MSFT]
> > Microsoft SQL Server Reporting Services
> >
> > This posting is provided "AS IS" with no warranties, and confers no rights.
> >
> >
> > "NI" <NI@.discussions.microsoft.com> wrote in message
> > news:3DE5068D-E7F9-41A3-8CC1-1405F20B6277@.microsoft.com...
> > > Hi:
> > >
> > > I have a report with 2 tables in it, both have drillthroughs. I need to
> > > toggle the visibility between the two tables based on the report
> > parameters,
> > > and hence have an expression in the visibility of the tables properties.
> > >
> > > 1) Loss of data when exported to excel (sublevels not exported)
> > > The problem caused with this is that when I export to excel the
> > > drillthroughs whihc usually get exported as levels in excel are not being
> > > exported. I remove the visibility expression in the table it works fine.
> > >
> > > 2) When the visibilty function is present even though only one table is
> > > visible in the report, when i export it i see both the tables. This is a
> > > little inconvenient but fine because it was excess data. But definetly 1
> > is
> > > an issue that needs to be dealt with.
> > >
> > > For now I'll just create two separate reports, or have the second table in
> > a
> > > subreport.
> > >
> > > Thanks
> > >
> > >
> > >
> >
> >
> >