Grouping with Page Break

I have a report that shows the monthwise details, i have performed this using the table control. the requirement was to have a page break after each month detail. End of each group display the monthwise total. This is absolutely working fine.

My problem is, I am suppose to display the Report total also. I used the Sum in the Table footer. The sum is coming fine the only problem that i have is it is printing on the a New Page instead of the Last Page.

Please suggest.

Hi,

you have to use the Group Footer, not the Table Footer. Edit Group -> include group footer:

Greez Daniel

|||

I have to display the Report Sub Total of the Group that i am already doing in the Group Footer, Where as I am wanting to display the Main Total of the Report, It wont work in the Group Footer as it is the Full Report total.

I tried to add a new Group fo the report just to have a main group and the other one as sub group, but it also did not work.

|||Try setting the RepeatOnNewPage property in the table footer to true. This will show the Main Total on every page.

Grouping with Page Break

I have a report that shows the monthwise details, i have performed this using the table control. the requirement was to have a page break after each month detail. End of each group display the monthwise total. This is absolutely working fine.

My problem is, I am suppose to display the Report total also. I used the Sum in the Table footer. The sum is coming fine the only problem that i have is it is printing on the a New Page instead of the Last Page.

Please suggest.

Hi,

you have to use the Group Footer, not the Table Footer. Edit Group -> include group footer:

Greez Daniel

|||

I have to display the Report Sub Total of the Group that i am already doing in the Group Footer, Where as I am wanting to display the Main Total of the Report, It wont work in the Group Footer as it is the Full Report total.

I tried to add a new Group fo the report just to have a main group and the other one as sub group, but it also did not work.

|||Try setting the RepeatOnNewPage property in the table footer to true. This will show the Main Total on every page.sql

Grouping in crystal reports

Hi

I m using crystal reports ver 8.0.

I have a report which is grouped on a field called "states".

My requirement is that the data for each state has to begin from a fresh page. i.e, each group item has to start from the next page.

I m not able to do this in crystal reports. Can anyone tell me if this is possible in crystal reports. and if possible, how it can be done ?

Thanking in advancethis works for 8.5 I don't know about version 8.0

Set "New Page Before" in the Group Header

Grouping Data for Consolidated Notification

We have a requirement where we need to send a single consolidated list of items belonging to a user which get active on particular date.

So, for example Item 1, Item 2, Item 3 gets active. These entries are inserted as Events. Now, we need to send single notification to the user with the email as:

=========================

Dear User,

Your following items got active today:

Item 1

Item 2

Item 3

Thanks,

Customer Care

=========================

For this we set the DigestDelievery to true and the emails indeed were consolidated. But, what it did was that it send single email notification repeating the entire content for each item as follows:

=========================

Dear User,

Your following items got active today:

Item 1

Thanks,

Customer Care

Dear User,

Your following items got active today:

Item 2

Thanks,

Customer Care

Dear User,

Your following items got active today:

Item 3

Thanks,

Customer Care

=========================

Please let me know if we are missing any setting or any changes need to be made in the .xslt for this to work.

regards,

Rajiv

I assume you are using a built-in XSLT formatter.

Since I have no idea about how to use/configure XSLT, in my application I created a custom content formatter in C# (this is really easy), and over there (in the .cs class) I "manually" built the resulting HTML string and inserted my items in a loop. The result HTML string is then sent as the email body.

Advantages:

1. I have 1 message "header" and 1 "footer". What's in between, gets "populated" at runtime in a loop, whether it's just 1 item or many.

2. Maybe, it's just as easy when using XSLT, ... I just don't know. But the emails my customers get contain hyperlinks which bring them right to the web page(s) for those particular item(s). Your management will love this feature!

3. Although I did not have to use this in my project, but if you need this, you can easily fetch additional data from some other non-NS data sources and "plug" it into your email message. It's possible because in a custom content formatter (C# or VB.NET, - your choice) you can use whatever .NET techniques you need, such as ADO.NET, System.IO (if you need to read from, say, some XML files), and whatever else you might want to do: a custom content formatter is just a regular .NET assembly, and you can use it as such.

|||Hi Rajiv -

From a SSNS perspective, it sounds like you have everything configured for digest delivery properly. Since it's not being formatted the way you wish, the issue is in the content formatter.

If you are using the built-in XSLT content formatter, try adjusting the
XSLTransform document.

Try placing the header and footer text directly in the XSLT document and the notification data in an <xsl:template> Match on "notification". You can then use teh <xsl:apply-templates> to call the notification section.

HTH...

Joe|||

Thanks for reply, I was able to resolve the issue as you have suggested.

regards,

Rajiv

Grouping Data By Weeks

I have a requirement to produce a report that breaks down some data into
totals by w. The data is in SQL Server, but the user just wants a one-off
report, so we can use Access or Excel as alternatives is more suitable.
We have a table of stock movements, and we want to total the number of
incoming and outgoing items for each w. We obviously know the dates of
these movements, but I'm unclear as to the SQL (or even general approach)
needed to break down this data.
StockMovements Table:
StockMoveID int
SerialNo int
MoveDate int
LocationFrom int
LocationTo int
etc
Report Format:
Wk Beginning | Num Issued | Num Returned | Running Total | Num Overdue
Any suggestions or pointers?
Thanks in advance,
Chris
cjmnews04@.REMOVEMEyahoo.co.uk
[remove the obvious bits]Do you want to use ISO ws or just a fixed 7-day w?
For a fixed 7-day w:
SELECT ...
FROM YourTable
GROUP BY ROUND(DATEDIFF(D,'20000101',movedate)/7.0,0,1)
(where '20000101' is a "base date" representing your chosen beginning
date of some arbitrary w)
For ISO ws look at the CREATE FUNCTION topic in Books Online for the
relevant formula.
David Portas
SQL Server MVP
--|||SELECT *
FROM <TABLE>
GROUP BY datepart(ww, <date> )
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"CJM" wrote:

> I have a requirement to produce a report that breaks down some data into
> totals by w. The data is in SQL Server, but the user just wants a one-o
ff
> report, so we can use Access or Excel as alternatives is more suitable.
> We have a table of stock movements, and we want to total the number of
> incoming and outgoing items for each w. We obviously know the dates of
> these movements, but I'm unclear as to the SQL (or even general approach)
> needed to break down this data.
> StockMovements Table:
> StockMoveID int
> SerialNo int
> MoveDate int
> LocationFrom int
> LocationTo int
> etc
> Report Format:
> Wk Beginning | Num Issued | Num Returned | Running Total | Num Overdue
> Any suggestions or pointers?
> Thanks in advance,
> Chris
> --
> cjmnews04@.REMOVEMEyahoo.co.uk
> [remove the obvious bits]
>
>|||Be careful with the w returned by DATEPART. The w numbering
convention it uses is a bit unusual. Even assuming that the DATEFIRST
setting is correct many people won't find the result of this function
useful.
David Portas
SQL Server MVP
--|||David,
Thanks for that - it looks to be a start, but I'm not quite there yet.
As I mention before I want the following columns for the report: W
commencing, Total Sent, Total Received, Running Total, & Total Overdue
I've started creating the SQL for the Total Sent/Received, but I still have
a problem - your code provides for a w number but how do I engineer the
W Commencing date from this?
Select Count(*) as NumSent, ROUND(DATEDIFF(D,'20031229',movedate)/7.0,0,1)
as Wk
from StockMovements
where LocationTo = 2
and MoveDate > '20031231'
and MoveDate < '20050601'
GROUP BY ROUND(DATEDIFF(D,'20031229',movedate)/7.0,0,1)
Select Count(*) as NumRecd, ROUND(DATEDIFF(D,'20031229',movedate)/7.0,0,1)
as Wk
from StockMovements
where LocationFrom = 2
and MoveDate > '20031231'
and MoveDate < '20050601'
GROUP BY ROUND(DATEDIFF(D,'20031229',movedate)/7.0,0,1)
Also, how do I combine these into one query? I tried Select (Select
...etc), (Select...etc) but it came up with an error:
"Only one expression can be specified in the select list when the subquery
is not introduced with EXISTS"
I'm not clear on what this means, not how to avoid it.
Thanks for your help so far...
Chris|||You might want to look up the ISO definition of a w within a year,
since it is different from Microsoft's and finally talk to teh
accounting department about the ws in the fiscal calendar. The best
way to handle this is to set up a calendar table with yourt fiscal
ws in it.|||Hello,
I notice you have posted the same question in our newsgroup, which I have
already responded. So please check my answer there.
Sophie Guo
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.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.|||On Thu, 26 May 2005 15:09:54 +0100, CJM wrote:

>Thanks for that - it looks to be a start, but I'm not quite there yet.
>As I mention before I want the following columns for the report: W
>commencing, Total Sent, Total Received, Running Total, & Total Overdue
>I've started creating the SQL for the Total Sent/Received, but I still have
>a problem - your code provides for a w number but how do I engineer the
>W Commencing date from this?
(snip)
>Also, how do I combine these into one query?
Hi CJM,
I noticed that you crossposted this to a SQL Server group and an Access
group. Since my answer works in SQL Server only, I removed the access
group for my reply.
Check if the following works (note: this assumes you consider monday to
be the first day of the wee; change the date constant '20031229' to
something else if you need another first day of the w - and remember
that it has to be changed in all four places it's used!)
SELECT DATEADD(day,
DATEDIFF(day, '20031229', MoveDate) / 7 * 7,
'20031229') AS WCommencingDate,
COUNT(CASE WHEN LocationTo = 2 THEN 1 END) AS TotalSent,
COUNT(CASE WHEN LocationFrom = 2 THEN 1 END) AS TotalReceived
FROM StockMovements
WHERE MoveDate > '20031231'
AND MoveDate < '20050601'
GROUP BY DATEADD(day,
DATEDIFF(day, '20031229', MoveDate) / 7 * 7,
'20031229')
This is untested, since you didn't post CREATE TABLE and INSERT
statements to create a test database. See www.aspfaq.com/5006.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Grouping based on input parameters

I'm not even sure if this is possible. Our requirement is that the user can input whether to view hourly, daily, or monthly data. Originally I had created three separate reports, but I've been asked to try again.

Is it possible to specify when grouping to group by the whole operation date, or group by days, or group by weeks?

Thanks.Its possible if the grouping is done in logical way say days->weeks->months->years.
Trying to say that if you want to group by Monday or tuesday then the answer is no.
Now what you do is in one report you start from inside grouping by days, then by weeks, then by months and then by years.
Make a parameter through which you can suppress the section accordingly.
Hope you can understand my theory.|||Thank you for your explanation. That was a lot easier than I thought!

:)|||I've been re-doing one of my other reports and noticed that my solution still has one problem with it. I have grouped by (from outer level) : Year, Month, Product Type.

When I view the product type by month, all are listed as one would expect. However, when viewing by year they are not grouped correctly, ie :

Jan 2005
prod x - 5
prod y - 3
prod z - 4

Feb 2005
prod x - 1
prod y - 7

So when viewing by year, I would like to see Prod x - 6. Instead, I get the two individual listings of prod x, etc.

Is there a way around this?

Thanks.|||You should expect only one entry per year if you have done gouping by year->month->type.
Right click on Group Producttype and click on change group and make sure that Order is in ascending and not Original order.

B.thakkar|||Thanks, though everything is grouped with order ascending. I think the problem is that they are essentially still grouped by month (we just choose not to acknowledge/display this by suppressing the header).