nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

How to display strings missing from sequence in report

bugsy

I am very new to BIRT. I have a dataset that returns typeNames from a database query like so 'A22, A25, B3, B7'.
 
What I need to display on the report are the typeNames, which I have from the query, and the typeNames that are missing from the sequence.
 
So for the above I will also need to display 'Missing: A23, A24, B4, B5, B6' on the report.
 
I have to figure out where to put code that can strip off that first character and sequence the numbers. And also how to reference them on the report once I do.
 
I have been researching this for a couple of days and cannot figure out how to proceed.
 
I would appreciate any suggestions. Thank you.

Find more posts tagged with

Comments

JFreeman

You should be able to use a persistent global variable.
You can create it in the beforeOpen of the data set.
In the onFetch of the data set you can populate it with the typeNames.
 
Then, you can create a text element in the report and iterate through the persistent global variable in the onCreate of the text element. While iterating through you can use some logic to determine which values are missing and then set the text of the text element to display the desired values.

PaulCooper

You can also do this via SQL, although the exact details varies depending on the database type the essence is described below:
 
Assuming your original SQL is (select typeName, ... from ....) then your SQL would be:
 
with d as (select ... "typeName", ... from ....),
c1 as (select c.*, 
 row_number() over (order by "prefix" asc) "order_num"
 from (select left(typeName, 1) "prefix",
 min(to_number(substr(typeName, 2, length(typeName)))) "min_n",
 max(to_number(substr(typeName, 2, length(typeName)))) "max_n" from d group by left(typeName, 1)) c),
c2 as (select c1.*,
(select sum(c1_2."max_n" - c1_2."min_n" + 1) from c1 c1_2 where c1_2."order_num" < c1."order_num") "offset",
(select sum(c1_2."max_n" - c1_2."min_n" + 1) from c1 c1_2 where c1_2."order_num" <= c1."order_num") "range_max"
from c1),
range as (select LEVEL "num" from DUAL connect by LEVEL<=(select sum(c1."max_n" - c1."min_n" + 1) from c1)),
range2 as (select concat(c2."prefix", (range."num" - c2.offset)) "typeName2"
from range, c2
where range."num" > c2."offset" and range."num" <= c2."range_max")
select range2."typeName2", d.*
from d left join range2 on range2."typeName2" = d."typeName"
 
d is the original data set
c1 groups the typeName by its extracted prefix and calculates the max and min of the typeName number and gives a row number for the prefix
c2 is c1 plus calculations to give 1) the number of distinct typeNames that come before this typeName prefix and 2) the number of distinct typeNames that come before this typeName prefix and include this typeName prefix.
range gives a sequence from 1 to the number of distinct typeNames including missing ones.
range2 takes the number from range and offset and range_max from c2 and creates a list of typeNames including the missing ones.
range2 is then left joined to the original data set (d) to duplicate the original data set (d) and adding a single row for each missing typeName with all the fields other than typeName2 blank.
 
The SQL above was written for Oracle so if you use some other database then you need to change the "range" SQL (See <a data-ipb='nomediaparse' href='http://sqlbisam.blogspot.co.uk/2014/01/generate-series-or-list-of-numbers.html'>http://sqlbisam.blogspot.co.uk/2014/01/generate-series-or-list-of-numbers.html</a> for the t-sql version) some function such as to_number are different for other databases. Also some databases may not support analytical functions such as row_number() over (...)
 
Finally I have to say I haven't tested the above SQL but it was only met to be for demonstration purposes.

bugsy

Sorry to be so late to reply. Have been off this. Thank you for the assistance. I decided the sql approach would work better for me although I wished to have a more BIRT solution.
 
I decided to use a cursor, although it would not have been my first choice I needed to get something working.
 
 SELECT to_number(substr(the_num,2)) after_gap, substr(the_number,1,1) gap_char, 
LAG(to_number(substr(the_num,2)),1,0) OVER (ORDER BY substr(the_number,1,1)) before_gap, 
LAG(substr(the_num,1,1),1,'0') OVER (ORDER BY substr(the_num,1,1)) prev_char
 
Again thank you for the responses.