Print Page | Close Window

Select Expert data

Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Technical Questions
Forum Discription: Formulas, charting data, Crystal syntax, etc.
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=6532
Printed Date: 28 Apr 2024 at 6:48am


Topic: Select Expert data
Posted By: irfan
Subject: Select Expert data
Date Posted: 01 Jun 2009 at 11:05am
Hello,
 I am trying to choose data from a list in Select Expert. I cannot see all the records for this column. If I query for teh records outside in SQL, it retrives more records than those shown in Select Expert. Any reason for this ?
Please advise
 
Thanks
Fm


Replies:
Posted By: DBlank
Date Posted: 01 Jun 2009 at 11:31am
Table joins or an already applied portion select expert (having previewed the report with another select expert statement already functioning) come to mind.

Posted By: irfan
Date Posted: 01 Jun 2009 at 11:32am

Can you please explain this in a little more detail.

Posted By: DBlank
Date Posted: 01 Jun 2009 at 11:44am
If you have more than one table (or view or stored procedure) added to the report the type of join may filter the records returned into the report.
If your SQL query is only querying one table (no joined data) it will look at the full table, not just the records that meet the join condition.
 
Adding one part of a select expert at a time means:
Say you add in the first condition of Vendor.country="USA" and preview the report. Now the only records in the report will have this value.
If you go to add a second part to the select statement of: table.city = and you go to pull the values into it here, it will have limited these to only cities where the country = USA. If you use a SQL query to check your original table to see all of the cities it won't match unless you all add in the country=usa critieria to your slq query as well.
My guess is this is a join issue though...
Does this clear it up or do you need more info on one topic or the other?

Posted By: irfan
Date Posted: 01 Jun 2009 at 11:46am
Thanks a lot, it has helped me.

Posted By: irfan
Date Posted: 01 Jun 2009 at 12:58pm

Hello,

     You are right my report uses multiple tables and a stored procedure to query data from. My problem is when I use this main select(or Query) in SQL to see the records then I can see all the records. But when I use select expert or even browse data on a particular column to see all the values then I dont see all the values that I saw from the main select.

  I hope my question is clear . Please advise
 
Thanks
fm  

Posted By: DBlank
Date Posted: 01 Jun 2009 at 1:24pm

Let me give you a scenario and tell me if it applies to you. If not can you give me more specific info to assist you...

Example of 2 table; Employees and Sales.
Employees lists all of the companies employees.
Sales lists all the sales of each employee that has a sale.
The link between the two tables is on Employee ID.
Not all employees have sales.
If you inner join the tables (and query employees table after joining) you will now only be able to see employees from the employee table that have a corresponding sales record (e.g. none of the support employees or managers that do not do sales will appear).
If you left join employees table to the sales table you will get all of the employees from employees table.
However if you now add a select statement to your report of Sales.amount > 100.00 it will now render your left join useless because it is only going to select records where there is data in sales.
If you change your select statement to "isnull(sales.amount) or sales.amount > 100.00" now your left join works again to include all records from employees that do not match to sales at all and all the ones that match with sales amounts >100.
Hope this helps.
 
 

Posted By: irfan
Date Posted: 01 Jun 2009 at 1:33pm
Your example is a good one, its a standard one. But my question is why am I not seeing the data in the column's browse list or in select expert(again there is no condition here only equal to "empno"), I am sure its not using any join or conditions there. The join or conditions are in the report's main select, but I am able to see the data if I use the main select, So why only the column does not display this ?

Posted By: DBlank
Date Posted: 01 Jun 2009 at 1:39pm

Let me make sure I understand ...

If you use the GUI select expert you see all of the possible values for column A correctly.
If you right click on column A in the Field Explorer and select Browse Data you only see a partial listing of data that should appear.
Is this an accurate description of the problem you are having?
 

Posted By: irfan
Date Posted: 01 Jun 2009 at 1:42pm
Actually I do not see all possible values in both the GUI select expert as well as when I right click on Column A in the field explorer.
  My report uses a main Query to select data from an Oracle Database. I can see all possible values for coumn A in this query. Am I clear ?
Thanks for your help.

Posted By: DBlank
Date Posted: 01 Jun 2009 at 2:07pm

Are you seeing any values in either of the Crystal GUI's?  You state you can see all of the values "in this query", does this mean you see them all when when you run the SQL query outside of crystal?

Is this query that shows all of your data correctly a query that has all of your tables and joins that are being used in the crystal report or is it just querying one table or your stored proc alone? If you are querying them alone try and build the full SQL with all the tables and joins together exactly the same as your crystal report to see if you still have different results.
 
Are there any parameters in any of your oracle that Crystal is not catching but you are putting in on your SQL query that works.
Do you have more than one version of the DB and is the crystal ODBC pointing to the same DB version as your SQL query is that works?
 
Try "Database" > "Verify Database" option to make sure any changes to your Stored Proc (or tables or views) have been updated in the report as well.
 
Any of these work or explain the difference?

Posted By: irfan
Date Posted: 01 Jun 2009 at 2:10pm
Yes its the same query from the report that I am running outside of crystal reports and can see all the possible values. I will work on the parameters that I am hardcoding this query when I run it outside of Crystal. May be that could be the problem. I will keep you posted. Thanks again.

Posted By: DBlank
Date Posted: 01 Jun 2009 at 2:12pm
If it is looking for user defined parameters that might explain it.
Also make sure to use the Verify DB whenever you alter a Stored Proc or view. Crystal does not automatically update this if you have them open and working on them at the same time.

Posted By: irfan
Date Posted: 01 Jun 2009 at 2:17pm
I also think it to be an issue with the parameters but I will confirm. I always use the verify DB feature.

Posted By: irfan
Date Posted: 02 Jun 2009 at 10:29am

Hello,

 I am still struggling to figure out what is wrong with the select expert or browse on this column.

   One thing I noticed is , that this works correctly on our TEST server and not on PROD server, but I really dont think that there is much difference between PROD and TEST data as the data was refreshed recently. So where could the problem be ?
Please advise.
 
Thanks
 

Posted By: DBlank
Date Posted: 02 Jun 2009 at 1:20pm

Are all of your views and stored procs (especially if a view uses another view or your stored proc uses views) that the report uses exactly the same between the two servers?

Can't tell you how many times I have altered one of these in my test environment to get a report to work as intended but forgot to make the same changes to my live DB.

Posted By: irfan
Date Posted: 03 Jun 2009 at 3:24pm
Thanks for helping me out here, actually it turned out to be a registry problem. When we upgraded from Crystal Reports X to X1 ,there were some registry entries left which had to be deleted and the new registry entries had to be modified to increase time out and the number of rows that could be retreived. This worked. Once agan thanks for all your replies.


Print Page | Close Window