none
Visibility formula help

    Question

  • Hi All,


     I want to calculate a formula, depending on that formula, I want to hide and make the column visible.  I have a column called SN. I want to make it visible when the value of the parameter called @section is “GCG” otherwise I want to make it invisible. Same thing with another columns, if @section parameter is “GED” then I want to make column 2  called PN visible and column SN invisible. I tried to calculate this formula and putting it in Visibility-hidden formula section

    IIF(INSTR(Join(Parameters!section.Value), "GCG") > 0 OR Parameters!section.Value(0) = "-1", false, true)


    the above formula didn’t make that particular columns visible/invisible

    Alternatively, I tried putting the formula in the XML file for the report.

    <TableColumn> 
    <Visibility> <Hidden>=IIF(INSTR(Join(Parameters!comp.Value), "GCG") > 0 OR Parameters!comp.Value(0) = "-1", false, true)</Hidden> </Visibility> 
    <Width>1in</Width> 
    </TableColumn>

    The above formula seems to be working, but the problem is when I export the report to the Excel. The column that I am making visible by the above formula is not exported to excel.

    I am not sure what am I doing wrong, but I am struggling with this issue for past two days.

    The value in the @section parameter can be “GCG” or “GED” or “PED” or all three of them “GCG”,”GED”, “PED” or two of them “GCG”, “PED”, it can be any combination. If I am passing the combination of two parameters then I want two columns to be visible and other invisible.

    any help or hint will be greatly appreciated. I am struggling for a while with this issue.


    Thursday, July 31, 2014 4:39 AM

Answers

  • Hi Anjali100,

    According to your description, you have used expression to control the visibility of columns in your tablix. However, when exporting into CSV file, it still shows all columns which some of columns need to be hidden. Right?

    In Reporting Services, when exporting to CSV/XML file, as mentioned in the link you post, these two are only data renders, it will not render any interactivity like Hide, Hyperlink... Please refer to the "Interactivity" in the link below:

    Exporting to a CSV File (Report Builder and SSRS)

    And as we tested in our local environment, it doesn't automatically omit the data in those columns which has set Hide. So your requirement can't be achieved in CSV format currently.

    If you have any question, please feel free to ask.

    Best Regards,
    Simon Hou

    Sunday, August 03, 2014 1:59 PM
    Moderator

All replies

  • So do you want columns to be always visible when you export to excel? Or does it again have to be based on report parameters?

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

    Thursday, July 31, 2014 5:35 AM
  • I think I didn't explain the question properly. All I want is to export the non hidden columns to be exported to CSV. Right now it is showing me the columns, but when I click on the export button to CSV then the columns that I am conditionally flipping using the XML code are not exported. Per one article

    

    

    

    http://blogs.msdn.com/b/bimusings/archive/2007/02/07/reporting-services-why-aren-t-all-my-report-columns-exporting-to-csv-and-or-xml.aspx

    · Expressions on Visibility– If you conditionally flip the Hidden property of a textbox or column in your report, data renderers will automatically omit this data from export, even if the expression evaluates to False (False meaning “don’t hide”). This is a by-design behavior.

    I want to get around this behavior programmatically. Is it possible to do that? I really need help.

    Thursday, July 31, 2014 5:16 PM
  • Hi Anjali100,

    According to your description, you have used expression to control the visibility of columns in your tablix. However, when exporting into CSV file, it still shows all columns which some of columns need to be hidden. Right?

    In Reporting Services, when exporting to CSV/XML file, as mentioned in the link you post, these two are only data renders, it will not render any interactivity like Hide, Hyperlink... Please refer to the "Interactivity" in the link below:

    Exporting to a CSV File (Report Builder and SSRS)

    And as we tested in our local environment, it doesn't automatically omit the data in those columns which has set Hide. So your requirement can't be achieved in CSV format currently.

    If you have any question, please feel free to ask.

    Best Regards,
    Simon Hou

    Sunday, August 03, 2014 1:59 PM
    Moderator