February 29, 2008 at 3:36 pm
I need some help building a report. I think this is got to be something very simple but I am not sure what I am missing
1. I created a stored procedure.
2. I created my dataset :
command type: storedProcedure
Query string: The stored procedure name
3. I refresh the report dataset
however the dataset does not populate with any fields.
What do I have to do for the fields to come up??
I appreciate if any one could give me a had with this
Sandra
February 29, 2008 at 5:45 pm
Could you show the SPROC? Is the IDE showing any errors when you execute the procedure?
Also
In the Windows where you put the SPROC Name did you include the schema name?
i.e. dbo.MyProcedure instead of just MyProcedure?
March 3, 2008 at 8:52 am
I included the name of the schema on the name.
when I click on refresh it brings a box with the stored procedure parameters, so it is reading the procedure, but after a enter the parameters no results actually populate on the grid, and the dataset fields do not populate either.
No error messages.
here is the stored proc:
BEGIN
IF object_id('tempdb..#tempcode') IS NOT NULL
BEGIN
DROP TABLE #tempcode
END
create table #tempcode (ChargeAllowanceCode nchar(4))
IF @IncludeFuelcharge_405=1
BEGIN
insert into #tempcode select distinct ChargeAllowanceCode from shipping.charge where ChargeAllowanceCode <>'400'
END
IF @IncludeFuelcharge_405=0
BEGIN
insert into #tempcode select distinct ChargeAllowanceCode from shipping.charge where ChargeAllowanceCode not in ('400','405')
END
END
Begin
declare @BoundInd int
--if (@StartDT = @EndDT ) -- Modified 2007-06-22 - SPH
begin
set @EndDT = convert(datetime, convert(varchar, @EndDT, 101) + ' 23:59:59')
end
if ( @seeInbound = 1 ) and (@seeOutBound = 1 )
set @BoundInd = 2 -- both
if ( @seeInbound = 1 ) and (@seeOutBound = 0 )
set @BoundInd = 0 -- only inbound
if ( @seeInbound = 0 ) and (@seeOutBound = 1 )
set @BoundInd = 1 -- only outbound
if ( @seeInbound > 0 ) or (@seeOutBound > 0 )
begin
select ih.isaControlNumber Batch,
ih.invoiceCode InvoiceNo,
sp.AirWeighBillCode AirbillNo,
Case ih.OutBoundInd
when 1
then sp.ShipToName
else sp.ShipFromName
end [Name],
Case ih.OutBoundInd
when 1
then sp.ShipToCity
else sp.ShipFromCity
end [City],
Case ih.OutBoundInd
when 1
then sp.ShipToState
else sp.ShipFromState
end [State],
Case ih.OutBoundInd
when 1
then sp.ShipToPostalCode
else sp.ShipFromPostalCode
end [ZIP],
sr.Destination,
sp.PickUp_DeliveryDate PickupDateTime,
c.ChargeAmount OtherCharges1,
c.ChargeAllowanceDesc OtherCharge1Description,
isnull(c1.ChargeAmount,0) OtherCharges2,
isnull(c1.ChargeAllowanceDesc,'') OtherCharge2Description,
isnull(Assc.otherCharges,0) Total_OtherCharges,
ih.ISADate BillingDate, -- Added 6/18/2007 - SPH
ih.InvoiceDate InvoiceDate,
c.ChargeAllowanceCode
from Shipping.InvoiceHeader ih with (nolock)
join Shipping.ShipmentParty sp with (nolock)
on (ih.ID = sp.InvoiceHeaderID)
join Shipping.LineItem li with (nolock)
on (ih.ID = li.InvoiceHeaderID
and sp.AirWeighBillCode = li.AirWeighBillCode)
join Shipping.ShipmentRouting sr with (nolock)
on (ih.ID = sr.InvoiceHeaderID
and sp.AirWeighBillCode = sr.AirWeighBillCode)
join shipping.charge c
on (ih.ID = c.InvoiceHeaderID
and sp.AirWeighBillCode = c.AirWeighBillCode
and c.ChargeAllowanceCode <> '400'
and c.LadingLineItemNumber = 2)
left join shipping.charge c1
on (ih.ID = c1.InvoiceHeaderID
and sp.AirWeighBillCode = c1.AirWeighBillCode
and c1.ChargeAllowanceCode <> '400'
and c1.LadingLineItemNumber = 3)
left join (select InvoiceHeaderID,invoiceCode, AirWeighBillCode,
sum(ChargeAmount) otherCharges
from Shipping.Charge with (nolock)
where ChargeAllowanceCode <> '400'
group by InvoiceHeaderID,invoiceCode, AirWeighBillCode ) AssC
on ( ih.ID = AssC.InvoiceHeaderID
and sp.AirWeighBillCode = AssC.AirWeighBillCode )
where ih.ISADate between @StartDT and @EndDT
--where ih.InvoiceDate between @StartDT and @EndDT
and (ih.OutBoundInd = (case @BoundInd
when 1 then 1
when 0 then 0
when 2 then 1
end)
or ih.OutBoundInd = (case @BoundInd -- Since we have to test for either AND both
when 1 then null -- this construct will allow for the both
when 0 then null -- case.
when 2 then 0
end))
and c.ChargeAllowanceCode in (select ChargeAllowanceCode from #tempcode)---- Added code to allow users to exclude fuel charges
end
END
March 3, 2008 at 9:25 am
The problem was the temp table. I changed the stored proc to create a table instead, anf the everything worked fine.
Not sure wht reporting services do not work well with temp tables, but that solved my issue
Sandra
March 3, 2008 at 10:22 am
That's quite a SPROC. Have you executed the SPROC thru Mgmt Studio using the same paramaters you are passing from RSS and gotten data back?
March 4, 2008 at 12:58 am
Check out the following article:
http://www.sqlservercentral.com/articles/Integration+Services/61824
It describes the same problem but then occuring in SSIS.
Peter
March 4, 2008 at 2:27 am
Hiiiiiiiii........
I have not read the total story but got the point
For getting the fields populated in dataset u have to select the required columns using select query after the end of the stored proc
i.e select columns from temptable
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply