Creating a Stored procedure dataset for a Reporting Services Repor

I need help with creating a Stored procedure dataset for a Reporting
Services Report:-
I am creating a dataset using a stored procedure that has parameters and I
am unable to see/use the Fields to create a report.
Here is an example of the stored procedure
CREATE procedure rptDataLoadOffice2(@.MISDataLoadOfficeID int)
AS
declare @.SQLString VARCHAR(400)
Set @.SQLString = 'Select MISDataLoadOfficeID, IsDeleted, InsertDateTime,
LastUpdateDateTime, ShortName, Office
From Dim_DataLoadOffice
Where MISDataLoadOfficeID = ' + cast(@.MISDataLoadOfficeID as varchar(10)) +
' Order By Office'
--PRINT (@.SQLString)
EXEC (@.SQLString)
The stored procedure runs fine, but does not create the field output needed
for the report, I should be able to see the fields for each dataset in the
Field window.
I notice this only happen when there is a parameter involve. I have tried
the above sp with no parameter and it works fine. I have the above sp with a
varchar parameter and I am having the same problem.
Try creating any sp in the above format and try to see if it will display
the dataset fields.
I have enclosed the actual stored procedure I am trying to use below, which
is having the same problem. I need to write the stored procedure in this
format because, I need the UnderwritingYear parameter to determine which
table to select from (i.e Fact_InwardTransaction_USD_' + @.UnderwritingYear ).
The are 30 tables involve (ie from Fact_InwardTransaction_USD_1970 to
Fact_InwardTransaction_USD_2009)
---
CREATE procedure rptInwardTransaction_USD(
@.MISDataLoadOfficeID int,
@.TeamCode varchar(10),
@.UnderwriterID varchar(100),
@.UnderwritingYear varchar(6),
@.GlobalReservingGroupCode varchar(10),
@.ExchangeRateTypeLabel varchar(50))
AS
declare @.SQLString VARCHAR(4000)
set @.SQLString = 'SELECT
Dim_DataLoadOffice.Office, Dim_Team.TeamName AS Team,
Dim_Underwriter.UnderwriterFullName AS Underwriter,
Dim_UnderwritingYear.UnderwritingYear AS UnderwritingYear,
Dim_GlobalReservingGroup.GlobalReservingGroupCode AS GRG_Code,
Dim_GlobalReservingGroup.GlobalReservingGroupLongName AS GRG,
Dim_ExchangeRateType.ExchangeRateTypeLabel AS ExchangeRate,
Dim_BookingScenario.BookingOfficeName AS BookingOffice,
Broker.DescriptiveName AS Broker,
Cedant.DescriptiveName AS Cedant,
MGA.DescriptiveName AS MGA,
Dim_Claim.ClaimEventDsc AS ClaimEvent,
Dim_Claim.ClaimHeaderClaimName AS ClaimHeader,
Dim_Claim.ClaimDetailClaimName AS ClaimDetail,
Dim_InwardSubContract.ContractReference AS ContractLayer,
Dim_InwardSubContract.ProgrammeTitle AS ContractProgramme,
Dim_InceptionDate.InceptionDate AS InceptionDate,
Dim_TransactionDate.TransactionDate AS TransactionDate,
Dim_DateOfLoss.DateOfLoss AS DateOfLoss,
Dim_PolicyBasis.PolicyBasisName AS PolicyBasis,
Dim_MethodOfAcceptance.MethodOfAcceptanceName AS MethodOfAcceptance,
fact.BookedPaidClaimSettCcy AS PaidLoss,
fact.BookedCaseReserveOrigCcy AS OutstandingLoss,
fact.BookedIncurredLoss AS IncurredLoss
FROM Fact_InwardTransaction_USD_' + @.UnderwritingYear + ' fact
INNER JOIN Dim_DataLoadOffice ON fact.MISDataLoadOfficeID = Dim_DataLoadOffice.MISDataLoadOfficeID
INNER JOIN Dim_Team ON fact.MISTeamID = Dim_Team.MISTeamID AND
fact.MISDataLoadOfficeID = Dim_Team.MISDataLoadOfficeID
INNER JOIN Dim_Underwriter ON fact.MISUnderwriterID = Dim_Underwriter.MISUnderwriterID
INNER JOIN Dim_UnderwritingYear ON fact.MISUnderwritingYearID = Dim_UnderwritingYear.MISUnderwritingYearID
INNER JOIN Dim_ExchangeRateType ON fact.MISExchangeRateTypeID = Dim_ExchangeRateType.MISExchangeRateTypeID
INNER JOIN Dim_InwardSubContract ON fact.MISInwardSubContractID = Dim_InwardSubContract.MISInwardSubContractID
INNER JOIN Dim_BookingScenario ON fact.MISBookingScenarioID = Dim_BookingScenario.MISBookingScenarioID
INNER JOIN Dim_Organisation Broker ON fact.MISBrokerID = Broker.MISOrganisationID
INNER JOIN Dim_GlobalReservingGroup ON fact.MISGlobalReservingGroupID = Dim_GlobalReservingGroup.MISGlobalReservingGroupID
INNER JOIN Dim_Organisation Cedant ON fact.MISCedantID = Cedant.MISOrganisationID
INNER JOIN Dim_Organisation MGA ON fact.MISMGAID = MGA.MISOrganisationID
INNER JOIN Dim_Claim ON fact.MISClaimID = Dim_Claim.MISClaimID AND
fact.MISDataLoadOfficeID = Dim_Claim.MISDataLoadOfficeID
INNER JOIN Dim_InceptionDate ON fact.MISInceptionDateID = Dim_InceptionDate.MISInceptionDateID
INNER JOIN Dim_TransactionDate ON fact.MISTransactionDateID = Dim_TransactionDate.MISTransactionDateID
INNER JOIN Dim_DateOfLoss ON fact.MISDateOfLossID = Dim_DateOfLoss.MISDateOfLossID
INNER JOIN Dim_PolicyBasis ON fact.MISPolicyBasisID = Dim_PolicyBasis.MISPolicyBasisID
INNER JOIN Dim_MethodOfAcceptance ON fact.MISMethodOfAcceptanceID = Dim_MethodOfAcceptance.MISMethodOfAcceptanceID
WHERE (Dim_DataLoadOffice.MISDataLoadOfficeID = ' +
cast(@.MISDataLoadOfficeID as varchar(10)) + ')
AND (Dim_Underwriter.UnderwriterFullName LIKE ''' + @.UnderwriterID + ''')
AND (Dim_UnderwritingYear.UnderwritingYear LIKE ''' + @.UnderwritingYear +
''')
AND (Dim_GlobalReservingGroup.GlobalReservingGroupCode LIKE ''' +
@.GlobalReservingGroupCode + ''')
AND (Dim_ExchangeRateType.ExchangeRateTypeLabel LIKE ''' +
@.ExchangeRateTypeLabel + ''')
AND (Dim_Team.TeamCode LIKE ''' + @.TeamCode + ''')
ORDER BY Dim_Team.TeamName, Dim_Underwriter.UnderwriterFullName'
--print @.SQLString
EXEC (@.SQLString)
GO
----
Is there a way to link the fields from a successful dataset run to a Report,
if they are not showing in the Field window. Or is there another way I can
write the stored procedure without
using UNION ALL to combine the table together. This will take a long time
when searching for records for a particular year.Have you tried clicking on the refresh fields button (it looks like the
refresh button for IE).
Bruce L-C
"Michael" <Michael@.discussions.microsoft.com> wrote in message
news:DD4B3A3B-DC83-455C-A2EE-4C7F7FFC48C2@.microsoft.com...
> I need help with creating a Stored procedure dataset for a Reporting
> Services Report:-
> I am creating a dataset using a stored procedure that has parameters and I
> am unable to see/use the Fields to create a report.
> Here is an example of the stored procedure
> CREATE procedure rptDataLoadOffice2(@.MISDataLoadOfficeID int)
> AS
> declare @.SQLString VARCHAR(400)
> Set @.SQLString = 'Select MISDataLoadOfficeID, IsDeleted, InsertDateTime,
> LastUpdateDateTime, ShortName, Office
> From Dim_DataLoadOffice
> Where MISDataLoadOfficeID = ' + cast(@.MISDataLoadOfficeID as varchar(10))
+
> ' Order By Office'
> --PRINT (@.SQLString)
> EXEC (@.SQLString)
> The stored procedure runs fine, but does not create the field output
needed
> for the report, I should be able to see the fields for each dataset in the
> Field window.
> I notice this only happen when there is a parameter involve. I have tried
> the above sp with no parameter and it works fine. I have the above sp with
a
> varchar parameter and I am having the same problem.
> Try creating any sp in the above format and try to see if it will display
> the dataset fields.
>
> I have enclosed the actual stored procedure I am trying to use below,
which
> is having the same problem. I need to write the stored procedure in this
> format because, I need the UnderwritingYear parameter to determine which
> table to select from (i.e Fact_InwardTransaction_USD_' +
@.UnderwritingYear ).
> The are 30 tables involve (ie from Fact_InwardTransaction_USD_1970 to
> Fact_InwardTransaction_USD_2009)
> ---
> CREATE procedure rptInwardTransaction_USD(
> @.MISDataLoadOfficeID int,
> @.TeamCode varchar(10),
> @.UnderwriterID varchar(100),
> @.UnderwritingYear varchar(6),
> @.GlobalReservingGroupCode varchar(10),
> @.ExchangeRateTypeLabel varchar(50))
> AS
>
> declare @.SQLString VARCHAR(4000)
> set @.SQLString = 'SELECT
> Dim_DataLoadOffice.Office, Dim_Team.TeamName AS Team,
> Dim_Underwriter.UnderwriterFullName AS Underwriter,
> Dim_UnderwritingYear.UnderwritingYear AS UnderwritingYear,
> Dim_GlobalReservingGroup.GlobalReservingGroupCode AS GRG_Code,
> Dim_GlobalReservingGroup.GlobalReservingGroupLongName AS GRG,
> Dim_ExchangeRateType.ExchangeRateTypeLabel AS ExchangeRate,
> Dim_BookingScenario.BookingOfficeName AS BookingOffice,
> Broker.DescriptiveName AS Broker,
> Cedant.DescriptiveName AS Cedant,
> MGA.DescriptiveName AS MGA,
> Dim_Claim.ClaimEventDsc AS ClaimEvent,
> Dim_Claim.ClaimHeaderClaimName AS ClaimHeader,
> Dim_Claim.ClaimDetailClaimName AS ClaimDetail,
> Dim_InwardSubContract.ContractReference AS ContractLayer,
> Dim_InwardSubContract.ProgrammeTitle AS ContractProgramme,
> Dim_InceptionDate.InceptionDate AS InceptionDate,
> Dim_TransactionDate.TransactionDate AS TransactionDate,
> Dim_DateOfLoss.DateOfLoss AS DateOfLoss,
> Dim_PolicyBasis.PolicyBasisName AS PolicyBasis,
> Dim_MethodOfAcceptance.MethodOfAcceptanceName AS MethodOfAcceptance,
> fact.BookedPaidClaimSettCcy AS PaidLoss,
> fact.BookedCaseReserveOrigCcy AS OutstandingLoss,
> fact.BookedIncurredLoss AS IncurredLoss
> FROM Fact_InwardTransaction_USD_' + @.UnderwritingYear + ' fact
> INNER JOIN Dim_DataLoadOffice ON fact.MISDataLoadOfficeID => Dim_DataLoadOffice.MISDataLoadOfficeID
> INNER JOIN Dim_Team ON fact.MISTeamID = Dim_Team.MISTeamID AND
> fact.MISDataLoadOfficeID = Dim_Team.MISDataLoadOfficeID
> INNER JOIN Dim_Underwriter ON fact.MISUnderwriterID => Dim_Underwriter.MISUnderwriterID
> INNER JOIN Dim_UnderwritingYear ON fact.MISUnderwritingYearID => Dim_UnderwritingYear.MISUnderwritingYearID
> INNER JOIN Dim_ExchangeRateType ON fact.MISExchangeRateTypeID => Dim_ExchangeRateType.MISExchangeRateTypeID
> INNER JOIN Dim_InwardSubContract ON fact.MISInwardSubContractID => Dim_InwardSubContract.MISInwardSubContractID
> INNER JOIN Dim_BookingScenario ON fact.MISBookingScenarioID => Dim_BookingScenario.MISBookingScenarioID
> INNER JOIN Dim_Organisation Broker ON fact.MISBrokerID => Broker.MISOrganisationID
> INNER JOIN Dim_GlobalReservingGroup ON fact.MISGlobalReservingGroupID => Dim_GlobalReservingGroup.MISGlobalReservingGroupID
> INNER JOIN Dim_Organisation Cedant ON fact.MISCedantID => Cedant.MISOrganisationID
> INNER JOIN Dim_Organisation MGA ON fact.MISMGAID = MGA.MISOrganisationID
> INNER JOIN Dim_Claim ON fact.MISClaimID = Dim_Claim.MISClaimID AND
> fact.MISDataLoadOfficeID = Dim_Claim.MISDataLoadOfficeID
> INNER JOIN Dim_InceptionDate ON fact.MISInceptionDateID => Dim_InceptionDate.MISInceptionDateID
> INNER JOIN Dim_TransactionDate ON fact.MISTransactionDateID => Dim_TransactionDate.MISTransactionDateID
> INNER JOIN Dim_DateOfLoss ON fact.MISDateOfLossID => Dim_DateOfLoss.MISDateOfLossID
> INNER JOIN Dim_PolicyBasis ON fact.MISPolicyBasisID => Dim_PolicyBasis.MISPolicyBasisID
> INNER JOIN Dim_MethodOfAcceptance ON fact.MISMethodOfAcceptanceID => Dim_MethodOfAcceptance.MISMethodOfAcceptanceID
> WHERE (Dim_DataLoadOffice.MISDataLoadOfficeID = ' +
> cast(@.MISDataLoadOfficeID as varchar(10)) + ')
> AND (Dim_Underwriter.UnderwriterFullName LIKE ''' + @.UnderwriterID + ''')
> AND (Dim_UnderwritingYear.UnderwritingYear LIKE ''' + @.UnderwritingYear +
> ''')
> AND (Dim_GlobalReservingGroup.GlobalReservingGroupCode LIKE ''' +
> @.GlobalReservingGroupCode + ''')
> AND (Dim_ExchangeRateType.ExchangeRateTypeLabel LIKE ''' +
> @.ExchangeRateTypeLabel + ''')
> AND (Dim_Team.TeamCode LIKE ''' + @.TeamCode + ''')
> ORDER BY Dim_Team.TeamName, Dim_Underwriter.UnderwriterFullName'
> --print @.SQLString
> EXEC (@.SQLString)
> GO
> ----
> Is there a way to link the fields from a successful dataset run to a
Report,
> if they are not showing in the Field window. Or is there another way I can
> write the stored procedure without
> using UNION ALL to combine the table together. This will take a long time
> when searching for records for a particular year.|||In reporting services, data tab there is a definitely the button to refresh
the fields. I guarantee you (I use it all the time). It is the third button
over from the combobox with the dataset name. Hover the mouse over each
button and get the tooltip to show up.
Bruce L-C
"Michael" <Michael@.discussions.microsoft.com> wrote in message
news:4E17AA33-F20B-4358-929F-161243C67294@.microsoft.com...
> I am creating a Stored procedure dataset for a Reporting Services Report
> using Microsoft Visual Studio .Net 2003, There is no refresh button there.
>
> "Bruce Loehle-Conger" wrote:
> > Have you tried clicking on the refresh fields button (it looks like the
> > refresh button for IE).
> >
> > Bruce L-C
> >
> > "Michael" <Michael@.discussions.microsoft.com> wrote in message
> > news:DD4B3A3B-DC83-455C-A2EE-4C7F7FFC48C2@.microsoft.com...
> > > I need help with creating a Stored procedure dataset for a Reporting
> > > Services Report:-
> > >
> > > I am creating a dataset using a stored procedure that has parameters
and I
> > > am unable to see/use the Fields to create a report.
> > >
> > > Here is an example of the stored procedure
> > >
> > > CREATE procedure rptDataLoadOffice2(@.MISDataLoadOfficeID int)
> > > AS
> > > declare @.SQLString VARCHAR(400)
> > >
> > > Set @.SQLString = 'Select MISDataLoadOfficeID, IsDeleted,
InsertDateTime,
> > > LastUpdateDateTime, ShortName, Office
> > > From Dim_DataLoadOffice
> > > Where MISDataLoadOfficeID = ' + cast(@.MISDataLoadOfficeID as
varchar(10))
> > +
> > > ' Order By Office'
> > >
> > > --PRINT (@.SQLString)
> > > EXEC (@.SQLString)
> > >
> > > The stored procedure runs fine, but does not create the field output
> > needed
> > > for the report, I should be able to see the fields for each dataset in
the
> > > Field window.
> > > I notice this only happen when there is a parameter involve. I have
tried
> > > the above sp with no parameter and it works fine. I have the above sp
with
> > a
> > > varchar parameter and I am having the same problem.
> > > Try creating any sp in the above format and try to see if it will
display
> > > the dataset fields.
> > >
> > >
> > > I have enclosed the actual stored procedure I am trying to use below,
> > which
> > > is having the same problem. I need to write the stored procedure in
this
> > > format because, I need the UnderwritingYear parameter to determine
which
> > > table to select from (i.e Fact_InwardTransaction_USD_' +
> > @.UnderwritingYear ).
> > > The are 30 tables involve (ie from Fact_InwardTransaction_USD_1970 to
> > > Fact_InwardTransaction_USD_2009)
> > > ---
> > > CREATE procedure rptInwardTransaction_USD(
> > > @.MISDataLoadOfficeID int,
> > > @.TeamCode varchar(10),
> > > @.UnderwriterID varchar(100),
> > > @.UnderwritingYear varchar(6),
> > > @.GlobalReservingGroupCode varchar(10),
> > > @.ExchangeRateTypeLabel varchar(50))
> > >
> > > AS
> > >
> > >
> > > declare @.SQLString VARCHAR(4000)
> > >
> > > set @.SQLString = 'SELECT
> > > Dim_DataLoadOffice.Office, Dim_Team.TeamName AS Team,
> > > Dim_Underwriter.UnderwriterFullName AS Underwriter,
> > > Dim_UnderwritingYear.UnderwritingYear AS UnderwritingYear,
> > > Dim_GlobalReservingGroup.GlobalReservingGroupCode AS GRG_Code,
> > >
> > > Dim_GlobalReservingGroup.GlobalReservingGroupLongName AS GRG,
> > > Dim_ExchangeRateType.ExchangeRateTypeLabel AS ExchangeRate,
> > >
> > > Dim_BookingScenario.BookingOfficeName AS BookingOffice,
> > > Broker.DescriptiveName AS Broker,
> > > Cedant.DescriptiveName AS Cedant,
> > > MGA.DescriptiveName AS MGA,
> > > Dim_Claim.ClaimEventDsc AS ClaimEvent,
> > > Dim_Claim.ClaimHeaderClaimName AS ClaimHeader,
> > > Dim_Claim.ClaimDetailClaimName AS ClaimDetail,
> > > Dim_InwardSubContract.ContractReference AS ContractLayer,
> > >
> > > Dim_InwardSubContract.ProgrammeTitle AS ContractProgramme,
> > > Dim_InceptionDate.InceptionDate AS InceptionDate,
> > > Dim_TransactionDate.TransactionDate AS TransactionDate,
> > > Dim_DateOfLoss.DateOfLoss AS DateOfLoss,
> > > Dim_PolicyBasis.PolicyBasisName AS PolicyBasis,
> > > Dim_MethodOfAcceptance.MethodOfAcceptanceName AS MethodOfAcceptance,
> > > fact.BookedPaidClaimSettCcy AS PaidLoss,
> > > fact.BookedCaseReserveOrigCcy AS OutstandingLoss,
> > > fact.BookedIncurredLoss AS IncurredLoss
> > > FROM Fact_InwardTransaction_USD_' + @.UnderwritingYear + ' fact
> > > INNER JOIN Dim_DataLoadOffice ON fact.MISDataLoadOfficeID => > > Dim_DataLoadOffice.MISDataLoadOfficeID
> > > INNER JOIN Dim_Team ON fact.MISTeamID = Dim_Team.MISTeamID AND
> > > fact.MISDataLoadOfficeID = Dim_Team.MISDataLoadOfficeID
> > > INNER JOIN Dim_Underwriter ON fact.MISUnderwriterID => > > Dim_Underwriter.MISUnderwriterID
> > > INNER JOIN Dim_UnderwritingYear ON fact.MISUnderwritingYearID => > > Dim_UnderwritingYear.MISUnderwritingYearID
> > > INNER JOIN Dim_ExchangeRateType ON fact.MISExchangeRateTypeID => > > Dim_ExchangeRateType.MISExchangeRateTypeID
> > > INNER JOIN Dim_InwardSubContract ON fact.MISInwardSubContractID => > > Dim_InwardSubContract.MISInwardSubContractID
> > > INNER JOIN Dim_BookingScenario ON fact.MISBookingScenarioID => > > Dim_BookingScenario.MISBookingScenarioID
> > > INNER JOIN Dim_Organisation Broker ON fact.MISBrokerID => > > Broker.MISOrganisationID
> > > INNER JOIN Dim_GlobalReservingGroup ON fact.MISGlobalReservingGroupID
=> > > Dim_GlobalReservingGroup.MISGlobalReservingGroupID
> > > INNER JOIN Dim_Organisation Cedant ON fact.MISCedantID => > > Cedant.MISOrganisationID
> > > INNER JOIN Dim_Organisation MGA ON fact.MISMGAID =MGA.MISOrganisationID
> > > INNER JOIN Dim_Claim ON fact.MISClaimID = Dim_Claim.MISClaimID AND
> > > fact.MISDataLoadOfficeID = Dim_Claim.MISDataLoadOfficeID
> > > INNER JOIN Dim_InceptionDate ON fact.MISInceptionDateID => > > Dim_InceptionDate.MISInceptionDateID
> > > INNER JOIN Dim_TransactionDate ON fact.MISTransactionDateID => > > Dim_TransactionDate.MISTransactionDateID
> > > INNER JOIN Dim_DateOfLoss ON fact.MISDateOfLossID => > > Dim_DateOfLoss.MISDateOfLossID
> > > INNER JOIN Dim_PolicyBasis ON fact.MISPolicyBasisID => > > Dim_PolicyBasis.MISPolicyBasisID
> > > INNER JOIN Dim_MethodOfAcceptance ON fact.MISMethodOfAcceptanceID => > > Dim_MethodOfAcceptance.MISMethodOfAcceptanceID
> > > WHERE (Dim_DataLoadOffice.MISDataLoadOfficeID = ' +
> > > cast(@.MISDataLoadOfficeID as varchar(10)) + ')
> > > AND (Dim_Underwriter.UnderwriterFullName LIKE ''' + @.UnderwriterID +
''')
> > > AND (Dim_UnderwritingYear.UnderwritingYear LIKE ''' +
@.UnderwritingYear +
> > > ''')
> > > AND (Dim_GlobalReservingGroup.GlobalReservingGroupCode LIKE ''' +
> > > @.GlobalReservingGroupCode + ''')
> > > AND (Dim_ExchangeRateType.ExchangeRateTypeLabel LIKE ''' +
> > > @.ExchangeRateTypeLabel + ''')
> > > AND (Dim_Team.TeamCode LIKE ''' + @.TeamCode + ''')
> > > ORDER BY Dim_Team.TeamName, Dim_Underwriter.UnderwriterFullName'
> > >
> > > --print @.SQLString
> > > EXEC (@.SQLString)
> > > GO
> > >
> >
> ----
> > > Is there a way to link the fields from a successful dataset run to a
> > Report,
> > > if they are not showing in the Field window. Or is there another way I
can
> > > write the stored procedure without
> > > using UNION ALL to combine the table together. This will take a long
time
> > > when searching for records for a particular year.
> >
> >
> >|||Try setting default for the parameter in your sproc
(@.MISDataLoadOfficeID int = 5)
Jeff
"Michael" <Michael@.discussions.microsoft.com> wrote in message
news:DD4B3A3B-DC83-455C-A2EE-4C7F7FFC48C2@.microsoft.com...
> I need help with creating a Stored procedure dataset for a Reporting
> Services Report:-
> I am creating a dataset using a stored procedure that has parameters and I
> am unable to see/use the Fields to create a report.
> Here is an example of the stored procedure
> CREATE procedure rptDataLoadOffice2(@.MISDataLoadOfficeID int)
> AS
> declare @.SQLString VARCHAR(400)
> Set @.SQLString = 'Select MISDataLoadOfficeID, IsDeleted, InsertDateTime,
> LastUpdateDateTime, ShortName, Office
> From Dim_DataLoadOffice
> Where MISDataLoadOfficeID = ' + cast(@.MISDataLoadOfficeID as varchar(10))
+
> ' Order By Office'
> --PRINT (@.SQLString)
> EXEC (@.SQLString)
> The stored procedure runs fine, but does not create the field output
needed
> for the report, I should be able to see the fields for each dataset in the
> Field window.
> I notice this only happen when there is a parameter involve. I have tried
> the above sp with no parameter and it works fine. I have the above sp with
a
> varchar parameter and I am having the same problem.
> Try creating any sp in the above format and try to see if it will display
> the dataset fields.
>
> I have enclosed the actual stored procedure I am trying to use below,
which
> is having the same problem. I need to write the stored procedure in this
> format because, I need the UnderwritingYear parameter to determine which
> table to select from (i.e Fact_InwardTransaction_USD_' +
@.UnderwritingYear ).
> The are 30 tables involve (ie from Fact_InwardTransaction_USD_1970 to
> Fact_InwardTransaction_USD_2009)
> ---
> CREATE procedure rptInwardTransaction_USD(
> @.MISDataLoadOfficeID int,
> @.TeamCode varchar(10),
> @.UnderwriterID varchar(100),
> @.UnderwritingYear varchar(6),
> @.GlobalReservingGroupCode varchar(10),
> @.ExchangeRateTypeLabel varchar(50))
> AS
>
> declare @.SQLString VARCHAR(4000)
> set @.SQLString = 'SELECT
> Dim_DataLoadOffice.Office, Dim_Team.TeamName AS Team,
> Dim_Underwriter.UnderwriterFullName AS Underwriter,
> Dim_UnderwritingYear.UnderwritingYear AS UnderwritingYear,
> Dim_GlobalReservingGroup.GlobalReservingGroupCode AS GRG_Code,
> Dim_GlobalReservingGroup.GlobalReservingGroupLongName AS GRG,
> Dim_ExchangeRateType.ExchangeRateTypeLabel AS ExchangeRate,
> Dim_BookingScenario.BookingOfficeName AS BookingOffice,
> Broker.DescriptiveName AS Broker,
> Cedant.DescriptiveName AS Cedant,
> MGA.DescriptiveName AS MGA,
> Dim_Claim.ClaimEventDsc AS ClaimEvent,
> Dim_Claim.ClaimHeaderClaimName AS ClaimHeader,
> Dim_Claim.ClaimDetailClaimName AS ClaimDetail,
> Dim_InwardSubContract.ContractReference AS ContractLayer,
> Dim_InwardSubContract.ProgrammeTitle AS ContractProgramme,
> Dim_InceptionDate.InceptionDate AS InceptionDate,
> Dim_TransactionDate.TransactionDate AS TransactionDate,
> Dim_DateOfLoss.DateOfLoss AS DateOfLoss,
> Dim_PolicyBasis.PolicyBasisName AS PolicyBasis,
> Dim_MethodOfAcceptance.MethodOfAcceptanceName AS MethodOfAcceptance,
> fact.BookedPaidClaimSettCcy AS PaidLoss,
> fact.BookedCaseReserveOrigCcy AS OutstandingLoss,
> fact.BookedIncurredLoss AS IncurredLoss
> FROM Fact_InwardTransaction_USD_' + @.UnderwritingYear + ' fact
> INNER JOIN Dim_DataLoadOffice ON fact.MISDataLoadOfficeID => Dim_DataLoadOffice.MISDataLoadOfficeID
> INNER JOIN Dim_Team ON fact.MISTeamID = Dim_Team.MISTeamID AND
> fact.MISDataLoadOfficeID = Dim_Team.MISDataLoadOfficeID
> INNER JOIN Dim_Underwriter ON fact.MISUnderwriterID => Dim_Underwriter.MISUnderwriterID
> INNER JOIN Dim_UnderwritingYear ON fact.MISUnderwritingYearID => Dim_UnderwritingYear.MISUnderwritingYearID
> INNER JOIN Dim_ExchangeRateType ON fact.MISExchangeRateTypeID => Dim_ExchangeRateType.MISExchangeRateTypeID
> INNER JOIN Dim_InwardSubContract ON fact.MISInwardSubContractID => Dim_InwardSubContract.MISInwardSubContractID
> INNER JOIN Dim_BookingScenario ON fact.MISBookingScenarioID => Dim_BookingScenario.MISBookingScenarioID
> INNER JOIN Dim_Organisation Broker ON fact.MISBrokerID => Broker.MISOrganisationID
> INNER JOIN Dim_GlobalReservingGroup ON fact.MISGlobalReservingGroupID => Dim_GlobalReservingGroup.MISGlobalReservingGroupID
> INNER JOIN Dim_Organisation Cedant ON fact.MISCedantID => Cedant.MISOrganisationID
> INNER JOIN Dim_Organisation MGA ON fact.MISMGAID = MGA.MISOrganisationID
> INNER JOIN Dim_Claim ON fact.MISClaimID = Dim_Claim.MISClaimID AND
> fact.MISDataLoadOfficeID = Dim_Claim.MISDataLoadOfficeID
> INNER JOIN Dim_InceptionDate ON fact.MISInceptionDateID => Dim_InceptionDate.MISInceptionDateID
> INNER JOIN Dim_TransactionDate ON fact.MISTransactionDateID => Dim_TransactionDate.MISTransactionDateID
> INNER JOIN Dim_DateOfLoss ON fact.MISDateOfLossID => Dim_DateOfLoss.MISDateOfLossID
> INNER JOIN Dim_PolicyBasis ON fact.MISPolicyBasisID => Dim_PolicyBasis.MISPolicyBasisID
> INNER JOIN Dim_MethodOfAcceptance ON fact.MISMethodOfAcceptanceID => Dim_MethodOfAcceptance.MISMethodOfAcceptanceID
> WHERE (Dim_DataLoadOffice.MISDataLoadOfficeID = ' +
> cast(@.MISDataLoadOfficeID as varchar(10)) + ')
> AND (Dim_Underwriter.UnderwriterFullName LIKE ''' + @.UnderwriterID + ''')
> AND (Dim_UnderwritingYear.UnderwritingYear LIKE ''' + @.UnderwritingYear +
> ''')
> AND (Dim_GlobalReservingGroup.GlobalReservingGroupCode LIKE ''' +
> @.GlobalReservingGroupCode + ''')
> AND (Dim_ExchangeRateType.ExchangeRateTypeLabel LIKE ''' +
> @.ExchangeRateTypeLabel + ''')
> AND (Dim_Team.TeamCode LIKE ''' + @.TeamCode + ''')
> ORDER BY Dim_Team.TeamName, Dim_Underwriter.UnderwriterFullName'
> --print @.SQLString
> EXEC (@.SQLString)
> GO
> ----
> Is there a way to link the fields from a successful dataset run to a
Report,
> if they are not showing in the Field window. Or is there another way I can
> write the stored procedure without
> using UNION ALL to combine the table together. This will take a long time
> when searching for records for a particular year.sql