CODEWITHSUNDEEP
ssrs-2008
Vasundhara
Vasundhara

Reputation: 745

Export to Excel queries for multiple worksheets in SSRS 2008

We have two reports, one is for Consumer details and another is for consumer transaction details. Consumer transaction details report is based on the consumerId from Consumer details report.

Consumer details report displays all consumer details. consumer transaction details contain all consumer’s all transaction details.

We have created two separate reports in SSRS. We have set “Go to Report” action for consumerId field of Consumer details report which will point to the consumer transaction details report for that consumer id only. For this, we have set parameters. In Preview of Consumer details report when we click on the consumerId field, it displays transactions details for the clicked consumer id.

Now, we want two things:

  1. When we export to excel, we want that Consumer details should be in sheet1, and consumer transactions details should get displayed in sheet2, sheet3,sheet4…etc. (number of sheets should be depends on number of consumer Id available in Consumer details report).
  2. Also, when we will click on the consumerId cell of ConsumerDetails, it should redirected to the particular transaction details sheet (for that consumerId).

Is there any way to achieve this?

Upvotes: 0

Views: 446

Answers (1)

KrazzyNefarious
KrazzyNefarious

Reputation: 3230

Your first requirement can be achieved through a adding a subreport referring to the consumer transaction details and make it visible only when exported to EXCEL. If you are using SSRS 2012, you can use the following expression in the subreport visibility:-

=IIF(Globals!RenderFormat.Name = "EXCELOPENXML", False,True)

Reason to make it a subreport is when you export a report, only current viewable content will be exported (unless Hidden Expression are mentioned), not the linked reports. So when you have a subreport consumer transaction details also rendered along with the main report Consumer details, you can export them in one excel file. Make sure you apply appropriate page breaks to view data in different sheets.

As for your second requirement, since when you export the data to EXCEL, its already linked to the other report with the given link, the hyperlink will direct you to the reporturl. As far as I have known about SSRS, this cant be done. Please comment if you have any questions.

Upvotes: 1

Related Questions