SQL connection missing table fields
Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Data Connectivity
Forum Discription: How to connect to data sources and export reports
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=17313
Printed Date: 18 May 2024 at 12:48pm
Topic: SQL connection missing table fields
Posted By: benwsear
Subject: SQL connection missing table fields
Date Posted: 14 Aug 2012 at 5:25am
|
Hello all. I'm new to Crystal XI and am trying to connect to a dataset to run some reports from it. The problem is I can connect to the tables but some fields are not present in my Field Explorer.
The connection I am using is called 'OLE DB ADO - Microsoft OLE DB Provider for SQL server' and is connecting to a Microsoft Dynamics NAV system. The fields that are missing are classed as decimal data type in NAV. However, some other decimal types are appearing as numbers etc. Is this a problem with Crystal XI not being able to see decimal field types? Or is it something else? I can't really change the field types in NAV to number (or something like that) as it is a huge system and I am only tasked with reporting from it. Has anyone got any ideas about how to see the missing fields in Crystal? Any help would be much appreciated. P.S. I'm using Crystal 11 v 11.0.0.1282 and my connection is direct to the data source using the above SQL provider. Many thanks for any help in advance. Ben. |
Replies:
Posted By: kevlray
Date Posted: 14 Aug 2012 at 6:35am
| The only times I have had an issue with seeing fields in CR is if there is an security issue with the database. I wish I had a better answer. |
Posted By: hilfy
Date Posted: 14 Aug 2012 at 11:31am
|
Usually the OLEDb driver for SQL Server works best, but might want to try to create an ODBC connection to the database and determine whether you can see the fields using that instead of the OLEDb connection. -Dell ------------- Proviti, Data & Analytics Practice http://www.protiviti.com/US-en/data-management-advanced-analytics - www.protiviti.com/US-en/data-management-advanced-analytics |
Posted By: benwsear
Date Posted: 14 Aug 2012 at 8:28pm
|
Thanks.
Tried the ODBC connection and still can't see the relevant table fields. Quite baffled now. Very bizarre. Thanks again. |
Posted By: hilfy
Date Posted: 15 Aug 2012 at 4:21am
|
What version of SQL Server are you connecting to? IIRC, there were some new field types added to SQL Server in the latest release and Crystal XI is older software that may not recognize the field type. -Dell ------------- Proviti, Data & Analytics Practice http://www.protiviti.com/US-en/data-management-advanced-analytics - www.protiviti.com/US-en/data-management-advanced-analytics |
Posted By: benwsear
Date Posted: 15 Aug 2012 at 4:27am
|
Originally posted by hilfy
What version of SQL Server are you connecting to? IIRC, there were some new field types added to SQL Server in the latest release and Crystal XI is older software that may not recognize the field type. -Dell Hello Dell. Our SQL Server is version 2008. When I connect through Crystal XI though, I have to connect through the Microsoft Provider that I noted in my first post. The SQL and the tables/system are held on a their own server that I connect to using the above method. I can't really plug directly into the tables. Thanks for your help. I'm quite new to this so please bear with me. :) |
Posted By: hilfy
Date Posted: 15 Aug 2012 at 4:46am
|
The provider may include the fields but if Crystal doesn't recognize the field type it won't display them. Can you connect to the database using SQL Server Management Studio or another tool and look at the structure of the table to see what type the missing fields are? -Dell ------------- Proviti, Data & Analytics Practice http://www.protiviti.com/US-en/data-management-advanced-analytics - www.protiviti.com/US-en/data-management-advanced-analytics |
Posted By: benwsear
Date Posted: 15 Aug 2012 at 4:48am
|
Originally posted by hilfy
The provider may include the fields but if Crystal doesn't recognize the field type it won't display them. Can you connect to the database using SQL Server Management Studio or another tool and look at the structure of the table to see what type the missing fields are? -Dell Thanks. I connected to the table in question directly in NAV and it said the field type was 'Decimal'. |
Posted By: hilfy
Date Posted: 15 Aug 2012 at 5:01am
|
I believe that is one of the new field types. How much control do you have in the database? How are your SQL skills? Can you create a view? If so, you could create a view that just returns the data from the table but casts the decimal fields as numbers. If not and your SQL skills are good, you could create a command (SQL Select statement) in Crystal that will pull all of the data for your report, casting the decimal fields as numbers. -Dell ------------- Proviti, Data & Analytics Practice http://www.protiviti.com/US-en/data-management-advanced-analytics - www.protiviti.com/US-en/data-management-advanced-analytics |
Posted By: benwsear
Date Posted: 15 Aug 2012 at 8:34pm
|
Thanks again Dell.
My SQL skills are very, very basic. However, I do work very closely with two SQL developers so they can lend me a hand with this. I have been looking at Crystal Reports 2011. Do you know if this would handle a problem like this better? Our company has been very slow on the uptake with Crystal and we're still moving away from 8.5!! Also, do you know if the 2011 is different enough to XI to not update it on an install? The reason I ask is because we have 8.5 and XI sat beside each other on the same machine, and they don't seem to affect each other. However, I'm not sure if the same could be said with XI and 2011. Thanks. |
Posted By: hilfy
Date Posted: 16 Aug 2012 at 3:32am
|
Yes, 2011 will install side-by-side with existing versions of Crystal. I'm not sure whether those new field types are available in 2011, but they very well might be. It would be worth downloading a demo to test it. (Be sure to get Crystal 2011 - NOT Crystal for Enterprise!) -Dell ------------- Proviti, Data & Analytics Practice http://www.protiviti.com/US-en/data-management-advanced-analytics - www.protiviti.com/US-en/data-management-advanced-analytics |
Posted By: benwsear
Date Posted: 16 Aug 2012 at 9:04pm
|
Thanks Dell.
Downloaded trial version of 2011 and still couldn't see the fields that were missing in XI. It looks like whatever version I use I cannot see these fields. 
Thanks for your help. |
Posted By: benwsear
Date Posted: 03 Oct 2012 at 8:29pm
|
Thanks for your help all. Found out the problem. The fields that we were seeing in the tables aren't actually there. They are generated when you view the details in Navision.
I'm quite new to this Crystal stuff so anyone with any experience would have noticed this ages ago. Now I know where not to look, it's a case of finding out where to look. Thanks for your help everyone. :) |