CAML Query Problem - Support - RS Data Extension

Search Forum Home

Forum

Enesys RS Data ...

Support - RS Da...

CAML Query Problem

2/28/2007 2:17 AM

anonymous

Joined: 1/1/0001

Posts: 0

CAML Query Problem

Hi Just purchased and having a bit of trouble with a caml query.

i'm using a caml builder to build my query http://www.u2u.info/Blogs/Patrick/Lists/Posts/Post.aspx?ID=1252

and have this full data source which is not working for me. it seems to ignore the Geq -- any ideas about this? is there a better reference for caml i should be using? the query works fine when i run it from the designer above. thanks sorry if i'm missing something basic.

<?xml version="1.0" encoding="utf-8" ?>

<root>

<query>

<Where>

<Geq>

</Geq>

</Where>

</query>

</list>

</root>

2/28/2007 9:41 AM

Frédéric LATOUR

Joined: 5/31/2006

Posts: 629

Re: CAML Query Problem

Hi,
At first sight, I can't see anything wrong with your query seems.
What's not clear to me is what you mean by "not working".
Can you confirm otherwise that ERSDE is properly configured and that you can retrieve data from SharePoint lists? If you omit the "caml" part from your previous query are you retrieving all items from the GLBE Metrics list?

Regards,

Frederic LATOUR
ENESYS

2/28/2007 12:42 PM

anonymous

Joined: 1/1/0001

Posts: 0

Re: CAML Query Problem

sorry, by not working i mean simply that the filter seems to not be respected. i just get the same data set back (all the data) as before). i was able to apply filtering in SSRS itself so was able to find a work around. is there a good reference for the supported CAML? is the tool i mentioned above valid?

thanks for your help.

2/28/2007 2:30 PM

Frédéric LATOUR

Joined: 5/31/2006

Posts: 629

Re: CAML Query Problem

Hi,
I can see that you are using a report parameter in your query (parameters are not a caml thing but a feature added by ERSDE).
The first time you are writing a query using a parameter, Report Designer will automatically create the report parameter if it's not already created. When a report parameter is created by the report designer, its data type defaults to string which is not what you would like in that case.
Can you confirm that your "Date" report parameter is of type "Date"?

About Caml
The CAML part within the <query> element (as part of ERSDE query syntax) is sent as is to SharePoint web services. The ability to use report parameters is of course not a CAML thing. Report parameters will be replaced by their current values before any CAML is sent. In that respect, whatever CAML construction is accepted by SharePoint is valid within ERSDE.

The whole CAML thing within SharePoint has many uses. Please not that only a subset is used for querying lists. This subset is rather basic once you know the syntax. SharePoint SDK could serve as a reference.

The tool you mentionned is valid.

Regards,

Frederic LATOUR
ENESYS

3/2/2007 8:52 PM

robert.bruce

Joined: 2/28/2007

Posts: 2

Re: CAML Query Problem

sorry i think i got the problem now (or at least understand more).

I have this

<?xml version="1.0" encoding="utf-8" ?>
<root>
<list title="GLBE Metrics" relativeSiteUrl="/Testsite/" tableName="GLBEMetrics" useDisplayName="true">
<fields>Date</fields>
<query xmlns="http://schemas.microsoft.com/sharepoint/soap/">
          <OrderBy>
               <FieldRef Name="TradeDate" Ascending="False" />
          </OrderBy>
</query>
</list>
</root>

and the ascending seems to work fine. i change to true and it changes the order.

when i have this however where i've added the sqlOp it seems like the sqlOp does not care about the order of teh data set.

is there a way to get the sqlOps to respect the order by? or si ther a sqlOp that will do order by? Thanks. also i had to add this tag to the query element to get some of the other camls to work xmlns="http://schemas.microsoft.com/sharepoint/soap/"

<?xml version="1.0" encoding="utf-8" ?>
<root>
<list title="GLBE Metrics" relativeSiteUrl="/Testsite/" tableName="GLBEMetrics" useDisplayName="true">
<fields>Date</fields>
<query xmlns="http://schemas.microsoft.com/sharepoint/soap/">
          <OrderBy>
               <FieldRef Name="TradeDate" Ascending="False" />
          </OrderBy>
</query>
<sqlOp op="distinct">
<dstTableName>DistinctTradeDate</dstTableName>
<tableName>GLBEMetrics</tableName>
<fieldName>Date</fieldName>
</sqlOp>
<resultSet>DistinctTradeDate</resultSet>
</list>
</root>

3/5/2007 5:04 PM

Frédéric LATOUR

Joined: 5/31/2006

Posts: 629

Re: CAML Query Problem

Hi,
The distinct operation will sort the "result set" for which distinct values are needed based on the fields specified.
This is usually not a problem as it is possible to sort the resulting data set at the report level.
But obviously you are using this query for a dataset that will be used for some report parameter values. And indeed in that situation, there's no way to sort the resulting data set (whatever operation has been used) before it is proposed as parameter values.

We will include the ability to specify the sord order used by the distinct operation in the next version that will be available by the end of march.

Regarding the xml schema reference, can you double check? Specifying the schema should not be necessary. While testing your case, my queries were perfectly working without specifying any schema reference.

Regards,

Frederic LATOUR
ENESYS

Page 1 of 1