We are excited to announce that the ASP.NET Forums are moving to the new Microsoft Q&A experience. Learn more >

How can I use Number as field name in reporting services? RSS

3 replies

Last post Sep 18, 2019 09:03 AM by Yongqing Yu

‹ Previous Thread|Next Thread ›

Shortcuts

simflex

Member

259 Points

1191 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:

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

- Reply
Yongqing Yu
Contributor

3710 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.

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.

simflex

Member

259 Points

1191 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.

- Reply
Yongqing Yu
Contributor

3710 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.

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.

‹ Previous Thread|Next Thread ›