none
Address block and parameter filter

    Question

  • 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

All replies

  • Hello,

    1. "  The problem is I only need the dropdown menu to show:

    *A certain customer class
    *Customers WITHOUT a zero balance "

    Can you post more details about your requirement? It seems that you want to filter data in the second dataset query. Did you try to specify WHERE condition in the dataset query? It is hard to interpret without sample data.

    2. Based on the screen shot, you display the values "customers name", "address", "city", "state and zip" in textboxs. In order to meet your requirement, please try to add a Rectangle and move all textboxs into the rectangle.

    Regards,
    Fanny Liu


    Fanny Liu
    TechNet Community Support

    Thursday, June 27, 2013 6:57 AM
    Moderator
  • Thank you for the attempt Fanny. 

    1. I've posted all the details. The SQL for the first and second dataset are posted. I'm not sure what else you are looking for....

    Yes I tried setting the WHERE condition. Every time I tried it resulted in an error. 

    2. Unfortunately, this did not work. The result was the same. 


    ipainter

    Wednesday, July 03, 2013 5:42 PM