Asked by:
Address block and parameter filter

-
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_nameQuery 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
Question
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 LiuFanny Liu
TechNet Community Support- Proposed as answer by Fanny LiuMicrosoft contingent staff, Moderator Wednesday, July 03, 2013 1:22 AM
- Unproposed as answer by hizic Wednesday, July 03, 2013 5:42 PM
-
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