Sanjeet
asked on
Data not appearing in SSRS report
I am using SSRS 2005 and Database SQL Server 2005.
I have a stored proc that I use to populate a SSRS report. All the fields get populated except for one field.
My Shipping Location Field does not populate.
When I run the SQL in the database all the fields return data.
In the SSRS report the code for the Shipping location is:
=IIf(Fields!shipping_locat ion.Value = "", Fields!cinv_shipping_locat ion.Value, Fields!shipping_location.V alue)
All fields have this same format.
Here is the code for my stored proc:
I have a stored proc that I use to populate a SSRS report. All the fields get populated except for one field.
My Shipping Location Field does not populate.
When I run the SQL in the database all the fields return data.
In the SSRS report the code for the Shipping location is:
=IIf(Fields!shipping_locat
All fields have this same format.
Here is the code for my stored proc:
create table #ShippedOrdersTable (InvoiceNo nvarchar(30),
BillToCustID int,
PrimaryContactName varchar(100),
customer_code nvarchar(40),
PONumber nvarchar(50),
distributor_PO varchar(20),
Shipping_Charges decimal(10,2),
Handling_Charges decimal(10,2),
Service_Charges decimal(10,2),
Misc_Charges decimal(10,2),
TDN nvarchar(30),
ProductCode nvarchar(30),
[Description] nvarchar(50),
CataloguePrice decimal(10,0),
ContractDiscount decimal(10,2),
InstantDiscount decimal(10,2),
InvoiceAmount decimal(10,2),
Ship_Attention nvarchar(100),
cinv_ship_attention nvarchar(100),
bill_attention nvarchar(100),
cinv_bill_attention nvarchar(100),
shippingaccountno nvarchar(30),
cinv_shippingaccountno nvarchar(30),
CreationDATE datetime,
ProdMonth nvarchar(50),
NumericMonth int,
NumericYear int,
shipping_location varchar(200),
inv_shipping_location varchar(200),
shipping_city nvarchar(100),
cinv_shipping_city nvarchar(100),
shipping_state nvarchar(100),
cinv_shipping_state nvarchar(100),
shipping_zip nvarchar(100),
cinv_shipping_zip nvarchar(100),
shipping_country varchar(50),
cinv_shipping_country varchar(50),
ship_phone nvarchar(30),
cinv_ship_phone nvarchar(30),
billing_location varchar(200),
cinv_billing_location varchar(200),
billing_city nvarchar(100),
cinv_billing_city nvarchar(100),
billing_state nvarchar(100),
cinv_billing_state nvarchar(100),
billing_zip nvarchar(50),
cinv_billing_zip nvarchar(50),
billing_country varchar(50),
cinv_billing_country varchar(50),
bill_phone nvarchar(30),
cinv_bill_phone nvarchar(30),
document_amount decimal(10,2),
shiptonumber varchar(5),
cinv_shiptonumber varchar(5),
invoice_printed_on datetime,
report_category varchar(50),
actualsize varchar(200),
[expiredate] datetime,
tracking_no nvarchar(30),
cinv_tracking_no nvarchar(30),
shipment_method varchar(200),
DistPartNo nvarchar(30), -- 'Dist Part No'
TissueCreationDate varchar(50), -- 'Tissue Creation Date'
TissueReleaseDate datetime -- 'Tissue Release Date'
);
INSERT INTO #ShippedOrdersTable
select distinct M.DOCUMENT_ID as InvoiceNo, M.CUSTOMER_ID as BillToCustID
, C.PrimaryContactName
, q.customer_code
, M.PONumber
, m.distributor_PO
, isnull(M.SHIPPING_CHARGES, 0) as Shipping_Charges
, isnull(M.HANDLING_CHARGES, 0) as Handling_Charges
, isnull(M.SERVICE_CHARGES, 0) as Service_Charges
, isnull(M.MISC_CHARGES, 0) as Misc_Charges
, D.Graft_ID as TDN
, D.ProductCode
, P.TypePrefixDetail as Description
, convert(decimal, isnull(D.LineItemAmount, 0)) as CataloguePrice
, isnull(D.LineItemAmount, 0) - isnull(D.GRAFT_COST, 0) as ContractDiscount
, isnull(D.discount,0) as InstantDiscount
, isnull(D.GRAFT_COST, 0) + isnull(D.discount,0) as InvoiceAmount
, M.Ship_Attention
, m2.ship_attention as cinv_ship_attention
, m.bill_attention
, m2.bill_attention as cinv_bill_attention
, m.shippingaccountno -- Shipping Customer ID??
, m2.shippingaccountno as cinv_shippingaccountno
, M.CreationDATE
,datename(month,dateadd(month, datepart("mm", m.invoice_printed_on) - 1, 0))+ ' ' + convert(nvarchar, datepart("yyyy", m.invoice_printed_on)) as ProdMonth
,Month(m.invoice_printed_on) as NumericMonth
, Year(m.invoice_printed_on) as NumericYear
, m.shipping_location
, m2.shipping_location as cinv_shipping_location
, m.shipping_city
, m2.shipping_city as cinv_shipping_city
, m.shipping_state
, m2.shipping_state as cinv_shipping_state
, m.shipping_zip
, m2.shipping_zip as cinv_shipping_zip
, m.shipping_country
, m2.shipping_country as cinv_shipping_country
, m.ship_phone
, m2.ship_phone as cinv_ship_phone
, m.billing_location
, m2.billing_location as cinv_billing_location
, m.billing_city
, m2.billing_city as cinv_billing_city
, m.billing_state
, m2.billing_state as cinv_billing_state
, m.billing_zip
, m2.billing_zip as cinv_billing_zip
, m.billing_country
, m2.billing_country as cinv_billing_country
, m.bill_phone
, m2.bill_phone as cinv_bill_phone
, m.document_amount
, m.shiptonumber -- Fed Ex Account,etc
, m2.shiptonumber as cinv_shiptonumber
, m.invoice_printed_on
--, f.SortOrder
--, f.fymonth
, rc.report_category
, pr.actualsize
, pr.expiredate
,m.tracking_no
, m2.tracking_no as cinv_tracking_no
,sm.shipment_method
,SDPI.DistPartNo As DistPartNo
,convert(varchar,pr.create_datetime,101) as TissueCreationDate
, min (tissueHistory.modified_datetime) as TissueReleaseDate
from dist_Document_Masters M
inner join Dist_Document_Details D on D.Document_ID = M.Document_ID
left join pr_deposit pr on pr.tissuedepositno = d.graft_id
inner join Dist_Customer C on C.ID = M.Customer_ID
join setup_customer_code q on c.customerid = q.customer_code_id
left join setup_tissue_prefix P on P.TypePrefix = D.ProductCode
left join setup_report_category_102 rc on p.repcat_id_102 = rc.report_category_id
left join dist_document_masters m2 on (d.parent_document_id = m2.document_id)
left join setup_shipment_method sm on (m.mode_of_shipment = sm.shipment_id or m2.mode_of_shipment = sm.shipment_id)
left join ds_donor_info di on (pr.donorid = di.donorid)
Left Join Setup_Distributor_Product_info SDPI on D.ProductCode = ( cast(SDPI.UMTBProductCode as varchar))
--join setup_FiscalYear f on Month(m.invoice_printed_on) = f.FYMonth
Join DIST_TISSUE_HISTORY_ARCHIVED tissueHistory On tissueHistory.TissueDepositNo = pr.tissuedepositno
Join [BTMTransplant_Audit].[dbo].[SetUp_Tissue_Status_WSE] WSETissueStatus ON WSETissueStatus.Tissue_Status_Type = tissueHistory.DepositStatusFlag
where
m.DOCUMENT_TYPE_ID = 'INV'
and (m.invoice_printed_on >= @dStartDate and m.invoice_printed_on < @dEndDate) --or (invoice_printed_on >= @dStartDate1 and invoice_printed_on < @dEndDate1))
and ( M2.Ship_Attention like '%' + isnull(@customer, '') + '%' )--or m.ship_attention is null)
and (m.isconsigned = '0' or m.isconsigned is null)
and C.PrimaryContactName like '%' + isnull(@PriContactName, '') + '%'
and D.ProductCode like '%' + isnull(@mproductcode, '') + '%'
and rc.report_category like '%' + isnull(@category, '') + '%'
--and pr.donorps like '%' + isnull(@tdonor, '') + '%'
and di.ps like '%' + isnull(@tdonor, '') + '%'
and M.PONumber like '%' + isnull(@PONumber, '') + '%'
and tissueHistory.DepositStatusFlag = 'R'
Group By M.DOCUMENT_ID, M.CUSTOMER_ID, C.PrimaryContactName, q.customer_code, M.PONumber, m.distributor_PO
, M.SHIPPING_CHARGES , M.HANDLING_CHARGES,M.SERVICE_CHARGES , M.MISC_CHARGES, D.Graft_ID, D.ProductCode, P.TypePrefixDetail , D.LineItemAmount
, D.LineItemAmount,D.GRAFT_COST,D.discount, D.GRAFT_COST,D.discount,M.Ship_Attention, m2.ship_attention, m.bill_attention
, m2.bill_attention, m.shippingaccountno -- Shipping Customer ID??
, m2.shippingaccountno, M.CreationDATE,m.invoice_printed_on, m.shipping_location, m2.shipping_location, m.shipping_city
, m2.shipping_city, m.shipping_state, m2.shipping_state, m.shipping_zip, m2.shipping_zip, m.shipping_country, m2.shipping_country
, m.ship_phone, m.billing_location, m.billing_city, m.billing_state, m.billing_zip, m.billing_country
, m2.ship_phone, m2.billing_location, m2.billing_city, m2.billing_state, m2.billing_zip, m2.billing_country
--, m2.billing_country
, m.bill_phone, m.document_amount, m.shiptonumber, m.invoice_printed_on, m2.bill_phone, m2.document_amount, m2.shiptonumber
, m2.invoice_printed_on, rc.report_category, pr.actualsize, pr.expiredate, m.tracking_no, m2.tracking_no
,sm.shipment_method,SDPI.DistPartNo,pr.create_datetime,WSETissueStatus.Tissue_Status_Name
UNION
select distinct M.DOCUMENT_ID as InvoiceNo, M.CUSTOMER_ID as BillToCustID
, C.PrimaryContactName
, q.customer_code
, M.PONumber
, m.distributor_PO
, isnull(M.SHIPPING_CHARGES, 0) as Shipping_Charges
, isnull(M.HANDLING_CHARGES, 0) as Handling_Charges
, isnull(M.SERVICE_CHARGES, 0) as Service_Charges
, isnull(M.MISC_CHARGES, 0) as Misc_Charges
, D.Graft_ID as TDN
, D.ProductCode
, P.TypePrefixDetail as Description
, convert(decimal, isnull(D.LineItemAmount, 0)) as CataloguePrice
, isnull(D.LineItemAmount, 0) - isnull(D.GRAFT_COST, 0) as ContractDiscount
, isnull(D.discount,0) as InstantDiscount
, isnull(D.GRAFT_COST, 0) + isnull(D.discount,0) as InvoiceAmount
, M.Ship_Attention
, m2.ship_attention as cinv_ship_attention
, m.bill_attention
, m2.bill_attention as cinv_bill_attention
, m.shippingaccountno -- Shipping Customer ID??
, m2.shippingaccountno as cinv_shippingaccountno
, M.CreationDATE
,datename(month,dateadd(month, datepart("mm", m.invoice_printed_on) - 1, 0))+ ' ' + convert(nvarchar, datepart("yyyy", m.invoice_printed_on)) as ProdMonth
,Month(m.invoice_printed_on) as NumericMonth
, Year(m.invoice_printed_on) as NumericYear
, m.shipping_location
, m2.shipping_location as cinv_shipping_location
, m.shipping_city
, m2.shipping_city as cinv_shipping_city
, m.shipping_state
, m2.shipping_state as cinv_shipping_state
, m.shipping_zip
, m2.shipping_zip as cinv_shipping_zip
, m.shipping_country
, m2.shipping_country as cinv_shipping_country
, m.ship_phone
, m2.ship_phone as cinv_ship_phone
, m.billing_location
, m2.billing_location as cinv_billing_location
, m.billing_city
, m2.billing_city as cinv_billing_city
, m.billing_state
, m2.billing_state as cinv_billing_state
, m.billing_zip
, m2.billing_zip as cinv_billing_zip
, m.billing_country
, m2.billing_country as cinv_billing_country
, m.bill_phone
, m2.bill_phone as cinv_bill_phone
, m.document_amount
, m.shiptonumber -- Fed Ex Account,etc
, m2.shiptonumber as cinv_shiptonumber
, m.invoice_printed_on
--, f.SortOrder
--, f.fymonth
, rc.report_category
, pr.actualsize
, pr.expiredate
,m.tracking_no
, m2.tracking_no as cinv_tracking_no
,sm.shipment_method
,SDPI.DistPartNo As DistPartNo
,convert(varchar,pr.create_datetime,101) as TissueCreationDate
, min (tissueHistory.modified_datetime) as TissueReleaseDate
from dist_Document_Masters M
inner join Dist_Document_Details D on D.Document_ID = M.Document_ID
left join pr_deposit pr on pr.tissuedepositno = d.graft_id
inner join Dist_Customer C on C.ID = M.Customer_ID
join setup_customer_code q on c.customerid = q.customer_code_id
left join setup_tissue_prefix P on P.TypePrefix = D.ProductCode
left join setup_report_category_102 rc on p.repcat_id_102 = rc.report_category_id
left join dist_document_masters m2 on (d.parent_document_id = m2.document_id)
left join setup_shipment_method sm on (m.mode_of_shipment = sm.shipment_id or m2.mode_of_shipment = sm.shipment_id)
left join ds_donor_info di on (pr.donorid = di.donorid)
Left Join Setup_Distributor_Product_info SDPI on D.ProductCode = ( cast(SDPI.UMTBProductCode as varchar))
--join setup_FiscalYear f on Month(m.invoice_printed_on) = f.FYMonth
Join DIST_TISSUE_HISTORY tissueHistory On tissueHistory.TissueDepositNo = pr.tissuedepositno
Join [BTMTransplant_Audit].[dbo].[SetUp_Tissue_Status_WSE] WSETissueStatus ON WSETissueStatus.Tissue_Status_Type = tissueHistory.DepositStatusFlag
where
m.DOCUMENT_TYPE_ID = 'INV'
and (m.invoice_printed_on >= @dStartDate and m.invoice_printed_on < @dEndDate) --or (invoice_printed_on >= @dStartDate1 and invoice_printed_on < @dEndDate1))
and ( M2.Ship_Attention like '%' + isnull(@customer, '') + '%' )--or m.ship_attention is null)
and (m.isconsigned = '0' or m.isconsigned is null)
and C.PrimaryContactName like '%' + isnull(@PriContactName, '') + '%'
and D.ProductCode like '%' + isnull(@mproductcode, '') + '%'
and rc.report_category like '%' + isnull(@category, '') + '%'
--and pr.donorps like '%' + isnull(@tdonor, '') + '%'
and di.ps like '%' + isnull(@tdonor, '') + '%'
and M.PONumber like '%' + isnull(@PONumber, '') + '%'
and tissueHistory.DepositStatusFlag = 'R'
Group By M.DOCUMENT_ID, M.CUSTOMER_ID, C.PrimaryContactName, q.customer_code, M.PONumber, m.distributor_PO
, M.SHIPPING_CHARGES , M.HANDLING_CHARGES,M.SERVICE_CHARGES , M.MISC_CHARGES, D.Graft_ID, D.ProductCode, P.TypePrefixDetail , D.LineItemAmount
, D.LineItemAmount,D.GRAFT_COST,D.discount, D.GRAFT_COST,D.discount,M.Ship_Attention, m2.ship_attention, m.bill_attention
, m2.bill_attention, m.shippingaccountno -- Shipping Customer ID??
, m2.shippingaccountno, M.CreationDATE,m.invoice_printed_on, m.shipping_location, m2.shipping_location, m.shipping_city
, m2.shipping_city, m.shipping_state, m2.shipping_state, m.shipping_zip, m2.shipping_zip, m.shipping_country, m2.shipping_country
, m.ship_phone, m.billing_location, m.billing_city, m.billing_state, m.billing_zip, m.billing_country
, m2.ship_phone, m2.billing_location, m2.billing_city, m2.billing_state, m2.billing_zip, m2.billing_country
--, m2.billing_country
, m.bill_phone, m.document_amount, m.shiptonumber, m.invoice_printed_on, m2.bill_phone, m2.document_amount, m2.shiptonumber
, m2.invoice_printed_on, rc.report_category, pr.actualsize, pr.expiredate, m.tracking_no, m2.tracking_no
,sm.shipment_method,SDPI.DistPartNo,pr.create_datetime,WSETissueStatus.Tissue_Status_Name
Order by C.PrimaryContactName,D.ProductCode,TDN
SELECT InvoiceNo,
BillToCustID,
PrimaryContactName,
customer_code,
PONumber,
distributor_PO,
Shipping_Charges,
Handling_Charges,
Service_Charges ,
Misc_Charges,
TDN ,
ProductCode,
[Description],
CataloguePrice,
ContractDiscount ,
InstantDiscount,
InvoiceAmount,
Ship_Attention,
cinv_ship_attention,
bill_attention,
cinv_bill_attention,
shippingaccountno,
cinv_shippingaccountno,
CreationDATE,
ProdMonth ,
NumericMonth,
NumericYear,
shipping_location,
inv_shipping_location,
shipping_city,
cinv_shipping_city ,
shipping_state,
cinv_shipping_state,
shipping_zip,
cinv_shipping_zip,
shipping_country,
cinv_shipping_country,
ship_phone,
cinv_ship_phone,
billing_location,
cinv_billing_location,
billing_city,
cinv_billing_city,
billing_state,
cinv_billing_state,
billing_zip,
cinv_billing_zip,
billing_country,
cinv_billing_country,
bill_phone,
cinv_bill_phone,
document_amount,
shiptonumber,
cinv_shiptonumber,
invoice_printed_on,
report_category ,
actualsize,
[expiredate],
tracking_no,
cinv_tracking_no,
shipment_method,
DistPartNo AS 'Dist Part No',
TissueCreationDate AS 'Tissue Creation Date',
MIN(TissueReleaseDate) AS 'Tissue Release Date'
FROM #ShippedOrdersTable
GROUP BY InvoiceNo,
BillToCustID,
PrimaryContactName,
customer_code,
PONumber,
distributor_PO,
Shipping_Charges,
Handling_Charges,
Service_Charges ,
Misc_Charges,
TDN ,
ProductCode,
[Description],
CataloguePrice,
ContractDiscount ,
InstantDiscount,
InvoiceAmount,
Ship_Attention,
cinv_ship_attention,
bill_attention,
cinv_bill_attention,
shippingaccountno,
cinv_shippingaccountno,
CreationDATE,
ProdMonth ,
NumericMonth,
NumericYear,
shipping_location,
inv_shipping_location,
shipping_city,
cinv_shipping_city ,
shipping_state,
cinv_shipping_state,
shipping_zip,
cinv_shipping_zip,
shipping_country,
cinv_shipping_country,
ship_phone,
cinv_ship_phone,
billing_location,
cinv_billing_location,
billing_city,
cinv_billing_city,
billing_state,
cinv_billing_state,
billing_zip,
cinv_billing_zip,
billing_country,
cinv_billing_country,
bill_phone,
cinv_bill_phone,
document_amount,
shiptonumber,
cinv_shiptonumber,
invoice_printed_on,
report_category ,
actualsize,
[expiredate],
tracking_no,
cinv_tracking_no,
shipment_method,
DistPartNo,
TissueCreationDate
DROP TABLE #ShippedOrdersTable
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
HI Mike - Is this where the change should be made I made a note at the identified field..
Chris - I did not try ISEmpty because the database was returning data. The report has many fields in it with the same format "=IIf(Len(Fields!shipping_ location.V alue) <= 0, Fields!cinv_shipping_locat ion.Value, Fields!shipping_location.V alue)"
to display data. Do you still recommend I try ISEmpty?
Chris - I did not try ISEmpty because the database was returning data. The report has many fields in it with the same format "=IIf(Len(Fields!shipping_
to display data. Do you still recommend I try ISEmpty?
[ BillToCustID,
PrimaryContactName,
customer_code,
PONumber,
distributor_PO,
Shipping_Charges,
Handling_Charges,
Service_Charges ,
Misc_Charges,
TDN ,
ProductCode,
[Description],
CataloguePrice,
ContractDiscount ,
InstantDiscount,
InvoiceAmount,
Ship_Attention,
cinv_ship_attention,
bill_attention,
cinv_bill_attention,
shippingaccountno,
cinv_shippingaccountno,
CreationDATE,
ProdMonth ,
NumericMonth,
NumericYear,
shipping_location,
inv_shipping_location,
shipping_city,
cinv_shipping_city ,
shipping_state,
cinv_shipping_state,
shipping_zip,
cinv_shipping_zip,
shipping_country,
cinv_shipping_country,
ship_phone,
cinv_ship_phone,
billing_location,
cinv_billing_location,
billing_city,
cinv_billing_city,
billing_state,
cinv_billing_state,
billing_zip,
cinv_billing_zip,
billing_country,
cinv_billing_country,
bill_phone,
cinv_bill_phone,
document_amount,
shiptonumber,
cinv_shiptonumber,
invoice_printed_on,
report_category ,
actualsize,
[expiredate],
tracking_no,
cinv_tracking_no,
shipment_method,
DistPartNo AS 'Dist Part No',
TissueCreationDate AS 'Tissue Creation Date',
MIN(TissueReleaseDate) AS 'Tissue Release Date'
FROM #ShippedOrdersTable
GROUP BY InvoiceNo,
BillToCustID,
PrimaryContactName,
customer_code,
PONumber,
distributor_PO,
Shipping_Charges,
Handling_Charges,
Service_Charges ,
Misc_Charges,
TDN ,
ProductCode,
[Description],
CataloguePrice,
ContractDiscount ,
InstantDiscount,
InvoiceAmount,
Ship_Attention,
cinv_ship_attention,
bill_attention,
cinv_bill_attention,
shippingaccountno,
cinv_shippingaccountno,
CreationDATE,
ProdMonth ,
NumericMonth,
NumericYear,
shipping_location,
inv_shipping_location, <-------------------- Is this what should be changed?
shipping_city,
cinv_shipping_city ,
shipping_state,
cinv_shipping_state,
shipping_zip,
cinv_shipping_zip,
shipping_country,
cinv_shipping_country,
ship_phone,
cinv_ship_phone,
billing_location,
cinv_billing_location,
billing_city,
cinv_billing_city,
billing_state,
cinv_billing_state,
billing_zip,
cinv_billing_zip,
billing_country,
cinv_billing_country,
bill_phone,
cinv_bill_phone,
document_amount,
shiptonumber,
cinv_shiptonumber,
invoice_printed_on,
report_category ,
actualsize,
[expiredate],
tracking_no,
cinv_tracking_no,
shipment_method,
DistPartNo,
TissueCreationDate
DROP TABLE #ShippedOrdersTable
I would definitely NOT recommend that you use IsEmpty(), as it's not a function. It may have been something custom I wrote a while back and got mixed up (sorry about that!).
If your result set is returning empty string, the LEN() function should do the trick.
If your result set is returning empty string, the LEN() function should do the trick.
=IIf(ISEMPTY(Fields!shippi
I'd also check Fields!shipping_location.v
=IIf(Len(Fields!shipping_l