Designing Reports with Reporting Services

The following blog has a good post on how to setup Reporting Services Add-in
http://blogs.threewill.com/implementingsharepoint/Lists/Categories/Category.aspx?Name=Reporting

Issue 1:

As I was working with designing reports, I had a scenario where I had to have a parameter with a dropdown of all the Locations that a user has access to. This is stored in SQL Server in my case. I was not sure how to do it. I knew I had to create a dataset that returns the locations and pass in the logged user. I did do that, but instead of passing User!UserID, I created another Report parameter called UserID and set that to User!UserID and passed this ReportParameter as the Dataset’s Query Parameter.

When I tried to do it like that, I ran in to an error. I searched for it and found the solution to the problem in the blog entry below. I had the Dataset Report Parameter declared above the UserID parameter. Fixing it as below, made the error go away.

Declare Parameter Y before X as below.
Go to the Layout tab
Select the Report object in the properties window
Go to the ReportParameters property and edit
Move Y above X using the arrows
http://blogs.infosupport.com/raimondb/archive/2006/08/09/sqlrepparamerror.aspx

Issue 2:

I had a need to conditionally sum the underlying data and display it in the group footer.
I followed the following advice from a forum and it worked.

Ex:=Sum(iif(Fields!Status.value = "Value a", Cdbl(Fields!Payment.Value), 0.0))

http://www.tek-tips.com/viewthread.cfm?qid=1072925&page=3


Issue 3:

I was trying to solve a problem by using an aggregate function and display it in the footer by using another aggregate function. It won’t work in Reporting Services as you cannot embed aggregate functions within each other, nor can you use a calculated field as an expression in an aggregation function.

I even considered using the technique below, but did not help.

http://www.codeprof.com/dev-archive/211/19-82-2111320.shtm

I finally solved my problem by using a Report Parameter instead.