Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
ReportItems in Header repeating when exporting to EXCEL

Yanıt ReportItems in Header repeating when exporting to EXCEL

  • Monday, March 04, 2013 8:31 PM
     
     
    Sign In to Vote
    0

    I used ReportItems."FieldName".Value to get the value to show in the header.

    When grouping by that FieldName Value, the value changes on every page break on the ReportViewer.

    The problem is that when I export the Excel spreadsheet, the FieldName Value shown on the spreadsheet is the first FieldName Value and that same value is shown on every worksheet.  Each worksheet tab pagename has the correct FieldName Value, it's just the value on the header repeats the first occurance of the FieldName Value.

     

All Replies

  • Wednesday, March 06, 2013 6:34 AM
    Moderator
     
     
    Sign In to Vote
    0

    Hello,

    Thanks for your post. I try to reproduce the issue in my test environment, however, it works well.
    Did you display the “FieldName” in page header by using the following expression:=ReportItems!FieldName.Value ?If so, the report should render the different values on each worksheet.
    If I have any misunderstanding, please post the expression you used in page header with us, it benefits us for further analysis.

    Regards,
    Fanny Liu

    Fanny Liu
    TechNet Community Support

     
  • Friday, March 08, 2013 6:44 PM
     
     
    Sign In to Vote
    0

    Hi Fanny,

    I could not get =ReportItems!FieldName.Value to work.  It always defaulted to the first value in the list of codes.

    The work around with the code not showing in the Tablix header was to put that code in the report header, which is not clean looking when exported to Excel, but it got the boss off my back. :-)

     
  • Friday, March 08, 2013 8:25 PM
     
     
    Sign In to Vote
    0

    Fanny,

    Did you try to export the Tablix tioExcel?

    It displays properly on all the new pages per a change in the [Field Name], but when I export the report into Excel and pdf the value on all the header pages is the first occurrence of that value.

    I think exporting the file to Excel, processes the data from scratch.

     
  • Monday, March 11, 2013 6:40 AM
    Moderator
     
     Proposed Answer
    Sign In to Vote
    0

    Hello,
    Based on my test, I can get the exact result when export the report to PDF and Excel. In the report, the report data is grouped by the field and add page break between each instance of the group. After export to Excel, the header is rendered to the cell grid on the Excel worksheet and display different values in each worksheet.

    Please post both the report and the dataset with sample data to us by the following E-mail address. It is benefit for us to do further analysis.
    E-mail: sqltnsp@microsoft.com

    Regards,
    Fanny Liu

    Fanny Liu
    TechNet Community Support

     
  • Wednesday, March 13, 2013 11:48 AM
     
     
    Sign In to Vote
    0

    Fanny,

    I just emailed my example to sqltnsp@microsoft.com.

    Here is my email message:

    Hello,
     
    Fanny Liu had asked me to email an example of my issue. 
    http://social.msdn.microsoft.com/Forums/en-US/sqlreportingservices/thread/9c5ff75e-9957-403d-a93a-fc4ab4512243/
     
    You can see on the CountryTabs.xls that the Tablix Report Header shows Australia and all of the Tablix Report Headers, while the detail countries change on all of the tabs.  When you look at the online execution of the rdl in SSRS, the Tablix Report Header country name changes when tabbing to other countries.
     
    I attached CountryTabs.xls, rdl zip file and the Create Table/data insert sql. 
     
    I used Patrick's LeBlanc example Public Shared Function ParameterDisplay, because I could not get the tabs to show the countries on the tabs.
     


    • Edited by nagshead_obx Wednesday, March 13, 2013 2:27 PM
    •  
     
  • Tuesday, March 19, 2013 1:01 PM
     
     
    Sign In to Vote
    0

    Fanny,

    Any luck using my example to change the country name in the Tablix header?

     
  • Wednesday, March 20, 2013 2:22 AM
    Moderator
     
     
    Sign In to Vote
    0

    Hello,

    I can reproduce the issue in my test environment. To work around this issue, please redesign the report with the following steps:

    1. Add a list control into the report design surface and add the table into the list.
    2. Click the list data region, and specify the detail group of the list group on field “country”.
    3. Specify page break in group of the list and specify page name as follows:=Fields!Country.value

    Regards,
    Fanny Liu

    Fanny Liu
    TechNet Community Support

     
  • Wednesday, March 20, 2013 1:59 PM
     
     
    Sign In to Vote
    0

    Fanny,

    I tried what you suggested and the first country name shows up on all of the Tablix pages.

    I guess I'm back to square one.

     
  • Thursday, March 21, 2013 12:43 PM
     
     
    Sign In to Vote
    0
    Can you create a small test rdl and email it to me.
     
  • Tuesday, April 02, 2013 2:00 PM
     
     Answered
    Sign In to Vote
    0

    I finally resolved my issue with the FieldName.Value issue that worked on the tabs, but would not work properly in the Tablix header.

    The solution was to create a child group from the parent group and then I could reference that Field value.

    Here is an example: The Parent Group is StateName. To have the StateName go into the Tablix header and change in each tab, I created a Child Group StateName1 both in the report query and in the Child Group. Now it works.

    • Marked As Answer by nagshead_obx Tuesday, April 02, 2013 2:00 PM
    •