Expected data is not coming the moment filter is added in the detail filter of R

[xyz]

Hi Gurus,

I have a requirement, where I have custom queries, which has filters joined with fact table. Output is not coming as expected when I use filter from one of the query subject. I am using Cognos 10.2.1 with the relational model, sql server as a database.

I have a query written in the sql server, which will display as below based on the same I need to get the output.

Select count(j.id), cs_sv.customvalue from jiraissue j

left outer (select v.issue, f.cfname, v.STRINGVALUE, o.customvalue from customvalue v
                   inner join custom f on F.ID = V.CUSTOMFIELD
                   inner join customoption o on v.STRINGVALUE = cast(o.id as varchar(30))
                  where f.cfname = 'Severity') cf_sv on cf_sv.issue = j.id

left outer join (select v.issue, f.cfname, v.STRINGVALUE, o.customvalue from customvalue v
                   inner join custom f on F.ID = V.CUSTOMFIELD
                   inner join customoption o on v.STRINGVALUE = cast(o.id as varchar(30))
                  where f.cfname = 'Cloned Issue') cf_clone  on cf_clone.issue = j.id
Where
cf_clone.customvalue <> 'Yes'
group by
cf_sv.customvalue

When I try to do the same in the FM, when I try to use the filter cf_clone.customvalue <> 'Yes' in the detail filter of Report Studio, I am getting the generated SQL twice with the combination of 'Severity' filter join once and 'Cloned Issue' join once with the detail filter, which is not getting generated similar to above query. Can you guys please help me, where I am doing wrong.

Can you guys please find the attachment of model in the screenshot.

Your help is much appreciated.

Thanks & Regards,
XYZ

[bdbits]

Answered identical question under Report Studio forum.