Creating Custom Queries in Report Builder with LQE Data Source
We are currently in the process of migrating from a Data Warehouse to a Lifecycle Query Engine data source. We have a number of report utilising custom queries and I am struggling to migrate even the most basic of queries.
Assuming I have a Requirement artifact with a Source ID attribute. There are 1000 artifacts, 450 have a Source ID of "A" and 300 have a Source ID of "B".
I have tried using the following:
1. COUNT(IF(LCASE($Requirement:Source ID$) = "a", 1, ""))
2. COUNT(DISTINCT IF(LCASE($Requirement:Source ID$) = "a", $Requirement:Id$, ""))
Query 1 returns 750, i.e. the number of Requirement artifacts where Source ID exists, not just Source ID = "A"
Query 2 return 451, i.e. all the distinct IDs where Source ID = "a" plus 1 for represented by the failure case.
I have also tried using:
SUM(IF(LCASE($Requirement:Source ID$) = "a", 1, 0))
However, this returns a blank value.
This seems like a ridiculously simple thing to do but I cannot find the answer and daren't attempt any of the actual more complex expressions that I need to convert. I know I could build this example just using the "Calculated Value" option but I am trying to do this to learn the basics, and I know I could filter the data in Choose data page but I want to be able to report on multiple metrics in one report so that is not an option.
Any help or pointers to useful resources online would be greatly appreciated!
2 answers
Further info:
If I include the Source ID as an attribute to display in the report I get the following output:
Row | Source ID | Custom Expression
-----|------------|-----------------------
1 | | 0
2 | A | 450
3 | B | 300
So if I display the Source ID it can count it correctly, but if I don't it provides the combined value??