Crystal Show SQL Query Question

I had a database named X. I renamed it to Y.

My report is mapped to a view named viewX.

After I changed database names, and I choose Show SQL Query in crystal reports, my query from clause says

FROM X.DBO.VIEWX

I want it to say Y.DBO.VIEWX since i changed database names. Is there any easy way to do this. Or is it possible to just eliminate the DB name from the SQL from clause.

thanks,

deep.Open the Report and Do verify databasesql

Crosstab woes

Good day all.

I'm using crystal reports 9.

I have a list of names down the left (sDriverName) and the months across the top (liMonth). The summarised field is the Driver score (liScore). There is another database field called liBandColour which indicates what colour band the driver's score is for that month (red, amber, green).

I have a formula on the Background colour of the liScore field (Basic syntax):
if {Rag.liBandColour} =255 then
formula = crRed 'Red
elseif {Rag.liBandColour} =65535 then
formula = rgb(255,255,0) 'Amber
elseif {Rag.liBandColour} =13434828 then
formula = crGreen 'Green
else
formula = crWhite 'White
end if

However this seems to make ALL the scores appear amber and doesn't seem to work on a per record basis.

On to question 2:
The user has now requested to add another column on the right with the YTD kms travelled. Now it's a simple thing to add another summarised field and suppress it (the DB also contains the kms travelled each month so this is easy), but then it appears underneath the averaged driver score total and not next to it in it's own column... Is there anyway around this?
Can I perhaps duplicate the look of my report without using a crosstabFor your first problem, I came thru something like that in CrystalDecisions.com

visit this url (http://support.businessobjects.com/library/kbase/articles/c2006837.asp)

For your second problem, it may not. any way

have a look at here (http://support.businessobjects.com/library/kbase/articles/c2006237.asp)

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