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]

Your SQL is what you have put in the FM query subject? The query subject SQL is always going to be used as the basis for the query of that object, then any Report Studio filters are going to be added in to what is in the query subject. What you are seeing in the generated SQL is a reasonable expectation - the base query, plus your filter.

If you want to filter cf_clone at runtime in the RS query, do not put it in the FM query subject.

Or have I missed something?