1. 

   ajdjackson, Member

   Hi

   Me again!

   I have created a report which has two parameters - company ID and Division ID. The user selects both these IDs from popups (dropdowns) and the report runs correctly.

   What I would like to do is as well as passing the IDs to be used in the linked report queries is to all pass the Company Name and Division Name to be used in the linked report titles.

   Hope you follow that

   Thanks again

   Jake
2. 

   myDBR Team, Key Master

   How are you calling the linked report?

   If you already have the ID's (company & division), why not just use the ID's to get the names. The less parameters you pass the easier the reports will be to maintain.

   --
   myDBR Team
3. 

   ajdjackson, Member

   Hi

   I may have confused you a bit.

   The report I'm running is not a linked report = it is a report that accepts user parameters.

   I'm running the report as follows:

   CREATE PROCEDURE sp_DBR_Residual_Value(inSite_ID tinyint(3),inPhase_ID tinyint(3),inLogin varchar(30))

   As I said previously the report runs fine. I can retrieve the Site Name and Phase Name using the user parameters but I can't find a way of putting these retrieved fields in the report title. Each method I've used results in 'unknown column in the field list' for example:

   select 'dbr.title',Site,Phase;

   Below is the query I'm using.

   Thanks

   Jake

   SELECT
   b.Site_ID as 'Site_ID[SiteID]',
   b.Site_Name as 'Site[Site]',
   c.tblContractXIDJobAnalysis as 'PhaseID[PhaseID]',
   e.tblJobAnalysis_JobAnalysis as 'Phase[Phase]',
   right(a.tblContractXidJobNo,length(a.tblContractXidJobNo)-locate("_",a.tblContractXidJobNo)) as 'Plot[Plot]',
   h.tblSalesInv_Net as 'Final Price',
   d.tblTender_Amount as 'Build Tender[Tender]',
   truncate((ifnull(d.tblTender_Amount,0)- a.Tender_FitOut),0) as 'Shell Tender[SCosts]',
   a.Tender_FitOut as 'FitOut Tender[FCosts]',
   a.Var_Construct as 'Build Vars[BVar]',
   a.Var_Customer as 'Cust. Vars[CVar]',
   truncate((ifnull(d.tblTender_Amount,0)+a.Var_Construct+a.Var_Customer),0) as 'Total Costs[TCosts]',
   f.Valuations as 'Valuations[Vals]',
   g.Materials as 'Materials[Mats]',
   ifnull(f.Valuations,0)+ ifnull(g.Materials,0) as 'Total Vals[TVals]',
   case
   when ifnull(f.Valuations,0) > truncate((ifnull(d.tblTender_Amount,0)- a.Tender_FitOut),0) then 0
   else truncate((ifnull(d.tblTender_Amount,0)- a.Tender_FitOut) - ifnull(f.Valuations,0),0)
   end as 'Shell Res. Spend[SVal]',
   case
   when d.tblTender_Amount is null then 0
   else (ifnull(d.tblTender_Amount,0)+a.Var_Construct+a.Var_Customer) - ifnull(f.Valuations,0) - ifnull(g.Materials,0)
   end as 'Total Res. Spend[RVal]'

   FROM
   hilmark.tblsitesplotsjake a
   INNER JOIN hilmark.tblsitesjake b
   ON a.Site_ID = b.Site_ID
   INNER JOIN hilmark.tblcontracts c
   ON a.tblContractXidJobNo = c.tblContractXidJobNo
   LEFT OUTER JOIN hilmark.tbltender d
   ON a.tblContractXidJobNo = d.tblTender_JobNo
   INNER JOIN hilmark.tbljobanalysis e
   ON c.tblContractXIDJobAnalysis = e.tblJobAnalysis_ID
   LEFT OUTER JOIN
   (SELECT
   hilmark.tblcertificates.tblCert_XIDJob,
   SUM(hilmark.tblcertificates.tblCertificates_AllocateWIP) as Valuations
   FROM
   hilmark.tblcertificates
   group by
   hilmark.tblcertificates.tblCert_XIDJob) as f
   ON a.tblContractXidJobNo = f.tblCert_XIDJob
   LEFT OUTER JOIN
   (SELECT
   hilmark.tblMaterials.tblMaterials_XIDJob,
   SUM(hilmark.tblMaterials.tblMaterials_AllocateWIP) as Materials
   FROM
   hilmark.tblMaterials
   group by
   hilmark.tblMaterials.tblMaterials_XIDJob) as g
   ON a.tblContractXidJobNo = g.tblMaterials_XIDJob
   LEFT OUTER JOIN hilmark.tblsalesinv h
   ON a.tblContractXidJobNo = h.tblSalesInv_XIDJob

   where (a.Site_ID = inSite_ID or inSite_ID = '%') and (e.tblJobAnalysis_ID = inPhase_ID or inPhase_ID = '%')

   Order by
   a.Site_ID ASC,
   e.tblJobAnalysis_ID ASC,
   a.tblContractXidJobNo ASC;
   
4. 

   myDBR Team, Key Master

   Hi,
   you can use a query that will return the dbr.title command and the desired title.

   select 'dbr.title', Site_Name
   
   from hilmark.tblsitesjake
   
   where b.Site_ID = inSite_ID;

   --
   myDBR Team
5. 

   ajdjackson, Member

   Great many thanks for that.

   It was the only thing I hadn't tried.

   Jake

Reply

You must log in to post.