|
|
You're in the
sql server reporting services
group:Dynamic Grouping
sql server reporting services:
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
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 [quoted text, click to view] "Hari" <Hari@discussions.microsoft.com> wrote in message services.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
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. [quoted text, click to view] "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 [quoted text, click to view] "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 [quoted text, click to view] "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! [quoted text, click to view] "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 [quoted text, click to view] "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. >> > > >> > > >> > > >> >> >>
Don't see what you're looking for? Try a search.
|
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
| home · blog · groups | Questions? Comments? Contact the dn |