Dynamic grouping problem

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.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@.nospam.nospam>
| Subject: Dynamic grouping problem
| Date: Tue, 31 Jan 2006 06:56:30 -0800
| Lines: 29
| Message-ID: <AE4B3F3C-C7DF-431E-9F62-5FCE1D4B82CB@.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.
||||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|||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@.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.|||Quick question Michael
One of my developers wanted the .rdls for AdventureWorks from SSRS 2000 SP1
(interested in one of the features demonstrated).
How does one go about getting these (other than the chunking sprocs in the
default Report Server database)?
rob
"Michael Cheng [MSFT]" wrote:
> 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@.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.
>|||all set (helped my developer...)
just 'edit' from the http interface and copy the .rdl into a VB.NET project.
thanks anyways
rob
"tutor" wrote:
> Quick question Michael
> One of my developers wanted the .rdls for AdventureWorks from SSRS 2000 SP1
> (interested in one of the features demonstrated).
> How does one go about getting these (other than the chunking sprocs in the
> default Report Server database)?
> rob
> "Michael Cheng [MSFT]" wrote:
> > 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@.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.
> >
> >|||Michael,
Do you have the rdl based on AdventureWorks for this reporting issue? I have
a similar need.
Thanks
John
"Michael Cheng [MSFT]" wrote:
> 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@.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.
>|||Did you resolve this problem? I have the exact same issue and would
appreciate any help.
"msdnuser" wrote:
> 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.