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
>>