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

High security Needs, Cheep Clients, Need Help

I have an existing server hosting several sites with associated back end
databases.
I have a new client who needs secure hosting solutions, including a secure
database, without the expense of a new server. I was thinking of the
following architecture. I know some ( most ) of this is not SQL related,
but bear with me please.
I am hosting web services on the server to provide the interface with the
external world. End-Use clients will have applications with certificates to
access these web services. I have two instances of SQL Server 2005 running,
MYSERVER\PUBLIC & MYSERVER\PRIVATE.
MYSERVER\PUBLIC contains all my existing, non-secure, databases plus some
info relevant to the new service which need not be secure.
MYSERVER\PRIVATE will contain all the new, to be secured, data.
MYSERVER\PRIVATE listens only on TCPIP Address 127.0.0.1, so should be
available only as 'localhost' from within the server, i.e. not be public to
the outside world. My web services should be able to access this data, and
the web services will only be accessible to these signed applications. Of
course, any relevant info would be encrypted.
Is this sufficient ?
Any input would be appreciated
Thanks
PhilYou have to make sure that you are using different service accounts for each
instance otherwise someone gaining elevated rights within one instance will
be able to get to the other instance. Additionally, you should not allow
any SQL logins to either instance. The web server should be configured to
connect to the instances using Windows credentials which forces anyone who
can not authenticate in Windows to not have access to either database
instance. The IIS server needs to be locked down to ensure that you don't
have any security holes. The service accounts for each instance should be
regular Windows accounts and NOT a member of local administrators or even
worse domain/enterprise administrators.
After accomplishing that, you pretty much have the front door closed. Now
you have to worry about all of the back end stuff. How are you going to
handle backups of the databases such that the data in your secure instance
remains secure even through the backup files? How are you going to control
administrator and developer access within your company to the secured
instance? How are you going to audit the operations within the secure
instance such that your customer on this secure instance can't create a
situation where they actually damaged data or caused the security to be
compromised, but are pointing the finger at you?
Mike
MHS Enterprises, Inc
Disclaimer: This communication is an original work and represents my sole
views on the subject. It does not represent the views of any other person
or entity either by inference or direct reference.
"Phil" <prounds@.cassandragroup.com> wrote in message
news:eFeo0WchGHA.4080@.TK2MSFTNGP03.phx.gbl...
>I have an existing server hosting several sites with associated back end
>databases.
> I have a new client who needs secure hosting solutions, including a secure
> database, without the expense of a new server. I was thinking of the
> following architecture. I know some ( most ) of this is not SQL related,
> but bear with me please.
> I am hosting web services on the server to provide the interface with the
> external world. End-Use clients will have applications with certificates
> to access these web services. I have two instances of SQL Server 2005
> running, MYSERVER\PUBLIC & MYSERVER\PRIVATE.
> MYSERVER\PUBLIC contains all my existing, non-secure, databases plus some
> info relevant to the new service which need not be secure.
> MYSERVER\PRIVATE will contain all the new, to be secured, data.
> MYSERVER\PRIVATE listens only on TCPIP Address 127.0.0.1, so should be
> available only as 'localhost' from within the server, i.e. not be public
> to the outside world. My web services should be able to access this data,
> and the web services will only be accessible to these signed applications.
> Of course, any relevant info would be encrypted.
> Is this sufficient ?
> Any input would be appreciated
> Thanks
> Phil
>|||"Phil" <prounds@.cassandragroup.com> wrote in message
news:eFeo0WchGHA.4080@.TK2MSFTNGP03.phx.gbl...
>I have an existing server hosting several sites with associated back end
>databases.
> I have a new client who needs secure hosting solutions, including a secure
> database, without the expense of a new server. I was thinking of the
> following architecture. I know some ( most ) of this is not SQL related,
> but bear with me please.
> I am hosting web services on the server to provide the interface with the
> external world. End-Use clients will have applications with certificates
> to access these web services. I have two instances of SQL Server 2005
> running, MYSERVER\PUBLIC & MYSERVER\PRIVATE.
> MYSERVER\PUBLIC contains all my existing, non-secure, databases plus some
> info relevant to the new service which need not be secure.
> MYSERVER\PRIVATE will contain all the new, to be secured, data.
> MYSERVER\PRIVATE listens only on TCPIP Address 127.0.0.1, so should be
> available only as 'localhost' from within the server, i.e. not be public
> to the outside world. My web services should be able to access this data,
> and the web services will only be accessible to these signed applications.
> Of course, any relevant info would be encrypted.
> Is this sufficient ?
>
Can't tell if it's sufficient, but it should work.
Also don't even need to enable TCP/IP for the PRIVATE instance. Programs
running on the same box can use the shared memory provider, which only works
locally. Also make sure to put the private web apps in their own
application pool (or web site), and use a seperate set of Windows accounts
to run the secure web apps, connect to SQL and as the SQL Service account.
Segegate the data and use NTFS permissions to restrict read/write to the
database files, logs and backups.
David

hiding group header conditionally

Hi,
I have an issue with reporting services report. I am including the rdl code
at the bottom of this email. In that report there are two levels of data
shown. The top level is manager id and the level below that is some info
about employees belonging to this manager id. The initial visibility of top
level is visible and second level is hidden with manager id as toggle item.
Now my question comes.
If for some reason in the database all entries for managerid column is null,
then the above mentioned report will have one plus sign alone and all the
employees will show up if we click on that plus sign. (This situation can be
achieved by changing our query to have all nulls for manager id column). Now
Please tell me if such a situation occurs how should I write the conditonal
expression such that the top level row is totally invisible(i.e plus sign
should not be shown as I want the group table1_group1 to be hidden under this
situation) and all employees info should show up directly when the report is
rendered.
Is this possible? if so how it should be done?
Thanks.
RDL File
--
<?xml version="1.0" encoding="utf-8"?>
<Report
xmlns="http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition"
xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
<DataSources>
<DataSource Name="dsrcEmployee">
<DataSourceReference>dsrcEmployee</DataSourceReference>
<rd:DataSourceID>c497ccbe-747c-4454-8e32-bb33161f9921</rd:DataSourceID>
</DataSource>
</DataSources>
<BottomMargin>1in</BottomMargin>
<RightMargin>1in</RightMargin>
<rd:DrawGrid>true</rd:DrawGrid>
<InteractiveWidth>8.5in</InteractiveWidth>
<rd:SnapToGrid>true</rd:SnapToGrid>
<Body>
<ReportItems>
<Table Name="table1">
<Footer>
<TableRows>
<TableRow>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox7">
<rd:DefaultName>textbox7</rd:DefaultName>
<ZIndex>7</ZIndex>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox8">
<rd:DefaultName>textbox8</rd:DefaultName>
<ZIndex>6</ZIndex>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox9">
<rd:DefaultName>textbox9</rd:DefaultName>
<ZIndex>5</ZIndex>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox6">
<rd:DefaultName>textbox6</rd:DefaultName>
<ZIndex>4</ZIndex>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
<Height>0.33333in</Height>
</TableRow>
</TableRows>
</Footer>
<Left>0.5in</Left>
<DataSetName>DataSet1</DataSetName>
<Top>0.375in</Top>
<TableGroups>
<TableGroup>
<Header>
<TableRows>
<TableRow>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="ManagerID">
<rd:DefaultName>ManagerID</rd:DefaultName>
<ZIndex>11</ZIndex>
<Style>
<TextAlign>Left</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Fields!ManagerID.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox10">
<rd:DefaultName>textbox10</rd:DefaultName>
<ZIndex>10</ZIndex>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox11">
<rd:DefaultName>textbox11</rd:DefaultName>
<ZIndex>9</ZIndex>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox12">
<rd:DefaultName>textbox12</rd:DefaultName>
<ZIndex>8</ZIndex>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
<Height>0.25in</Height>
</TableRow>
</TableRows>
</Header>
<Grouping Name="table1_Group1">
<GroupExpressions>
<GroupExpression>=Fields!ManagerID.Value</GroupExpression>
</GroupExpressions>
</Grouping>
</TableGroup>
</TableGroups>
<Details>
<TableRows>
<TableRow>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="EmployeeName">
<rd:DefaultName>EmployeeName</rd:DefaultName>
<ZIndex>3</ZIndex>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Fields!EmployeeName.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="EmailAddress">
<rd:DefaultName>EmailAddress</rd:DefaultName>
<ZIndex>2</ZIndex>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Fields!EmailAddress.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="Phone">
<rd:DefaultName>Phone</rd:DefaultName>
<ZIndex>1</ZIndex>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Fields!Phone.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="Gender">
<rd:DefaultName>Gender</rd:DefaultName>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Fields!Gender.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
<Height>0.33333in</Height>
</TableRow>
</TableRows>
<Visibility>
<ToggleItem>ManagerID</ToggleItem>
<Hidden>true</Hidden>
</Visibility>
</Details>
<Header>
<TableRows>
<TableRow>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox1">
<rd:DefaultName>textbox1</rd:DefaultName>
<ZIndex>15</ZIndex>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>Employee Name</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox2">
<rd:DefaultName>textbox2</rd:DefaultName>
<ZIndex>14</ZIndex>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>Email Address</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox3">
<rd:DefaultName>textbox3</rd:DefaultName>
<ZIndex>13</ZIndex>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>Phone</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox4">
<rd:DefaultName>textbox4</rd:DefaultName>
<ZIndex>12</ZIndex>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>Gender</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
<Height>0.33333in</Height>
</TableRow>
</TableRows>
</Header>
<TableColumns>
<TableColumn>
<Width>1.66667in</Width>
</TableColumn>
<TableColumn>
<Width>1.66667in</Width>
</TableColumn>
<TableColumn>
<Width>1.66667in</Width>
</TableColumn>
<TableColumn>
<Width>1.66667in</Width>
</TableColumn>
</TableColumns>
<Height>1.25in</Height>
</Table>
</ReportItems>
<Height>2in</Height>
</Body>
<rd:ReportID>3b3ab633-ae51-4bcf-8abd-bf6113721db1</rd:ReportID>
<LeftMargin>1in</LeftMargin>
<DataSets>
<DataSet Name="DataSet1">
<Query>
<rd:UseGenericDesigner>true</rd:UseGenericDesigner>
<CommandText>select ManagerID,isnull(ct.Title,' ') + ' ' +
isnull(ct.FirstName,' ') + ' ' + isnull(ct.MiddleName,' ') + ' ' +
isnull(ct.LastName,' ') as EmployeeName,ct.EmailAddress,ct.Phone,Gender=case
emp.Gender when 'M' then 'Male' when 'F' then 'Female' else 'Unknown' end
from HumanResources.Employee emp inner join Person.Contact ct on
emp.ContactID = ct.ContactID</CommandText>
<DataSourceName>dsrcEmployee</DataSourceName>
</Query>
<Fields>
<Field Name="ManagerID">
<rd:TypeName>System.Int32</rd:TypeName>
<DataField>ManagerID</DataField>
</Field>
<Field Name="EmployeeName">
<rd:TypeName>System.String</rd:TypeName>
<DataField>EmployeeName</DataField>
</Field>
<Field Name="EmailAddress">
<rd:TypeName>System.String</rd:TypeName>
<DataField>EmailAddress</DataField>
</Field>
<Field Name="Phone">
<rd:TypeName>System.String</rd:TypeName>
<DataField>Phone</DataField>
</Field>
<Field Name="Gender">
<rd:TypeName>System.String</rd:TypeName>
<DataField>Gender</DataField>
</Field>
</Fields>
</DataSet>
</DataSets>
<Width>7.16667in</Width>
<InteractiveHeight>11in</InteractiveHeight>
<Language>en-US</Language>
<TopMargin>1in</TopMargin>
</Report>I don't think you can toggle the expand/collapse for each toggle item,
in this case the manager ID. Since it is basically working for you,
except when there isn't a manger ID, why don't you modify your dataset
so that a no manger ID message is outputed instead of nulls?
On Mon, 5 Jun 2006 08:47:01 -0700, msdnuser <ringt@.nospam.nospam>
wrote:
>Hi,
>I have an issue with reporting services report. I am including the rdl code
>at the bottom of this email. In that report there are two levels of data
>shown. The top level is manager id and the level below that is some info
>about employees belonging to this manager id. The initial visibility of top
>level is visible and second level is hidden with manager id as toggle item.
>Now my question comes.
>If for some reason in the database all entries for managerid column is null,
>then the above mentioned report will have one plus sign alone and all the
>employees will show up if we click on that plus sign. (This situation can be
>achieved by changing our query to have all nulls for manager id column). Now
>Please tell me if such a situation occurs how should I write the conditonal
>expression such that the top level row is totally invisible(i.e plus sign
>should not be shown as I want the group table1_group1 to be hidden under this
>situation) and all employees info should show up directly when the report is
>rendered.
>Is this possible? if so how it should be done?
>Thanks.
>RDL File (snipped by responder)|||Hello Ringt,
You can try selecting the certain table row (which contains the group
header ) and right click it and choose "Edit Group" menu item. In the
displayed dialog, we choose the "visibility" tab, we can find that we can
define some expression to control the visibility of it.
Regards,
Steven Cheng
Microsoft Online Community Support
==================================================
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.
Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)