how do I put a value in a sql field that will not be counted in a excel pivot table? I am using excel as a front-end to sql. In particular, I am using pivot tables to slice and dice sql tables. The field I am interested in has either a text value or a NULL: CaseID (varchar (15), null). When I pull the data into excel the values in the pivot table are exactly that: NULL or the 15 char CaseID. But when I do a count (to sum the number of cases) in a pivot table, the count of CaseID includes the NULL records... is there a value I can put in the sql table, such that it will come into excel as a blank field and NOT be counted in the field summation of pivot tables?
so, my question really is, "Is there a way to load the sql fields with something that comes across into excel as a true empty cell?" The data looks like this, a union of three tables (change orders, task orders, and help desk tickets). So the three types of record would look like this: KeyID ChangeID TaskID TicketID Manager Createdate ....etc... 1 CHG0022334 NULL NULL hayes 2007-07-16 .... 2 NULL TSK003234 NULL gitar 2007-07-20 .... 3 NULL NULL HD002342 rashid 2007-07-18 ... so when I do my pivot I want to slice and dice the three types based on the analysis needed, eg. "How many change/task tickets were assigned to rashid in 2nd quarter?" perhaps my problem is in the dataset itself...instead of the three new counter fields, maybe I just need a field to indicate the type of record..then use as a selector in the pivot table?.. RecordType varchar (16) values would be set to Change Order; Task Order; or Help Desk..nulls are not allowed.... ??
sql 2005, excel
July 27, 2010 6:00 PM
July 27, 2010 6:29 PM