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
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?