Group Totals on Last page - How?

I have a report that groups by dept #, job code and earnings code.

9999 Administration

033 Secretary

200 Regular Pay 44.00 1000.00

300 Sick Pay 8.00 25.00

400 Overtime 3.00 75.00

8888 Janoitorial

055 Janitor

200 Regular Pay 24.00 800.00

300 Sick Pay 4.00 15.00

400 Overtime 1.00 45.00

On the last page of my report I want to sum the earnings totals by earnings number. For Example:

Totals

200 Regular Pay 68.00 1800.00

300 Sick Pay 12.00 40.00

400 Overtime 4.00 120.00

Can this be done?

Create a new table at the bottom of the report, group by earnings code, and sum the numbers.|||

Hi Jim,

another way to do this, is to use the table footer for the sums.

In the table footer, put the following expression

=Sum(IIF(Fields!earnings_code.value = 200, Fields!totals, cint(0))) this should be for regular pay and just replace the earning code for
the rest.

Then select the table, go to the properties, and there is an option called "Print footer rows on last page" and check that.

That should do it, hope that helps!

Bernard

|||Thanks for the help. I really appreciate it|||

Can you mark the answers that were helpful?

Thanks.

Group Totals - Maybe Simple * Please help!

I am having a lot of difficulties in grouping totals.
Could someone please help!
EXAMPLE:
Group1 123
Group2 456
Group3 789Try using the SUM(<field_name>) function in the footer of the group. The
placement of this is going to depend on if you are using a table or lists to
do your grouping. You can play with your grouping to only show the totals if
you do not want the detail.
"Terry" wrote:
> I am having a lot of difficulties in grouping totals.
> Could someone please help!
> EXAMPLE:
> Group1 123
> Group2 456
> Group3 789
>|||Thank you.
However, are there indepth technical notes, white papers or links on how to
create mailing lists (columns) within a report?
Our China office need assistance in this matter and I have no idea what to do.
I have tried setting the report column setting to 3 and changing the width
of the report to no avail.
Any help would be greatly appreciated.
"TBraun" wrote:
> Try using the SUM(<field_name>) function in the footer of the group. The
> placement of this is going to depend on if you are using a table or lists to
> do your grouping. You can play with your grouping to only show the totals if
> you do not want the detail.
> "Terry" wrote:
> > I am having a lot of difficulties in grouping totals.
> >
> > Could someone please help!
> >
> > EXAMPLE:
> >
> > Group1 123
> > Group2 456
> > Group3 789
> >
> >|||Terry,
I have created mailing lables for some of our clients and unfortunately I
have not found any good documentation on decent procedures. I cretaed a
mailing list using a single column table with 5 detail rows. My query
returns the fields I need and formatting was a huge issue. I set the report
width and height to the size of the labels we were using. I maxed out the
column to be a little bit less than the actual report itself. I found that
sometimes if you run stuff right up to the edge on the width a little
variance will mess up the report. Make sure you set your cells to not be
able to grow. This will also mess up the formatting. Put a page break at
the end of each one so each lable prints on it's own page. We export to pdf
first then print them on a lable printer.
The same mailing list was created for sheets of labels on normal 8.5 x 11
sheets. For that I created basically the same thing but made it multiple
columns and then had to make small adjustments to place the elements on the
sheets properly.
I wish I could be of more assistance to you but that is what I have. If you
have more specific questions let me know and I will do what I can.
Tim
"Terry" wrote:
> Thank you.
> However, are there indepth technical notes, white papers or links on how to
> create mailing lists (columns) within a report?
> Our China office need assistance in this matter and I have no idea what to do.
> I have tried setting the report column setting to 3 and changing the width
> of the report to no avail.
> Any help would be greatly appreciated.
> "TBraun" wrote:
> > Try using the SUM(<field_name>) function in the footer of the group. The
> > placement of this is going to depend on if you are using a table or lists to
> > do your grouping. You can play with your grouping to only show the totals if
> > you do not want the detail.
> >
> > "Terry" wrote:
> >
> > > I am having a lot of difficulties in grouping totals.
> > >
> > > Could someone please help!
> > >
> > > EXAMPLE:
> > >
> > > Group1 123
> > > Group2 456
> > > Group3 789
> > >
> > >|||Do you have an example .RDL file available or could you provide basic
instructions on this matter?
"TBraun" wrote:
> Terry,
> I have created mailing lables for some of our clients and unfortunately I
> have not found any good documentation on decent procedures. I cretaed a
> mailing list using a single column table with 5 detail rows. My query
> returns the fields I need and formatting was a huge issue. I set the report
> width and height to the size of the labels we were using. I maxed out the
> column to be a little bit less than the actual report itself. I found that
> sometimes if you run stuff right up to the edge on the width a little
> variance will mess up the report. Make sure you set your cells to not be
> able to grow. This will also mess up the formatting. Put a page break at
> the end of each one so each lable prints on it's own page. We export to pdf
> first then print them on a lable printer.
> The same mailing list was created for sheets of labels on normal 8.5 x 11
> sheets. For that I created basically the same thing but made it multiple
> columns and then had to make small adjustments to place the elements on the
> sheets properly.
> I wish I could be of more assistance to you but that is what I have. If you
> have more specific questions let me know and I will do what I can.
> Tim
> "Terry" wrote:
> > Thank you.
> >
> > However, are there indepth technical notes, white papers or links on how to
> > create mailing lists (columns) within a report?
> >
> > Our China office need assistance in this matter and I have no idea what to do.
> >
> > I have tried setting the report column setting to 3 and changing the width
> > of the report to no avail.
> >
> > Any help would be greatly appreciated.
> >
> > "TBraun" wrote:
> >
> > > Try using the SUM(<field_name>) function in the footer of the group. The
> > > placement of this is going to depend on if you are using a table or lists to
> > > do your grouping. You can play with your grouping to only show the totals if
> > > you do not want the detail.
> > >
> > > "Terry" wrote:
> > >
> > > > I am having a lot of difficulties in grouping totals.
> > > >
> > > > Could someone please help!
> > > >
> > > > EXAMPLE:
> > > >
> > > > Group1 123
> > > > Group2 456
> > > > Group3 789
> > > >
> > > >

Group Totals

Can some one help me with totaling a group value on SQL Server
Reporting Services report? Here is a simplified version of what I am
struggling with:
Let's say I have an SQL statement that returns the flowing set
RoomID Max Occupants Name
1 3 Bill Clinton
1 3 Hilary Clinton
1 3 Chelsea Clinton
2 4 George W Bush
2 4 Barbara Bush
Total 7 5
I have a report with a group that groups rooms, shows the max occupants
on the header of each group and lists the current occupants underneath
each group header. On the bottom I want to show the total number of
occupants the rooms could have and the number of spots currently
occupied, 7 and 5 in my example. What do I need to do to show 7 on the
bottom of the report? It looks simple and I have done this many times
on different report writers but for some reason I have trouble figuring
out what to do on RS report.
Any help is appreciated.
Tim.Group by room ID and then add the First(Fields!occupants.value) from each
group.
"Tim." wrote:
> Can some one help me with totaling a group value on SQL Server
> Reporting Services report? Here is a simplified version of what I am
> struggling with:
> Let's say I have an SQL statement that returns the flowing set
> RoomID Max Occupants Name
> 1 3 Bill Clinton
> 1 3 Hilary Clinton
> 1 3 Chelsea Clinton
> 2 4 George W Bush
> 2 4 Barbara Bush
> Total 7 5
> I have a report with a group that groups rooms, shows the max occupants
> on the header of each group and lists the current occupants underneath
> each group header. On the bottom I want to show the total number of
> occupants the rooms could have and the number of spots currently
> occupied, 7 and 5 in my example. What do I need to do to show 7 on the
> bottom of the report? It looks simple and I have done this many times
> on different report writers but for some reason I have trouble figuring
> out what to do on RS report.
> Any help is appreciated.
> Tim.
>sql

Group Total Not working right

Hi,

I have a problem that i cannot figure out how to fix.

I have a sub report that i need to have the group totals in the Page header and i cannot for the life of me remember how to do this.

I have grouped by field 2 which gives me a time for Planned and unplanned events, I need to add up the time of the Planned items and put next to my Downtime Planned Text Box, and then sum the Unplanned and do the same against my unplanned downtime box

Regards SteveExample:
subreport 1 formula:
whileprintingrecords;
shared numbervar x:= sum({table.field})
subreport 2 formula:
whileprintingrecords;
shared numbervar y:= sum({table.field})
Formula in the main report:
whileprintingrecords;
shared numbervar x;
shared numbervar y;
x+y|||just to add some thing. You can use the sum function as

sum({table.field}, "Group on the field");

So you can get the planned and unplanned in the same subreport.|||Very confused with First reply, but will have a go at that, Second reply worked fine fo the same report

Regards

Steve

Group summary totals Help please

Hi,

I am pretty new to Crystal 8.5 and am having problems using formulas on a group summary field.

The database is > 1 million records and I am trying to group a field (for quicker report generation). The field is the time (in seconds) numeric and I want to be able to manipulate this field and get it into hhhh:mm:ss format.

In this application, I want to add up all the seconds for each person in a department, then give a total for each person however in hh:mm:ss format.

Eg Person Table

Name Duration(in seconds)

Peter 50
John 10
Peter 45
John 22
John 80
Peter 100

The Crystal generated SQL will generate

Peter 195
John 112

This is straight forward, however, I now need to convert this to hh:mm:ss format to give me

Peter 00:03:15
John 00:01:52

I can do the above if I look at each individual record (ie no grouping) and use formulas on the Duratin field, however, because of the size of the database, I want to group the records and then manipulate the SUM (Person."Duration") field.

Does anybody have a solution?

(I hope my explanation of my problem is clear ...)

Thanking you in advance.

TheodoreOK Guys,

Solved the problem.

Thanks anyway.

Theodore|||Theo,

I have similar problem, any chance of posting the solution

Regards

Steve Dyson|||Why do you not just use the same formula you used for individual records, to convert the summary? The field would be sum of duration.|||My problem is that i have the following:

Planned and unplanned items.

I can group and Sum them that is the easy bit.

Because this is going to be a sub report, i need these totals to appear in the Page header as i only want to see the Totals

Steve

GROUP BY question

Hi,
I have a table which includes date (day, month, year, as one col.) &
deposits for each day. I need to summarize the deposits into totals per
month. I have used SUM(deposit). I need to group it by month/year. How do I
do this? If I use DATENAME to create a col. to return a col. which includes
only the date & year, how can I use this to group the aggregate function? I
can��t use its alias in the group by clause & I can't group by the date in
its original format as this won't return monthly groupings. Is it possible to
do this without creating separate columns for each element of the date? Below
is the query:
SELECT DATENAME(MONTH,date)+ ' ' + DATENAME(YEAR,date) as [mnth & yr],
SUM(deposit)
FROMloan_tbl
GROUP BY what? (I'd like to group by month - Year)
Any input would be greatly appreciated
thanks, Ant
Have the same expression in your GROUP BY as in the column list, or use a derived table, such as:
SELECT [mnth & yr], SUM(deposit)
FROM
(
SELECT DATENAME(MONTH,date)+ ' ' + DATENAME(YEAR,date) as [mnth & yr],
deposit
FROM loan_tbl
) AS d
GROUP BY [mnth & yr]
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Ant" <Ant@.discussions.microsoft.com> wrote in message
news:9D493D95-C622-4BB2-94DC-A389D823FF57@.microsoft.com...
> Hi,
> I have a table which includes date (day, month, year, as one col.) &
> deposits for each day. I need to summarize the deposits into totals per
> month. I have used SUM(deposit). I need to group it by month/year. How do I
> do this? If I use DATENAME to create a col. to return a col. which includes
> only the date & year, how can I use this to group the aggregate function? I
> can't use its alias in the group by clause & I can't group by the date in
> its original format as this won't return monthly groupings. Is it possible to
> do this without creating separate columns for each element of the date? Below
> is the query:
> SELECT DATENAME(MONTH,date)+ ' ' + DATENAME(YEAR,date) as [mnth & yr],
> SUM(deposit)
> FROM loan_tbl
> GROUP BY what? (I'd like to group by month - Year)
> Any input would be greatly appreciated
> thanks, Ant
|||Ant
SELECT DATENAME(MONTH,date)+ ' ' + DATENAME(YEAR,date) as [mnth & yr],
SUM(deposit)
FROM loan_tbl
GROUP BY DATENAME(MONTH,date)+ ' ' + DATENAME(YEAR,date)
"Ant" <Ant@.discussions.microsoft.com> wrote in message
news:9D493D95-C622-4BB2-94DC-A389D823FF57@.microsoft.com...
> Hi,
> I have a table which includes date (day, month, year, as one col.) &
> deposits for each day. I need to summarize the deposits into totals per
> month. I have used SUM(deposit). I need to group it by month/year. How do
I
> do this? If I use DATENAME to create a col. to return a col. which
includes
> only the date & year, how can I use this to group the aggregate function?
I
> cant use its alias in the group by clause & I can't group by the date in
> its original format as this won't return monthly groupings. Is it possible
to
> do this without creating separate columns for each element of the date?
Below
> is the query:
> SELECT DATENAME(MONTH,date)+ ' ' + DATENAME(YEAR,date) as [mnth & yr],
> SUM(deposit)
> FROM loan_tbl
> GROUP BY what? (I'd like to group by month - Year)
> Any input would be greatly appreciated
> thanks, Ant
|||That's great. Thank you very much. Both solutions work nicely.
Regards
Ant
"Tibor Karaszi" wrote:

> Have the same expression in your GROUP BY as in the column list, or use a derived table, such as:
> SELECT [mnth & yr], SUM(deposit)
> FROM
> (
> SELECT DATENAME(MONTH,date)+ ' ' + DATENAME(YEAR,date) as [mnth & yr],
> deposit
> FROM loan_tbl
> ) AS d
> GROUP BY [mnth & yr]
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "Ant" <Ant@.discussions.microsoft.com> wrote in message
> news:9D493D95-C622-4BB2-94DC-A389D823FF57@.microsoft.com...
>
>

GROUP BY question

Hi,
I have a table which includes date (day, month, year, as one col.) &
deposits for each day. I need to summarize the deposits into totals per
month. I have used SUM(deposit). I need to group it by month/year. How do I
do this? If I use DATENAME to create a col. to return a col. which includes
only the date & year, how can I use this to group the aggregate function? I
can��t use its alias in the group by clause & I can't group by the date in
its original format as this won't return monthly groupings. Is it possible t
o
do this without creating separate columns for each element of the date? Belo
w
is the query:
SELECT DATENAME(MONTH,date)+ ' ' + DATENAME(YEAR,date) as [mnth & yr],
SUM(deposit)
FROM loan_tbl
GROUP BY what? (I'd like to group by month - Year)
Any input would be greatly appreciated
thanks, AntHave the same expression in your GROUP BY as in the column list, or use a de
rived table, such as:
SELECT [mnth & yr], SUM(deposit)
FROM
(
SELECT DATENAME(MONTH,date)+ ' ' + DATENAME(YEAR,date) as [mnth & yr],
deposit
FROM loan_tbl
) AS d
GROUP BY [mnth & yr]
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Ant" <Ant@.discussions.microsoft.com> wrote in message
news:9D493D95-C622-4BB2-94DC-A389D823FF57@.microsoft.com...
> Hi,
> I have a table which includes date (day, month, year, as one col.) &
> deposits for each day. I need to summarize the deposits into totals per
> month. I have used SUM(deposit). I need to group it by month/year. How do
I
> do this? If I use DATENAME to create a col. to return a col. which include
s
> only the date & year, how can I use this to group the aggregate function?
I
> can't use its alias in the group by clause & I can't group by the date in
> its original format as this won't return monthly groupings. Is it possible
to
> do this without creating separate columns for each element of the date? Be
low
> is the query:
> SELECT DATENAME(MONTH,date)+ ' ' + DATENAME(YEAR,date) as [mnth & yr],
> SUM(deposit)
> FROM loan_tbl
> GROUP BY what? (I'd like to group by month - Year)
> Any input would be greatly appreciated
> thanks, Ant|||Ant
SELECT DATENAME(MONTH,date)+ ' ' + DATENAME(YEAR,date) as [mnth & yr],
SUM(deposit)
FROM loan_tbl
GROUP BY DATENAME(MONTH,date)+ ' ' + DATENAME(YEAR,date)
"Ant" <Ant@.discussions.microsoft.com> wrote in message
news:9D493D95-C622-4BB2-94DC-A389D823FF57@.microsoft.com...
> Hi,
> I have a table which includes date (day, month, year, as one col.) &
> deposits for each day. I need to summarize the deposits into totals per
> month. I have used SUM(deposit). I need to group it by month/year. How do
I
> do this? If I use DATENAME to create a col. to return a col. which
includes
> only the date & year, how can I use this to group the aggregate function?
I
> cant use its alias in the group by clause & I can't group by the date in
> its original format as this won't return monthly groupings. Is it possible
to
> do this without creating separate columns for each element of the date?
Below
> is the query:
> SELECT DATENAME(MONTH,date)+ ' ' + DATENAME(YEAR,date) as [mnth & yr],
> SUM(deposit)
> FROM loan_tbl
> GROUP BY what? (I'd like to group by month - Year)
> Any input would be greatly appreciated
> thanks, Ant|||That's great. Thank you very much. Both solutions work nicely.
Regards
Ant
"Tibor Karaszi" wrote:

> Have the same expression in your GROUP BY as in the column list, or use a
derived table, such as:
> SELECT [mnth & yr], SUM(deposit)
> FROM
> (
> SELECT DATENAME(MONTH,date)+ ' ' + DATENAME(YEAR,date) as [mnth & yr],
> deposit
> FROM loan_tbl
> ) AS d
> GROUP BY [mnth & yr]
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "Ant" <Ant@.discussions.microsoft.com> wrote in message
> news:9D493D95-C622-4BB2-94DC-A389D823FF57@.microsoft.com...
>
>

GROUP BY question

Hi,
I have a table which includes date (day, month, year, as one col.) &
deposits for each day. I need to summarize the deposits into totals per
month. I have used SUM(deposit). I need to group it by month/year. How do I
do this? If I use DATENAME to create a col. to return a col. which includes
only the date & year, how can I use this to group the aggregate function? I
canâ't use its alias in the group by clause & I can't group by the date in
its original format as this won't return monthly groupings. Is it possible to
do this without creating separate columns for each element of the date? Below
is the query:
SELECT DATENAME(MONTH,date)+ ' ' + DATENAME(YEAR,date) as [mnth & yr],
SUM(deposit)
FROM loan_tbl
GROUP BY what? (I'd like to group by month - Year)
Any input would be greatly appreciated
thanks, AntHave the same expression in your GROUP BY as in the column list, or use a derived table, such as:
SELECT [mnth & yr], SUM(deposit)
FROM
(
SELECT DATENAME(MONTH,date)+ ' ' + DATENAME(YEAR,date) as [mnth & yr],
deposit
FROM loan_tbl
) AS d
GROUP BY [mnth & yr]
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Ant" <Ant@.discussions.microsoft.com> wrote in message
news:9D493D95-C622-4BB2-94DC-A389D823FF57@.microsoft.com...
> Hi,
> I have a table which includes date (day, month, year, as one col.) &
> deposits for each day. I need to summarize the deposits into totals per
> month. I have used SUM(deposit). I need to group it by month/year. How do I
> do this? If I use DATENAME to create a col. to return a col. which includes
> only the date & year, how can I use this to group the aggregate function? I
> can't use its alias in the group by clause & I can't group by the date in
> its original format as this won't return monthly groupings. Is it possible to
> do this without creating separate columns for each element of the date? Below
> is the query:
> SELECT DATENAME(MONTH,date)+ ' ' + DATENAME(YEAR,date) as [mnth & yr],
> SUM(deposit)
> FROM loan_tbl
> GROUP BY what? (I'd like to group by month - Year)
> Any input would be greatly appreciated
> thanks, Ant|||Ant
SELECT DATENAME(MONTH,date)+ ' ' + DATENAME(YEAR,date) as [mnth & yr],
SUM(deposit)
FROM loan_tbl
GROUP BY DATENAME(MONTH,date)+ ' ' + DATENAME(YEAR,date)
"Ant" <Ant@.discussions.microsoft.com> wrote in message
news:9D493D95-C622-4BB2-94DC-A389D823FF57@.microsoft.com...
> Hi,
> I have a table which includes date (day, month, year, as one col.) &
> deposits for each day. I need to summarize the deposits into totals per
> month. I have used SUM(deposit). I need to group it by month/year. How do
I
> do this? If I use DATENAME to create a col. to return a col. which
includes
> only the date & year, how can I use this to group the aggregate function?
I
> can?t use its alias in the group by clause & I can't group by the date in
> its original format as this won't return monthly groupings. Is it possible
to
> do this without creating separate columns for each element of the date?
Below
> is the query:
> SELECT DATENAME(MONTH,date)+ ' ' + DATENAME(YEAR,date) as [mnth & yr],
> SUM(deposit)
> FROM loan_tbl
> GROUP BY what? (I'd like to group by month - Year)
> Any input would be greatly appreciated
> thanks, Ant|||That's great. Thank you very much. Both solutions work nicely.
Regards
Ant
"Tibor Karaszi" wrote:
> Have the same expression in your GROUP BY as in the column list, or use a derived table, such as:
> SELECT [mnth & yr], SUM(deposit)
> FROM
> (
> SELECT DATENAME(MONTH,date)+ ' ' + DATENAME(YEAR,date) as [mnth & yr],
> deposit
> FROM loan_tbl
> ) AS d
> GROUP BY [mnth & yr]
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "Ant" <Ant@.discussions.microsoft.com> wrote in message
> news:9D493D95-C622-4BB2-94DC-A389D823FF57@.microsoft.com...
> > Hi,
> > I have a table which includes date (day, month, year, as one col.) &
> > deposits for each day. I need to summarize the deposits into totals per
> > month. I have used SUM(deposit). I need to group it by month/year. How do I
> > do this? If I use DATENAME to create a col. to return a col. which includes
> > only the date & year, how can I use this to group the aggregate function? I
> > can't use its alias in the group by clause & I can't group by the date in
> > its original format as this won't return monthly groupings. Is it possible to
> > do this without creating separate columns for each element of the date? Below
> > is the query:
> >
> > SELECT DATENAME(MONTH,date)+ ' ' + DATENAME(YEAR,date) as [mnth & yr],
> > SUM(deposit)
> > FROM loan_tbl
> > GROUP BY what? (I'd like to group by month - Year)
> >
> > Any input would be greatly appreciated
> >
> > thanks, Ant
>
>