Running "subreport" in SSRS from custom code

Question

Running "subreport" in SSRS from custom code

brett.walker

SSC-Addicted

Points: 475
More actions
August 6, 2015 at 8:00 am

#306404

Hello,
This question is regarding SQL Server 2008 R2 SSRS. Please let me know if this is the incorrect forum and I'll move it.
I have a balance sheet report developed and working. What I need to do is add the ability to click (or double-click) on a number and have a separate report (drill-down) open with the detail that makes up that number. I've researched drill-through reports, sub reports, etc. but can't find a way to do what I want.
I'm now thinking that I need to add custom code in the report properties, code window to do this. I'm hoping that there is a VB method I can use to call this report and pass a parameter. Pseudo-code for this function would look something like this: RunReport("DrillDownReport", "Parameter").
This seems like it should be pretty easy but I don't know what the function is in VB for running an SSRS report (if there is one).
Is this possible or am I barking up the wrong code tree?
Thanks in advance,
Brett
Forum Etiquette: How to post Reporting Services problems

[/url]

Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]

How to Post Performance Problems - by Gail Shaw
[/url]

sgmunson SSC Guru Points: 110640 More actions · Answer 1

brett.walker (8/6/2015)
Hello,
This question is regarding SQL Server 2008 R2 SSRS. Please let me know if this is the incorrect forum and I'll move it.
I have a balance sheet report developed and working. What I need to do is add the ability to click (or double-click) on a number and have a separate report (drill-down) open with the detail that makes up that number. I've researched drill-through reports, sub reports, etc. but can't find a way to do what I want.
I'm now thinking that I need to add custom code in the report properties, code window to do this. I'm hoping that there is a VB method I can use to call this report and pass a parameter. Pseudo-code for this function would look something like this: RunReport("DrillDownReport", "Parameter").
This seems like it should be pretty easy but I don't know what the function is in VB for running an SSRS report (if there is one).
Is this possible or am I barking up the wrong code tree?
Thanks in advance,
Brett

I'm thinking it's more of a report design issue. If one number justifies an entirely separate report, I start to wonder what value that single number really holds. Maybe it would be best to design the report around having detail rows whose visibility can be toggled based on an existing item in the report, where you place the toggling control at the left of a given line, and clicking on it exposes the section below because it toggles the visibility. This is fairly common on reports with summary info, and you click on a given row to get supporting details.

That said, I think I remember there being a way to invoke another SSRS report, but you'd have to know the hyperlink for it, and then just set up a control that has a hyperlink and when they click on it, that report will get opened. You can also have a subreport whose visibility is toggled by a toggle control somewhere.

There are lots of choices, but without a lot more detail on the nature of the report as well as the nature of its audience, it would be difficult to offer much in the way of specific advice AND have it turn out to be a good idea.

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

mister.magoo SSC-Forever Points: 47174 More actions · Answer 2

It's very simple.

You add an action to the textbox that contains the value. The action links to the detail report and passes parameters to it from the data row/group that populates your main report cell.

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

brett.walker SSC-Addicted Points: 475 More actions · Answer 3

Steve,

Thanks for your response. I've done "standard" sub reports, drill down, etc. This is a somewhat new type of report I'm working on. It is a financial balance sheet. The format is pretty rigid and it's not a standard report with detail data, grouping, and subtotals, etc.

For example, one line on the balance sheet might be:

This year Last year

Cash and Cash equivalents $100 $95

I need to be able to click on $100 and have it open up a separate report with the detail that makes up this number. Each line will be comprised of different accounts with different account grouping. I've got info in the record set that I can pass as a parameter to the separate report.

The separate "detail report" would look something like this:

Cash and cash equivalents

Acct Description Amount

GL101 Cash $75

GL102 Deposits $25

Hope this helps. I realize it's a very non-standard way of doing things but like I said above, this is my first foray into developing financial reports.

Thanks again,

Brett

brett.walker SSC-Addicted Points: 475 More actions · Answer 4

Thanks MM,

I'll give it a try and post the outcome.

Brett

sgmunson SSC Guru Points: 110640 More actions · Answer 5

Usually, if the number of records to appear in a separate report is small enough, there's really no reason NOT to have a section whose visibility is controlled. Your example clearly fits that bill, but it's only an example. Your audience should be taken into consideration, but if you have maybe 10 to 20 items to list, having a visible section for it isn't usually a problem, and if often far simpler than just going off to an entirely separate report. However, take a look at what Mr. Magoo just posted. I'm not sure I recall seeing Go to a Report in SSRS 2008 or not, but if it's there, your options are unlimited, so to speak...

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)