That's quite a report!


>>>>>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
>