Highest week of sales/usage

I'm working with Crystal Reports 10 with a Sql Server 2000 database. I've got a table which stores the details of items including the saletimes (a date/time field). I need to calculate the sales figure for the highest week of sales. In other words I need to find out what the highest amount of sales recorded in any given week. How can I do this?Quickest way would be to create a group based on date, and in the group options select "for each week" as the required grouping.
Create Sum(sigma sysmbol) fields for the data.
Suppress your details section.
In the group footer you will have Totals based on weekly aggregates.
In the Report menu, select TopN/Sort Group Expert.
Select the Tab for your weekly group (if you have more than one group that is).
In"for This Group Sort" change All to TopN.
Enter 1 in the "Where N is" field.
Remove the tick from the "Show others" checkbox.
OK that dialog.
Run you report. Should see only highest weeks.
May want to format the Date Field in your group to show start end end date of the week that finished top........

Hope this helps

dave|||re above - this is all client-side in Crystal.
would be quicker if could achieve it in SQL on server-side, but above solution is quick-ish, because Crystal's query to SQL Server will include an SQL-compliant "TopN" modifier.

dave

Hierarchical table (count)

Hello

for MS SQL 2000 i am having :

CREATE TABLE [dbo].[Items](
[id_Items] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,
[id_ItemsSup] [int] NULL,
[Name] [nvarchar] (100) NOT NULL,
[SubItems][int] DEFAULT (0)
) ON [PRIMARY]

with :
UPDATE [Items] SET SubItems = (SELECT COUNT(id_Items) AS ct FROM dbo.Items WHERE id_ItemsSup = 1) WHERE id_Items = 1
I get how many subItems has Item = 1

how can I update the Column SubItems (for each row) ?
to get the total of subItems for each Item ?

thank youupdate Items set subitems = (select count(*) from items where Id_ItemsSup = A.Id_ItemsSup) from Items A|||it works fine

thank you

Hiding Main Report Items based on SubReport Value

Hi Group,
How do i Conditionally Hide the Main Report Iitem based on the SubReport
Value.
My Main Report Looks Like This
Task Id Description Effort
1020 Project Plan 100(Subreport Item)
What i want is if the effort is Zero i Should hide 1020,Project Plan and My
Subreport Value,Items How do i do
Respond ASAPThe main report cannot reach inside the subreport(s) to extract values. You
would need to make sure the relevant value is available in your main query.
--
This post is provided 'AS IS' with no warranties, and confers no rights. All
rights reserved. Some assembly required. Batteries not included. Your
mileage may vary. Objects in mirror may be closer than they appear. No user
serviceable parts inside. Opening cover voids warranty. Keep out of reach of
children under 3.
"Manoj.Pasumarthi" <ManojPasumarthi@.discussions.microsoft.com> wrote in
message news:D0A47874-C1C6-4D0A-88AA-A96BE0E27094@.microsoft.com...
> Hi Group,
> How do i Conditionally Hide the Main Report Iitem based on the SubReport
> Value.
> My Main Report Looks Like This
> Task Id Description Effort
> 1020 Project Plan 100(Subreport Item)
> What i want is if the effort is Zero i Should hide 1020,Project Plan and
My
> Subreport Value,Items How do i do
> Respond ASAP
>|||Hi Chris,
Thank u
"Chris Hays [MSFT]" wrote:
> The main report cannot reach inside the subreport(s) to extract values. You
> would need to make sure the relevant value is available in your main query.
> --
> This post is provided 'AS IS' with no warranties, and confers no rights. All
> rights reserved. Some assembly required. Batteries not included. Your
> mileage may vary. Objects in mirror may be closer than they appear. No user
> serviceable parts inside. Opening cover voids warranty. Keep out of reach of
> children under 3.
> "Manoj.Pasumarthi" <ManojPasumarthi@.discussions.microsoft.com> wrote in
> message news:D0A47874-C1C6-4D0A-88AA-A96BE0E27094@.microsoft.com...
> > Hi Group,
> >
> > How do i Conditionally Hide the Main Report Iitem based on the SubReport
> > Value.
> > My Main Report Looks Like This
> >
> > Task Id Description Effort
> > 1020 Project Plan 100(Subreport Item)
> >
> > What i want is if the effort is Zero i Should hide 1020,Project Plan and
> My
> > Subreport Value,Items How do i do
> >
> > Respond ASAP
> >
> >
>
>|||"Chris Hays [MSFT]" wrote:
> The main report cannot reach inside the subreport(s) to extract values. You
> would need to make sure the relevant value is available in your main query.
> --
> This post is provided 'AS IS' with no warranties, and confers no rights. All
> rights reserved. Some assembly required. Batteries not included. Your
> mileage may vary. Objects in mirror may be closer than they appear. No user
> serviceable parts inside. Opening cover voids warranty. Keep out of reach of
> children under 3.
> "Manoj.Pasumarthi" <ManojPasumarthi@.discussions.microsoft.com> wrote in
> message news:D0A47874-C1C6-4D0A-88AA-A96BE0E27094@.microsoft.com...
> > Hi Group,
> >
> > How do i Conditionally Hide the Main Report Iitem based on the SubReport
> > Value.
> > My Main Report Looks Like This
> >
> > Task Id Description Effort
> > 1020 Project Plan 100(Subreport Item)
> >
> > What i want is if the effort is Zero i Should hide 1020,Project Plan and
> My
> > Subreport Value,Items How do i do
> >
> > Respond ASAP
> >
> >
>
>|||I'm guessing the original question was in regards to RS 2000. Has the answer
changed with RS 2005? Can subreport values be referenced from the master
report in RS 2005
"Chris Hays [MSFT]" wrote:
> The main report cannot reach inside the subreport(s) to extract values. You
> would need to make sure the relevant value is available in your main query.
> --
> This post is provided 'AS IS' with no warranties, and confers no rights. All
> rights reserved. Some assembly required. Batteries not included. Your
> mileage may vary. Objects in mirror may be closer than they appear. No user
> serviceable parts inside. Opening cover voids warranty. Keep out of reach of
> children under 3.
> "Manoj.Pasumarthi" <ManojPasumarthi@.discussions.microsoft.com> wrote in
> message news:D0A47874-C1C6-4D0A-88AA-A96BE0E27094@.microsoft.com...
> > Hi Group,
> >
> > How do i Conditionally Hide the Main Report Iitem based on the SubReport
> > Value.
> > My Main Report Looks Like This
> >
> > Task Id Description Effort
> > 1020 Project Plan 100(Subreport Item)
> >
> > What i want is if the effort is Zero i Should hide 1020,Project Plan and
> My
> > Subreport Value,Items How do i do
> >
> > Respond ASAP
> >
> >
>
>