locked
How can I use Number as field name in reporting services? RRS feed

  • Question

  • User1216627406 posted

    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:

    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;

    Many thanks in advance

    Wednesday, September 11, 2019 8:31 PM

All replies

  • User665608656 posted

    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.

    Thursday, September 12, 2019 8:19 AM
  • User1216627406 posted

    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.

    Thursday, September 12, 2019 2:53 PM
  • User665608656 posted

    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.

    Wednesday, September 18, 2019 9:03 AM