Framework Manager: Filter item on the right side of a left join

[dougp]

Cognos 11.0.7
Framework Manager
Compatible Query Mode
Windows 10
MS SQL Server 2016

I work with a great bunch of people.  I usually have the luxury of working with databases that are modeled in a way that makes Cognos modeling really easy.  It's great.  I can focus on reporting.

Now I need to enable my users to create reports from a model that is less than optimal.  I need to present it in a way that is reliable and doesn't require them to be report development experts.  (Sounds like the role of a Cognos Framework Manager developer, right?)  This seems like it should be simple, but I haven't figured it out yet.  It's time to work on other things for a while.  I'm hoping one of you Cognos gurus looks at this and thinks it's as easy as I think it should be.


I'm trying to create a model that includes an outer join, but I can't get the filters working.

Code Select Expand

┌─────────────┐    ┌─────────────┐    ┌─────────────┐1..n   0..1┌───────────────┐    ┌───────────────┐
│ Fund Source ├────┤    Fact     ├────┤   Project   ├───────────┤ Project Group ├────┤  Group List   │
└─────────────┘    └───────┬─────┘    └─────────────┘           └───────────────┘    └───────────────┘
                           │
                           │
                   ┌───────┴──────┐
                   │ Project List │
                   └──────────────┘


I want to be able to report on all projects  in a specific project list, but use the project group to roll up the dollars.  If a project is in the project list but not in a project group, I want that project listed separately.  Something like this

Code Select Expand

select coalesce(pg.GroupName, p.ProjectName) as Project
, fs.FundSourceDescription as 'Fund Source'
, sum(fact.Dollars) as Dollars

from Fact fact
  inner join Project p on p.ProjectId = fact.ProjectId
  inner join ProjectList pl on p.ProjectId = pl.ProjectId
  inner join FundSource fs on fs.FundSourceId = fact.FundSourceId
  left outer join ProjectGroup pg on pg.ProjectId = p.ProjectId
  inner join GroupList gl on gl.ProjectGroupId = pg.ProjectGroupId
                         and gl.GroupListName = 'MyGroupList'

where pl.ListName = 'My Project List'

group by  coalesce(pg.GroupName, p.ProjectName)
, fs.FundSourceDescription

...which should return something like this...

Project	Fund Source	Dollars
Group A	A	6,000,000
Group A	B	3,000,000
Group B	A	400,000
Group B	C	2,500,000
Solo Project A	B	120,000
Solo Project B	B	70,000
Solo Project B	C	35,000

Unfortunately, when I create the model to look like what I think I want, the query it creates is more like this:

Code Select Expand

select coalesce(pg.GroupName, p.ProjectName) as Project
, fs.FundSourceDescription as 'Fund Source'
, sum(fact.Dollars) as Dollars

from Fact fact
  inner join Project p on p.ProjectId = fact.ProjectId
  inner join ProjectList pl on p.ProjectId = pl.ProjectId
  inner join FundSource fs on fs.FundSourceId = fact.FundSourceId
  left outer join ProjectGroup pg on pg.ProjectId = p.ProjectId
  inner join GroupList gl on gl.ProjectGroupId = pg.ProjectGroupId

where pl.ListName = 'My Project List'
  and gl.GroupListName = 'MyGroupList'

group by  coalesce(pg.GroupName, p.ProjectName)
, fs.FundSourceDescription

But putting the filter in the WHERE clause violates the left join, making the query behave like it's using an inner join, so I get...

Project	Fund Source	Dollars
Group A	A	6,000,000
Group A	B	3,000,000
Group B	A	400,000
Group B	C	2,500,000

Does anybody here know how to tell Cognos to put the filter in the right place to make the outer join work?

[CognosPaul]

Having the filter set up like

Code Select Expand

where pl.ListName = 'My Project List'
  and (gl.GroupListName = 'MyGroupList' or gl.GroupListName is null)


Should give you the results you're expecting

[dougp]

I get that, but the filter is not in the model, it's in the report.  How do I design the model so that I don't need to spend a week teaching each user the basics of SQL querying?

[CognosPaul]

Try putting the coalesce on GroupListName dataitem in FM. The SQL won't look pretty when they filter, but I think it should work. 

[dougp]

You lost me.  coalesce(GroupListName) is the same as GroupListName.

[CognosPaul]

I think I might be a little confused by the goal here. The left outer join of Project Group/Group List makes sense, but it makes perfect sense to me that the solo projects wouldn't appear when you filter on a group list. They're, by definition, not part of a group. You could try something like coalesce(GroupListName,'Solo Projects') on the business layer of the model. When the user tries to filter on Solo Project, it'll essentially be trying to filter on GroupListName is null.

[dougp]

The goal is to summarize the projects that are in groups, but still list those that are not.
The user would never want to know which projects were not part of a group.  They would either want projects that are members of the selected group masked by that group (summarizing the dollars to the group level) or to not use a project group to summarize (showing all projects individually).  The latter is simple:  just don't include any group information.  The former is what I'm trying to do.

If I understand the recent recommendation, it changes this...

Code Select Expand

select coalesce(pg.GroupName, p.ProjectName) as Project
, fs.FundSourceDescription as 'Fund Source'
, sum(fact.Dollars) as Dollars

from Fact fact
  inner join Project p on p.ProjectId = fact.ProjectId
  inner join ProjectList pl on p.ProjectId = pl.ProjectId
  inner join FundSource fs on fs.FundSourceId = fact.FundSourceId
  left outer join (
    ProjectGroup pg on pg.ProjectId = p.ProjectId
    inner join (
      select ProjectGroupId
      , GroupListName
      from GroupList grouplist
    ) gl on gl.ProjectGroupId = pg.ProjectGroupId

where pl.ListName = 'My Project List'
  and gl.GroupListName = 'MyGroupList'

group by  coalesce(pg.GroupName, p.ProjectName)
, fs.FundSourceDescription

...to this...

Code Select Expand

select coalesce(pg.GroupName, p.ProjectName) as Project
, fs.FundSourceDescription as 'Fund Source'
, sum(fact.Dollars) as Dollars

from Fact fact
  inner join Project p on p.ProjectId = fact.ProjectId
  inner join ProjectList pl on p.ProjectId = pl.ProjectId
  inner join FundSource fs on fs.FundSourceId = fact.FundSourceId
  left outer join (
    ProjectGroup pg on pg.ProjectId = p.ProjectId
    inner join (
      select ProjectGroupId
      , coalesce(GroupListName, 'Solo Projects') as GroupListName
      from GroupList grouplist
    ) gl on gl.ProjectGroupId = pg.ProjectGroupId

where pl.ListName = 'My Project List'
  and gl.GroupListName = 'MyGroupList'

group by  coalesce(pg.GroupName, p.ProjectName)
, fs.FundSourceDescription

That doesn't change the outcome.  I would still not see the solo projects.

[CognosPaul]

Not exactly like that. The bottom SQL is doing the coalesce on the subselect, which won't accomplih anything. The coalesce needs to be done later:

Code Select Expand

select coalesce(pg.GroupName, p.ProjectName) as Project
, coalesce(gl.GroupListName , 'Solo Project') as GroupListName
, fs.FundSourceDescription as 'Fund Source'
, sum(fact.Dollars) as Dollars

from Fact fact
  inner join Project p on p.ProjectId = fact.ProjectId
  inner join ProjectList pl on p.ProjectId = pl.ProjectId
  inner join FundSource fs on fs.FundSourceId = fact.FundSourceId
  left outer join (
    ProjectGroup pg on pg.ProjectId = p.ProjectId
    inner join (
      select ProjectGroupId
      , GroupListName
      from GroupList grouplist
    ) gl on gl.ProjectGroupId = pg.ProjectGroupId

where pl.ListName = 'My Project List'
  and coalesce(gl.GroupListName , 'Solo Project') in ( 'MyGroupList', 'Solo Project')

group by  coalesce(pg.GroupName, p.ProjectName)
, coalesce(gl.GroupListName , 'Solo Project')
, fs.FundSourceDescription