Help with Template « myDBR Forums

myDBR Forums » General discussion

Help with Template

(3 posts) (2 voices)

Started by ajdjackson
Latest reply from myDBR Team

Tags:

Templates

ajdjackson, Member

Hi

I've created a report based on a template. It's working great except I'm get null displayed centrally above and below the report output.
Looking at the page source these nulls are coming from:

<div class="template">null<style type="text/css">

and

</table>null</div></div>

I've no idea where these nulls are coming from.

When I run the report normally say as pageview everything is fine - no nulls displayed anywhere.

I'm running latest build 2425.

Any thoughts?

Thanks

Jake

The template code is:

<style type="text/css">
.tg {border-collapse:collapse;border-spacing:0;border-width:1px;border-style:solid;margin:0px auto;}
.tg td{font-family:Arial, sans-serif;font-size:14px;padding:10px 5px;border-style:solid;border-width:0px;overflow:hidden;word-break:normal;}
.tg th{font-family:Arial, sans-serif;font-size:14px;font-weight:normal;padding:10px 5px;border-style:solid;border-width:0px;overflow:hidden;word-break:normal;}
.tg .tg-oiq4{font-weight:bold;background-color:#fffc9e;text-align:right}
.tg .tg-0ord{text-align:right}
.tg .tg-5t2x{font-weight:bold;background-color:#fffc9e}
.tg .tg-ht8o{font-weight:bold;color:#3166ff;text-align:right}
.tg .tg-wsnc{background-color:#fffc9e}
.tg .tg-1vxb{font-weight:bold;font-style:italic;font-size:20px;background-color:#fffc9e}
.tg .tg-e3zv{font-weight:bold}
.tg .tg-ud6e{font-weight:bold;background-color:#fffc9e;color:#3166ff;text-align:right}
.tg .tg-34fq{font-weight:bold;text-align:right}
</style>
<table class="tg" style="undefined; width: 900px">
<tr>
<th class="tg-031e"></th>
<th class="tg-031e"></th>
<th class="tg-031e"></th>
<th class="tg-031e"></th>
<th class="tg-031e"></th>
<th class="tg-031e"></th>
<th class="tg-031e"></th>
<th class="tg-031e"></th>
<th class="tg-031e"></th>
<th class="tg-031e"></th>
<th class="tg-031e"></th>
</tr>
<tr>
<td class="tg-1vxb" colspan="6">Financials</td>
<td class="tg-031e"></td>
<td class="tg-031e"></td>
<td class="tg-031e"></td>
<td class="tg-031e"></td>
<td class="tg-031e"></td>
</tr>
<tr>
<td class="tg-031e"></td>
<td class="tg-031e"></td>
<td class="tg-031e"></td>
<td class="tg-031e"></td>
<td class="tg-031e"></td>
<td class="tg-031e"></td>
<td class="tg-031e"></td>
<td class="tg-031e"></td>
<td class="tg-031e"></td>
<td class="tg-031e"></td>
<td class="tg-031e"></td>
</tr>
<tr>
<td class="tg-031e"></td>
<td class="tg-031e"></td>
<td class="tg-031e"></td>
<td class="tg-031e"></td>
<td class="tg-031e"></td>
<td class="tg-031e"></td>
<td class="tg-031e"></td>
<td class="tg-031e"></td>
<td class="tg-031e"></td>
<td class="tg-031e"></td>
<td class="tg-031e"></td>
</tr>
<tr>
<td class="tg-wsnc"></td>
<td class="tg-wsnc"></td>
<td class="tg-wsnc"></td>
<td class="tg-oiq4">Final Price</td>
<td class="tg-wsnc"></td>
<td class="tg-ud6e">#SP</td>
<td class="tg-wsnc"></td>
<td class="tg-5t2x" colspan="2">Tender Analysis</td>
<td class="tg-wsnc"></td>
<td class="tg-wsnc"></td>
</tr>
<tr>
<td class="tg-031e"></td>
<td class="tg-031e"></td>
<td class="tg-031e"></td>
<td class="tg-031e"></td>
<td class="tg-031e"></td>
<td class="tg-0ord"></td>
<td class="tg-031e"></td>
<td class="tg-031e"></td>
<td class="tg-031e"></td>
<td class="tg-031e"></td>
<td class="tg-031e"></td>
</tr>
<tr>
<td class="tg-031e"></td>
<td class="tg-e3zv">Costs</td>
<td class="tg-031e"></td>
<td class="tg-031e"></td>
<td class="tg-031e"></td>
<td class="tg-0ord"></td>
<td class="tg-031e"></td>
<td class="tg-031e"></td>
<td class="tg-031e"></td>
<td class="tg-031e"></td>
<td class="tg-031e"></td>
</tr>
<tr>
<td class="tg-031e"></td>
<td class="tg-031e"></td>
<td class="tg-031e"></td>
<td class="tg-34fq">Subcontractor</td>
<td class="tg-031e"></td>
<td class="tg-ht8o">#Subbie</td>
<td class="tg-031e"></td>
<td class="tg-34fq">Tender Value</td>
<td class="tg-031e"></td>
<td class="tg-ht8o">#Tender</td>
<td class="tg-031e"></td>
</tr>
<tr>
<td class="tg-031e"></td>
<td class="tg-031e"></td>
<td class="tg-031e"></td>
<td class="tg-34fq">Materials</td>
<td class="tg-031e"></td>
<td class="tg-ht8o">#Mats</td>
<td class="tg-031e"></td>
<td class="tg-34fq">Contracted Costs</td>
<td class="tg-031e"></td>
<td class="tg-ht8o">#ConCosts</td>
<td class="tg-031e"></td>
</tr>
<tr>
<td class="tg-031e"></td>
<td class="tg-031e"></td>
<td class="tg-031e"></td>
<td class="tg-34fq">Site Wide Charge</td>
<td class="tg-031e"></td>
<td class="tg-ht8o">#SiteWide</td>
<td class="tg-031e"></td>
<td class="tg-34fq"></td>
<td class="tg-031e"></td>
<td class="tg-ht8o"></td>
<td class="tg-031e"></td>
</tr>
<tr>
<td class="tg-031e"></td>
<td class="tg-031e"></td>
<td class="tg-031e"></td>
<td class="tg-34fq" style="background-color:#efefef;border-top:1px;">Totals Costs</td>
<td class="tg-031e" style="background-color:#efefef;border-top:1px;"></td>
<td class="tg-ht8o" style="background-color:#efefef;border-top:1px;">#TC</td>
<td class="tg-031e"></td>
<td class="tg-34fq" style="background-color:#efefef;border-top:1px;">#TendAnal</td>
<td class="tg-031e" style="background-color:#efefef;border-top:1px;"></td>
<td class="tg-ht8o" style="background-color:#efefef;border-top:1px;">#TendDiff</td>
<td class="tg-031e"></td>
</tr>
<tr>
<td class="tg-031e"></td>
<td class="tg-031e"></td>
<td class="tg-031e"></td>
<td class="tg-031e"></td>
<td class="tg-031e"></td>
<td class="tg-0ord"></td>
<td class="tg-031e"></td>
<td class="tg-031e"></td>
<td class="tg-031e"></td>
<td class="tg-031e"></td>
<td class="tg-031e"></td>
</tr>
<tr>
<td class="tg-031e"></td>
<td class="tg-031e"></td>
<td class="tg-031e"></td>
<td class="tg-34fq" style="background-color:#efefef;border-top:1px;">#ContType</td>
<td class="tg-031e" style="background-color:#efefef;border-top:1px;"></td>
<td class="tg-ht8o" style="background-color:#efefef;border-top:1px;">#Contrib</td>
<td class="tg-031e"></td>
<td class="tg-031e"></td>
<td class="tg-031e"></td>
<td class="tg-031e"></td>
<td class="tg-031e"></td>
</tr>
</table>

and the report code is :

DROP PROCEDURE IF EXISTS sp_DBR_Plot_Financials_Detailed
$$
CREATE PROCEDURE sp_DBR_Plot_Financials_Detailed(inPlot_ID varchar(25))
BEGIN

select 'dbr.template', '#Det_Plot_Fin_Anal_2';
/*select 'dbr.pageview';*/
select 'dbr.title','';

select 'dbr.colstyle', 1, '%.0f';
select 'dbr.colstyle', 2, '%.0f';
select 'dbr.colstyle', 3, '%.0f';
select 'dbr.colstyle', 4, '%.0f';
select 'dbr.colstyle', 5, '%.0f';
select 'dbr.colstyle', 6, '%.0f';
select 'dbr.colstyle', 7, '%.0f';
select 'dbr.colstyle', 8, '%.0f';
select 'dbr.colstyle', 10, '%.0f';

SELECT
x.tblSalesInv_Net AS 'Final Price[SP]',
w.tblTender_Amount AS 'Tender[Tender]',
y.Sub_Vals AS 'SubCon_Vals[Subbie]',
z.Materials AS 'Materials[Mats]',
t.Site_Wide_Charge AS 'Site Wide Charge[SiteWide]',
ifnull(y.Sub_Vals,0) + ifnull(z.Materials,0) as 'Contracted Costs[ConCosts]',
ifnull(y.Sub_Vals,0) + ifnull(z.Materials,0)+ t.Site_Wide_Charge AS 'Total Cost[TC]',
case
when x.tblSalesInv_Net>0 then x.tblSalesInv_Net - (ifnull(y.Sub_Vals,0) + ifnull(z.Materials,0)+ t.Site_Wide_Charge)
when x.tblSalesInv_Net<=0 then (ifnull(y.Sub_Vals,0) + ifnull(z.Materials,0))
end as 'Contrib[Contrib]',
case
when x.tblSalesInv_Net>0 then "Contribution"
when x.tblSalesInv_Net<=0 then "WIP"
end as 'ContType[ContType]',
case
when w.tblTender_Amount>0 then w.tblTender_Amount - y.Sub_Vals - z.Materials
else
0
end as 'Residual Spend[TendDiff]',
case
/*when length(w.tblTender_Amount)=0 then ''*/
when (w.tblTender_Amount - y.Sub_Vals - z.Materials) >=0 and length(x.tblSalesInv_Net) = 0 then 'Residual Spend'
when (w.tblTender_Amount - y.Sub_Vals - z.Materials) >=0 and length(x.tblSalesInv_Net) > 0 then 'Under Spend'
when (w.tblTender_Amount - y.Sub_Vals - z.Materials) <0 then 'Over Spend'
end as 'Tender Analysis[TendAnal]'
FROM
hilmark.tblsitesplotsjake t
LEFT OUTER JOIN hilmark.tblsitesjake u
ON t.Site_ID = u.Site_ID
LEFT OUTER JOIN hilmark.tblcontracts v
ON t.tblContractXidJobNo = v.tblContractXidJobNo
LEFT OUTER JOIN hilmark.tbltender w
ON t.tblContractXidJobNo = w.tblTender_JobNo
LEFT OUTER JOIN hilmark.tblsalesinv x
ON t.tblContractXidJobNo = x.tblSalesInv_XIDJob
LEFT OUTER JOIN (SELECT
tblcertificates.tblCert_XIDJob,
sum( tblcertificates.tblCertificates_AllocateWIP ) AS 'Sub_Vals'
FROM
hilmark.tblcertificates
GROUP BY
tblcertificates.tblCert_XIDJob) y
ON t.tblContractXidJobNo = y.tblCert_XIDJob
LEFT OUTER JOIN (SELECT
tblmaterials.tblmaterials_XIDJob,
sum( tblmaterials.tblMaterials_AllocateWIP ) AS Materials
FROM
hilmark.tblmaterials
GROUP BY
tblmaterials.tblmaterials_XIDJob) z
ON t.tblContractXidJobNo = z.tblmaterials_XIDJob
LEFT OUTER JOIN hilmark.tbljobanalysis a
ON v.tblContractXIDJobAnalysis = a.tblJobAnalysis_ID

WHERE

t.tblContractXidJobNo = inPlot_ID;

END
$$
ajdjackson, Member

Sorted!!!!

I didn't 'see' the Header/Footer' tabs in the template adding screen. Guess what - they had nulls in them. Doh!

Working now

Jake
myDBR Team, Key Master

Jake,
if your query returns just one row, you do not need Header or Footer. But if your query returns zero or more than one rows, you should put the CSS and the table header in the Header, repeating rows into the Row, and table footer in the Footer.

--
myDBR Team

Reply

You must log in to post.