dynamic grouping

Hi,
I have a report with grouping. I am passing a parameter via asp.net and if
that parameter @.paramrep=7 then i want to group by type and by region
otherwise i want to group only by region.
I have looked at Chris Hay's example but I am still not sure i do get it to
work as I keep on getting an error: the expression referenced a non-existing
field in the fields collection.
This is my expression:
=iif(Parameters!Paramreport.Value
=7,1,Fields(iif(Parameters!Paramreport.Value =7,
"region",Parameters!Paramreport.Value)).Value)
I am not sure what I am doing wrong as basically I didn't really understand
the article.
I would appreciate any help offered.
ThanksWhile I haven't seen the example you mention, I often use functions for
dynamic grouping ie...( my syntax here might be bad...)
Public function GetGroups(Byref Groupid as integer, Byref Paramval as
Integer) as String
Switch Paramval
Case 7 If Groupid = 1 Then
Return("Price")
Else Return("Productname")
End IF
Case Else If Groupid = 1 Then
Return("Othercol1")
Else Return("Othercol2")
End IF
End
End
THen in the Grouping dialog
=Fields(GetGroups(1,Parameters!Parametername.Value)).Value
=Fields(GetGroups(2,Parameters!Parametername.Value)).Value
Hope this helps... By the way, sometimes I have to dynamically change the
sort to match the grouping at the highest level, otherwise I have gotten an
error...But the sort is handled the same way..
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"collie" <collie@.discussions.microsoft.com> wrote in message
news:416E56D8-9856-412A-8381-A7BBD1DCBAC5@.microsoft.com...
> Hi,
> I have a report with grouping. I am passing a parameter via asp.net and if
> that parameter @.paramrep=7 then i want to group by type and by region
> otherwise i want to group only by region.
> I have looked at Chris Hay's example but I am still not sure i do get it
to
> work as I keep on getting an error: the expression referenced a
non-existing
> field in the fields collection.
> This is my expression:
> =iif(Parameters!Paramreport.Value
> =7,1,Fields(iif(Parameters!Paramreport.Value =7,
> "region",Parameters!Paramreport.Value)).Value)
> I am not sure what I am doing wrong as basically I didn't really
understand
> the article.
> I would appreciate any help offered.
> Thanks|||Hi,
Thanks so much for your response.
I have a few questions about your code if you could please clarify (I feel
stupid for asking but...)
Ok here goes :-)
What do you mean by groupid such as groupid=1?
What is price? A field name to group by if groupid=1?
Parmetername in my case would be the parameter that i send from asp.net
@.paramrep=7 correct?
Thanks
"Wayne Snyder" wrote:
> While I haven't seen the example you mention, I often use functions for
> dynamic grouping ie...( my syntax here might be bad...)
> Public function GetGroups(Byref Groupid as integer, Byref Paramval as
> Integer) as String
> Switch Paramval
> Case 7 If Groupid = 1 Then
> Return("Price")
> Else Return("Productname")
> End IF
> Case Else If Groupid = 1 Then
> Return("Othercol1")
> Else Return("Othercol2")
> End IF
> End
> End
>
> THen in the Grouping dialog
> =Fields(GetGroups(1,Parameters!Parametername.Value)).Value
> =Fields(GetGroups(2,Parameters!Parametername.Value)).Value
> Hope this helps... By the way, sometimes I have to dynamically change the
> sort to match the grouping at the highest level, otherwise I have gotten an
> error...But the sort is handled the same way..
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "collie" <collie@.discussions.microsoft.com> wrote in message
> news:416E56D8-9856-412A-8381-A7BBD1DCBAC5@.microsoft.com...
> > Hi,
> >
> > I have a report with grouping. I am passing a parameter via asp.net and if
> > that parameter @.paramrep=7 then i want to group by type and by region
> > otherwise i want to group only by region.
> > I have looked at Chris Hay's example but I am still not sure i do get it
> to
> > work as I keep on getting an error: the expression referenced a
> non-existing
> > field in the fields collection.
> > This is my expression:
> > =iif(Parameters!Paramreport.Value
> > =7,1,Fields(iif(Parameters!Paramreport.Value =7,
> > "region",Parameters!Paramreport.Value)).Value)
> > I am not sure what I am doing wrong as basically I didn't really
> understand
> > the article.
> >
> > I would appreciate any help offered.
> >
> > Thanks
>
>|||Wayne your code was a great help.
Thanks :-)
"Wayne Snyder" wrote:
> While I haven't seen the example you mention, I often use functions for
> dynamic grouping ie...( my syntax here might be bad...)
> Public function GetGroups(Byref Groupid as integer, Byref Paramval as
> Integer) as String
> Switch Paramval
> Case 7 If Groupid = 1 Then
> Return("Price")
> Else Return("Productname")
> End IF
> Case Else If Groupid = 1 Then
> Return("Othercol1")
> Else Return("Othercol2")
> End IF
> End
> End
>
> THen in the Grouping dialog
> =Fields(GetGroups(1,Parameters!Parametername.Value)).Value
> =Fields(GetGroups(2,Parameters!Parametername.Value)).Value
> Hope this helps... By the way, sometimes I have to dynamically change the
> sort to match the grouping at the highest level, otherwise I have gotten an
> error...But the sort is handled the same way..
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "collie" <collie@.discussions.microsoft.com> wrote in message
> news:416E56D8-9856-412A-8381-A7BBD1DCBAC5@.microsoft.com...
> > Hi,
> >
> > I have a report with grouping. I am passing a parameter via asp.net and if
> > that parameter @.paramrep=7 then i want to group by type and by region
> > otherwise i want to group only by region.
> > I have looked at Chris Hay's example but I am still not sure i do get it
> to
> > work as I keep on getting an error: the expression referenced a
> non-existing
> > field in the fields collection.
> > This is my expression:
> > =iif(Parameters!Paramreport.Value
> > =7,1,Fields(iif(Parameters!Paramreport.Value =7,
> > "region",Parameters!Paramreport.Value)).Value)
> > I am not sure what I am doing wrong as basically I didn't really
> understand
> > the article.
> >
> > I would appreciate any help offered.
> >
> > Thanks
>
>