1.
Objective
The objective is to show the multiple report items in the same report
pane while viewing the report in report viewer or report manager but each
report item should export to separate sheet in an worksheet while exporting to
excel.
1)
Below is the sample report in which there are
two tablix report items are there. While viewing the report, both tablix report
should display in the same page
2)
When I export to excel, each report item
should export to separate sheet in an excel work sheet
2.
Solution
We are going to use the following to
achieve this requirement
1)
Conditional visibility based on
Globals!RenderFormat.Name = "EXCELOPENXML"
2)
Conditional page break based on Globals!RenderFormat.Name
= "EXCELOPENXML"
3)
Multiple redundant reporting items
3.
Implementation
Create the redundant report items in the reporting pane
1)
Set the conditional visibility to both the
reporting items in the following way
a.
Set the conditional visibility = false for
original report item when Render format = excel
b.
Set the conditional visibility = true for duplicate
report item when Render format = excel
It means only one report item will be
visible when we view the report and only one report item will be visible when
we export the report to excel. In this case, duplicate report item will be
displayed while exporting to excel whereas original report item will be
displayed while viewing the report in report viewer
To set the tablix visibility, follow
the following steps:
i.
Right click on the tablix
ii.
Go to visibility è choose show & hide based on an
expression. Put the below expression & click ok
=IIF(Globals!RenderFormat.Name
= "EXCELOPENXML",TRUE,FALSE)
iii.
Repeat the same steps for duplicate report
item and change expression to below
=IIF(Globals!RenderFormat.Name
= "EXCELOPENXML",FALSE,TRUE)
2)
Set the page break option to report item which
is visible only when we export the report to excel. In this case, Page break
will be implemented at the start of duplicate report item
3)
Follow the following steps for conditional
page break
i.
Go to the property of duplicate report itemèPage Breakè
ii.
Set Break Location to Start
Disbale =IIF(Globals!RenderFormat.Name
= "EXCELOPENXML",FALSE,TRUE)
4.
Conclusion
By using the conditional visibility &
conditional page break & redundant report items, we can display the
multiple report items in the same reporting pane while displaying in report viewer
& can display in separate sheets while exporting to excel.
No comments:
Post a Comment