How to Format Text Area in Report

Hi,
I need create report that should look like a letter,
i.e. I have long, formatted text, where I should place few fields from
the record.
For instance:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Dear Sir,
1. Hear goes
2. some formatted text
3. and {Fields!one_field.Value} inserted at line number 3
4. and {Fields!another_field.Value} inserted here
And text continues here
and here, and {Fields!next_filed.Value}
etc.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
I did not found possibility to have one text box with formatted content
and fields/ calculations inserted into it in Report Design.
I tried also divide my text area to few text boxes where static text was
separated from data fields, but I've got into the issues with spacing
between the fields. It looks good in design view, but in preview it adds
extra vertical spaces within the fields. Also it is quite hard to format
a set of text boxes to look like one formatted letter.
Is it possible insert fields into formatted text box? If no, how in
general I should build such report?
Thanks for any help,
Alexander.On Dec 25, 2:38 am, "Alexander N. Treyner" <a...@.treyner.israel.net>
wrote:
> Hi,
> I need create report that should look like a letter,
> i.e. I have long, formatted text, where I should place few fields from
> the record.
> For instance:
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> Dear Sir,
> 1. Hear goes
> 2. some formatted text
> 3. and {Fields!one_field.Value} inserted at line number 3
> 4. and {Fields!another_field.Value} inserted here
> And text continues here
> and here, and {Fields!next_filed.Value}
> etc.
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> I did not found possibility to have one text box with formatted content
> and fields/ calculations inserted into it in Report Design.
> I tried also divide my text area to few text boxes where static text was
> separated from data fields, but I've got into the issues with spacing
> between the fields. It looks good in design view, but in preview it adds
> extra vertical spaces within the fields. Also it is quite hard to format
> a set of text boxes to look like one formatted letter.
> Is it possible insert fields into formatted text box? If no, how in
> general I should build such report?
> Thanks for any help,
> Alexander.
You should consider using a table control in place of the textboxes.
This should allow for a little better layout/design flexibility. Also,
concatenating text and expressions/etc together as an expression is
fairly straight forward, i.e.,:
="and " + Fields!one_field.Value + "inserted at line number 3"
Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant|||EMartinez wrote:
> You should consider using a table control in place of the textboxes.
> This should allow for a little better layout/design flexibility. Also,
> concatenating text and expressions/etc together as an expression is
> fairly straight forward, i.e.,:
> ="and " + Fields!one_field.Value + "inserted at line number 3"
> Hope this helps.
> Regards,
> Enrique Martinez
> Sr. Software Consultant
I tried to use expression, but it does not allow to format text. How can
I insert CRLF in the expression? I tried chr(10)+chr(13). It looks good
in preview, when I run report from my web site, seems it just ignore it
and print report without CRLF.
I will try to use tables.
Thanks,
Alex.|||EMartinez wrote:
> You should consider using a table control in place of the textboxes.
> This should allow for a little better layout/design flexibility. Also,
> concatenating text and expressions/etc together as an expression is
> fairly straight forward, i.e.,:
> ="and " + Fields!one_field.Value + "inserted at line number 3"
> Hope this helps.
> Regards,
> Enrique Martinez
> Sr. Software Consultant
I tried to use expression, but it does not allow to format text. How can
I insert CRLF in the expression? I tried chr(10)+chr(13). It looks good
in preview, when I run report from my web site, seems it just ignore it
and print report without CRLF.
I will try to use tables.
Thanks,
Alex.|||RS is pretty crummy at this time for this. RS 2008 will have much better
support for rich text. I have not experimented with it in pre-release
software so other than knowing the support will be better (i.e. there will
be support for rich text) I can't give you particulars.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Alexander N. Treyner" <alex@.treyner.israel.net> wrote in message
news:ufqbGGtRIHA.536@.TK2MSFTNGP06.phx.gbl...
> Hi,
> I need create report that should look like a letter,
> i.e. I have long, formatted text, where I should place few fields from the
> record.
> For instance:
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> Dear Sir,
> 1. Hear goes
> 2. some formatted text
> 3. and {Fields!one_field.Value} inserted at line number 3
> 4. and {Fields!another_field.Value} inserted here
> And text continues here
> and here, and {Fields!next_filed.Value}
> etc.
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> I did not found possibility to have one text box with formatted content
> and fields/ calculations inserted into it in Report Design.
> I tried also divide my text area to few text boxes where static text was
> separated from data fields, but I've got into the issues with spacing
> between the fields. It looks good in design view, but in preview it adds
> extra vertical spaces within the fields. Also it is quite hard to format a
> set of text boxes to look like one formatted letter.
>
> Is it possible insert fields into formatted text box? If no, how in
> general I should build such report?
> Thanks for any help,
> Alexander.

how to find records in database using select

I need to be able to find certain data as the user has submitted that data twice and delete one record,except that have hundreds of tables and don't know the table where she would have submitted the data, but I have some other key info that I can start with.

My question is,how do i select a record from the database if I don't know the table it comes from?

Could somebody give me an expamle please?How did they submit the data? Do you know the data submitted? You have to have something to go on.|||Can you pose the question exactly as the teacher wrote it in your assignment? Just type it verbatim into a posting, and you'll get a much better quality answer.

-PatP

How to find record with xml data containing value...

Hello all,
SQL2005
I am newbe in XPath and XQuery
I have to find in table with xml field all records where value of attribute
contains string (e.g person name)
drop table dbo.bbb;
create table dbo.bbb(
p1 int not null identity,
p2 xml,
primary key(p1) );
insert into dbo.bbb values
('<root><item werte="d1"/><item werte="a2"/></root>');
insert into dbo.bbb values
('<root><item werte="b1"/><item werte="b2"/></root>');
select *
from dbo.bbb
where p2.value('contains((/root/item/@.werte)[1],"a")','bit') = 1
this query give me records with @.werte containing "a" but only if it is in
first item,
I have to find records with @.werte containing "a" regardless of item
position.
Can anybody help me write this query?
Regards
YaroOK, I know
select *
from dbo.bbb
where
p2.exist('/root/item/@.werte[contains(.,"a")]') = 1
Yaro
Uytkownik "Yaro" <yarok_delthisdes_@.op.pl> napisa w wiadomoci
news:e12vcm$nq4$1@.83.238.170.160...
> Hello all,
> SQL2005
> I am newbe in XPath and XQuery
> I have to find in table with xml field all records where value of
> attribute contains string (e.g person name)
> drop table dbo.bbb;
> create table dbo.bbb(
> p1 int not null identity,
> p2 xml,
> primary key(p1) );
> insert into dbo.bbb values
> ('<root><item werte="d1"/><item werte="a2"/></root>');
> insert into dbo.bbb values
> ('<root><item werte="b1"/><item werte="b2"/></root>');
> select *
> from dbo.bbb
> where p2.value('contains((/root/item/@.werte)[1],"a")','bit') = 1
> this query give me records with @.werte containing "a" but only if it is
> in first item,
> I have to find records with @.werte containing "a" regardless of item
> position.
> Can anybody help me write this query?
> Regards
> Yaro

How to find record with xml data containing value...

Hello all,
SQL2005
I am newbe in XPath and XQuery
I have to find in table with xml field all records where value of attribute
contains string (e.g person name)
drop table dbo.bbb;
create table dbo.bbb(
p1 int not null identity,
p2 xml,
primary key(p1) );
insert into dbo.bbb values
('<root><item werte="d1"/><item werte="a2"/></root>');
insert into dbo.bbb values
('<root><item werte="b1"/><item werte="b2"/></root>');
select *
from dbo.bbb
where p2.value('contains((/root/item/@.werte)[1],"a")','bit') = 1
this query give me records with @.werte containing "a" but only if it is in
first item,
I have to find records with @.werte containing "a" regardless of item
position.
Can anybody help me write this query?
Regards
Yaro
OK, I know
select *
from dbo.bbb
where
p2.exist('/root/item/@.werte[contains(.,"a")]') = 1
Yaro
Uytkownik "Yaro" <yarok_delthisdes_@.op.pl> napisa w wiadomoci
news:e12vcm$nq4$1@.83.238.170.160...
> Hello all,
> SQL2005
> I am newbe in XPath and XQuery
> I have to find in table with xml field all records where value of
> attribute contains string (e.g person name)
> drop table dbo.bbb;
> create table dbo.bbb(
> p1 int not null identity,
> p2 xml,
> primary key(p1) );
> insert into dbo.bbb values
> ('<root><item werte="d1"/><item werte="a2"/></root>');
> insert into dbo.bbb values
> ('<root><item werte="b1"/><item werte="b2"/></root>');
> select *
> from dbo.bbb
> where p2.value('contains((/root/item/@.werte)[1],"a")','bit') = 1
> this query give me records with @.werte containing "a" but only if it is
> in first item,
> I have to find records with @.werte containing "a" regardless of item
> position.
> Can anybody help me write this query?
> Regards
> Yaro

How to find Rank(return Top 3 Country)

Hi All,
These are the records I've in my table. I need to return top 3 countries (rank) by Sales.
Record Date Country Sales
1 01.01.04 AU 80%
2 01.01.04 IN 92%
3 01.01.04 CH 50%
4 01.01.04 USA 75%
5 01.01.04 SG 25%
The result set should be:
Record Date Country Sales
2 01.01.04 IN 92%
1 01.01.04 AU 80%
4 01.01.04 USA 75%
How to write query for that?
Thanx
Rgds
K.Senthil Kumar
SELECT TOP 3 Record, Date, Country, Sales
FROM your_table
ORDER BY Sales DESC
Jacco Schalkwijk
SQL Server MVP
"K.Senthil Kumar" <K.Senthil Kumar@.discussions.microsoft.com> wrote in
message news:6EB6A9BF-7A6D-4B10-8F9D-CC9087946489@.microsoft.com...
> Hi All,
> These are the records I've in my table. I need to return top 3 countries
(rank) by Sales.
> Record Date Country Sales
> 1 01.01.04 AU 80%
> 2 01.01.04 IN 92%
> 3 01.01.04 CH 50%
> 4 01.01.04 USA 75%
> 5 01.01.04 SG 25%
>
> The result set should be:
> Record Date Country Sales
> 2 01.01.04 IN 92%
> 1 01.01.04 AU 80%
> 4 01.01.04 USA 75%
> How to write query for that?
> Thanx
> Rgds
> K.Senthil Kumar

How to find Rank(return Top 3 Country)

Hi All,
These are the records I've in my table. I need to return top 3 countries (r
ank) by Sales.
Record Date Country Sales
1 01.01.04 AU 80%
2 01.01.04 IN 92%
3 01.01.04 CH 50%
4 01.01.04 USA 75%
5 01.01.04 SG 25%
The result set should be:
Record Date Country Sales
2 01.01.04 IN 92%
1 01.01.04 AU 80%
4 01.01.04 USA 75%
How to write query for that?
Thanx
Rgds
K.Senthil KumarSELECT TOP 3 Record, Date, Country, Sales
FROM your_table
ORDER BY Sales DESC
Jacco Schalkwijk
SQL Server MVP
"K.Senthil Kumar" <K.Senthil Kumar@.discussions.microsoft.com> wrote in
message news:6EB6A9BF-7A6D-4B10-8F9D-CC9087946489@.microsoft.com...
> Hi All,
> These are the records I've in my table. I need to return top 3 countries
(rank) by Sales.
> Record Date Country Sales
> 1 01.01.04 AU 80%
> 2 01.01.04 IN 92%
> 3 01.01.04 CH 50%
> 4 01.01.04 USA 75%
> 5 01.01.04 SG 25%
>
> The result set should be:
> Record Date Country Sales
> 2 01.01.04 IN 92%
> 1 01.01.04 AU 80%
> 4 01.01.04 USA 75%
> How to write query for that?
> Thanx
> Rgds
> K.Senthil Kumar

How to find last record on page?

How can i find number of last record on page?Do you want to find the value or count?|||i want to find count|||Use three formula

Formula1 @.Reset having the code
Numbervar count;
WhilePrintingRecords;
count:=0;

Formula2 @.count having the code
Numbervar count;
WhilePrintingRecords;
count:=count+1;

Formula3 @.Display having the code
Numbervar count;
WhilePrintingRecords;
count;

Place Formula1 at PageHeader and suppress
Place Formula2 at Details Section and suppress
Place Formula3 at PageFooter and dont suppress|||can i pass this max(count) parameter into Details Section? I want to suppress details if record is not last on page|||Not sure what you are asking. Do you want to show only the last record?|||i have detais a, details b sections in report and want to show details b section only for last record on page|||Goto section export
Select Details b
Nect to suppress option, there is button labelled x-2
clcik that button and write this code

not Nextisnull({FieldofDetailsB})|||not Nextisnull({FieldofDetailsB})

this code define last record in report, not in page

How to find last record in sql server 2000?

Hi friend, I am unable to get last record in sql server 2000. How to find last record in sql server 2000?

Quote:

Originally Posted by mcasaurabhsumit

Hi friend, I am unable to get last record in sql server 2000. How to find last record in sql server 2000?

select top 1 id from emp order by id desc

How to find how a record was deleted

Is there a way to tell who or what process deleted a record in a SQL table.
I know you can setup triggers or Profiler - but is there a way to see how a
record was deleted if you DO NOT have a trigger or Profiler already running?
This is SQL 2000 Standard, sp3a with default options and configuration.

ThanksHi rdraider,
It may help your cause
Step 1 : Login as sa or user with sa rights
Step 2 : Run a trace with minimum TSQL (Statement Start , Statement
Complete)
If you wish to know from where(EM or SQL Query Analyzer) record is
deleted then the column Applicationname of Trace output will help you.

With warm regards
Jatinder|||If you have log backups, then there are several third-party tools which
can read them and recover information about when data was deleted. For
example:

http://www.lumigent.com/products/le_sql.html

Simon

How to find duplicate records by tree field?

I have a table named student, there are tree fields GradeID, ClassID, Seat
in it.
How can I fild the duplicate record by the tree fields?
Perhaps this will help.
SELECT GradeID, ClassID, Seat, count(*) as Rows
FROM Students
GROUP BY GradeID, ClassID, Seat
HAVING count(*) > 1
Roy Harvey
Beacon Falls, CT
On Thu, 5 Oct 2006 05:45:22 +0800, "ad" <flying@.wfes.tcc.edu.tw>
wrote:

>I have a table named student, there are tree fields GradeID, ClassID, Seat
>in it.
>How can I fild the duplicate record by the tree fields?
>
|||try this
select Count(*),GradeID, ClassID, Seat
from dbo.tablename
group by GradeID, ClassID, Seat
having count(*) > 1
the count will show you how many rows are duplicated for each instance...
/*
Warren Brunk - MCITP - SQL 2005, MCDBA
www.techintsolutions.com
*/
"ad" <flying@.wfes.tcc.edu.tw> wrote in message
news:Ox9to5$5GHA.3292@.TK2MSFTNGP02.phx.gbl...
>I have a table named student, there are tree fields GradeID, ClassID, Seat
>in it.
> How can I fild the duplicate record by the tree fields?
>
|||Thanks,
The tabe's primary is PID, how can I show the duplicate rows with PID.

> select Count(*),GradeID, ClassID, Seat
> from dbo.tablename
> group by GradeID, ClassID, Seat
> having count(*) > 1
> the count will show you how many rows are duplicated for each instance...
> --
> /*
> Warren Brunk - MCITP - SQL 2005, MCDBA
> www.techintsolutions.com
> */
>
> "ad" <flying@.wfes.tcc.edu.tw> wrote in message
> news:Ox9to5$5GHA.3292@.TK2MSFTNGP02.phx.gbl...
>

How to find duplicate records by tree field?

I have a table named student, there are tree fields GradeID, ClassID, Seat
in it.
How can I fild the duplicate record by the tree fields?Perhaps this will help.
SELECT GradeID, ClassID, Seat, count(*) as Rows
FROM Students
GROUP BY GradeID, ClassID, Seat
HAVING count(*) > 1
Roy Harvey
Beacon Falls, CT
On Thu, 5 Oct 2006 05:45:22 +0800, "ad" <flying@.wfes.tcc.edu.tw>
wrote:

>I have a table named student, there are tree fields GradeID, ClassID, Seat
>in it.
>How can I fild the duplicate record by the tree fields?
>|||try this
select Count(*),GradeID, ClassID, Seat
from dbo.tablename
group by GradeID, ClassID, Seat
having count(*) > 1
the count will show you how many rows are duplicated for each instance...
--
/*
Warren Brunk - MCITP - SQL 2005, MCDBA
www.techintsolutions.com
*/
"ad" <flying@.wfes.tcc.edu.tw> wrote in message
news:Ox9to5$5GHA.3292@.TK2MSFTNGP02.phx.gbl...
>I have a table named student, there are tree fields GradeID, ClassID, Seat
>in it.
> How can I fild the duplicate record by the tree fields?
>|||Thanks,
The tabe's primary is PID, how can I show the duplicate rows with PID.

> select Count(*),GradeID, ClassID, Seat
> from dbo.tablename
> group by GradeID, ClassID, Seat
> having count(*) > 1
> the count will show you how many rows are duplicated for each instance...
> --
> /*
> Warren Brunk - MCITP - SQL 2005, MCDBA
> www.techintsolutions.com
> */
>
> "ad" <flying@.wfes.tcc.edu.tw> wrote in message
> news:Ox9to5$5GHA.3292@.TK2MSFTNGP02.phx.gbl...
>

How to find duplicate records by tree field?

I have a table named student, there are tree fields GradeID, ClassID, Seat
in it.
How can I fild the duplicate record by the tree fields?Perhaps this will help.
SELECT GradeID, ClassID, Seat, count(*) as Rows
FROM Students
GROUP BY GradeID, ClassID, Seat
HAVING count(*) > 1
Roy Harvey
Beacon Falls, CT
On Thu, 5 Oct 2006 05:45:22 +0800, "ad" <flying@.wfes.tcc.edu.tw>
wrote:
>I have a table named student, there are tree fields GradeID, ClassID, Seat
>in it.
>How can I fild the duplicate record by the tree fields?
>|||try this
select Count(*),GradeID, ClassID, Seat
from dbo.tablename
group by GradeID, ClassID, Seat
having count(*) > 1
the count will show you how many rows are duplicated for each instance...
--
/*
Warren Brunk - MCITP - SQL 2005, MCDBA
www.techintsolutions.com
*/
"ad" <flying@.wfes.tcc.edu.tw> wrote in message
news:Ox9to5$5GHA.3292@.TK2MSFTNGP02.phx.gbl...
>I have a table named student, there are tree fields GradeID, ClassID, Seat
>in it.
> How can I fild the duplicate record by the tree fields?
>|||Thanks,
The tabe's primary is PID, how can I show the duplicate rows with PID.
> select Count(*),GradeID, ClassID, Seat
> from dbo.tablename
> group by GradeID, ClassID, Seat
> having count(*) > 1
> the count will show you how many rows are duplicated for each instance...
> --
> /*
> Warren Brunk - MCITP - SQL 2005, MCDBA
> www.techintsolutions.com
> */
>
> "ad" <flying@.wfes.tcc.edu.tw> wrote in message
> news:Ox9to5$5GHA.3292@.TK2MSFTNGP02.phx.gbl...
>>I have a table named student, there are tree fields GradeID, ClassID, Seat
>>in it.
>> How can I fild the duplicate record by the tree fields?
>

How to find a running application in SQL Server?

How to find a running application in SQL Server?

Hi

In SQL Server (7.0, 2000), we know HOST_NAME() in a DEFAULT definition is used to record the workstation name of computers. But I need to find the name of the application which interacts my DB from the workstations. Is it possible? Please help me

Regards

Ahmed Sahib

ibnukuraish@.gmail.com

Hi Ahmed,

You can get this info from the following:

Code Snippet

Select * from master..sysprocesses

--or

SP_WHO2

Jag

|||

The equivalent to the host_name function for the application name is the app_name() function. The application name may or may not be set - it depends on the connection properties used by the application.

-Sue

How to find a record by number

What would be the best way in SQL to query a table to return a single record by number?
Let's say I want record number 49 and my primary key is a text field so a numeric key is out of the question.I think I figured it out unless anyone has a better way.

SELECT TOP 1 * FROM (SELECT TOP 49 * FROM [Table] ORDER BY [Key] ASC) AS Whatever ORDER BY [Key] DESC|||That looks like a bad idea. If you pass any number bigger than the max number of records in the table, you always get the last record.

any ideas?|||which row did you want, the 49th? by which column?

and yes, if there are fewer than 49 rows, it would seem appropriate (at least, it seems so to me) to choose the last one

what database are you using? this forum is for non-specific (i.e. standard) sql

you may want to consider the Access or SQL Server forums, if TOP works in your database

How to find a record

How to find a record in table without key? How to get specific record in table without key field and displaying on datagrid?

YOu explanation has to be a bit more specific. There is no problem in finfing any values (even if there is no "key" on the column) by just using a query for retrieving the data. I also do not understand what "How to get a specific record without a key filed" means, because if you want to have a specific record, you will have to provide an identifier for extracting / querying the data from the database.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

hello,

you can search with the select statement, e.g.

NUMBER: SELECT * FROM STORES WHERE ZIPCODE = 21512

SELECT * FROM STORES WHERE ZIPCODE IN (12345, 23145, 23456)

SELECT * FROM STORES WHERE ZIPCODE BETWEEN 21431 AND 21531

TEXT: SELECT * FROM BOOKS WHERE DESCRIPTION = 'COOKING GUIDE'

SELECT * FROM BOOKS WHERE DESCRIPTION LIKE '%COOKING%'

DATE: SELECT * FROM SALES WHERE DATESOLD BETWEEN '2006-01-01' AND '2006-01-02'

SELECT * FROM SALES WHERE DATESOLD >= '2006-01-01' AND DATESOLD < '2006-01-02'

As you can see it depends on the type of data you are looking and on which tables you have to combine to gather the data

More info (tables involved, columns) and the report you are trying to make would help

(I believe this is a T-SQL topic though)