stored procedures - How can I supply report parameters to ancillary reports from a "base" SSRS report? -

i need have several (four) reports identical except value of particular parameter. don't want user have enter parameters manually, though.

when enter following parameters:

beatles ------- begdate: 1962 enddate: 1970 unit: beatles 

i want 4 reports run, using these parameters:

lennon ------ begdate: 1962 enddate: 1970 unit: john lennon  mccartney --------- begdate: 1962 enddate: 1970 unit: paul mccartney  harrison -------- begdate: 1962 enddate: 1970 unit: george harrison  starr ----- begdate: 1962 enddate: 1970 unit: ringo starr 

so when user enters 3 parameters, first 2 ("date") parameters , appropriate-for-the-ancillary-report "unit" parameter ("john lennon" first, "paul mccartney" second, etc.) passed.

the report "robot" says after person enters "beatles" unit: "oh, entered 'beatles'! i'll pass 'john lennon' unit parameter first report, 'paul mccartney' unit parameter second report, " (etc.).

i tried doing supplying literal values inside practically-the-same stored procedures (as expounded on extent here, didn't work @ all.

so i'm looking other way put multiple related distinct reports on same surface. ultimate goal see that, when ran , exported excel, each report (lennon, mccartney, etc.) display on own sheet in excel file.

for now, though, need know how pass appropriate parameters 1 report ("beatles") others ("lennon", etc.). i'm thinking of these distinct reports embedded in 1 report project/page, perhaps needed report , bunch of subreports (if you're reading this, know better inner workings , vagaries of ssrs).

so, how can supply report parameters ancillary (sub-?) reports "base" ssrs report - or there another/better way accomplish this?

i have solution may work you.

create report 3 parameters have mention above. add report dataset take 3 parameters , return dataset similar this:

band      member          start   end     page beatles   john lennon     1962    1970    lennon beatles   paul mccartney  1962    1970    mccartney … 

in report body add list toolbox. set dataset name of list name of dataset above. in row groups pain, there row labelled (details) list. right-click on row , choose group properties context menu. add new group , choose page column group on. go page breaks section of group properties dialog , check between each instance of group. add sort if like, click ok button.

make sure (details) row in row groups selected, , group option in properties pain. expand that, , page name setting. add expression field (screen cap below, too):

=fields!page.value 

this make each page in report have value name. so, when export excel, each sheet have own name based on value.

add text boxes inside list , set values fields need on each page. in case page, start, end , member. add labels if match have in question.

as long dataset returns 4 expected rows when choose right parameters, should end report has 4 pages, 1 each band member. when export excel, have 4 sheets , should have names page field.

hope helps you!