none
export ssrs report in excel in multiple sheets

    Question

  • Hi all

    split excel into multiple sheets based on parameter instead of group

    and my parameter value are fixed i.e "in","Out" ,"Reg","Pend"

    how to make four sheets of above.


    Dilip Patil..

    Saturday, March 22, 2014 5:12 AM

Answers

  • Hi Dilip,

    In order to achieve your requirement, we can use a list to contain the matrix, then add a page break between each instance of the column group. For more details, please refer to the following steps:

    1. Drag a list to contain the matrix, and the set the dataset of the list to current dataset.
    2. In the Row Groups pane, right-click the Details group to add a group grouped on the expression below:
      =IIF(Fields!column_group_field.Value="LOAN",”LOAN”,”Others”)
    3. Click the Details group to navigate to Properties Windows.
    4. Expand the PageBreak group to set the BreakLocation property to Between.
    5. Use the same expression to set the PageName property:
      =IIF(Fields!column_group_field.Value="LOAN",”LOAN”,”Others”)


    Then the yellow marked column and green marked columns will be in two sheets in Excel.

    If there are any other questions, please feel free to ask.

    Thanks,
    Katherine Xiong


    Katherine Xiong
    TechNet Community Support

    • Marked as answer by Dilip Patil Wednesday, March 26, 2014 11:24 AM
    • Unmarked as answer by Dilip Patil Wednesday, March 26, 2014 11:24 AM
    • Marked as answer by Dilip Patil Wednesday, March 26, 2014 11:24 AM
    Wednesday, March 26, 2014 5:56 AM

All replies

  • Create multiple tablix for each dataset in the report having a page break between them. With this it will allow you to export in multiple sheet.

    I don't think using only one parameter will work, as you have said you do not need as a group. i.e 2 parameters for 2 Excel sheet(1:1). If a single parameter is used to filter multiple tablix then this will much easier and the above option can be apply directly.


    Regards, RSingh

    Saturday, March 22, 2014 5:26 AM
  • You can create a dummy grouping based on parameter values and then export to excel. Inside your container add a grouping based on selected parameter values (if its static you can even give values as is) and then you can select property insert page break after

    see

    http://visakhm.blogspot.in/2013/10/using-ssrs-to-export-sqlserver-data-to.html


    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    Saturday, March 22, 2014 5:37 AM
  • thanks visakh16 

    i want split this yello marked column group and green marked column in two sheets 

    this is matrix is it possible in it..


    Dilip Patil..

    Saturday, March 22, 2014 5:51 AM
  • Hi Dilip,

    In order to achieve your requirement, we can use a list to contain the matrix, then add a page break between each instance of the column group. For more details, please refer to the following steps:

    1. Drag a list to contain the matrix, and the set the dataset of the list to current dataset.
    2. In the Row Groups pane, right-click the Details group to add a group grouped on the expression below:
      =IIF(Fields!column_group_field.Value="LOAN",”LOAN”,”Others”)
    3. Click the Details group to navigate to Properties Windows.
    4. Expand the PageBreak group to set the BreakLocation property to Between.
    5. Use the same expression to set the PageName property:
      =IIF(Fields!column_group_field.Value="LOAN",”LOAN”,”Others”)


    Then the yellow marked column and green marked columns will be in two sheets in Excel.

    If there are any other questions, please feel free to ask.

    Thanks,
    Katherine Xiong


    Katherine Xiong
    TechNet Community Support

    • Marked as answer by Dilip Patil Wednesday, March 26, 2014 11:24 AM
    • Unmarked as answer by Dilip Patil Wednesday, March 26, 2014 11:24 AM
    • Marked as answer by Dilip Patil Wednesday, March 26, 2014 11:24 AM
    Wednesday, March 26, 2014 5:56 AM
  • hi katherine

    i would propose this will work for me..

    but as i drag a list on matrix and want to preview..

    my matrix show data on last page

     why there are so many blank pages coming


    Dilip Patil..

    Wednesday, March 26, 2014 11:37 AM