Trouble with using a list with new columns

Question

Trouble with using a list with new columns

mschaper

Ten Centuries

Points: 1120
More actions
April 27, 2016 at 8:08 am

#318558

I've inherited a report. Changes were made to the SQL and it works as expected. The problem occurs with a list we are using. We use a list for column headings called "ColHeadings". The user selects "English", "French", or "Spanish" from the "Language" parameter. When the report is run, there is an expression in each column heading that selects the appropriate value from the list and displays it on the report. This process has worked well and has not caused any issues.
At the beginning of February 2016, there was an upgrade to Report Web. This process still worked well. Last week, for the first time since the upgrade, new column headings were added to list. Now when we run the report, we get an error message that says:
"The SharePoint list query is not valid: The field VOUCHER_STYLE was not found in the list AM_Headings. (rsSPDataProviderError)
----------------------------
Query execution failed for dataset 'ColHeadings'. (rsErrorExecutingCommand)"
I believe we have added the new columns the same way as before. I checked and we are connected to the Data Source. I cannot figure out why it doesn't recognize the new fields. Below is the dataset query we are using to retrieve the column headings.
<RSSharePointList xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<ListName>AM_Headings</ListName>
<ViewFields>
<FieldRef Name="Title" />
<FieldRef Name="FLDTYPE" />
<FieldRef Name="ACCEL_DEPR" />
<FieldRef Name="ACCOUNT" />
<FieldRef Name="ACCOUNT_AD" />
<FieldRef Name="ACCOUNT_FA" />
<FieldRef Name="BEGINNING_COST" />
<FieldRef Name="ENDING_COST" />
<FieldRef Name="GPBR" />
<FieldRef Name="PRIOR" />
<FieldRef Name="CURRENT" />
<FieldRef Name="TICKET_NUMBER" />
<FieldRef Name="FIRST_6" />
<FieldRef Name="LAST_4" />
<FieldRef Name="GL_AMOUNT" />
<FieldRef Name="BANK_AMOUNT" />
<FieldRef Name="AUTHORIZATION_NUMBER" />
<FieldRef Name="BRANCH_NAME" />
<FieldRef Name="PAYMENT_DATE" />
<FieldRef Name="PHYSICAL_LOCATION " />
<FieldRef Name="VOUCHER_STYLE" /> ''''new
<FieldRef Name="VENDOR_LOCATION" /> ''''new
<FieldRef Name="INVOICE_TOTAL" /> ''''new
<FieldRef Name="MONETARY_AMOUNT" /> ''''new
<FieldRef Name="LinkTitleNoMenu" />
<FieldRef Name="LinkTitle" />
<FieldRef Name="ID" />
<FieldRef Name="ContentType" />
<FieldRef Name="Modified" />
<FieldRef Name="Created" />
<FieldRef Name="Author" />
<FieldRef Name="Editor" />
<FieldRef Name="_UIVersionString" />
<FieldRef Name="Attachments" />
<FieldRef Name="Edit" />
<FieldRef Name="DocIcon" />
</ViewFields>
<Query>
<Where>
<And>
<Eq>
<FieldRef Name="Title" />
<Value Type="Text">
<Parameter Name="LANGUAGE" />
</Value>
</Eq>
<Eq>
<FieldRef Name="FLDTYPE" />
<Value Type="Text">LONGDESCR</Value>
</Eq>
</And>
</Where>
</Query>
</RSSharePointList>
Thanks for your help...........

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Alan Burstein SSC Guru Points: 61136 More actions · Answer 1

Perhaps it's a shot in the dark and I don't know anything about Sharepoint lists but, if you can write a stored proc that accesses the XML in a SQL query (via OPENXML or some other method) you can pull the column names out as shown below. For the example I'm assigning the XML as a variable for demo purposes.

-- Demo XML (you would access the sp list xml differently

DEClARE @SPList XML =

'<RSSharePointList xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">

<ListName>AM_Headings</ListName>

<ViewFields>

<FieldRef Name="Title" />

<FieldRef Name="FLDTYPE" />

<FieldRef Name="ACCEL_DEPR" />

<FieldRef Name="ACCOUNT" />

<FieldRef Name="ACCOUNT_AD" />

<FieldRef Name="ACCOUNT_FA" />

<FieldRef Name="BEGINNING_COST" />

<FieldRef Name="ENDING_COST" />

<FieldRef Name="GPBR" />

<FieldRef Name="PRIOR" />

<FieldRef Name="CURRENT" />

<FieldRef Name="TICKET_NUMBER" />

<FieldRef Name="FIRST_6" />

<FieldRef Name="LAST_4" />

<FieldRef Name="GL_AMOUNT" />

<FieldRef Name="BANK_AMOUNT" />

<FieldRef Name="AUTHORIZATION_NUMBER" />

<FieldRef Name="BRANCH_NAME" />

<FieldRef Name="PAYMENT_DATE" />

<FieldRef Name="PHYSICAL_LOCATION " />

<FieldRef Name="VOUCHER_STYLE" />''''new

<FieldRef Name="VENDOR_LOCATION" /> ''''new

<FieldRef Name="INVOICE_TOTAL" /> ''''new

<FieldRef Name="MONETARY_AMOUNT" /> ''''new

<FieldRef Name="LinkTitleNoMenu" />

<FieldRef Name="LinkTitle" />

<FieldRef Name="ID" />

<FieldRef Name="ContentType" />

<FieldRef Name="Modified" />

<FieldRef Name="Created" />

<FieldRef Name="Author" />

<FieldRef Name="Editor" />

<FieldRef Name="_UIVersionString" />

<FieldRef Name="Attachments" />

<FieldRef Name="Edit" />

<FieldRef Name="DocIcon" />

</ViewFields>

<Query>

<Where>

<And>

<Eq>

<FieldRef Name="Title" />

<Value Type="Text">

<Parameter Name="LANGUAGE" />

</Value>

</Eq>

<Eq>

<FieldRef Name="FLDTYPE" />

<Value Type="Text">LONGDESCR</Value>

</Eq>

</And>

</Where>

</Query>

</RSSharePointList>';

-- query to return the column heading values

SELECT

col1 = v.value('(ViewFields/FieldRef[01]/@Name)[1]','varchar(50)'),

col2 = v.value('(ViewFields/FieldRef[02]/@Name)[1]','varchar(50)'),

col3 = v.value('(ViewFields/FieldRef[03]/@Name)[1]','varchar(50)'),

col4 = v.value('(ViewFields/FieldRef[04]/@Name)[1]','varchar(50)'),

col5 = v.value('(ViewFields/FieldRef[05]/@Name)[1]','varchar(50)'),

col6 = v.value('(ViewFields/FieldRef[06]/@Name)[1]','varchar(50)'),

col7 = v.value('(ViewFields/FieldRef[07]/@Name)[1]','varchar(50)'),

col8 = v.value('(ViewFields/FieldRef[08]/@Name)[1]','varchar(50)'),

col9 = v.value('(ViewFields/FieldRef[09]/@Name)[1]','varchar(50)'),

col10 = v.value('(ViewFields/FieldRef[10]/@Name)[1]','varchar(50)'),

col11 = v.value('(ViewFields/FieldRef[11]/@Name)[1]','varchar(50)'),

col12 = v.value('(ViewFields/FieldRef[12]/@Name)[1]','varchar(50)'),

col13 = v.value('(ViewFields/FieldRef[13]/@Name)[1]','varchar(50)'),

col14 = v.value('(ViewFields/FieldRef[14]/@Name)[1]','varchar(50)'),

col15 = v.value('(ViewFields/FieldRef[15]/@Name)[1]','varchar(50)'),

col16 = v.value('(ViewFields/FieldRef[16]/@Name)[1]','varchar(50)'),

col17 = v.value('(ViewFields/FieldRef[17]/@Name)[1]','varchar(50)'),

col18 = v.value('(ViewFields/FieldRef[18]/@Name)[1]','varchar(50)'),

col19 = v.value('(ViewFields/FieldRef[19]/@Name)[1]','varchar(50)'),

col20 = v.value('(ViewFields/FieldRef[20]/@Name)[1]','varchar(50)'),

col21 = v.value('(ViewFields/FieldRef[21]/@Name)[1]','varchar(50)'),

col22 = v.value('(ViewFields/FieldRef[22]/@Name)[1]','varchar(50)'),

col23 = v.value('(ViewFields/FieldRef[23]/@Name)[1]','varchar(50)'),

col24 = v.value('(ViewFields/FieldRef[24]/@Name)[1]','varchar(50)'),

col25 = v.value('(ViewFields/FieldRef[25]/@Name)[1]','varchar(50)'),

col26 = v.value('(ViewFields/FieldRef[26]/@Name)[1]','varchar(50)'),

col27 = v.value('(ViewFields/FieldRef[27]/@Name)[1]','varchar(50)'),

col28 = v.value('(ViewFields/FieldRef[28]/@Name)[1]','varchar(50)'),

col29 = v.value('(ViewFields/FieldRef[29]/@Name)[1]','varchar(50)'),

col30 = v.value('(ViewFields/FieldRef[30]/@Name)[1]','varchar(50)'),

col31 = v.value('(ViewFields/FieldRef[31]/@Name)[1]','varchar(50)'),

col32 = v.value('(ViewFields/FieldRef[32]/@Name)[1]','varchar(50)'),

col33 = v.value('(ViewFields/FieldRef[33]/@Name)[1]','varchar(50)'),

col34 = v.value('(ViewFields/FieldRef[34]/@Name)[1]','varchar(50)'),

col35 = v.value('(ViewFields/FieldRef[35]/@Name)[1]','varchar(50)'),

col36 = v.value('(ViewFields/FieldRef[36]/@Name)[1]','varchar(50)')

FROM (VALUES (@SPList)) spList(x)

CROSS APPLY x.nodes('RSSharePointList') v(v);

Copy/paste/run this in a local SQL instance, you'll notice that it returns one row with each column name. If you can get that far you're home free. All you need to do then is turn that into a data set and, for the column heading value, use an expression that references the value of col1, col2, col3, etc....

Hope this helps, let me know if you have any followup questions or if this won't work at all.

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

mschaper Ten Centuries Points: 1120 More actions · Answer 2

Thanks for your suggestion. I've found out there are 2 ways to add a column to a SharePoint List. One way gives it the column name we expected, the other creates a column name. We added the new columns the wrong way. We deleted the columns and added them back the right way and it is working like we expect. Thanks for taking the time to respond to my question.

Alan Burstein SSC Guru Points: 61136 More actions · Answer 3

No problem. Glad to help.

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001