Reporting - how to sum boolean fields (check boxes)

(0) Share

Report

Posted on by Community Member

Hi all,

We have a phone call form that captures what the call is about using check boxes, which allows the user to select multiple options. There is also an option set that allows them to select the type of caller.

The users need a simple report summarizing what the phone call was about by the caller type. so something like:

Caller Type	Activity Count	DBS Filtering	DBS Other
Employer	16	0	7
Service user	435	45	123
Practitioner	68	12	23

Now it seems i cant sum and count these within CRM so i pulled the relevant fetch XML data using advanced query, converted to SQL and manipulated the SQL to pull the required data. The query looks as below:

The problem i have is that as SQL cant sum a boolean i have to use CAST to get the desired results but this then is not compatable to convert back to fetch xml for me to use in visual studio.

Any help on how to create what seems a simple report would be appreciated.

*This post is locked for comments

I have the same question (0)

All responses (7)

Answers (1)

Suggested answer

Mahadeo Matre 17,021 on at

Like (0)

Report
try your query in this way..

SELECT PhoneCall.new_callerType) as "CallerTyper" , COUNT(PhoneCall.ActivityId) as "Activtiyid" , SUM(CASE PhoneCall.new_initialenquiryDBSOther WHEN 'FALSE' THEN 0 WHEN 'TRUE' THEN 1 END) AS "DBSOther" , SUM(CASE PhoneCall.new_initialenquiryDBSFiltering WHEN 'FALSE' THEN 0 WHEN 'TRUE' THEN 1 END) AS "DBSFiltering" , SUM(CASE PhoneCall.new_initialenquiryDBSeigiblity WHEN 'FALSE' THEN 0 WHEN 'TRUE' THEN 1 END) AS "DBSeigiblity" , SUM(CASE PhoneCall.new_initialenquiryDBSbasicDisclosure WHEN 'FALSE' THEN 0 WHEN 'TRUE' THEN 1 END) AS "DBSbasicDisclosure" , SUM(CASE PhoneCall.new_initialenquiryDBSbarring WHEN 'FALSE' THEN 0 WHEN 'TRUE' THEN 1 END) AS "DBSbarring" FROM PhoneCall (NOLOCK) WHERE ( phonecall.CreatedOn>='2015-03-22' AND phonecall.createdon<='2015-04-21' AND phonecall.new_callerType IS NOT NULL ) GROUP BY phoecall.new_callerType

Hope this will help..

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Thanks Mahadeo but it seems case is not supported in fetchXML either. Can i create the report in visual studio using a SQL connection rather than FetchXML? Will i still be able to import into CRM?

Was this reply helpful? Yes No
Mahadeo Matre 17,021 on at

Like (0)

Report

Yes crm supports sql reports..only you need to use filteref views

Was this reply helpful? Yes No
Verified answer

Mahadeo Matre 17,021 on at

Like (0)

Report

Here are some links for SQL based SSRS report for MS CRM .

msdn.microsoft.com/.../gg328097.aspx

technet.microsoft.com/.../dn920281.aspx

technet.microsoft.com/.../dn531183.aspx

crmbook.powerobjects.com/.../advanced-reporting-options

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Thanks. Ive managed to do this using SQL based reports now.

Terry

Was this reply helpful? Yes No
suresh maurya 630 on at

Like (0)

Report

For testing of FetchXML, you can test here online http://msxrmtools.com

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Hi All,

There's an even easier way to do this- all in Fetch XML with so SQL required.

Simply bring the data into SSRS using your FetchXML query

Then create a table control- insert an expression along the following lines- iif is an SSRS function which is equivalent to Case in T-SQL.

=sum(iif(Fields!MyBoolValue.Value=true,1,0))

Syntax is basically iif(booleantest,valueiftrue,valueiffalse)- You'll also need to group on a variable in your query- might be the caller type in your case) in the table. You can hide the individual values and only show the Group-ed by total if required.

Hope that helps

john

Was this reply helpful? Yes No