crystal reports row count

bebe lush posted at 20-Sep-06 05:58
How can i add a column in the report to count the rows, and to auto increment.
Like:
1. | line 1
2. | line 2
3. |
to show me the current number.Friend,

Do one thing create one Formula Field named @.SlNo, and insert into detail section. Formula is given below:

//------------------

numberVar numSlno = 0;

IF PREVIOUSISNULL(<Name of Any Field>) THEN
numSlno := 1
ELSE
numSlno := numSlno + 1

//------------------|||why use formula
just include record number itself :wave:|||why use formula
just include record number itself :wave:
Thats correct way|||I tried this one but I got error at the part (<Name of Any Field>). I am a newbie, could you please elaborate on it? Thanks a lot!

Friend,

Do one thing create one Formula Field named @.SlNo, and insert into detail section. Formula is given below:

//------------------

numberVar numSlno = 0;

IF PREVIOUSISNULL(<Name of Any Field>) THEN
numSlno := 1
ELSE
numSlno := numSlno + 1

//------------------|||Well, that code has a bug anyway.
As said before, in the Field Explorer, just use the special field Record Number.|||as one of the repliers said,
the best way to me is to add a special field recordNumber.
OR you can also use a running total field.
In fields Explorer, Create new Running Total Field, Choose any of the fields (Preferably numeric) Summerize.
and select Count .
Allow it to execute for Each Record
One of these things should solve the problem.
The Last Solution i would suggest will be to use shared variables.|||as one of the repliers said,
the best way to me is to add a special field recordNumber.
OR you can also use a running total field.
In fields Explorer, Create new Running Total Field, Choose any of the fields (Preferably numeric) Summerize.
and select Count .
Allow it to execute for Each Record
One of these things should solve the problem.
The Last Solution i would suggest will be to use shared variables.

Why to make things complicated?

Just use Record Number field.

Crystal report export problem

Hello,

I'm having difficulty exporting a crystal 7 report to excel. All appears to be fine except the last column which does not appear in the excel sheet.

All the fields are snapped to guidelines and all have the same formatting, so that doesn't seem to be the problem.

Any ideas greatly appreciated.

Thanks
SeasnWhat is the information shown as last column?

Crosstab Report

I am creating report with total costs by job, it repeats "column totals"
two times, and at the end column "grand total". "grand total" column is okay.
Is there any way I can drop/disable repeating "column totals". See example below, help me to drop repeating column.

Thanks

Job 1 Job 2 GrandTotal
Total Total

Cost 1 177,855 177,855 162,755 162,755 340,610
Cost 2 169,847 169,847 161,063 161,063 330,910
Cost 3 37,404 37,404 102,416 102,416 139,820In CR,right click on the cross tabl. Choose format Cross tabl. U ve a tab like "Customize Style". Choose each and every row in Rows List and below that u ve Grouping Option. Suppres that grouping option. I think this may be a soln. Try it out.

Cross-Tab Query

I need to produce a query in cross-tab format that displays the column data as the first day of the week for every week for a given date range.

i.e. for August
4 Aug 03 | 11 Aug 03 | 18 Aug 03 | 25 Aug 03

Row1
Row2
etc...

Any pointer in the right direction would be appreciated.
Thanks
TimThe basic idea is to make a query, which returns the value to be accumulated with the corresponding week indication, like:

SELECT YourGroupingField,
case datediff(wk, '2003-01-01', getdate()) < 30 THEN YourSumField ELSE 0 END AS Aug0,
case datediff(wk, '2003-01-01', getdate()) = 30 THEN YourSumField ELSE 0 END AS Aug1,
case datediff(wk, '2003-01-01', getdate()) = 31 THEN YourSumField ELSE 0 END AS Aug2,
case datediff(wk, '2003-01-01', getdate()) > 31 THEN YourSumField ELSE 0 END AS Aug3
FROM YourTable

You may use this query as a subquery or as a database view:

SELECT YourGroupingField, sum(Aug0), sum(Aug1), ...
FROM (YourQuery)
GROUP BY YourGroupingField

Let me know if this helps, or when you don't understand my outline.|||Thank you for your quick reply.
I think I did not explain myself that well. So here goes - the column dates should auto-generate based on a date range. This date range could potentially be several months or years. So if the date range was 1 March 03 - 30 June 03 this would produde 18 columns starting with 3 Mar 03.

I know how to write a normal cross-tab but it's the auto-column generation that's tricky for me.|||You want the heavy stuff? Look at this (www.sqlmag.com/Articles/Index.cfm?ArticleID=15608).

Crosstab help

Hello,

I need to accomplish the turning column data into row data via SQL. I can sorta get what I want with creating the report as a matrix report. However, I always seem to need one little thing to happen to shape my data as I need it. Anyway, here goes...

I have a table with 4 columns

UNIQUE | Code | FieldID | CustomField

The Code column is the customer code. FieldID numbers 1-100 and CustomField has string data.

My problem. I need to be able to choose ALL Customers [Code] where FieldID/s ="6", "7" & "8" and the CustomField rowdata corresponding to the FieldID data. BUT I then need to be able to use a daterange parameter on any row with a FieldID of "6" but that is not a datetime format it is in a string format.

Currently my SQL is:

SELECT

[Unique ID], Code, [Field ID] AS IUdate, [Field ID] AS IUNote, [Field ID] AS IUReq, [Custom Field]

FROM

dbo.[Customer Custom Field]

WHERE

(Code = '07-8111')AND ([Field ID] = 6) OR (Code = '07-8111')AND ([Field ID] = 7) OR (Code = '07-8111')AND ([Field ID] = 8)

However, everytime I try to run a daterange against, I get all kinds of data I don't need because the parameter is running against all the fields and NOT just the stringdate.

My idea is to convert the column data into row data (like a crosstab query) to sharpen it up for parameter ranges.

Is there something i am missing here? Can someone point me in the right direction?

Thanks in advance!

phorest

It sounds like you need a pivot table. Here is a great article on building queries using dynamic pivot tables.

http://www.sqlservercentral.com/columnists/plarsson/pivottableformicrosoftsqlserver.asp

If you have trouble, provide an example table full of data and I will take a shot at building a sample query.

Good luck!

Larry

|||

Thanks for your help.

Just as I expected, though I did not mention that the database is SQL2000 so the pivot is probably not an option. I suppose I need to use CAST in the SQL statement to accomplish this. Is it possible to do this simply without having to create a stored procedure -or- temporary table?

My sample data below: (NOTE: I am only using the FieldID numbers 6,7, & 8 as they are the only ones I need)

Unique ID Code FieldID Custom Field
-- -- --
5625 05-4256 8 C
5629 COLE0001 6 02/24/2006
5630 COLE0001 7 Need Invoice date & claim#
5631 COLE0001 8 C
5637 05-3945 6 09/07/2006
5638 05-3945 7 Auth expired 08/21/06 (11/18)
5660 05-3670 6 06/17/2006
5661 05-3670 7 auth expired 06/06/06 (3/10)
5670 HUNTER0000 6 04/27/2006
5671 HUNTER0000 7 Need Charges04/07/06 DOS (box checked: not listed on walkout)
5672 HUNTER0000 8 R
5680 REAVES0000 6 04/27/2006
5681 REAVES0000 7 Need Auth from 04/14/06 DOS (box checked: not listed)
5682 REAVES0000 8 R
5684 SMITH0003 6 04/27/2006
5685 SMITH0003 7 Need Auth from 04/14/06 DOS (box checked: not listed)
5686 SMITH0003 8 R

(602 row(s) affected)

|||

I failed to mention that the article above shows both the 2000 and 2005 syntax to accomplish the goal. Let me give it a shot with the data you provided:

Code Snippet

SELECT
DISTINCT T.CODE,
Eight.[Custom Field] as Eight,
Seven.[Custom Field] as Seven,
Six.[Custom Field] as Six
FROM
TEST t
LEFT OUTER JOIN (SELECT CODE, [Custom Field] FROM TEST where FieldID = 8) Eight ON t.Code = Eight.Code
LEFT OUTER JOIN (SELECT CODE, [Custom Field] FROM TEST where FieldID = 7) Seven ON t.Code = Seven.Code
LEFT OUTER JOIN (SELECT CODE, [Custom Field] FROM TEST where FieldID = 6) Six ON t.Code = Six.Code

where TEST is the name of the table I created to hold your data. It produces the following output:

CODE Eight Seven Six
-
05-3670 NULL auth expired 06/06/06 (3/10) 06/17/2006
05-3945 NULL Auth expired 08/21/06 (11/18) 09/07/2006
05-4256 C NULL NULL
COLE0001 C Need Invoice date & claim# 02/24/2006
HUNTER0000 R NULL 04/27/2006
REAVES0000 R NULL 04/27/2006
SMITH0003 R NULL 04/27/2006

(7 row(s) affected)

Please understand that I am not claiming this to be the best/most efficient way of handling your needs. My intuition tells me that there is a clever way to do this that I am overlooking.

Larry

|||

Yes, that works marvelously!

Just for my learning benefit, are the following statements essentially true?

The line "FROM dbo.[Customer Custom Field] AS t" is just declaring "t" to be a temporary table with all the available fields from dbo.[Customer Custom Field]?

"t.Code" is declaring 3 new separate tables (stripped from "t" to be separate tables where the LEFT OUTER JOINS recombine all fields into the resulting SQL data?

SELECT DISTINCT t.Code, Six.[Custom Field] AS Six, Seven.[Custom Field] AS Seven, Eight.[Custom Field] AS Eight

FROM dbo.[Customer Custom Field] AS t

LEFT OUTER JOIN

(SELECT Code, [Custom Field]

FROM dbo.[Customer Custom Field]

WHERE ([Field ID] = 6)) AS Six ON t.Code = Six.Code

LEFT OUTER JOIN

(SELECT Code, [Custom Field]

FROM dbo.[Customer Custom Field] AS [Customer Custom Field_2]

WHERE ([Field ID] = 7)) AS Seven ON t.Code = Seven.Code

LEFT OUTER JOIN

(SELECT Code, [Custom Field]

FROM dbo.[Customer Custom Field] AS [Customer Custom Field_1]

WHERE ([Field ID] = 8)) AS Eight ON t.Code = Eight.Code

WHERE (Six.[Custom Field] BETWEEN '04/01/2007' AND '07/15/2007')

When I constructed the query from your generous guide and tested it, It pulled the data the way I need it. Then I opened the SQL in the design editor and applied the daterange filter and it worked beautifully...

Thank you ever so much!

|||

The line "FROM dbo.[Customer Custom Field] AS t" is just declaring "t" to be a temporary table with all the available fields from dbo.[Customer Custom Field]?

Yes. It is setting up an alias. I would normally use something more descriptive or identifiable (ccf comes to mind) but there is only the one table so I got lazy.

"t.Code" is declaring 3 new separate tables (stripped from "t" to be separate tables where the LEFT OUTER JOINS recombine all fields into the resulting SQL data?

Not exactly. What we are doing is using the same table four times. Remove the distinct and put an * for the fields and you will see what I mean. We are joining the table to itself. The 'Left Outer' part says that all of the rows from the table to the left are to be in the resulting table, even if there isn't a corresponding row in the table on the right. I would suggest looking at Inside Microsoft SQL Server 2005: T-SQL Querying by Itzik Ben-Gan if you want to learn more about building complex queries.

You are very welcome. I am glad it worked out so well.

Larry

Cross Tab Query

I have a table called summary. This table contains calldate, projects, hours
.
I need to create a report/table that shows the date down the left column and
the projects across the top.
Date ProjectA ProjectB ProjectC
20060401 12 0 2
20060402 2 5 4
20060403 3 5 1
How can I accomplish this?
Any help would be greatly appreciated.

code:

CREATE TABLE [#TEST] (
[sCalldate] [varchar]((20) NULL ,
[sProject] [varchar] (20) NULL ,
[dHours] numeric (10,4) NULL)
INSERT [#TEST] (sCalldate,sProject, dHours)
VALUES ('20060401', 'A', 12)
INSERT [#TEST] (sCalldate,sProject, dHours)
VALUES ('20060401', 'B', 0)
INSERT [#TEST] (sCalldate,sProject, dHours)
VALUES ('20060401', 'C', 2)
INSERT [#TEST] (sCalldate,sProject, dHours)
VALUES ('20060402', 'A', 2)
INSERT [#TEST] (sCalldate,sProject, dHours)
VALUES ('20060402', 'B', 5)
INSERT [#TEST] (sCalldate,sProject, dHours)
VALUES ('20060402', 'C', 4)
INSERT [#TEST] (sCalldate,sProject, dHours)
VALUES ('20060403', 'A', 3)
INSERT [#TEST] (sCalldate,sProject, dHours)
VALUES ('20060403', 'B', 5)
INSERT [#TEST] (sCalldate,sProject, dHours)
VALUES ('20060403', 'C', 1)

Thanks,
Ninel
Message posted via http://www.webservertalk.comTry:
select
sCallDate
, sum (case when sProject = 'A' then dHours else 0 end) ProjectA
, sum (case when sProject = 'B' then dHours else 0 end) ProjectB
, sum (case when sProject = 'C' then dHours else 0 end) ProjectC
from
#TEST
group by
sCallDate
go
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"ngorbunov via webservertalk.com" <
u9125@.uwe>
wrote in message
news:5e3bc0ad31e3a@.uwe...
I have a table called summary. This table contains calldate, projects,
hours.
I need to create a report/table that shows the date down the left column and
the projects across the top.
Date ProjectA ProjectB ProjectC
20060401 12 0 2
20060402 2 5 4
20060403 3 5 1
How can I accomplish this?
Any help would be greatly appreciated.

code:

CREATE TABLE [#TEST] (
[sCalldate] [varchar]((20) NULL ,
[sProject] [varchar] (20) NULL ,
[dHours] numeric (10,4) NULL)
INSERT [#TEST] (sCalldate,sProject, dHours)
VALUES ('20060401', 'A', 12)
INSERT [#TEST] (sCalldate,sProject, dHours)
VALUES ('20060401', 'B', 0)
INSERT [#TEST] (sCalldate,sProject, dHours)
VALUES ('20060401', 'C', 2)
INSERT [#TEST] (sCalldate,sProject, dHours)
VALUES ('20060402', 'A', 2)
INSERT [#TEST] (sCalldate,sProject, dHours)
VALUES ('20060402', 'B', 5)
INSERT [#TEST] (sCalldate,sProject, dHours)
VALUES ('20060402', 'C', 4)
INSERT [#TEST] (sCalldate,sProject, dHours)
VALUES ('20060403', 'A', 3)
INSERT [#TEST] (sCalldate,sProject, dHours)
VALUES ('20060403', 'B', 5)
INSERT [#TEST] (sCalldate,sProject, dHours)
VALUES ('20060403', 'C', 1)

Thanks,
Ninel
Message posted via http://www.webservertalk.com|||--2000
SELECT
sCallDate
,SUM(CASE WHEN sProject = 'A' THEN dHours ELSE NULL END) AS ProjectA
,SUM(CASE WHEN sProject = 'B' THEN dHours ELSE NULL END) AS ProjectB
,SUM(CASE WHEN sProject = 'C' THEN dHours ELSE NULL END) AS ProjectC
FROM #TEST
GROUP BY sCallDate
--2005
SELECT sCallDate, A AS ProjectA, B AS ProjectB, C AS ProjectC
FROM #TEST
PIVOT(MAX(dHours) FOR sProject IN(A, B, C)) AS P
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"ngorbunov via webservertalk.com" <
u9125@.uwe>
wrote in message news:5e3bc0ad31e3a@.uwe...[color
=darkred]
>
I have a table called summary. This table contains calldate, projects, hour
s.
>
I need to create a report/table that shows the date down the left column a
nd
>
the projects across the top.
>
>
Date ProjectA ProjectB ProjectC
>
20060401 12 0 2
>
20060402 2 5 4
>
20060403 3 5 1
>
>
How can I accomplish this?
>
Any help would be greatly appreciated.
>
>

code:

>
 CREATE TABLE [#TEST] (
>
 [sCalldate] [varchar]((20) NULL ,
>
 [sProject] [varchar] (20) NULL ,
>
 [dHours] numeric (10,4) NULL)
>
>
 INSERT [#TEST] (sCalldate,sProject, dHours)
>
 VALUES ('20060401', 'A', 12)
>
>
 INSERT [#TEST] (sCalldate,sProject, dHours)
>
 VALUES ('20060401', 'B', 0)
>
>
 INSERT [#TEST] (sCalldate,sProject, dHours)
>
 VALUES ('20060401', 'C', 2)
>
>
 INSERT [#TEST] (sCalldate,sProject, dHours)
>
 VALUES ('20060402', 'A', 2)
>
>
 INSERT [#TEST] (sCalldate,sProject, dHours)
>
 VALUES ('20060402', 'B', 5)
>
>
 INSERT [#TEST] (sCalldate,sProject, dHours)
>
 VALUES ('20060402', 'C', 4)
>
>
 INSERT [#TEST] (sCalldate,sProject, dHours)
>
 VALUES ('20060403', 'A', 3)
>
>
 INSERT [#TEST] (sCalldate,sProject, dHours)
>
 VALUES ('20060403', 'B', 5)
>
>
 INSERT [#TEST] (sCalldate,sProject, dHours)
>
 VALUES ('20060403', 'C', 1)
>

>
>
Thanks,
>
Ninel
>
>
--
>
Message posted via http://www.webservertalk.com[/color]|||How can I do this without hardcoding the projects? New projects are added
almost everyday.
Tibor Karaszi wrote:
>--2000
>SELECT
> sCallDate
>,SUM(CASE WHEN sProject = 'A' THEN dHours ELSE NULL END) AS ProjectA
>,SUM(CASE WHEN sProject = 'B' THEN dHours ELSE NULL END) AS ProjectB
>,SUM(CASE WHEN sProject = 'C' THEN dHours ELSE NULL END) AS ProjectC
>FROM #TEST
>GROUP BY sCallDate
>--2005
>SELECT sCallDate, A AS ProjectA, B AS ProjectB, C AS ProjectC
>FROM #TEST
>PIVOT(MAX(dHours) FOR sProject IN(A, B, C)) AS P
>
>[quoted text clipped - 44 lines]
Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Forum...amming/200604/1|||SELECT SCALLDATE, SUM([PROJECT A]) AS [PROJECT A], SUM([PROJECT B]) AS
[PROJECT B], SUM([PROJECT C]) AS [PROJECT C]FROM
(
SELECT SCALLDATE,
CASE WHEN SPROJECT = 'A' THEN DHOURS ELSE 0 END AS [PROJECT A] ,
CASE WHEN SPROJECT = 'B' THEN DHOURS ELSE 0 END AS [PROJECT B] ,
CASE WHEN SPROJECT = 'C' THEN DHOURS ELSE 0 END AS [PROJECT C]
FROM #TEST
)AS A
GROUP BY SCALLDATE|||Unfortunately, you'd have to use dynamic SQL to generate the statement and
then execute the statement.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"ngorbunov via webservertalk.com" <u9125@.uwe> wrote in message
news:5e3bec27d6652@.uwe...
How can I do this without hardcoding the projects? New projects are added
almost everyday.
Tibor Karaszi wrote:
>--2000
>SELECT
> sCallDate
>,SUM(CASE WHEN sProject = 'A' THEN dHours ELSE NULL END) AS ProjectA
>,SUM(CASE WHEN sProject = 'B' THEN dHours ELSE NULL END) AS ProjectB
>,SUM(CASE WHEN sProject = 'C' THEN dHours ELSE NULL END) AS ProjectC
>FROM #TEST
>GROUP BY sCallDate
>--2005
>SELECT sCallDate, A AS ProjectA, B AS ProjectB, C AS ProjectC
>FROM #TEST
>PIVOT(MAX(dHours) FOR sProject IN(A, B, C)) AS P
>
>[quoted text clipped - 44 lines]
Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Forum...amming/200604/1|||Try this,,
select sCalldate, ProjectA = ISNULL ((SELECT dHours FROM #Test WHERE
sProject = 'A' AND sCalldate =
Q.sCallDate),0),
ProjectB = ISNULL ((SELECT dHours FROM #Test WHERE sProject = 'B' AND
sCalldate =
Q.sCallDate),0),
ProjectC = ISNULL ((SELECT dHours FROM #Test WHERE sProject = 'C' AND
sCalldate =
Q.sCallDate),0)
FROM #test Q
GROUP BY sCallDate
Ref : EN-US;
q175574" target="_blank">http://support.microsoft.com/defaul...b;
EN-US;
q175574
Thanks,
Sree
[Please specify the version of Sql Server as we can save one thread and time
asking back if its 2000 or 2005]
"ngorbunov via webservertalk.com" wrote:

>
I have a table called summary. This table contains calldate, projects, hou
rs.
>
I need to create a report/table that shows the date down the left column a
nd
>
the projects across the top.
>
>
Date ProjectA ProjectB ProjectC
>
20060401 12 0 2
>
20060402 2 5 4
>
20060403 3 5 1
>
>
How can I accomplish this?
>
Any help would be greatly appreciated.
>
>

code:

>
 CREATE TABLE [#TEST] (
>
 [sCalldate] [varchar]((20) NULL ,
>
 [sProject] [varchar] (20) NULL ,
>
 [dHours] numeric (10,4) NULL)
>
>
 INSERT [#TEST] (sCalldate,sProject, dHours)
>
 VALUES ('20060401', 'A', 12)
>
>
 INSERT [#TEST] (sCalldate,sProject, dHours)
>
 VALUES ('20060401', 'B', 0)
>
>
 INSERT [#TEST] (sCalldate,sProject, dHours)
>
 VALUES ('20060401', 'C', 2)
>
>
 INSERT [#TEST] (sCalldate,sProject, dHours)
>
 VALUES ('20060402', 'A', 2)
>
>
 INSERT [#TEST] (sCalldate,sProject, dHours)
>
 VALUES ('20060402', 'B', 5)
>
>
 INSERT [#TEST] (sCalldate,sProject, dHours)
>
 VALUES ('20060402', 'C', 4)
>
>
 INSERT [#TEST] (sCalldate,sProject, dHours)
>
 VALUES ('20060403', 'A', 3)
>
>
 INSERT [#TEST] (sCalldate,sProject, dHours)
>
 VALUES ('20060403', 'B', 5)
>
>
 INSERT [#TEST] (sCalldate,sProject, dHours)
>
 VALUES ('20060403', 'C', 1)
>

>
>
Thanks,
>
Ninel
>
>
--
>
Message posted via http://www.webservertalk.com
>
|||Thats really informative sql 2005 way of doing cross tab.
Thanks,
Sree
[Please specify the version of Sql Server as we can save one thread and time
asking back if its 2000 or 2005]
"Tibor Karaszi" wrote:

> --2000
> SELECT
> sCallDate
> ,SUM(CASE WHEN sProject = 'A' THEN dHours ELSE NULL END) AS ProjectA
> ,SUM(CASE WHEN sProject = 'B' THEN dHours ELSE NULL END) AS ProjectB
> ,SUM(CASE WHEN sProject = 'C' THEN dHours ELSE NULL END) AS ProjectC
> FROM #TEST
> GROUP BY sCallDate
> --2005
> SELECT sCallDate, A AS ProjectA, B AS ProjectB, C AS ProjectC
> FROM #TEST
> PIVOT(MAX(dHours) FOR sProject IN(A, B, C)) AS P
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "ngorbunov via webservertalk.com" <u9125@.uwe> wrote in message news:5e3bc0ad3
1e3a@.uwe...
>|||Hi,
Check out the RAC utility.It will easily do what you want and
will not insult your intelligence:)
www.rac4sql.net
"ngorbunov via webservertalk.com" <u9125@.uwe> wrote in message
news:5e3bec27d6652@.uwe...
> How can I do this without hardcoding the projects? New projects are added
> almost everyday.
> Tibor Karaszi wrote:
> --
> Message posted via webservertalk.com
> http://www.webservertalk.com/Uwe/Forum...amming/200604/1|||How much does it take for your intelligence to be insulted?
Over 5 years in development and an army to do it and this is what
they throw at users':)
"Sreejith G" <SreejithG@.discussions.microsoft.com> wrote in message
news:B53C964A-F511-451D-8597-BF21640130BA@.microsoft.com...
> Thats really informative sql 2005 way of doing cross tab.
> --
> Thanks,
> Sree
> [Please specify the version of Sql Server as we can save one thread and
> time
> asking back if its 2000 or 2005]
>
> "Tibor Karaszi" wrote:
>

Cross Tab Missing Column

Hi,

Have a bit of a strange problem here.

Am working with a colleague on a system which generates "simple" tables and cross tables, both using the same SQL views.

The "simple" tables show all the data correctly... When we come to the cross tabs however, something very weird happens. The x axis is for the section in the factory - for 14 out of the 15 sections the data appears perfectly, but for one column we get nothing, no data, no column heading, nothing. Looking at the info pulled back from the SQL views all the info is there as well.

If we change the data to point to a different section code, it quite happily appears on the report...

Anyone had anything similar, or any ideas as to what could be happening.

I have googled this and come up with nothing, have asked on another forum with no response - am rapidly pulling out what little hair I have left!Problem Solved...

Spelling error in the Select string....

Sorry !!!

Cross tab in crystal 8.5

Hi

I would like to know how to add column headings for the cols created in the Cross tab report in crystal 8.5. Most of the cross tab reports are come with horizontal headings as the fields in the database. If I add a few cols in the cross tab and want to have my own custom headings, how do I do that?. Your help is really appreciated.

thanks

ArunHi

It is difficult to change column heading, because the value of particular field is becoming the heading.
One solution is you can replace column field with a formula. Formula return appropriate value for each data in the field.

e.g

If {PLACE} = "Bombay" Then
"Mumbai"
Else If {PLACE} = "Maddras" Then
"Chenni"
Else
{PLACE}

Cross tab Crystal Report Problem ( Column Heading)

Dear all,

I need help with the version IX of Crystal Reports. i am using Cross
tab crystal report with Dynamically increasing columns, How it can be repeated the column-head in all the pages of a report that uses the across?

Right now alone with himself to put the column-head of the fields on
the first page and on the remainders does not leave...

i already searched more sites, most of them asked the same questions.., but no replies are there,

Any Suggestions or ideas to display the column heading in all pages...?

(In Cross tab Crystal Report)

Thanks & Regards,
Anbalagan. KI didn't get your problem, I think column labels repeats by itself where as u need to tick option to repeat row lables .Im working on CRXI|||hi sraheem,

yes i have already checked that "Repeat Row Labels" Checkbox,

any other ideas?....

Thanks & Regards,
Anbalagan. K|||my issue is i dont want the column names to be repeated as i am displaying in a single page. but i can see the col heading at page breaks.help me

Cross tab based on DateDIFF

I'm trying trying to build a crosstab query using a Case statement to count
the number of records in one column with the DateDiff >=0 and the other
column to be a count of the Records with the DateDiff <0. I can do this
easlily in MS Access with the IIF Function in a crosstab query but I can't
get the Sql Statement to work in an MS Sql 2000 View
Geoff,
Is this what you are after?
SELECT
SUM (CASE
WHEN Col1 >= 0 THEN 1
ELSE 0
END) AS NonNegative,
SUM (CASE
WHEN Col1 >= 0 THEN 0
ELSE 1
END) AS Negative
FROM YourTable
--RLF
"Geoff" <Geoff@.discussions.microsoft.com> wrote in message
news:253BF539-8139-4E63-B824-73440A6302E9@.microsoft.com...
> I'm trying trying to build a crosstab query using a Case statement to
> count
> the number of records in one column with the DateDiff >=0 and the other
> column to be a count of the Records with the DateDiff <0. I can do this
> easlily in MS Access with the IIF Function in a crosstab query but I can't
> get the Sql Statement to work in an MS Sql 2000 View
|||How do I put in the condition of wether the DateDiff is >= 0. I was trying
someting similiar to this view below but replacing the Count(CASE ACTCAT WHEN
N'X4' Then ACTCAT END with something to make one column count the number of
records where DATEDIFF(DD, dbo.JobLogTbl.JSTime, dbo.ContActivTbl.AddDate)
AS DATEDIFF >=0 and another column show the count if DATEDIFF(DD,
dbo.JobLogTbl.JSTime, dbo.ContActivTbl.AddDate) AS DATEDIFF < 0 in a view
SELECT TOP 100 PERCENT DATENAME(MM, dbo.JobLogTbl.JSTime) AS Month,
MONTH(dbo.JobLogTbl.JSTime) AS MoNum,
COUNT(CASE ACTCAT WHEN N'X4' THEN ACTCAT END) AS X4,
COUNT(CASE ACTCAT WHEN N'AG' THEN ACTCAT END) AS AG
FROM dbo.ContActivTbl INNER JOIN
dbo.JobLogTbl ON dbo.ContActivTbl.JobNo =
dbo.JobLogTbl.JobNo
WHERE (dbo.ContActivTbl.ActCat = 'X4') OR
(dbo.ContActivTbl.ActCat = 'AG') AND
(dbo.JobLogTbl.JSTime >= 01 / 01 / 07)
GROUP BY DATENAME(MM, dbo.JobLogTbl.JSTime), MONTH(dbo.JobLogTbl.JSTime)
ORDER BY MONTH(dbo.JobLogTbl.JSTime)
I can do this in MS Access with
TRANSFORM Count(ContActivTbl.JobNo) AS CountOfJobNo
SELECT Format([JSTime],"mmmm") AS [Month], Count(ContActivTbl.JobNo) AS
[Total Shipments], JobLogTbl.DLocNo
FROM ContActivTbl RIGHT JOIN JobLogTbl ON ContActivTbl.JobNo = JobLogTbl.JobNo
WHERE (((ContActivTbl.ActCat)="X4") AND ((JobLogTbl.JobStart)>#1/1/2007#)
AND ((JobLogTbl.JSTime) Is Not Null) AND ((JobLogTbl.SoType)="1") AND
((JobLogTbl.ShipStatus)<>"Cancelled"))
GROUP BY Month([JSTime]), Format([JSTime],"mmmm"), ContActivTbl.CoNo,
JobLogTbl.DLocNo, JobLogTbl.SoType, JobLogTbl.ShipStatus
ORDER BY Month([JSTime])
PIVOT IIf([JobLogTbl].JSTime>=[ContActivTbl].AddDate,"Before Arrival","After
Arrival");
"Russell Fields" wrote:

> Geoff,
> Is this what you are after?
> SELECT
> SUM (CASE
> WHEN Col1 >= 0 THEN 1
> ELSE 0
> END) AS NonNegative,
> SUM (CASE
> WHEN Col1 >= 0 THEN 0
> ELSE 1
> END) AS Negative
> FROM YourTable
> --RLF
> "Geoff" <Geoff@.discussions.microsoft.com> wrote in message
> news:253BF539-8139-4E63-B824-73440A6302E9@.microsoft.com...
>
>
|||Geoff,
If I am tracking you correctly then you would want something like:
SELECT
DATENAME(MM, dbo.JobLogTbl.JSTime) AS Month,
MONTH(dbo.JobLogTbl.JSTime) AS MoNum,
SUM (CASE
WHEN DATEDIFF(DAY,dbo.JobLogTbl.JSTime, GETDATE())>= 0 THEN 1
ELSE 0
END) AS NonNegative,
SUM (CASE
WHEN DATEDIFF(DAY,dbo.JobLogTbl.JSTime, GETDATE()) >= 0 THEN 0
ELSE 1
END) AS Negative
FROM YourTable
GROUP BY DATENAME(MM, dbo.JobLogTbl.JSTime),
MONTH(dbo.JobLogTbl.JSTime)
ORDER BY MONTH(dbo.JobLogTbl.JSTime)
I have not put all columns in, but I hope that this helps.
RLF
"Geoff" <Geoff@.discussions.microsoft.com> wrote in message
news:DC89754E-0A4C-4D78-B5C2-75F547C62D67@.microsoft.com...[vbcol=seagreen]
> How do I put in the condition of wether the DateDiff is >= 0. I was trying
> someting similiar to this view below but replacing the Count(CASE ACTCAT
> WHEN
> N'X4' Then ACTCAT END with something to make one column count the number
> of
> records where DATEDIFF(DD, dbo.JobLogTbl.JSTime,
> dbo.ContActivTbl.AddDate)
> AS DATEDIFF >=0 and another column show the count if DATEDIFF(DD,
> dbo.JobLogTbl.JSTime, dbo.ContActivTbl.AddDate) AS DATEDIFF < 0 in a view
>
>
> SELECT TOP 100 PERCENT DATENAME(MM, dbo.JobLogTbl.JSTime) AS Month,
> MONTH(dbo.JobLogTbl.JSTime) AS MoNum,
> COUNT(CASE ACTCAT WHEN N'X4' THEN ACTCAT END) AS X4,
> COUNT(CASE ACTCAT WHEN N'AG' THEN ACTCAT END) AS AG
> FROM dbo.ContActivTbl INNER JOIN
> dbo.JobLogTbl ON dbo.ContActivTbl.JobNo =
> dbo.JobLogTbl.JobNo
> WHERE (dbo.ContActivTbl.ActCat = 'X4') OR
> (dbo.ContActivTbl.ActCat = 'AG') AND
> (dbo.JobLogTbl.JSTime >= 01 / 01 / 07)
> GROUP BY DATENAME(MM, dbo.JobLogTbl.JSTime), MONTH(dbo.JobLogTbl.JSTime)
> ORDER BY MONTH(dbo.JobLogTbl.JSTime)
> I can do this in MS Access with
> TRANSFORM Count(ContActivTbl.JobNo) AS CountOfJobNo
> SELECT Format([JSTime],"mmmm") AS [Month], Count(ContActivTbl.JobNo) AS
> [Total Shipments], JobLogTbl.DLocNo
> FROM ContActivTbl RIGHT JOIN JobLogTbl ON ContActivTbl.JobNo =
> JobLogTbl.JobNo
> WHERE (((ContActivTbl.ActCat)="X4") AND ((JobLogTbl.JobStart)>#1/1/2007#)
> AND ((JobLogTbl.JSTime) Is Not Null) AND ((JobLogTbl.SoType)="1") AND
> ((JobLogTbl.ShipStatus)<>"Cancelled"))
> GROUP BY Month([JSTime]), Format([JSTime],"mmmm"), ContActivTbl.CoNo,
> JobLogTbl.DLocNo, JobLogTbl.SoType, JobLogTbl.ShipStatus
> ORDER BY Month([JSTime])
> PIVOT IIf([JobLogTbl].JSTime>=[ContActivTbl].AddDate,"Before
> Arrival","After
> Arrival");
> "Russell Fields" wrote:

Cross Row Referential Integrity

In SS05 XML typed columns, is it possible to have cross row constraints and referential integrity?

Say I have a column with two schemas: Author and Book. Each has an ID. Each row in the table has only one author or book. Can I enforce that all Author ID are unique across all rows and that there is an author for every book?

Is it possible now? If yes, how? If no, is there a plan to add this feature in the future? Is there any work around now?

I am appending the script to demonstrate what I want to achieve.

Thanks in advance.

-

USE [pubs]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

--Drop the table
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[MyXMLTable]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[MyXMLTable]
GO

if exists (select * from sys.xml_schema_collections where name = N'XmlSchemaCollection')
DROP XML SCHEMA COLLECTION XmlSchemaCollection
GO

--Create the schema
CREATE XML SCHEMA COLLECTION XmlSchemaCollection AS
N'<?xml version="1.0" encoding="UTF-16"?>
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema"
targetNamespace="http://www.test.com/XmlTest"
xmlns ="http://www.test.com/XmlTest"
elementFormDefault="qualified"
attributeFormDefault="unqualified" >

<xs:element name="Author">
<xs:complexType>
<xs:sequence>
<xs:element name="auId" type="xs:long" />
<xs:element name="auName" type="xs:string" minOccurs="0" />
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:schema>

<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema"
targetNamespace="http://www.test.com/XmlTest"
xmlns ="http://www.test.com/XmlTest"
elementFormDefault="qualified"
attributeFormDefault="unqualified" >

<xs:element name="Book">
<xs:complexType>
<xs:sequence>
<xs:element name="bookId" type="xs:long" />
<xs:element name="title" type="xs:string" minOccurs="0" />
<xs:element name="bookAuID" type="xs:long"/>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:schema>' ;
GO

--Create the table
CREATE TABLE [dbo].[MyXMLTable](
i int primary key,
recType varchar(max),
[XMLData] xml (XmlSchemaCollection)
) ON [PRIMARY]
GO

DECLARE @.s varchar(2048)

-- insert records into Author table
SET @.s = '<xns:Author xmlns:xns="http://www.test.com/XmlTest">
<xns:auId>1</xns:auId><xns:auName>Tom</xns:auName></xns:Author>'
INSERT INTO [dbo].[MyXMLTable] VALUES (1, 'Author', @.s)
SET @.s = '<xns:Author xmlns:xns="http://www.test.com/XmlTest">
<xns:auId>2</xns:auId><xns:auName>Dick</xns:auName></xns:Author>'
INSERT INTO [dbo].[MyXMLTable] VALUES (2, 'Author', @.s)
SET @.s = '<xns:Author xmlns:xns="http://www.test.com/XmlTest">
<xns:auId>3</xns:auId><xns:auName>Harry</xns:auName></xns:Author>'
INSERT INTO [dbo].[MyXMLTable] VALUES (3, 'Author', @.s)

-- insert records into Book table
SET @.s = '<xns:Book xmlns:xns="http://www.test.com/XmlTest">
<xns:bookId>1</xns:bookId>
<xns:title>Butterflies</xns:title>
<xns:bookAuID>1</xns:bookAuID>
</xns:Book>'
INSERT INTO [dbo].[MyXMLTable] VALUES (9, 'Book', @.s)
SET @.s = '<xns:Book xmlns:xns="http://www.test.com/XmlTest">
<xns:bookId>2</xns:bookId>
<xns:title>Tigers</xns:title>
<xns:bookAuID>3</xns:bookAuID>
</xns:Book>'
INSERT INTO [dbo].[MyXMLTable] VALUES (10, 'Book', @.s)
SET @.s = '<xns:Book xmlns:xns="http://www.test.com/XmlTest">
<xns:bookId>3</xns:bookId>
<xns:title>Elephants</xns:title>
<xns:bookAuID>2</xns:bookAuID>
</xns:Book>'
INSERT INTO [dbo].[MyXMLTable] VALUES (11, 'Book', @.s)
SET @.s = '<xns:Book xmlns:xns="http://www.test.com/XmlTest">
<xns:bookId>4</xns:bookId>
<xns:title>Eagles</xns:title>
<xns:bookAuID>3</xns:bookAuID>
</xns:Book>'
INSERT INTO [dbo].[MyXMLTable] VALUES (12, 'Book', @.s);

-- Table View with a join
WITH XMLNAMESPACES ('http://www.test.com/XmlTest' AS xns)
SELECT A.i, A.[XMLData].value('
(/xns:Book/xns:title)[1]', 'nvarchar(max)') as Title,
B.[XMLData].value('
(/xns:Author/xns:auName)[1]', 'nvarchar(max)') as [Author Name]
FROM dbo.MyXMLTable A inner join dbo.MyXMLTable B on
A.[XMLData].value('(/xns:Book/xns:bookAuID)[1]', 'int') =
B.[XMLData].value('(/xns:Author/xns:auId)[1]', 'int')
WHERE (B.[XMLData].exist('/xns:Author[xns:auId=3]')=1)

This may work, I haven't tried it:

Create a persisted computed column which promotes out the author id as the primary key for the table, and another persisted computed column which promotes out the bookId. Then create a 1:n PK-FK relationship between those two computed columns.

To create the computed columns you will have to create a UDF which wraps the XQuery invocation. SQL Server 2005 does not support XQuery directly in comptued column definitions or check constraints.

|||? Why do you want to put authors and books in the same column? This violates basic relational modeling fundamentals; I'd be very interested in knowing what you feel you'd gain by doing something like that. -- Adam MachanicPro SQL Server 2005, available nowhttp://www..apress.com/book/bookDisplay.html?bID=457-- <Hemant K@.discussions.microsoft.com> wrote in message news:d06f449c-15af-4a81-96a9-f32a06aa5366@.discussions.microsoft.com... In SS05 XML typed columns, is it possible to have cross row constraints and referential integrity? Say I have a column with two schemas: Author and Book. Each has an ID. Each row in the table has only one author or book. Can I enforce that all Author ID are unique across all rows and that there is an author for every book? Is it possible now? If yes, how? If no, is there a plan to add this feature in the future? Is there any work around now? I am appending the script to demonstrate what I want to achieve. Thanks in advance. - USE [pubs]GO SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO --Drop the tableif exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[MyXMLTable]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)drop table [dbo].[MyXMLTable]GO if exists (select * from sys.xml_schema_collections where name = N'XmlSchemaCollection')DROP XML SCHEMA COLLECTION XmlSchemaCollection GO --Create the schemaCREATE XML SCHEMA COLLECTION XmlSchemaCollection AS N'<?xml version="1.0" encoding="UTF-16"?><xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema"targetNamespace="http://www.test.com/XmlTest" xmlns ="http://www.test.com/XmlTest" elementFormDefault="qualified" attributeFormDefault="unqualified" > <xs:element name="Author"><xs:complexType><xs:sequence><xs:element name="auId" type="xs:long" /><xs:element name="auName" type="xs:string" minOccurs="0" /></xs:sequence></xs:complexType></xs:element></xs:schema> <xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema"targetNamespace="http://www.test.com/XmlTest" xmlns ="http://www.test.com/XmlTest" elementFormDefault="qualified" attributeFormDefault="unqualified" > <xs:element name="Book"><xs:complexType><xs:sequence><xs:element name="bookId" type="xs:long" /><xs:element name="title" type="xs:string" minOccurs="0" /><xs:element name="bookAuID" type="xs:long"/></xs:sequence></xs:complexType></xs:element></xs:schema>' ;GO --Create the tableCREATE TABLE [dbo].[MyXMLTable](i int primary key, recType varchar(max),[XMLData] xml (XmlSchemaCollection)) ON [PRIMARY]GO DECLARE @.s varchar(2048) -- insert records into Author tableSET @.s = '<xns:Author xmlns:xns="http://www.test.com/XmlTest"><xns:auId>1</xns:auId><xns:auName>Tom</xns:auName></xns:Author>'INSERT INTO [dbo].[MyXMLTable] VALUES (1, 'Author', @.s)SET @.s = '<xns:Author xmlns:xns="http://www.test.com/XmlTest"><xns:auId>2</xns:auId><xns:auName>Dick</xns:auName></xns:Author>'INSERT INTO [dbo].[MyXMLTable] VALUES (2, 'Author', @.s)SET @.s = '<xns:Author xmlns:xns="http://www.test.com/XmlTest"><xns:auId>3</xns:auId><xns:auName>Harry</xns:auName></xns:Author>'INSERT INTO [dbo].[MyXMLTable] VALUES (3, 'Author', @.s) -- insert records into Book tableSET @.s = '<xns:Book xmlns:xns="http://www.test.com/XmlTest"><xns:bookId>1</xns:bookId><xns:title>Butterflies</xns:title> <xns:bookAuID>1</xns:bookAuID></xns:Book>'INSERT INTO [dbo].[MyXMLTable] VALUES (9, 'Book', @.s)SET @.s = '<xns:Book xmlns:xns="http://www.test.com/XmlTest"><xns:bookId>2</xns:bookId><xns:title>Tigers</xns:title> <xns:bookAuID>3</xns:bookAuID></xns:Book>'INSERT INTO [dbo].[MyXMLTable] VALUES (10, 'Book', @.s)SET @.s = '<xns:Book xmlns:xns="http://www.test.com/XmlTest"><xns:bookId>3</xns:bookId><xns:title>Elephants</xns:title> <xns:bookAuID>2</xns:bookAuID></xns:Book>'INSERT INTO [dbo].[MyXMLTable] VALUES (11, 'Book', @.s)SET @.s = '<xns:Book xmlns:xns="http://www.test.com/XmlTest"><xns:bookId>4</xns:bookId><xns:title>Eagles</xns:title> <xns:bookAuID>3</xns:bookAuID></xns:Book>'INSERT INTO [dbo].[MyXMLTable] VALUES (12, 'Book', @.s); -- Table View with a joinWITH XMLNAMESPACES ('http://www.test.com/XmlTest' AS xns)SELECT A.i, A.[XMLData].value('(/xns:Book/xns:title)[1]', 'nvarchar(max)') as Title,B.[XMLData].value('(/xns:Author/xns:auName)[1]', 'nvarchar(max)') as [Author Name]FROM dbo.MyXMLTable A inner join dbo.MyXMLTable B on A.[XMLData].value('(/xns:Book/xns:bookAuID)[1]', 'int') =B.[XMLData].value('(/xns:Author/xns:auId)[1]', 'int')WHERE (B.[XMLData].exist('/xns:Author[xns:auId=3]')=1)|||

Well, First I wanted to have a constraint that works across all the rows e.g. all books in all rows have unique book id and all authors in all rows have unique author id.

Then, since XML type is a schema collection, it can hold both authors and books in the same column to make my table generic. So, I thought may be I can extend the concept and create a PK-FK relationship between authors/books.

|||

Thank you John.

I was hoping that SQL Server 2005 and XML provide some support for cross-row constrains.