Custom Reporting Table with Boolean Column

Mark Fasel asked on November 17, 2015 21:40

Hi Everyone,

I am new to Kentico and really enjoy developing so far! I have exhausted all search efforts and thought I'd reach out to the community. I am creating a custom report (table to be exact) using the reporting built into Kentico. I have a custom query:

Select FirstName as [First Name], LastName as [Last Name], Email, Phone, StreetAddress as [Street Address], City, State, Country, Zip, Email, Phone, PaymentDate as [Payment Date], TransactionID as [Transaction ID], PaymentStatus as [Payment Status]
from TableName E
WHERE E.ID = 1 AND E.PaymentStatus = False
ORDER BY E.ItemCreatedWhen ASC

The issue that I find is that PaymentStatus is coming thru as a "Checkbox - unchecked or checked" instead of True or False. In the actual table and data it shows True/False. Is there any way around this? Thanks for your help!

reporting query

Recent Answers

Brenden Kehren answered on November 17, 2015 21:48

What is the field type you are reporting on? bit/boolean? It should return at least 1 or 0 but I'm guessing you are dealing with a string field.

0 votesVote for this answer Mark as a Correct answer

Mark Fasel answered on November 17, 2015 22:08

Hi Brenden, Thanks for reaching out and I remember seeing you a Kentico Connection, great stuff! The field in the custom table is a Boolean and form control is a checkbox.

Thanks!

1 votesVote for this answer Mark as a Correct answer

Brenden Kehren answered on November 17, 2015 22:10

When you run your query in SSMS does it does it execute properly?

0 votesVote for this answer Mark as a Correct answer

Mark Fasel answered on November 17, 2015 22:12

If i run it with out E.PaymentStatus = False it runs just fine, but if include it i get "Invalid Column Name" and no data found

0 votesVote for this answer Mark as a Correct answer