Custom Reporting Table Query Boolean Column

Question

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!

Answer 1

I get the same checkbox, you can get a text value instead by wrapping your boolean (bit) field in a CASE. Your Report table Query would look like this;

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], 
CASE WHEN PaymentStatus = 0 THEN 'False' 
    ELSE 'True' 
END as [Payment Status]
FROM    TableName E
WHERE   E.ID = 1
        AND E.PaymentStatus = False
ORDER BY E.ItemCreatedWhen ASC