formats and imports

[jmoe007]

<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o> </o>
Hello All. <o></o>
having some issues with my statment and/or import.<o></o>
<o> </o>
Using a statment to count the number of rows by type and then format the count of rows into a number.<o></o>
Issue Im having is when i load my sql statment into excel and run my query, the count of row column comes down as a text, which i have to format into number to be able to pivot table the data. but when i refresh my query, the row count column reverts back to a text (general) format and lose the ability to use my pivot table.<o></o>
<o> </o>
End result is to have my pivot table update when refreshed using my statement below<o></o>
<o> </o>
is there a way to code my sql statment so the row count is allready cast as interger when i run my query in excel or is there a way in excel to keep the column format as number.<o></o>
<o> </o>
SELECT <o></o>
<o> </o>
A.AsofDate,<o></o>
datename(weekday,DATEPART(weekday,AsofDate))as Weekday,<o></o>
A.NAME as List,<o></o>
cast(COUNT(NAME) as int)AS Listcount<o></o>
<o> </o>
FROM <o></o>
[x].[dbo].[x] as A<o></o>
<o> </o>
Where <o></o>
AsofDate>=DateAdd(day, DateDiff(day,0,getdate()),-30)<o></o>
<o> </o>
group by <o></o>
A.AsofDate,A.NAME<o></o>

 

[xenou]

Count does return a number. There is no need to cast it to int.

I would rebuild the pivot table in a clean sheet (safest). You probably got a text value in there once by mistake and now its "stuck" that way. Or for a quick fix, you could *try* removing all data from the pivot table and refreshing the empty pivot table. Then put the items back in and see if the numeric values stay. Be sure the sheet is not using text format in any cells. Check the number format of the pivot table columns to make sure they aren't in text format either.