How to exclude "no data" panels from repeating panel and repeating rows

[willwaterston]

Hi,

I’m using repeating panels and repeating rows. My dataset is production data for a mining application that shows production rates for each truck on a minesite. I have 2 variables, one for truckname and one for loadername. I’m using repeating panels to show each truck data, and repeating rows for each loader.

My query is

SELECT TOP(50) startTime as time, cycleProdRateBCM, deviceName FROM PitOps.dbo.tTruckProduction
where deviceName=${TruckName}
and loaderName=${LoaderName}

The rows are showing the loaders and the panels are showing the trucks, however the repeating panels are showing a panel for EVERY truck, even if there is no production data for that truck+loader combination, so my chart is very full. How can I remove the panels that have no data?

Thanks

[yosiasz]

Can you create variables only for records that only values then only the good trucks will show up

What data source type is it

[willwaterston]

Thanks for the reply,

MSSQL Server database is my datasource.

my variable definitions are currently

TruckName : select distinct deviceName from ttruckProduction
LoaderName : select distinct loaderName from ttruckProduction

How would I create a variable only for trucks that have production data?

All the trucks in the entire dataset will have some production data, but each truck only has production data for certain loaders (typically only 1 loader in a day), so for each loader (row) there will be different trucks (panels) shown

[yosiasz]

Self referencing table? Please post schema of that table

[willwaterston]

this is the table schema, below. This is my first attempt at a dynamic dashboard, actually this is my first use of Grafana so I’m still trying to work out how the various parts fit together

[id] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[deviceID] [int] NULL,
[deviceName] nvarchar NULL,
[loaderID] [int] NULL,
[loaderName] varchar NULL,
[enabled] [bit] NOT NULL,
[startTime] [datetime] NOT NULL,
[endTime] [datetime] NOT NULL,
[modified] [datetime] NOT NULL,
[payloadTonnes] [real] NULL,
[payloadBCM] [real] NULL,
[loadCycleID] [bigint] NULL,
[shiftProdRateTonnes] [real] NULL,
[hourProdRateTonnes] [real] NULL,
[cycleProdRateTonnes] [real] NULL,
[shiftProdRateBCM] [real] NULL,
[hourProdRateBCM] [real] NULL,
[cycleProdRateBCM] [real] NULL,
[cycleHaulingTime] [int] NULL,
[materialID] [int] NULL,
[materialDensity] [real] NULL,

(deviceName is the truck name)

[willwaterston]

Here is a small sample dataset, the main point to note is that only certain trucks will have production rates for certain loaders, hope thats clear

loaderName | deviceName | startTime | cycleProdRateTonnes | cycleProdRateBCM
EX0018 | RD4707 | 2023-05-07 01:25:47.000 | 682.1369 | 278.4232
EX0018 | RD4707 | 2023-05-07 01:08:08.000 | 756.374 | 308.7241
EX0018 | RD4707 | 2023-05-07 00:43:12.000 | 499.5722 | 203.907
EX1161 | RD1817 | 2023-05-07 01:10:41.000 | 231.9032 | 144.0392
EX1161 | RD2127 | 2023-05-07 01:56:06.000 | 210.8711 | 130.9758
EX1161 | RD2139 | 2023-05-07 01:28:27.000 | 268.554 | 166.8037

[yosiasz]

So there can be data where there is a truck but no loader associated with it.

Sonfor trucks what if you did

select distinct deviceName
 from ttruckProduction
 where loadername is not null 
and rtrim(ltrim(loadername)) <> ''

Side note unrelated: who designed this table?

[willwaterston]

no, not exactly. every row in the table will always have both a truckname and loader name. but each loader will have different truck records, for example loader EX01 will have truck records for RD01, RD02, RD03, and loader EX02 will have truck records for RD04, RD05, RD06

I designed the table, why?

[yosiasz]

@willwaterston

designed the table, why?

If truck A changes it name and you have a thoudand records with that truck name what happens?

Anyways not the main focus. Lets solve your main problem

Did you try query I recommended?

[willwaterston]

I did try your suggestion just in case, but it made no change, as the loaderName is never null

[yosiasz]

So are there any rows where decicename is empty or null

[willwaterston]

Truck A will always be called Truck A, it never changes its name. Perhaps I can explain a bit better. On the mine we have “circuits”, a circuit is defined by the loader that is loading all the trucks on that circuit. Say a mine has 10 loaders (10 circuits) and 100 trucks. There would be 10 trucks “assigned” to each loader (in a very simple case). So the table will only contain production data for each loader for the trucks that are assigned to it. So if truck A is assiged to loader A and truck B is assigned to loader B then there will be records in the table for loaderA+truckA and loaderB+TruckB but never for loaderA+truckB

so the panels showing “no data” are the ones for loaderA+truckB and there won’t be any records with that combination

the only thing that can happen is that truckA may be re-assigned to loaderB in which case records will start to appear for truckA+loaderB, and I would assume that a new panel automatically appears for that combination

[willwaterston]

there wont be any records with null loader or null truck

[yosiasz]

Select distinct loadername
Where deviceName in (
${truckname:singlequote}
)

Maybe this. Hitting the hay

[willwaterston]

I’ll try it. have a good night

[willwaterston]

no change, I also tried modifying the TruckName variable -

select distinct deviceName from ttruckProduction
where loaderName in ${LoaderName}

[yosiasz]

Let me ingest your data and see what we can do. Is the last on shown the issue

Tried all kinds of stuff, this seems to be a missing functionality. not sure why it brings that bogus data

[willwaterston]

Hi, I didn’t quite understand your last comment, but I can give you a dataset if you think it will help?

I could export from sql into .csv?

Thanks

[yosiasz]

no you good. I think it is a “bug” in grafana. It just an issue with repeated panels for some reason when there is no data it puts up a panel.

[willwaterston]

so there is nothing I can do about it?