Adding Blank Line in Jasper Studio Report if Sequence is missing

[ryanneves_1]

I have a SQL query that pulls a list like this based off the data returned from the query.

Lane Run 
10      2
10      3
10      6

(new page on lane change)

11  2
11  3
11  5

I would like it to insert a blank line when one is missing like below, anyone have ideas on doing that.

Lane Run 
10      2
10      3


10      6

(new page on lane change)

11  2
11  3

11  5

   

[Alex42]

This is SQL work. I suggest you generate the missing data and add a flag that will tell you if the row is generated or not(ex: O=original G=generated). Your result data set would look like

Lane Run Flag
10      2     O
10      3     O
10      4     G
10      5     G
10      6     O

(new page on lane change)

The flag will be the conditional that will display or not the values in the line

 

[ryanneves_1]

This is kinda what I came up with but I don't know how to make it work with JasperReports.

My query would be something like this:

WITH CTE AS (
SELECT lane_number, MIN(sequence) minSeq, MAX(sequence) maxSeq
from v_sample
GROUP BY lane_number
UNION ALL
SELECT lane_number, minSeq + 1, maxSeq
from CTE
WHERE minSeq+1 <= maxSeq
)

Select t2.*,  t1.lane_number, t1.minSeq as sequence from CTE t1 left join v_sample t2 on t1.lane_number = t2.lane_number and t1.minSeq = t2.sequence
order by t1.lane_number, t1.minSeq;

 

But it would have my original one nested in it for the VIEW, but I don't see Jaspersoft supporting this in Reports.

create view v_sample as 
(
SELECT products.client_id, 
          clients.name AS client_name, 
          cars.vin, 
          cars.make, 
          cars.model, 
          cars.style, 
          cars.has_title, 
          cars.general_description, 
          cars.custom_description, 
          cars.mileage, 
          cars.'year', 
          cars.ext_paint, 
          cars.int_color, 
          cars.manheim_submitted_on, 
          products.stock_number, 
          products.intake_date, 
          manheim_auction_lanes.lane_number, 
          manheim_auction_listings.sequence, 
          product_titles.status AS title_status, 
          manheim_auction_listings.gross_sale_price, 
          product_purchases.sale_fee AS sales_fee, 
          products.asking_price, 
          products.asking_price_condition, 
          manheim_auctions.auction_date, 
          manheim_auctions.auction_number, 
          manheim_auction_listings.created_at, 
          manheim_auction_listings.updated_at, 
          product_purchases.total_spent, 
          users.username               AS buyer_username, 
          product_statuses.id          AS product_status, 
          product_statuses.description AS status_description, 
          product_purchases.purchase_price, 
          ( 
                 SELECT Count(*) 
                 FROM   manheim_auction_listings AS listings_sub 
                 WHERE  listings_sub.car_id = manheim_auction_listings.car_id 
                 AND    listings_sub.id <> manheim_auction_listings.id) AS previous_auction_count
FROM      manheim_auction_listings 
JOIN      cars 
ON        cars.id = manheim_auction_listings.car_id 
JOIN      products 
ON        cars.product_id = products.id 
JOIN      product_purchases 
ON        current_product_purchase_id = product_purchases.id 
JOIN      users 
ON        product_purchases.buyer_id = users.id 
JOIN      manheim_auctions 
ON        manheim_auctions.id = manheim_auction_listings.manheim_auction_id 
AND       manheim_auctions.auction_date = $p{auctiondate} 
JOIN      manheim_auction_lanes 
ON        manheim_auction_lanes.id = manheim_auction_listings.manheim_auction_lane_id 
JOIN      product_categories 
ON        products.product_category_id = product_categories.id 
AND       product_categories.consigned_inventory=0 
JOIN      clients 
ON        clients.id = products.client_id 
AND       clients.id LIKE $p{loggedinuserattribute_clientid} 
LEFT JOIN product_titles 
ON        product_titles.id = product_purchases.product_title_id 
JOIN      product_statuses 
ON        product_statuses.id = products.product_status_id 
ORDER BY  manheim_auction_lanes.lane_number DESC, 
          manheim_auction_listings.sequence DESC
          )
go