Recently on the microsoft.public.project.developer newsgroup, a user posted the following question:
I want to consume the PSI Calendar webservice for reporting purposes. I created a shared datasource in Visual Studio 2008 (installed by SQL2008) to access the PSI ListCalendars webservice and it works fine (finally).
Now I'm trying to consume the Calendar.ReadCalendars PSI webservice via the same datasource but it keeps on giving me the following error:
***
The remote server returned an error: (500) Internal Server Error.
<snip>
<class name="Value cannot be null. Parameter name: s"><error id="13043" name="CalendarFilterInvalid" uid="7ae0376c-f8c9-48ed-8184-c9e0fd6c3694" /></class> <snip>
***
Obviously it complains about 'CalendarFilterInvalid', but the filter parameter is set (see below) and even when put a value in it, it keeps on giving the same error.
I am using this query...
1: <Query>
2: <SoapAction>http://schemas.microsoft.com/office/project/server/webservices/Calendar/ReadCalendars</SoapAction>
3: <Method Name="ReadCalendars" Namespace="http://schemas.microsoft.com/office/project/server/webservices/Calendar">
4: <Parameters>
5: <Parameter Name="filter">
6: <DefaultValue>String.Empty</DefaultValue>
7: </Parameter>
8: <Parameter Name="autoCheckOut">
9: <DefaultValue>false</DefaultValue>
10: </Parameter>
11: </Parameters>
12: </Method>
13: <ElementPath IgnoreNamespaces="true">*</ElementPath>
14: </Query>
I was able to reliably reproduce the reported error in using a console application, and examination of the SoapException’s Detail.InnerXml property yielded the following additional information:
1: <errinfo>
2: <general>
3: <class name="The filter parameter cannot be empty. You must at least specify the table and fields to return.">
4: <error id="13043" name="CalendarFilterInvalid" uid="8f74dedb-4491-4b36-81ee-295d7ac5a988" />
5: </class>
6: </general>
7: </errinfo>
Although the SDK article on the ReadCalendars() method states that passing String.Empty for the filter parameter will return a list of all enterprise calendars, from the post quoted above and my own testing it would appear that this is not the case. When I reported this inconsistency between the SDK and the PSI’s behavior to Microsoft, their response was that they do not plan to fix this issue in the near future. However, they do plan to update the SDK to reflect the actual behavior of the PSI.
In order to get ReadCalendars() to correctly return a list of all enterprise calendars, you need to use code similar to the following:
1: CalendarSvc.Calendar _calPsi = new CalendarSvc.Calendar();
2: _calPsi.Url = "http://epmdemo/pwa/_vti_bin/psi/calendar.asmx";
3: _calPsi.Credentials = CredentialCache.DefaultCredentials;
4:
5: Filter _calFilter = new Filter();
6: using (CalendarSvc.CalendarDataSet _calData = new CalendarSvc.CalendarDataSet())
7: { 8: _calFilter.FilterTableName = _calData.Calendars.TableName;
9: _calFilter.Fields.Add(new Filter.Field(_calData.Calendars.CAL_CHECKOUTBYColumn.ColumnName));
10: _calFilter.Fields.Add(new Filter.Field(_calData.Calendars.CAL_CHECKOUTDATEColumn.ColumnName));
11: _calFilter.Fields.Add(new Filter.Field(_calData.Calendars.CAL_IS_STANDARD_CALColumn.ColumnName));
12: _calFilter.Fields.Add(new Filter.Field(_calData.Calendars.CAL_NAMEColumn.ColumnName));
13: _calFilter.Fields.Add(new Filter.Field(_calData.Calendars.CAL_UIDColumn.ColumnName));
14: }
15:
16: CalendarSvc.CalendarDataSet _calDs = _calPsi.ReadCalendars(_calFilter.GetXml(), false);
If you are trying to consume the ReadCalendars() method in SSRS, things get a little more complicated.
First, you need to create an XML DataSource and set its connection string to the Calendar Web service’s URL (e.g. http://epmdemo/pwa/_vti_bin/psi/calendar.asmx).
Next, create a new dataset that uses the XML data source, as shown below.
Set the query for this dataset to:
1: <Query>
2: <Method Namespace="http://schemas.microsoft.com/office/project/server/webservices/Calendar/" Name="ReadCalendars" />
3: <SoapAction>http://schemas.microsoft.com/office/project/server/webservices/Calendar/ReadCalendars</SoapAction>
4: <Parameters>
5: <Parameter Name="filter" Type="xml" />
6: <Parameter Name="autoCheckOut" Type="boolean" />
7: </Parameters>
8: <ElementPath IgnoreNamespaces="true">ReadCalendarsResponse/ReadCalendarsResult/diffgram/CalendarDataSet/Calendars</ElementPath>
9: </Query>
Make sure to leave the parameter elements blank!
Next, create two parameters in the dataset. The names of these parameters need to match the names of the parameters for the ReadCalendars() method, so you should name the first parameter autoCheckOut and name the second filter. If there are any extraneous pa
Set the parameter value for autoCheckOut to false, and populate the parameter value for filter with:
1: <?xml version="1.0" encoding="utf-16"?>
2: <Filter xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" filterTableName="Calendars" xmlns="http://microsoft.com/ProjectServer/FilterSchema.xsd">
3: <Fields>
4: <Field tableName="Calendars" fieldName="CAL_CHECKOUTBY" />
5: <Field tableName="Calendars" fieldName="CAL_CHECKOUTDATE" />
6: <Field tableName="Calendars" fieldName="CAL_IS_STANDARD_CAL" />
7: <Field tableName="Calendars" fieldName="CAL_NAME" />
8: <Field tableName="Calendars" fieldName="CAL_UID" />
9: </Fields>
10: <Criteria />
11: </Filter>
We’re almost done. Now you need to delete any fields the Report Designer added into the dataset and replace them with your desired fields from the CalendarDataSet.Calendars table. In this example, I’m only using CAL_UID and CAL_NAME. Note that the field name and field source for each field need to match.
Finally, create a new table in the report that uses the Dataset you created and add your desired columns.
You should now have a functioning report.
If you specify the value for the filter parameter in the dataset query, the report will not work properly – for some reason it gets mangled during query processing. Placing the filter XML into a dataset parameter avoids this issue.