Crystal Report queries

In my Crystal Report's Details Section, the blank space after the last line is unnecessarily printed and this disturbs page length settings. How to delete the unnecessary lines below the last text in the Details Section, so that they are not printed as Blank.

I even dragged the dividing line to the last Text Box in the Details section, but the problem persists.

Secondly, when I issue a Print Command, a message box with no text appears. IT's title is "Crystal Reports ActiveX Designer" and an OK button. No other message.Hi,
In the details section set the property of Fit Section(Right click on the left side of Details section, select Fit section). Make sure that page footer does not have empty space.

Madhivanan|||I use CR 8.5, so you may need to tweak my examples a bit if you're using a different version.

1. To suppress the last blank line, go into the Format of that section. Find where you can check Suppress and click the 'x-2' next to it. Enter the following keyword in the formula section: OnLastRecord. This will suppress that section only on the last record.

2. To keep the program from popping up a box when printing, try Report.PrintOut False

Crosstabs queries

Hello,
I'm a newbie and have just setup my first sql server based database. I
access it was quite easy creating queries and attaching them to reports.
Moving from access to Access data project, I'm finding it difficult to create
crosstab queries.
Can anyone point me in the right direction, bearing in mind that the
database created is in SQL server 2000 with Access 2000 (ADP) Client.
Thanks in advance.
I'm pretty sure there is but try these following groups as they should have the
exact syntax:
news:microsoft.public.sqlserver.server
news:microsoft.public.sqlserver.misc
news:comp.databases.ms-sqlserver
news:bit.databases.mssql-l
"Hans" wrote:

> Hello,
> I'm a newbie and have just setup my first sql server based database. I
> access it was quite easy creating queries and attaching them to reports.
> Moving from access to Access data project, I'm finding it difficult to create
> crosstab queries.
> Can anyone point me in the right direction, bearing in mind that the
> database created is in SQL server 2000 with Access 2000 (ADP) Client.
> Thanks in advance.
|||Its not as easy as creating cross tab queries in Access... You may want to
use sub queries and use those results from the sub queries and turn it into a
case statement to display in the main select quary..! Since you havent give
any idea on what you are trying to cross tab... (columns names, tables,
criteria) its hard to give you a syntex...
"Hans" wrote:

> Hello,
> I'm a newbie and have just setup my first sql server based database. I
> access it was quite easy creating queries and attaching them to reports.
> Moving from access to Access data project, I'm finding it difficult to create
> crosstab queries.
> Can anyone point me in the right direction, bearing in mind that the
> database created is in SQL server 2000 with Access 2000 (ADP) Client.
> Thanks in advance.
|||There are several xtab reoprts that needto be created. Example of one that
need to be prepared is.
table : tbl_patientepisodes
fields : EpisodeID - Unique(Index)
DateRefRecved - Date
DateRefAccepted - Date
DateRefNotAccepted - Date
rpt format:-
Title: Referals <from> to <To>
Apr May Jun Jul Aug Sep Oct Nov Dec Jan Feb Mar
No of referals reveived
No Of Referals Accepted
No Not Accepted
This reports will be based on a start and end date specified by the user.
hope this helps.
"SQLDBA" wrote:
[vbcol=seagreen]
> Its not as easy as creating cross tab queries in Access... You may want to
> use sub queries and use those results from the sub queries and turn it into a
> case statement to display in the main select quary..! Since you havent give
> any idea on what you are trying to cross tab... (columns names, tables,
> criteria) its hard to give you a syntex...
> "Hans" wrote:
|||PS sorry forgot to mention a graph need to be prepared with the reports as well
"SQLDBA" wrote:
[vbcol=seagreen]
> Its not as easy as creating cross tab queries in Access... You may want to
> use sub queries and use those results from the sub queries and turn it into a
> case statement to display in the main select quary..! Since you havent give
> any idea on what you are trying to cross tab... (columns names, tables,
> criteria) its hard to give you a syntex...
> "Hans" wrote:
|||"Hans" wrote:

> There are several xtab reoprts that needto be created. Example of one that
> need to be prepared is.
> table : tbl_patientepisodes
> fields : EpisodeID - Unique(Index)
> DateRefRecved - Date
> DateRefAccepted - Date
> DateRefNotAccepted - Date
Perhaps extracting data thusly:
-- Date Referred counts by month
SUM(CASE WHEN MONTH(DateRefRecvd) = 1 THEN 1 else 0 END) as JanRefRcvdCount,
SUM(CASE WHEN MONTH(DateRefRecvd) = 2 THEN 1 else 0 END) as FebRefRcvdCount,
..
..
..
SUM(CASE WHEN MONTH(DateRefRecvd) = 12 THEN 1 else 0 END) as DecRfRcvdCount,
-- Date Acepteded counts by month:
SUM(CASE WHEN MONTH(DateRefAccepted) = 1 THEN 1 else 0 END) as
JanRefAcceptedCount,
SUM(CASE WHEN MONTH(DateRefAccepted) = 2 THEN 1 else 0 END) as
FebRefAcceptedCount,
..
..
..
SUM(CASE WHEN MONTH(DateRefAccepted) = 12 THEN 1 else 0 END) as
DecRfAcceptedCount,

crosstab?

I've made "crosstab" queries in Access - it this doable in sql'
My table has:
ID
TestNumber
Score
Each user can have 1-3 records, one for each of the 3 tests.
I need my output to look like this:
ID Test1 Test2 Test3
--
1 score1 score2 score3
2 score1
3 score1 score2
(not everyone will have taken the 2nd or 3rd test at the same time; score#
just indicates the testscore)
'
Thanks for any help or pointers.
-RYes, but it must be statically defined like so:
Select Id
, Min(Case When TestNumber = 1 Then Score End) As Test1
, Min(Case When TestNumber = 2 Then Score End) As Test2
, Min(Case When TestNumber = 3 Then Score End) As Test3
From TableName
Group By Id
Thomas
"r" <r@.r.com> wrote in message news:uB0xq6%23XFHA.2768@.tk2msftngp13.phx.gbl...d">
> I've made "crosstab" queries in Access - it this doable in sql'
> My table has:
> ID
> TestNumber
> Score
> Each user can have 1-3 records, one for each of the 3 tests.
> I need my output to look like this:
> ID Test1 Test2 Test3
> --
> 1 score1 score2 score3
> 2 score1
> 3 score1 score2
> (not everyone will have taken the 2nd or 3rd test at the same time; score#
> just indicates the testscore)
> '
> Thanks for any help or pointers.
> -R
>
>|||SELECT id,
SUM(CASE WHEN testnumber = 1 THEN score END) AS test1,
SUM(CASE WHEN testnumber = 2 THEN score END) AS test2,
SUM(CASE WHEN testnumber = 3 THEN score END) AS test3
FROM YourTable
GROUP BY id
David Portas
SQL Server MVP
--|||If you ever need to go beyond xtab 101 check out
the powerful RAC utility.Similar in concept to Access xtab
but goes way beyond with its options and features.Also
includes other functionality (ie. ranking options) made easy.
www.rac4sql.net

cross-tab query

Hi all.

we have a department that has an access database with a bunch of queries in it. They want us to convert it to sql server. One of the queries is a cross-tab query. Is there an easy way to create this in sql? the column headings are the value of column from a table. This could change each month that they run it. How do I make the column heading a variable? I'm guessing a stored procedure would be best. Does anyone have any suggestions?

Thanks so much.
ODanielsWhat version of SQL Server?

If 2000, then no, there is no "easy" way. This is one of the more irritating omissions from SQL 2k.

If you have 2k5, then you're good to go.|||it's not too hard in sql 2k. there is even a section in BOL if you need a how to.|||The section in BOL requires you to know all of the "column" values in advance...|||It's sql2k.
That's what I saw also - need to know all the column values in advance.
I can build the table with a bunch of statements, I was just hopeing there was an easier way.|||I have a crosstab sproc tucked away somewhere. I'll dig it up in a bit here.

... and there she is:

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO



CREATE PROCEDURE crosstab @.XField varChar(50), @.XTable varChar(50),
@.XWhereString varChar(250), @.XFunction varChar(10), @.XFunctionField varChar(20), @.XRow varchar(40)
AS
Declare @.SqlStr nvarchar(4000)
Declare @.tempsql nvarchar(4000)
Declare @.SqlStrCur nvarchar(4000)
Declare @.col nvarchar(100)

set @.SqlStrCur = N'Select [' + @.XField + '] into ##temptbl_Cursor from [' + @.XTable + '] ' + @.XWhereString + ' Group By [' + @.XField + ']'

exec sp_executesql @.sqlstrcur

declare xcursor Cursor for Select * from ##temptbl_Cursor
open xcursor

Fetch next from xcursor
into @.Col

While @.@.Fetch_Status = 0
Begin
set @.Sqlstr = @.Sqlstr + ", "
set @.tempsql = isnull(@.sqlstr,'') + isnull(@.XFunction + '( Case When ' + @.XField + " = '" +@.Col +
"' then [" + @.XFunctionField + "] Else 0 End) As [" + @.Col + "]" ,'')
set @.Sqlstr = @.tempsql
Fetch next from xcursor into @.Col

End

set @.tempsql = 'Select ' + @.XRow + ', ' + @.Sqlstr + ' From ' + @.XTable +
@.XWhereString + ' Group by ' + @.XRow + ' Order By ' + @.XRow
set @.Sqlstr = @.tempsql
PRINT @.tempsql
Close xcursor
Deallocate xcursor

set @.tempsql = N'Drop Table ##temptbl_Cursor'
exec sp_executesql @.tempsql

exec sp_executesql @.Sqlstr


GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

I should add that I shameless stole this from some random website, though I can't remember where it was.|||You are awsome Teddy.
I will give that a try. Thank you.

Crosstab Queries in SQL Server 2005

Hi there,

I'm trying to make a cross tab query in SQL Server 2005 SP2, I have it done in MS Access but I want to switch all my queries that use crosstabs into SQL.

I find this Query on the web:

Code Snippet

CREATE PROCEDURE [sp_CrossTabIntoTable]

@.select varchar(8000),

@.sumfunc varchar(100),

@.pivot varchar(100),

@.table varchar(100)

AS

DECLARE @.sql varchar(8000), @.delim varchar(1)

SET NOCOUNT ON

SET ANSI_WARNINGS OFF

EXEC ('SELECT ' + @.pivot + ' AS pivot INTO ##pivot FROM ' + @.table + ' WHERE 1=2')

EXEC ('INSERT INTO ##pivot SELECT DISTINCT ' + @.pivot + ' FROM ' + @.table + ' WHERE '

+ @.pivot + ' Is Not Null')

SELECT @.sql='', @.sumfunc=stuff(@.sumfunc, len(@.sumfunc), 1, ' END)' )

SELECT @.delim=CASE Sign( CharIndex('char', data_type)+CharIndex('date', data_type) )

WHEN 0 THEN '' ELSE '''' END

FROM tempdb.information_schema.columns

WHERE table_name='##pivot' AND column_name='pivot'

SELECT @.sql=@.sql + '''' + convert(varchar(100), pivot) + ''' = ' +

stuff(@.sumfunc,charindex( '(', @.sumfunc )+1, 0, ' CASE ' + @.pivot + ' WHEN '

+ @.delim + convert(varchar(100), pivot) + @.delim + ' THEN ' ) + ', ' FROM ##pivot

DROP TABLE ##pivot

SELECT @.sql=left(@.sql, len(@.sql)-1)

SELECT @.select=stuff(@.select, charindex(' FROM ', @.select)+1, 0, ', ' + @.sql + ' ')

EXEC (@.select)

SET ANSI_WARNINGS ON

But when I tried to run it I got this error message:

Msg 156, Level 15, State 1, Procedure sp_CrossTabIntoTable, Line 25

Incorrect syntax near the keyword 'pivot'.

I need it ASAP because I need to create new reports and I don't want to use MS Access no more.

Is there someone who knows what the problem is?

Thanks in advance,

Julien

PIVOT is a reserved keyword in 2005. Either change the column name eg. Pvt or put identifiers around the column name eg 'Pivot'

HTH
|||

Ok PIVOT is the operator/keyword in SQL Server 2005 use the following query...

Code Snippet

CREATE PROCEDURE [sp_CrossTabIntoTable]

@.select varchar(8000),

@.sumfunc varchar(100),

@.pivot varchar(100),

@.table varchar(100)

AS

DECLARE @.sql varchar(8000), @.delim varchar(1)

SET NOCOUNT ON

SET ANSI_WARNINGS OFF

EXEC ('SELECT ' + @.pivot + ' AS [pivot] INTO ##pivot FROM ' + @.table + ' WHERE 1=2')

EXEC ('INSERT INTO ##pivot SELECT DISTINCT ' + @.pivot + ' FROM ' + @.table + ' WHERE '

+ @.pivot + ' Is Not Null')

SELECT @.sql='', @.sumfunc=stuff(@.sumfunc, len(@.sumfunc), 1, ' END)' )

SELECT @.delim=CASE Sign( CharIndex('char', data_type)+CharIndex('date', data_type) )

WHEN 0 THEN '' ELSE '''' END

FROM tempdb.information_schema.columns

WHERE table_name='##pivot' AND column_name='pivot'

SELECT @.sql=@.sql + '''' + convert(varchar(100), pivot) + ''' = ' +

stuff(@.sumfunc,charindex( '(', @.sumfunc )+1, 0, ' CASE ' + @.pivot + ' WHEN '

+ @.delim + convert(varchar(100), pivot) + @.delim + ' THEN ' ) + ', ' FROM ##pivot

DROP TABLE ##pivot

SELECT @.sql=left(@.sql, len(@.sql)-1)

SELECT @.select=stuff(@.select, charindex(' FROM ', @.select)+1, 0, ', ' + @.sql + ' ')

EXEC (@.select)

SET ANSI_WARNINGS ON

|||THANK YOU BOTH!!

I finally get it working!!

Best regards

Julien

Crosstab queries in MS SQL

Am I right in coming to the conclusion that there is no built in method for
creating crosstab queries in MS SQL 2000? I have form the reference under
"Pivot" in SQL books that demonstrates how to build your own but it doesn't
account for scenarios where I don't know how many column headings I require.
I have mocked up this SQL text in MS Access which shows what I want but I
can't seem to generate such a result set in SQL.
Any Ideas?
Mr. Smith
SQL Text:............
TRANSFORM Sum(testoutput.JobCount) AS SumOfJobCount
SELECT testoutput.Client, testoutput.ContractName,
testoutput.OfficeLocationID, [Request Month] & ' ' & [Request Year] AS ReqMt
h
FROM testoutput
GROUP BY testoutput.Client, testoutput.ContractName,
testoutput.OfficeLocationID, [Request Month] & ' ' & [Request Year]
PIVOT testoutput.TradeCategory;That's correct. To pivot in SQL 2000 you need to know how many columns
there will be because you essentially have to have a case statement for
each column. SQL 2005 is in that respect because they've
introduced the PIVOT & UNPIVOT keywords (part of the SELECT clause
syntax) to make the syntax native to the T-SQL language used in Yukon.
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
Mr. Smith wrote:

> Am I right in coming to the conclusion that there is no built in method fo
r
>creating crosstab queries in MS SQL 2000? I have form the reference under
>"Pivot" in SQL books that demonstrates how to build your own but it doesn't
>account for scenarios where I don't know how many column headings I require
.
>I have mocked up this SQL text in MS Access which shows what I want but I
>can't seem to generate such a result set in SQL.
>Any Ideas?
>Mr. Smith
>SQL Text:............
>TRANSFORM Sum(testoutput.JobCount) AS SumOfJobCount
>SELECT testoutput.Client, testoutput.ContractName,
>testoutput.OfficeLocationID, [Request Month] & ' ' & [Request Year] AS ReqM
th
>FROM testoutput
>GROUP BY testoutput.Client, testoutput.ContractName,
>testoutput.OfficeLocationID, [Request Month] & ' ' & [Request Year]
>PIVOT testoutput.TradeCategory;
>
>|||Mr. Smith meet RAC:)
www.rac4sql.net

Crosstab queries

Dear friends,

I wonder if exist a way to make crosstab queries in SQL Server
like those in Access without "external" programming. Does the
SQL Server supports the "TRANSFORM" SQL-extension?

Thanks in advance, Sotiris.Sotiris Rentoulis (rentoulis@.hotmail.com) writes:
> I wonder if exist a way to make crosstab queries in SQL Server
> like those in Access without "external" programming. Does the
> SQL Server supports the "TRANSFORM" SQL-extension?

No.

There is no particular support for pivot tables in SQL 2000. SQL 2005,
currently in beta, comes with a PIVOT operator. It still only supports
static pivot tables.

Here is a simple example of a static crosstab in SQL 2000:

SELECT product,
Q1 = SUM(CASE datepart(month, salesdate) / 3 WHEN 0 THEN amt END),
Q2 = SUM(CASE datepart(month, salesdate) / 3 WHEN 1 THEN amt END),
Q3 = SUM(CASE datepart(month, salesdate) / 3 WHEN 2 THEN amt END),
Q4 = SUM(CASE datepart(month, salesdate) / 3 WHEN 3 THEN amt END)
FROM sales
GROUP BY product

Dynamic crosstabs requires you to write dynamic SQL. Note that dynamic
crosstabs, how useful they be, do not really fit into the relational
model. A popular tool for dynamic crosstab is RAC, see
http://www.rac4sql.net.

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

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

Cross Tab Query giving erros

Hi,
I'm having a problem while writing cross tab queries. The fact is I'm getting errors when I execute the query.
The query is as follows.

SELECT skill_Id,
count(CASE final_rating WHEN 5 THEN assoc_name ELSE 0 END) AS "5",
count(CASE final_rating WHEN 4 THEN assoc_name ELSE 0 END) AS "4",
count(CASE final_rating WHEN 3 THEN assoc_name ELSE 0 END) AS "3",
count(CASE final_rating WHEN 2 THEN assoc_name ELSE 0 END) AS "2"
FROM viewfinalrating
GROUP BY skill_id

My purpose is to get the count of the assoc_name for each final_rating grouped by skill_id. When I execute the above query I'm getting the error

Server: Msg 245, Level 16, State 1, Line 1
Syntax error converting the varchar value 'Name1' to a column of data type int.

Name1 is an assoc_name in the view viewfinalrating. Why is SQL server trying to convert the assoc_name to int. Isn't the query supposed to get the count of assoc name based on the CASE statements?

What could be the problem?

Thanks in advance
Regards,
P.C. VaidyanathanWhen you use CASE statments you must return like data types. You need to change the "ELSE 0" to a "ELSE '0'" or "Null". However if you change you approach just a bit I think you can achive your goal...

Try
------------------------------
SELECT skill_Id,
sum(CASE final_rating WHEN 5 THEN 1 ELSE 0 END) AS "5",
sum(CASE final_rating WHEN 4 THEN 1 ELSE 0 END) AS "4",
sum(CASE final_rating WHEN 3 THEN 1 ELSE 0 END) AS "3",
sum(CASE final_rating WHEN 2 THEN 1 ELSE 0 END) AS "2"
FROM viewfinalrating
GROUP BY skill_id
----------------------------

Cross referncing databases

Does Referencing databases in queries make queries any
slower
For ex
use DB2
go
select col1 from DB1..Table1
will above be any slower than
use DB1
go
select col1 from DB1..Table1
sanjayI don't think it would make much difference in speed.. But having cross
database references does introduce a level of complexity that you might not
otherwise have... Since each database can be restored independently of the
others, they could get 'out of sync' ... leaving you with big referential
problems... So , I *try* to keep all references local whenever possible... I
have seen some major applications which have 20-30 databases for a single
app and tons of cross database references, these companies are successful in
their products, but it makes my skin crawl...
--
Wayne Snyder MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
(Please respond only to the newsgroups.)
I support the Professional Association for SQL Server
(www.sqlpass.org)
"Sanjay" <sanjayg@.hotmail.com> wrote in message
news:9a6b01c34636$605f4320$a401280a@.phx.gbl...
> Does Referencing databases in queries make queries any
> slower
> For ex
> use DB2
> go
> select col1 from DB1..Table1
> will above be any slower than
> use DB1
> go
> select col1 from DB1..Table1
> sanjay
>
>

Cross Database Queries in SQL Server 2000

Outside of security considerations, are there any performance issues
that should be addressed when using cross database queries on the same
server?Not for SELECT. There is a slight overhead for cross-database transactions b
ecause they involve
several databases transaction logs so an internal 2-phase commit protocol is
used.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Paul Sinclair" <paul.sinclair@.gmails.com> wrote in message
news:enV%23GO1UGHA.4944@.TK2MSFTNGP10.phx.gbl...
> Outside of security considerations, are there any performance issues that
should be addressed when
> using cross database queries on the same server?|||Tibor Karaszi wrote:
> Not for SELECT. There is a slight overhead for cross-database
> transactions because they involve several databases transaction logs so
> an internal 2-phase commit protocol is used.
>
Great, that's what I was seeing in my execution plans against recreated
objects in the same database vs objects in another database - but wasn't
sure if I was missing something or not. Thanks again for the help.
Paul

cross database queries

i know that db.owner.tablename works in the query analyzer, but what i really appreciate from anyone is how to apply this in vb6 code since the recordset is opened only from one db using the following syntax:

rs.open "select ...", dbname, ..., adopendynamic, adlockoptimistic ...

thanksWrite a stored procedure. :)