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

Crystal Reports 8.5/ASP 3.0 - report not shown in browser

Hi

I am trying to run a web application which is done using asp 3.0 which uses crystal reports 8.5 for reporting. I am not able to view the report in the browser, it is just showing the Crystal reports viewer control.

Please help me in trobleshooting this...

Thanks,
R.K.T. AshokaAnybody can answer this ?

Hi

I am trying to run a web application which is done using asp 3.0 which uses crystal reports 8.5 for reporting. I am not able to view the report in the browser, it is just showing the Crystal reports viewer control.

Please help me in trobleshooting this...

Thanks,
R.K.T. Ashokasql

Crystal Reports - Formula

Hi everyone
I really need to get this done, please try to help me.
I'll appreciate any help i get.

I have a View in my SQL Server DB which has following data.
--------------------------
cardtype | totaltype | amount
--------------------------
type1 | R16-Balance | 12000.50
type1 | R32-Balance | 15000.70
type2 | R16-Balance | 18000.50
type2 | R32-Balance | 19450.60
type3 | R16-Balance | 12500.50
type3 | R32-Balance | 18550.80
[hr]

I am trying to create a report in which i have grouped this data by Cardtype, Now the question is I want the difference between the amounts for the same card type. for Example :-
I want my report to look like following:
_____________________________________________________________
type1 R32-Balance 15000.70
type1 R16-Balance 12000.50
--- ------ ----
Outbalance:- 3000.20

type2 R32-Balance 19450.60
type2 R16-Balance 18000.50
--- ----- ----
Outbalance:- 1450.10

type3 R32-Balance 18550.80
type3 R16-Balance 12500.50
---------- ----
Outbalance:- 6050.30
____________________________________________________________

Thanks. Please Look into itHi ..
Group by card type.
Place cardtype | totaltype | amount in detail section.

Create a formula @.fDiff and the code for the formula is
Previous(View.amount)-View.amount

and place the formula in group footer

Try this & revert back.

Regards,
Rohini :)|||I'll try it out...and let you know...

so kind of you to reply...i really needed this...|||hello

this is my first time to post and i'm in need of your help in crystal... i tried to follow the instructions rohini gave but it gives me an error: " The ) is missing."

I tried to put ) marks on all possible places but still no luck...

I'm trying to get the total quantity per product line and I have already inserted a group for product line and placed the necessary fields in the details...

Please please reply soon! I'm anxious to see my first report! :)

Thanks in advance!|||hi siomai
u do not type the field name..in the formula editor..instead u double click the field..or try the following

Previous({View.amount})-{View.amount}

Try this one..All the best

regards,
Rohini|||Hello RohiniR!

I feel so stupid...so view is the table name! :blush: Now I get it... thanks! Stay cool! :wave:|||hi again!

The code is now error free but it gives the wrong result...

I did it as a sum...was it right that I just replaced the "-" sign to "+" sign? will this work with multiple line items,say, 5 line items or more?:confused:

Please answer back... :o

Thanks and best regards!|||Hi!

I noticed that it only adds the first two records. What I need is to sum many records. Is there a way without using insert summary? or can you suggest an approach to come up with a formula to get the sum of a field with grouping? Please assist me on this!|||siomai..
i d'nt know why u do not want to insert summary.
When we use formula ,u can give..
sum({View.Amount})

view is the table name..and amount is the field.. . :rolleyes:

but this formula returns the whole sum and not the sum for a group..
for the sum of a group u can u running total...
select
field to summarize - Amount ,
type of summary - sum,
evaluate - for each record,
reset - on change of group - (and select the group name) :lol:

Try this and get back yaar..hope this 'll work..
tell me why u do not want to insert summary.

Regards,
Rohini :thumb:|||Hi!
The reason why I don't want to insert summary is because I need it for my other formula..I have to come up with the total quantity per product line then use the sum as the divisor to the sum of net amount with this i'll be able to come up with the Average price sold.

Please give me your suggestions if i can't get the summation of a field per group on how to come up with the formula for total net amount/total quantity per product line. I'll be forever thankful! :o

a million THANKS!!!|||Hi!

I have tried your suggestion but I also need is to come up with this formula:

sum of amount/sum of qty

so if i use the insert running total, i still cannot use the summary field in my formula. :( that's the reason why i don't want to use the insert summary/running total method.

Please help me and tell me a better approach to solve my problem...|||Hello, I was given the task of creating Crystal Reports by my boss and I have no formal training on this application. Any assistance would be appreciated. I have the below code, but am getting the following error message "The ) is missing".

Code:
//count the number of days between the greatest received date (when the ticket was logged) and the greatest last modified date (the last date the ticket was updated).

count (maximum (date {CallLog.RecvdDate})) - maximum (date {CallLog.ModDate}))|||Are the {CallLog.RecvdDate} and {CallLog.ModDate} fields in date_time format?

Actually, to find "the number of days between the greatest received date (when the ticket was logged) and the greatest last modified date (the last date the ticket was updated)" you have to create a formula:

maximum (date ({CallLog.RecvdDate})) - maximum (date({CallLog.ModDate}))

But if both of the fields are in date format, your formula will be more simple:

maximum ({CallLog.RecvdDate}) - maximum ({CallLog.ModDate})|||Thanks you for the reply. I get the following error: "A number, currency, amount, or date is required here."

{CallLog.RecvdDate} and {CallLog.ModDate} are both String types. I have tried to convert them into date types without luck.|||Are those fields strings? Can you give me an example of the fields' value, please?|||Both {CallLog.RecvdDate} and {CallLog.ModDate} are strings.
An example of {CallLog.RecvdDate} is "2007-05-19" and an example of {CallLog.ModDate} is "2007-05-19".

I tried to convert the strings to date by using the below code, but it didn't work: maximum (date{CallLog.RecvdDate}) - maximum (date{CallLog.ModDate})|||Create 2 formulas which will convert the field values into date format (actually, you can copy them):

Formula1 - @.RecvdDate:

Date(tonumber(left(trim({CallLog.RecvdDate}),4)),tonumber(mid(trim({CallLog.RecvdDate}),6,2)),tonumber(right(trim({CallLog.RecvdDate}),2)))

Formula2 - @.ModDate:

Date(tonumber(left(trim({CallLog.ModDate}),4)),tonumber(mid(trim({CallLog.ModDate}),6,2)),tonumber(right(trim({CallLog.ModDate}),2)))

You can use these formulas in your report instead of those fields.

To find the difference, create another formula, call it @.difference (or whatever you like):

maximum ({@.RecvdDate}) - maximum ({@.ModDate})

Don't forget to read the help file on the used functions.

Crystal Report in VB.net Why Server Login?

I have created a report in VB.Net and use Crystal Report viewer to view it...but when I try to display the report it asked for a login to the server. I am using Intregated Security for all my other db connections in my application...how do I get that for the report?
Thanks,
CathyI've noticed that a report will ask for a database log-in, even if it was saved with data, and isn't automatically refreshed, if the database that the report was connected to when it was created, is not "locatable". I noticed this because when working at home, I would be prompted, and at work, I would not be. If I changed the data source (at home), it would no longer prompt for log-in info.|||I am at work and I'm not prompted when I connect to the same data for my forms...only with Crystal...
Thanks,
Cathy|||Cathy,

Don't know if this might apply in your situation, but, the thread "How to load a database name at runtime" http://www.dev-archive.com/forum/showthread.php?t=288950, shows people providing the log-in info just before the reports are opened, by coding it into the program. That doesn't really answer why, but it sounds like a workaround.

john

Crystal Report File View

Hi,
I'm using Crystal Report 8.5 version for report viewing in my application developed in VB6.
Usually in my application it will contain 10 to 15 pages in report to view, which is viewed by clicking on the next page arrow. I just want to know whether this is possible to view the pages by scrolling down the mouse.
ie, i want to view all the pages just like MS word page viewing. Is that possible?? How to do that if possible?

Thanks n Regards,

Binjose
Binjose is online now Add to Binjose's Reputation Edit/Delete MessageNo it is not possible. You have to click next page

Crystal Design view

Hi,
How can prevent regular crystal report users from accessing the design view of my reports?
Your help is appreciated
Thanks
SayedOne way is to use front end application like vb and show the report from there

Cross-Database View Permissions

I have a view in database1 that uses views in database2.
My DBA says I must give users DATA READER permissions to
the tables referenced by the database2 views. I am trying
to keep customers OUT of the tables, only allowing access
thru views.
Do I really have to give DATA READER to the tables in
database2 to get my cross-database view references to work?It depends on whether you allow cross database ownership chaining. This is a
configurable option post SP3 wheras prior to that it was implicit. Basically
if all the views and tables have the same owner e.g. dbo and the databases
themselves are owned by the same login e.g. sa then users with permissions
to select from a view in database1 don't need any explicit permissions on
objects in database2 - they do still require access though.Post SP3 this is
configured using sp_configure 'Cross DB Ownership Chaining' and sp_dboption
with the 'db chaining' option. See BOL for more on cross database ownership
chaining.
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Barnes" <anonymous@.discussions.microsoft.com> wrote in message
news:13a2e01c44408$5da79f30$a501280a@.phx
.gbl...
> I have a view in database1 that uses views in database2.
> My DBA says I must give users DATA READER permissions to
> the tables referenced by the database2 views. I am trying
> to keep customers OUT of the tables, only allowing access
> thru views.
> Do I really have to give DATA READER to the tables in
> database2 to get my cross-database view references to work?|||To add to Jasper's response, both databases need to be owned by the same
login in order for the dbo-owned object ownership chain to be unbroken.
Your users still need a security context in database2 but no permissions or
role memberships need to be granted if objects are only accessed indirectly
via referencing database1 objects.
Hope this helps.
Dan Guzman
SQL Server MVP
"Barnes" <anonymous@.discussions.microsoft.com> wrote in message
news:13a2e01c44408$5da79f30$a501280a@.phx
.gbl...
> I have a view in database1 that uses views in database2.
> My DBA says I must give users DATA READER permissions to
> the tables referenced by the database2 views. I am trying
> to keep customers OUT of the tables, only allowing access
> thru views.
> Do I really have to give DATA READER to the tables in
> database2 to get my cross-database view references to work?

cross project reporting in project server 2002

forum is there a way to view and report mulpitle projects
the same time. i.e see all the resorces and all their
projects, see all costs assciated with all projects.
Basically I'm trying to view all projects together in one
report as opposed to viewing each project individually?
and if so how id this done? Thanks for all repliesIs this a sql server question?
If this is a database structure access you are asking
about thgen you will need to post the structure and what
you want as the result of the query.
>--Original Message--
>forum is there a way to view and report mulpitle
projects
>the same time. i.e see all the resorces and all their
>projects, see all costs assciated with all projects.
>Basically I'm trying to view all projects together in one
>report as opposed to viewing each project individually?
>and if so how id this done? Thanks for all replies
>.
>

Cross Join/? Query in a View Help

Hey All...
Got a View question.
Have 2 tables:
#1 Currencies
|CCY_Name|CCY_Code|

#2 Rates
|CCY1|CCY2|CCY3|...etc|Active|
-> where the Columns CCY# = the Records in #1

How do I build a View to Select the ONE record in #2 where Active=Y, having the CCY_Name from #1 based on #2.CCY1 (Column NAME) = #1.CCY_Code (Record).

Thanks

robbied111Is there a reason to have this CROSS-TAB-LOOK-ALIKE structure for #2?|||To keep a single record in the table by a Date.

Its currently in an ASP page via 2 Arrays, but it would be more convenient to have it in a View.

Thanks

Cross Database Join in a Data Source View

Hi,

Is it possible to do a cross database join in a report services data source view? It doesn't look like it.

If not I was thinking of linking the table into the other database.

TIA,
Darren

Hello Darren,

Yes, you can do joins to other databases.

For example:

select table1.col1

from db1.dbo.table1 t1

inner join db2.dbo.table2 t2 on t1.col1 = t2.col2

Jarret

|||In the interest of reuse I would like to reference two Data Source Views in the Model. I have a database that has static database for everyone and a separate database for every customer? Is that possible?|||If I add a related table from another database the model blows up when I autogenerate and says it can't find dbo.Table. It shouldn't be dbo.Table it should be database.dbo.Table. If I explore the data it works fine.

Cross Database Issue

Hi

I have a view which is like following

Like

DB1.View1

Select * from Accounting,Employee

where Accounting.Emp_Id = Accounting.Emp_Id

and i am using this view in DB2.storedProcedure1, but this gives me an error when I execute this Stored procedure from VBA code. the same SP works from query analyser with same user_id and pwd

Error - Could not find database id 9. Database may not be activated yet or may be in transition.

If I change the view to following the SP works

DB1.View1

Select * from DB1.Accounting,DB1.Employee

where DB1.Accounting.Emp_Id = DB1.Accounting.Emp_Id

But I dont want to change the view as i dont own it.

Please suggest the solution for this.

Thanks in Advance.

Ashutosh

Moving your question to a different thread, where they can offer more help.

Thanks

Richard Cook

VS Debugger Team

|||

Is there a synonym defined on the objects mentioned, forcing SQL Server to go to a non-existing database ? Did you trace the command which is fired against the database using SQL Profiler ?

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||

You did not mentioned the version and the service pack of sql server. IF its 2000 check this, it seems to be u have not applied the patches

http://support.microsoft.com/kb/834688

Madhu

|||

Thanks Jens,

the issue is - I have a view in one database which i refer from another database.

e.g. DB1 has view V1 and i need to use it in a SP1 in DB2

so i refer to the view as DB1.V1 in DB2.SP1. but the tables inside DB1.V1 are without database reference i.e. without [db].[tables]..its just- "select a from [table]"

and i run this DB2.SP1 from sql server query analyzer with login "abc",pwd - "abc". This works perfectly fine.

but when i run the same SP from my VBA code with same login "abc" pwd "abc"..it gives me above specified error.

but..if i fix the view..to "select a from [db].[dbo].[table]...it works from my VBA code as well.

therefore it makes me think that i need to change the view to set all the tables as [db].[table]...but as the db name changes from environment to environment i.e. development/integration...we can not put [db].[table]..

INSTEAD i need something like [currentDB].[Table].

Is that possible in sql server 2000

|||

Thanks Madhu.

I did saw that post on support.microsoft.com but here there are 2 things

1. It was working till yesterday and stoped working today..so can it be an issue with user privilages?

2. Its working with the solution that i have implmeneted, but that is too specific and i need generic solution.

Its SQL server 2000.

please see this

the issue is - I have a view in one database which i refer from another database.

e.g. DB1 has view V1 and i need to use it in a SP1 in DB2

so i refer to the view as DB1.V1 in DB2.SP1. but the tables inside DB1.V1 are without database reference i.e. without [db].[tables]..its just- "select a from [table]"

and i run this DB2.SP1 from sql server query analyzer with login "abc",pwd - "abc". This works perfectly fine.

but when i run the same SP from my VBA code with same login "abc" pwd "abc"..it gives me above specified error.

but..if i fix the view..to "select a from [db].[dbo].[table]...it works from my VBA code as well.

therefore it makes me think that i need to change the view to set all the tables as [db].[table]...but as the db name changes from environment to environment i.e. development/integration...we can not put [db].[table]..

INSTEAD i need something like [currentDB].[Table].

Is that possible in sql server 2000