CODEWITHSUNDEEP
ssrs-2008-r2reportbuilder3.0
Kram_Koorbse
Kram_Koorbse

Reputation: 492

SSRS - Why is my report only returning dates from current year?

I have the following query that works correctly in SSMS and it works in SSRS report builder 3.0's Query Designer. It takes three parameters one of which (@year) is how many years back from today do you want results from. I have specified for @year to be -1, -2, -3, -4, or -5. But when I run the report, no matter what value I choose for year it only returns results from January of this current year (see image). When I add the @year expression to the report it shows the correct value is being passed to the query. Is there something I am doing wrong?

declare @complete varchar(1) = 'y',
@supplier_id Nvarchar(max) = 1,
@year int = -1


select p21_view_address.name [Supplier Name], p21_view_po_hdr.supplier_id [Supplier ID], p21_view_po_line.po_no [PO #],p21_view_po_hdr.location_id [PO Location ID], line_no [PO Line #]
    , p21_view_inv_mast.item_id [Item ID], p21_view_inv_mast.item_desc [item_desc],  p21_view_po_line.date_created [PO Date Created], unit_quantity [Qty Ordered]
    , qty_received [Qty Received] , convert(varchar(10), cast(p21_view_po_line.date_due as date), 101) [Expected Date], coalesce(convert(varchar(10), cast(p21_view_po_line.received_date as date), 101), 'Not Received Yet') [Receipt Date], p21_view_po_hdr.approved [PO Approved?], [pi].average_lead_time [Lead Time]
    , convert(varchar(10), cast(po_hdr_ud.purchase_order_confirmation as date), 101) [Confirmation Date], convert(varchar(10), cast(p21_view_po_line.required_date as date), 101) [Required Date] from p21_view_po_line
INNER JOIN p21_view_po_hdr ON p21_view_po_line.po_no = p21_view_po_hdr.po_no
join p21_view_inv_mast on p21_view_inv_mast.inv_mast_uid = p21_view_po_line.inv_mast_uid
join p21_view_supplier_purchasing_info [pi] on p21_view_po_line.inv_mast_uid = [pi].inv_mast_uid and p21_view_po_hdr.location_id = [pi].location_id and p21_view_po_hdr.supplier_id = [pi].supplier_id
join p21_view_address on p21_view_po_hdr.supplier_id = p21_view_address.id
join po_hdr_ud on p21_view_po_line.po_no = po_hdr_ud.po_no
where p21_view_po_line.cancel_flag = 'N' and p21_view_po_line.delete_flag = 'N' and p21_view_po_line.complete = @complete and (@supplier_id = 1 or p21_view_po_hdr.supplier_id in (@supplier_id))
and p21_view_po_line.date_created > DATEADD(year, @year, GETDATE())
order by p21_view_po_line.date_created asc

Results from Running the Report in SSRS Results from Running the Report in SSRS

Results from SSRS Query Designer enter image description here

Upvotes: 0

Views: 110

Answers (1)

Kram_Koorbse
Kram_Koorbse

Reputation: 492

I not sure why this happens, but I fixed the issue by removing the Sort Tablix property I had on the table. I had it set on PO_Date_created but I also had order by PO_Date_created in my SQL code. When I removed the Tablix sort property from the table then the results returned properly. enter image description here

Upvotes: 1

Related Questions