Morning All,

I dont know whether this is a BIC or Excel query? (That's why it is on both)

I have written my first BIC report, which gives me a pivot table of all cost centres and there sales by Period (Jan09, Feb09 etc.). The pivot gives me all the targets achieved, but I need to add monthly commitments as well as totals by quarter.

In the past I used to go and link the cell to the pivot (=getpivotdata), but surely there is an easier way of doing it. By linking manually there is always room for error.

What formula can I use to lookup the value by cost centre (keeping in mind that the cost centre codes in the pivot is number, where as the cost centres on my report is branch names). The formula has to look at a specific month and put it in the correct cell on my report.

Another problem is that I would like to link all the cells when I design the report, even if my pivot table does not have for instance an December sales figures yet, but if it did, it should be the value in, otherwise return a zero to ensure not #Ref errors..

Please assist.

Thanks!

Catrien

Depends on what you are trying to accomplish. Where are you sourcing your monthly commitments and totals by quarter data from?


I assume you are saying that you want to see all months in the pivot regardless of it containing data. Where is the date data being sourced from and what is the current expression used? You would need to change your Month source expression to include all months regardless of them containing values or not.

An example solution would be something like the following:

SELECT *
FROM
(SELECT DISTINCT 'January' AS MTH FROM [_bvARTransactionsFull] WHERE 1=1
UNION ALL
SELECT DISTINCT 'February' AS MTH FROM [_bvARTransactionsFull] WHERE 1=1
UNION ALL
SELECT DISTINCT 'March' AS MTH FROM [_bvARTransactionsFull] WHERE 1=1
UNION ALL
SELECT DISTINCT 'April' AS MTH FROM [_bvARTransactionsFull] WHERE 1=1
UNION ALL
SELECT DISTINCT 'May' AS MTH FROM [_bvARTransactionsFull] WHERE 1=1) AS MONTHS
LEFT JOIN [_bvARTransactionsFull]
ON
[MONTHS].[MTH] = CAST(DATENAME(Month,[_bvARTransactionsFull].[TxDate])AS VARCHAR)


This example only runs Jan to May thus you would need to add in the remaining months as per above to obtain the result you require. Your month expression would then source from [MONTHS].[MTH]

BTW creating a JOIN based on a CAST etc. is not really efficient SQL coding but worth playing with.