Dynamic Row group in matrix

Hi All !

I want to show row groups as hierarchy levels and need the

sub total values belongs to each group and sub group levels. But the

most important point is that my top next top group (from child to

parent ) is not static its dynamic.i.e for a diffrent senario my under

displayed example can have Universe>Earth as parent for Australia and USA.

eg:

1.Australia

|-sydney

|-Melbourne

2.USA

|North US

|North US(1)

|North US(2)

|South US

|South US(1)

|South US(2)

Can I get some help from anybody for making a dynamic row groups in the matrix.

Waiting for a kind help.

Regards,

This is the Dynamic Group I used. I am not the original author, I search on dynamic grouping for SSRS and modified the example showed.

Put this is group expression:

=iif(Parameters!Group_By.Value is Nothing,Fields!SomeField.Value , Fields(iif(Parameters!Group_By.Value is Nothing, "DefaultID",Parameters!Group_By.Value)).Value)

|||Look at this blog entry from Chris Hayes http://blogs.msdn.com/chrishays/archive/2004/07/15/DynamicGrouping.aspx|||thanx for ur help.
I have done this with some other logic.

Dynamic Row group in matrix

Hi All !
I want to show row groups as hierarchy levels and need the sub total values belongs to each group and sub group levels. But the most important point is that my top next top group (from child to parent ) is not static its dynamic.i.e for a diffrent senario my under displayed example can have Universe>Earth as parent for Australia and USA.
eg:
1.Australia
|-sydney
|-Melbourne
2.USA
|North US
|North US(1)
|North US(2)
|South US
|South US(1)
|South US(2)
Can I get some help from anybody for making a dynamic row groups in the matrix.
Waiting for a kind help.
Regards,

This is the Dynamic Group I used. I am not the original author, I search on dynamic grouping for SSRS and modified the example showed.

Put this is group expression:

=iif(Parameters!Group_By.Value is Nothing,Fields!SomeField.Value , Fields(iif(Parameters!Group_By.Value is Nothing, "DefaultID",Parameters!Group_By.Value)).Value)

|||Look at this blog entry from Chris Hayes http://blogs.msdn.com/chrishays/archive/2004/07/15/DynamicGrouping.aspx|||thanx for ur help.
I have done this with some other logic.sql

Dynamic Row Group in a matrix

Hi All !

I want to show row groups as hierarchy levels and need the

sub total values belongs to each group and sub group levels. But the

most important point is that my top next top group (from child to

parent ) is not static its dynamic.i.e for a diffrent senario my under

displayed example can have Universe>Earth as parent for Australia and USA.

eg:

1.Australia

|-sydney

|-Melbourne

2.USA

|North US

|North US(1)

|North US(2)

|South US

|South US(1)

|South US(2)

Can I get some help from anybody for making a dynamic row groups in the matrix.

Waiting for a kind help.

Regards,

Hi Sanjib,

Check this link.It will be helpful.

|||

Hi Dear !

Thanx for reply me , but i'm sorry to say that there is no link mentioned.

Plz mention the link.

waiting for u'r reply,

Dynamic Grouping.. Is it possible?

Hello All,
I have a table with 2 groups in my report. When I edit a group I can
specify the expression to "group on" for the groups.
I would like to be able to make the expression, for the top group, a value
from a report parameter so that the user can specify this when the report
is generated.
The Edit Group Window certainly allows me to pick a report parameter but I
am not sure if it would actually work.. nor what the value of the parameter
should be to make it work. Is this possible?
Example:
Top Group: User Selectable (Country, State, City)
Second Group: Product
Detail Row: Sales data for that product/place combo
So the user could say he would like to see sales grouped by
country/product, state/product, or city/product.
Is this doable without creating 3 different reports?
--
Message posted via http://www.sqlmonster.comTry the following dynamic group expression:
=Fields(Parameters!TopGroup.Value).Value
This requires that the values (or labels) of the TopGroup parameters have
matching field names in your dataset (i.e. Country, State, City).
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Brian W via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:7c78eafd5f1d46329db04bd95646fb57@.SQLMonster.com...
> Hello All,
> I have a table with 2 groups in my report. When I edit a group I can
> specify the expression to "group on" for the groups.
> I would like to be able to make the expression, for the top group, a value
> from a report parameter so that the user can specify this when the report
> is generated.
> The Edit Group Window certainly allows me to pick a report parameter but I
> am not sure if it would actually work.. nor what the value of the
> parameter
> should be to make it work. Is this possible?
> Example:
> Top Group: User Selectable (Country, State, City)
> Second Group: Product
> Detail Row: Sales data for that product/place combo
> So the user could say he would like to see sales grouped by
> country/product, state/product, or city/product.
> Is this doable without creating 3 different reports?
> --
> Message posted via http://www.sqlmonster.com|||Worked like a charm. Thanks.
--
Message posted via http://www.sqlmonster.com

Dynamic Grouping with Report Designer

I've got this working -- I allow users to have 2 groups, and choose what they want to group by. I'd like to add one extra bit of functionality -- for the inner grouping, I would like my users to have the option "None" -- i.e. don't have an inner group.

I've tried setting the group expression of the second (inner) group to "" when the user chooses the "None" option but the report errors out. Any suggestions as to how to dynamically get rid of the inner group?

Thx

Helen

Helen:

I had a similar requirement and here's how I solved it. For the "None" entry at each grouping level you'll need to include a value for it in the combobox that's the following functions will recognize. Include the following in the "Code" and call the appropriate function. You're probably most interested in the "GetSubGrouping" function but I'm including "GetGrouping" so you can also use it at the main grouping level. Include the call to GetSubGrouping in the "Group on"; you'll also find that "GetField" is useful instead of coding a lot of iif's for controlling the visibility of the groupings.

Hope this helps

Glenn L

SharedFunction GetGrouping(ByVal Parameters AsObject, ByVal Fields AsObject) AsObject

Return GetField(Fields, Parameters("GroupBy").Value, "no_grouping")

EndFunction

SharedFunction GetSubGrouping(ByVal Parameters AsObject, ByVal Fields AsObject) AsObject

Return IIf(Parameters("GroupBy").Value = "no_grouping" _

Or Parameters("GroupBy").Value = Parameters("SubGroupBy").Value, Nothing, GetField(Fields, Parameters("SubGroupBy").Value, "no_sub_grouping"))

EndFunction

SharedFunction GetField(ByVal Fields AsObject, ByVal FieldName AsString, ByVal NoGroupingValue AsString) AsObject

If Trim$(FieldName) = Trim$(NoGroupingValue) Then

ReturnNothing

ElseIf IsDate(Fields(Trim$(FieldName)).Value) Then

Return FormatDateTime(Fields(Trim$(FieldName)).Value, 2)

Else

Return Fields(Trim$(FieldName)).Value

EndIf

EndFunction

|||

Thank you Glenn! That did the trick!

Helen

Dynamic Grouping with Report Designer

I've got this working -- I allow users to have 2 groups, and choose what they want to group by. I'd like to add one extra bit of functionality -- for the inner grouping, I would like my users to have the option "None" -- i.e. don't have an inner group.

I've tried setting the group expression of the second (inner) group to "" when the user chooses the "None" option but the report errors out. Any suggestions as to how to dynamically get rid of the inner group?

Thx

Helen

Helen:

I had a similar requirement and here's how I solved it. For the "None" entry at each grouping level you'll need to include a value for it in the combobox that's the following functions will recognize. Include the following in the "Code" and call the appropriate function. You're probably most interested in the "GetSubGrouping" function but I'm including "GetGrouping" so you can also use it at the main grouping level. Include the call to GetSubGrouping in the "Group on"; you'll also find that "GetField" is useful instead of coding a lot of iif's for controlling the visibility of the groupings.

Hope this helps

Glenn L

SharedFunction GetGrouping(ByVal Parameters AsObject, ByVal Fields AsObject) AsObject

Return GetField(Fields, Parameters("GroupBy").Value, "no_grouping")

EndFunction

SharedFunction GetSubGrouping(ByVal Parameters AsObject, ByVal Fields AsObject) AsObject

Return IIf(Parameters("GroupBy").Value = "no_grouping" _

Or Parameters("GroupBy").Value = Parameters("SubGroupBy").Value, Nothing, GetField(Fields, Parameters("SubGroupBy").Value, "no_sub_grouping"))

EndFunction

SharedFunction GetField(ByVal Fields AsObject, ByVal FieldName AsString, ByVal NoGroupingValue AsString) AsObject

If Trim$(FieldName) = Trim$(NoGroupingValue) Then

ReturnNothing

ElseIf IsDate(Fields(Trim$(FieldName)).Value) Then

Return FormatDateTime(Fields(Trim$(FieldName)).Value, 2)

Else

Return Fields(Trim$(FieldName)).Value

EndIf

EndFunction

|||

Thank you Glenn! That did the trick!

Helen

Dynamic Grouping Group Header Problem

Hi, I am currently trying to create a report the dynamicaly groups from parameters. The grouping part works fine but I need to show the parameter label name or the field referenced by a parameter.

=iif(Parameters!Param1.Value="P",Parameters!Param2.Label,Fields(Parameters!Param1.Value).Value)

This is the expression I have written to do this. The problem seems to be is with the true part of the iif statement. The false displays fine in the group header. The Parameters!Param2.Label also displays if used on its own.

This is the error that is fires back when I run the report.

The Value expression for the textbox ‘textbox4’ contains an error: The expression referenced a non-existing field in the fields collection.

I am quite new to SSRS and I am using VS2005 pro.

Thanks

D

D,

Try

iif(Parameters!Param1.Value="P",Fields(Parameters!Param2.Label).Value,Fields(Parameters!Param1.Value).Value)

I believe this should work for you.

Ham

Dynamic Group Sorting in Crystal Report

i am using crystal report application in visual studio .net. Now, I make one crystal report, has 5 groups. Now, I want to change their order from VB .net application. What to do for that?? I'm new in crystal report.... I guess you need to organize your dynamic grouping in your report. You can use a parameter(s) for that, it will let you/user to select/change a group. Then create a formula based on the parameter response.

For example:
I would create a parameter to allow you/user to select 'SDate' Shipping Date or 'CustID' - Customer ID.

Then I would write a formula for my 1st group @.Group1:

If {?Group1}='SDate' then {table.shipping_date} else {table.CustomerID}

For my 2nd group, I would write a formula @.Group2:

If {?Group1}='SDate' {table.CustomerID} else {table.shipping_date}

After that, I would grouped my records on @.Group1 and on @.Grop2.

----

Or you can create one parameter per group and then a formula based on that parameter (1 per group as well):
For your 1st group I would create a parameter {?Group1} which allows me to select 'SDate' for shipping date, 'CustID' for CustomerID, 'Country' for country code etc.

Then create a formula @.Group1:

If {?Group1}='SDate' then {table.shipping_gdate} else
If {?Group1}='CustID' then {table.CustomerID} else
If {?Group1}='Country' then {table.CountryCode} else

For my 2nd group I would create a parameter {?Group2} and then a formula @.Group2:

If {?Group2}='Source' then {table.origination_number} else
If {?Group2}='DestCity' then {table.destination_city} else
If {?Grop2}='Carrier' then {tabme.CarrierID} else
.
.
.
You can create as many group options as you need.

I hope this will be helpful

Dynamic Data/Groups/Page breaks

I am absolutely new to SSRS (about a day into it). I am trying to figure out
the reporting methodology to use for building custom reports and would really
like some suggestions on the reporting architecture that I should use.
The base functionality is
1) Provide a c# UI to allow end-user to set report options (described below).
2) Use a stored procedure to generate dynamic sql based on user selection in
(1).
2) Show the report in the report viewer control (for now, we might develop
our own custom report viewer control later).
Almost all our report options are highly dynamic in nature. These include
ability to include/exclude some data, dynamic grouping on certain data,
dynamic page breaks as requested by the user and a whole lot more.
The Report Designer seems almost like a non-starter for us, based on our
requirements.
From what I've read so far, the best option seems to be to dynamically
generate the RDL, from our c# application, to be used in our reports. This
means we won't have to publish our reports to the ReportServer.
Is this the best approach based on the above requirements? Are there any
other options available? Also, what might be the best resources for dynamic
RDL generation on the web or books?
Any inputs will be greatly appreciated.
Thanks,
NaveenOn Feb 21, 11:06 am, Naveen <Nav...@.discussions.microsoft.com> wrote:
> I am absolutely new to SSRS (about a day into it). I am trying to figure out
> the reporting methodology to use for building custom reports and would really
> like some suggestions on the reporting architecture that I should use.
> The base functionality is
> 1) Provide a c# UI to allow end-user to set report options (described below).
> 2) Use a stored procedure to generate dynamic sql based on user selection in
> (1).
> 2) Show the report in the report viewer control (for now, we might develop
> our own custom report viewer control later).
> Almost all our report options are highly dynamic in nature. These include
> ability to include/exclude some data, dynamic grouping on certain data,
> dynamic page breaks as requested by the user and a whole lot more.
> The Report Designer seems almost like a non-starter for us, based on our
> requirements.
> From what I've read so far, the best option seems to be to dynamically
> generate the RDL, from our c# application, to be used in our reports. This
> means we won't have to publish our reports to the ReportServer.
> Is this the best approach based on the above requirements? Are there any
> other options available? Also, what might be the best resources for dynamic
> RDL generation on the web or books?
> Any inputs will be greatly appreciated.
> Thanks,
> Naveen
I would have to say that dynamically generated RDLs via C# would
probably be the best route to take. Just an FYI, you can dynamically
control data (i.e., filtering, including, excluding data) w/a
combination of report parameters tied to report expressions. Hope this
helps.
Regards,
Enrique Martinez
Sr. SQL Server Developer|||I just about discovered that using expressions might be worth a try before
attempting to generate RDL dynamically. Your response seems to confirm that.
If that doesn't work, maybe dynamic RDL generation would be the way to go.
Thanks for your reply.
Naveen
"EMartinez" wrote:
> On Feb 21, 11:06 am, Naveen <Nav...@.discussions.microsoft.com> wrote:
> > I am absolutely new to SSRS (about a day into it). I am trying to figure out
> > the reporting methodology to use for building custom reports and would really
> > like some suggestions on the reporting architecture that I should use.
> >
> > The base functionality is
> > 1) Provide a c# UI to allow end-user to set report options (described below).
> > 2) Use a stored procedure to generate dynamic sql based on user selection in
> > (1).
> > 2) Show the report in the report viewer control (for now, we might develop
> > our own custom report viewer control later).
> >
> > Almost all our report options are highly dynamic in nature. These include
> > ability to include/exclude some data, dynamic grouping on certain data,
> > dynamic page breaks as requested by the user and a whole lot more.
> >
> > The Report Designer seems almost like a non-starter for us, based on our
> > requirements.
> >
> > From what I've read so far, the best option seems to be to dynamically
> > generate the RDL, from our c# application, to be used in our reports. This
> > means we won't have to publish our reports to the ReportServer.
> >
> > Is this the best approach based on the above requirements? Are there any
> > other options available? Also, what might be the best resources for dynamic
> > RDL generation on the web or books?
> >
> > Any inputs will be greatly appreciated.
> >
> > Thanks,
> > Naveen
>
> I would have to say that dynamically generated RDLs via C# would
> probably be the best route to take. Just an FYI, you can dynamically
> control data (i.e., filtering, including, excluding data) w/a
> combination of report parameters tied to report expressions. Hope this
> helps.
> Regards,
> Enrique Martinez
> Sr. SQL Server Developer
>