Dynamic Grouping

Hi all
I have a table which has employee information like
empno, empname, empsalary, empmgr. The empmgr column points to empno.
I want to create a report which will dynamically recognise the employees
under a employee and show his salary. The other requirement is that i want to
enable drill down, ie. at first one employee and his salary will appear who
is the head. On drilling down all the employee directly reporting to him
should appear and so on and so forth. Is this possible in reporting services.RS has drilldown and drillthrough. With drill down you have all the info
returned in a single dataset. You set up your grouping and then hide the
detail rows based on the previous field. The downside to this can be the
amount of information you are bringing back. You do not want to be bringing
back more than a thousand or so records (definitely not 50,000+ records). So
it depends on your situation. Drillthrough allows the user to click on a
field and automatically jump to another report, filling in the report
parameters and pulling up the report. I find that this works very well and
is intuitive for the user. They are very used to this from using the web. I
make the text in the field they should click on blue and underlined. In
books online search on drilldown and drillthrough.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Hari" <Hari@.discussions.microsoft.com> wrote in message
news:0A6360E7-1FF7-41A8-B94E-E5536AA20A01@.microsoft.com...
> Hi all
> I have a table which has employee information like
> empno, empname, empsalary, empmgr. The empmgr column points to empno.
> I want to create a report which will dynamically recognise the employees
> under a employee and show his salary. The other requirement is that i want
to
> enable drill down, ie. at first one employee and his salary will appear
who
> is the head. On drilling down all the employee directly reporting to him
> should appear and so on and so forth. Is this possible in reporting
services.|||Hi Bruce
I am able to acheive what i was looking for. But i am also facing a problem.
The first time i run the report it showed like this
empid empsalary
(+)1 2000
on drilling down on the 1 i see like this.
empid empsalary
(-)1 2000
(+)2 1000
(+)3 3000
4 4000
on drill down on 2 i get some thing like this.
empid empsalary
(-)1 2000
(-)2 1000
5 500
6 350
(+)3 3000
4 4000
Now at this point when i want to drill up to the top most item by clicking
on 1, i expect everything to be collapsed. But i am getting something like
this
empid empsalary
(+)1 2000
5 500
6 350
Which is wrong it should show only the first record. Can anybody help in
resolving this. I hope i have given you the correct picture of my problem.
Please help me with this.
"Bruce L-C [MVP]" wrote:
> RS has drilldown and drillthrough. With drill down you have all the info
> returned in a single dataset. You set up your grouping and then hide the
> detail rows based on the previous field. The downside to this can be the
> amount of information you are bringing back. You do not want to be bringing
> back more than a thousand or so records (definitely not 50,000+ records). So
> it depends on your situation. Drillthrough allows the user to click on a
> field and automatically jump to another report, filling in the report
> parameters and pulling up the report. I find that this works very well and
> is intuitive for the user. They are very used to this from using the web. I
> make the text in the field they should click on blue and underlined. In
> books online search on drilldown and drillthrough.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Hari" <Hari@.discussions.microsoft.com> wrote in message
> news:0A6360E7-1FF7-41A8-B94E-E5536AA20A01@.microsoft.com...
> > Hi all
> >
> > I have a table which has employee information like
> > empno, empname, empsalary, empmgr. The empmgr column points to empno.
> >
> > I want to create a report which will dynamically recognise the employees
> > under a employee and show his salary. The other requirement is that i want
> to
> > enable drill down, ie. at first one employee and his salary will appear
> who
> > is the head. On drilling down all the employee directly reporting to him
> > should appear and so on and so forth. Is this possible in reporting
> services.
>
>|||You can set the visibility of a row or of a field. So you set the visibility
of the row based on the field that you will have the +/- with. I have two
levels and it works as you expect it to.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Hari" <Hari@.discussions.microsoft.com> wrote in message
news:EA77BD4A-F9E3-423F-B2FA-5FBCD32C7913@.microsoft.com...
> Hi Bruce
> I am able to acheive what i was looking for. But i am also facing a
problem.
> The first time i run the report it showed like this
> empid empsalary
> (+)1 2000
> on drilling down on the 1 i see like this.
> empid empsalary
> (-)1 2000
> (+)2 1000
> (+)3 3000
> 4 4000
> on drill down on 2 i get some thing like this.
> empid empsalary
> (-)1 2000
> (-)2 1000
> 5 500
> 6 350
> (+)3 3000
> 4 4000
> Now at this point when i want to drill up to the top most item by clicking
> on 1, i expect everything to be collapsed. But i am getting something like
> this
> empid empsalary
> (+)1 2000
> 5 500
> 6 350
> Which is wrong it should show only the first record. Can anybody help in
> resolving this. I hope i have given you the correct picture of my problem.
> Please help me with this.
> "Bruce L-C [MVP]" wrote:
> > RS has drilldown and drillthrough. With drill down you have all the info
> > returned in a single dataset. You set up your grouping and then hide the
> > detail rows based on the previous field. The downside to this can be the
> > amount of information you are bringing back. You do not want to be
bringing
> > back more than a thousand or so records (definitely not 50,000+
records). So
> > it depends on your situation. Drillthrough allows the user to click on a
> > field and automatically jump to another report, filling in the report
> > parameters and pulling up the report. I find that this works very well
and
> > is intuitive for the user. They are very used to this from using the
web. I
> > make the text in the field they should click on blue and underlined. In
> > books online search on drilldown and drillthrough.
> >
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> > "Hari" <Hari@.discussions.microsoft.com> wrote in message
> > news:0A6360E7-1FF7-41A8-B94E-E5536AA20A01@.microsoft.com...
> > > Hi all
> > >
> > > I have a table which has employee information like
> > > empno, empname, empsalary, empmgr. The empmgr column points to empno.
> > >
> > > I want to create a report which will dynamically recognise the
employees
> > > under a employee and show his salary. The other requirement is that i
want
> > to
> > > enable drill down, ie. at first one employee and his salary will
appear
> > who
> > > is the head. On drilling down all the employee directly reporting to
him
> > > should appear and so on and so forth. Is this possible in reporting
> > services.
> >
> >
> >|||I have set the visibility to hidden and visibility can be toggeled by the
field that has +/- with , i.e. the employe id field. But its still not
working
"Bruce L-C [MVP]" wrote:
> You can set the visibility of a row or of a field. So you set the visibility
> of the row based on the field that you will have the +/- with. I have two
> levels and it works as you expect it to.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Hari" <Hari@.discussions.microsoft.com> wrote in message
> news:EA77BD4A-F9E3-423F-B2FA-5FBCD32C7913@.microsoft.com...
> > Hi Bruce
> >
> > I am able to acheive what i was looking for. But i am also facing a
> problem.
> >
> > The first time i run the report it showed like this
> >
> > empid empsalary
> > (+)1 2000
> >
> > on drilling down on the 1 i see like this.
> >
> > empid empsalary
> > (-)1 2000
> > (+)2 1000
> > (+)3 3000
> > 4 4000
> >
> > on drill down on 2 i get some thing like this.
> >
> > empid empsalary
> > (-)1 2000
> > (-)2 1000
> > 5 500
> > 6 350
> > (+)3 3000
> > 4 4000
> >
> > Now at this point when i want to drill up to the top most item by clicking
> > on 1, i expect everything to be collapsed. But i am getting something like
> > this
> >
> > empid empsalary
> > (+)1 2000
> > 5 500
> > 6 350
> > Which is wrong it should show only the first record. Can anybody help in
> > resolving this. I hope i have given you the correct picture of my problem.
> > Please help me with this.
> >
> > "Bruce L-C [MVP]" wrote:
> >
> > > RS has drilldown and drillthrough. With drill down you have all the info
> > > returned in a single dataset. You set up your grouping and then hide the
> > > detail rows based on the previous field. The downside to this can be the
> > > amount of information you are bringing back. You do not want to be
> bringing
> > > back more than a thousand or so records (definitely not 50,000+
> records). So
> > > it depends on your situation. Drillthrough allows the user to click on a
> > > field and automatically jump to another report, filling in the report
> > > parameters and pulling up the report. I find that this works very well
> and
> > > is intuitive for the user. They are very used to this from using the
> web. I
> > > make the text in the field they should click on blue and underlined. In
> > > books online search on drilldown and drillthrough.
> > >
> > >
> > > --
> > > Bruce Loehle-Conger
> > > MVP SQL Server Reporting Services
> > >
> > > "Hari" <Hari@.discussions.microsoft.com> wrote in message
> > > news:0A6360E7-1FF7-41A8-B94E-E5536AA20A01@.microsoft.com...
> > > > Hi all
> > > >
> > > > I have a table which has employee information like
> > > > empno, empname, empsalary, empmgr. The empmgr column points to empno.
> > > >
> > > > I want to create a report which will dynamically recognise the
> employees
> > > > under a employee and show his salary. The other requirement is that i
> want
> > > to
> > > > enable drill down, ie. at first one employee and his salary will
> appear
> > > who
> > > > is the head. On drilling down all the employee directly reporting to
> him
> > > > should appear and so on and so forth. Is this possible in reporting
> > > services.
> > >
> > >
> > >
>
>|||I don't know what to tell you. It works for me (I only go two levels deep
however, although that was the same as the example you gave). Do you have
groups too. This works in tandem with grouping.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Hari" <Hari@.discussions.microsoft.com> wrote in message
news:CB32D4AF-F7AE-4795-8E9C-CCA429565B63@.microsoft.com...
>I have set the visibility to hidden and visibility can be toggeled by the
> field that has +/- with , i.e. the employe id field. But its still not
> working
> "Bruce L-C [MVP]" wrote:
>> You can set the visibility of a row or of a field. So you set the
>> visibility
>> of the row based on the field that you will have the +/- with. I have
>> two
>> levels and it works as you expect it to.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "Hari" <Hari@.discussions.microsoft.com> wrote in message
>> news:EA77BD4A-F9E3-423F-B2FA-5FBCD32C7913@.microsoft.com...
>> > Hi Bruce
>> >
>> > I am able to acheive what i was looking for. But i am also facing a
>> problem.
>> >
>> > The first time i run the report it showed like this
>> >
>> > empid empsalary
>> > (+)1 2000
>> >
>> > on drilling down on the 1 i see like this.
>> >
>> > empid empsalary
>> > (-)1 2000
>> > (+)2 1000
>> > (+)3 3000
>> > 4 4000
>> >
>> > on drill down on 2 i get some thing like this.
>> >
>> > empid empsalary
>> > (-)1 2000
>> > (-)2 1000
>> > 5 500
>> > 6 350
>> > (+)3 3000
>> > 4 4000
>> >
>> > Now at this point when i want to drill up to the top most item by
>> > clicking
>> > on 1, i expect everything to be collapsed. But i am getting something
>> > like
>> > this
>> >
>> > empid empsalary
>> > (+)1 2000
>> > 5 500
>> > 6 350
>> > Which is wrong it should show only the first record. Can anybody help
>> > in
>> > resolving this. I hope i have given you the correct picture of my
>> > problem.
>> > Please help me with this.
>> >
>> > "Bruce L-C [MVP]" wrote:
>> >
>> > > RS has drilldown and drillthrough. With drill down you have all the
>> > > info
>> > > returned in a single dataset. You set up your grouping and then hide
>> > > the
>> > > detail rows based on the previous field. The downside to this can be
>> > > the
>> > > amount of information you are bringing back. You do not want to be
>> bringing
>> > > back more than a thousand or so records (definitely not 50,000+
>> records). So
>> > > it depends on your situation. Drillthrough allows the user to click
>> > > on a
>> > > field and automatically jump to another report, filling in the report
>> > > parameters and pulling up the report. I find that this works very
>> > > well
>> and
>> > > is intuitive for the user. They are very used to this from using the
>> web. I
>> > > make the text in the field they should click on blue and underlined.
>> > > In
>> > > books online search on drilldown and drillthrough.
>> > >
>> > >
>> > > --
>> > > Bruce Loehle-Conger
>> > > MVP SQL Server Reporting Services
>> > >
>> > > "Hari" <Hari@.discussions.microsoft.com> wrote in message
>> > > news:0A6360E7-1FF7-41A8-B94E-E5536AA20A01@.microsoft.com...
>> > > > Hi all
>> > > >
>> > > > I have a table which has employee information like
>> > > > empno, empname, empsalary, empmgr. The empmgr column points to
>> > > > empno.
>> > > >
>> > > > I want to create a report which will dynamically recognise the
>> employees
>> > > > under a employee and show his salary. The other requirement is that
>> > > > i
>> want
>> > > to
>> > > > enable drill down, ie. at first one employee and his salary will
>> appear
>> > > who
>> > > > is the head. On drilling down all the employee directly reporting
>> > > > to
>> him
>> > > > should appear and so on and so forth. Is this possible in reporting
>> > > services.
>> > >
>> > >
>> > >
>>|||Bruce,
I use the drill thru however we have discovered an issue when the report
is deployed to a portal. User has a portal screen with content for selection
on the left and display area on the right. User selects Report A for display
and Report A comes up and renders in the right hand side of the page (content
area). Then user selects item on Report A for drill thru to Report B.
Report B is replacing the entire browser window Content Selection on left and
Display area on right where Report A was. When the user hits the BACK button
on the browser they are not returned to the previous report display - they
are returned to the screen as it appeared before they selected Report A for
display. I want to be able to return to the report they drilled from (that
report takes LOTS of parameters and I dont want to have to carry them all
forward and have to code another "jump to report" just to return back again
... that seems rather lame). What am I missing here' thanks!
"Bruce L-C [MVP]" wrote:
> RS has drilldown and drillthrough. With drill down you have all the info
> returned in a single dataset. You set up your grouping and then hide the
> detail rows based on the previous field. The downside to this can be the
> amount of information you are bringing back. You do not want to be bringing
> back more than a thousand or so records (definitely not 50,000+ records). So
> it depends on your situation. Drillthrough allows the user to click on a
> field and automatically jump to another report, filling in the report
> parameters and pulling up the report. I find that this works very well and
> is intuitive for the user. They are very used to this from using the web. I
> make the text in the field they should click on blue and underlined. In
> books online search on drilldown and drillthrough.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Hari" <Hari@.discussions.microsoft.com> wrote in message
> news:0A6360E7-1FF7-41A8-B94E-E5536AA20A01@.microsoft.com...
> > Hi all
> >
> > I have a table which has employee information like
> > empno, empname, empsalary, empmgr. The empmgr column points to empno.
> >
> > I want to create a report which will dynamically recognise the employees
> > under a employee and show his salary. The other requirement is that i want
> to
> > enable drill down, ie. at first one employee and his salary will appear
> who
> > is the head. On drilling down all the employee directly reporting to him
> > should appear and so on and so forth. Is this possible in reporting
> services.
>
>