Prev: Error when trying to generate report model.
Next: Subscription sending email multiple times
From: Chris Slowinski on 4 May 2010 19:22
Hi,

I'm quite new to Reporting Services and am having a bit of a confusing
afternoon with it. I'm trying to build a report that contains a series of
discrete reports for a number of projects that show project data and any
milestone tasks associated with each project; if a project has no
milesetones, the project data is still to be displayed. So, I build a report
that has a series of fields showing project information (name, cost, state,
etc.) and then a table below it to contain the milestone tasks for each
project. All of these are placed within a list data region with a grouping
on ProjectName.

My dataset is basically to select everything I need from two tables:

SELECT MSP_EpmProject_UserView.ProjectName,
MSP_EpmProject_UserView.ProjectCost,
MSP_EpmProject_UserView.ProjectActualCost,
MSP_EpmProject_UserView.[ProjectState], MSP_EpmTask_UserView.TaskName,
MSP_EpmTask_UserView.[TaskIsMilestone ], MSP_EpmTask_UserView.TaskStartDate,
MSP_EpmProject_UserView.ProjectUID

FROM MSP_EpmProject_UserView LEFT OUTER JOIN MSP_EpmTask_UserView
ON MSP_EpmProject_UserView.ProjectUID = MSP_EpmTask_UserView.ProjectUID

ORDER BY MSP_EpmProject_UserView.ProjectName

I *think* this is okay, but we'll see.

My next step is to place a filter on the table in the report to only show
milestones:
Field!TaskIsMilestone_.Value = True.

The difficulty I experience is that the filter works but when I scroll
through the projects in the preview, if a project has no milestones, the
fields (project name, cost, and so forth) are empty. Remove the filter from
the table and everything appears.

I can see what is happening but I fail to understand why the filter applied
to a table is affecting what seems like a larger scope. This is almost
analogous to me applying a WHERE clause to the dataset query (except it's
returning blank rows).

Am I misunderstanding the scope to which table filters apply or is my query
flawed?
 | 
Pages: 1
Prev: Error when trying to generate report model.
Next: Subscription sending email multiple times