Access 2013 Crosstab Query with Dynamic Column Headers

 Computer, Sharepoint, Software  Comments Off on Access 2013 Crosstab Query with Dynamic Column Headers
Mar 212017
 
Share

In my scenario, I have a SharePoint 2013 list with Task Name, Assignee, Task Start and Task End. I needed to query the list and parse the data into a pivot table with all dates between the task dates as the column headers. The following steps were pieced together from multiple posts around the Web.

There are easier ways to do this with SQL or ReportBuilder, but I had to use Access in this case. After exporting the list into Access…

First create a Table called “Dual” with two columns: ID, DummyTxt
Populate one row with anything.

Create a Query (qDual) against Dual that will create 9 rows.
Query SELECT 0 AS a
FROM dual
UNION ALL
SELECT 1
FROM dual
UNION ALL
SELECT 2
FROM dual
UNION ALL
SELECT 3
FROM dual
UNION ALL
SELECT 4
FROM dual
UNION ALL
SELECT 5
FROM dual
UNION ALL
SELECT 6
FROM dual
UNION ALL
SELECT 7
FROM dual
UNION ALL
SELECT 8
FROM dual
UNION ALL SELECT 9
FROM dual;

Create a Query (qDates).
SELECT top 90 date() + (a.a + (10 * b.a) + (100 * c.a)) AS MyDate
FROM (SELECT *
FROM qDual) AS a, (SELECT *
FROM qDual) AS b, (SELECT *
FROM qDual) AS c;

This creates 90 days of dates from Today. I am limiting to 90 days out. If you have too many days, the later crosstab query will complain about having too many crosstab headers.

Create Crosstab Query.
TRANSFORM [Staffing].Name
SELECT [Staffing].Name
FROM [Staffing], [qDates]
WHERE [qDates].MyDate between [Staffing].[Task Start] and [Staffing].[Task End]
GROUP BY [Staffing].Name
PIVOT [qDates].MyDate

You can use another column (eg. Task Name) for the Transform. When you run the query you should have something like this.

The first column is today’s date. And though it’s not in the screenshot, the last column is the MAX Task End. In this pseudo-GANTT table, you can see that I am tasked from today and everyday forward. Aquaman has a task starting on 3/22 and forward and Superman has a task starting 4/1 and forward.

InfoPath – Delete Item via UpdateListItems Web Service

 Sharepoint  Comments Off on InfoPath – Delete Item via UpdateListItems Web Service
Mar 042016
 
Share

I have been having problems sending a delete request via the lists.asmx web service in SharePoint.
My Delete XML packet looks like this:

<Batch OnError="Continue" PreCalc="TRUE" ListVersion="0">
<Method ID="1" Cmd="Delete">
<Field Name="ID"></Field>
</Method>
</Batch>

Using the steps found here (http://jaliyaudagedara.blogspot.com/2011/03/submitting-data-to-sharepoint-2010-list.html) I setup a Delete List Item data connection.

When trying to delete an item from a list (not document library) I was seeing the following error in the
ULS logs:

The form definition (.xsf) file has an invalid mapping from the document to the submit web service

After a little digging it appears the web service call wants the list item ID to be an integer.
After exporting my template files, I edited the schema definition file (.XSD) for the Delete function.
At the bottom of this file you will find:

	<xsd:attribute name="PreCalc" type="xsd:string"></xsd:attribute>
	<xsd:attribute name="Cmd" type="xsd:string"></xsd:attribute>
	<xsd:attribute name="ListVersion" type="xsd:string"></xsd:attribute>
	<xsd:attribute name="OnError" type="xsd:string"></xsd:attribute>
	<xsd:attribute name="ID" type="xsd:string"></xsd:attribute>
	<xsd:attribute name="Name" type="xsd:string"></xsd:attribute>

Simply change:

	<xsd:attribute name="ID" type="xsd:string"></xsd:attribute>

to

	<xsd:attribute name="ID" type="xsd:integer"></xsd:attribute>

and my delete function works!