I am creating a report in the ReportViewer however the SQL command I need to use for the Query is not supported by the TableAdapter PIVOT command.
Command Used
SELECT Descricao
, Codigo
, [012017] AS JANEIRO
, [022017] AS FEVEREIRO
, [032017] AS MARÇO
, [042017] AS ABRIL
, [052017] AS MAIO
, [062017] AS JUNHO
, [072017] AS JULHO
, [082017] AS AGOSTO
, [092017] AS SETEMBRO
, [102017] AS OUTUBRO
, [112017] AS NOVEMBRO
, [122017] AS DEZEMBRO
FROM (SELECT C.Descricao, C.Codigo, C.Competencia, SUM(Valor) as Total
FROM Salario AS C WHERE SUBSTRING(C.Competencia,3,4) = '2017'
AND Folha IN (SELECT idFolha FROM Folha WHERE mat = '****' and Servidor
IN (SELECT idServidor FROM Servidor WHERE cpf = '*******'))
GROUP BY C.VDDescricao,C.CodigoVD, C.VD, C.Competencia) SQ
PIVOT (SUM(Total) FOR Competencia IN ([012017],[022017],[032017],[042017],[052017],[062017],[072017],[082017],[092017],[102017],[112017],[122017])) AS PT
ORDER BY Codigo
I have created a list with the Return of this Query, but the ReportViewer does not seem to support the passing list of objects for web projects.