crystal reports XI with ascii file

hello CR users,
i'am nearly completely new in the CR field, so sorry for my question...

i have a big ascii file about 300mb. how can i use this file within CR?
is there any way to convert this ascii file into a excel file ore wathever so i can use it.
smaller pieces of this file i can convert into a 'comma separately' csv file.
but there is no well formatet column separation...

i would be very happy if there is somebody out there how wuold post a short solution for this...
thanks HagenSo do you want to show the Ascii values in the report?

Crystal Reports Group By and MySQL mediumtext field

Hello Friends,

I have connected Crystal Reports to MySQL database 4.x.
Now I want to run a query using the group by option on a mediumtext data type field. But as Crystal Reports does not allow to do a Group by on a mediumtext field is there any way i can convert the field to String and then do a group by ?

Thanks for your help in advance.
Shanker.Do you have unique id value for that column?
If so group by itsql

Crystal Reports Crosstab

Hello Everyone,
Is there anyway I can repeat the row field values for each record in a crosstab report. Currently they only appear once per group.
Regards,
Omar Malikif u want to repeat row label then
u can repeat by checking repeat row label option in customize style tab of cross tab expert.

if u want to repeat field values
use a formula, assign same field to it and use formula in cross tab.

Crystal reports and page breaks

I have a requirement from our users that they want to add page breaks into the middle of a text field in our application.

This text is being printed in a Crystal report, so they would have the facility then to split text over 1 or more pages without coding needed to be done.

Is it possible to type in (use of varous keys e.g. Ctrl + Shift + A) a special character in the text field which Crystal Reports will process as a new page?

Any assistance would be gratefully received.you can use chr(13). Create new formula having this code

left({field},100)+chr(13)+mid({field},101,len({Feidl})

and set can grow propery to true

Crystal Report Sorting Problem

Hi,

I have set a formula field '@.sorting' , where {?yy} is a parameter.

Following is the formula:-

if {?yy}="1" then
{Table1.RSRCE_NME}
else if {?yy}="2" then
{Table2.PROJ_NME}

Now i have set this formula field '@.sorting' as a SortField. I am able to dynamically change the sort parameter. But i am unable to set the Order [Asc/Desc] of the sort field.

What should be done?. Please help. :)

Thanx in advance
Rajesh RajendranHi,
Goto Report > Record Sort. In the menu you can see the Formula field and table fields. Select @.sorting. Now the report will be sorted by @.sorting.

Madhivanan

Crystal Report Question

I'm designing a report and as an example, I'll say that I am using a field called COLORS. And during 2005 the Color Red was not used for any purchases. However, I still want the color RED to appear on the report with a zero in the monthly columns. I've tried everything to get this to work and I can't. Any suggestions, please?? Using an Oracle database.Do you want to show the field with red color if it is 0?|||No, I do not want to show a red color background if a value is zero. I want the COLOR field to show the name RED even if there is no data for that field up to this point in time.

So, for example, even if no RED color purchases were made I would still like RED to appear on the output of the report with a value of zero such as an example displayed below;

COLOR JAN FEB MAR APR

BLUE 10 10 10 10
GRAY 10 10 10 10
RED 0 0 0 0
BLACK 5 5 5 5|||I think you need to use Left Outer join in your SQL Query
What is your BackEnd?|||By BackEnd do you mean where does our data come from - it is an ORACLE database.|||If you use Joins then Use Left Outer Join

Crystal Report Can Grow Growing too much

Hello,

I'm experiencing something really weird. When i set the "Can Grow" option on my report, all my text is now displaying, but the field is sized much larger than what the text is using. So i end up having a report with rows seperated almost 1 to 2 inch from each other.

Has anyone expierences this before, or can explain this behavior to me?

ThanksIf one of the rows has data with higher length then other rows will also occupy same width. Thats correct. Otherwise increase the width manually

Crystal report - using sub report

i am using a sub report in my main report. I am able to count/ total a field using the Running total within that subreport which match the criteria of selection. i am passing a parameter from my main report say Issue number to subreport.

For each issue no passed i am totalling and writing in the report - one row per issue no ( with different columns like January, Feb, March,...)

Problem: At the end of the report i want the Verticle total of each coulmn say total in January, Feb, March,...I am not able to do this.

Is there a way to do this?

In the sub report i use Formula field in running total as i need to pick up only those records in the database which match the selection criteria provided. I tried other options like Sum of a field,..but no success.

let me know if any one of you have answer. THANKSdo you use grouping in the report(Jan, Feb, Mar...)?|||NO.

I have formula fields to calculate the First and the last day of each of the months using the current date. Using these i check the date in the record to count that record whether that should fall in Jan or Feb or march in the sub report.

Data comes from 2 seperate db tables. Issueno table(main report) and Tickets table (sub report)
Only Selected Issue no is passed as parameter to the sub report to do the above calculation (count no of tickets with date in Jan or Feb or Mar..).
Sub Report footer has these running total fields with the formula to count the tickets.

Report looks like this:

Issue# Rec in Jan Recs..Feb RecMarch Recs..April Total
5 0 1 2 0 3
12 2 0 7 2 11
14 0 2 0 4 6
17 11 4 0 0 15
24 0 7 1 0 8
======================================
GTotal ?? ?? ?? ?? ??
========================================
Or is there a way to count the figures in each column at the end of the Report?

(Data: First Row:- Issue no=5, Jan=0, Feb=1, March=2, Apr=0, Total=3)|||Your bottom line is to count the tickets on january, february..., i im right?

If that is you case, try this... add running total field(january) and the type of summary is count. Then in "evaluate", click the formula option and type this

month({ticket.date})=1

This means fo the month of january...

Add the another running total field for the month of february and so for...

Sorry, im slow in english so i cant understand yor question clearly.|||Your English: Not a problem at all. Thanks for the Feedback.

My problem in Short:
I am trying to Grand Total a DERIVED Field/Column like Jan, Feb, Mar...SO there is no Month field as such to Grand Total. All this calculation is done ina Sub report using the Formula field to determine in which coulmn (jan/feb/..) the record falls and accordingly counted in that month. For every issue no passed from main report as a parameter to subreport all the attached tickets to that Issue no are validated and if the selection criteria matched then counted.
Only vertical total of a devived field like Jan/feb/.. is a problem

Crystal Report - Map Field

Hi,
I had designed the crystal reports with the SQL command as the data source. Now I have created views with the same command with some modifications. I use the "Set DataSource Location" to change the data source to the created view. When i click update, I get the Map Dialog.
I'm fine with all these things. My problem is, if i had left some fields unmapped and saved the report, I'm unable to map further.Also I'm not able to change my mapping later. How to go about it? Is there a way to change the mappings?

Regards,
- KulanthaivelDo verify Database that will show the mappings if any

crystal Report

hi All,

i m using Vb 6.0,M.Access and crystal report 9

i have one form in vb which have id and date field,
i want to select values from that vb form and seaching records in table in Access and display record in crystal report....

plz..help ma...it's urgent..

Thanx in Adavance

Regards..

Sabina Maniar

U.A.epass parameters to crystal report , then use those parameter in report query or in record selection formula.|||Hi..

Thankx to reply

i have already pased a para to crystal report ,

but i dont know how to pass CR para in vb in crystal viewer...

plz...if u have any related topics then plz..plz...reply me..

thanx again

Regards,

Sabina Maniar
U.A.E|||Set Report = crystal.OpenReport(App.Path & "\report1.rpt")

Report.DiscardSavedData
Report.Database.SetDataSource rs
Report.ParameterFields(1).AddCurrentValue ("aaa")

You can use this method to set parameters..

Regards,
Ahmed

Crystal Report

I am using VB 6 with MS Access as Back End. I saved a path name of a text file in a field of Access MDB. I am using Crystal report 8 for creating reports. Will anybody help me to print the contents of the file stored in the field in the crystal report? (For eg the field contains the value "c:\project\test.txt" How to print the contents in the "detail" portion of the crystal report. When I select the field, the file name comes not the contents). Pl. help me.
Meenakshi.RMeenakshi,
I dont think there is a way to do exactly you want. But you can use heperlink option. Right Click on any field; Goto Format Field and Select HyperLink and File and Select the text file. When running the report, if you click that Field, then the text file will be opened.|||Madhi,
Each record has a different text file. With your suggestion, I have to make a permanent link to that text file. How to get different text file links? pl. guide.
Meenakshi.R|||Meenakshi,
If each record has to show different file, then you can use formula.
Do the same procedure that I told you except that instead of selecting the file, there is button labelled x-2 near text box. Click that and if the field it self represents the file name, then write this
{FieldName}|||Madhi,
I tried with your option. But when the report is run, the value stored in the field (ie) the file name comes without any hyperlink. No provision for clicking. Kindly guide me.

Meenakshi.R|||Which version of Crystal Report are you using?|||Sir,
The version of the crystal report is 8.0
Meenakshi.R|||Meenakshi,
I dont know where it goes wrong
Refer this (http://support.businessobjects.com/library/kbase/articles/c2006151.asp)|||Madhi,
Referred the article. Done as per the instructions. But still I am getting the field value only without the hyperlink. Any other solution?

Meenakshi.R|||I have a report where each record brings back a field with the path of a different image file. Each record in the details section prints out the word "Image" as a link, which I have set up as follows:

1. Created a formula which has nothing more in it than the word "Image". Placed into details section of report.
2. Click Format Field on this formula, go to Hyperlink tab. Choose 'A Website on the Internet', then open the formula box for Website Address.
3. In here, place the field from the database.

My images are available via a http server, but you may choose to use one of the other options there instead (eg 'A File' instead of 'A Website on the Internet'.|||Hai,
Created a formula. Placed in the detail section. Using format option, I have made a hyperlink by choosing the option Hyperlink from a file. With the formula button (hyperlink screen), I specified the field value. During run time, the formula name comes (but without hyperlink option). What to do?|||Should work, try debugging. I'd suggest copying the formula you are using to create the hyperlink into a regular formula and seeing what comes out, ie what it is trying to use to create this hyperlink. You might find that it has an invalid path, or is coming back blank, etc...|||Tried tried. I saved the report with field in the detail section by choosing "a file" from the hyperlink option, run the report, the field value stored in the field comes without hyperlink option. When viewing the report in design view, the hyperlink option shows 'a website on the internet'. I don't know why? pl. explain in detail.
Meenakshi.R|||My suggestion is that your file path is invalid. Test to see what CR thinks is in that field, for starters.

Ie, if you have a url stored in your database field and http:// already exists, you could end up with something like http://http://google.com

In my report, I can click on the field to open a file, provided that file path is correct. I typed C:\temp\test.txt into the File Name box. However, changing it to C:\temp\test1.txt simply does nothing as this file does not exist.

Also note that the field may be a hyperlink but will appear to be just a regular field until you mouse over. (ie, if you want it to appear as a hyperlink should then you will have to underline and colour it yourself).|||Sir,
I have created a fresh report and do all the things. When I click the preview button in Crystal report screen, I am able to click the hyperlink, and the corresponding file stored in the field gets opened. When this report is accessed via a VB program coding, simply the file name is displayed and I could not click the hyperlink. Pl. help me.

Meenakshi.R

Crystal 8.5 Summing question

In a one to many join How do you sum a field coming from the one table. example:
Table 1 has the following columns:
Employee id
check number
Gross pay
Table 2 has
Employee id
check number
ded code
ded amount
The data is as follows

employee id check number Gross Pay
1 1 100
1 2 100
1 3 100

employee id check number ded code ded amt
1 1 A 10
1 1 B 5
1 1 C 1

Each check would have the same 3 deductions.
When these tables are joined by employee id and check number you get a total of 9 records. If you wanted the total gross for the employee you can not just sum gross pay because you would get 900. The real gross for the employee should be 300.
I have been able to create a formula that gets the gross of 100 on one of the record and 0 on the other two records for each check, but Crystal will not let me sum that field.
Any help would be good.Try to create a Group on Employee ID so that it's not repeated. The records under an employee id will be displayed in Detail Section.

Create a summary field on gross pay and place it in Group Footer. this will returns the gross pay without duplication. To sum the check amout, create a separate sum field/running total field.

Hope this work.

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