Table Filters and Empty Records
in [Replication]
|
Prev: 2005 sp2 standard edition to 2005 sp2 standard edition replication
Next: Microsoft Responds to the Evolution of Online Communities
From: Chris Slowinski on 4 May 2010 18:28 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.[Project State], 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?
From: Chris Slowinski on 4 May 2010 19:18
Gah...wrong forum. Misclicked. Sorry! "Chris Slowinski" wrote: > 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.[Project > State], 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? |