Pivot Table from SQL tabel - how to work with boolean fields

[neFAST]

Dear Excel gurus,
I'm fairly new to pivot table and I'm trying to show some statistics from data stored in a MSSQL table. It's working great so far except that I have a lot of PASS/FAIL columns that contain either TRUE or FALSE (type is 'bit' in MSSQL) and I would like to display a PASS percentage.

I've tried adding calculated fields to convert this to 0/1 but I'm hitting a wall. Google did not help neither.
Is there a way I can convert booleans?

[Norie]

Have you tried converting TRUE/FALSE to 1/0 in a query based on the MSSql table?

[neFAST]

Hi, thanks for looking into my issue.
Unfortunately I can't alter the MSSQL table, if this is one you mean.

[Norie]

I'm not suggesting you alter the table, I'm suggesting you use a query to get the data from the table and in that query convert the TRUE/FALSE field(s) to 1/0.

How are you currently getting the data from table?

[neFAST]

Yes, but I'm doing this with the Excel "wizard" (wihen adding a data source 'from SQL server') so I did now wrote the SQL query by myself.
How can I modify the query?

[Norie]

Normally you would use CAST,

SELECT column1, column2, ..., CAST(bitcolumn AS INT)
FROM MyTable

not sure how that would work with the wizard.

[neFAST]

So I should probabyl create a new Connection. But how can I create a SQL connection that will let me input the query?

[Norie]

Do you not get the option to return the data to MS Query?

Or when you look at the query properties is there not a field for CommandText, or something similar?

PS Not used SQL Server with Excel for a while, hence the vagueness.

[neFAST]

Ok I've managed to work something out. Thanks for your help
Is there a simpler solution that addin the CAST command to all my collumns? (simpler query)
Also it looks like th PowerPivot addin can handle data conversion on the fly, are there other free addin that would do that?

[Norie]

You could do the conversion in Excel with simple formulas adjacent to the raw data and then you could use the results of those formulas as the base for the pivot table.