Crystal Reports help!

I am connecting to an AS400 via ODBC using Crystal Reports 9.0. I have a date that is coming out in a 7 digit number format. I need a formula for Crystal Reports that will convert this to a date format.

For example 1051111 would return 11/11/05.

The formula will have to get rid of the first "1" and put the other numbers in the correct format.

If someone can provide me with this formula and where I need to put it in Crystal Reports V9.0 that would be great.

ThanksSomething like this should do it. Just place it in your detail section.

StringVar ConvertDate := Right (ToText({your_number_field),6 );
StringVar YY := ConvertDate [1 to 2];
StringVar MM := ConvertDate [3 to 4];
StringVar DD := ConvertDate [4 to 5];

DD & "/" & MM & "/" & YY;

Brian
http://www.briankuipers.com|||Thanks alot, that helped a little. Except I told you wrong. The original format actually comes down as 1,051,111.00 with the commas and the decimal point.

When I use your formula it is counting the commas as text. I need a formula that would pull out the commas and decimal points and just use the text string of 1051111 that I originally stated was being used.

Thanks

Crystal Reports 7 using wrong date format

Whenever one of my users prints any of our reports, the date is in dd/mm/yyyy format, when it should be in mm/dd/yyyy format. This might be more of a Windows settings issue rather than a CR7 issue, but it only seems to do this when a CR7 report is printed, anything else involving a date in any other software prints/displays correctly. I have checked the regional settings on their computer and they are all set properly. Uninstalling and reinstalling the CR7 runtime files didn't seem to help either.

Does anyone know if CR7 stores its own date formatting information? Thank you in advance.Use Field Formatting to set the format of the date. Else you can covert the date into a string also.

In case you need to pass date as a parameter, Crystal Reports allows you to pass the date in three pieces (Date, Month and Year).

Hope you will solve the problem.

Kangkan

<a href="http://links.10026.com/?link=http://www.geekays.net">geekays.net</a>sql

crystal reports

how can i design the crystal reports in windows application using C#.
what code i should write to get all the fields in a report format which should be generated into a crystal report.Visit www.VBCity.com and search in Crystal Reports section|||As you specified on that topic, Doevents would do that. You need to write it twice or thrice

Doevents
Doevents
Doevents

Crystal Report & PDF Format

hello
I need 10-15 reports on one button click from the database, all the reports should be print in a PDF Format. so pl. tell how i can do this in dot net envoirnment.
ASAP
Shoaib ShaikhHave you tried doing a search on this board? There are lots of examples. I have posted several for VB 6 and CR 8.5 which you might be able to tweak a bit to use with .NET.

Also, look at Crystal Report's Search (http://support.businessobjects.com/search/advsearch.asp) or Crystal Report's Forum (http://support.businessobjects.com/forums/default.asp).

Crystal Report

i'm facing one problem with crystal report. i've one database in following format.

PCode p1 p2 p3
------------------
aaa 876 54 98
bbb 46 878 98
ccc 769 980 676

i need this data in my report with the following format.

p1 876
p2 54
p3 98
if i select the PCode 'aaa'.

Note:
--
in my table the fields can be increase at any time. like p4,p5,p6,etc...

So could anyone help me to solve this problem.Hi,
Dont put the headers p1,p2 and p3 in the report header. Put them in the details section

Madhivanan

Crosstab result

Hi there,

I'm using sql2005. I've got a table in tabular form and I need to convert it to crosstab format. I've tried to use pivot operator but i can't get the result i want. Because pivot operator needs to aggregate a field but what i need is Dim values as column names and DimCode as values within those columns as it is, no aggregations.

For example : This is tabular table.

Dim DimCode JrnlEntry GLDistId TrxDate GLPostDT TrxType AccountIndex DebitAmt CreditAmt Amount 4 2 54222 1 25/05/2007 25/05/2007 1 44256 617 0 617 4 11991 54222 1 25/05/2007 25/05/2007 1 44256 617 0 617 5 7 54222 1 25/05/2007 25/05/2007 1 44256 617 0 617 5 5 54222 1 25/05/2007 25/05/2007 1 44256 617 0 617 4 1 54225 1 31/05/2007 31/05/2007 1 44256 600 0 600 5 5 54225 1 31/05/2007 31/05/2007 1 44256 600 0 600 4 12821 54225 2 31/05/2007 31/05/2007 1 44256 400 0 400 5 4 54225 2 31/05/2007 31/05/2007 1 44256 400 0 400 4 3 54227 3 31/05/2007 31/05/2007 1 44256 0 500 -500 5 8 54227 3 31/05/2007 31/05/2007 1 44256 0 500 -500

And i want the end result as under..

jrnentry aaGLDistID TRXDATE GLPOSTDT aaTRXType Dim1 Dim2 Dim3 Dim4 Dim5 Dim6 Dim7 Dim8 Dim9 Dim10 ACTINDX DEBITAMT CRDTAMNT Amount 54222 1 25/05/2007 25/05/2007 1 0 0 0 2 7 0 0 0 0 0 44256 617 0 617 54222 1 25/05/2007 25/05/2007 1 0 0 0 11991 5 0 0 0 0 0 44256 617 0 617 54225 1 31/05/2007 31/05/2007 1 0 0 0 1 5 0 0 0 0 0 44256 600 0 600 54225 2 31/05/2007 31/05/2007 1 0 0 0 12821 4 0 0 0 0 0 44256 400 0 400 54227 3 31/05/2007 31/05/2007 1 0 0 0 3 8 0 0 0 0 0 44256 0 500 -500

Your help will much appreciated.

There is an excellent article about PIVOT by Peter Larsson.

Follow the link :

Pivot table for Microsoft SQL Server

Thanks

Naras.

|||

For quite a few examples of using PIVOT, do a search on this Forum for the keyword 'Pivot'. Amazing how often this question is asked, and how often folks don't seem to bother looking in the archives to find their answer.

|||

Thanks Naras,

I'm not looking for help in Pivot functionality of sql2005 as it won't be usefull for me. If you look at the first table, i want the first column Dim to be used for the new columns i.e. Dim1, Dim2, Dim3 will be columns in the crosstab result but then i want DimCode to appear under respective columns without any aggregations. In pivot you've to use aggregate function. Also in crosstab result if look at the record with jrnentry 54222, there are two lines with the same aaGLDistId i.e. i don't want to aggregate values within dim1, dim2 etc.

Thanks

Vivek

|||

Vivek,

You are wrong. You can achive it using the PIVOT Operator. See the below query..

Code Snippet

Create Table #data (

[Dim] int ,

[DimCode] int ,

[JrnlEntry] int ,

[GLDistId] int ,

[TrxDate] datetime ,

[GLPostDT] datetime ,

[TrxType] int ,

[AccountIndex] int ,

[DebitAmt] int ,

[CreditAmt] int ,

[Amount] int

);

Set Dateformat DMY

Insert Into #data Values('4','2','54222','1','25/05/2007','25/05/2007','1','44256','617','0','617');

Insert Into #data Values('4','11991','54222','1','25/05/2007','25/05/2007','1','44256','617','0','617');

Insert Into #data Values('5','7','54222','1','25/05/2007','25/05/2007','1','44256','617','0','617');

Insert Into #data Values('5','5','54222','1','25/05/2007','25/05/2007','1','44256','617','0','617');

Insert Into #data Values('4','1','54225','1','31/05/2007','31/05/2007','1','44256','600','0','600');

Insert Into #data Values('5','5','54225','1','31/05/2007','31/05/2007','1','44256','600','0','600');

Insert Into #data Values('4','12821','54225','2','31/05/2007','31/05/2007','1','44256','400','0','400');

Insert Into #data Values('5','4','54225','2','31/05/2007','31/05/2007','1','44256','400','0','400');

Insert Into #data Values('4','3','54227','3','31/05/2007','31/05/2007','1','44256','0','500','-500');

Insert Into #data Values('5','8','54227','3','31/05/2007','31/05/2007','1','44256','0','500','-500');

Select

[JrnlEntry]

,[GLDistId]

,[TrxDate]

,[GLPostDT]

,[TrxType]

,Isnull([1],0) Dim1

,Isnull([2],0) Dim2

,Isnull([3],0) Dim3

,Isnull([4],0) Dim4

,Isnull([5],0) Dim5

,Isnull([6],0) Dim6

,Isnull([7],0) Dim7

,Isnull([8],0) Dim8

,Isnull([9],0) Dim9

,Isnull([10],0) Dim10

,[AccountIndex]

,[DebitAmt]

,[CreditAmt]

,[Amount] From

(

Select

[Dim]

,[DimCode]

,[GLDistId]

,[JrnlEntry]

,[TrxDate]

,[GLPostDT]

,[TrxType]

,[AccountIndex]

,[DebitAmt]

,[CreditAmt]

,[Amount]

,Row_Number() Over (Partition By [Dim],[GLDistId],[JrnlEntry] Order By [Dim],[GLDistId],[JrnlEntry]) RowId

From

#data

) as Data

Pivot

(

Max([DimCode])

For [Dim] in

(

[1],[2],[3],[4],[5],[6],[7],[8],[9],[10]

)

) As PVT

|||

Thanks Manivannan, much appreciated. Its returning correct result. I almost spent the whole day on this yesterday with frustration.

Regards,

Vivek

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 Qry with dynamic field names

Hi,

I am trying to create a stored procedures (SQL 7.0), to provide data
in
a crosstab format.
(I'm using Crystal Reports 8.5, but the Crosstab capabilities are
terrible, so I have to do as much as possible on the SQL side)

I have a table [Occurrences] with the following fields:
Year (int)
Month (int)
Occurs (int)
Claims (int)

I need a query to give me the following format:
Acct_Month 2001 2002 2003
Occurs Claims Occurs Claims Occurs Claims

January 120 180 132 196 110 140
February 154 210 165 202 144 178
March etc..
....

Catch! I need the Year field name to be the contents of the field
Year in the Table (2001, 2002, 2003...). Not the usual Year_1, Year_2
approach.

I got the month name ok...
Acct_Month = DATENAME(month, Convert(Varchar(2), Month) + '/01/'+
Convert(Char(4),Year))

Is it possible to do this easely, without the use of cursors?

Any help would be much appreciated.

Luis PintoLuis (luispinto@.att.net) writes:
> I need a query to give me the following format:
> Acct_Month 2001 2002 2003
> Occurs Claims Occurs Claims Occurs Claims
> January 120 180 132 196 110 140
> February 154 210 165 202 144 178
> March etc..
> ...
> Catch! I need the Year field name to be the contents of the field
> Year in the Table (2001, 2002, 2003...). Not the usual Year_1, Year_2
> approach.
> I got the month name ok...
> Acct_Month = DATENAME(month, Convert(Varchar(2), Month) + '/01/'+
> Convert(Char(4),Year))
> Is it possible to do this easely, without the use of cursors?

It takes a whole of dynamic SQL to get there.

You should probably investigate what RAC can do for you, see
http://www.rac4sql.net/.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

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

Cross-database Ownership Chaining in SQL 2005

This is a multi-part message in MIME format.
--=_NextPart_000_0010_01C5DBA4.D8BB4C00
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Hi,
Sorry to post in this group. It seems only few people in SQL 2005 = newsgroup now.
I met a problem in SQL SERVER 2005 SEPTEMBER CTP. The option "Cross Database Ownership Chaining" won't work in SQL 2005 Step CTP. But there = is no problem if I call stored procedure to access a table in the sample = database--AdventureWorks. for example:
use msdb
go
drop proc sp_my_test
go
create proc sp_my_test
as
select * from AdventureWorks.Person.Address
--select * from pubs.dbo.test2
go
grant exec on sp_my_test to wdw
go
"wdw" is a normal account with minimal permission. When I called = sp_my_test I got the data return.
But if I try to call a stored procedure to access a table in a new user = database I got error: for example:
use msdb
go
drop proc sp_my_test
go
create proc sp_my_test
as
select * from pubs.dbo.test2
go
grant exec on sp_my_test to wdw
go
"wdw" is the same account with minimal permission. Pubs database and = test2 table are created by sa account.
When I call sp_my_test I met permission denied error:
The error is:
Msg 229, Level 14, State 5, Procedure my_test, Line 4
SELECT permission denied on object 'test2', database 'pubs', schema = 'dbo'.
I changed the server and database option for Cross Database Ownership Chaining. Neither works in my database but it DOES work for = AdventureWorks. Even I disable this option at server and database level it still works for = AdventureWorks database. Can you do this simple test on your SQL 2005 = and post your result?
Thanks for any help!
Bill
--=_NextPart_000_0010_01C5DBA4.D8BB4C00
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
Hi,
Sorry to post in this group. It = seems only few people in SQL 2005 newsgroup now.
I met a problem in SQL SERVER 2005 SEPTEMBER CTP. The option "Cross Database Ownership Chaining" won't work in SQL 2005 Step CTP. But = there is no problem if I call stored procedure to access a table in the = sample database--AdventureWorks. for example:
use msdbgodrop proc = sp_my_testgocreate proc sp_my_testasselect * from = AdventureWorks.Person.Address--select * from pubs.dbo.test2gogrant exec on sp_my_test to = wdwgo
"wdw" is a normal account with minimal permission. When I called sp_my_test I got the data return.But if I try to call a stored procedure to access a table in a new user database I got error: for example:use msdbgodrop proc = sp_my_testgocreate proc sp_my_testasselect * from pubs.dbo.test2gogrant exec on sp_my_test to wdwgo
"wdw" is the same account with = minimal permission. Pubs database and test2 table are created by sa account.
When I call sp_my_test I met permission = denied error:
The error is:Msg 229, Level 14, State 5, Procedure = my_test, Line 4SELECT permission denied on object 'test2', database 'pubs', schema = 'dbo'.I changed the server and database option for Cross = Database Ownership Chaining. Neither works in my database but it DOES = work for AdventureWorks. Even I disable this option at server and = database level it still works for AdventureWorks database. Can you do this simple test = on your SQL 2005 and post your result?Thanks for any = help!Bill
--=_NextPart_000_0010_01C5DBA4.D8BB4C00--In order for the cross-database chain to be unbroken for schema owned by
'dbo' users, the databases need to be owned by the same login. The msdb
system database is owned by 'sa' so I would expect your test to work if you
change the owner of your 'pubs' database to 'sa' and turn on the DB_CHAINING
database option:
ALTER AUTHORIZATION ON DATABASE::pubs
TO sa
ALTER DATABASE pubs
SET DB_CHAINING ON
Hope this helps.
Dan Guzman
SQL Server MVP
"Bill Wang" <wdw2130833@.hotmail.com> wrote in message
news:%23BO$hZ82FHA.3136@.TK2MSFTNGP09.phx.gbl...
Hi,
Sorry to post in this group. It seems only few people in SQL 2005 newsgroup
now.
I met a problem in SQL SERVER 2005 SEPTEMBER CTP. The option "Cross
Database Ownership Chaining" won't work in SQL 2005 Step CTP. But there is
no problem if I call stored procedure to access a table in the sample
database--AdventureWorks. for
example:
use msdb
go
drop proc sp_my_test
go
create proc sp_my_test
as
select * from AdventureWorks.Person.Address
--select * from pubs.dbo.test2
go
grant exec on sp_my_test to wdw
go
"wdw" is a normal account with minimal permission. When I called sp_my_test
I got the data return.
But if I try to call a stored procedure to access a table in a new user
database I got error: for
example:
use msdb
go
drop proc sp_my_test
go
create proc sp_my_test
as
select * from pubs.dbo.test2
go
grant exec on sp_my_test to wdw
go
"wdw" is the same account with minimal permission. Pubs database and test2
table are created by sa account.
When I call sp_my_test I met permission denied error:
The error is:
Msg 229, Level 14, State 5, Procedure my_test, Line 4
SELECT permission denied on object 'test2', database 'pubs', schema 'dbo'.
I changed the server and database option for Cross Database Ownership
Chaining. Neither works in my database but it DOES work for AdventureWorks.
Even I
disable this option at server and database level it still works for
AdventureWorks database. Can you do this simple test on your SQL 2005 and
post your result?
Thanks for any help!
Bill

Cross Tab?

I need to format the following tables:

tbEmployees
EmployeeID | fName | lName
--------------
jdoe | Joe | Doe
bsmith | Blake | Smith

tbDepartments
DepartmentID | Department
----------
ENG | Engineering
DET | Detailing

tbDepartmentEmployees
fkEmployee ID | fkEmployeeID
----------
ENG | jdoe
DET | bsmith

tbProjects
ProjectID |
-----
1001

tbProjectTeam
fkProjectID | fkEmployeeID | fkDepartmentID
--------------
1001 | jdoe | ENG
1001 | bsmith | DET

To the following view :

vProjects
ProjectID | Engineer | Detailer
------------
1001 | Joe Doe | Blake Smith

Any Ideas?
Mike BSQL server does not have built-in crosstab querys as Access does. You can replicate a cross-tab query using CASE statements. It looks intimidating at first, but it actually pretty straight-forward. Look up Crosstab in Books Online for a good explanation and example.