Count Records in a Group

Hello All.
I'm running SSRS 2005 on top of a sql2000 database.
I need to create a report that counts the number of invoices salesreps
process per month.
The fields I have selected are;
SalesRep ID Invoice No Invoice Date
1 5467 2/22/2006 3:06:17 PM
5 4526 2/22/2006 3:29:56 PM
8 6589 6/14/2005 4:20:26 PM
5 8569 2/22/2006 3:29:56 PM
5 2563 6/10/2007 8:29:56 AM
5 1523 2/22/2006 3:29:56 PM
8 9876 8/23/2006 5:29:56 PM
1 7563 4/23/2006 1:29:56 PM
What i want to do is group by Salesrep ID, then show the total number of
invoices that rep did per month;
SalesRep ID Month TotalInvoices Processed
1 Jan 4
Feb 2
Mar 5
5 Jan 5
Feb 6
Mar 3
8 Jan 2
Feb 10
Mar 20 ... and so on.
I'm not sure exactly how to do this. Also how do I convert the date format
into just showing the month, not every minute of every day?
Any help is much appreciated.
Thanks.You want to do this with the sql statement. Since you didn't show your SQL I
had to make up names.
select a.salesrepid, datepart(month, a.invoicedate) as month, count(*) as
invoices_count from yourtable a
group by a.salesrepid, datepart(month, a.invoicedate)
order by a.salesrepid, month
Note this gives you month by number which is what you need in order to order
it properly, if you want by month name then add that in and still order by
the month number to keep in in the proper order.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Damon Johnson" <DamonJohnson@.discussions.microsoft.com> wrote in message
news:410E3C42-D07E-4388-97A7-E6278586890F@.microsoft.com...
> Hello All.
> I'm running SSRS 2005 on top of a sql2000 database.
> I need to create a report that counts the number of invoices salesreps
> process per month.
> The fields I have selected are;
> SalesRep ID Invoice No Invoice Date
>
> 1 5467 2/22/2006 3:06:17 PM
> 5 4526 2/22/2006 3:29:56 PM
> 8 6589 6/14/2005 4:20:26 PM
> 5 8569 2/22/2006 3:29:56 PM
> 5 2563 6/10/2007 8:29:56 AM
> 5 1523 2/22/2006 3:29:56 PM
> 8 9876 8/23/2006 5:29:56 PM
> 1 7563 4/23/2006 1:29:56 PM
> What i want to do is group by Salesrep ID, then show the total number of
> invoices that rep did per month;
> SalesRep ID Month TotalInvoices Processed
> 1 Jan 4
> Feb 2
> Mar 5
> 5 Jan 5
> Feb 6
> Mar 3
> 8 Jan 2
> Feb 10
> Mar 20 ... and so on.
>
> I'm not sure exactly how to do this. Also how do I convert the date format
> into just showing the month, not every minute of every day?
> Any help is much appreciated.
> Thanks.
>|||Thank you soo much Bruce.
Here is the statement;
SELECT TOP 100 PERCENT dbo.invoice_hdr.salesrep_id,
dbo.invoice_hdr.order_date
FROM dbo.contacts INNER JOIN
dbo.invoice_hdr ON dbo.contacts.id =dbo.invoice_hdr.salesrep_id
"Bruce L-C [MVP]" wrote:
> You want to do this with the sql statement. Since you didn't show your SQL I
> had to make up names.
> select a.salesrepid, datepart(month, a.invoicedate) as month, count(*) as
> invoices_count from yourtable a
> group by a.salesrepid, datepart(month, a.invoicedate)
> order by a.salesrepid, month
> Note this gives you month by number which is what you need in order to order
> it properly, if you want by month name then add that in and still order by
> the month number to keep in in the proper order.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>
> "Damon Johnson" <DamonJohnson@.discussions.microsoft.com> wrote in message
> news:410E3C42-D07E-4388-97A7-E6278586890F@.microsoft.com...
> > Hello All.
> >
> > I'm running SSRS 2005 on top of a sql2000 database.
> >
> > I need to create a report that counts the number of invoices salesreps
> > process per month.
> >
> > The fields I have selected are;
> >
> > SalesRep ID Invoice No Invoice Date
> >
> >
> > 1 5467 2/22/2006 3:06:17 PM
> > 5 4526 2/22/2006 3:29:56 PM
> > 8 6589 6/14/2005 4:20:26 PM
> > 5 8569 2/22/2006 3:29:56 PM
> > 5 2563 6/10/2007 8:29:56 AM
> > 5 1523 2/22/2006 3:29:56 PM
> > 8 9876 8/23/2006 5:29:56 PM
> > 1 7563 4/23/2006 1:29:56 PM
> >
> > What i want to do is group by Salesrep ID, then show the total number of
> > invoices that rep did per month;
> >
> > SalesRep ID Month TotalInvoices Processed
> > 1 Jan 4
> > Feb 2
> > Mar 5
> >
> > 5 Jan 5
> > Feb 6
> > Mar 3
> >
> > 8 Jan 2
> > Feb 10
> > Mar 20 ... and so on.
> >
> >
> > I'm not sure exactly how to do this. Also how do I convert the date format
> > into just showing the month, not every minute of every day?
> >
> > Any help is much appreciated.
> > Thanks.
> >
>
>|||Bruce that worked perfectly!!
Thanks again!
"Damon Johnson" wrote:
> Thank you soo much Bruce.
> Here is the statement;
> SELECT TOP 100 PERCENT dbo.invoice_hdr.salesrep_id,
> dbo.invoice_hdr.order_date
> FROM dbo.contacts INNER JOIN
> dbo.invoice_hdr ON dbo.contacts.id => dbo.invoice_hdr.salesrep_id
> "Bruce L-C [MVP]" wrote:
> > You want to do this with the sql statement. Since you didn't show your SQL I
> > had to make up names.
> >
> > select a.salesrepid, datepart(month, a.invoicedate) as month, count(*) as
> > invoices_count from yourtable a
> > group by a.salesrepid, datepart(month, a.invoicedate)
> > order by a.salesrepid, month
> >
> > Note this gives you month by number which is what you need in order to order
> > it properly, if you want by month name then add that in and still order by
> > the month number to keep in in the proper order.
> >
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> >
> > "Damon Johnson" <DamonJohnson@.discussions.microsoft.com> wrote in message
> > news:410E3C42-D07E-4388-97A7-E6278586890F@.microsoft.com...
> > > Hello All.
> > >
> > > I'm running SSRS 2005 on top of a sql2000 database.
> > >
> > > I need to create a report that counts the number of invoices salesreps
> > > process per month.
> > >
> > > The fields I have selected are;
> > >
> > > SalesRep ID Invoice No Invoice Date
> > >
> > >
> > > 1 5467 2/22/2006 3:06:17 PM
> > > 5 4526 2/22/2006 3:29:56 PM
> > > 8 6589 6/14/2005 4:20:26 PM
> > > 5 8569 2/22/2006 3:29:56 PM
> > > 5 2563 6/10/2007 8:29:56 AM
> > > 5 1523 2/22/2006 3:29:56 PM
> > > 8 9876 8/23/2006 5:29:56 PM
> > > 1 7563 4/23/2006 1:29:56 PM
> > >
> > > What i want to do is group by Salesrep ID, then show the total number of
> > > invoices that rep did per month;
> > >
> > > SalesRep ID Month TotalInvoices Processed
> > > 1 Jan 4
> > > Feb 2
> > > Mar 5
> > >
> > > 5 Jan 5
> > > Feb 6
> > > Mar 3
> > >
> > > 8 Jan 2
> > > Feb 10
> > > Mar 20 ... and so on.
> > >
> > >
> > > I'm not sure exactly how to do this. Also how do I convert the date format
> > > into just showing the month, not every minute of every day?
> > >
> > > Any help is much appreciated.
> > > Thanks.
> > >
> >
> >
> >|||First, you do not need TOP 100 percent. That means you want all the records
which is what you get without the TOP syntax.
SELECT a.salesrep_id, datepart(month,b.order_date) as Month,
datename(month,b.order_date) as Month_Name,count(*) as invoices_count
FROM dbo.contacts a INNER JOIN dbo.invoice_hdr b ON a.id =b.salesrep_id
where b.order_date >= @.STARTDATE and b.order_date < @.ENDDATE
group by a.salesrep_id, datepart(month,b.order_date)
order by a.salesrep_id, month
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Damon Johnson" <DamonJohnson@.discussions.microsoft.com> wrote in message
news:8C3A09EC-3B59-4263-BD46-82880D31C84B@.microsoft.com...
> Thank you soo much Bruce.
> Here is the statement;
> SELECT TOP 100 PERCENT dbo.invoice_hdr.salesrep_id,
> dbo.invoice_hdr.order_date
> FROM dbo.contacts INNER JOIN
> dbo.invoice_hdr ON dbo.contacts.id => dbo.invoice_hdr.salesrep_id
> "Bruce L-C [MVP]" wrote:
>> You want to do this with the sql statement. Since you didn't show your
>> SQL I
>> had to make up names.
>> select a.salesrepid, datepart(month, a.invoicedate) as month, count(*) as
>> invoices_count from yourtable a
>> group by a.salesrepid, datepart(month, a.invoicedate)
>> order by a.salesrepid, month
>> Note this gives you month by number which is what you need in order to
>> order
>> it properly, if you want by month name then add that in and still order
>> by
>> the month number to keep in in the proper order.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>>
>> "Damon Johnson" <DamonJohnson@.discussions.microsoft.com> wrote in message
>> news:410E3C42-D07E-4388-97A7-E6278586890F@.microsoft.com...
>> > Hello All.
>> >
>> > I'm running SSRS 2005 on top of a sql2000 database.
>> >
>> > I need to create a report that counts the number of invoices salesreps
>> > process per month.
>> >
>> > The fields I have selected are;
>> >
>> > SalesRep ID Invoice No Invoice Date
>> >
>> >
>> > 1 5467 2/22/2006 3:06:17 PM
>> > 5 4526 2/22/2006 3:29:56 PM
>> > 8 6589 6/14/2005 4:20:26 PM
>> > 5 8569 2/22/2006 3:29:56 PM
>> > 5 2563 6/10/2007 8:29:56 AM
>> > 5 1523 2/22/2006 3:29:56 PM
>> > 8 9876 8/23/2006 5:29:56 PM
>> > 1 7563 4/23/2006 1:29:56 PM
>> >
>> > What i want to do is group by Salesrep ID, then show the total number
>> > of
>> > invoices that rep did per month;
>> >
>> > SalesRep ID Month TotalInvoices Processed
>> > 1 Jan 4
>> > Feb 2
>> > Mar 5
>> >
>> > 5 Jan 5
>> > Feb 6
>> > Mar 3
>> >
>> > 8 Jan 2
>> > Feb 10
>> > Mar 20 ... and so on.
>> >
>> >
>> > I'm not sure exactly how to do this. Also how do I convert the date
>> > format
>> > into just showing the month, not every minute of every day?
>> >
>> > Any help is much appreciated.
>> > Thanks.
>> >
>>|||OK Bruce I think this is my last request.
the DATENAME(month, invoice_date) returns the month only.
How do i get it to return the month and year. I will be setting this up as a
parameter query;
Between @.StartDate and @.EndDate
and want the user to put in May 07 and June 07
thanks again.
"Bruce L-C [MVP]" wrote:
> First, you do not need TOP 100 percent. That means you want all the records
> which is what you get without the TOP syntax.
> SELECT a.salesrep_id, datepart(month,b.order_date) as Month,
> datename(month,b.order_date) as Month_Name,count(*) as invoices_count
> FROM dbo.contacts a INNER JOIN dbo.invoice_hdr b ON a.id => b.salesrep_id
> where b.order_date >= @.STARTDATE and b.order_date < @.ENDDATE
> group by a.salesrep_id, datepart(month,b.order_date)
> order by a.salesrep_id, month
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>
> "Damon Johnson" <DamonJohnson@.discussions.microsoft.com> wrote in message
> news:8C3A09EC-3B59-4263-BD46-82880D31C84B@.microsoft.com...
> > Thank you soo much Bruce.
> > Here is the statement;
> >
> > SELECT TOP 100 PERCENT dbo.invoice_hdr.salesrep_id,
> > dbo.invoice_hdr.order_date
> > FROM dbo.contacts INNER JOIN
> > dbo.invoice_hdr ON dbo.contacts.id => > dbo.invoice_hdr.salesrep_id
> >
> > "Bruce L-C [MVP]" wrote:
> >
> >> You want to do this with the sql statement. Since you didn't show your
> >> SQL I
> >> had to make up names.
> >>
> >> select a.salesrepid, datepart(month, a.invoicedate) as month, count(*) as
> >> invoices_count from yourtable a
> >> group by a.salesrepid, datepart(month, a.invoicedate)
> >> order by a.salesrepid, month
> >>
> >> Note this gives you month by number which is what you need in order to
> >> order
> >> it properly, if you want by month name then add that in and still order
> >> by
> >> the month number to keep in in the proper order.
> >>
> >>
> >> --
> >> Bruce Loehle-Conger
> >> MVP SQL Server Reporting Services
> >>
> >>
> >> "Damon Johnson" <DamonJohnson@.discussions.microsoft.com> wrote in message
> >> news:410E3C42-D07E-4388-97A7-E6278586890F@.microsoft.com...
> >> > Hello All.
> >> >
> >> > I'm running SSRS 2005 on top of a sql2000 database.
> >> >
> >> > I need to create a report that counts the number of invoices salesreps
> >> > process per month.
> >> >
> >> > The fields I have selected are;
> >> >
> >> > SalesRep ID Invoice No Invoice Date
> >> >
> >> >
> >> > 1 5467 2/22/2006 3:06:17 PM
> >> > 5 4526 2/22/2006 3:29:56 PM
> >> > 8 6589 6/14/2005 4:20:26 PM
> >> > 5 8569 2/22/2006 3:29:56 PM
> >> > 5 2563 6/10/2007 8:29:56 AM
> >> > 5 1523 2/22/2006 3:29:56 PM
> >> > 8 9876 8/23/2006 5:29:56 PM
> >> > 1 7563 4/23/2006 1:29:56 PM
> >> >
> >> > What i want to do is group by Salesrep ID, then show the total number
> >> > of
> >> > invoices that rep did per month;
> >> >
> >> > SalesRep ID Month TotalInvoices Processed
> >> > 1 Jan 4
> >> > Feb 2
> >> > Mar 5
> >> >
> >> > 5 Jan 5
> >> > Feb 6
> >> > Mar 3
> >> >
> >> > 8 Jan 2
> >> > Feb 10
> >> > Mar 20 ... and so on.
> >> >
> >> >
> >> > I'm not sure exactly how to do this. Also how do I convert the date
> >> > format
> >> > into just showing the month, not every minute of every day?
> >> >
> >> > Any help is much appreciated.
> >> > Thanks.
> >> >
> >>
> >>
> >>
>
>

Could not process 'sp_replcmds' LogReader

I received the following error message after fours days of
SQL Server 2000 transactional replication.
Output Error from LogReader:
The process could not process 'sp_replcmds' command.
What would cause this error and stop the LogReader?
Thank You,
Dan
do logging as detailed in
http://support.microsoft.com/default...b;EN-US;312292
Post the log back here when you are done. This should give you more details
as to what the exact problem is.
"Dan" <anonymous@.discussions.microsoft.com> wrote in message
news:1c67501c42221$165bda00$a401280a@.phx.gbl...
> I received the following error message after fours days of
> SQL Server 2000 transactional replication.
> Output Error from LogReader:
> The process could not process 'sp_replcmds' command.
> What would cause this error and stop the LogReader?
> Thank You,
> Dan
>
>

Could not load file or assembly SQLXMLBULKLOADLib

Greetings,

I'm in the process of migrating several SQL Server 2000 DTS packages to Integration Services packages. One of the old 2000 DTS packages used the SQLXML Bulk Loader component. In order to use the new SQLXML 4 COM object in my Script Task (to initiate the Bulk Loader using .NET code) I've used the tlbimp.exe tool to create a .NET wrapper DLL. I've placed the DLL in the appropriate directory (C:\Program Files\Microsoft SQL Server\90\SDK\Assemblies), successfully added it to my project (with Intellisense working), but when I run the package it fails with the following error:

Could not load file or assembly 'SQLXMLBULKLOADLib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=null' or one of its dependencies. The system cannot find the file specified.

Note: I also tried placing the DLL in C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727 with no prevail.

I've confirm the file exists (and any dependencies) in their appropriate locations. Has anyone else run into this? Any help is much appreciated.

Thanks, Shaun

i'm not sure if this resolves your issue, but have you considered using the ole db destination component to bulk load the data?|||Reason I leaned toward the SQLXML Bulk Loader class is because i have a ton of XML I'm downloading and want to leverage the performance of the XML bulk loader. I'm not aware of how the OLE DB Destination object can help me with this.|||

I've resolved this issue by using... well... what I'm supposed to use. I created a Data Flow task and implemented an XML Source (applying an XSD transformation), Data Conversion (to convert from Unicode) and Ole Db Destination. This successfully loads the data into the database from XML.

|||shaun,

i'm glad that you found a solution to your problem. as you probably already know, the ole db destination is designed to bulk load data.|||

Did you add it to the GAC? You need to add it to the framework folder for design-time only (C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727) but at runtime it uses the GAC.

I think the better solution is to re-write to use the XML Source as this is very powerful and handles hierarchical data, but obviously this may not be ideal if you have a lot of packages and XMl formats.

Could not find the index entry for RID

Hi all, got this error and well want some advice really. Cheers
The process could not enumerate changes at the 'Subscriber'.
(Source: Merge Replication Provider (Agent); Error number: -2147200999)
------
Could not find the index entry for RID
'16f8db910fd437730d2cca409ba472f23ca363ef200' in index page (1:353425),
index ID 0, database 'VMR'.
(Source: BBSERVER-SAT1 (Data source); Error number: 644)
------
The merge process encountered an unexpected network error. The connection to
Subscriber 'BBSERVER-SAT1' is no longer available.
(Source: Merge Process (Agent); Error number: -2147199469)
------
Put the database in single user mode and run a dbcc dbreindex, this normally
solves this problem.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"andrew bourne" <andrewbourne@.vardells.com> wrote in message
news:OoLVGQN9FHA.4076@.tk2msftngp13.phx.gbl...
> Hi all, got this error and well want some advice really. Cheers
> The process could not enumerate changes at the 'Subscriber'.
> (Source: Merge Replication Provider (Agent); Error number: -2147200999)
> ------
> Could not find the index entry for RID
> '16f8db910fd437730d2cca409ba472f23ca363ef200' in index page (1:353425),
> index ID 0, database 'VMR'.
> (Source: BBSERVER-SAT1 (Data source); Error number: 644)
> ------
> The merge process encountered an unexpected network error. The connection
> to Subscriber 'BBSERVER-SAT1' is no longer available.
> (Source: Merge Process (Agent); Error number: -2147199469)
> ------
>

Could not find file 'C:\WINDOWS\system32\dbo.mdb

Error message "OLE DB error: OLE DB or ODBC error: Could not find file 'C:\WINDOWS\system32\dbo.mdb'.; 3024. is reported when I try to process a cube in the SQL Server Management Studio and in the SQL Server Business Intelligence Development Studio.

What is the problem, I didn't install new software!!

Check the data source connection string. It looks like a Jet data source pointing to C:\WINDOWS\system32\dbo.mdb. Edit the data source in SQL Mgmt Studio and fix it.

|||This is not a data source connection problem, all the cubes are reporting the same error message!!|||If Analysis Services is raising this error, then this Jet connection string must be specified *somewhere*. Check the config file msmdsrv.ini (or server properties in mgmt studio). Maybe the query log or something else points to this mdb.|||

This error message do not appear when I'm connected to the network, only when I work stand alone!!!

My connection string in the data source property window:

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Data\SQL2005Cubes\TBC_Europe.mdb

could not execute sp_replcmds

I am working on SQL Server 2000 SP4
I am getting a very strange error on one of my instances;

The process could not execute 'sp_replcmds' on #SERVERNAME#

Replication failure. File 'T:\shiloh\sql\ntdbms\srvrepl\src\replicat.cpp',
line 1677.
(Source: #SERVERNAME# (Data source); Error number: 18759)

In microsoft http://support.microsoft.com/kb/308865 it tells you to apply
SQL SP4, which i have done and re did just incase it wasn't applied properly,
it made no difference to the situations. Ive restarted the intance and the
server and still to no avail.

Last month we did have a situation where our master database went suspect
and this is the first time anything has had to be replicated on that server
since then. The publisher and subscriber database are on the same server.

Thanks.

Was this post helpful to you?

Why should I rate a post?I have a recollection with replication that you have to enable replication before applying SP4 (or re-apply SP4 after enabling replication). You must do this on both the publisher and the distributor. If I recall the sequence correctly, you must do the distributor first and then then publisher.

I know that you said you re-applied SP4, did you do it in accordance with the steps outlined above?

Regards,

hmscott

CORRUPTED BAK files

Please could you help me to determine why a BAK file can be refused by
sql server when calling a recovery process
I Allready have these ideas
- Other version of sql server making BAK file than the recovery one
(or... not a real BAK file !)
- BAK file altered on file system (disk problem...)
- Backup launched without integrity check before / altered database
has been baked up
Is there other possibilities ?
ThksHaving different builds of SQL Server is another reason. Let's say you
backup was created with a SP3 build and is being restored on an instance
with a SP4 build...
http://bassplayerdoc.blogspot.com/2007/10/when-sql-server-backups-cant-be.html
"mrique" <mriquelyon@.gmail.com> wrote in message
news:1193999517.156781.102050@.y42g2000hsy.googlegroups.com...
> Please could you help me to determine why a BAK file can be refused by
> sql server when calling a recovery process
> I Allready have these ideas
> - Other version of sql server making BAK file than the recovery one
> (or... not a real BAK file !)
> - BAK file altered on file system (disk problem...)
> - Backup launched without integrity check before / altered database
> has been baked up
> Is there other possibilities ?
> Thks
>|||> - Backup launched without integrity check before / altered database
> has been baked up
I don't understand this one.
Linchi
"mrique" wrote:
> Please could you help me to determine why a BAK file can be refused by
> sql server when calling a recovery process
> I Allready have these ideas
> - Other version of sql server making BAK file than the recovery one
> (or... not a real BAK file !)
> - BAK file altered on file system (disk problem...)
> - Backup launched without integrity check before / altered database
> has been baked up
> Is there other possibilities ?
> Thks
>