Excel as a front end to sql, data integrity

10 pts.
Tags:
Microsoft Excel database
SQL 2005
SQL Export to Excel
SQL Server
SQL to Excel Database Conversion
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.... ??



Software/Hardware used:
sql 2005, excel

Answer Wiki

Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Discuss This Question:  

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following