We need a report that will be used as a directory for each class and possibly the school: I'd like it to be able to have the following information and the ability to be sorted in all the different following categories: Name of Child Classroom Parents names street address Town phone #
Dear User,
NOTE: Since sorting is not available for Custom Reports, you may export the report in excel format and sort as per your requirements. Sorting custom report columns will soon be possible.
A Custom Report has been added to your account. Navigate to the iCare Report Center and run the custom report, Children Directory, under Children tab. Click the Export button to export the report data in excel format on your local machine.
Following query is used to produce the custom report as per your requirement,
--QUERY STARTS HERE--
Select distinct
ChildlastName+', '+childFirstName as 'Child Name', case when isnull(ccr.RoomId,0)=0 then 'Not Specified' Else rs.RoomName End as 'Room', Adultlastname+', '+AdultfirstName+' ' +'('+AdultRelationship+')' as 'Contact Name', case when isnull(Address+' '+City+' '+StateOrProvince+' '+ga.PostalCode,'')='' then '--' else Address+', '+City+', '+StateOrProvince+', '+ga.PostalCode end as 'Address', isnull( SUBSTRING(isnull( REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(CellularPhone, '+', ''), '(', ''), ')', ''), '-', ''), ' ', ''),''), 1, 3) + '-' +SUBSTRING(isnull( REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(CellularPhone, '+', ''), '(', ''), ')', ''), '-', ''), ' ', ''),''), 4, 3) + '-' +SUBSTRING(isnull( REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(CellularPhone, '+', ''), '(', ''), ')', ''), '-', ''), ' ', ''),''), 7, 4),'--') as 'Mobile Phone'
from cr_child cc
inner join cr_Child_Enroll ce on cc.child_id=ce.child_id and isnull(cc.RecordDeleted,0)=0
inner join cr_Child_Room ccr on cc.Child_Id=ccr.Child_Id
inner join po_RoomStructure rs on ccr.RoomId=rs.RoomId
inner join cr_childAdult cca on cc.child_id=cca.child_id
inner join Cr_adult ca on cca.Adult_id=ca.Adult_id and isnull(ca.RecordDeleted,0)=0 and isnull(cca.RecordDeleted,0)=0
inner join gn_Address ga on ca.Address_Id=ga.Address_Id
where
convert (date, StartingDate) <=
convert (date,getdate()) and (convert (date, (ce.DischargeDate)) >=convert (date, getdate())
or (DischargeDate < getdate())
or (DischargeDate is null))
--QUERY ENDS HERE--
Click here or below link to see the help article on using Custom Reports in iCare,
