Dynamic grouping problem
in [Reports]
|
Prev: Using Reporting Services 2000 with Visual Studios 2005
Next: Help with embedded code for SSRS Report
From: msdnuser on 31 Jan 2006 09:56 Hi all, I need a certain functionality in my Reporting service report. I need a report with 4 levels of grouping above the detail level. The initial visibility of all the lower levels should be hidden expect the top most level. For all the levels toggle item is set in the group properties. There should also be a boolean parameter called "expand all" which will affect the initial visibility of the report. This report should also have dynamic grouping facility with the help of 4 parameters one for each level. Thus if the value passed to one of these groupby parameters is the word "none" then that group header row should not be shown. In all cases the detail row should be visible either by drilling down to that level or as a top level if all other levels are "none". I am partially successful in the above scenario by making the top level a mandatory one. i.e the top level can never be "none". However my problem is that if in the database a particular column supplied to report thorough one of the groupby parameters has all nulls then also the group header row should not show up. But when I try to put expression to control this using "isnothing" function, the detail row never shows up. Thus there are 3 boolean values to be controlled along with toggle in each group and detail section. How to achieve this? Please help me solve this issue. Thanks.
From: Justin Shen[MSFT] on 1 Feb 2006 03:08 Hi, Welcome to MSDN Managed NewsGroup. This is Justin from Microsoft. I do not quite understand the following: "if in the database a particular column supplied to report thorough one of the groupby parameters has all nulls then also the group header row should not show up. But when I try to put expression to control this using "isnothing" function, the detail row never shows up. " Could you elaborate your issue? Please aslo let me know the expression you set for the visiblity of the group so that I could better understand your issue. If you have any question, please feel free to let me know. Thanks & Regards, Justin Shen Microsoft Online Partner Support Get Secure! - www.microsoft.com/security When responding to posts, please "Reply to Group" via your newsreader so that others may learn and benefit from your issue. ===================================================== Business-Critical Phone Support (BCPS) provides you with technical phone support at no charge during critical LAN outages or "business down" situations. This benefit is available 24 hours a day, 7 days a week to all Microsoft technology partners in the United States and Canada. This and other support options are available here: BCPS: https://partner.microsoft.com/US/technicalsupport/supportoverview/40010469 Others: https://partner.microsoft.com/US/technicalsupport/supportoverview/ If you are outside the United States, please visit our International Support page: http://support.microsoft.com/default.aspx?scid=%2finternational.aspx. ===================================================== This posting is provided "AS IS" with no warranties, and confers no rights. -------------------- | Thread-Topic: Dynamic grouping problem | thread-index: AcYmdoUqBM3uTevuTUye6qEq3EKGnQ== | X-WBNR-Posting-Host: 38.113.18.195 | From: "=?Utf-8?B?bXNkbnVzZXI=?=" <ringt(a)nospam.nospam> | Subject: Dynamic grouping problem | Date: Tue, 31 Jan 2006 06:56:30 -0800 | Lines: 29 | Message-ID: <AE4B3F3C-C7DF-431E-9F62-5FCE1D4B82CB(a)microsoft.com> | MIME-Version: 1.0 | Content-Type: text/plain; | charset="Utf-8" | Content-Transfer-Encoding: 7bit | X-Newsreader: Microsoft CDO for Windows 2000 | Content-Class: urn:content-classes:message | Importance: normal | Priority: normal | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0 | Newsgroups: microsoft.public.sqlserver.reportingsvcs | NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250 | Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGXA03.phx.gbl | Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.reportingsvcs:67863 | X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs | | Hi all, | | I need a certain functionality in my Reporting service report. | | I need a report with 4 levels of grouping above the detail level. The | initial visibility of all the lower levels should be hidden expect the top | most level. For all the levels toggle item is set in the group properties. | There should also be a boolean parameter called "expand all" which will | affect the initial visibility of the report. | | This report should also have dynamic grouping facility with the help of 4 | parameters one for each level. Thus if the value passed to one of these | groupby parameters is the word "none" then that group header row should not | be shown. In all cases the detail row should be visible either by drilling | down to that level or as a top level if all other levels are "none". | | I am partially successful in the above scenario by making the top level a | mandatory one. i.e the top level can never be "none". However my problem is | that if in the database a particular column supplied to report thorough one | of the groupby parameters has all nulls then also the group header row should | not show up. But when I try to put expression to control this using | "isnothing" function, the detail row never shows up. | | Thus there are 3 boolean values to be controlled along with toggle in each | group and detail section. How to achieve this? | | Please help me solve this issue. | | Thanks. |
From: msdnuser on 1 Feb 2006 10:14 Hi, I have 3 parameters by name groupby2,groupby3,groupby4 which helps to create 3 dynamic groups. Also I have the top most non-dynamic group. Thus 4 group headers above my details row and no group footers. I pass the word "NONE" through one or many of the above parameters,if I dont want to see any or all of the group header. I use a parameter called expand_all to set the initial toggle state of all the groups to be expanded or collapsed. Here are the values I set for each group: 1st group header: (I reach visibility tab as follows: right click group header--edit group-visibility tab) There I set the initial visibility to be visible and no toggle item. close dialog boxes. Then I select the first group header row and in the properties window, I set the visibility(hidden) to be visible. 2nd Group Header: I reach visibility tab. Set initial visibility to be an expression ="(not parameters!expand_all.value)" and toggle item to be the name of first textbox in the 1st group header. Then I select the 2nd group header row and in the properties window, I set the visibility(hidden) to be ="IIF(parameters!groupby2.value="none",true,false) 3rd Group Header: I reach the visibility tab. Set initial visibility to be ="(not parameters!expand_all.value) and iif(parameters!groupby2.value="none",false,true) and toggle item to be name of the first text box in the 2nd group header row. Then I select the 3nd group header row and in the properties window, I set the visibility(hidden) to be ="IIF(parameters!groupby3.value="none",true,false) 4th Group Header: I reach the visibility tab. Set initial visibility to be ="(not parameters!expand_all.value) and iif(parameters!groupby3.value="none",false,true) and the toggle item to be name of the first textbox in the 3rd group header row. Then I select the 4th group header row and in the properties window, I set the visibility(hidden) to be ="IIF(parameters!groupby4.value="none",true,false) Details row: initial visibility: =(NOT Parameters!EXPAND_ALL.Value) AND IIF(PARAMETERS!GROUPBY4.VALUE="NONE",FALSE,TRUE) and the toggle item to be the name of the first text box in the 4th group header row. Then I select the details row and in the properties window, I set the visibility(hidden) to be =false. With the above set up, the report works good. But my problem is say we pass a particular database field name to groupby2 parameter. (These 4 groupby parameters are attached in the select list of the dataset of report). Now in the table if the sent column has no data just nulls, then the group header first column has just the + toggle sign or - toggle sign based on the state of toggle and no value. So I tried to suppress the group header when the field passed for that group header has null values in the database using the a condition like this in the group header row properties. =iif(parameters!groupby2.value=none or fields!groupby2databasefieldname.value is nothing,true,false) now this trick does work good when the initial visibillity is expanded. But when the initial visibility is collapsed, then the detials row always remains hidden. Never shows up. To put the problem precisely, I want to suppress the group header rows conditionally; but details row should show up under all circumstances, even if the initial state of details row is hidden. Thanks
From: Michael Cheng [MSFT] on 6 Feb 2006 05:26
Hi, Is it possible for you to generate a sample rdl file using AdventureWorks database to describe the issue more? I understand the information may be sensitive to you, my direct email address is v-mingqc(a)ONLINEmicrosoft.com (Please make sure you have removed ONLINE before you click SEND), you may send the file to me directly and I will keep secure. Thank you for your patience and cooperation. If you have any questions or concerns, don't hesitate to let me know. We are always here to be of assistance! Sincerely yours, Michael Cheng Microsoft Online Partner Support ====================================================== When responding to posts, please "Reply to Group" via your newsreader so that others may learn and benefit from your issue. ===================================================== This posting is provided "AS IS" with no warranties, and confers no rights. |