Create an index in a PDF file

Hi!!

I have a report that is exported to a PDF file... Is possible to get an index in the beginning of the file? I have the page number at the bottom of each page but i would need an index...

Thx in advance!!

Any idea? Isn′t possible to do it? At the moment, I havent found anything linked with this...|||

Hi Sergio,

Do you mean a document map? Check out this link: http://msdn2.microsoft.com/en-us/library/ms156383.aspx

|||

Hi Brad!!

not exactly... i would like to appear something similar but in a page in the beginning of the PDF document including the page number where each element is, i.e. the tables of the document... is there any option to do this? it′s very similar to the document map but with the page number...

I see two problems:

- I was thinking of using "=Globals.PageNumber" but i can′t, it says me that only can be placed in the header or the footer...

- and the other problem is setting the content of the document map in a page of the PDF... could I do this?

Perhaps, this is a bit difficult but I am always looking for challenges ... any suggestions? thx in advance!!

Sergio

|||I know of no way to accomplish exactly what you are looking for sorry to say. The document map links to items as repeated on pages. But I am not sure what you mean by "setting the content of the document map in a page of the PDF". If you mean as a page, there is no way to control this.|||

Hi Brad,

that was exactly i ment: i want to show it as a page, but if there is no way to do it... anyway, thank you for your help...

Sergio

create an incremental counter in the stored procedure

Hello, I have a following SP
I want to add an extra field "ranking" that just increments the row number.
Another feature would be: if several users have an equal totalvalue, they
should have an equal ranking number. the rankings following users would have
to be adjusted as well. thanks

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

CREATE PROCEDURE [dbo].[Rankings]
@.iErrorCode int OUTPUT
AS

SELECT top 30
###COUNTER##,
[user],
[totalvalue], [cash], [stocksvalue]

FROM [dbo].[users]
ORDER BY totalvalue DESC

SELECT @.iErrorCode=@.@.ERROR

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GOThere is more than one way to rank a set with tied values.

CREATE TABLE Users (userid INTEGER PRIMARY KEY, totalvalue NUMERIC(10,2) NOT
NULL, cash NUMERIC(10,2) NOT NULL, stocksvalue NUMERIC(10,2) NOT NULL)

INSERT INTO Users VALUES (101,1010,100,99)
INSERT INTO Users VALUES (102,2020,100,99)
INSERT INTO Users VALUES (103,3030,100,99)
INSERT INTO Users VALUES (104,3030,100,99)
INSERT INTO Users VALUES (105,1002,100,99)
INSERT INTO Users VALUES (106,1002,100,99)
INSERT INTO Users VALUES (107,1002,100,99)
INSERT INTO Users VALUES (108,1002,100,99)
INSERT INTO Users VALUES (109,1000,100,99)

See if this gives the result you expect:

SELECT COUNT(U2.totalvalue)+1 AS ranking,
U1.userid, U1.totalvalue, U1.cash, U1.stocksvalue
FROM Users AS U1
LEFT JOIN Users AS U2
ON U1.totalvalue < U2.totalvalue
GROUP BY U1.userid, U1.totalvalue, U1.cash, U1.stocksvalue
ORDER BY ranking, U1.userid

Result:

ranking userid totalvalue cash stocksvalue
---- ---- ---- ---- ----
1 103 3030.00 100.00 99.00
1 104 3030.00 100.00 99.00
3 102 2020.00 100.00 99.00
4 101 1010.00 100.00 99.00
5 105 1002.00 100.00 99.00
5 106 1002.00 100.00 99.00
5 107 1002.00 100.00 99.00
5 108 1002.00 100.00 99.00
9 109 1000.00 100.00 99.00

(9 row(s) affected)

Or maybe this:

SELECT COUNT(DISTINCT U2.totalvalue) AS ranking,
U1.userid, U1.totalvalue, U1.cash, U1.stocksvalue
FROM Users AS U1
LEFT JOIN Users AS U2
ON U1.totalvalue <= U2.totalvalue
GROUP BY U1.userid, U1.totalvalue, U1.cash, U1.stocksvalue
ORDER BY ranking, U1.userid

Result:

ranking userid totalvalue cash stocksvalue
---- ---- ---- ---- ----
1 103 3030.00 100.00 99.00
1 104 3030.00 100.00 99.00
2 102 2020.00 100.00 99.00
3 101 1010.00 100.00 99.00
4 105 1002.00 100.00 99.00
4 106 1002.00 100.00 99.00
4 107 1002.00 100.00 99.00
4 108 1002.00 100.00 99.00
5 109 1000.00 100.00 99.00

(9 row(s) affected)

--
David Portas
SQL Server MVP
--|||thank you! it works fine.

"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> schrieb im
Newsbeitrag news:1pmdnW4hl-GFFt3dRVn-uA@.giganews.com...
> There is more than one way to rank a set with tied values.
> CREATE TABLE Users (userid INTEGER PRIMARY KEY, totalvalue NUMERIC(10,2)
NOT
> NULL, cash NUMERIC(10,2) NOT NULL, stocksvalue NUMERIC(10,2) NOT NULL)
> INSERT INTO Users VALUES (101,1010,100,99)
> INSERT INTO Users VALUES (102,2020,100,99)
> INSERT INTO Users VALUES (103,3030,100,99)
> INSERT INTO Users VALUES (104,3030,100,99)
> INSERT INTO Users VALUES (105,1002,100,99)
> INSERT INTO Users VALUES (106,1002,100,99)
> INSERT INTO Users VALUES (107,1002,100,99)
> INSERT INTO Users VALUES (108,1002,100,99)
> INSERT INTO Users VALUES (109,1000,100,99)
> See if this gives the result you expect:
> SELECT COUNT(U2.totalvalue)+1 AS ranking,
> U1.userid, U1.totalvalue, U1.cash, U1.stocksvalue
> FROM Users AS U1
> LEFT JOIN Users AS U2
> ON U1.totalvalue < U2.totalvalue
> GROUP BY U1.userid, U1.totalvalue, U1.cash, U1.stocksvalue
> ORDER BY ranking, U1.userid
> Result:
> ranking userid totalvalue cash stocksvalue
> ---- ---- ---- ---- ----
> 1 103 3030.00 100.00 99.00
> 1 104 3030.00 100.00 99.00
> 3 102 2020.00 100.00 99.00
> 4 101 1010.00 100.00 99.00
> 5 105 1002.00 100.00 99.00
> 5 106 1002.00 100.00 99.00
> 5 107 1002.00 100.00 99.00
> 5 108 1002.00 100.00 99.00
> 9 109 1000.00 100.00 99.00
> (9 row(s) affected)
> Or maybe this:
> SELECT COUNT(DISTINCT U2.totalvalue) AS ranking,
> U1.userid, U1.totalvalue, U1.cash, U1.stocksvalue
> FROM Users AS U1
> LEFT JOIN Users AS U2
> ON U1.totalvalue <= U2.totalvalue
> GROUP BY U1.userid, U1.totalvalue, U1.cash, U1.stocksvalue
> ORDER BY ranking, U1.userid
> Result:
> ranking userid totalvalue cash stocksvalue
> ---- ---- ---- ---- ----
> 1 103 3030.00 100.00 99.00
> 1 104 3030.00 100.00 99.00
> 2 102 2020.00 100.00 99.00
> 3 101 1010.00 100.00 99.00
> 4 105 1002.00 100.00 99.00
> 4 106 1002.00 100.00 99.00
> 4 107 1002.00 100.00 99.00
> 4 108 1002.00 100.00 99.00
> 5 109 1000.00 100.00 99.00
> (9 row(s) affected)
> --
> David Portas
> SQL Server MVP
> --

Create a Sequential Line Count for Group...

I have a report that groups by item number but then is filtered by a
parameter to only show the top # the user has entered. Does anyone
know if it is possible to number the results I return so that the user
can see how that particular record ranks with other records? Basically
I want to be able to have the user see 1 for the top item sold, 2 for
the second most items sold, etc...Try using
=Rownumber(Groupname)
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Brent" <Brent.Raymond@.gmail.com> wrote in message
news:1126532242.793502.287600@.o13g2000cwo.googlegroups.com...
>I have a report that groups by item number but then is filtered by a
> parameter to only show the top # the user has entered. Does anyone
> know if it is possible to number the results I return so that the user
> can see how that particular record ranks with other records? Basically
> I want to be able to have the user see 1 for the top item sold, 2 for
> the second most items sold, etc...
>

create a global data source

Hi guys,

I have a number of reports deployed on the report server. I want to point all these reports to another data source. Currently, what I am doing is go to the individual report on the report server and change the data source property to point to the new data source. It is very tedious to do the same thing for all of the reports.

So, Is there any way to point all the reports under a project at one time.

Any idea is appreciated.

Thx!

I would use global replace. That's the only way that I know of.

|||

would u explain that a lil bit

thx

|||

At the top menu, click Edit -> quick replace.

In the find box, enter the old dataset name.

In the replace box, enter the new dataset name.

You may want to do a find next and replace on a case by case basis instead of replace all, because I'm not exactly sure how your project is set up.

|||This is exactly how we have our reports configured.

1. In your business intelligence projects, create a new "shared" data source that points to the database your are pulling your data from. If you are using more than once database, create a single shared data source for each database. Each data source will have a name. For this example, lets assume you created a datasource called "DS_TEST" that points to a database called "MY_DATA"

2. When you build your reports, always use the shared datasources that you created from step 1.

3. When you deploy the reports, they will still be looking for the datasources by name. From step 1, the reports are all looking for a datasource called "DS_TEST". If you developed the reports on the same machine that hosts reporting services, everything should work (although my environment is not set up this way so I have not tested this). If you developed the reports on one machine and deployed to another, you must create the datasource on the deployment machine. To do this, navigate to the reporting services home page where the reports are deployed and click the "Add New Datasource" link (if you do not see this link, then you do not have the correct permissions). Point the datasource to the database you want the reports to pull from, but make sure and name it "DS_TEST" or whatever name you choose in step 1. The reports will now use the datasource called DS_TEST on the server the report is deployed on..

By having the global data source, it makes it easy during development to point the reports to test data so we do not effect the performance of the live system, and later when the reports are deployed to the live system they naturally point to the correct data.

Create "Object Type" in sql server

I'm facing problem while creating object type in sql server.
ex:sql query
"create type student as object ( name varchar2 ( 12) , no number ( 5))" is working fine in Oracle where 'student' can be used in any table as datatype, but its not working in sqlserver. Please can you help me how to create object type in sql server.

Thanks and Regrads,

Suzan:

To create a user defined datatype you need to use the sp_addtype procedure. You might want to look this up in books online. There is an example of use on this page:

http://msdn2.microsoft.com/en-us/library/aa259606(SQL.80).aspx

|||

Thanks , but what i am looking for is different.

i am looking for somting in sql server similar to Oracle OBJECT type

CREATE TYPE Pet_t AS OBJECT (
tag_no INTEGER,
name VARCHAR2(60),
MEMBER FUNCTION set_tag_no (new_tag_no IN INTEGER)
RETURN Pet_t
);

is there a way in sql server to do so....

Thanks

|||

If you're using SQL Server 2005 then you can create CLR user-defined types.

Check out this BOL link for more info:

http://msdn2.microsoft.com/en-us/library/ms131120.aspx

Chris

|||

i have found that this is possible using SQL server 2005, but i need a way to do it in sql server 200.

is it possible to do so?

suzan

|||

Nope.. It is not posible in SQL Server 2000.

SQL SERVER 2000 uses UDT as synonyms for pre-defined types (example - PhoneNumber := Varchar(24) ,etc).

The possible alternate solution is storing your data as BINARY. The issue here is you wont retrive your data on SQL statements (not visible on your QA), you have to depend on your UI/BL to retrive and view the data.

Other simple solution may be storing the data as XML.

|||Thanks ...|||Thanks , but do u have a link to where i can find how to store the data as xml

CR9 - Link String field to Number Field

Hey,

I have two tables used within a report one therough btrieve and the other odbc.

Within each table there is a field called {emp.no}. However, one field is located within a Jobshop database and is a string field and the other is located within a TimeLOG database and is a number field.

Is there anyway of linking these fieds within crystal? I dont belive its possible with the Database Expert. But maybe elsewhere?

The report feeds through from the Jobshop (string) field and I need to match records within the TimeLOG (number) database to those within Jobshop based on emp.no and a date field.

Any help much appreciated.

Regards

RobSee the help on the ToText and ToNumber functions.
You should be able to use these in the Record Selection formula.

CR Format

Hello,

Is there a way to format the data when it is passed into the Crystal Report(CR).. for example.. In the original table, a number may be left aligned, how to change it to right alignment?.. in other words I want to change it's

1. Format: Horizontal Alignment
2. from Left To Right
3. if the data passed in is an Integer, Currency, or DateTime.

4. There is a Format Formula Editor beside it. Does anyone know what's the syntax to do the alignment either in crystal syntax or in basic syntax.

thanx1. You can add a Parameter field in Crystal Report.

2. Pass the value to this Parameter field.
// Solution

Report.ParameterFields(1).AddCurrentValue (strkey1)

Remark : (a) ParameterFields(1) is the FIRST PARAMETER ADDED IN CR.
(b) strkey1 is the value or variable that contains the string or
any value that is received to be passed on to CR.
(c) Place this parameter field wherever you require in CR you
created.

3. You can format this Parameter field in Crystal Report as you WISH.

CR 8.5, VB6, Crviewer how to preset Copies

Ok, I know how to use printout feature and can set number of copies this is fine when going directly to printer, But I have endusers that want to preview the report befre printing.

They however make an easy mistake, the document they are previewing has a requirement of 3 copies when printed. The preview has no obvious way to set that and of course startup with 1 copy. I know I could add a line to run a printout of 2 copies but the previewer form is used by more than one report.

I just want to know is there an easy way to set the copies in the previewer print.

Thanks in advance.This code goes into the VB form that your CRViewer is located. I named mine CRViewer. From the Left drop down list in your code window, choose CRViewer (or whatever you named it), in the right Dropdown list, choose PrintButtonClicked.

Private Sub CRViewer_PrintButtonClicked(useDefault As Boolean)

Dim intCopies As Integer

useDefault = False

intCopies = 3

Report.PrintOut False, intCopies

End Sub

CPU Utilization

Hi All,

I want to keep track of the CPU utilization & number of users connected for each database on our production box. I chose to get the data from sysprocesses table from master database.

But I realised that for some reason the master..sysprocesses.CPU column stays static or just keeps on adding to existing values.

Is there any ways thru which I can clear this data ( cpu column in sysprocesses table) after I have captured it in a table ?

Any help is appreciated.

Thanks.You may be better off using perfmon with those two counters. yOu can put the output to a .csv, for later import into databases. This will not give you CPU usage per connection, however, so it is somewhat useless if you want to do chargebacks of some sort.

CPU usage

I want to know what is the %CPU used by a process in SQL Server. In other
words, I got the CPUTime from the sysprocesses table and the number is say
10,000.
How can I relate this CPU time with NT processor percentage? A user called
me before running some stored procedure and wanted to know what is the
percentage of Total NT CPU this stored procedure is using?
Any idea?
Thanks in advanceHi David
Sysprocesses/CPU is a cumulative CPU usage and therefore can not be taken
against the current perfmon value. The amount of CPU any given stored
procedure takes will probably be slightly different each time it runs,
depending on what else is running and other factors such as how up-to-date
are the statistics or if it encountered blocking etc...
John
"David" wrote:

> I want to know what is the %CPU used by a process in SQL Server. In other
> words, I got the CPUTime from the sysprocesses table and the number is say
> 10,000.
> How can I relate this CPU time with NT processor percentage? A user called
> me before running some stored procedure and wanted to know what is the
> percentage of Total NT CPU this stored procedure is using?
> Any idea?
> Thanks in advance

CPU usage

I want to know what is the %CPU used by a process in SQL Server. In other
words, I got the CPUTime from the sysprocesses table and the number is say
10,000.
How can I relate this CPU time with NT processor percentage? A user called
me before running some stored procedure and wanted to know what is the
percentage of Total NT CPU this stored procedure is using?
Any idea?
Thanks in advanceHi David
Sysprocesses/CPU is a cumulative CPU usage and therefore can not be taken
against the current perfmon value. The amount of CPU any given stored
procedure takes will probably be slightly different each time it runs,
depending on what else is running and other factors such as how up-to-date
are the statistics or if it encountered blocking etc...
John
"David" wrote:
> I want to know what is the %CPU used by a process in SQL Server. In other
> words, I got the CPUTime from the sysprocesses table and the number is say
> 10,000.
> How can I relate this CPU time with NT processor percentage? A user called
> me before running some stored procedure and wanted to know what is the
> percentage of Total NT CPU this stored procedure is using?
> Any idea?
> Thanks in advance

covert from string to integer

Are there any function to conver a string to integer?
like it can convert a string "16" to a number 16=CInt("16") should work
or
=Int32.Parse("16")
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"ad" <ad@.wfes.tcc.edu.tw> wrote in message
news:eE3Z0DQnEHA.3324@.TK2MSFTNGP15.phx.gbl...
> Are there any function to conver a string to integer?
> like it can convert a string "16" to a number 16
>

Couple of questions = )

1.
Does anyone know the max number of Databases a single MSSQL 2000 enterprise machine can handle ??
2.
Does anyone have an idea on how you could use the DTS in MSSQL server to kick a script off on a UNIX machine ??
Chris1. 32,767
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_ar_ts_8dbn.asp

Counts in Date Ranges

Hi,
I have table with the following structure
User Name, Name of Product, Date of Purchase
I want to find out the number of times a particular product was purchased by
anybody in the last 30, 60 and 90 days.
Is there any function in SQL that will give this type of distribution? What
will be the best way of writing the query?
Thanks in anticipation,
Nitin MNitim
SELECT COUNT(ProdunctName) FROM
TableName WHERE [Date of Purchase] >=DATEADD(DAY,-30,GETDATE()) AND
<GETDATE()
AND ProductName='Something'
"Nitin M" <nitin@.nowhere.com> wrote in message
news:%23Zbsk3yPFHA.2932@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I have table with the following structure
> User Name, Name of Product, Date of Purchase
> I want to find out the number of times a particular product was purchased
by
> anybody in the last 30, 60 and 90 days.
> Is there any function in SQL that will give this type of distribution?
What
> will be the best way of writing the query?
> Thanks in anticipation,
> Nitin M
>|||Thanks Uri,
This is the solution to get the purchase count for a given time period.
However I was looking for a solution that gives me the purchase counts for
three time periods in one query. I am looking for a result set that looks
like
Product PurchaseInLast30Days PurchaseInLast60Days
PurchaseInLast90Days
----
--
A 10 13
31
B 0 5
5
and so on ...
I have a query that looks like... but it amounts to scanning the table 4
times. Is there a better way out?
select
P.ProductName,
Last30Days = (select count(P1.ProductName) from Products P1 where
P1.ProductName = P.ProductName and
datediff(day,P1.PurchaseDate,getutcdate()) <= 30 group by P1.ProductName),
Last60Days = (select count(P1.ProductName) from Products P1 where
P1.ProductName = P.ProductName and
datediff(day,P1.PurchaseDate,getutcdate()) <= 60 group by P1.ProductName),
Last90Days = (select count(P1.ProductName) from Products P1 where
P1.ProductName = P.ProductName and
datediff(day,P1.PurchaseDate,getutcdate()) <= 90 group by P1.ProductName),
from
Products P
group by
P.ProductName
Thanks,
Nitin M
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:eJjWlBzPFHA.2876@.TK2MSFTNGP10.phx.gbl...
> Nitim
> SELECT COUNT(ProdunctName) FROM
> TableName WHERE [Date of Purchase] >=DATEADD(DAY,-30,GETDATE()) AND
> <GETDATE()
> AND ProductName='Something'
>
> "Nitin M" <nitin@.nowhere.com> wrote in message
> news:%23Zbsk3yPFHA.2932@.TK2MSFTNGP09.phx.gbl...
> by
> What
>|||I see what you mean
select
ProductName,
COUNT(CASE WHEN datediff(day,PurchaseDate,getutcdate()) <= 30 THEN 1 END)
Last30Days ,
COUNT(CASE WHEN datediff(day,PurchaseDate,getutcdate()) <=60 THEN 1 END)
Last60Days ,
COUNT(CASE WHEN datediff(day,PurchaseDate,getutcdate()) <= 90 THEN 1 END)
Last90Days
from
Products
group by
ProductName
"Nitin M" <nitin@.nowhere.com> wrote in message
news:OVtGoHzPFHA.532@.TK2MSFTNGP09.phx.gbl...
> Thanks Uri,
> This is the solution to get the purchase count for a given time period.
> However I was looking for a solution that gives me the purchase counts for
> three time periods in one query. I am looking for a result set that looks
> like
> Product PurchaseInLast30Days PurchaseInLast60Days
> PurchaseInLast90Days
> ----
--
> A 10 13
> 31
> B 0 5
> 5
> and so on ...
> I have a query that looks like... but it amounts to scanning the table 4
> times. Is there a better way out?
> select
> P.ProductName,
> Last30Days = (select count(P1.ProductName) from Products P1 where
> P1.ProductName = P.ProductName and
> datediff(day,P1.PurchaseDate,getutcdate()) <= 30 group by P1.ProductName),
> Last60Days = (select count(P1.ProductName) from Products P1 where
> P1.ProductName = P.ProductName and
> datediff(day,P1.PurchaseDate,getutcdate()) <= 60 group by P1.ProductName),
> Last90Days = (select count(P1.ProductName) from Products P1 where
> P1.ProductName = P.ProductName and
> datediff(day,P1.PurchaseDate,getutcdate()) <= 90 group by P1.ProductName),
> from
> Products P
> group by
> P.ProductName
> Thanks,
> Nitin M
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:eJjWlBzPFHA.2876@.TK2MSFTNGP10.phx.gbl...
purchased
>|||Thanks a lot Uri ... I got it ... It helped me.
Thank You
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23dOdsRzPFHA.1096@.TK2MSFTNGP12.phx.gbl...
>I see what you mean
> select
> ProductName,
> COUNT(CASE WHEN datediff(day,PurchaseDate,getutcdate()) <= 30 THEN 1 END)
> Last30Days ,
> COUNT(CASE WHEN datediff(day,PurchaseDate,getutcdate()) <=60 THEN 1 END)
> Last60Days ,
> COUNT(CASE WHEN datediff(day,PurchaseDate,getutcdate()) <= 90 THEN 1 END)
> Last90Days
> from
> Products
> group by
> ProductName
> "Nitin M" <nitin@.nowhere.com> wrote in message
> news:OVtGoHzPFHA.532@.TK2MSFTNGP09.phx.gbl...
> --
> purchased
>

Counting with a filter

Using SS2000, VS2003, RS2000.
I want to list all of the individual orders and how they were shipped. Then
I want to count the total number of orders and get a count for each method of
shipment. So, the data might look like this:
Order # Shipped
1 UPS
2 FedEx
3 USPS
4 FedEx
5 UPS
6 FedEx
Total Orders 6
FedEx 3
UPS 2
USPS 1
I tried adding a group and putting a filter in the group (FedEx) but it only
limited the detail part of the report to the rows that were shipped by FedEx.
Now I can't get rid of that filter. I even deleted all of the headers,
footers, groups and detail section and it still only gives me the rows with
FedEx.
Any ideas,
Thanks,
--
Dan D.I'm closer but not there yet. What I have now is this:
Order # Shipped
2 FedEx
4 FedEx
6 FedEx
Total Orders 3
FedEx 3
1 UPS
5 UPS
Total Orders 2
UPS 2
3 USPS
Total Orders 1
USPS 1
But what I need is what was in my original post
--
Dan D.
"Dan D." wrote:
> Using SS2000, VS2003, RS2000.
> I want to list all of the individual orders and how they were shipped. Then
> I want to count the total number of orders and get a count for each method of
> shipment. So, the data might look like this:
> Order # Shipped
> 1 UPS
> 2 FedEx
> 3 USPS
> 4 FedEx
> 5 UPS
> 6 FedEx
> Total Orders 6
> FedEx 3
> UPS 2
> USPS 1
> I tried adding a group and putting a filter in the group (FedEx) but it only
> limited the detail part of the report to the rows that were shipped by FedEx.
> Now I can't get rid of that filter. I even deleted all of the headers,
> footers, groups and detail section and it still only gives me the rows with
> FedEx.
> Any ideas,
> Thanks,
> --
> Dan D.|||Dan,
Can you do this with 2 datasets and 2 tables?
Dataset1:
SELECT orderID, carrier FROM orders ORDER BY orderID
Dataset2:
SELECT carrier, COUNT(orderID) AS carrierCount FROM orders GROUP BY
carrier ORDER BY COUNT(orderID) DESC
You can use a header/footer row for the grand total.
-Josh
Dan D. wrote:
> I'm closer but not there yet. What I have now is this:
> Order # Shipped
> 2 FedEx
> 4 FedEx
> 6 FedEx
> Total Orders 3
> FedEx 3
> 1 UPS
> 5 UPS
> Total Orders 2
> UPS 2
> 3 USPS
> Total Orders 1
> USPS 1
> But what I need is what was in my original post
> --
> Dan D.
>
> "Dan D." wrote:
> > Using SS2000, VS2003, RS2000.
> > I want to list all of the individual orders and how they were shipped. Then
> > I want to count the total number of orders and get a count for each method of
> > shipment. So, the data might look like this:
> >
> > Order # Shipped
> > 1 UPS
> > 2 FedEx
> > 3 USPS
> > 4 FedEx
> > 5 UPS
> > 6 FedEx
> >
> > Total Orders 6
> > FedEx 3
> > UPS 2
> > USPS 1
> >
> > I tried adding a group and putting a filter in the group (FedEx) but it only
> > limited the detail part of the report to the rows that were shipped by FedEx.
> > Now I can't get rid of that filter. I even deleted all of the headers,
> > footers, groups and detail section and it still only gives me the rows with
> > FedEx.
> >
> > Any ideas,
> >
> > Thanks,
> > --
> > Dan D.|||It's worth a try. I'm wondering RS will keep the two datasets in sync. BTW, I
also posted a different example this morning under the subject "is this
possible in RS".
For the time being, I've created a another group on the carrier. Even though
it's not in the format the client wanted, it will give the counts they want.
I'll keep experimenting, though and try your idea.
Thanks,
--
Dan D.
"Josh" wrote:
> Dan,
> Can you do this with 2 datasets and 2 tables?
> Dataset1:
> SELECT orderID, carrier FROM orders ORDER BY orderID
> Dataset2:
> SELECT carrier, COUNT(orderID) AS carrierCount FROM orders GROUP BY
> carrier ORDER BY COUNT(orderID) DESC
> You can use a header/footer row for the grand total.
> -Josh
>
> Dan D. wrote:
> > I'm closer but not there yet. What I have now is this:
> > Order # Shipped
> >
> > 2 FedEx
> > 4 FedEx
> > 6 FedEx
> > Total Orders 3
> > FedEx 3
> >
> > 1 UPS
> > 5 UPS
> > Total Orders 2
> > UPS 2
> >
> > 3 USPS
> > Total Orders 1
> > USPS 1
> >
> > But what I need is what was in my original post
> > --
> > Dan D.
> >
> >
> > "Dan D." wrote:
> >
> > > Using SS2000, VS2003, RS2000.
> > > I want to list all of the individual orders and how they were shipped. Then
> > > I want to count the total number of orders and get a count for each method of
> > > shipment. So, the data might look like this:
> > >
> > > Order # Shipped
> > > 1 UPS
> > > 2 FedEx
> > > 3 USPS
> > > 4 FedEx
> > > 5 UPS
> > > 6 FedEx
> > >
> > > Total Orders 6
> > > FedEx 3
> > > UPS 2
> > > USPS 1
> > >
> > > I tried adding a group and putting a filter in the group (FedEx) but it only
> > > limited the detail part of the report to the rows that were shipped by FedEx.
> > > Now I can't get rid of that filter. I even deleted all of the headers,
> > > footers, groups and detail section and it still only gives me the rows with
> > > FedEx.
> > >
> > > Any ideas,
> > >
> > > Thanks,
> > > --
> > > Dan D.
>

Counting total number of queries executed within the page

Hi everyone,

Does exist an easy way to count the actually number of queries executed within a page?

I've searched here and in google but found anything...

Thanks in advance!

If SQL Server is your backend database, you can use SQL Profiler to run a trace on the statements executed.

|||

The db is sql server 2000, but it's on a shared server.. can I use the profiler in a shared environment too?

In case I can use it, does it aggregate someqhat the queries per ASP.NET page executed?

Thanks!

|||

>can I use the profiler in a shared environment too?
You can limit it by database.

>In case I can use it, does it aggregate someqhat the queries per ASP.NET page executed?
Given the right option it will report each query run.

Counting total number of items in each category

Hello everyone,

I have 2 tables. One with a list of countries and another with a list of users. The users table stores the Country they are from as well as other info.

Eg the structure is a little bit like this...

Countries:

--

CountryId

CountryName

Users:

UserId

UserName

CountryId

So, the question is how to a list all my countries with total users in each. Eg, so the results are something like this......

CountryName TotalUsers

United Kingdom 334

United States 1212

France 433

Spain 0

Any help woulld be great as I have been fumbling with this all morning. Im not 100% with SQL yet!!

Cheer

Stephen

here You go...

Code Snippet

Create Table #countries (

[CountryId] int ,

[CountryName] Varchar(100)

);

Insert Into #countries Values('1','USA');

Insert Into #countries Values('2','UK');

Insert Into #countries Values('3','IN');

Create Table #users (

[UserId] int ,

[UserName] Varchar(100) ,

[CountryId] int

);

Insert Into #users Values('1','John','1');

Insert Into #users Values('2','Dale','1');

Insert Into #users Values('3','Thome','2');

--With ZERO COUNT

Select

[CountryName],

Count([UserId])

from

#countries C

left Outer Join #users U on C.[CountryId] = U.[CountryId]

Group By

[CountryName]

--Without ZERO COUNT

Select

[CountryName],

Count([UserId])

from

#countries C

Inner Join #users U on C.[CountryId] = U.[CountryId]

Group By

[CountryName]

|||

Super! Many thanks and thank you for replying so quickly.

Makes sense now - easier than what I was trying to do!!!

Counting the number of fields that are populated in row

I have 2 large tables that I'm selecting off of. part of my select is
to get the number of stores visited. The table that has this
information has the store names as columns. If a store wasn't visited
there will be a null value. I need to be able to count the number of
fields that don't have the null value in order to get the desired
result. I can't use the Count() function as the select statement is
very large. and I don't want to group by all of the fields that I'm
selecting.
Does anyone know of another way to get this?what data type are the store name columns?
numeric (of some sort) indicating number of visits?
bit indicating visited?
are you returning the store columns as well?
dmagoo22 wrote:
> I have 2 large tables that I'm selecting off of. part of my select is
> to get the number of stores visited. The table that has this
> information has the store names as columns. If a store wasn't visited
> there will be a null value. I need to be able to count the number of
> fields that don't have the null value in order to get the desired
> result. I can't use the Count() function as the select statement is
> very large. and I don't want to group by all of the fields that I'm
> selecting.
>
> Does anyone know of another way to get this?
>|||> to get the number of stores visited. The table that has this
> information has the store names as columns.
Egads. So if you add a store, you change your schema? That's really not
how it should work. The store names are *data* not *metadata*...

> If a store wasn't visited
> there will be a null value. I need to be able to count the number of
> fields that don't have the null value in order to get the desired
> result.
If this returns exactly one row, you can try this:
SELECT
col1,
col2,
NumberOfStoresVisited =
CASE WHEN Store1 = NULL THEN 0 ELSE 1 END
+ CASE WHEN Store2 = NULL THEN 0 ELSE 1 END
+ ...
+ CASE WHEN StoreN = NULL THEN 0 ELSE 1 END
FROM
table
...
Without better specs, that's about as good as I can do. Please see
http://www.aspfaq.com/5006|||> CASE WHEN Store1 = NULL THEN 0 ELSE 1 END
WHOA! I meant WHEN Store1 IS NULL
*smack*|||try the @.@.rowcount Function
run the select statement to select where your column is not null and then
get the @.@.rowcount
"Aaron Bertrand [SQL Server MVP]" wrote:

> WHOA! I meant WHEN Store1 IS NULL
> *smack*
>
>|||> try the @.@.rowcount Function
> run the select statement to select where your column is not null and then
> get the @.@.rowcount
Unfortunately, I think the OP's table looks like this:
Store1 Store2 Store3 Store4 Store5
5 12 NULL 3 1
So @.@.ROWCOUNT will always be 1...|||Oops!!!! Sorry thot the data was in multiple rows
"Aaron Bertrand [SQL Server MVP]" wrote:

> Unfortunately, I think the OP's table looks like this:
> Store1 Store2 Store3 Store4 Store5
> 5 12 NULL 3 1
> So @.@.ROWCOUNT will always be 1...
>
>

counting the inserts and updates on a table in a sql server database

Hello,

Can someone point me to getting the total number of inserts and updates on a table
over a period of time?

I just want to measure the insert and update activity on the tables.

Thanks.

- VishOn a single statement you can capture the @.@.rowcount into a variable and
write it to a log table. But if I'm reading this correctly, you don't want
to do this through the existing code base. Inserts are usually easy if
there is a primary or unique key. Assuming no deletes, simply how many new
keys are there since the last count. Or if the key is incrementing by one
what's the max value - the previous max value. Updates are more vague. How
many rows were updated or how many updates occurred These activities are
usually accommodated for in the initial table design with flag and
last_mod_date columns. Without auditing written into every piece of code or
proper schema design it's an ugly intensive task to rub to data sets
together (using checksums or straight comparisons) to find differences.

Danny

"Viswanatha Thalakola" <vthalakola@.yahoo.com> wrote in message
news:d762e418.0411301845.7504b0b4@.posting.google.c om...
> Hello,
> Can someone point me to getting the total number of inserts and updates on
> a table
> over a period of time?
> I just want to measure the insert and update activity on the tables.
> Thanks.
> - Vish|||On 30 Nov 2004 18:45:55 -0800, Viswanatha Thalakola wrote:

>Hello,
>Can someone point me to getting the total number of inserts and updates on a table
>over a period of time?
>I just want to measure the insert and update activity on the tables.
>Thanks.
>- Vish

Hi Vish,

The easiest way to do this is to add some counting logic to the stored
procedures that do the inserting, updating and deleting. But if you can't
or won't change those tables (or if you allow direct data modifications,
without using stored procedures), you have two other options:

1. Set up a profiler trace. Catch the trace results in a table or in a
file, then use either SQL queries (if in a table) or text manipulation
tools (if in a file) to count the number of inserts, updates, etc. I must
add that I don't know the exact format and I'm not sure either if the
number of rows affected is included in the trace data (it it isn't, you
can't use this approach).

2. Create triggers for each table you need to monitor. Have these triggers
copy @.@.rowcount in a local variable as the first statement (that yields
the number of rows affected by the statement that fired the trigger) and
save that value to a table.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hugo Kornelis (hugo@.pe_NO_rFact.in_SPAM_fo) writes:
> 1. Set up a profiler trace. Catch the trace results in a table or in a
> file, then use either SQL queries (if in a table) or text manipulation
> tools (if in a file) to count the number of inserts, updates, etc. I must
> add that I don't know the exact format and I'm not sure either if the
> number of rows affected is included in the trace data (it it isn't, you
> can't use this approach).

It isn't, but you can catch number of page writes. Still, though, not a
wholly reliable number.

Then again, I assume that the aim is not to save exact numbers, but get
some approxamite statistics, so some Profiler method is proably better
than adding triggers to the system.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Counting Stored Procedures

I ran out of fingers...
How can I count the number of stored procedures for which the name starts
with "XYZ_"
Thanks!SELECT COUNT(*)
FROM INFORMATION_SCHEMA.ROUTINES
WHERE routine_name LIKE 'XYZ[_]%'
AND routine_type = 'PROCEDURE'
David Portas
SQL Server MVP
--|||select count(*) from sysobjects where name like 'xyz\_%' escape '' and
objectproperty(id, 'IsProcedure') = 1
Mike
"Guadala Harry" <GMan@.NoSpam.net> wrote in message
news:eWV2yVBTFHA.3144@.TK2MSFTNGP09.phx.gbl...
> I ran out of fingers...
> How can I count the number of stored procedures for which the name starts
> with "XYZ_"
> Thanks!
>|||Use <dbname>
Go
Select Count(*)
From dbo.sysobjects as S
Where ObjectProperty(S.id, 'IsProcedure') = 1
And ObjectProperty(S.id, 'IsMSShipped') = 0
And Name Like 'XYZ%'
Granted, there aren't any Microsoft shipped stored procs that begin with 'XY
Z',
so you could skip that part of the criteria.
Thomas
"Guadala Harry" <GMan@.NoSpam.net> wrote in message
news:eWV2yVBTFHA.3144@.TK2MSFTNGP09.phx.gbl...
>I ran out of fingers...
> How can I count the number of stored procedures for which the name starts
with
> "XYZ_"
> Thanks!
>|||Can my response. David's response is more portable, using the
INFORMATION_SCHEMA views. I need to remember I'm supposed to use those...
"Mike Jansen" <mjansen_nntp@.mail.com> wrote in message
news:O8kkPZBTFHA.544@.TK2MSFTNGP15.phx.gbl...
> select count(*) from sysobjects where name like 'xyz\_%' escape '' and
> objectproperty(id, 'IsProcedure') = 1
> Mike
> "Guadala Harry" <GMan@.NoSpam.net> wrote in message
> news:eWV2yVBTFHA.3144@.TK2MSFTNGP09.phx.gbl...
starts
>|||Select * From sysObjects
Where type = 'P'
And name Line 'XYZ_%'
"Guadala Harry" wrote:

> I ran out of fingers...
> How can I count the number of stored procedures for which the name starts
> with "XYZ_"
> Thanks!
>
>|||ditto Mike's comment
"CBretana" wrote:
> Select * From sysObjects
> Where type = 'P'
> And name Line 'XYZ_%'
>
> "Guadala Harry" wrote:
>|||It's because Microsoft doesn't support queries on system tables. The table
structure may be changed in future releases.
For a one time deal though, no problem, as long as you don't include it in
production code.
Ditto CBretana
Just kidding. I know I don't have to remind you. Let's just chalk it up to a
long, hard day... or w. :-)
"Mike Jansen" <mjansen_nntp@.mail.com> wrote in message
news:ODF4HcBTFHA.548@.tk2msftngp13.phx.gbl...
> Can my response. David's response is more portable, using the
> INFORMATION_SCHEMA views. I need to remember I'm supposed to use those...
> "Mike Jansen" <mjansen_nntp@.mail.com> wrote in message
> news:O8kkPZBTFHA.544@.TK2MSFTNGP15.phx.gbl...
> starts
>