>>>>The report lists jobs. Each job can have multiple customers (so customers and some other info is in subreport). Each customer can have multiple phones but only one address.
>>>>
>>>>See attached for the report layout.
>>>>
>>>>I need to show phones close to the Address (the SubReport).
>>>
>>>Now I see what the issue is. Unfortunately, I cannot think of any ideas within Crystal that would solve it. The only idea I came up with was to create a local view in the database that joins the customer and phone tables so that the subreport can work off of the view instead.
>>
>>The report uses datasets.
>>
>>I can not think of any idea either except for pivoting the phones (assuming 5 phones as the maximum) and use these 5 fields in the report. That actually was the original data structure.
>>
>>Alternatively, I may try to create a CHAR(13) delimited AllPhones field using concatenation idea in SQL Server - this actually looks like a promising solution and should work well for my purposes - since I currently don't have extra DataSet for phones. This way I can simply add an extra field to Customers dataset.
>>
>>Thanks again - sounds like a plan.
>Hi, Naomi. If I understand correctly, you can create a dataset in a generic reporting business object (in other words, one not tied to a specific data table). From there, you can call a stored procedure that would create a dataset including both customer and phone data. Once you have that dataset in Crystal, you can group by customer to show a customer's phone numbers. You could do the same thing with a view, as suggested by Jon, and build a business object on the view to get your dataset.

Hi Linda,

In this particular case I believe the trick was to put customer's phones into one record (separated with CRLF).

Here is the SP I use to get all tables (individual datasets) needed for this report

USE [Squadware]
GO

/****** Object:  StoredProcedure [dbo].[getRouteScheduleReportData]    Script Date: 08/24/2009 08:49:04 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		Nadya Nosonovsky
-- Create date: 07/28/09	
-- Description:	Returns dataset for the Route Schedule Report
-- =============================================
ALTER PROCEDURE [dbo].[getRouteScheduleReportData] 
	-- Add the parameters for the stored procedure here
	@nCompanyID int = 1,
	@StartDate datetime = NULL, 
	@EndDate datetime = NULL
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	
	if @EndDate IS NULL
	 -- see http://www.karaszi.com/SQLServer/info_datetime.asp#GettingRidOfTimePortion
	   set @EndDate = DATEADD(day, DATEDIFF(day, '20040101', CURRENT_TIMESTAMP) + 1, '20040101') -- tomorrow
	
	if @StartDate is NULL
	   set @StartDate = dateadd(day,-2, @EndDate) 

	-- Route Header information
	declare @Hdr table (nRouteHeaderID bigint not null, 
	                    cDescription varchar(100) null, 
	                    dtRouteDate datetime null,
	                    dtRouteEnd datetime)
	
  insert into @Hdr 
  SELECT [nRouteHeaderID]
      ,[cDescription] 
      ,[dtRouteDate]     
      ,[dtRouteEnd]            
  FROM [Squadware].[dbo].[RouteHeader] 
  where [dtRouteDate] >= @StartDate and [dtRouteDate] < @EndDate and nCompanyID = @nCompanyID 
  order by dtRouteDate

select * from @Hdr
  
-- Cleaners 
  select Hdr.nRouteHeaderID, coalesce(vRUA.FirstName + ' ','') + 
  coalesce(vRUA.MiddleInitial + ' ','') + coalesce(vRua.LastName,'') as Cleaner
  from @Hdr Hdr INNER JOIN v_Route_Users_Available vRUA ON Hdr.nRouteHeaderID = vRUA.nRouteHeaderID
  where Hdr.dtRouteDate >=vRUA.dtStart and (Hdr.dtRouteDate <=vRUA.dtEnd OR vRUA.dtEnd IS NULL)


-- Equipment  
 select Hdr.nRouteHeaderID, vE.cEquipment as Description, 
                    vE.cAssetTag as AssetTag  from @Hdr Hdr
                      INNER JOIN v_Route_Equipment vE on Hdr.nRouteHeaderID = vE.nRouteHeaderID
 
 declare @Jobs table (IdField int identity(1,1), nRouteHeaderID bigint, nCustomerID bigint null, 
 nJobHeaderID bigint, SplitAmount decimal(12,2) null, 
 PermanentNote varchar(max), ScheduleTime datetime)
 insert into @Jobs  
 select Hdr.nRouteHeaderID, JH.nCustomerID,            
                     JH.[nJobHeaderID],
                     JS.nSubTotal as SplitAmount, 
                     mNote as PermanentNote, JS.dtScheduled as ScheduleTime
                     FROM   @Hdr Hdr 
                     INNER JOIN dbo.RouteDetail RD on Hdr.nRouteHeaderID = RD.nRouteHeaderID 
                      INNER JOIN
                      dbo.RouteDetailCategories RC ON 
                      RD.nRouteDetailCategoryID = RC.nRouteDetailCategoryID AND 
                      RC.cDescription = 'JobSplit' 
                      INNER JOIN dbo.JobSplits JS ON RD.nID = JS.nJobSplitID 
                      INNER JOIN dbo.JobHeader JH on JS.nJobHeaderID = JH.nJobHeaderID
                      order by JS.dtScheduled
-- Job Header
-- see discussion at http://forums.asp.net/p/1442148/3365052.aspx#3365052 and links from there

;WITH

-- -----------------------------------------------

-- Calcualtes sum of active taxes

-- for each of Tax Groups

-- -----------------------------------------------

Taxes as

(

  SELECT tg.nTaxGroupID             as nTaxGroupID,

         tg.cName                   as TaxGroupName,

         Sum(isNull(ti.nPercent,0)) as TaxPercent

    FROM TaxGroups                tg

    LEFT JOIN TaxGroupsToTaxItems tgi on tg.nTaxGroupID = tgi.nTaxGroupID

    LEFT JOIN TaxItems            ti  on tgi.nTaxItemID = ti.nTaxItemID

   WHERE tg.lActive = 1

   GROUP BY tg.nTaxGroupID,

            tg.cName

),

-- -----------------------------------------------

-- calculates total cost per Job

-- -----------------------------------------------

JDS as

(

  SELECT nJobHeaderID,

         Sum(nAmount * nQuantity) as TotalAmount

    FROM JobDetail

   GROUP BY nJobHeaderID

),

-- -----------------------------------------------

-- concatenates all Customer's phone numbers

-- into one string (LF delimited)

-- -----------------------------------------------

P as

(

  SELECT nCustomerID,

         Stuff(PhoneInfo, 1, 1, '') AS PhoneInfo      

    FROM ( SELECT InnerData.nCustomerID,

                  ( SELECT Char(10) + P.cPhoneNumber + ' - ' + rTrim(PT.cType)
                           
                      FROM PhoneNumbers   P

                     INNER JOIN PhoneType PT on P.nPhoneTypeID = PT.nPhoneTypeID

                     WHERE P.nCustomerID = InnerData.nCustomerID

                       FOR XML PATH('') -- this does string concatenation

                  ) as PhoneInfo

             FROM ( SELECT DISTINCT nCustomerID

                      FROM PhoneNumbers

                  ) as InnerData

         ) as OuterData

)          

SELECT J.nRouteHeaderID   as nRouteHeaderID, 

       JH.nCustomerID     as nCustomerID,

       JH.nJobHeaderID    as JobHeaderID,

       J.ScheduleTime     as ScheduleTime,

       JH.cContact        as Contact,

       J.SplitAmount      as SplitAmount,

       vJ.cAddr1          as Address1,

       vJ.cAddr2          as Address2,

       vJ.cCity           as City,

       vJ.cState          as State,

       vJ.cZip            as Zip,

       vJ.JobDescription  as JobDescription,

       JH.nDiscountPercent/100 * JDS.TotalAmount

                          as DiscountAmount,

       JH.nAmount         as JobTotal,

       T.TaxGroupName     as TaxType,

       T.TaxPercent       as TaxPercent,

       J.PermanentNote    as PermanentNote,

       P.PhoneInfo        as PhoneInfo

  FROM @Jobs               J

 INNER JOIN dbo.JobHeader JH  on J.nJobHeaderID   = JH.nJobHeaderID

  LEFT JOIN v_JobAddress  vJ  on J.nJobHeaderID   = vJ.nJobHeaderID

  LEFT JOIN Taxes         T   on JH.nTaxGroupID   = T.nTaxGroupID

  LEFT JOIN JDS           JDS on JH.nJobHeaderID  = JDS.nJobHeaderID

  LEFT JOIN P             P   on JH.[nCustomerID] = P.[nCustomerID]

 ORDER BY J.IdField -- Used for order but not retrieved



-- Job Details                      
select J.nJobHeaderID as JobID, JD.nQuantity as Quantity, JD.cDescription as Description, JD.nAmount as Amount
                      from @Jobs J                      
                      INNER JOIN dbo.JobDetail JD on J.nJobHeaderID = JD.nJobHeaderID
                      
 -- All notes combined
 select J.nRouteHeaderID, NN.mNote as RouteNote from @Jobs J 
 INNER JOIN (select N.nID as nCustomerID, mNote from Notes N 
 INNER JOIN NoteCategories NC on N.nNoteCategoryID = NC.nNoteCategoryID 
 and NC.cDescription = 'Customer') NN  
 ON J.nCustomerID = NN.nCustomerID
 UNION ALL
 select J.nRouteHeaderID, J.PermanentNote as RouteNote from @Jobs J 
 where J.PermanentNote IS NOT NULL and cast(J.PermanentNote as varchar(max)) <> ''
 UNION ALL
 select J.nRouteHeaderID, NN.mNote as RouteNote from @Jobs J 
 INNER JOIN (select N.nID as nJobHeaderID, mNote from Notes N 
 INNER JOIN NoteCategories NC on N.nNoteCategoryID = NC.nNoteCategoryID and NC.cDescription = 'JobHeader') NN  
 ON J.nJobHeaderID = NN.nJobHeaderID
   

END

GO

declare @StartDate datetime, @EndDate datetime
set @StartDate = dateadd(dd,-102,getdate())
set @EndDate = getdate()
select @StartDate
execute getRouteScheduleReportData 1, @StartDate,  @EndDate