Technical Questions
 Crystal Reports Forum : Crystal Reports 9 through 2020 : Technical Questions
Message Icon Topic: Select Expert data Post Reply Post New Topic
Page  of 2 Next >>
Author Message
irfan
Newbie
Newbie


Joined: 27 May 2009
Location: Canada
Online Status: Offline
Posts: 16
Quote irfan Replybullet Topic: Select Expert data
    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
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet 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.
IP IP Logged
irfan
Newbie
Newbie


Joined: 27 May 2009
Location: Canada
Online Status: Offline
Posts: 16
Quote irfan Replybullet Posted: 01 Jun 2009 at 11:32am

Can you please explain this in a little more detail.

IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet 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?
IP IP Logged
irfan
Newbie
Newbie


Joined: 27 May 2009
Location: Canada
Online Status: Offline
Posts: 16
Quote irfan Replybullet Posted: 01 Jun 2009 at 11:46am
Thanks a lot, it has helped me.
IP IP Logged
irfan
Newbie
Newbie


Joined: 27 May 2009
Location: Canada
Online Status: Offline
Posts: 16
Quote irfan Replybullet 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  
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet 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.
 
 


Edited by DBlank - 01 Jun 2009 at 1:25pm
IP IP Logged
irfan
Newbie
Newbie


Joined: 27 May 2009
Location: Canada
Online Status: Offline
Posts: 16
Quote irfan Replybullet 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 ?
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet 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?
 


Edited by DBlank - 01 Jun 2009 at 1:39pm
IP IP Logged
irfan
Newbie
Newbie


Joined: 27 May 2009
Location: Canada
Online Status: Offline
Posts: 16
Quote irfan Replybullet 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.
IP IP Logged
Page  of 2 Next >>
Post Reply Post New Topic
Printable version Printable version

Forum Jump
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum



This page was generated in 0.031 seconds.