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 #

Posted on: 1/9/2019 11:02:30 PM
Replied by : support@icaresoftware.com

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,

www.icaresoftware.com/support/customreports

Leave a Comment / Question

Copyright © 2020 Orgamation Technologies, Inc. All rights reserved.