Crystal Reports problem

I have a problem when I try to work with alias.
It takes the report 15min to load.
I'm working with 16 aliases and I think thet might be the problem.
Has anybody got any ideas?Do you mean you've got 16 tables joined together in the report?
Does the SQL itself run any faster outside of Crystal?
I had a report that had a driver table joined to another table 3 times. It performed well with 2 tables, just about OK with 3 and terribly with 4. Turned out that the query plan on the database server was rubbish even though all the filter conditions were using indexed columns. Changing the SQL to prevent the indexes being used caused the query to run quickly again!|||Do you mean you have 16 tables joined in the report?
Does the Crystal generated query run as slowly outside of Crystal, or is Crystal itself doing lots of post-processing on the returned data?|||Thet is correct I have 1 table but it has 16 diferent names, because in the table that this one is connected to, has 16 diferent names (but each name gets its value from the same table). So I tryed doing it with only 10 aliases and it works fine (the report loads in 9s).

So what I'm trying to figure out is, does the crystal have a constrain to how many aliases you can use?|||No constraint that I'm aware of - it's just another table after all. My example was similar but I only aliased the same table 3 times with each alias joined to one other table.
Check the query plan - it may be doing a whole load of nested loops that get worse with each new alias you add.|||Thax

I changed my query, and you were wright, I had some sub querys, I changed it a bit and now it works prity fine.

Crystal Reports from VC++

Hi All,

I am creating crystal reports(version 8.5) using VC++ code.
Its working fine on the the machines which have crystal reports installed .
however same is not the case with non crystal machines even after registring the Crystl32.OCX used by the Crystal Reports Object.

I am getting error " Unable to logon to server" while trying to run the report.
DSNs are same on both type of machines.
(one with crystal reports and one without it )

I have already distributed crystal dlls with the application.
(Crystl32.OCX,Craxdrt.dll, CRViewer.dll,CRDesignerCtrl.dll,Craxddrt.dll)

Do I need to do some thing in addition to this ?

kindly help.

thanks
SanjayCoule you please provide me with your code, it helps me build my own app...

Rob

Crystal Reports Exception in ASP.NET

Hi All...!!!
Im new() here :cool:
Well, ive been working on Crystal Reports in ASP.Net (using vb.net). While genrating reports (@. runtime) i encountered few problems... Im not able to make head or tails of these exceptions. Can anyone explain to me wht r these exceptions n y these occur?

1. CrystalDecisions.CrystalReports.Engine.LogOnException: Logon failed.
2. QueryEngineFailure

ThanX in advance...

Regards,
ReiO :Dhi,
freind.
You may not installed crystal report components while installing .NET.
Please reinstall all components.
I hope it will work

regard
RKK|||Try this

remove @. in .net code where u set parameter field

hope this helps you|||i am getting the same error. i have seperately installed the components as said above and still no luck

whats weird is dat it works fine in a windows app bt not asp

y this occurs is beyond human understanding bt losing my job over it is not!

Crystal Reports E10 and Data warehouse

Hi, there,

Is there anybody here who is working with data warehouse with Crystal Reports as a tool? Would you please share some experience with me?

I used to work with Oracle reports. It seems to me that Crystal needs to bring all the table data back to client before I may link the tables to get query right.

Another question is how to implement the following requirements:
List all the detail account information for all the accounts under firm A.

Here Firm A might have over 100,000 accounts. The fact tables will holds all accounts for all firms data.

In Oracle reports, we can define 2 path query to get the account number for firm A first then get each account detail by using parent child query. How crystal achieve this kind of query ?

Thanks,Design the report using parent and child tables
Group by the report by key column

crystal reports 9.0

I know this sounds stupid but no one can answer this for me. I am working with Micros 3700 and it requires ver 9 to work. But there are differnt versions, pro, dev, standard. Does anyone know the differences,,Visit http://support.businessobjects.com

Crystal Reports 4.6 not working in XP

My application uses Crystal Reports 4.6 & Visual Basic 6.0. The database is in MS Access 97.

It works on Windows 95, 98, NT 4.0, 2000. My users just upgraded to Windows XP.
But, in Windows XP the report is not shown and no error with .PrintReport(). When I tried with .Action = 1, it shows 2 errors.

1)
Runtime error '5':
Invalid procedure call or argument

2)
Error Number: 20535
Unable to connect: incorrect session parameters.

And the application terminates!

This is the code.

With crwReport
.Destination = crptToWindow
.WindowTitle = "Bill"
.WindowState = crptMaximized
.ReportFileName = "D:\reports\Bill.rpt"
.DataFiles(0) = "D:\client.mdb"
.Action = 1
End With

I have tried creating setup from Package & Deployment Wizard and Visual Studio Installer 1.1 but neither work for Crystal Reports...

I tried to install Crystal Reports 4.6 on user's machine but still it is not working!

The only ultimate solution I got is to install Visual Studio 6.0 on user's machine. Heck, thats a lame way... and my client can't afford it.

Isn't there any other better way??i had the same problem and i was able to get it to work by doing this: download the update here: http://www.randem.com/support.html
Automatic OS Updater for MDAC/JET/DCOM

this is the update that the computer needs for access runtime files and vb files... i installed this on the other xp computer and it fixxed the errors i was havin openin the reports... i stuggld with this for a long time
hope this works...

Crystal reports

I am working on creating Crystal Reports from a SQL database.
In CR you can define a multiple range parameter i.e. 1-10, 100-200, 300-400 and Cr will filer records that satisfy this criteria.
How can I transfer this functionality using a stored procedure?What functionality? Filtering? A stored procedure can do that by being passed the Min and Max as paramters, of course.

Crystal report takes lot of time to display

hi k. babu
i hopes that u will solve my problem
i am ketan kshirsagar working on the some project
i am facing the problem at the time displaying data in crystal report.
i am fetching data from sql table t display the data in crystal report vrsion 4.5
it contains more than 3 lakhs records and crystal report will take more than half an hour to display it .what should i do to reduce the time to display these records?
please help me

regards
ketanYou should limit the records that are shown in the report
Did you use index for the key column in that table?
How many users are using your project?

Crystal report setup using Package and Deployment Wizard of VB6.0

Hi
I am working on Visual Basic 6.0 and I've use crystal report 9.2 for report printnig.
But when I made the setup using Package and Deployment Wizard.
While installing the setup in the client's machine setup gives error -
An error occured while registering these files...
crtslv.dll
exportModeller.dll
filedialog.dll
crqe.dll

Due to which my report does not show up. when I call my report system gives error - "Load report failed"

Please help.

PuneetInclude these dlls while making setup.|||I've tried that. I've addid these file while making setup but invane its still not working.

Actually the prob is when I run setup on the win 98 machine it gives error
"An error occured while registering these files..."
even after the inclusion of these files while making the setup.

Now what should I do.|||It seems the dll is corrupted

Crystal report refresh

Hi All i'm working in crystal report 8.5 with visual basic 6. All reports are working well only problem when we open any report. coz when we open report it take much time to show data on report. mean its processing speed is very slow. i wants when i open report, with in few seconds it shows all record, but this can be huge record. mean i wants to make it more efficient, then usual.
if i convert these reports into crystal report 10 from 8.5, how it will be....... mean it will increase its efficiency or not.
plz tell me if any one knows abt it.

mushiHow many data does the table have?

Crystal Report parameter issue

Hello,

There is probably a really simple solution to this problem... but, here it is..

I am working on CR Designer v11 and have a report that uses a stored procedure. Originally, when the report was created, this stored procedure had one parameter. Since then, however, I have added an additional parameter to the SP in SQL. After doing so, I opened the report, in report designer, and manually added the new parameter to the parameter list. I then realized that I did not need to do this. The Verify Database function should auto populate the parameter list for me. So, I then deleted the parameter(that I had created manually) and performed the Verify Database. But now the parameter does not auto populate in the parameter list.

By deleting the parameter (that I had created manually), did I cause the designer to ignore this second parameter in the actual SP? How can I get the designer to auto populate the parameter list with this new parameter?

Try this forum: http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=77&SiteID=1

Crystal Report on Remoting Problem.

Hi All,

Following is the scenario i am working on.
1. There are some .RPT files which reside on server.
2. Client will request to see the report, in that case RPT file will
be dynamically loaded on server , dataset is passed as datasource to
report and then that report is streamed to client using
ExportToStream method.
3. Client will receive this stream and reconstruct Report Document
and will display in viewer.
4. Client Server communication is mentained using .Net remoting.

Everything works fine in stand alone mode. Problem is in remoting
mode.
Problem occurs at the time of loading report file dynamically,
following is the exception i get.

The type
CrystalDecisions.CrystalReports.Engine.LoadSaveReportException in
Assembly CrystalDecisions.Shared, Version=9.1.5000.0,
Culture=neutral, PublicKeyToken=692fbea5521e1304 is not marked as
serializable.

Server stack trace:
at
System.Runtime.Serialization.Formatters.Binary.WriteObjectInfo.InitSe
rialize(Object obj, ISurrogateSelector surrogateSelector,
StreamingContext context, SerObjectInfoInit serObjectInfoInit,
IFormatterConverter converter)
at
System.Runtime.Serialization.Formatters.Binary.WriteObjectInfo.Serial
ize(Object obj, ISurrogateSelector surrogateSelector,
StreamingContext context, SerObjectInfoInit serObjectInfoInit,
IFormatterConverter converter)
at
System.Runtime.Serialization.Formatters.Binary.ObjectWriter.Serialize
(Object graph, Header[] inHeaders, __BinaryWriter serWriter, Boolean
fCheck)
at
System.Runtime.Serialization.Formatters.Binary.BinaryFormatter.Serial
ize(Stream serializationStream, Object graph, Header[] headers,
Boolean fCheck)
at
System.Runtime.Remoting.Channels.BinaryServerFormatterSink.SerializeR
esponse(IServerResponseChannelSinkStack sinkStack, IMessage msg,
ITransportHeaders& headers, Stream& stream)
at
System.Runtime.Remoting.Channels.BinaryServerFormatterSink.ProcessMes
sage(IServerChannelSinkStack sinkStack, IMessage requestMsg,
ITransportHeaders requestHeaders, Stream requestStream, IMessage&
responseMsg, ITransportHeaders& responseHeaders, Stream&
responseStream)

Exception rethrown at [0]:
at System.Runtime.Remoting.Proxies.RealProxy.HandleReturnMessage
(IMessage reqMsg, IMessage retMsg)
at System.Runtime.Remoting.Proxies.RealProxy.PrivateInvoke
(MessageData& msgData, Int32 type)
at Hebron.Common.Interfaces.IFeesReportController.GetInvoice
(ArrayList oArrylst)
at Hebron.ReportClient.ReportTool.PrintReport(ReportName name,
ArrayList oArry)
at Hebron.FeesClient.ReviewInvoices.btnPrint_Click(Object sender,
EventArgs e)Normally if an object is serializable then you can just add an attribute [serialziable] to your class and it works.

Crystal Report Document Object is not Serializable as per the Business Object documentation(atleast till version 10). During remoting the object needs to be serialized and that is why you get the error. Try to find out from Business Objects support team if you can, at all serialize the object ? Will face the same issue if using ASP.NET "StateServer" session and try to save the Report Document object in session.

Frank

Crystal Report formula

I am currently working on a payroll distribution report and I am have some trouble. I have pulled in the employees for the payroll along with the gross and net amounts of their paychecks. The only other peice that I need is the OT amount which comes from a different table. I have linked the two tables and when I try to pull in the OT amount with a (IF THEN, so I can try to only pull in the dollar amount for anyone who has an OT code) statement it gives me $0.00 listed for all of their pay codes. I need the OT dollar amount for just those that had OT and $0.00 for those that did not. Each person has several pay lines and I need everyone not just those with OT so filtering out the OT code will not work. Any suggestion would be deeply appreciated.

Example of what I am getting
Gross Net OT
398 John Doe 500.00 150.00 0.00 (This is a base pay)
398 John Doe 500.00 150.00 50.00 (I only need this line if an individual had OT)

What I need to see:
Gross Net OT
398 John Doe 500.00 150.00 50.00 (Had OT)
399 Sue Brown 600.00 250.00 0.00 (Did not have OT)If your OT table doesn't allow for nulls, you wouldn't need an IF statement. If so, you'd just check if it was null - if so put 0, if not put the value.
I.E.: if IsNull({Overtime.OT}) Then $0 Else ${Overtime.OT} //the $ sign is for converting to currency

Or am I not understanding your question?

Crystal Report drives me crazy

Hi,

My company purchased Crystal Report Software two days back only b'coz I requested for it. And now that I started working on it, I am not getting the result what I want. I am really worried as to how am I going to answere to my boss's questions. I even searched the internet SO MUCH, but I couldn't get any help.

Let me tell you what I have done.

I have used unbound fields in the report bcoz I prefer dynamic reports as the system that I am developing is a multiuser system.
I want to display records in Crystal report according to certain criteria that the user picks from a form.

This is my code:

Dim Report As New CrystalReport1
Dim Rs As New ADODB.Recordset

Private Sub Form_Load()

Screen.MousePointer = vbHourglass

Rs.Open "select StaffMaster.[EmpName], StaffHrs.[ATH] from staffmaster,staffhrs where staffmaster.empcode=" & _
"staffhrs.empcode and staffhrs.discipline='architecture' order by empname", Cn, adOpenStatic, adLockReadOnly, adCmdText

Report.DiscardSavedData

Report.Database.SetDataSource Rs

Report.EmpName.SetUnboundFieldSource "{Rs.EmpName}"
Report.ATH.SetUnboundFieldSource "{Rs.ATH}"

CRViewer1.ReportSource = Report
CRViewer1.ViewReport
Screen.MousePointer = vbDefault

End Sub

The error that I get is as follows:

'The field name is not known.'

Any help would be appreciated.

ThanksProbably one of the fields that you are selecting in the rs.open recordset might be wrong. Either there is a spelling mistake or the field is not there in your database. Check it out. Hope this helps.|||I double checked the names and even the upper case and lower case letters. Still the same.

I looped through the recordset to see if it displays the correct data and it is so. This is the loop:

If Not (Rs.BOF Or Rs.EOF) Then
Rs.MoveFirst
Do Until Rs.EOF
MsgBox Rs(0) & " " & Rs(1)
Rs.MoveNext
Loop
End If|||Where exactly in the line of code do you get the error?Debug it and check where you are getting the error. This might help you in
streamlining the code and closing in the problem

Just check whether the code in the following lines is giving a problem

Report.DiscardSavedData
Report.Database.SetDataSource Rs

Report.EmpName.SetUnboundFieldSource "{Rs.EmpName}"
Report.ATH.SetUnboundFieldSource "{Rs.ATH}"



Check out the syntax for the above lines of code and whether the parameters are correctly passed|||When I stepped through the code, I get the error message in the line

CRViewer1.ViewReport

These lines did not show any error.

Report.DiscardSavedData
Report.Database.SetDataSource Rs

Report.EmpName.SetUnboundFieldSource "{Rs.EmpName}"
Report.ATH.SetUnboundFieldSource "{Rs.ATH}"

Thanks|||Do you have any formula fields in the report. If yes then you will have to set the formulas befor you view the report.

Does your report show the details when you preview it through crystal reports? Or are you having any problems in viewing it.

I am not sure whether this will help but just try to set the location of the database to the report and check it out. Even through the try to set the datafiles property of CR.|||Yes, both the unbound fields are formula fields. How do I set the formula?|||This is the code once again.

Report.DiscardSavedData
Report.Database.SetDataSource Rs

Report.UnboundString11.SetUnboundFieldSource ("{Rs.EmpName}")
Report.ATH.SetUnboundFieldSource ("{Rs.ATH}")

CRViewer1.ReportSource = Report
CRViewer1.ViewReport
Screen.MousePointer = vbDefault|||I normally use to set the formulas as

CR1.Formulas(0) = "Compname = " & CoName

where coname is a variable populated with the company's name in the initial startupcode

Just check whether the below code does set the formulas

Report.UnboundString11.SetUnboundFieldSource ("{Rs.EmpName}")
Report.ATH.SetUnboundFieldSource ("{Rs.ATH}")


Probably it is in thispart of your code the error is there. I am not sure of how to set the unbound fields as formulas. You will have to check out the syntax in your CR help files.

Check whether the field name you have given in the report has to be entered while setting the unbound fields

Hope this helps.|||Thanks for your reply. I found the solution. The code should be like this.

Report.Database.Tables.Add "", , Rs

I have one other doubt too. I have a table which contains the availability percentage and Month_Year of staff. Each staff can have more than one record in the table since one record is for one month. If he is assigned any work for, say, June, he has a record for June in the table. If not assigned, he does not have one. I wish to create a report in Crystal Report where I can see the report of each staff for 6 months in one line. If he does not have a record of any of these 6 months, then his availability should display 100. Can this be accomplished? If so, please help me.

Thanks.|||you can place a formula field where in you can check whether there is a record for that month. if there are no records display 100 else display the availability.

hope this helps|||Yeah! Exactly! That is what I want. But how do I place a formula field with code? Please help.

Thanks|||First Insert a formula field. In the formula editor you can set the conditions like below. You can place the formula field then right click on the field. you will find an option edit formula. click on that option and you can enter the code.

if not IsNull ({Ttest.SoldQty}) then
{@.Unstk}*{PurchaseReg.Rate} else
{PurchaseReg.Amount}|||But I am formatting the report with code, so that I only have to add one report into the project and design the report with code according to the different reports of requirement.

And one more thing. My table called Availability contains one record for every month that the staff is assigned. So how will I get the recordset? Please help.

Thanks|||I am not getting your point. In a report if you place a formula field and put the conditions in it.

you can create the report based on the availability field and the last column instead of the database field can be the derived field.

what do you mean formatting the report with code ?|||I will explain it clearly.

TableName: Availability

Fields:

EmpCode
Discipline
Month_Year
Percentage_of_Availability

One record in this table is for one month. I wish to display the Percentage of Availability in the report for 6 months in one line.

I hope it can be done with formula fields.

Thanks

crystal report compatability

The company that I am working for is looking to make the upgrade to 2005 from 2000, and we will need to continue to use crystal reports 8.5 until we have everything ported into the 2005 reporting services.

Does sql server 2005 support crystal reports 8.5?

Thanks

Moving to "SQL Server Reporting Services", which has a better chance answering this.

Crystal Report based on SQL Query!

Hi all,

I'm working on Crystal Reports in C#. (Ofcourse ... this section of Forums is on VB... But still... i may geta solution for my prob!!)

I need to generate a report based on data retrieved from an SQL Query. Every time I need to pass this Query dynamically to the report, I mean, my SQL Query is not constant but changes depending on certain criteria.

How can I let the Report know this changing Query??
I could generate a report based on a fixed SQL Query. But How to send this dynamically changing Query to the report??

Pls help me out.

Thank you all.

Bye.i dunno about C# but, u can do it in VB.Net by setting the dataset as report source.

may be it can be done in C# too.

Already answered how to do it in VB.net, in this forum.|||Mr. Addidas

Hope the field names and number of fields are same eventhough query is changing.

Why don't you go for procedure with parameters. You can pass parameter dynamically to procedure. In VB the crystal report control provide this facility by ".StoredProcParam(0)" property. obviously you can give any number of parameter.

Second method is you can pass total query as string parameter to procedure.

Crystal report 8.5 help

i m using access 2000 from there i m calling crystal report 8.5 , every thing working fine except one query havib=ng having Criteria with parameters , how to pass this parameter to the Crystal report.

would some body help me

SELECT Assignment.BNo, Trade.Trade, Assignment.Source, Assignment.MobDate, Project.ProjectID
FROM Trade INNER JOIN (Project INNER JOIN (Orders INNER JOIN (Employees INNER JOIN Assignment ON Employees.[Badge Number] = Assignment.BNo) ON Orders.OrderID = Assignment.OrderNo) ON Project.ProjectID = Orders.ProjectID) ON Trade.[Serial No] = Assignment.Trade
GROUP BY Assignment.BNo, Trade.Trade, Assignment.Source, Assignment.MobDate, Project.ProjectID, Month([Assignment].[mobdate]), Year([Assignment].[mobdate])
HAVING (((Assignment.MobDate)=[enter date]) AND ((Month([Assignment].[mobdate]))=Month(Date())) AND ((Year([Assignment].[mobdate]))=Year(Date())))
ORDER BY Assignment.BNo;

this is the queryWhile writing the query, u may have some option to create parameter.

CR 9 has that option. No idea about CR 8.5

Crystal Report 8.0, Summary function !

Friends,

I'm working with crystal report 8.0 and SQL server.

I need to create a formula in group section, which should return the summary value of next group.

Or is there any function for get summary value if the group value is 'x'. same as SumIF( , , ) in excel.You need to write a formula having the code

If groupvalue='x' then
{field}

and use the summary using this

Crystal 9 - broken Report SQL Server

I'm using Crystal 9 with SQL Server database. I have a report that was working fine for the first 8 months. As soon as I add another month, the report fails. I've tried different setting in my indexes with different variations of periods. The minute I have over 8 periods included, the report fails. No errors, just no data. I don't even have a clue where to look for possible answers on the Internet.

Can any one help or direct me to some resources??

Thanks,

DebbieMy first guess would be that your report is doing something (like a cross tab) that has a "columnarity" to it, and the report definition only provides for 8 columns of data in the result set. This is a common problem for reporting tools like Crystal.

My next guess would be that there is some kind of "query governor" that is putting an artificial limit on the size/resources of the result set. This is something you'd have to ask one of your administrators about, since there isn't any way that I know of for an end-user to reliably detect these.

I've got lots of other guesses, but these two cover about 98% of the situations that I find like this, so lets try the easy answers first, and only worry about the hard stuff if we need to! ;)

-PatP|||I'm not using a Cross Tab, but do have several columns of data. How would I look around for limitations with regards to limitations in the results set?

Also, we don't really have an expert in Crystal. I'm probably the closest we have. Could you point me in the direction of looking for the "query govenor" that may be putting a limit on my size/resources result set?

Also, do you know of any way to set up debugging in Crystal? I miss that from my programming projects. It would be so useful to know of a way of following what is going on with the report behind the scenes.

Thanks,

Debbie S.|||There is no such thing as a Crystal Reports expert. I've met many who claim to be, but all of them turn out to be absolute noobs at programming. This is because Crystal is a poorly designed product that requires you to programming like an absolute noob to use it.

I'm guessing Pat is right that your report is failing because it is receiving an extra column from a cross-tab style result-set, even if you aren't specifically creating a crosstab query.

Does your report pull directly from a stored procedure of SQL Server view, or does it send an SQL query to the server? If so, the first thing you should do is strip any sort of SQL statements or logic out of Crystal and put it in a stored procedure. Crystal is a sucky sql editor, and you will have a much easier time debugging a store proc. Crystal, and other reporting tools, should be used for displaying the data ONLY, and all data manipulation should be handled on the server.|||That is exactly what I've been telling my boss. I keep asking him to send me to training for views and procs because I would rather get the data together using those tools and displaying it with Crystal. Of course, what do I know. My boss doesn't even know what a view is and thus questions why we need one.

Thanks for the input.

Deb S.

CROSSTAB QUERY FROM ACCESS TO SQL

Hello,
I'm quite basically new to working with SQL databases, coming from a
Microsoft Access background. As we've got the SQL server sitting here,
I've been asked to get the MS Access db's moved across onto it. I've
had no problem with the tables, but as the majority of the reporting
comes from cross tab queries I'm having trouble getting started with
the design of a cross tab/pivot view. I've tried all the examples the
search on groups provided but I'm having a hard time making it work for
me.
I'm reporting off one table tblMONTHCOSTS, the data at the moment is
displayed as
txtMOBILE NO txtBILLMONTH curCALLCOST
078******** 200501 15.25
077******* 200501 12.54
078******** 200502 9.54
077******* 200502 10.55
And so on...... For 12 months, for 39 mobile phones.
What I want it to do is display like this :
txtMOBILE NO 200501 200502 200503......
078******** 15.25 9.54
077******* 12.54 10.55
Does anyone have any advice on how this can be done? I know I'm
pretty new to this so if you could even just point me in the direction
of a good book I would be very grateful.
ThanksRefer this
http://weblogs.sqlteam.com/jeffs/archive/2005/05.aspx
Madhivanan