Views not showing in Crystal Report

[former_member736866]

I have a crystal report that is connected to a IBM DB2 database that I am converting to a Microsoft database. When I do -> Database -> Set Datasource Location -> connect to the database and click the update button. When I try to map the fields only one column show and it should be two.

( I ran the query view in Mirosoft SQL Server Management Studio)

Here is the view data:

Here is what it shows in Crystal Report:

Can anyone help with why all columns are not showing in this view in crystal report?

[DellSC]

I suspect that the data types of the fields don't match up well. What type is the field in DB2 and what type is it in SQL Server?

-Dell

[former_member736866]

This the IBM Table:

Here is the SQL Sever:

[DellSC]

UserText is a nvarchar(max) field, which is actually a BLOB field that contains text instead of a basic varchar field type. That's why it's not showing up on the right. I might try converting the field to an nvarchar of a specific length in order to match up the report. Save the updated report and then change the field type back to nvarchar(max) and verify the database in the report.

If that doesn't work, you'll need to map the other field under Set Datasource Location. Crystal will then delete the unmapped field from the report and you'll just replace it on the report. Since the report only has two fields, this should be a fairly easy thing to do.

-Dell

[former_member736866]

Good morning dell.stinnett-christy when I used Set Datasource location and mapped the the field that was displaying the other dropped from the report like you said. It have the UserText as a memo type. The problem I have now is the field is being used in a group and I don't know how to add the updated field (UserText) to the group Footer.

[DellSC]

Yeah, Crystal won't let you use a memo field for a group. However, you could do one of two things to get this to work:

1. Modify the view to something like this:

Select
  [JobNumber],
  [UserText],
  Left([UserText], 50) as UserTextGroup
From [dbo].[AJ_D40]
Order by UserTextGroup

This will give you a field that isn't a memo that you can group on. You don't have to use 50 characters - it could be more or less based on your needs. It just has to be less than (max). This method would process more efficiently in Crystal than the next one because it pushes the processing to the database instead of doing it in memory in Crystal.

2. Create a formula in the report something like this:

Left({ViewName.UserText}, 50)

Then group on this formula. Again, you don't have to use 50 characters. This formula would then be processed in memory in Crystal to configure your groups.

-Dell