I've configured a block in Invantive Control which downloads data using the query:

select sii.QuotationNumber
,      sie.QuotationNumber
,      sii.quotationDate
,      sii.DueDate
,      sii.InvoiceAccountCode
,      sii.InvoiceAccountName
,      sii.StatusDescription
,      sie.Description
,      sie.Quantity
,      Currency
,      sie.NetPrice
,      sie.amountDC
,      sie.UnitCode
,      month(sii.DueDate) monthclosedate
,      year(sii.DueDate) yearclosedate
,      datepart('week', sii.DueDate) weekclosedate
from   exactonlinerest..QuotationLines sie
join   exactonlinerest..Quotations sii
on     sii.QuotationNumber = sie.QuotationNumber
where  sii.StatusDescription = 'Afgedrukt'
       sii.StatusDescription = 'Open'
by     sii.CloseDate

A named range Sie_Border and Sie_Data have been specified in the block and these are created. Above the data I've put a header with the labels of the fields.

The resulting pivot table structure is:

Pivot table

However, I would expect the fields such as "QuotationNumber" to be used for the Pivot table.

How can I get the right labels for the Pivot table?


The data in the Pivot structure shows what seems to be actual data in the rows of the block instead of the labels above the data.

Please note that when you base the Pivot table on an Excel named range such as Sie_Border, that the named range must include the labels on top of it. So for instance, when the Excel row with number 1 contains the labels, the named range should start at row 1, like for instance $a$1:$z$999. The Pivot table in this case will take the labels from row 1 and the data from row 2 to 999.

The named ranges maintained by Invantive Control will automatically include labels by setting the border size. For instance, when you have the labels above the data, you would set:

setting border

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.