When I export to a file, say excel, I want certain textboxes to not show up.
How can I hide them on export?
bradSorry, this is not possible. You can only hide based on e.g. report
parameters.
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"bradtm" <bradtm@.discussions.microsoft.com> wrote in message
news:9145ACFB-54E5-4BFC-82B4-0368B7AFA0FE@.microsoft.com...
> When I export to a file, say excel, I want certain textboxes to not show
> up.
> How can I hide them on export?
> brad|||Don't forget, though, if you use your own "export" button through a
custom interface, setting the rs:FORMAT=PDF or similar, you can set a
report parameter that can be referenced to show or hide selected fields.
Showing posts with label excel. Show all posts
Showing posts with label excel. Show all posts
Friday, March 30, 2012
Wednesday, March 28, 2012
Hide dimension members which have no rows in fact table
When a cube is presented to the end user (thru Excel or Reporting services), I would like to limit the available dimension members to only those which have at least one row in fact table. This is to simplify what user sees as a list of choices under a dimension attribute/hierarchy and not be overwhelmed with all the dimension members for which no data may exist in reality in the fact table. Does Analysis Services 2005 have any easy way to accomplish this as part of UDM/Cube design?
Let me give you an example. Take "Locations" role playing dimension. In ocean transportation business, "Locations" are of different types such as Port of Load (sea-port), Port of Discharge (sea-port), Inland Point of origin, Inland Point of destination etc. Sea-ports are only few (5 to 10) where as inland locations are in thousands. The entire list of locations, which includes locations which may not have had any shipment booked till date, may be even bigger. To keep the ETL simple, we want to keep one central dimension table for all location roles. However, for cube query selection purposes, users want to see a simple and compact list of Port of Load locations for which bookings have been made (i.e., data exists in fact table with those Port Of Load locations) - for example.
One possible solution is... to use one view for each location role in the UDM and setup relationships from fact table column to these individual location views, where the view joins Location dimension table with the fact table to select only those locations that exist in the fact table. Is this a good approach? Will there be any performance impact in Cube processing or end user cube query performance because of using views like this (since view is run every time it is referenced on the fly to generate the result set)?
Another solution is... to have separate dimension tables for each location role with only those members that are used in fact table records. But this complicates the ETL due to lot of redundancy. Will it give any performance advantage since it uses a permanent table versus view in UDM (data source view)?
Please share your insights on the best ways of handling this problem in SQL Server 2005. Thank you!
Hello. The standard behaviour you are looking for is the standard behaviour in a TSQL inner join. So if you make a report in SSRS2005 and use TSQL with your data mart / data warehouse as a source, you will only see connected records in the dimensions and the fact table.
A cube is more like a crossjoin in TSQL. If you crossjoin two tables in TSQL you will get all combinations of members in the tables. This is standard behaviour in SSAS2005.
But within a dimension table in SSAS2005 you have a new feature, only within the dimension, called autoexist, that will only show combinations of members like a TSQL inner join of members. Within a dimension, in the previous version of SSAS (2000), you would get a TSQL-crossjoin of all members within a dimension.
If you combine sea-ports and inland destinations, in the same dimension(in SSAS2005), you will get an inner join of dimension member combinations(autoexists). If you put them as separate dimensions you will miss autoexist.
To reduce empty cells in the grid(in a SSAS2005 cube) you will have to add NonEmpty or Non Empty in MDX in order to not show empty cells. Many clients can help help you with this by having a button to hide empty rows and columns in the grid.
There are many more superior MDX experts, on this forum, that can explain this even further.
HTH
Thomas Ivarsson
|||Hello. Auto exist does not have anything to do with data in the fact table. Auto exist is a dimensional concept only. Auto-exist only pertains to the attributes in the same dimension. If we separate the datasources (either using view or permanent table) for each location role dimension, then with in any single location role dimension, "auto exist" is still effective across the attributes of that dimension. My problem has to do with not wanting to see the dimension members if they (dimension key) are not present in the fact table data. Thanks for the reply. Appreciate it.
|||I have tried to tell you about the behavior of Autoexist if you read my post carefully. You have also an explanation for the dimension and fact problem.
Autoexist is not new to me either.
Regards
Thomas Ivarsson
|||
Shan,
If I understand your requirement correctly, you have one Locations dimension table, but you use that single dimension within your UDM in different roles. In each of its roles, you want the dimension to have a different set of members based on whether or not the members exist within a related fact table or not. Is that correct?
If that is the case, then I believe your best option is to create different views (or named queries in the DSV) on top fo the Locations dimension table that handle the inner join to each related fact table. Then, in the UDM, set up separate dimensions on top of each view and relate each dimension to each fact table/measure group as appropriate.
There will be some performance implications to doing this. For example, you'll be running similar (or even the exact same) queries agains the Locations dimension table as you process each of the separate dimensions in the UDM. So, if you have a lot of them and/or if the joins to the fact tables within the views or named queries are expensive, you'll be adding processing time. Once processing is complete, however, I don't think query performance against any given measure group within the UDM will be any different than if you had a single dimension that was being used as a role-playing dimenision within the cube.
HTH,
Dave Fackler
|||Dave,
That's correct summary of my requirement. We will proceed with this recommended solution. Thank you. I was not sure whether Analysis Services has some built-in support to meet this common need which does not require separating data sources (view or separate table) since that approach takes some additional work! Trying to find an easier or better way since this question applies to many dimension tables in our data warehouse (both simple and role playing dimensions)... :-) Now I understand that there is no such easy way (out of the box support).
If I am right, the added performance time you mentioned impacts the cube processing step time during ETL job run. That's okay.
sqlHide dimension members which have no rows in fact table
When a cube is presented to the end user (thru Excel or Reporting services), I would like to limit the available dimension members to only those which have at least one row in fact table. This is to simplify what user sees as a list of choices under a dimension attribute/hierarchy and not be overwhelmed with all the dimension members for which no data may exist in reality in the fact table. Does Analysis Services 2005 have any easy way to accomplish this as part of UDM/Cube design?
Let me give you an example. Take "Locations" role playing dimension. In ocean transportation business, "Locations" are of different types such as Port of Load (sea-port), Port of Discharge (sea-port), Inland Point of origin, Inland Point of destination etc. Sea-ports are only few (5 to 10) where as inland locations are in thousands. The entire list of locations, which includes locations which may not have had any shipment booked till date, may be even bigger. To keep the ETL simple, we want to keep one central dimension table for all location roles. However, for cube query selection purposes, users want to see a simple and compact list of Port of Load locations for which bookings have been made (i.e., data exists in fact table with those Port Of Load locations) - for example.
One possible solution is... to use one view for each location role in the UDM and setup relationships from fact table column to these individual location views, where the view joins Location dimension table with the fact table to select only those locations that exist in the fact table. Is this a good approach? Will there be any performance impact in Cube processing or end user cube query performance because of using views like this (since view is run every time it is referenced on the fly to generate the result set)?
Another solution is... to have separate dimension tables for each location role with only those members that are used in fact table records. But this complicates the ETL due to lot of redundancy. Will it give any performance advantage since it uses a permanent table versus view in UDM (data source view)?
Please share your insights on the best ways of handling this problem in SQL Server 2005. Thank you!
Hello. The standard behaviour you are looking for is the standard behaviour in a TSQL inner join. So if you make a report in SSRS2005 and use TSQL with your data mart / data warehouse as a source, you will only see connected records in the dimensions and the fact table.
A cube is more like a crossjoin in TSQL. If you crossjoin two tables in TSQL you will get all combinations of members in the tables. This is standard behaviour in SSAS2005.
But within a dimension table in SSAS2005 you have a new feature, only within the dimension, called autoexist, that will only show combinations of members like a TSQL inner join of members. Within a dimension, in the previous version of SSAS (2000), you would get a TSQL-crossjoin of all members within a dimension.
If you combine sea-ports and inland destinations, in the same dimension(in SSAS2005), you will get an inner join of dimension member combinations(autoexists). If you put them as separate dimensions you will miss autoexist.
To reduce empty cells in the grid(in a SSAS2005 cube) you will have to add NonEmpty or Non Empty in MDX in order to not show empty cells. Many clients can help help you with this by having a button to hide empty rows and columns in the grid.
There are many more superior MDX experts, on this forum, that can explain this even further.
HTH
Thomas Ivarsson
|||Hello. Auto exist does not have anything to do with data in the fact table. Auto exist is a dimensional concept only. Auto-exist only pertains to the attributes in the same dimension. If we separate the datasources (either using view or permanent table) for each location role dimension, then with in any single location role dimension, "auto exist" is still effective across the attributes of that dimension. My problem has to do with not wanting to see the dimension members if they (dimension key) are not present in the fact table data. Thanks for the reply. Appreciate it.
|||I have tried to tell you about the behavior of Autoexist if you read my post carefully. You have also an explanation for the dimension and fact problem.
Autoexist is not new to me either.
Regards
Thomas Ivarsson
|||
Shan,
If I understand your requirement correctly, you have one Locations dimension table, but you use that single dimension within your UDM in different roles. In each of its roles, you want the dimension to have a different set of members based on whether or not the members exist within a related fact table or not. Is that correct?
If that is the case, then I believe your best option is to create different views (or named queries in the DSV) on top fo the Locations dimension table that handle the inner join to each related fact table. Then, in the UDM, set up separate dimensions on top of each view and relate each dimension to each fact table/measure group as appropriate.
There will be some performance implications to doing this. For example, you'll be running similar (or even the exact same) queries agains the Locations dimension table as you process each of the separate dimensions in the UDM. So, if you have a lot of them and/or if the joins to the fact tables within the views or named queries are expensive, you'll be adding processing time. Once processing is complete, however, I don't think query performance against any given measure group within the UDM will be any different than if you had a single dimension that was being used as a role-playing dimenision within the cube.
HTH,
Dave Fackler
|||Dave,
That's correct summary of my requirement. We will proceed with this recommended solution. Thank you. I was not sure whether Analysis Services has some built-in support to meet this common need which does not require separating data sources (view or separate table) since that approach takes some additional work! Trying to find an easier or better way since this question applies to many dimension tables in our data warehouse (both simple and role playing dimensions)... :-) Now I understand that there is no such easy way (out of the box support).
If I am right, the added performance time you mentioned impacts the cube processing step time during ETL job run. That's okay.
Monday, March 26, 2012
Hidding rows disappearing in Excel export
I have a report that looks fine in Report Server, but when I export to
Excel, I lose data.
I have a group that I am repeating the group footer 3 times. The
original footer is the toggle. when I hit the + in Rep Services, it
expands with no problem, showing all of my hidden rows.
when I export it to excel and hit the toggle, it only shows the first
hidden row and loses the other two.
Any ideas?What version of Report Server and Office do you have?
daw
"M@." wrote:
> I have a report that looks fine in Report Server, but when I export to
> Excel, I lose data.
> I have a group that I am repeating the group footer 3 times. The
> original footer is the toggle. when I hit the + in Rep Services, it
> expands with no problem, showing all of my hidden rows.
> when I export it to excel and hit the toggle, it only shows the first
> hidden row and loses the other two.
> Any ideas?
>|||Microsoft SQL Server Reporting Services Version 8.00.1038.00
Excel 2003
thx,
M@.
On Mar 21, 3:52 pm, daw <d...@.discussions.microsoft.com> wrote:
> What version of Report Server and Office do you have?
> daw
> "M@." wrote:
> > I have a report that looks fine in Report Server, but when I export to
> > Excel, I lose data.
> > I have a group that I am repeating the group footer 3 times. The
> > original footer is the toggle. when I hit the + in Rep Services, it
> > expands with no problem, showing all of my hidden rows.
> > when I export it to excel and hit the toggle, it only shows the first
> > hidden row and loses the other two.
> > Any ideas?|||Are you sure that the rows are not just hidden rows in Excel? Are the row
numbers consecutive?
"M@." wrote:
> Microsoft SQL Server Reporting Services Version 8.00.1038.00
> Excel 2003
> thx,
> M@.
> On Mar 21, 3:52 pm, daw <d...@.discussions.microsoft.com> wrote:
> > What version of Report Server and Office do you have?
> >
> > daw
> >
> > "M@." wrote:
> > > I have a report that looks fine in Report Server, but when I export to
> > > Excel, I lose data.
> >
> > > I have a group that I am repeating the group footer 3 times. The
> > > original footer is the toggle. when I hit the + in Rep Services, it
> > > expands with no problem, showing all of my hidden rows.
> >
> > > when I export it to excel and hit the toggle, it only shows the first
> > > hidden row and loses the other two.
> >
> > > Any ideas?
>
>|||I have a group footer (group1) that I repeat. The first line is the
toggle, lines 2-5 are details. I'm using running values. Row 1 is
the sum. Row2 is a sum when a field = PRV, Row3 is a sum when a field
= MCD and so on.
It looks fine in Reporting Services, but if I export it to excel and
hit the toggle, only the first hidden row pops in.
Yeah, the row numbers are consecutive.
On Mar 21, 4:32 pm, daw <d...@.discussions.microsoft.com> wrote:
> Are you sure that the rows are not just hidden rows in Excel? Are the row
> numbers consecutive?
>
> "M@." wrote:
> > Microsoft SQL Server Reporting Services Version 8.00.1038.00
> > Excel 2003
> > thx,
> > M@.
> > On Mar 21, 3:52 pm, daw <d...@.discussions.microsoft.com> wrote:
> > > What version of Report Server and Office do you have?
> > > daw
> > > "M@." wrote:
> > > > I have a report that looks fine in Report Server, but when I export to
> > > > Excel, I lose data.
> > > > I have a group that I am repeating the group footer 3 times. The
> > > > original footer is the toggle. when I hit the + in Rep Services, it
> > > > expands with no problem, showing all of my hidden rows.
> > > > when I export it to excel and hit the toggle, it only shows the first
> > > > hidden row and loses the other two.
> > > > Any ideas... Hide quoted text -
> - Show quoted text -
Excel, I lose data.
I have a group that I am repeating the group footer 3 times. The
original footer is the toggle. when I hit the + in Rep Services, it
expands with no problem, showing all of my hidden rows.
when I export it to excel and hit the toggle, it only shows the first
hidden row and loses the other two.
Any ideas?What version of Report Server and Office do you have?
daw
"M@." wrote:
> I have a report that looks fine in Report Server, but when I export to
> Excel, I lose data.
> I have a group that I am repeating the group footer 3 times. The
> original footer is the toggle. when I hit the + in Rep Services, it
> expands with no problem, showing all of my hidden rows.
> when I export it to excel and hit the toggle, it only shows the first
> hidden row and loses the other two.
> Any ideas?
>|||Microsoft SQL Server Reporting Services Version 8.00.1038.00
Excel 2003
thx,
M@.
On Mar 21, 3:52 pm, daw <d...@.discussions.microsoft.com> wrote:
> What version of Report Server and Office do you have?
> daw
> "M@." wrote:
> > I have a report that looks fine in Report Server, but when I export to
> > Excel, I lose data.
> > I have a group that I am repeating the group footer 3 times. The
> > original footer is the toggle. when I hit the + in Rep Services, it
> > expands with no problem, showing all of my hidden rows.
> > when I export it to excel and hit the toggle, it only shows the first
> > hidden row and loses the other two.
> > Any ideas?|||Are you sure that the rows are not just hidden rows in Excel? Are the row
numbers consecutive?
"M@." wrote:
> Microsoft SQL Server Reporting Services Version 8.00.1038.00
> Excel 2003
> thx,
> M@.
> On Mar 21, 3:52 pm, daw <d...@.discussions.microsoft.com> wrote:
> > What version of Report Server and Office do you have?
> >
> > daw
> >
> > "M@." wrote:
> > > I have a report that looks fine in Report Server, but when I export to
> > > Excel, I lose data.
> >
> > > I have a group that I am repeating the group footer 3 times. The
> > > original footer is the toggle. when I hit the + in Rep Services, it
> > > expands with no problem, showing all of my hidden rows.
> >
> > > when I export it to excel and hit the toggle, it only shows the first
> > > hidden row and loses the other two.
> >
> > > Any ideas?
>
>|||I have a group footer (group1) that I repeat. The first line is the
toggle, lines 2-5 are details. I'm using running values. Row 1 is
the sum. Row2 is a sum when a field = PRV, Row3 is a sum when a field
= MCD and so on.
It looks fine in Reporting Services, but if I export it to excel and
hit the toggle, only the first hidden row pops in.
Yeah, the row numbers are consecutive.
On Mar 21, 4:32 pm, daw <d...@.discussions.microsoft.com> wrote:
> Are you sure that the rows are not just hidden rows in Excel? Are the row
> numbers consecutive?
>
> "M@." wrote:
> > Microsoft SQL Server Reporting Services Version 8.00.1038.00
> > Excel 2003
> > thx,
> > M@.
> > On Mar 21, 3:52 pm, daw <d...@.discussions.microsoft.com> wrote:
> > > What version of Report Server and Office do you have?
> > > daw
> > > "M@." wrote:
> > > > I have a report that looks fine in Report Server, but when I export to
> > > > Excel, I lose data.
> > > > I have a group that I am repeating the group footer 3 times. The
> > > > original footer is the toggle. when I hit the + in Rep Services, it
> > > > expands with no problem, showing all of my hidden rows.
> > > > when I export it to excel and hit the toggle, it only shows the first
> > > > hidden row and loses the other two.
> > > > Any ideas... Hide quoted text -
> - Show quoted text -
Friday, March 23, 2012
Hidden member causes Excel to throw error
Hi,
I have an AS2000 utility dimension that hold one calculated member and one 'dummy' member for the fact - dimension join. I use it in a virtual cube to achive currency conversion and I implemented the utility dimension to be able to use it with the aggregrate function that Excel uses on multiple filter selections.
I want to prevent this dummy member to appear in my Excel pivot table. I have tried to use the setting 'Hide member if -No name' and it works great when I browse my cube in ASManager but when I browse it Excel 2007 I get a popup message saying something like 'member could not be found...'
Any idea?
Thanks,Christer
It has been some time since I have worked with AS2000 but I think there is a "Visible" property that you can set on your dummy member.
Another way can be to remove the parent for the calculated member in you shell dimension. Only use the dimension without parent member.
HTH
Thomas Ivarsson
|||Another thing to try is to change the default member for this dimension to point to the calculated member.Hidden Columns Problem in Excel
Hi there,
Been so annoyed to get rid of all the hidden columns after exporting my report to Excel which when I merge and centre then unhide, shows blank columns. The hidden column could appear in the middle of report or at the end of each report.
Known from somewhere that this might cause by the below mentioned facts and i've got some of the solutions with me but may i know is there a guideline to follow when designing a report for Excel and meanwhile get rid of hidden column's problem ?
i. The width of each and every tables are not aligned when there are more than 2 tables presented in a page vertically (each one on top of another) and therefore, gotta make sure the location from the left (start location) for all tables are the same, and the total width of the table are equal to the sum of width of each column, and also the length of every single column in all tables is align
--SOMEHOW, this is not enough to fix the problem and so i have to try steps ii (as follows).
ii. The text width in a field is greater than the width of the field. (Need to keep on trying for various width until the hidden column gone) e.g. i had allocate 2cm for a 'int' type field and apparently, i could see from preview the value just occupied bout half of the field. However, i need to expand the field to 3cm at last to accomodate the value or hidden column may appear when i export to Excel.
--SO, is there a way to know how much space a field needs and is data type affect the column width needed?
iii. Matrixes' width is greater than page width after expanding (This is hard to measure because we do not know how many columns a matrix is going to expand), and the best i could do is to set it less than or equal to the lenght of (page width - left margin -right margin) but somehow the hidden column still appear
-- ANY better solution to deal with this?
Any comment is welcome and thank you in advance!
Cheers,
Julez
Here is some information about merged cells with the Excel renderer:
http://blogs.msdn.com/chrisbal/archive/2006/07/08/659545.aspx
-Chris
|||
Hi Chris,
Thank you so much! This is really helpful!! =)
Cheers,
Julez
hidden cell in excel
Hello,
Can someone please tell me why there's ALWAYS a hidden cell in Excel when a report has a more than one page?
for example, a report has 10 page breaks and after exported to Excel it has the first row hidden in each sheet except the first sheet.
Why this happens always? is there any workaround to avoid this?
thanks
I also have the same questions as the original poster.
How does one get rid of the hidden row in an exported Excel spreadsheet when there are page breaks in the report?
Thanks in advance.
|||Hey Guys.
Can you clarify this a little more? I have exported my reports to excel but I get one long continuous sheet even when my report has multiple pages.
I have notice however that excel does include your report page margins, So I'm taking a guess that this could be what you're seeing in your export.
Ham
|||Hey Ham,
Yes, one long continuous sheet in Excel, but the page breaks are there in the report definition for viewing via the Report Manager web site. We (independently, the OP and I) think that somehow the page breaks are influencing the "hidden row" phenomenon.
The hidden row presents itself in the exported Excel spreadsheet on "Sheet 2", where "Sheet 1" was the parameter detail information. If one inspects Sheet 2 (which is the actual data produced by the report), one would see that row 1 is hidden (perhaps a better word is collapsed; one can expand it by double clicking on the row separator in the left margin-ish area), row 2 contains the header row/column headings, then row 3 and beyond contain the actual data.
Our report users don't like that hidden row 1. I know that it would be easy to expand and then remove it in Excel but....you know users, they don't want it there from the get-go.
Thanks for any advice.
John
|||Hi,
Can it be that you are using Document Maps in your report? It might be that it are document maps that you see.
Greetz,
Geert
Geert Verhoeven
Consultant @. Ausy Belgium
My Personal Blog
|||Hi Geert,
Although I didn't build the reports myself, I spoke with the author, and inspected the layout view (and RDL), and we are NOT using Document Maps.
Might there be another reason for the "hidden row" issue with exported Excel reports?
John
|||Hi John,
It is hard to say like that. Can you please mail or post the contents of the RDL file so I can take a look at it.
Greetz,
Geert
Geert Verhoeven
Consultant @. Ausy Belgium
My Personal Blog
|||Here is the RDL. I took the majority of lines associated with a BMP image. Thanks for taking a look.
<?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="dsKnetch">
<DataSourceReference>dsKnetch</DataSourceReference>
<rd:DataSourceID>42d0feab-c2ad-4306-b39a-0993cc80c5f8</rd:DataSourceID>
</DataSource>
</DataSources>
<BottomMargin>0.5in</BottomMargin>
<RightMargin>0.5in</RightMargin>
<PageWidth>11in</PageWidth>
<ReportParameters>
<ReportParameter Name="StartDtRange">
<DataType>DateTime</DataType>
<Prompt>Start of Date Range</Prompt>
</ReportParameter>
<ReportParameter Name="EndDtRange">
<DataType>DateTime</DataType>
<Prompt>End of Date Range</Prompt>
</ReportParameter>
<ReportParameter Name="Title">
<DataType>String</DataType>
<Prompt>Title Starts With</Prompt>
</ReportParameter>
</ReportParameters>
<rd:DrawGrid>true</rd:DrawGrid>
<InteractiveWidth>8.5in</InteractiveWidth>
<rd:SnapToGrid>true</rd:SnapToGrid>
<Body>
<ReportItems>
<Image Name="image1">
<Sizing>FitProportional</Sizing>
<ZIndex>5</ZIndex>
<Source>Embedded</Source>
<Style />
<Height>0.375in</Height>
<Value>knetch</Value>
</Image>
<Textbox Name="textbox38">
<Top>0.75in</Top>
<rd:DefaultName>textbox38</rd:DefaultName>
<ZIndex>4</ZIndex>
<Style>
<TextAlign>Center</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<FontFamily>Times New Roman</FontFamily>
<FontWeight>700</FontWeight>
<FontSize>12pt</FontSize>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Height>0.25in</Height>
<Value>Knetch - Crosscheck Report (by Title & Date)</Value>
</Textbox>
<Textbox Name="textbox37">
<Top>0.375in</Top>
<rd:DefaultName>textbox37</rd:DefaultName>
<ZIndex>3</ZIndex>
<Style>
<TextAlign>Center</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<FontFamily>Times New Roman</FontFamily>
<FontWeight>700</FontWeight>
<FontSize>12pt</FontSize>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Height>0.25in</Height>
<Value>Company</Value>
</Textbox>
<Rectangle Name="rectangle1">
<ReportItems>
<Textbox Name="textbox55">
<Left>1.5in</Left>
<Top>0.125in</Top>
<rd:DefaultName>textbox55</rd:DefaultName>
<ZIndex>3</ZIndex>
<Width>8.375in</Width>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<FontFamily>Times New Roman</FontFamily>
<FontSize>9pt</FontSize>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Height>0.2in</Height>
<Value>=Parameters!Title.Value</Value>
</Textbox>
<Textbox Name="textbox28">
<Left>0.125in</Left>
<Top>0.125in</Top>
<ZIndex>2</ZIndex>
<Width>1.25in</Width>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<FontFamily>Times New Roman</FontFamily>
<FontSize>9pt</FontSize>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Height>0.2in</Height>
<Value>Title Starts With:</Value>
</Textbox>
<Textbox Name="textbox2">
<Left>0.125in</Left>
<Top>0.375in</Top>
<ZIndex>1</ZIndex>
<Width>1.25in</Width>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<FontFamily>Times New Roman</FontFamily>
<FontSize>9pt</FontSize>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Height>0.2in</Height>
<Value>Class(es) Date Range:</Value>
</Textbox>
<Textbox Name="textbox3">
<Left>1.5in</Left>
<Top>0.375in</Top>
<Width>8.375in</Width>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<FontFamily>Times New Roman</FontFamily>
<FontSize>9pt</FontSize>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Height>0.2in</Height>
<Value>=Parameters!StartDtRange.Value & " to " & Parameters!EndDtRange.Value</Value>
</Textbox>
</ReportItems>
<Top>1.375in</Top>
<ZIndex>2</ZIndex>
<PageBreakAtEnd>true</PageBreakAtEnd>
<Height>0.625in</Height>
</Rectangle>
<Textbox Name="textbox54">
<Top>1.125in</Top>
<ZIndex>1</ZIndex>
<Width>2.75in</Width>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<FontFamily>Times New Roman</FontFamily>
<FontWeight>700</FontWeight>
<FontSize>9pt</FontSize>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Height>0.2in</Height>
<Value>Self Service Filter Options:</Value>
</Textbox>
<Table Name="table1">
<DataSetName>dsDisvantageCrosscheck</DataSetName>
<Top>2.125in</Top>
<Width>9.925in</Width>
<Details>
<TableRows>
<TableRow>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox4">
<rd:DefaultName>textbox4</rd:DefaultName>
<ZIndex>5</ZIndex>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<FontFamily>Times New Roman</FontFamily>
<FontSize>9pt</FontSize>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Fields!username.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox5">
<rd:DefaultName>textbox5</rd:DefaultName>
<ZIndex>4</ZIndex>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<FontFamily>Times New Roman</FontFamily>
<FontSize>9pt</FontSize>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Fields!employee_name.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox6">
<rd:DefaultName>textbox6</rd:DefaultName>
<ZIndex>3</ZIndex>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<FontFamily>Times New Roman</FontFamily>
<FontSize>9pt</FontSize>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Fields!office_name.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox8">
<rd:DefaultName>textbox8</rd:DefaultName>
<ZIndex>2</ZIndex>
<Style>
<TextAlign>Center</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<FontFamily>Times New Roman</FontFamily>
<FontSize>9pt</FontSize>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Fields!class_no.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox12">
<rd:DefaultName>textbox12</rd:DefaultName>
<ZIndex>1</ZIndex>
<Style>
<TextAlign>Center</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<FontFamily>Times New Roman</FontFamily>
<FontSize>9pt</FontSize>
<Format>MM/dd/yy</Format>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Fields!start_date.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox10">
<rd:DefaultName>textbox10</rd:DefaultName>
<Style>
<TextAlign>Center</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<FontFamily>Times New Roman</FontFamily>
<FontSize>9pt</FontSize>
<Format>MM/dd/yy</Format>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Fields!end_date.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
<Height>0.2in</Height>
</TableRow>
</TableRows>
<Grouping Name="table1_Details_Group">
<GroupExpressions>
<GroupExpression>=Fields!username.Value</GroupExpression>
</GroupExpressions>
</Grouping>
<Sorting>
<SortBy>
<SortExpression>=Fields!employee_name.Value</SortExpression>
<Direction>Ascending</Direction>
</SortBy>
<SortBy>
<SortExpression>=Fields!start_date.Value</SortExpression>
<Direction>Ascending</Direction>
</SortBy>
<SortBy>
<SortExpression>=Fields!end_date.Value</SortExpression>
<Direction>Ascending</Direction>
</SortBy>
</Sorting>
</Details>
<Header>
<TableRows>
<TableRow>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox29">
<ZIndex>11</ZIndex>
<Style>
<BorderStyle>
<Bottom>Solid</Bottom>
</BorderStyle>
<PaddingLeft>2pt</PaddingLeft>
<BorderWidth>
<Bottom>1pt</Bottom>
</BorderWidth>
<PaddingBottom>2pt</PaddingBottom>
<FontFamily>Times New Roman</FontFamily>
<FontWeight>700</FontWeight>
<FontSize>9pt</FontSize>
<BorderColor>
<Bottom>Black</Bottom>
</BorderColor>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>Username</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox30">
<ZIndex>10</ZIndex>
<Style>
<BorderStyle>
<Bottom>Solid</Bottom>
</BorderStyle>
<PaddingLeft>2pt</PaddingLeft>
<BorderWidth>
<Bottom>1pt</Bottom>
</BorderWidth>
<PaddingBottom>2pt</PaddingBottom>
<FontFamily>Times New Roman</FontFamily>
<FontWeight>700</FontWeight>
<FontSize>9pt</FontSize>
<BorderColor>
<Bottom>Black</Bottom>
</BorderColor>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>Participant Name</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox31">
<ZIndex>9</ZIndex>
<Style>
<BorderStyle>
<Bottom>Solid</Bottom>
</BorderStyle>
<PaddingLeft>2pt</PaddingLeft>
<BorderWidth>
<Bottom>1pt</Bottom>
</BorderWidth>
<PaddingBottom>2pt</PaddingBottom>
<FontFamily>Times New Roman</FontFamily>
<FontWeight>700</FontWeight>
<FontSize>9pt</FontSize>
<BorderColor>
<Bottom>Black</Bottom>
</BorderColor>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>Office</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox32">
<ZIndex>8</ZIndex>
<Style>
<BorderStyle>
<Bottom>Solid</Bottom>
</BorderStyle>
<TextAlign>Center</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<BorderWidth>
<Bottom>1pt</Bottom>
</BorderWidth>
<PaddingBottom>2pt</PaddingBottom>
<FontFamily>Times New Roman</FontFamily>
<FontWeight>700</FontWeight>
<FontSize>9pt</FontSize>
<BorderColor>
<Bottom>Black</Bottom>
</BorderColor>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>Class Number</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox33">
<ZIndex>7</ZIndex>
<Style>
<BorderStyle>
<Bottom>Solid</Bottom>
</BorderStyle>
<TextAlign>Center</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<BorderWidth>
<Bottom>1pt</Bottom>
</BorderWidth>
<PaddingBottom>2pt</PaddingBottom>
<FontFamily>Times New Roman</FontFamily>
<FontWeight>700</FontWeight>
<FontSize>9pt</FontSize>
<BorderColor>
<Bottom>Black</Bottom>
</BorderColor>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>Start Date</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox34">
<ZIndex>6</ZIndex>
<Style>
<BorderStyle>
<Bottom>Solid</Bottom>
</BorderStyle>
<TextAlign>Center</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<BorderWidth>
<Bottom>1pt</Bottom>
</BorderWidth>
<PaddingBottom>2pt</PaddingBottom>
<FontFamily>Times New Roman</FontFamily>
<FontWeight>700</FontWeight>
<FontSize>9pt</FontSize>
<BorderColor>
<Bottom>Black</Bottom>
</BorderColor>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>End Date</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
<Height>0.25in</Height>
</TableRow>
</TableRows>
<RepeatOnNewPage>true</RepeatOnNewPage>
</Header>
<TableColumns>
<TableColumn>
<Width>1.5in</Width>
</TableColumn>
<TableColumn>
<Width>2.5in</Width>
</TableColumn>
<TableColumn>
<Width>3.125in</Width>
</TableColumn>
<TableColumn>
<Width>1in</Width>
</TableColumn>
<TableColumn>
<Width>0.9in</Width>
</TableColumn>
<TableColumn>
<Width>0.9in</Width>
</TableColumn>
</TableColumns>
<Height>0.45in</Height>
</Table>
</ReportItems>
<Height>2.75in</Height>
</Body>
<rd:ReportID>a8221bfa-83bd-4bbb-9054-09ad45a8dcad</rd:ReportID>
<LeftMargin>0.5in</LeftMargin>
<EmbeddedImages>
<EmbeddedImage Name="knetch">
<ImageData>Qk1atAA////</ImageData>
<MIMEType>image/bmp</MIMEType>
</EmbeddedImage>
</EmbeddedImages>
<DataSets>
<DataSet Name="dsDisCrosscheck">
<Query>
<rd:UseGenericDesigner>true</rd:UseGenericDesigner>
<CommandType>StoredProcedure</CommandType>
<CommandText>rpt.up_get_class_attend_crosscheck_title_date</CommandText>
<QueryParameters>
<QueryParameter Name="@.Title">
<Value>=Parameters!Title.Value</Value>
</QueryParameter>
<QueryParameter Name="@.StartDtRange">
<Value>=Parameters!StartDtRange.Value</Value>
</QueryParameter>
<QueryParameter Name="@.EndDtRange">
<Value>=Parameters!EndDtRange.Value</Value>
</QueryParameter>
</QueryParameters>
<DataSourceName>dsKnetch</DataSourceName>
</Query>
<Fields>
<Field Name="reg_id">
<rd:TypeName>System.String</rd:TypeName>
<DataField>reg_id</DataField>
</Field>
<Field Name="class_no">
<rd:TypeName>System.String</rd:TypeName>
<DataField>class_no</DataField>
</Field>
<Field Name="course_no">
<rd:TypeName>System.String</rd:TypeName>
<DataField>course_no</DataField>
</Field>
<Field Name="course_title">
<rd:TypeName>System.String</rd:TypeName>
<DataField>course_title</DataField>
</Field>
<Field Name="course_type">
<rd:TypeName>System.String</rd:TypeName>
<DataField>course_type</DataField>
</Field>
<Field Name="function">
<rd:TypeName>System.String</rd:TypeName>
<DataField>function</DataField>
</Field>
<Field Name="start_date">
<rd:TypeName>System.DateTime</rd:TypeName>
<DataField>start_date</DataField>
</Field>
<Field Name="end_date">
<rd:TypeName>System.DateTime</rd:TypeName>
<DataField>end_date</DataField>
</Field>
<Field Name="credit_point">
<rd:TypeName>System.Decimal</rd:TypeName>
<DataField>credit_point</DataField>
</Field>
<Field Name="student_count">
<rd:TypeName>System.Decimal</rd:TypeName>
<DataField>student_count</DataField>
</Field>
<Field Name="peoplesoft_id">
<rd:TypeName>System.String</rd:TypeName>
<DataField>peoplesoft_id</DataField>
</Field>
<Field Name="username">
<rd:TypeName>System.String</rd:TypeName>
<DataField>username</DataField>
</Field>
<Field Name="employee_name">
<rd:TypeName>System.String</rd:TypeName>
<DataField>employee_name</DataField>
</Field>
<Field Name="reg_status">
<rd:TypeName>System.String</rd:TypeName>
<DataField>reg_status</DataField>
</Field>
<Field Name="text_status">
<rd:TypeName>System.String</rd:TypeName>
<DataField>text_status</DataField>
</Field>
<Field Name="loc_name">
<rd:TypeName>System.String</rd:TypeName>
<DataField>loc_name</DataField>
</Field>
<Field Name="office_name">
<rd:TypeName>System.String</rd:TypeName>
<DataField>office_name</DataField>
</Field>
<Field Name="office_id">
<rd:TypeName>System.String</rd:TypeName>
<DataField>office_id</DataField>
</Field>
<Field Name="area_name">
<rd:TypeName>System.String</rd:TypeName>
<DataField>area_name</DataField>
</Field>
<Field Name="area_id">
<rd:TypeName>System.String</rd:TypeName>
<DataField>area_id</DataField>
</Field>
<Field Name="product_id_name">
<rd:TypeName>System.String</rd:TypeName>
<DataField>product_id_name</DataField>
</Field>
<Field Name="product_id">
<rd:TypeName>System.String</rd:TypeName>
<DataField>product_id</DataField>
</Field>
<Field Name="segment_name">
<rd:TypeName>System.String</rd:TypeName>
<DataField>segment_name</DataField>
</Field>
<Field Name="segment_id">
<rd:TypeName>System.String</rd:TypeName>
<DataField>segment_id</DataField>
</Field>
<Field Name="service_name">
<rd:TypeName>System.String</rd:TypeName>
<DataField>service_name</DataField>
</Field>
<Field Name="service_id">
<rd:TypeName>System.String</rd:TypeName>
<DataField>service_id</DataField>
</Field>
<Field Name="bu_name">
<rd:TypeName>System.String</rd:TypeName>
<DataField>bu_name</DataField>
</Field>
<Field Name="bu_id">
<rd:TypeName>System.String</rd:TypeName>
<DataField>bu_id</DataField>
</Field>
<Field Name="process_name">
<rd:TypeName>System.String</rd:TypeName>
<DataField>process_name</DataField>
</Field>
<Field Name="process_id">
<rd:TypeName>System.String</rd:TypeName>
<DataField>process_id</DataField>
</Field>
<Field Name="job_type">
<rd:TypeName>System.String</rd:TypeName>
<DataField>job_type</DataField>
</Field>
</Fields>
</DataSet>
</DataSets>
<Author>Cll (Consultant)</Author>
<Code>public function IsDup(byval currentUsername as string, byref previousUsername as object) as string
dim result as string = "False"
if (currentUsername = previousUsername.Value)
result = "True"
else
previousUsername.Value = currentUsername
end if
IsDup = result
end function</Code>
<Width>10in</Width>
<InteractiveHeight>11in</InteractiveHeight>
<Description>This report returns a list of learners who have registration statuses of Open-Normal or Open-Confirmed for the title(s) and date range(s) specified. It provides the learner’s username, name and office. This report is used to crosscheck learners enrolled in Knetch and learners with a hotel reservation for the particular class or classes.</Description>
<Language>en-US</Language>
<PageFooter>
<ReportItems>
<Textbox Name="textbox16">
<Top>0.03125in</Top>
<rd:DefaultName>textbox16</rd:DefaultName>
<ZIndex>2</ZIndex>
<Width>1.25in</Width>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<FontFamily>Times New Roman</FontFamily>
<FontSize>6pt</FontSize>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Height>0.1875in</Height>
<Value>="Page " & Globals!PageNumber & " of " & Globals!TotalPages</Value>
</Textbox>
<Textbox Name="textbox18">
<Left>1.375in</Left>
<Top>0.03125in</Top>
<ZIndex>1</ZIndex>
<Width>6.9375in</Width>
<Style>
<TextAlign>Center</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<FontFamily>Times New Roman</FontFamily>
<FontSize>6pt</FontSize>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Height>0.1875in</Height>
<Value>="? " & DateTime.Now.Year &</Value>
</Textbox>
<Textbox Name="textbox19">
<Left>8.375in</Left>
<Top>0.03125in</Top>
<Width>1.5625in</Width>
<Style>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<FontFamily>Times New Roman</FontFamily>
<FontSize>6pt</FontSize>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Height>0.1875in</Height>
<Value>="Run Date/Time: " & DateTime.Now</Value>
</Textbox>
</ReportItems>
<Height>0.25in</Height>
<PrintOnLastPage>true</PrintOnLastPage>
<PrintOnFirstPage>true</PrintOnFirstPage>
</PageFooter>
<TopMargin>0.5in</TopMargin>
<PageHeight>8.5in</PageHeight>
</Report>
hidden cell in excel
Hello,
Can someone please tell me why there's ALWAYS a hidden cell in Excel when a report has a more than one page?
for example, a report has 10 page breaks and after exported to Excel it has the first row hidden in each sheet except the first sheet.
Why this happens always? is there any workaround to avoid this?
thanks
I also have the same questions as the original poster.
How does one get rid of the hidden row in an exported Excel spreadsheet when there are page breaks in the report?
Thanks in advance.
|||Hey Guys.
Can you clarify this a little more? I have exported my reports to excel but I get one long continuous sheet even when my report has multiple pages.
I have notice however that excel does include your report page margins, So I'm taking a guess that this could be what you're seeing in your export.
Ham
|||Hey Ham,
Yes, one long continuous sheet in Excel, but the page breaks are there in the report definition for viewing via the Report Manager web site. We (independently, the OP and I) think that somehow the page breaks are influencing the "hidden row" phenomenon.
The hidden row presents itself in the exported Excel spreadsheet on "Sheet 2", where "Sheet 1" was the parameter detail information. If one inspects Sheet 2 (which is the actual data produced by the report), one would see that row 1 is hidden (perhaps a better word is collapsed; one can expand it by double clicking on the row separator in the left margin-ish area), row 2 contains the header row/column headings, then row 3 and beyond contain the actual data.
Our report users don't like that hidden row 1. I know that it would be easy to expand and then remove it in Excel but....you know users, they don't want it there from the get-go.
Thanks for any advice.
John
|||Hi,
Can it be that you are using Document Maps in your report? It might be that it are document maps that you see.
Greetz,
Geert
Geert Verhoeven
Consultant @. Ausy Belgium
My Personal Blog
|||Hi Geert,
Although I didn't build the reports myself, I spoke with the author, and inspected the layout view (and RDL), and we are NOT using Document Maps.
Might there be another reason for the "hidden row" issue with exported Excel reports?
John
|||Hi John,
It is hard to say like that. Can you please mail or post the contents of the RDL file so I can take a look at it.
Greetz,
Geert
Geert Verhoeven
Consultant @. Ausy Belgium
My Personal Blog
|||Here is the RDL. I took the majority of lines associated with a BMP image. Thanks for taking a look.
<?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="dsKnetch">
<DataSourceReference>dsKnetch</DataSourceReference>
<rd:DataSourceID>42d0feab-c2ad-4306-b39a-0993cc80c5f8</rd:DataSourceID>
</DataSource>
</DataSources>
<BottomMargin>0.5in</BottomMargin>
<RightMargin>0.5in</RightMargin>
<PageWidth>11in</PageWidth>
<ReportParameters>
<ReportParameter Name="StartDtRange">
<DataType>DateTime</DataType>
<Prompt>Start of Date Range</Prompt>
</ReportParameter>
<ReportParameter Name="EndDtRange">
<DataType>DateTime</DataType>
<Prompt>End of Date Range</Prompt>
</ReportParameter>
<ReportParameter Name="Title">
<DataType>String</DataType>
<Prompt>Title Starts With</Prompt>
</ReportParameter>
</ReportParameters>
<rd:DrawGrid>true</rd:DrawGrid>
<InteractiveWidth>8.5in</InteractiveWidth>
<rd:SnapToGrid>true</rd:SnapToGrid>
<Body>
<ReportItems>
<Image Name="image1">
<Sizing>FitProportional</Sizing>
<ZIndex>5</ZIndex>
<Source>Embedded</Source>
<Style />
<Height>0.375in</Height>
<Value>knetch</Value>
</Image>
<Textbox Name="textbox38">
<Top>0.75in</Top>
<rd:DefaultName>textbox38</rd:DefaultName>
<ZIndex>4</ZIndex>
<Style>
<TextAlign>Center</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<FontFamily>Times New Roman</FontFamily>
<FontWeight>700</FontWeight>
<FontSize>12pt</FontSize>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Height>0.25in</Height>
<Value>Knetch - Crosscheck Report (by Title & Date)</Value>
</Textbox>
<Textbox Name="textbox37">
<Top>0.375in</Top>
<rd:DefaultName>textbox37</rd:DefaultName>
<ZIndex>3</ZIndex>
<Style>
<TextAlign>Center</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<FontFamily>Times New Roman</FontFamily>
<FontWeight>700</FontWeight>
<FontSize>12pt</FontSize>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Height>0.25in</Height>
<Value>Company</Value>
</Textbox>
<Rectangle Name="rectangle1">
<ReportItems>
<Textbox Name="textbox55">
<Left>1.5in</Left>
<Top>0.125in</Top>
<rd:DefaultName>textbox55</rd:DefaultName>
<ZIndex>3</ZIndex>
<Width>8.375in</Width>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<FontFamily>Times New Roman</FontFamily>
<FontSize>9pt</FontSize>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Height>0.2in</Height>
<Value>=Parameters!Title.Value</Value>
</Textbox>
<Textbox Name="textbox28">
<Left>0.125in</Left>
<Top>0.125in</Top>
<ZIndex>2</ZIndex>
<Width>1.25in</Width>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<FontFamily>Times New Roman</FontFamily>
<FontSize>9pt</FontSize>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Height>0.2in</Height>
<Value>Title Starts With:</Value>
</Textbox>
<Textbox Name="textbox2">
<Left>0.125in</Left>
<Top>0.375in</Top>
<ZIndex>1</ZIndex>
<Width>1.25in</Width>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<FontFamily>Times New Roman</FontFamily>
<FontSize>9pt</FontSize>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Height>0.2in</Height>
<Value>Class(es) Date Range:</Value>
</Textbox>
<Textbox Name="textbox3">
<Left>1.5in</Left>
<Top>0.375in</Top>
<Width>8.375in</Width>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<FontFamily>Times New Roman</FontFamily>
<FontSize>9pt</FontSize>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Height>0.2in</Height>
<Value>=Parameters!StartDtRange.Value & " to " & Parameters!EndDtRange.Value</Value>
</Textbox>
</ReportItems>
<Top>1.375in</Top>
<ZIndex>2</ZIndex>
<PageBreakAtEnd>true</PageBreakAtEnd>
<Height>0.625in</Height>
</Rectangle>
<Textbox Name="textbox54">
<Top>1.125in</Top>
<ZIndex>1</ZIndex>
<Width>2.75in</Width>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<FontFamily>Times New Roman</FontFamily>
<FontWeight>700</FontWeight>
<FontSize>9pt</FontSize>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Height>0.2in</Height>
<Value>Self Service Filter Options:</Value>
</Textbox>
<Table Name="table1">
<DataSetName>dsDisvantageCrosscheck</DataSetName>
<Top>2.125in</Top>
<Width>9.925in</Width>
<Details>
<TableRows>
<TableRow>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox4">
<rd:DefaultName>textbox4</rd:DefaultName>
<ZIndex>5</ZIndex>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<FontFamily>Times New Roman</FontFamily>
<FontSize>9pt</FontSize>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Fields!username.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox5">
<rd:DefaultName>textbox5</rd:DefaultName>
<ZIndex>4</ZIndex>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<FontFamily>Times New Roman</FontFamily>
<FontSize>9pt</FontSize>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Fields!employee_name.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox6">
<rd:DefaultName>textbox6</rd:DefaultName>
<ZIndex>3</ZIndex>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<FontFamily>Times New Roman</FontFamily>
<FontSize>9pt</FontSize>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Fields!office_name.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox8">
<rd:DefaultName>textbox8</rd:DefaultName>
<ZIndex>2</ZIndex>
<Style>
<TextAlign>Center</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<FontFamily>Times New Roman</FontFamily>
<FontSize>9pt</FontSize>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Fields!class_no.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox12">
<rd:DefaultName>textbox12</rd:DefaultName>
<ZIndex>1</ZIndex>
<Style>
<TextAlign>Center</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<FontFamily>Times New Roman</FontFamily>
<FontSize>9pt</FontSize>
<Format>MM/dd/yy</Format>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Fields!start_date.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox10">
<rd:DefaultName>textbox10</rd:DefaultName>
<Style>
<TextAlign>Center</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<FontFamily>Times New Roman</FontFamily>
<FontSize>9pt</FontSize>
<Format>MM/dd/yy</Format>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Fields!end_date.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
<Height>0.2in</Height>
</TableRow>
</TableRows>
<Grouping Name="table1_Details_Group">
<GroupExpressions>
<GroupExpression>=Fields!username.Value</GroupExpression>
</GroupExpressions>
</Grouping>
<Sorting>
<SortBy>
<SortExpression>=Fields!employee_name.Value</SortExpression>
<Direction>Ascending</Direction>
</SortBy>
<SortBy>
<SortExpression>=Fields!start_date.Value</SortExpression>
<Direction>Ascending</Direction>
</SortBy>
<SortBy>
<SortExpression>=Fields!end_date.Value</SortExpression>
<Direction>Ascending</Direction>
</SortBy>
</Sorting>
</Details>
<Header>
<TableRows>
<TableRow>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox29">
<ZIndex>11</ZIndex>
<Style>
<BorderStyle>
<Bottom>Solid</Bottom>
</BorderStyle>
<PaddingLeft>2pt</PaddingLeft>
<BorderWidth>
<Bottom>1pt</Bottom>
</BorderWidth>
<PaddingBottom>2pt</PaddingBottom>
<FontFamily>Times New Roman</FontFamily>
<FontWeight>700</FontWeight>
<FontSize>9pt</FontSize>
<BorderColor>
<Bottom>Black</Bottom>
</BorderColor>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>Username</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox30">
<ZIndex>10</ZIndex>
<Style>
<BorderStyle>
<Bottom>Solid</Bottom>
</BorderStyle>
<PaddingLeft>2pt</PaddingLeft>
<BorderWidth>
<Bottom>1pt</Bottom>
</BorderWidth>
<PaddingBottom>2pt</PaddingBottom>
<FontFamily>Times New Roman</FontFamily>
<FontWeight>700</FontWeight>
<FontSize>9pt</FontSize>
<BorderColor>
<Bottom>Black</Bottom>
</BorderColor>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>Participant Name</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox31">
<ZIndex>9</ZIndex>
<Style>
<BorderStyle>
<Bottom>Solid</Bottom>
</BorderStyle>
<PaddingLeft>2pt</PaddingLeft>
<BorderWidth>
<Bottom>1pt</Bottom>
</BorderWidth>
<PaddingBottom>2pt</PaddingBottom>
<FontFamily>Times New Roman</FontFamily>
<FontWeight>700</FontWeight>
<FontSize>9pt</FontSize>
<BorderColor>
<Bottom>Black</Bottom>
</BorderColor>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>Office</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox32">
<ZIndex>8</ZIndex>
<Style>
<BorderStyle>
<Bottom>Solid</Bottom>
</BorderStyle>
<TextAlign>Center</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<BorderWidth>
<Bottom>1pt</Bottom>
</BorderWidth>
<PaddingBottom>2pt</PaddingBottom>
<FontFamily>Times New Roman</FontFamily>
<FontWeight>700</FontWeight>
<FontSize>9pt</FontSize>
<BorderColor>
<Bottom>Black</Bottom>
</BorderColor>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>Class Number</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox33">
<ZIndex>7</ZIndex>
<Style>
<BorderStyle>
<Bottom>Solid</Bottom>
</BorderStyle>
<TextAlign>Center</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<BorderWidth>
<Bottom>1pt</Bottom>
</BorderWidth>
<PaddingBottom>2pt</PaddingBottom>
<FontFamily>Times New Roman</FontFamily>
<FontWeight>700</FontWeight>
<FontSize>9pt</FontSize>
<BorderColor>
<Bottom>Black</Bottom>
</BorderColor>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>Start Date</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox34">
<ZIndex>6</ZIndex>
<Style>
<BorderStyle>
<Bottom>Solid</Bottom>
</BorderStyle>
<TextAlign>Center</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<BorderWidth>
<Bottom>1pt</Bottom>
</BorderWidth>
<PaddingBottom>2pt</PaddingBottom>
<FontFamily>Times New Roman</FontFamily>
<FontWeight>700</FontWeight>
<FontSize>9pt</FontSize>
<BorderColor>
<Bottom>Black</Bottom>
</BorderColor>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>End Date</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
<Height>0.25in</Height>
</TableRow>
</TableRows>
<RepeatOnNewPage>true</RepeatOnNewPage>
</Header>
<TableColumns>
<TableColumn>
<Width>1.5in</Width>
</TableColumn>
<TableColumn>
<Width>2.5in</Width>
</TableColumn>
<TableColumn>
<Width>3.125in</Width>
</TableColumn>
<TableColumn>
<Width>1in</Width>
</TableColumn>
<TableColumn>
<Width>0.9in</Width>
</TableColumn>
<TableColumn>
<Width>0.9in</Width>
</TableColumn>
</TableColumns>
<Height>0.45in</Height>
</Table>
</ReportItems>
<Height>2.75in</Height>
</Body>
<rd:ReportID>a8221bfa-83bd-4bbb-9054-09ad45a8dcad</rd:ReportID>
<LeftMargin>0.5in</LeftMargin>
<EmbeddedImages>
<EmbeddedImage Name="knetch">
<ImageData>Qk1atAA////</ImageData>
<MIMEType>image/bmp</MIMEType>
</EmbeddedImage>
</EmbeddedImages>
<DataSets>
<DataSet Name="dsDisCrosscheck">
<Query>
<rd:UseGenericDesigner>true</rd:UseGenericDesigner>
<CommandType>StoredProcedure</CommandType>
<CommandText>rpt.up_get_class_attend_crosscheck_title_date</CommandText>
<QueryParameters>
<QueryParameter Name="@.Title">
<Value>=Parameters!Title.Value</Value>
</QueryParameter>
<QueryParameter Name="@.StartDtRange">
<Value>=Parameters!StartDtRange.Value</Value>
</QueryParameter>
<QueryParameter Name="@.EndDtRange">
<Value>=Parameters!EndDtRange.Value</Value>
</QueryParameter>
</QueryParameters>
<DataSourceName>dsKnetch</DataSourceName>
</Query>
<Fields>
<Field Name="reg_id">
<rd:TypeName>System.String</rd:TypeName>
<DataField>reg_id</DataField>
</Field>
<Field Name="class_no">
<rd:TypeName>System.String</rd:TypeName>
<DataField>class_no</DataField>
</Field>
<Field Name="course_no">
<rd:TypeName>System.String</rd:TypeName>
<DataField>course_no</DataField>
</Field>
<Field Name="course_title">
<rd:TypeName>System.String</rd:TypeName>
<DataField>course_title</DataField>
</Field>
<Field Name="course_type">
<rd:TypeName>System.String</rd:TypeName>
<DataField>course_type</DataField>
</Field>
<Field Name="function">
<rd:TypeName>System.String</rd:TypeName>
<DataField>function</DataField>
</Field>
<Field Name="start_date">
<rd:TypeName>System.DateTime</rd:TypeName>
<DataField>start_date</DataField>
</Field>
<Field Name="end_date">
<rd:TypeName>System.DateTime</rd:TypeName>
<DataField>end_date</DataField>
</Field>
<Field Name="credit_point">
<rd:TypeName>System.Decimal</rd:TypeName>
<DataField>credit_point</DataField>
</Field>
<Field Name="student_count">
<rd:TypeName>System.Decimal</rd:TypeName>
<DataField>student_count</DataField>
</Field>
<Field Name="peoplesoft_id">
<rd:TypeName>System.String</rd:TypeName>
<DataField>peoplesoft_id</DataField>
</Field>
<Field Name="username">
<rd:TypeName>System.String</rd:TypeName>
<DataField>username</DataField>
</Field>
<Field Name="employee_name">
<rd:TypeName>System.String</rd:TypeName>
<DataField>employee_name</DataField>
</Field>
<Field Name="reg_status">
<rd:TypeName>System.String</rd:TypeName>
<DataField>reg_status</DataField>
</Field>
<Field Name="text_status">
<rd:TypeName>System.String</rd:TypeName>
<DataField>text_status</DataField>
</Field>
<Field Name="loc_name">
<rd:TypeName>System.String</rd:TypeName>
<DataField>loc_name</DataField>
</Field>
<Field Name="office_name">
<rd:TypeName>System.String</rd:TypeName>
<DataField>office_name</DataField>
</Field>
<Field Name="office_id">
<rd:TypeName>System.String</rd:TypeName>
<DataField>office_id</DataField>
</Field>
<Field Name="area_name">
<rd:TypeName>System.String</rd:TypeName>
<DataField>area_name</DataField>
</Field>
<Field Name="area_id">
<rd:TypeName>System.String</rd:TypeName>
<DataField>area_id</DataField>
</Field>
<Field Name="product_id_name">
<rd:TypeName>System.String</rd:TypeName>
<DataField>product_id_name</DataField>
</Field>
<Field Name="product_id">
<rd:TypeName>System.String</rd:TypeName>
<DataField>product_id</DataField>
</Field>
<Field Name="segment_name">
<rd:TypeName>System.String</rd:TypeName>
<DataField>segment_name</DataField>
</Field>
<Field Name="segment_id">
<rd:TypeName>System.String</rd:TypeName>
<DataField>segment_id</DataField>
</Field>
<Field Name="service_name">
<rd:TypeName>System.String</rd:TypeName>
<DataField>service_name</DataField>
</Field>
<Field Name="service_id">
<rd:TypeName>System.String</rd:TypeName>
<DataField>service_id</DataField>
</Field>
<Field Name="bu_name">
<rd:TypeName>System.String</rd:TypeName>
<DataField>bu_name</DataField>
</Field>
<Field Name="bu_id">
<rd:TypeName>System.String</rd:TypeName>
<DataField>bu_id</DataField>
</Field>
<Field Name="process_name">
<rd:TypeName>System.String</rd:TypeName>
<DataField>process_name</DataField>
</Field>
<Field Name="process_id">
<rd:TypeName>System.String</rd:TypeName>
<DataField>process_id</DataField>
</Field>
<Field Name="job_type">
<rd:TypeName>System.String</rd:TypeName>
<DataField>job_type</DataField>
</Field>
</Fields>
</DataSet>
</DataSets>
<Author>Cll (Consultant)</Author>
<Code>public function IsDup(byval currentUsername as string, byref previousUsername as object) as string
dim result as string = "False"
if (currentUsername = previousUsername.Value)
result = "True"
else
previousUsername.Value = currentUsername
end if
IsDup = result
end function</Code>
<Width>10in</Width>
<InteractiveHeight>11in</InteractiveHeight>
<Description>This report returns a list of learners who have registration statuses of Open-Normal or Open-Confirmed for the title(s) and date range(s) specified. It provides the learner’s username, name and office. This report is used to crosscheck learners enrolled in Knetch and learners with a hotel reservation for the particular class or classes.</Description>
<Language>en-US</Language>
<PageFooter>
<ReportItems>
<Textbox Name="textbox16">
<Top>0.03125in</Top>
<rd:DefaultName>textbox16</rd:DefaultName>
<ZIndex>2</ZIndex>
<Width>1.25in</Width>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<FontFamily>Times New Roman</FontFamily>
<FontSize>6pt</FontSize>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Height>0.1875in</Height>
<Value>="Page " & Globals!PageNumber & " of " & Globals!TotalPages</Value>
</Textbox>
<Textbox Name="textbox18">
<Left>1.375in</Left>
<Top>0.03125in</Top>
<ZIndex>1</ZIndex>
<Width>6.9375in</Width>
<Style>
<TextAlign>Center</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<FontFamily>Times New Roman</FontFamily>
<FontSize>6pt</FontSize>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Height>0.1875in</Height>
<Value>="? " & DateTime.Now.Year &</Value>
</Textbox>
<Textbox Name="textbox19">
<Left>8.375in</Left>
<Top>0.03125in</Top>
<Width>1.5625in</Width>
<Style>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<FontFamily>Times New Roman</FontFamily>
<FontSize>6pt</FontSize>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Height>0.1875in</Height>
<Value>="Run Date/Time: " & DateTime.Now</Value>
</Textbox>
</ReportItems>
<Height>0.25in</Height>
<PrintOnLastPage>true</PrintOnLastPage>
<PrintOnFirstPage>true</PrintOnFirstPage>
</PageFooter>
<TopMargin>0.5in</TopMargin>
<PageHeight>8.5in</PageHeight>
</Report>
Friday, February 24, 2012
HELP: How to avoid ridiculous system resource issues when connecting to a huge Excel file
Hi all,
I have a 400MB Excel file that I consume from another automated process (don't ask). I copy this file down locally to my server, and I am attempting to create an SSIS package that points to this file via a connection manager. My computer starts gobbling up massive amounts of memory (devenv.exe gets up to about 800MB or so, then drops back down to 100MB) even when I attempt to rename the connection in the connection managers tab.
I have set all BypassPrepare to TRUE and ValidateExternalMetadata properties to FALSE, and still it can take up to 3 to 6 minutes for BI Dev Studio to respond. My specs:
Intel Centrino Duo 2.00 GHz
2GB RAM
XP Pro SP2
There MUST be a way for me to work effectively on a file of this size. Please help! Thanks much for any assistance.
Sincerely,
Brian Pulliam
Ok, so it sounds like it wants to read the entire file, a nice feature, and probably unavoidable.
Personally I would be developing and testing against a smaller file that is easier to work with, and easier to manipulate for test cases. Then when the bulk of the dev is ready run a test with the real file.
You may think this is a big issue, and whilst it would be nicer if it behaved better, what if this was a SQL load, woud you expect to dev against a 100GB database? Whilst the metadata would be OK, the simple running of a test would be impractical, so I'd still go with smaller test data during development.
|||Just one more idea to try...
If you are using "Excel Source" try to use "OLEDB Source" instead and specify Microsoft Jet 4.0 OLE DB Provider.
If you are using Excel 2007, you should use the new ACE provider.
Regards,
Yitzhak
|||In case if you don't have MS Office 2007 installed on your machine,
here is the link to donwload the connectivity components:
2007 Office System Driver: Data Connectivity Components.
http://www.microsoft.com/downloads/details.aspx?FamilyID=7554F536-8C28-4598-9B72-EF94E038C891&displaylang=ja
Regards,
Yitzhak
|||Thanks much for the reply Darren. I agree that a small subset of data would be great to test with. Actually I would dev against a 100GB database, although I would not pull all the data in my tests.
But your analogy to running a test on a 100GB DB is not very applicable. Firstly, I am not loading an entire DB. My Excel connection is pointing at one worksheet in an Excel 2003 file. And I have not even tried executing this package yet, I am nowhere near that far. Simply renaming the connection to the Excel file takes about 5 minutes before BI Developer Studio is responsive again. Also note that when connecting to a 100GB database, BI does not attempt to load the entire data set in that scenario. Maybe it has something to do with using an Excel file, that SSIS goes all paranoid and wants to look at the whole dang file.
Do you think if I set up a linked server to the Excel file in Management Studio that it might not have this problem? Maybe that would fake it into trusting the data source enough to not to the validation load. I attempted to do this on Friday but did not have much luck. Has anyone done this for an Excel 11.0 workbook?
Thanks again,
Brian
Sunday, February 19, 2012
HELP: Exporting to excel
Hi,
I have been working on this problem for some time now and I am no closer to
a solution/answer.
The trouble is that I have created two reports which are basically tables
which display database data, with the input of several search parameters, but
when they get to a certain size (it appears to be like this) and I try to
export to excel I get this error when trying to open the file:
************
Microsoft Office Excel File Repair Log
Errors were detected in file 'ViewRequests.xls'
The following is a list of repairs:
Damage to the file was so extensive that repairs were not possible. Excel
attempted to recover your formulas and values, but some data may have been
lost or corrupted.
************
From this error, it seems like an excel problem, but we have tried different
versions and we do have a possible workaround we got from this site:
http://support.microsoft.com/default.aspx?scid=kb en-us 820712
but it is very time consuming and not many users have two versions on their
machine.
A couple of people have told me that it must be something in the report, so
I am wondering if formulas or something else would cause this, or if anyone
as encountered this before, or if anyone can confirm is this is a report or
excel error. It appers to be happening only for larger files, but I am not
sure.
ThanksThis command reaks havick on my emf files.
=System.Math.Floor((RowNumber(table4_Group1)-1)/10)
It makes all of multiple page reports act differently and look
differently.
Trint|||Specer23,
You made no mention of clearing out your user/system temp folders or creating a
new Excel template or being sure that there are no Addins or similar templates
loaded in your Excel.
Nor did you mention the scope of affected users.
These are all cursory steps to solving any MSO product issue and if not done is
a grand waste of time if not done.
You certainly could have a problem w/your report, however once you tip Excel,
you must do a little clean up.
I also would through trial & error, get to the magic number between a rendered
report and Excel or not.
Then I'd look at the raw data near that point and look for things like coma's in
numeric fields, invalid dates etc...
HTH
JeffP....
"Spencer23" <Spencer23@.discussions.microsoft.com> wrote in message
news:76DEC6BB-B20C-41BE-9CD3-60A1D638120C@.microsoft.com...
> Hi,
> I have been working on this problem for some time now and I am no closer to
> a solution/answer.
> The trouble is that I have created two reports which are basically tables
> which display database data, with the input of several search parameters, but
> when they get to a certain size (it appears to be like this) and I try to
> export to excel I get this error when trying to open the file:
> ************
> Microsoft Office Excel File Repair Log
> Errors were detected in file 'ViewRequests.xls'
> The following is a list of repairs:
> Damage to the file was so extensive that repairs were not possible. Excel
> attempted to recover your formulas and values, but some data may have been
> lost or corrupted.
> ************
> From this error, it seems like an excel problem, but we have tried different
> versions and we do have a possible workaround we got from this site:
> http://support.microsoft.com/default.aspx?scid=kb en-us 820712
> but it is very time consuming and not many users have two versions on their
> machine.
> A couple of people have told me that it must be something in the report, so
> I am wondering if formulas or something else would cause this, or if anyone
> as encountered this before, or if anyone can confirm is this is a report or
> excel error. It appers to be happening only for larger files, but I am not
> sure.
>
> Thanks
I have been working on this problem for some time now and I am no closer to
a solution/answer.
The trouble is that I have created two reports which are basically tables
which display database data, with the input of several search parameters, but
when they get to a certain size (it appears to be like this) and I try to
export to excel I get this error when trying to open the file:
************
Microsoft Office Excel File Repair Log
Errors were detected in file 'ViewRequests.xls'
The following is a list of repairs:
Damage to the file was so extensive that repairs were not possible. Excel
attempted to recover your formulas and values, but some data may have been
lost or corrupted.
************
From this error, it seems like an excel problem, but we have tried different
versions and we do have a possible workaround we got from this site:
http://support.microsoft.com/default.aspx?scid=kb en-us 820712
but it is very time consuming and not many users have two versions on their
machine.
A couple of people have told me that it must be something in the report, so
I am wondering if formulas or something else would cause this, or if anyone
as encountered this before, or if anyone can confirm is this is a report or
excel error. It appers to be happening only for larger files, but I am not
sure.
ThanksThis command reaks havick on my emf files.
=System.Math.Floor((RowNumber(table4_Group1)-1)/10)
It makes all of multiple page reports act differently and look
differently.
Trint|||Specer23,
You made no mention of clearing out your user/system temp folders or creating a
new Excel template or being sure that there are no Addins or similar templates
loaded in your Excel.
Nor did you mention the scope of affected users.
These are all cursory steps to solving any MSO product issue and if not done is
a grand waste of time if not done.
You certainly could have a problem w/your report, however once you tip Excel,
you must do a little clean up.
I also would through trial & error, get to the magic number between a rendered
report and Excel or not.
Then I'd look at the raw data near that point and look for things like coma's in
numeric fields, invalid dates etc...
HTH
JeffP....
"Spencer23" <Spencer23@.discussions.microsoft.com> wrote in message
news:76DEC6BB-B20C-41BE-9CD3-60A1D638120C@.microsoft.com...
> Hi,
> I have been working on this problem for some time now and I am no closer to
> a solution/answer.
> The trouble is that I have created two reports which are basically tables
> which display database data, with the input of several search parameters, but
> when they get to a certain size (it appears to be like this) and I try to
> export to excel I get this error when trying to open the file:
> ************
> Microsoft Office Excel File Repair Log
> Errors were detected in file 'ViewRequests.xls'
> The following is a list of repairs:
> Damage to the file was so extensive that repairs were not possible. Excel
> attempted to recover your formulas and values, but some data may have been
> lost or corrupted.
> ************
> From this error, it seems like an excel problem, but we have tried different
> versions and we do have a possible workaround we got from this site:
> http://support.microsoft.com/default.aspx?scid=kb en-us 820712
> but it is very time consuming and not many users have two versions on their
> machine.
> A couple of people have told me that it must be something in the report, so
> I am wondering if formulas or something else would cause this, or if anyone
> as encountered this before, or if anyone can confirm is this is a report or
> excel error. It appers to be happening only for larger files, but I am not
> sure.
>
> Thanks
Subscribe to:
Posts (Atom)