need help

Where can I get the matrix report tutorial in report builder? Thanks
Try:
http://msdn2.microsoft.com/en-gb/library/aa337450.aspx
-Sue
On Tue, 21 Nov 2006 11:37:35 +0800, "Norman Yuan"
<NotReal@.NotReal.not> wrote:

>Where can I get the matrix report tutorial in report builder? Thanks
>

need help

Where can I get the matrix report tutorial in report builder? ThanksTry:
http://msdn2.microsoft.com/en-gb/library/aa337450.aspx
-Sue
On Tue, 21 Nov 2006 11:37:35 +0800, "Norman Yuan"
<NotReal@.NotReal.not> wrote:
>Where can I get the matrix report tutorial in report builder? Thanks
>sql

need help

Where can I get the matrix report tutorial in report builder? ThanksTry:
http://msdn2.microsoft.com/en-gb/library/aa337450.aspx
-Sue
On Tue, 21 Nov 2006 11:37:35 +0800, "Norman Yuan"
<NotReal@.NotReal.not> wrote:

>Where can I get the matrix report tutorial in report builder? Thanks
>

Need a good start on matrix type report, please

I've got the following data I need to report in this format:
Stage P Screen RE-Screen GrdTotal
user1 1 2
3
user2 2 1
6
user3 1 2 1
4
From a sproc the data looks like:
User Type
user1 Stage
user1 RE-Screen
user1 RE-Screen
user2 P-Screen
user2 P-Screen
User2 RE-Screen
I created a report with a subreport for each type count but I haven't been
able to
figure out a way to total up the output from the Sub-Reports. In Crystal I
think I remember passing a gloval value between the report and sub. From
reading on this forum it sounds like there is no way to do this. If you know
how please let me know, then I would have it.
The other ideas is mabe I should have a different SQL and do the
accumulating before I enter the report or I'm gettign ready to write the
report with a grouping on each type. The report has 6 types however.
I haven't worked with Matrix's much and that data regions seems to fit this
need, but...what do you all think?
THANKS!!!
Robert HansenA matrix would exactly do what you want.
The User field would become a matrix row grouping (just drop the field on
row group section of the matrix). The Type field would be a matrix column
grouping with a subtotal (after dropping the field into the column group
section, right click and select "Subtotal"). For the matrix cell you would
use an expression like =Count(Fields!User.Value).
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Bob Hansen" <BobHansen@.discussions.microsoft.com> wrote in message
news:396F1769-F8F5-4AFD-A659-3A84F97BA142@.microsoft.com...
> I've got the following data I need to report in this format:
> Stage P Screen RE-Screen GrdTotal
> user1 1 2
> 3
> user2 2 1
> 6
> user3 1 2 1
> 4
> From a sproc the data looks like:
> User Type
> user1 Stage
> user1 RE-Screen
> user1 RE-Screen
> user2 P-Screen
> user2 P-Screen
> User2 RE-Screen
> I created a report with a subreport for each type count but I haven't been
> able to
> figure out a way to total up the output from the Sub-Reports. In Crystal
> I
> think I remember passing a gloval value between the report and sub. From
> reading on this forum it sounds like there is no way to do this. If you
> know
> how please let me know, then I would have it.
> The other ideas is mabe I should have a different SQL and do the
> accumulating before I enter the report or I'm gettign ready to write the
> report with a grouping on each type. The report has 6 types however.
> I haven't worked with Matrix's much and that data regions seems to fit
> this
> need, but...what do you all think?
> THANKS!!!
> Robert Hansen|||Thanks!!! Robert
The one thing about a matrix that is throwing me for a loop, is that I need
to display all my Users even if thay have NO activity records (That is
within a data
range) I need their name to show up with blanks counts and 0 GrdTot.
I can do this so nicely with subreports and a real simple table of all users
to drive the report to make all user names show on each row.
I haven't been able to write an SQL to get the data in that form, where I've
got all Users with a row in my dataset. Is there any custom programming
way to get the totals from the subreports? I'm going to try to create a
more complex SQL to get the data I need for thre port so it can include ALL
USERS.
Thanks again for your help!!!
--
Robert Hansen
"Robert Bruckner [MSFT]" wrote:
> A matrix would exactly do what you want.
> The User field would become a matrix row grouping (just drop the field on
> row group section of the matrix). The Type field would be a matrix column
> grouping with a subtotal (after dropping the field into the column group
> section, right click and select "Subtotal"). For the matrix cell you would
> use an expression like =Count(Fields!User.Value).
>
> -- Robert
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Bob Hansen" <BobHansen@.discussions.microsoft.com> wrote in message
> news:396F1769-F8F5-4AFD-A659-3A84F97BA142@.microsoft.com...
> > I've got the following data I need to report in this format:
> >
> > Stage P Screen RE-Screen GrdTotal
> > user1 1 2
> > 3
> > user2 2 1
> > 6
> > user3 1 2 1
> > 4
> >
> > From a sproc the data looks like:
> > User Type
> > user1 Stage
> > user1 RE-Screen
> > user1 RE-Screen
> > user2 P-Screen
> > user2 P-Screen
> > User2 RE-Screen
> >
> > I created a report with a subreport for each type count but I haven't been
> > able to
> > figure out a way to total up the output from the Sub-Reports. In Crystal
> > I
> > think I remember passing a gloval value between the report and sub. From
> > reading on this forum it sounds like there is no way to do this. If you
> > know
> > how please let me know, then I would have it.
> >
> > The other ideas is mabe I should have a different SQL and do the
> > accumulating before I enter the report or I'm gettign ready to write the
> > report with a grouping on each type. The report has 6 types however.
> >
> > I haven't worked with Matrix's much and that data regions seems to fit
> > this
> > need, but...what do you all think?
> >
> > THANKS!!!
> > Robert Hansen
>
>|||If you want all users to show up, you need to use an OUTER JOIN in your SQL
query to get them into the resulting data set. Depending on the data source,
the actual SQL syntax for outer joins will vary.
You will also need to modify the expression in the matrix cell to take into
account those users without activity, but showing up in the data set.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Bob Hansen" <BobHansen@.discussions.microsoft.com> wrote in message
news:7735073D-1D95-4D72-B66A-AE56F696E2EE@.microsoft.com...
> Thanks!!! Robert
> The one thing about a matrix that is throwing me for a loop, is that I
> need
> to display all my Users even if thay have NO activity records (That is
> within a data
> range) I need their name to show up with blanks counts and 0 GrdTot.
> I can do this so nicely with subreports and a real simple table of all
> users
> to drive the report to make all user names show on each row.
> I haven't been able to write an SQL to get the data in that form, where
> I've
> got all Users with a row in my dataset. Is there any custom programming
> way to get the totals from the subreports? I'm going to try to create a
> more complex SQL to get the data I need for thre port so it can include
> ALL
> USERS.
> Thanks again for your help!!!
> --
> Robert Hansen
>
> "Robert Bruckner [MSFT]" wrote:
>> A matrix would exactly do what you want.
>> The User field would become a matrix row grouping (just drop the field on
>> row group section of the matrix). The Type field would be a matrix column
>> grouping with a subtotal (after dropping the field into the column group
>> section, right click and select "Subtotal"). For the matrix cell you
>> would
>> use an expression like =Count(Fields!User.Value).
>>
>> -- Robert
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>>
>> "Bob Hansen" <BobHansen@.discussions.microsoft.com> wrote in message
>> news:396F1769-F8F5-4AFD-A659-3A84F97BA142@.microsoft.com...
>> > I've got the following data I need to report in this format:
>> >
>> > Stage P Screen RE-Screen GrdTotal
>> > user1 1 2
>> > 3
>> > user2 2 1
>> > 6
>> > user3 1 2 1
>> > 4
>> >
>> > From a sproc the data looks like:
>> > User Type
>> > user1 Stage
>> > user1 RE-Screen
>> > user1 RE-Screen
>> > user2 P-Screen
>> > user2 P-Screen
>> > User2 RE-Screen
>> >
>> > I created a report with a subreport for each type count but I haven't
>> > been
>> > able to
>> > figure out a way to total up the output from the Sub-Reports. In
>> > Crystal
>> > I
>> > think I remember passing a gloval value between the report and sub.
>> > From
>> > reading on this forum it sounds like there is no way to do this. If
>> > you
>> > know
>> > how please let me know, then I would have it.
>> >
>> > The other ideas is mabe I should have a different SQL and do the
>> > accumulating before I enter the report or I'm gettign ready to write
>> > the
>> > report with a grouping on each type. The report has 6 types however.
>> >
>> > I haven't worked with Matrix's much and that data regions seems to fit
>> > this
>> > need, but...what do you all think?
>> >
>> > THANKS!!!
>> > Robert Hansen
>>

navigation on subtotal

I have a matrix and a subtotal footer summing up a group of numbers. Each row has a primary key. I use the primary key to drill-through another report through "Navigation".

Unfortunately, the generated subtotal column also have a link but it has a primary key of whatever the first row is, which shouldn't have a primary key. So I can drill-through the subtotal but it returns the wrong result.

I have not been able to override the subtotal to specify a new link with different paramters.

Anybody can help would be great!

Thanks.

In the place where you define the drillthrough action with its parameters, you have to use the InScope(...) function to determine if your are in a subtotal or not.

Please check the MSDN documentation about the InScope function:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSCREATE/htm/rcr_creating_expressions_v1_0jmt.asp

With InScope you can determine the current scope of a matrix cell. Note: a matrix cell is "in scope" of column and row groupings, so you need at least two InScope function calls in the case where you have one dynamic row and one dynamic column grouping. E.g.
=iif(InScope("ColumnGroup1"), iif(InScope("RowGroup1"), "In Cell", "In Subtotal of RowGroup1"), iif(InScope("RowGroup1"), "In Subtotal of ColumnGroup1", "In Subtotal of entire matrix"))

-- Robert

|||Thanks for the suggestion. But I have two dynamic rows, one for SubTotal and one for Grand Total.

<DynamicRows>
<Grouping Name="GrandTotal">
....
<DynamicRows>
<Grouping Name="SubTotal">
...

I am having a hard time to link it to a correct report as it appears that GrandTotal is in scope of SubTotal. I am not sure to put that in the omit node as well.

If you can give me some instructions that would be great.

Thanks.

Jason
|||there are two subtotals, one is the rightmost column and the other is the bottom row. How can I disable the navigation for these two subtotals and still have navigation "In Cell"? Thanks if you can help or point out some resouces that I can go check.|||

Use the InScope function as described earlier in this thread.

For the cells, you would generate the desired hyperlink or drillthrough link. For subtotal scope you would just return Nothing (i.e. VB equivalent of null) in the IIF arguments. If a hyperlink expression evaluates to Nothing for a particular cell, the hyperlink action will be removed entirely.

-- Robert

|||

Hi, Robert,

The problem is still not solved. I still want the subtotal number shows on rightmost column and bottom row. The way that you told me, I will get empty column and row on the end. Let me try an example here:

co1 col2 col3 subtotal

row1 3 5 7 15

row2 1 2 3 6

subtotal 4 7 10 21

3, 5, 7, 1, 2, and 3, will have a hyperlink to jump to another report show detailed page, but I wanto 4, 7, 10, 15, 6, and 21 to show the number but disable the hyplerlinks.

Thanks!

Henry

|||

Yes, that should work fine.

For the matrix cell, you use an expression that shows calculates the value (e.g. =Sum(Fields!A.Value)).

Then, on that textbox add a navigation action. Only for the hyperlink expression you would use the complex expression using IIF calls and InScope calls.

Hope this clarifies it.

|||

Hi, Robert,

It works now. Thanks!

The key point: "sum(fields!a.value)" in Matrix cell and complex expression using iif and inscope in navigation action.

Henry

|||

hi,

can u please send me the query you used.

and please also gimme a explanation if possible.

Thanks in advance,

Vinu

navigation on subtotal

I have a matrix and a subtotal footer summing up a group of numbers. Each row has a primary key. I use the primary key to drill-through another report through "Navigation".

Unfortunately, the generated subtotal column also have a link but it has a primary key of whatever the first row is, which shouldn't have a primary key. So I can drill-through the subtotal but it returns the wrong result.

I have not been able to override the subtotal to specify a new link with different paramters.

Anybody can help would be great!

Thanks.

In the place where you define the drillthrough action with its parameters, you have to use the InScope(...) function to determine if your are in a subtotal or not.

Please check the MSDN documentation about the InScope function:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSCREATE/htm/rcr_creating_expressions_v1_0jmt.asp

With InScope you can determine the current scope of a matrix cell. Note: a matrix cell is "in scope" of column and row groupings, so you need at least two InScope function calls in the case where you have one dynamic row and one dynamic column grouping. E.g.
=iif(InScope("ColumnGroup1"), iif(InScope("RowGroup1"), "In Cell", "In Subtotal of RowGroup1"), iif(InScope("RowGroup1"), "In Subtotal of ColumnGroup1", "In Subtotal of entire matrix"))

-- Robert

|||Thanks for the suggestion. But I have two dynamic rows, one for SubTotal and one for Grand Total.

<DynamicRows>
<Grouping Name="GrandTotal">
....
<DynamicRows>
<Grouping Name="SubTotal">
...

I am having a hard time to link it to a correct report as it appears that GrandTotal is in scope of SubTotal. I am not sure to put that in the omit node as well.

If you can give me some instructions that would be great.

Thanks.

Jason|||there are two subtotals, one is the rightmost column and the other is the bottom row. How can I disable the navigation for these two subtotals and still have navigation "In Cell"? Thanks if you can help or point out some resouces that I can go check.|||

Use the InScope function as described earlier in this thread.

For the cells, you would generate the desired hyperlink or drillthrough link. For subtotal scope you would just return Nothing (i.e. VB equivalent of null) in the IIF arguments. If a hyperlink expression evaluates to Nothing for a particular cell, the hyperlink action will be removed entirely.

-- Robert

|||

Hi, Robert,

The problem is still not solved. I still want the subtotal number shows on rightmost column and bottom row. The way that you told me, I will get empty column and row on the end. Let me try an example here:

co1 col2 col3 subtotal

row1 3 5 7 15

row2 1 2 3 6

subtotal 4 7 10 21

3, 5, 7, 1, 2, and 3, will have a hyperlink to jump to another report show detailed page, but I wanto 4, 7, 10, 15, 6, and 21 to show the number but disable the hyplerlinks.

Thanks!

Henry

|||

Yes, that should work fine.

For the matrix cell, you use an expression that shows calculates the value (e.g. =Sum(Fields!A.Value)).

Then, on that textbox add a navigation action. Only for the hyperlink expression you would use the complex expression using IIF calls and InScope calls.

Hope this clarifies it.

|||

Hi, Robert,

It works now. Thanks!

The key point: "sum(fields!a.value)" in Matrix cell and complex expression using iif and inscope in navigation action.

Henry

|||

hi,

can u please send me the query you used.

and please also gimme a explanation if possible.

Thanks in advance,

Vinu

Navigation on Matrix Subtotal

Here's a sample matrix:

Men Women Total

Full Professor 36 12 48

Assoc. Professor 16 9 25

Assistant Professor 11 14 25

Total 63 35 98

Now, it's easy enough to make the values clickable so that somebody can drill down to a report that shows detail about the people. I have also discovered how to turn off clickability on the totals. However, what I really want is for the totals to be clickable so that, for example, if I click on the 63, I see a report that shows all men. Likewise, If I click on the 48, I want to see a report that shows all Full Professors. What currently happens when the totals are clickable is that if I click on the 63, I get all men who are full professors (36 records instead of 63). If I click on the 48, I get all Full Professors who are men. (36 records instead of 48).

Is there any way to send different parameters (or even no parameters) to the secondary report if the subtotals are clicked instead of the regular results?

Thanks in advance!

Daniel,

If your total link you should be passing the SubTtotal report field value - ReportItems!MySubtotal.value and not the Fields!MyMatrix.value.

I hope this helps.

Carl

|||

Thanks for your reply, Carl.

The problem is that the matrix only has one field on which to create the navigation... the data field. The total field is generated automatically, so I don't see how it's possible to creating a unique link for the total itself. If there is a way, please tell me how.

Thanks,
Dan

|||

Yes,

you can just past the "ReportItems!MyField.value" if you click on the "36" it passes "36" if you click on the "58" it passes "58"

Ham

|||

Thanks for your assistance.

But... I'm not sure we're connecting here. In Layout View, the matrix has one data field. I right click on that field and choose Properties. Then I click on the Navigation tab and in the "Jump to report" menu, I choose the name of my sub report. Then I click on Parameters and add the two parameters, =Fields!Gender.Value and =Fields!JobEEO.Value.

Now, when I Preview my report, every number is clickable, including the subtotals. It's just that clicking on the subtotals doesn't bring the correct result, as described in my first post.

I'm trying desperately to explain this so that you can see it, but I have a feeling I'm not doing a very good job...

|||

dj,

Go to where you add the two parameters in the navigation, In the parameter value, select expression, in your expression, you can type " ReportItems! " your intellisense will then allow your to select the Report Field you want to pass.

Ham

|||

The report field values are meaningless as parameters, though. What I really am saying when I click on the 63 is: "Show me a list of all Full Professors, Associate Professors, and Assistant Professors who are Male."

So the parameters I need to pass are

1) JobEEO = Full Professor, Associate Professor, Assistant Professor

2) Gender = Male

|||

Hi,

I think you need to use the InScope function to determine which parameter values to pass.

This link may help:

http://msdn2.microsoft.com/en-us/library/aa255807(sql.80).aspx

Ian.

|||

I have finally figured this out and I though I would pass it on to anyone who needs it in the future.

The best way to deal with this situation is

1) On the sub-report, for a multi-valued parameter, make every option the default. So in my example, the default for the Job EEO would be Full Professors, Associate Professors, and Assistant Professors and the default value for Gender would be both male and female.

2) On the first report, only pass the parameter if its value is in scope. This is accomplished by editing the Omit property of the parameter you are passing... setting it to something like: =Not(InScope("matrix1_Gender"))

This means that if there is no distinct value for the gender, the parameter will not be passed. The subreport will then show data for both genders because that is the subreport's default.

If making every option in a subreport's parameter the default doesnt' work for your needs, there is another way. On the parameter that's being passed out of the first report, you can edit the expression to something like this:

=Iif(InScope("matrix1_Gender"), Fields!Gender.Value, Split("Male,Female", ","))

This, translated into English, says, "If you clicked on a field related to a specific gender, then send that gender to the subreport. Otherwise, send both Male and Female to the subreport."

Navigation on Matrix Subtotal

Here's a sample matrix:

Men Women Total

Full Professor 36 12 48

Assoc. Professor 16 9 25

Assistant Professor 11 14 25

Total 63 35 98

Now, it's easy enough to make the values clickable so that somebody can drill down to a report that shows detail about the people. I have also discovered how to turn off clickability on the totals. However, what I really want is for the totals to be clickable so that, for example, if I click on the 63, I see a report that shows all men. Likewise, If I click on the 48, I want to see a report that shows all Full Professors. What currently happens when the totals are clickable is that if I click on the 63, I get all men who are full professors (36 records instead of 63). If I click on the 48, I get all Full Professors who are men. (36 records instead of 48).

Is there any way to send different parameters (or even no parameters) to the secondary report if the subtotals are clicked instead of the regular results?

Thanks in advance!

Daniel,

If your total link you should be passing the SubTtotal report field value - ReportItems!MySubtotal.value and not the Fields!MyMatrix.value.

I hope this helps.

Carl

|||

Thanks for your reply, Carl.

The problem is that the matrix only has one field on which to create the navigation... the data field. The total field is generated automatically, so I don't see how it's possible to creating a unique link for the total itself. If there is a way, please tell me how.

Thanks,
Dan

|||

Yes,

you can just past the "ReportItems!MyField.value" if you click on the "36" it passes "36" if you click on the "58" it passes "58"

Ham

|||

Thanks for your assistance.

But... I'm not sure we're connecting here. In Layout View, the matrix has one data field. I right click on that field and choose Properties. Then I click on the Navigation tab and in the "Jump to report" menu, I choose the name of my sub report. Then I click on Parameters and add the two parameters, =Fields!Gender.Value and =Fields!JobEEO.Value.

Now, when I Preview my report, every number is clickable, including the subtotals. It's just that clicking on the subtotals doesn't bring the correct result, as described in my first post.

I'm trying desperately to explain this so that you can see it, but I have a feeling I'm not doing a very good job...

|||

dj,

Go to where you add the two parameters in the navigation, In the parameter value, select expression, in your expression, you can type " ReportItems! " your intellisense will then allow your to select the Report Field you want to pass.

Ham

|||

The report field values are meaningless as parameters, though. What I really am saying when I click on the 63 is: "Show me a list of all Full Professors, Associate Professors, and Assistant Professors who are Male."

So the parameters I need to pass are

1) JobEEO = Full Professor, Associate Professor, Assistant Professor

2) Gender = Male

|||

Hi,

I think you need to use the InScope function to determine which parameter values to pass.

This link may help:

http://msdn2.microsoft.com/en-us/library/aa255807(sql.80).aspx

Ian.

|||

I have finally figured this out and I though I would pass it on to anyone who needs it in the future.

The best way to deal with this situation is

1) On the sub-report, for a multi-valued parameter, make every option the default. So in my example, the default for the Job EEO would be Full Professors, Associate Professors, and Assistant Professors and the default value for Gender would be both male and female.

2) On the first report, only pass the parameter if its value is in scope. This is accomplished by editing the Omit property of the parameter you are passing... setting it to something like: =Not(InScope("matrix1_Gender"))

This means that if there is no distinct value for the gender, the parameter will not be passed. The subreport will then show data for both genders because that is the subreport's default.

If making every option in a subreport's parameter the default doesnt' work for your needs, there is another way. On the parameter that's being passed out of the first report, you can edit the expression to something like this:

=Iif(InScope("matrix1_Gender"), Fields!Gender.Value, Split("Male,Female", ","))

This, translated into English, says, "If you clicked on a field related to a specific gender, then send that gender to the subreport. Otherwise, send both Male and Female to the subreport."

Navigation on Matrix Subtotal

Here's a sample matrix:

Men Women Total

Full Professor 36 12 48

Assoc. Professor 16 9 25

Assistant Professor 11 14 25

Total 63 35 98

Now, it's easy enough to make the values clickable so that somebody can drill down to a report that shows detail about the people. I have also discovered how to turn off clickability on the totals. However, what I really want is for the totals to be clickable so that, for example, if I click on the 63, I see a report that shows all men. Likewise, If I click on the 48, I want to see a report that shows all Full Professors. What currently happens when the totals are clickable is that if I click on the 63, I get all men who are full professors (36 records instead of 63). If I click on the 48, I get all Full Professors who are men. (36 records instead of 48).

Is there any way to send different parameters (or even no parameters) to the secondary report if the subtotals are clicked instead of the regular results?

Thanks in advance!

Daniel,

If your total link you should be passing the SubTtotal report field value - ReportItems!MySubtotal.value and not the Fields!MyMatrix.value.

I hope this helps.

Carl

|||

Thanks for your reply, Carl.

The problem is that the matrix only has one field on which to create the navigation... the data field. The total field is generated automatically, so I don't see how it's possible to creating a unique link for the total itself. If there is a way, please tell me how.

Thanks,
Dan

|||

Yes,

you can just past the "ReportItems!MyField.value" if you click on the "36" it passes "36" if you click on the "58" it passes "58"

Ham

|||

Thanks for your assistance.

But... I'm not sure we're connecting here. In Layout View, the matrix has one data field. I right click on that field and choose Properties. Then I click on the Navigation tab and in the "Jump to report" menu, I choose the name of my sub report. Then I click on Parameters and add the two parameters, =Fields!Gender.Value and =Fields!JobEEO.Value.

Now, when I Preview my report, every number is clickable, including the subtotals. It's just that clicking on the subtotals doesn't bring the correct result, as described in my first post.

I'm trying desperately to explain this so that you can see it, but I have a feeling I'm not doing a very good job...

|||

dj,

Go to where you add the two parameters in the navigation, In the parameter value, select expression, in your expression, you can type " ReportItems! " your intellisense will then allow your to select the Report Field you want to pass.

Ham

|||

The report field values are meaningless as parameters, though. What I really am saying when I click on the 63 is: "Show me a list of all Full Professors, Associate Professors, and Assistant Professors who are Male."

So the parameters I need to pass are

1) JobEEO = Full Professor, Associate Professor, Assistant Professor

2) Gender = Male

|||

Hi,

I think you need to use the InScope function to determine which parameter values to pass.

This link may help:

http://msdn2.microsoft.com/en-us/library/aa255807(sql.80).aspx

Ian.

|||

I have finally figured this out and I though I would pass it on to anyone who needs it in the future.

The best way to deal with this situation is

1) On the sub-report, for a multi-valued parameter, make every option the default. So in my example, the default for the Job EEO would be Full Professors, Associate Professors, and Assistant Professors and the default value for Gender would be both male and female.

2) On the first report, only pass the parameter if its value is in scope. This is accomplished by editing the Omit property of the parameter you are passing... setting it to something like: =Not(InScope("matrix1_Gender"))

This means that if there is no distinct value for the gender, the parameter will not be passed. The subreport will then show data for both genders because that is the subreport's default.

If making every option in a subreport's parameter the default doesnt' work for your needs, there is another way. On the parameter that's being passed out of the first report, you can edit the expression to something like this:

=Iif(InScope("matrix1_Gender"), Fields!Gender.Value, Split("Male,Female", ","))

This, translated into English, says, "If you clicked on a field related to a specific gender, then send that gender to the subreport. Otherwise, send both Male and Female to the subreport."

|||Thanks !
It works !