Visual Basic Crystal Reports - Grouping Question

Author

Grouping Question

vtdw

2005-06-09, 4:01 pm

Is there a way to suppress the grouping as well as the group? I know
how to suppress the group but I also want the grouping to be
suppressed. I have three groups that surround my data. One is for year,
one for quarter and one for month. Based on a report switch I can
suppress 2 of the three sections but the grouping from the two that I
suppress are still present.

Bob Holmes

2005-06-09, 8:59 pm

If the grouping is suppressed, do you want the records to appear in a
different order than if the grouping was not suppressed. If not, then just
suppressing the group sections (header and footer) should be enough.
If you have records for all of last year, starting with Jan.1 and ending
Dec. 31, if you group them by quarter (gp1)and then by month (gp2), you will
have a gp1 header before Jan, before April, before July, and before Oct.
You will have gp2 headers before each month. If you suppress both of those
headers and footers, you will still get all of your records in order,
starting in Jan, going through Dec.

If this is not what you are asking, could you clarify it a little?

--
Bob Holmes

"vtdw" <David.White@Competitive.com> wrote in message
news:1118332953.699776.93560@o13g2000cwo.googlegroups.com...
> Is there a way to suppress the grouping as well as the group? I know
> how to suppress the group but I also want the grouping to be
> suppressed. I have three groups that surround my data. One is for year,
> one for quarter and one for month. Based on a report switch I can
> suppress 2 of the three sections but the grouping from the two that I
> suppress are still present.
>

vtdw

2005-06-09, 8:59 pm

Thanks Bob,

I do want to group them differently. I have three groups, Yearly,
Quarterly, Monthly. If I suppress both footer and header on the Monthly
and Quarterly groups to get my data grouped by year, it still seems to
also group the records by the suppressed groups, quarterly and monthly.
The groups are in order Group 3 (Yearly), Group 4(Quarterly) and Group
5 (Monthly) all grouped on the same time field. Group 6 has some
aggregates and that's where I know even if I am suppressing Group 4 and
Group 5 I get output grouped by Quarter and Month as well as year. I
don't get the text that's specific to Group 4 or Group 5, ei "Month :
Jan " but the grouping affect is still there.If I take away Group 4 and
Group 5 I get correct grouping for years. Not being a CR GURU there's
probably something I can do to get the three into the same group or
dynamically set the grouping but I don't know how. Thanks for any
help!!

Briscobar

2005-06-09, 8:59 pm

In news:1118349340.751929.215730@f14g2000cwb.googlegroups.com,
vtdw <David.White@Competitive.com> rambled:
> Thanks Bob,
>
> I do want to group them differently. I have three groups, Yearly,
> Quarterly, Monthly. If I suppress both footer and header on the
> Monthly and Quarterly groups to get my data grouped by year, it still
> seems to also group the records by the suppressed groups, quarterly
> and monthly. The groups are in order Group 3 (Yearly), Group
> 4(Quarterly) and Group 5 (Monthly) all grouped on the same time
> field. Group 6 has some aggregates and that's where I know even if I
> am suppressing Group 4 and Group 5 I get output grouped by Quarter
> and Month as well as year. I don't get the text that's specific to
> Group 4 or Group 5, ei "Month : Jan " but the grouping affect is
> still there.If I take away Group 4 and Group 5 I get correct grouping
> for years. Not being a CR GURU there's probably something I can do to
> get the three into the same group or dynamically set the grouping but
> I don't know how. Thanks for any help!!

You'll want to create a dynamic group for this, with a parameter that the
user can edit when the report is run.

Try this:

Create a parameter {?Group} with 3 options: "Yearly", "Quarterly" and
"Monthly".
Create a formula called {@Group} that looks something like this:

if {?Group} = "Yearly" then ({table.YourCurrentGroupsDateField}, "Yearly")
else
if {?Group} = "Quarterly" then ({table.YourCurrentGroupsDateField},
"Quarterly") else
if {?Group} = "Monthly" then ({table.YourCurrentGroupsDateField},
"Monthly")

and make {@Group} the field you group on, not
{table.YourCurrentGroupsDateField}.

I'm not sure this formula will work, but if it doesn't, it won't be too hard
to create a new formula that will allow you to separate the month from the
year, then group on that.

HTH.

--

KB - MCNGP "silent thug" #26

Briscobar AT gmail DOT com

A.S.S.F.U.C.K.E.R.S. are not welcome at www.mcngp.com. Unless you email
cheatsheets@mcngp.com first.

vtdw

2005-06-09, 8:59 pm

Ok so basically I'm grouping on the table field when I should be
creating a formula field and group on that instead. That should make it
dynamic. Sounds easy enough I'll try it. Thanks!

Briscobar

2005-06-09, 8:59 pm

In news:1118350545.057833.326540@g14g2000cwa.googlegroups.com,
vtdw <David.White@Competitive.com> rambled:
> Ok so basically I'm grouping on the table field when I should be
> creating a formula field and group on that instead. That should make
> it dynamic. Sounds easy enough I'll try it. Thanks!

Yep, exactly. Although I'm not sure if that formula I gave will work. I know
that aggregate functions can take "Yearly", "Monthly", etc as parameters,
but I'm not sure that it's proper syntax for a group. If not, I'll help you
break that formula down into months, then into quarters if you need it. But
not today - I'm getting ready to go home :)

--

KB - MCNGP "silent thug" #26

Briscobar AT gmail DOT com

If you don't hightail it over to www.mcngp.com, your job will be
outsourced.

vtdw

2005-06-10, 4:03 pm

Ok the formula field grouping worked. Now I have one group that
dynamically switches the grouping based on what the user chooses.
Thanks!!

if {?Trend} = "Y" then
{@Year}
else if {?Trend} = "M" then
{@Month}
else if {?Trend} = "Q" then
{@Quarter}

Formula for Quarter

if (Month ({vTest.DTTM}) >= 1) and (Month ({vTest.DTTM}) <= 3) then
ToText (Year ({vTest.CompletedDTTM}),0,'') + " Q1"

else if (Month ({vTest.DTTM}) >= 4) and (Month ({vTest.DTTM}) <= 6)
then
ToText (Year ({vTest.DTTM}),0,'') + " Q2"

else if (Month ({vTest.DTTM}) >= 7) and (Month ({vTest.DTTM}) <= 9)
then
ToText (Year ({vTest.DTTM}),0,'') + " Q3"

else if (Month ({vTest.DTTM}) >= 10) and (Month ({vTest.DTTM}) <= 12)
then
ToText (Year ({vTest.DTTM}),0,'') + " Q4"

And the Month and Year formulas are taken from above

Sponsored Links