1. 

   ajdjackson, Member

   Hi

   I need a steer on how I'd do the following:

   I have a report where I wish to give the user a means of running the report by entering 1 of 3 variables - to either enter a PO number or Item Code or Item Description.
   I would like them to be able only to enter one of these parameters.

   I've created the following report which works but doesn't block them entering more than one parameter.

   select 'dbr.parameters.show'; select 'dbr.css','.row_header .report_params_header_row {display:none;}';

   select 'dbr.calc','[LTotal]','([rQty]*[Price])/[Rate]'; select 'dbr.calc','[GBPCost]','([Price])/[Rate]'; select 'dbr.colstyle','Price','%0.5f'; select 'dbr.colstyle','Rate','%0.5f'; select 'dbr.colstyle','LTotal','%0.5f'; select 'dbr.colstyle','GBPCost','%0.5f';

   select b.PONumber as "PO Number[PONum]", c.SuppName as "Supplier[Supp]", date_format(b.OrderDate,"%d/%m/%Y") as "Order Date", a.ItemCode as "Item Code[iCode]", a.Description as "Description[Desc]", f.GRN as "Batch #[Batch]", a.QtyOrdered as "Qty Ordered[oQTY]", a.QtyRec as "Qty Received[rQty]", a.Price as "Unit Price[Price]", (case c.Currency when "Euro" then "Euro" when "US Dollar" then "USD" when "Yen" then "Yen" when "CHF" then "CHF" else "GBP" end) as "Curr.[Cur]", ifnull(d.Rate,1) as "Rate[Rate]", null as "£ Cost[GBPCost]", null as "Item Total[LTotal]" from podetails a left outer join poheader b on a.PONumber = b.PONumber left outer join supplier c on b.Supplier = c.`SageCode` left outer join tblcurxref e on c.Currency = e.PCurrency left outer join tblcurrency d on e.Currency = d.Currency and Year(d.CDate) = Year(b.OrderDate) and Month(d.CDate) = month(b.OrderDate) left outer join goodsinaudit f on a.PONumber = f.POnumber and a.ItemCode = f.ItemCode

   where (case when length(inPO) >0 then a.PONumber = inPO when length(inItemC) >0 then a.ItemCode = inItemC else a.Description = inItemD end) and a.QtyRec > 0 order by b.OrderDate desc;

   As you can see I'm checking to see if any of the parameters have been entered by checking their length. It works but I don't think it's the best way to do this.

   Any help most appreciated.

   Cheers

   Jake
2. 

   myDBR Team, Key Master

   Jake,
   that is a perfectly ok way of doing it. Another option is to check if parameter is empty before the query and then use ifnull-function.

   if (inPO='') then set inPO = null end if;
   
   select ...
   
   ...
   
   where a.PONumber = ifnull(inPO, a.PONumber)...
   
   

   --
   myDBR Team
3. 

   ajdjackson, Member

   Hi
   Thanks for getting back.

   I'll have a look at your suggestion as it looks easier than case statement.

   The bit I really would like to be able to do is to blank the other two input files when a value is entered. This would prevent 2 or more parameters being sent to the stored procedure.

   I can't see a way to get at the parameter form to do this.

   Cheers

   Jake

Reply

You must log in to post.