Multi Page Report

Hi All,
I have two tables in a SQL 2000 database. One table contains a few text
fields and the other contains any overflow data from the original table.
I am required to print a one page report with the data from the first table
and then if there are any data in the overflow table, to automatically print
a second page containing the data from the second table.
Page 2 of the report doesn't look anything like page 1. It's layout is
different and it only contains the data from the second table.
These text fields (in the overflow) may be a max of 10K.
I'm new to RS and this requirement has been beating me up.
Any ideas?
Thanks.Two options. You can use either multiple datasets and put a page break
between them or you can use a sub report. Again with a page break between
them.
Bruce L-C
"brawtaman" <brawtaman@.discussions.microsoft.com> wrote in message
news:20F24200-5B1D-449F-BA9D-E9AD48C2B5B4@.microsoft.com...
> Hi All,
> I have two tables in a SQL 2000 database. One table contains a few text
> fields and the other contains any overflow data from the original table.
> I am required to print a one page report with the data from the first
> table
> and then if there are any data in the overflow table, to automatically
> print
> a second page containing the data from the second table.
> Page 2 of the report doesn't look anything like page 1. It's layout is
> different and it only contains the data from the second table.
> These text fields (in the overflow) may be a max of 10K.
> I'm new to RS and this requirement has been beating me up.
> Any ideas?
> Thanks.|||Thanks.
We actually started looking at the Sub-Report option.
Thanks for your help.
"Bruce Loehle-Conger" wrote:
> Two options. You can use either multiple datasets and put a page break
> between them or you can use a sub report. Again with a page break between
> them.
> Bruce L-C
> "brawtaman" <brawtaman@.discussions.microsoft.com> wrote in message
> news:20F24200-5B1D-449F-BA9D-E9AD48C2B5B4@.microsoft.com...
> > Hi All,
> >
> > I have two tables in a SQL 2000 database. One table contains a few text
> > fields and the other contains any overflow data from the original table.
> >
> > I am required to print a one page report with the data from the first
> > table
> > and then if there are any data in the overflow table, to automatically
> > print
> > a second page containing the data from the second table.
> >
> > Page 2 of the report doesn't look anything like page 1. It's layout is
> > different and it only contains the data from the second table.
> >
> > These text fields (in the overflow) may be a max of 10K.
> >
> > I'm new to RS and this requirement has been beating me up.
> >
> > Any ideas?
> >
> > Thanks.
>
>

Multi Language Form

Hi,

I have a table in an MS SQL 2000 database that represents fields on a form.

CREATE TABLE [dbo].[TagData](
[FieldName] [nvarchar](255),
[UserID] [int] NOT NULL,
[Data] [nvarchar](255)
)

A requirement has come up where some of these fields must contain Hebrew, or any other unicode character, data and some of the fields will be English. How can I go about saving and retrieving this information.

The current solution is a legacy Classic ASP application and I suspect I am going to have to redo this in ASP.Net

Thanks,
Leon

Since the data type in the table is nvarchar, you can store any unicode values.

You select/insert into the table as normal way of inserting the data there will not be any difference in doing DML operations.

Sample insert statement is as follows:

INSERT INTO TagData values(N'Sample Field', 10, N'Sample Data'); --> please "N" is for specifying it as nvarchar.

When you want to display content in ASP.Net depending on the language setting, we have to use localization and Globalization concepts in ASP.Net

Following URL will provide you more info on the same in ASP.Net:

http://www.codeproject.com/useritems/localization.asp

|||

I found this link which helped a lot.

http://www.microsoft.com/globaldev/getWR/steps/wrg_codepage.mspx

sql

multi field select using IN...

here is what i am wanting to do but is it possible to use multi fields with an IN statement?

SELECT *
FROM usmastf
WHERE usm_book, usm_acct IN
(SELECT usac_book, usac_acct
FROM uscommf
WHERE usac_code = 'O/W')i finally found it|||Not with an IN function. Use a standard JOIN instead:
SELECT usmastf.*
FROM usmastf
inner join uscommf
on usmastf.usm_book = uscommf.usac_book
and usmastf.usm_acct = uscommf.usac_acct
WHERE uscommf.usac_code = 'O/W'|||What does the pipe sign actually represent in the following

select * from usmastf
where ((usm_book | usm_acct) in (select usac_book | usac_acct from uscommf where usac_code = 'O/W'))|||The pipe character represents a boolean "OR" evaluation, but I don't think it is syntactically correct in your statement.|||in particular, the vertical bar means *bitwise* or, not logical or (same as in C/C++).

logical or is just the word "or" in sql (this would be || in C/C++).|||oh you silly microsoft peoples

hicpics, i think you're thinking of two vertical pipes, which is the sql standard for concatenation

... where usm_book||usm_acct in (select usac_book||usac_acct from ...|||oh you silly microsoft peoplesI can't imagine why people would assume that the post was talking about Microsoft SQL in this forum! ;)

You have a good point, and I thought of that myself, but we need to assume that the poster really is working with Microsoft SQL when they post a question here. If they are using a different SQL or SQL-like language then one of the moderators ought to move the post for them.

-PatP|||I too thought of concatenation, as I am unfortunately mired in the Oracle world at the moment. But the poster asked about the meaning of a single pipe, not double-pipes.|||actually the original poster never said "single pipe" -- and he was clearly using it as a concatenation operator in post #4

and being mired in oracle would only be an interesting coincidence, the double pipes are standard sql -- are you suggesting that oracle peoples have a better understanding of standard sql than microsoft peoples?

:)|||can one of you educate me? I have never heard of | being used as a concatenation operator. Do you mean string concatenation? Every reference in BOL I can find about | says it's bitwise or.

the | means bitwise or in most languages I am familiar with (except befunge, but I can't say I'm that familiar with it :))

and this don't work at all: select 'asdf' || 'qwerty'

:)|||Double-pipes concatenation is not allowed syntax for SQL Server.

You are trying to say "If A OR B is in C OR D", but this is a logical comparison and not a bitwise "OR" which is what the pipe character stands for. So your syntax will not work.

Did you try the code I posted?|||Who uses a single pipe as a conact operator?

MySQL?

I doubt it...

Oracle and DB2 are ||

I always thought + was a silly conact operator for chars|||I always thought + was a silly conact operator for chars

really? makes perfect sense to me.

||, on the other hand, makes no sense to me as concat. || has always meant logical or to me. But I was raised on C, not sql.

If you were implementing a string class in C++ and overloaded || to mean string concat, I'd have to, well, I won't say it. :)

EDIT: actually, you should never overload || for any reason, but that's a separate issue.|||really? makes perfect sense to me. oh?

what is the result of SELECT 2 + '2' then?

:)|||IMO, that *should* return a cast error. Only in nasty languages such as vb or javascript would I expect such things to be permitted.

it appears the ms (or maybe sybase) devs thought otherwise.

EDIT: SELECT 2 + '0.2' -- now there's a result I like (well, sort of)

multi columns in detail section

I have three fields in my report that make up each record. I would like to display these in detail section.

so instead of

1. alam Dept1 10000
2. khaiser dept2 20000
3. mujeeb dept2 20000

I would have

alam khaiser mujeeb ...
dept1 dept2 dept2 ...
10000 20000 20000 ...I have three fields in my report that make up each record. I would like to display these in detail section.

so instead of

1. alam Dept1 10000
2. khaiser dept2 20000
3. mujeeb dept2 20000

I would have

alam khaiser mujeeb ...
dept1 dept2 dept2 ...
10000 20000 20000 ..|||I have three fields in my report that make up each record. I would like to display these in detail section.

so instead of

1. alam Dept1 10000
2. khaiser dept2 20000
3. mujeeb dept2 20000

I would have

alam khaiser mujeeb ...
dept1 dept2 dept2 ...
10000 20000 20000 ..

i'm in urgent need of the solution. my project is at termination . i need some help on the above said details . i will be thankfull for it .|||'m in urgent need of the solution. my project is at termination . i need some help on the above said details . i will be thankfull for it .|||Use Cross tab Report
What database are you using?

Mullti-report navigation through hyperlinking in Crystal Reports XI

Hi everybody.
I have 4 fields(columns) in report A, 3 fields in report B and 3 fields in report C. What I want is, when a user clicks on the values in the first field in report A, say EmpId, it should jump to report B with only the data for the EmpId on which the user clicked. Again, when the user clicks on the 3rd field in report B(the opened/generated from A), it should open the report C with corresponding values of report B. In short, it should jump from A->B->C.
A part of it can be achieved by using subreports, but it cannot jump from B->C.
Can anyone suggest anything.
Thanks in advance.
Waiting eagerly for reply.

Thanks.U can use on demand subreports, but a main report can have many subrepports but a subreport cannot have another subreport.

Mulitple Queries in a Stored Procedure

I have written a stored procedure that contains queries which return data.
It works correctly in Query Analyzer, returning each of the labled fields.
When I create a report the only return field the report wizard shows is from
the first query. When I add each of the other return fields and run the
report in VS I get an out of index error.RS only supports a single resultset being returned.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"bassbuster" <bassbuster@.discussions.microsoft.com> wrote in message
news:39FAA817-C491-493D-81BB-FDA153D7DB57@.microsoft.com...
> I have written a stored procedure that contains queries which return data.
> It works correctly in Query Analyzer, returning each of the labled fields.
> When I create a report the only return field the report wizard shows is
> from
> the first query. When I add each of the other return fields and run the
> report in VS I get an out of index error.

Mulitple Joins and Nulls

I am trying to join two tables on multiple fields. But the nulls aren't considered a match so they aren't included in the results set.

Select A.Lot, A.Block, A.Plan, B.Key

from A join B on

A.Lot=B.Lot

A.Block=B.Block

A.Plan=B.Plan

In the data, there can be an instance where Block is null in both tables so it "matches" but not in SQL. How do I get the "matched" nulls to be returned as well?

You can't match on the nulls in the database.|||Try something like this:

Select A.Lot, A.Block, A.Plan, B.KeyFrom Ajoin Bon A.Lot = B.LotAnd A.Plan = B.PlanAnd( (A.BlockIsNull And B.BlockIsNull)Or (A.Block = B.Block))

|||Richard, that worked great. Thanks for the excellent advice.

mulitiple selects in a stored procedure

Hi,
I am trying to create a pagination within a stored procedure but I need
to select from several tables:
First I am inserting the fields into a temp table and this works fine:
INSERT INTO #TempItems (Name)
SELECT Name FROM tblName
I thought I could get data from other tables using UNION:
INSERT INTO #TempItems (Name,Address,Telephone,Street)
SELECT Name FROM tblName
UNION
SELECT Address FROM tblAddress
UNION
SELECT Telephone FROM tblTelephone
UNION
SELECT Street FROM tblStreet
*These are bogus fields I have used as examples.
But the error I get is that the number of insert fields is less then the
select? Could anyone help on how the best way to achieve this?
many thanks in advance
Peter
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!You can google the newsgroups (suggest .programming) for pagination
techniques. Your error is due to a misunderstanding of how union works.
Union merely combines separate result sets into one; you are attempting to
use it (incorrectly) as you would a join. Assuming that a union could be
used, you must specify 4 items within the select list of each select
statement that is part of the union, corresponding to the 4 columns to be
inserted. There are other flaws in your logic, but this should get you
started.
You should be inserting using something like the following
insert ...
select ...
from tblName inner join tblAddress on ...
inner join tblTelephone on ...
inner join tblStreet on ...
where ...
How those joins are made (and their type - inner, outer, cross, etc) I
cannot answer without knowing the relationships between the table.
"Peter Rooney" <peter@.whoba.co.uk> wrote in message
news:OoqGZO3rDHA.2456@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I am trying to create a pagination within a stored procedure but I need
> to select from several tables:
> First I am inserting the fields into a temp table and this works fine:
> INSERT INTO #TempItems (Name)
> SELECT Name FROM tblName
> I thought I could get data from other tables using UNION:
> INSERT INTO #TempItems (Name,Address,Telephone,Street)
> SELECT Name FROM tblName
> UNION
> SELECT Address FROM tblAddress
> UNION
> SELECT Telephone FROM tblTelephone
> UNION
> SELECT Street FROM tblStreet
> *These are bogus fields I have used as examples.
>
> But the error I get is that the number of insert fields is less then the
> select? Could anyone help on how the best way to achieve this?
> many thanks in advance
> Peter
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!sql

MSSQL Query Memory when using long fields

Here is our problem...

We are doing a lot of selects against a table that has one large field
in it.

If we do a select against all the fields except for description, the
query comes back relatively quickly. If we add that last field (768
chars) to the query, our query takes 10x longer (5 seconds vs 56
seconds.)

When we run the one without the description column, we can watch
perfmon and see a very quick spike to physical disk. If we add in the
description field we can see that the server becomes I/O bound - the
disk sits at 100% until the query is complete.

We have tweaked the min query memory setting for the server but it
seems to have had no effect no matter how high we set it. Is there
some point at which MSSQL decides it cannot perform the transaction in
memory? What would I increase to cure this problem?

For example:
TMZDIFF int410
WRITETIMEcharno 16
System_Namecharno 64
Timestampcharno16
Name charno32
Mount_Pointcharno32
Size intno4 10
Space_Usedintno410
Space_Availableintno410
Inode_Sizeintno410
Inodes_Usedintno410
Inodes_Freeintno410
Space_Used_Percentintno410
Inodes_Used_Percentintno410
FS_Type charno8
Space_Available_Percentintno410
Name_U ncharno32
Descriptionncharno768(wildfyre53207@.yahoo.com) writes:
> If we do a select against all the fields except for description, the
> query comes back relatively quickly. If we add that last field (768
> chars) to the query, our query takes 10x longer (5 seconds vs 56
> seconds.)

My initial reaction is that there is an index on the table that covers
all columns, save the long column.

Could you post CREATE TABLE and CREATE INDEX statements for the table?
Don't forget PRIMARY KEY and UNIQUE constraints.

> We have tweaked the min query memory setting for the server but it
> seems to have had no effect no matter how high we set it. Is there
> some point at which MSSQL decides it cannot perform the transaction in
> memory? What would I increase to cure this problem?

How many rows are you returning? On what sort of network connection?
How much memory do you have in the machine?

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

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||give us an example of the two selects.