I used Common Table Expressions to write a pivot table so I can dynamically capture current year in displaying results.
For instance, each of our members is required to pay annual dues each year.
So, to display current dues year, we use something like this:
Member Name, Date Registered, Amount Owed, Amount Paid, Remaining Balance 2019 2020 2021.
Jane Doe 07/01/2019 120.00 70.00 50.00 50.00 0.00 0.00
This works just fine.
Where we are running into trouble is when we use reporting services (using visual studio report viewer) to display this results.
I am getting error that the years (2019, 2020, 2021) are not valid field names.
I even tried using [] as in [2019]… but still no love.
Any work around to this?
Any assistance is greatly appreciated.
USE [myDB]
GO
/****** Object: StoredProcedure [dbo].[uspGetPivotedData]
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF OBJECT_ID('MemberHistory', 'U') IS NOT NULL
DROP TABLE MemberHistory;
GO
ALTER PROCEDURE [dbo].[uspGetPivotedData]
AS
DECLARE @cols AS NVARCHAR(MAX), @query AS NVARCHAR(MAX);
SET @cols = STUFF((SELECT DISTINCT ',' + QUOTENAME(YEAR(t.EventYear))
FROM Transactions t
FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'),1,1,'');
set @query = N'WITH CTE AS
(
SELECT
t.TransactionID,
m.memberName,
m.date_registered,
envelopeNumber,
registrationFee,
Amount,
YEAR(eventyear) eventyear,
CONVERT(varchar, CAST(t.AmountPaid AS money), 1) AS AmountPaid,
t.Balance
FROM
dbo.Transactions AS t
INNER JOIN dbo.Members AS m
ON t .MemberID = m.MemberID
INNER JOIN dbo.PaymentTypes AS p
ON t .TypeID = p.PaymentTypeID
)
SELECT
TransactionID,
memberName,
date_registered,
envelopeNumber,
CONVERT(varchar, CAST(registrationFee AS money), 1) AS registrationFee,
Amount,
' + @cols + N',
CONVERT(varchar, CAST(Balance AS money), 1) AS Balance
INTO MemberHistory
FROM
CTE
PIVOT
(
MAX(AmountPaid)
FOR eventyear IN (' + @cols + N')
) p
ORDER BY
TransactionID';
EXECUTE sp_executesql @query;EXECUTE sp_executesql @query;
After testing the similar data in my page based on your needs, I found that in ssrs
data format is not allowed as field name such as your fields [2017],][2018],[2019] etc.
I recommend that you when you pivot year to fields, you should convert it to varchar format, then in the dataset fields , you should change the number FieldName to string type like below:
Best Regards,
YongQing.
MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue.
If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.
Member
251 Points
1140 Posts
How can I use Number as field name in reporting services?
Sep 11, 2019 08:31 PM|simflex|LINK
Greetings experts,
I used Common Table Expressions to write a pivot table so I can dynamically capture current year in displaying results.
For instance, each of our members is required to pay annual dues each year.
So, to display current dues year, we use something like this:
This works just fine.
Where we are running into trouble is when we use reporting services (using visual studio report viewer) to display this results.
I am getting error that the years (2019, 2020, 2021) are not valid field names.
I even tried using [] as in [2019]… but still no love.
Any work around to this?
Any assistance is greatly appreciated.
USE [myDB] GO /****** Object: StoredProcedure [dbo].[uspGetPivotedData] SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF OBJECT_ID('MemberHistory', 'U') IS NOT NULL DROP TABLE MemberHistory; GO ALTER PROCEDURE [dbo].[uspGetPivotedData] AS DECLARE @cols AS NVARCHAR(MAX), @query AS NVARCHAR(MAX); SET @cols = STUFF((SELECT DISTINCT ',' + QUOTENAME(YEAR(t.EventYear)) FROM Transactions t FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'),1,1,''); set @query = N'WITH CTE AS ( SELECT t.TransactionID, m.memberName, m.date_registered, envelopeNumber, registrationFee, Amount, YEAR(eventyear) eventyear, CONVERT(varchar, CAST(t.AmountPaid AS money), 1) AS AmountPaid, t.Balance FROM dbo.Transactions AS t INNER JOIN dbo.Members AS m ON t .MemberID = m.MemberID INNER JOIN dbo.PaymentTypes AS p ON t .TypeID = p.PaymentTypeID ) SELECT TransactionID, memberName, date_registered, envelopeNumber, CONVERT(varchar, CAST(registrationFee AS money), 1) AS registrationFee, Amount, ' + @cols + N', CONVERT(varchar, CAST(Balance AS money), 1) AS Balance INTO MemberHistory FROM CTE PIVOT ( MAX(AmountPaid) FOR eventyear IN (' + @cols + N') ) p ORDER BY TransactionID'; EXECUTE sp_executesql @query;EXECUTE sp_executesql @query;Many thanks in advance
Contributor
3690 Points
1043 Posts
Re: How can I use Number as field name in reporting services?
Sep 12, 2019 08:19 AM|Yongqing Yu|LINK
Hi simflex,
According to your description, for this error message, I recommend you change table control to martix control to solve it.
How to use martix in ssrs, you can refer to this link :
Create a Matrix (Report Builder and SSRS)
Best Regards,
YongQing.
Please remember to click "Mark as Answer" the responses that resolved your issue.
If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.
Member
251 Points
1140 Posts
Re: How can I use Number as field name in reporting services?
Sep 12, 2019 02:53 PM|simflex|LINK
YongQing,
Thanks very much for your response.
The same error exists whether I use matrix/Tablix or not.
Sorry to post the long code but this is where the error is occurring.
<?xml version="1.0" encoding="utf-8"?> <Report xmlns="http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition" xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner"> <Body> <ReportItems> <Textbox Name="Textbox15"> <CanGrow>true</CanGrow> <KeepTogether>true</KeepTogether> <Paragraphs> <Paragraph> <TextRuns> <TextRun> <Value>APO Summary Report</Value> <Style> <Color>Pink</Color> </Style> </TextRun> </TextRuns> <Style> <TextAlign>Center</TextAlign> </Style> </Paragraph> </Paragraphs> <rd:DefaultName>Textbox15</rd:DefaultName> <Top>0.22444in</Top> <Left>4.05083in</Left> <Height>0.25in</Height> <Width>1.625in</Width> <Style> <Border> <Style>None</Style> </Border> <BackgroundColor>DarkGreen</BackgroundColor> <PaddingLeft>2pt</PaddingLeft> <PaddingRight>2pt</PaddingRight> <PaddingTop>2pt</PaddingTop> <PaddingBottom>2pt</PaddingBottom> </Style> </Textbox> <Tablix Name="Tablix3"> <TablixCorner> <TablixCornerRows> <TablixCornerRow> <TablixCornerCell> <CellContents> <Textbox Name="Textbox28"> <CanGrow>true</CanGrow> <KeepTogether>true</KeepTogether> <Paragraphs> <Paragraph> <TextRuns> <TextRun> <Value /> <Style /> </TextRun> </TextRuns> <Style /> </Paragraph> </Paragraphs> <rd:DefaultName>Textbox28</rd:DefaultName> <Style> <Border> <Color>LightGrey</Color> <Style>Solid</Style> </Border> <BackgroundColor>DarkGreen</BackgroundColor> <PaddingLeft>2pt</PaddingLeft> <PaddingRight>2pt</PaddingRight> <PaddingTop>2pt</PaddingTop> <PaddingBottom>2pt</PaddingBottom> </Style> </Textbox> </CellContents> </TablixCornerCell> <TablixCornerCell> <CellContents> <Textbox Name="Textbox49"> <CanGrow>true</CanGrow> <KeepTogether>true</KeepTogether> <Paragraphs> <Paragraph> <TextRuns> <TextRun> <Value>Transaction ID</Value> <Style /> </TextRun> </TextRuns> <Style /> </Paragraph> </Paragraphs> <rd:DefaultName>Textbox49</rd:DefaultName> <Style> <Border> <Color>LightGrey</Color> <Style>Solid</Style> </Border> <PaddingLeft>2pt</PaddingLeft> <PaddingRight>2pt</PaddingRight> <PaddingTop>2pt</PaddingTop> <PaddingBottom>2pt</PaddingBottom> </Style> </Textbox> </CellContents> </TablixCornerCell> </TablixCornerRow> </TablixCornerRows> </TablixCorner> <TablixBody> <TablixColumns> <TablixColumn> <Width>1.33135in</Width> </TablixColumn> <TablixColumn> <Width>1.37302in</Width> </TablixColumn> <TablixColumn> <Width>1.33135in</Width> </TablixColumn> <TablixColumn> <Width>1in</Width> </TablixColumn> <TablixColumn> <Width>1.33135in</Width> </TablixColumn> <TablixColumn> <Width>1.33135in</Width> </TablixColumn> </TablixColumns> <TablixRows> <TablixRow> <Height>0.25in</Height> <TablixCells> <TablixCell> <CellContents> <Textbox Name="date_registered1"> <CanGrow>true</CanGrow> <KeepTogether>true</KeepTogether> <Paragraphs> <Paragraph> <TextRuns> <TextRun> <Value>=Fields!date_registered.Value</Value> <Style /> </TextRun> </TextRuns> <Style /> </Paragraph> </Paragraphs> <rd:DefaultName>date_registered1</rd:DefaultName> <Style> <Border> <Color>LightGrey</Color> <Style>Solid</Style> </Border> <PaddingLeft>2pt</PaddingLeft> <PaddingRight>2pt</PaddingRight> <PaddingTop>2pt</PaddingTop> <PaddingBottom>2pt</PaddingBottom> </Style> </Textbox> </CellContents> </TablixCell> <TablixCell> <CellContents> <Textbox Name="envelopeNumber1"> <CanGrow>true</CanGrow> <KeepTogether>true</KeepTogether> <Paragraphs> <Paragraph> <TextRuns> <TextRun> <Value>=Fields!envelopeNumber.Value</Value> <Style /> </TextRun> </TextRuns> <Style /> </Paragraph> </Paragraphs> <rd:DefaultName>envelopeNumber1</rd:DefaultName> <Style> <Border> <Color>LightGrey</Color> <Style>Solid</Style> </Border> <PaddingLeft>2pt</PaddingLeft> <PaddingRight>2pt</PaddingRight> <PaddingTop>2pt</PaddingTop> <PaddingBottom>2pt</PaddingBottom> </Style> </Textbox> </CellContents> </TablixCell> <TablixCell> <CellContents> <Textbox Name="registrationFee1"> <CanGrow>true</CanGrow> <KeepTogether>true</KeepTogether> <Paragraphs> <Paragraph> <TextRuns> <TextRun> <Value>=Sum(Fields!registrationFee.Value)</Value> <Style /> </TextRun> </TextRuns> <Style /> </Paragraph> </Paragraphs> <rd:DefaultName>registrationFee1</rd:DefaultName> <Style> <Border> <Color>LightGrey</Color> <Style>Solid</Style> </Border> <PaddingLeft>2pt</PaddingLeft> <PaddingRight>2pt</PaddingRight> <PaddingTop>2pt</PaddingTop> <PaddingBottom>2pt</PaddingBottom> </Style> </Textbox> </CellContents> </TablixCell> <TablixCell> <CellContents> <Textbox Name="Amount"> <CanGrow>true</CanGrow> <KeepTogether>true</KeepTogether> <Paragraphs> <Paragraph> <TextRuns> <TextRun> <Value>=Sum(Fields!Amount.Value)</Value> <Style /> </TextRun> </TextRuns> <Style /> </Paragraph> </Paragraphs> <rd:DefaultName>Amount</rd:DefaultName> <Style> <Border> <Color>LightGrey</Color> <Style>Solid</Style> </Border> <PaddingLeft>2pt</PaddingLeft> <PaddingRight>2pt</PaddingRight> <PaddingTop>2pt</PaddingTop> <PaddingBottom>2pt</PaddingBottom> </Style> </Textbox> </CellContents> </TablixCell> <TablixCell> <CellContents> <Textbox Name="Balance1"> <CanGrow>true</CanGrow> <KeepTogether>true</KeepTogether> <Paragraphs> <Paragraph> <TextRuns> <TextRun> <Value>=Sum(Fields!Balance.Value)</Value> <Style /> </TextRun> </TextRuns> <Style /> </Paragraph> </Paragraphs> <rd:DefaultName>Balance1</rd:DefaultName> <Style> <Border> <Color>LightGrey</Color> <Style>Solid</Style> </Border> <PaddingLeft>2pt</PaddingLeft> <PaddingRight>2pt</PaddingRight> <PaddingTop>2pt</PaddingTop> <PaddingBottom>2pt</PaddingBottom> </Style> </Textbox> </CellContents> </TablixCell> <TablixCell> <CellContents> <Textbox Name="Year_2019"> <CanGrow>true</CanGrow> <KeepTogether>true</KeepTogether> <Paragraphs> <Paragraph> <TextRuns> <TextRun> <Value>=Sum(Fields!Year_2019.Value)</Value> <Style /> </TextRun> </TextRuns> <Style /> </Paragraph> </Paragraphs> <rd:DefaultName>Year_2019</rd:DefaultName> <Style> <Border> <Color>LightGrey</Color> <Style>Solid</Style> </Border> <PaddingLeft>2pt</PaddingLeft> <PaddingRight>2pt</PaddingRight> <PaddingTop>2pt</PaddingTop> <PaddingBottom>2pt</PaddingBottom> </Style> </Textbox> </CellContents> </TablixCell> </TablixCells> </TablixRow> </TablixRows> </TablixBody> <TablixColumnHierarchy> <TablixMembers> <TablixMember> <TablixHeader> <Size>0.25in</Size> <CellContents> <Textbox Name="Textbox47"> <CanGrow>true</CanGrow> <KeepTogether>true</KeepTogether> <Paragraphs> <Paragraph> <TextRuns> <TextRun> <Value>date registered</Value> <Style /> </TextRun> </TextRuns> <Style /> </Paragraph> </Paragraphs> <rd:DefaultName>Textbox47</rd:DefaultName> <Style> <Border> <Color>LightGrey</Color> <Style>Solid</Style> </Border> <PaddingLeft>2pt</PaddingLeft> <PaddingRight>2pt</PaddingRight> <PaddingTop>2pt</PaddingTop> <PaddingBottom>2pt</PaddingBottom> </Style> </Textbox> </CellContents> </TablixHeader> </TablixMember> <TablixMember> <TablixHeader> <Size>0.25in</Size> <CellContents> <Textbox Name="Textbox45"> <CanGrow>true</CanGrow> <KeepTogether>true</KeepTogether> <Paragraphs> <Paragraph> <TextRuns> <TextRun> <Value>Envelope #</Value> <Style /> </TextRun> </TextRuns> <Style /> </Paragraph> </Paragraphs> <rd:DefaultName>Textbox45</rd:DefaultName> <Style> <Border> <Color>LightGrey</Color> <Style>Solid</Style> </Border> <PaddingLeft>2pt</PaddingLeft> <PaddingRight>2pt</PaddingRight> <PaddingTop>2pt</PaddingTop> <PaddingBottom>2pt</PaddingBottom> </Style> </Textbox> </CellContents> </TablixHeader> </TablixMember> <TablixMember> <TablixHeader> <Size>0.25in</Size> <CellContents> <Textbox Name="Textbox43"> <CanGrow>true</CanGrow> <KeepTogether>true</KeepTogether> <Paragraphs> <Paragraph> <TextRuns> <TextRun> <Value>registration Fee</Value> <Style /> </TextRun> </TextRuns> <Style /> </Paragraph> </Paragraphs> <rd:DefaultName>Textbox43</rd:DefaultName> <Style> <Border> <Color>LightGrey</Color> <Style>Solid</Style> </Border> <PaddingLeft>2pt</PaddingLeft> <PaddingRight>2pt</PaddingRight> <PaddingTop>2pt</PaddingTop> <PaddingBottom>2pt</PaddingBottom> </Style> </Textbox> </CellContents> </TablixHeader> </TablixMember> <TablixMember> <TablixHeader> <Size>0.25in</Size> <CellContents> <Textbox Name="Textbox51"> <CanGrow>true</CanGrow> <KeepTogether>true</KeepTogether> <Paragraphs> <Paragraph> <TextRuns> <TextRun> <Value>Annual Dues</Value> <Style /> </TextRun> </TextRuns> <Style /> </Paragraph> </Paragraphs> <rd:DefaultName>Textbox51</rd:DefaultName> <Style> <Border> <Color>LightGrey</Color> <Style>Solid</Style> </Border> <PaddingLeft>2pt</PaddingLeft> <PaddingRight>2pt</PaddingRight> <PaddingTop>2pt</PaddingTop> <PaddingBottom>2pt</PaddingBottom> </Style> </Textbox> </CellContents> </TablixHeader> </TablixMember> <TablixMember> <TablixHeader> <Size>0.25in</Size> <CellContents> <Textbox Name="Textbox41"> <CanGrow>true</CanGrow> <KeepTogether>true</KeepTogether> <Paragraphs> <Paragraph> <TextRuns> <TextRun> <Value>Balance</Value> <Style /> </TextRun> </TextRuns> <Style /> </Paragraph> </Paragraphs> <rd:DefaultName>Textbox41</rd:DefaultName> <Style> <Border> <Color>LightGrey</Color> <Style>Solid</Style> </Border> <PaddingLeft>2pt</PaddingLeft> <PaddingRight>2pt</PaddingRight> <PaddingTop>2pt</PaddingTop> <PaddingBottom>2pt</PaddingBottom> </Style> </Textbox> </CellContents> </TablixHeader> </TablixMember> <TablixMember> <TablixHeader> <Size>0.25in</Size> <CellContents> <Textbox Name="Textbox39"> <CanGrow>true</CanGrow> <KeepTogether>true</KeepTogether> <Paragraphs> <Paragraph> <TextRuns> <TextRun> <Value>2019</Value> <Style /> </TextRun> </TextRuns> <Style /> </Paragraph> </Paragraphs> <rd:DefaultName>Textbox39</rd:DefaultName> <Style> <Border> <Color>LightGrey</Color> <Style>Solid</Style> </Border> <PaddingLeft>2pt</PaddingLeft> <PaddingRight>2pt</PaddingRight> <PaddingTop>2pt</PaddingTop> <PaddingBottom>2pt</PaddingBottom> </Style> </Textbox> </CellContents> </TablixHeader> </TablixMember> </TablixMembers> </TablixColumnHierarchy> <TablixRowHierarchy> <TablixMembers> <TablixMember> <Group Name="memberName"> <GroupExpressions> <GroupExpression>=Fields!memberName.Value</GroupExpression> </GroupExpressions> </Group> <SortExpressions> <SortExpression> <Value>=Fields!memberName.Value</Value> </SortExpression> </SortExpressions> <TablixHeader> <Size>1.33135in</Size> <CellContents> <Textbox Name="memberName1"> <CanGrow>true</CanGrow> <KeepTogether>true</KeepTogether> <Paragraphs> <Paragraph> <TextRuns> <TextRun> <Value>=Fields!memberName.Value</Value> <Style> <FontFamily>Bodoni MT</FontFamily> <FontWeight>Bold</FontWeight> <Color>Yellow</Color> </Style> </TextRun> </TextRuns> <Style /> </Paragraph> </Paragraphs> <rd:DefaultName>memberName1</rd:DefaultName> <Style> <Border> <Color>LightGrey</Color> <Style>Solid</Style> </Border> <BackgroundColor>DarkGreen</BackgroundColor> <PaddingLeft>2pt</PaddingLeft> <PaddingRight>2pt</PaddingRight> <PaddingTop>2pt</PaddingTop> <PaddingBottom>2pt</PaddingBottom> </Style> </Textbox> </CellContents> </TablixHeader> <TablixMembers> <TablixMember> <TablixHeader> <Size>1.33135in</Size> <CellContents> <Textbox Name="TransactionID1"> <CanGrow>true</CanGrow> <KeepTogether>true</KeepTogether> <Paragraphs> <Paragraph> <TextRuns> <TextRun> <Value>=Sum(Fields!TransactionID.Value)</Value> <Style /> </TextRun> </TextRuns> <Style /> </Paragraph> </Paragraphs> <rd:DefaultName>TransactionID1</rd:DefaultName> <Style> <Border> <Color>LightGrey</Color> <Style>Solid</Style> </Border> <PaddingLeft>2pt</PaddingLeft> <PaddingRight>2pt</PaddingRight> <PaddingTop>2pt</PaddingTop> <PaddingBottom>2pt</PaddingBottom> </Style> </Textbox> </CellContents> </TablixHeader> </TablixMember> </TablixMembers> </TablixMember> </TablixMembers> </TablixRowHierarchy> <DataSetName>DataSet1</DataSetName> <Top>0.57861in</Top> <Left>0.43972in</Left> <Height>0.5in</Height> <Width>10.36111in</Width> <ZIndex>1</ZIndex> <Style> <Border> <Style>None</Style> </Border> </Style> </Tablix> </ReportItems> <Height>4.78472in</Height> <Style /> </Body> <Width>10.80083in</Width> <Page> <LeftMargin>1in</LeftMargin> <RightMargin>1in</RightMargin> <TopMargin>1in</TopMargin> <BottomMargin>1in</BottomMargin> <Style /> </Page> <AutoRefresh>0</AutoRefresh> <DataSources> <DataSource Name="TCPROPDataSet1"> <ConnectionProperties> <DataProvider>System.Data.DataSet</DataProvider> <ConnectString>/* Local Connection */</ConnectString> </ConnectionProperties> <rd:DataSourceID>d7ecd806-9b95-401c-81b8-ffb593d5c305</rd:DataSourceID> </DataSource> </DataSources> <DataSets> <DataSet Name="DataSet1"> <Query> <DataSourceName>TCPROPDataSet1</DataSourceName> <CommandText>/* Local Query */</CommandText> </Query> <Fields> <Field Name="TransactionID"> <DataField>TransactionID</DataField> <rd:TypeName>System.Int32</rd:TypeName> </Field> <Field Name="memberName"> <DataField>memberName</DataField> <rd:TypeName>System.String</rd:TypeName> </Field> <Field Name="date_registered"> <DataField>date_registered</DataField> <rd:TypeName>System.DateTime</rd:TypeName> </Field> <Field Name="envelopeNumber"> <DataField>envelopeNumber</DataField> <rd:TypeName>System.String</rd:TypeName> </Field> <Field Name="registrationFee"> <DataField>registrationFee</DataField> <rd:TypeName>System.Decimal</rd:TypeName> </Field> <Field Name="Balance"> <DataField>Balance</DataField> <rd:TypeName>System.Decimal</rd:TypeName> </Field> <Field Name="2019"> <DataField>2019</DataField> <rd:TypeName>System.Decimal</rd:TypeName> </Field> <Field Name="registrationFee1"> <DataField>registrationFee</DataField> <rd:TypeName>System.String</rd:TypeName> </Field> <Field Name="Amount"> <DataField>Amount</DataField> <rd:TypeName>System.Decimal</rd:TypeName> </Field> <Field Name="Balance1"> <DataField>Balance</DataField> <rd:TypeName>System.String</rd:TypeName> </Field> <Field Name="20191"> <DataField>2019</DataField> <rd:TypeName>System.String</rd:TypeName> </Field> </Fields> <rd:DataSetInfo> <rd:DataSetName>TCPROPDataSet1</rd:DataSetName> <rd:SchemaPath>C:\ComboBox_DataTable\ComboBox_DataTable_CS\TCPROPDataSet1.xsd</rd:SchemaPath> <rd:TableName>MemberHistory</rd:TableName> <rd:TableAdapterFillMethod>Fill</rd:TableAdapterFillMethod> <rd:TableAdapterGetDataMethod>GetData</rd:TableAdapterGetDataMethod> <rd:TableAdapterName>MemberHistoryTableAdapter</rd:TableAdapterName> </rd:DataSetInfo> </DataSet> </DataSets> <rd:ReportUnitType>Inch</rd:ReportUnitType> <rd:ReportID>e7998fcb-0289-4716-a1b1-f7ea3618a285</rd:ReportID> </Report>Any instance of 2019 is causing an error even as I am using matrix.
Contributor
3690 Points
1043 Posts
Re: How can I use Number as field name in reporting services?
Sep 18, 2019 09:03 AM|Yongqing Yu|LINK
Hi simflex,
After testing the similar data in my page based on your needs, I found that in ssrs data format is not allowed as field name such as your fields [2017],][2018],[2019] etc.
I recommend that you when you pivot year to fields, you should convert it to varchar format, then in the dataset fields , you should change the number FieldName to string type like below:
Best Regards,
YongQing.
Please remember to click "Mark as Answer" the responses that resolved your issue.
If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.