Archive for category SSRS

More than two conditions in CAML query

CAML doesn’t support more than two conditions in a query. I just realized that I never needed more than two conditions in a query, but I just did when I was designing an SSRS report against a SharePoint list.
But there’s nothing to worry about, here is the workaround;

<Query>

<Where>

<Or>

    <Contains>

    <FieldRef Name=”Comment” />

    <Value Type=”Text”>    <Parameter Name=”SearchParam” />     </Value>

    </Contains>

<Or>

    <Eq>

    <FieldRef Name=”ID” />

    <Value Type=”Integer”><Parameter Name=”SearchParam” /></Value>

    </Eq>

<Or>

<Contains>

    <FieldRef Name=”Title” />

    <Value Type=”Text”><Parameter Name=”SearchParam” /></Value>

 </Contains>

<Contains>

    <FieldRef Name=”Record_x0020_Number” />

    <Value Type=”Text”>    <Parameter Name=”SearchParam” /></Value>

</Contains>

</Or>

</Or>

</Or>

</Where>

</Query>

Keep nesting for more conditions.

Leave a comment

PerformancePoint Time Intelligence Filter with SSRS report connection error

Recently while designing a Dashboard in SharePoint 2010, I created a SSRS 2008 R2 report, which had a parameter of type date, “no problem, it would work great with Time Intelligence Filter”, that’s what I thought, but it didn’t.

My SharePoint site is using UK regional settings, but my reporting services are installed with default options, means it uses US settings.

In the picture below it can be seen that both of these controls understand UK date format, which is logical that they are considering the client’s regional settings.

 

 

 

 

But when I connect these two, it seems that the date which is being passed to the report is not in the UK format. I can confirm this by selecting an earlier date, for example 10/07/2011, the report interprets it as 7th Oct.

 

 

 

 

 

 

 

 

 

 

 

I haven’t found the solution to this problem yet, but I am using a workaround which is to deploy the dashboard without filter and then edit the page in browser and use SharePoint date filter, it works straights away.

So in this case I would blame the Time Intelligence Filter, because the report is working fine with filter web part in the browser.

 

 

 

3 Comments

SQL Server 2005 Reporting Services doesn’t work in integrated mode with SharePoint 2010

When my company made the decision to upgrade our intranet from SharePoint 2007 to SharePoint 2010 version, I was over the moon, but immediately I was told that we wouldn’t be upgrading to SQL Server 2008, it was still fine, because Microsoft states that SharePoint 2010 works with SQL Server 2005 SP2 with cumulative updates. It’s only later when we found out that SQL Server Reporting Services 2005 would not work with SharePoint 2010 in integrated mode.

This is not all, we were lucky enough to get our hands on Visual Studio 2010, but guess what? You can’t develop or even open SSRS 2005 reports in Visual Studio 2010.

Happy coding J

Leave a comment