Totals from SQL using data connectors

[dlorenc]

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?

 

[Fazza]

Hi,

If it is relevant, and it may not be, data doesn't need to be pulled into Excel and populated in a worksheet if all that is wanted is a pivot table - the pivot table can have its source data remotely.

I'm not 100% clear on the requirement. The field has either text or Null. It pulls in OK as either text or Null. When the pivot table counts the field, they are counted (correctly). You want the Nulls to be either blank or some value that is NOT counted. That is the bit I don't understand. Whether it is Null, blank or some other value it will be counted, unless they are filtered out. Just making it a different value won't stop it being counted; if it is in the data it must be counted (unless filtered out).

So, I'm thinking the solution is to filter them out.

Such as, when pulling from the database add a criteria to the SQL,
WHERE YourFieldName Is Not Null
(Or this could be put into the SQL for the pivot table.)

Or if you want to change the Null to something else, simplest might be
SELECT Iif(YourFieldName Is Null,'new entry',YourFieldName) AS [YourFieldName]

Or maybe filter out the Nulls in the pivot table field.

Or if I haven't got that right, please explain again. I think the bit I struggle with is how the data can be in the dataset but not counted in the pivot table. If you post some simple example it would help. Maybe a modification to the SQL can achieve what is wanted.

HTH

 

[dlorenc]

1. As you suggest, I am not pulling the data into the excel sheet, but using a data connector to the sql table.
2. once in excel a pivot table has the summation functions that will subtotal and grand total; I am using count for the subtotals and grand total.
3. As you mentioned, these count functions will count a NULL. This is what I do not want.
4. If I WERE to move the dataset into excel, and first replace the NULL's with "" (nothing), the pivot table would NOT count the "".... this is the behavior I want.
5. In sql, I tried to replace NULLS with '' (nothing, a double asterick)...and with a ' ' (asterick blank asterick)... I expect these are both coming across as some ascii characters (even tho they show in the pivot table as (nothing)...so the pivot subtotal/grand totals are still counting them...

thank you for your help!

 

[dlorenc]

I think I will have to do a new view..and add three new calculated fields (I actually have three fields that I need to count like this in the pivot table (taskid, ticketid, and changeID).

these three new fields would be set to 0 or 1 (bit field); taskidcnt, ticketidcnt and changeidcnt. then in my pivot table, I would to add the count field and use the sum function, instead of the count funtion..for the pivot table subtotal/grandtotals.....

 

[dlorenc]

oh, you asked to see the data... this is 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....

??

 

[Fazza]

Yes, the data table might be the problem. If the data is like this, is it good?

Manager Createdate ActivityType ID
hayes 2007-07-16 ChangeID CHG0022334
gitar 2007-07-20 TaskID TSK003234
rashid 2007-07-18 TicketID HD002342

Can you check that? If OK, it can be set up via a union query from the three tables. Something like below.

SELECT Manager, Createdate, 'ChangeID' AS [ActivityType], ChangeID AS [ID]
FROM tblChangeID
UNION
SELECT Manager, Createdate, 'TaskID' AS [ActivityType], TaskID AS [ID]
FROM tblTaskID
UNION
SELECT Manager, CreateDate, 'TicketID' AS [ActivityType], TicketID AS [ID]
FROM tblTicketID

I've only guessed as the structure of the source tables. If they contain records with Nulls, & I guess they don't, then exclude them in the SQL. So in each sub-part of the above SQL add a criteria WHERE whatever Is Not Null

If the SQL isn't quite right & you want help with that, please post some info.

 

[dlorenc]

exactly... you validated my current implementation...except I opted for a relational table..using integer types of 1,2,3..related to a table with the expanded text fields....

thank you very much for the assistance...!

 

[Fazza]

you're welcome