Address block and parameter filter

Question

• 

  

  0

  Sign in to vote

  This is pretty simple. Two issues. 

  1. I've created a parameter as a dropdown menu using a second dataset. The problem is I only need the dropdown menu to show:

  *A certain customer class

  *Customers WITHOUT a zero balance

  Each time I try to manipulate the query in my second dataset it messes things up. Can anyone provide an example of how I would set up those filters in the dropdown menu?

  Query for first dataset:

     

  select
  CM.CUSTNMBR Customer_ID,
  CM.CUSTNAME Customer_Name,
  CM.PYMTRMID Customer_Terms,
  CM.CUSTCLAS Customer_Class,
  CM.PRCLEVEL Price_Level,
  CM.SLPRSNID Salesperson_ID,
  CM.ADDRESS1 Address_1,
  CITY as [City],
  STATE as [State],
  ZIP as [Zip],
  case RM.RMDTYPAL
    when 1 then 'Sale / Invoice'
    when 3 then 'Debit Memo'
    when 4 then 'Finance Charge'
    when 5 then 'Service Repair'
    when 6 then 'Warranty'
    when 7 then 'Credit Memo'
    when 8 then 'Return'
    when 9 then 'Payment'
    else 'Other'
    end Document_Type,
  RM.DOCNUMBR Document_Number,
  RM.DOCDATE Document_Date,
  RM.DUEDATE Due_Date,
  RM.CSPORNBR Customer_Purchase_Order_Number,
  case
    when RM.RMDTYPAL < 7 then RM.ORTRXAMT
    else RM.ORTRXAMT * -1
    end Document_Amount,
  case
    when RM.RMDTYPAL < 7 then RM.CURTRXAM
    else RM.CURTRXAM * -1
    end Unapplied_Amount,
  case
    when DATEDIFF(d, RM.DUEDATE, getdate()) < 31
       and RM.RMDTYPAL < 7 then RM.CURTRXAM
    when DATEDIFF(d, RM.DOCDATE, getdate()) < 31
       and RM.RMDTYPAL > 6 then RM.CURTRXAM *-1
    else NULL
    end [Current],
  case
    when DATEDIFF(d, RM.DUEDATE, getdate()) between 31 and 60
       and RM.RMDTYPAL < 7 then RM.CURTRXAM
    when DATEDIFF(d, RM.DOCDATE, getdate()) between 31 and 60
       and RM.RMDTYPAL > 6 then RM.CURTRXAM * -1
    else NULL
    end [31_to_60_Days],
  case
    when DATEDIFF(d, RM.DUEDATE, getdate()) between 61 and 90
       and RM.RMDTYPAL < 7 then RM.CURTRXAM
    when DATEDIFF(d, RM.DOCDATE, getdate()) between 61 and 90
       and RM.RMDTYPAL > 6 then RM.CURTRXAM * -1
    else NULL
    end [61_to_90_Days],
  case
    when DATEDIFF(d, RM.DUEDATE, getdate()) > 90
       and RM.RMDTYPAL < 7 then RM.CURTRXAM
    when DATEDIFF(d, RM.DOCDATE, getdate()) > 90
       and RM.RMDTYPAL > 6 then RM.CURTRXAM *-1
    else NULL
    end [91_and_over]
  
  from RM20101 RM
  
  inner join RM00101 CM
       on RM.CUSTNMBR = CM.CUSTNMBR
  
  where RM.VOIDSTTS = 0 and RM.CURTRXAM <> 0 and CM.CUSTCLAS = 'Z-PROTRACK' and CM.CUSTNAME = @customer_name

  Query for second dataset:

      select distinct
  CUSTNAME as [Customer Name]
  from RM00101

  
  

  2. After a customer is selected from the parameter the report shows the customers name, address, city, state and zip. The problem is the name, city, state and zip are really spaced out. Is there a way to get them closer together? It is formatted like this in the design view (name on top, address in middle, city-state-zip on bottom). 

  

  ---

  ipainter

  
  
  

  • Edited by hizic Tuesday, June 25, 2013 10:21 PM

  Tuesday, June 25, 2013 10:18 PM

  Reply

  |

  Quote