Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
dlorenc
Posting Yak Master
172 Posts |
 Posted - 2010-07-26 : 14:12:50
|
| 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? |
|
|
bburton
Starting Member
1 Post |
Posted - 2010-07-27 : 08:34:07
|
| You could use: CASE WHEN CaseID IS NULL THEN '' ELSE CaseID END as 'CaseID' |
 |
|
|
dlorenc
Posting Yak Master
172 Posts |
Posted - 2010-07-27 : 10:56:52
|
| yes...I did try that...the problem I have is how the data comes into excel via the odbc data connector to sql...in a pivot table, there is a subtotal/grand total function..that can use either count or sum the detail records...i was using count.count was 'counting' NULL (not surprising for excel); but it was also counting a '' (double asterick); I also tried ' ' (blank)... I can only guess the '' was coming across as some ascii string that the pivot table still recognizes as a character (not an empty cell)....?..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 data in excel via the excel data connector