Crystal Report 2 tables in one report problem RSS

• • Reply
  

  Philip1914

  Member

  10 Points

  6 Posts

  Crystal Report 2 tables in one report problem

  Mar 16, 2012 08:39 AM|LINK

  Hi,

  Iam using VS2008

  i have developed a software in that i have to print a billing report

  i have designed the report

  the problem is, in that report i have to use two different tables

  one table is Sales entity(Grand Total) and other is SalesItem entity(Item name) i have a primary key Invoice_no used as foreign key in SalesItem

  I have writen a query

  select Sales.Grand_Total,SalesItem.Item_name from Sales,SalesItem where Sales.Invoice_no=SalesItem.Invoice_no where Invoice_no=100;

  the problem is when i run the report on button click only one table date is displaying and not other.

  i run the query in MSAcess no error works perfectly i need a solution for it.

  One thing i saw is  in crysatl report while using database expert if i import sales table first and then salesitem table then on running project sales table

  select Sales.Grand_Total,SalesItem.Item_name from Sales,SalesItem where Sales.Invoice_no=SalesItem.Invoice_no where Invoice_no=100;

  data is shown and not other and vice vers

• • Reply
  

  Sandeep Shen...

  Participant

  1678 Points

  492 Posts

  Re: Crystal Report 2 tables in one report problem

  Mar 16, 2012 08:42 AM|LINK

  hi

  select s.Grand_Total,si.Item_name from Sales s JOIN SalesItem si ON(Sales.Invoice_no=SalesItem.Invoice_no) where s.Invoice_no=100;

  Mark as Answer If the Post Helped you..
  Skype : sandeep.d.shenoy
  Gmail : sandeepdshenoy@gmail.com
• • Reply
  

  amit.jain

  Star

  11225 Points

  1815 Posts

  Re: Crystal Report 2 tables in one report problem

  Mar 16, 2012 09:06 AM|LINK

  refer crystal reports in asp.net

  Don't say thanks rather mark my reply as "Answer" if it helps you
  amiT jaiN
  
  ASP.NET C# VB Articles And Code Examples
  • Edited by amit.jain on Jul 13, 2012 10:11 AM
• • Reply
  

  Philip1914

  Member

  10 Points

  6 Posts

  Re: Crystal Report 2 tables in one report problem

  Mar 16, 2012 09:47 AM|LINK

  hi sandeep

  i tried ur query but it give an error

  "Synatx error in FROM clause"

  thanks

• • Reply
  

  Sandeep Shen...

  Participant

  1678 Points

  492 Posts

  Re: Crystal Report 2 tables in one report problem

  Mar 16, 2012 10:01 AM|LINK

  hi please try this..
  
  select s.Grand_Total,si.Item_name from Sales as s JOIN SalesItem as si
  ON s.Invoice_no=si.Invoice_no
  AND s.Invoice_no=100;

  Mark as Answer If the Post Helped you..
  Skype : sandeep.d.shenoy
  Gmail : sandeepdshenoy@gmail.com
  • Edited by Sandeep Shenoy on Mar 16, 2012 10:03 AM
• • Reply
  

  Philip1914

  Member

  10 Points

  6 Posts

  Re: Crystal Report 2 tables in one report problem

  Mar 16, 2012 10:20 AM|LINK

  hi

  its showing the same error

  i removed the aliases and tried but same error

  i tried using inner join,left and right join but it says "join expression not supported"

  thanks

• • Reply
  

  Sandeep Shen...

  Participant

  1678 Points

  492 Posts

  Re: Crystal Report 2 tables in one report problem

  Mar 16, 2012 10:23 AM|LINK

  hi,

  are you sure about the columns and the primary key connections ?

  change the fields name according to the need, the code is for joining two tables with a common field in both table,and to check a condition.

  Mark as Answer If the Post Helped you..
  Skype : sandeep.d.shenoy
  Gmail : sandeepdshenoy@gmail.com
• • Reply
  

  Philip1914

  Member

  10 Points

  6 Posts

  Re: Crystal Report 2 tables in one report problem

  Mar 16, 2012 10:23 AM|LINK

  hi amit

  i refered crystal report in asp.net

  that showed me how to use 2 tables in crytal report but not how to display data from 2 tables

  we need to write query

• • Reply
  

  Philip1914

  Member

  10 Points

  6 Posts

  Re: Crystal Report 2 tables in one report problem

  Mar 16, 2012 10:33 AM|LINK

  i checked its fine

  invoiceno is primary key in sales and foreign key in salesitem

• • Reply
  

  Sandeep Shen...

  Participant

  1678 Points

  492 Posts

  Re: Crystal Report 2 tables in one report problem

  Mar 16, 2012 10:40 AM|LINK

  hi

  Please check this code too.

  select s.Grand_Total,si.Item_name from Sales as s,SalesItem as si
  where s.Invoice_no=si.Invoice_no AND s.Invoice_no=100;

  Mark as Answer If the Post Helped you..
  Skype : sandeep.d.shenoy
  Gmail : sandeepdshenoy@gmail.com