I’m creating a BAQ for a dashboard that list all material for jobs, what I would like to do is add a blank space after each job number to make the list more readable for the user. Can this even be done?
Hi, Rich. I strongly recommend against doing this. Carefully consider what the users are going to use it for. I might recommend either using the grid grouping functionality, or creating a publish and subscribe dashboard.
For instance, create a BAQ that shows only job head information, then when the user clicks on this, this will allow a second BAQ on the screen to filter only for that job’s materials.
Can I have a bit more information in relation to the application, and I can advise better?
That said, here is some quick SQL that can be used to create a view linked to an external BAQ. You’ll want to add additional JobMtl fields and joins that you want, creating additional empty string ‘’ in the bottom select statement to match.
There is a lot of reasons why this is a bad idea: for instance, the grid won’t keep the blank space when you sort it, and it really takes away the advantages of having a dashboard rather than just a CSV dumping report.
SELECT Comp, Job, Seq, PartNum FROM
(SELECT m.Company, m.JobNum, m.MtlSeq, m.Company As Comp, m.JobNum As Job, CONVERT(NVARCHAR,m.MtlSeq) As Seq, m.Partnum
FROM Epicor10.Erp.JobMtl m
UNION
SELECT s.Company, s.JobNum, s.MtlSeq, '', '', '', ''
FROM (SELECT Company, JobNum, MAX(MtlSeq) + 10 As MtlSeq FROM Epicor10.Erp.JobMtl GROUP BY Company, JobNum) s
) x
ORDER BY Company, JobNum, MtlSeq
1 Like
One idea to add to what Joseph said would be to do conditional formatting on the dashboard. When the job number change the row color? That way the user can see there is a difference.
Ken, this is a great idea. You could set a row rule in a dashboard to alter the color of every other job’s group of materials.
Do you know how to do that? I don’t see how to do the conditional formatting based on a group change.
You also could possibly set DefaultAppStyling to false on the grid, then increase the font size and even increase the grid cell height to add space and better visibility.
I’ve done that here (not an extreme case) as well as add some color rules based on code.
Changing (adding) font to grid:
//Fonts for my panels
System.Drawing.FontFamily ff = new System.Drawing.FontFamily("Times New Roman");
System.Drawing.Font f = new System.Drawing.Font(ff,14,System.Drawing.FontStyle.Bold);
epiUltraGridXfers.Font = f;
epiUltraGridPartLoc.Font = f;
epiUltraGridBin.Font = f;
PrinterGrid.Font = f;
//hide a column
PrinterGrid.DisplayLayout.Bands[0].Columns[0].Hidden = true;
//set a width
PrinterGrid.DisplayLayout.Bands[0].Columns[1].Width = 150;
If your job numbers are continuously incremented numbers with no gaps, you could probably define a column in your BAQ with the calculation convert(int,JobMtl.JobNum) % 2 as a field called ToggleRow. However, if you have gaps in your job numbers or have any job numbers that have any letters in them (very common), I might suggest using an external BAQ using a view that looks something like this:
SELECT m.Company, m.JobNum, m.MtlSeq, j.RowNum % 2 As ToggleRow FROM Erp.JobMtl m INNER JOIN (SELECT Company, JobNum, ROW_Number() OVER(PARTITION BY Company ORDER BY Company, JobNum) As RowNum FROM Erp.JobHead) j ON m.Company = j.Company AND m.JobNum = j.JobNum
This gives you a data column that basically alternates between 0 and 1 as the job number changes.
You can then add a row rule to the grid that sets the color for all fields where the calculated field is 1. The 0 would remain the default background color.
(This does assume for simplicity that all jobs have at least one material, though you could alter the query as a tradeoff for performance to use the distinct jobs in the job material table in your subquery.)
EDIT: If this is possible through the application layer, I’m going to hazard a guess based on my experience with these grids that the performance will be better performing this in the data layer albeit perhaps not with my exact implementation.
1 Like