Creating a VIEW with multiple DATATABLES

I am still confounded with how to create a VIEW involving multiple DATATABLES in a DATASET. Could someone point out a MSDN page where I can see this working and how I go about doing this? Let me re-iterate - I want to create a VIEW that uses JOIN type statements that involves multiple DATATABLES in my loaded DATASET.

Hi johncosmas

check this link you will get what you want, with code

http://www.java2s.com/Code/CSharp/Database-ADO.net/PopulateaDataSetobjectwithmultipleDataTableobjects.htm

hope it helps

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

Creating a SQLExecute function in c#

Hi im trying to create a function called SQLExecute that takes an SQL query, executes it and returns the resultant dataset in dsetResponse and if an error in strError, however i am unsure if whether im on the right track or not, where would i put the sql query and what else needs to be done, my code is as follows;

publicstaticDataSet SQLExecute(string strSQL,string strError)

{

DataSet dsetResponse =newDataSet();

try

{

using (SqlConnection conn =newSqlConnection(DHOC.clsDHOC.GetConnString()))

{

SqlCommand cmd =newSqlCommand();cmd.CommandType =CommandType.Text;

}

}

catch (ThreadAbortException thEx)

{

throw;

}

catch (Exception ex)

{

string strError

}

return dsetResponse;

}

At least I can point out two things:

1). You don't need to pass string error into your function because the error should be caught inside your function;

2). In your two catch blocks, you should throw the error like throw thEx and ex. In my practice, I usually just do error throw in development phase. Before I move to product, I will LOG the error, and return null for the DataSet if error is caught, so that client will not be able to see ugly error.

|||

Hi thanks for responding, i have adjusted it but i dont think what i have done so far is correct;

publicstaticDataSet SQLExecute(string strSQL)

{

string strData ="";string strTableName ="";

DataSet dsetResponse =newDataSet();

try

{

using (SqlConnection conn =newSqlConnection(DHOC.clsDHOC.GetConnString()))

{

SqlCommand cmd =newSqlCommand("SELECT ColumnName FROM TableName WHERE ColumnName = 'ColumnValue'", conn);

cmd.CommandType =CommandType.Text;

cmd.Parameters["@.ColumnName"].Value = strData;

cmd.Parameters["@.TableName"].Value = strTableName;

cmd.Parameters["@.ColumnValue"].Value = strData;

cmd.ExecuteNonQuery;

}

}

catch (ThreadAbortException thEx)

{

throw;

}

catch (Exception ex)

{

clsDHOC objDHOC =newclsDHOC();objDHOC.Write2ErrorLogTable(ex1.Message,"GetDataSetByID","clsDHOC", System.Web.HttpContext.Current.Session["UserFullName"].ToString());

}

return dsetResponse;

}

|||

Hi,

SqlCommand cmd = new SqlCommand("SELECT ColumnName FROM TableName WHERE ColumnName = 'ColumnValue'", conn);
cmd.CommandType = CommandType.Text;
cmd.Parameters["@.ColumnName"].Value = strData;
cmd.Parameters["@.TableName"].Value = strTableName;
cmd.Parameters["@.ColumnValue"].Value = strData;
cmd.ExecuteNonQuery;

From the code you provided, I think there should be something wrong while you are using Parameters. As you have modify your select command, "SELECT ColumnName FROM TableName WHERE ColumnName = 'ColumnValue'"".

There's no parameter holders for ColumnName,TableName and ColumnValue. All the parameter you want to bind should be declared with a "@." prefix which indicates that it's a parameter in your select command.

Besides, only values in condition part can be the parameter. Such value like TableName cannot be the parameter, if you want to select dynamical tables, you may create your select command manually by string.Format() method.

Thanks.

Creating a filter on a Dataset

Can I create a report parameter label list to only show the values
that have been extracted wthin a field in my dataset?
i.e. in the same way you add an autofilter on a column in excel, to
return the values in that column.You would need to have a second dataset. Make it exactly the same except use
the distinct and just the single field you care about.
Of course if you are doing this, then your main dataset should take this and
use it as a query parameter so return as little data as possible. Stay away
from filters as much as possible.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Andy" <andywilliams1971@.msn.com> wrote in message
news:17287b26-fbc2-4442-a6f2-ba6cfdd83718@.u10g2000prn.googlegroups.com...
> Can I create a report parameter label list to only show the values
> that have been extracted wthin a field in my dataset?
> i.e. in the same way you add an autofilter on a column in excel, to
> return the values in that column.

Creating a dataset

Im trying to complete my function which will allow me to insert data into a table by referencing it in the relevant pages, the following code is what i am using to take the SQL query, execute it and return the resultant dataset in destResponse. However i am getting the following error message; "Compiler Error Message:CS1026: ) expected". And it is coming from the following line;

SqlCommand command =newSqlCommand("INSERT INTO" + strTableName + strData +") VALUES (" + strData +")";

Here is my code below, please feel free to criticise it, and im sure the error above is not the only error i will be getting.

publicstaticvoid SQLExecute(string strTableName,string strData)

{

DataSet dsetResponse =newDataSet();

// create connection object

strConnection =ConfigurationManager.AppSettings["strConnectionString"];

SqlConnection conn =newSqlConnection(strConnection);

SqlCommand command =newSqlCommand("INSERT INTO" + strTableName + strData +") VALUES (" + strData +")";command.Fill(dsetResponse,"table");

conn.Close();

return dsetResponse;

}

("INSERT INTO" + strTableName + strData +" VALUES " + strData);

|||

let me know if it is ok

|||

your line should be like below

SqlCommand command =newSqlCommand("INSERT INTO " + strTableName + strData +") VALUES (" + strData +")");

|||

Missing a space after INTO.

Missing a closing parenthesis before the first semi-colon.

Missing a literal space and opening parenthesis between the strTableName and strData -- should be strTableName + ' (' + strData.

Used strData for both the column name list AND the column values.

SqlCommand command =new SqlCommand("INSERT INTO " + strTableName +" (" + strColumnList +") VALUES (" + strData +")");

|||

Hi thanks for responding, the one that worked was Motleys suggestion, however my next task is to return the results in a dataset; this is my code so far, am i on the right track?

publicstaticvoid SQLExecute(string strTableName,string strData)

{

String strConnection =null;

try

{

DataSet dsetResponse =newDataSet();

// create connection object

strConnection =ConfigurationManager.AppSettings["strConnectionString"];

SqlConnection conn =newSqlConnection(strConnection);

SqlCommand command =newSqlCommand("INSERT INTO " + strTableName +" (" + strData +") VALUES (" + strData +")");command.Fill(dsetResponse,"table");

conn.Close();

return dsetResponse;

}

}

Creating a comma delimited list

Hi,
I have a complex query where each row in the final dataset is a product.
However each product has a number of authors associated with it. What I
would like to do is have a query/subroutine join the authors to the product,
as a string:

ProductID Title Authors
1 The Sacred and the Profane John Rieggle, George Alexi
2 Dancingin the Dark DanBrown, Peter Kay, Paul
Dwebinski
Products Table
==============
ProductID
Title
Authors Table
=============
AuthorID
Name
Product Authors Table
=====================
AuthorID
ProductID
Is this at all possible?
Thanks
jr.
It is possible with the use of a UDF. However, I highly suggestthat you do this work on the front end. Preparing the data foroutput is not something the database engine should be doing, and theUDF method is fairly intensive as it results in additional queries.
Here's a link showing the UDF method:Joining Strings into Delimiter Separated Lists.

.
|||Terri,
Thanks for your reply. I understand that this is a UI rendering issue, and I have been toying with how best to proceed. Currently my paging routine returns one resultset, were each row represents a product. To return the authors, I would need to return a second resultset, and join them at the middle tier:
Pseudo steps:
1. Do complex paging query
2. Based on items in paging query, join them to the authors table and return that resultset too.
This would solve the problem, but sends more data across the wire which I was thinknig could be avoided, even though I know that the DB shouldnt be doing ui rendering steps. Other than "best practice" I see no other reason to put this code in the ui, since it is just a dumb string without much meaning when rendered in a listing. As you mention though, this maybe putting too much stress on the DB when using a UDF...I am relatively new to this so forgive my naivete...
Thanks Terri,
jr.|||

This might not be very efficient if you have a large sum of data, but here is how to do it.

create table #product (
productID int,
productTitle varchar(50)
)
go

create table #author (
authorID int,
name varchar(100)
)
go

create table #productAuthor (
productID int,
authorID int
)
go

insert into #product values (1,'product1')
insert into #product values (2,'product2')

insert into #author values (1,'author1')
insert into #author values (2,'author2')
insert into #author values (3,'author3')
insert into #author values (4,'author4')

insert into #productAuthor values (1,1)
insert into #productAuthor values (1,2)
insert into #productAuthor values (2,3)
insert into #productAuthor values (2,4)

create table #nameList (
productid int,
names varchar(1000)
)
go

select distinct p.productid into #tmp
from #product p
inner join #productAuthor pa on p.productid = pa.productid

declare @.productid int,
@.names varchar(1000)

while exists (select null from #tmp)
begin
set @.names = ''
select top 1 @.productid = productid from #tmp

select @.names = CASE WHEN @.names = '' THEN name ELSE @.names + ',' + name END
from #product p
inner join #productAuthor pa on p.productid = pa.productid
inner join #author a on pa.authorid = a.authorid
where p.productid = @.productid

insert into #nameList values (@.productid, @.names)

delete from #tmp where productid = @.productid
end

select p.productID, productTitle, nl.names
from #product p
inner join #nameList nl ON p.productid = nl.productID

Let me know if you have any questions.

Nick

Creating a chart based on data in a matrix

I am using a matrix to display current and historical financial data. To do this, I am using 5 different datasets. Each dataset contains 1 row of data for a specific year for a specific account.

Now that problem I am having is displaying this data in a chart. When I create a new chart, it asks which dataset I would like to use. I do not want to use any specific dataset, I want to get chart's data from the matrix. Is this possible?

Anybody have any ideas?

If that can't be done. Is it possible to create a chart based on multiple datasets?

|||

I think it should be possible. No matter what dataset it is theoretically pointed to, when you set up the value expressions, you can still use (First(...)) expressions to reference the other datasets, right? Considering that you only have one row per each, I mean.

>L<

|||

I can use the First() function and use multiple datasets, but this doesn't quite work the way I want it.

I have 5 seperate datasets, CurrentData, 1YearOldData, 2YearOldData, 3YearOldData, and 4YearOldData.

Lets say I want to display "Return on Assets" for the current year, and previous 3 years in my chart.

The forumla I need to use for the current ROA is... =First(Fields!NetIncome.Value, "CurrentData") / Avg(First(Fields!Assets, "CurrentData") + First(Fields!Assets, "1YearOldData")

The formula I need to use for the ROA 1 year ago is... =First(Fields!NetIncome.Value, "1YearOldData") / Avg(First(Fields!Assets, "1YearOldData") + First(Fields!Assets, "2YearOldData")

Etc...

I can't dynamically change the "Values" section of the report to display the data based on different datasets. If I enter each "value" seperately, they get grouped funny and the data isn't displayed how I need it. Is there anyway to use the data from a matrix, or any other workaround for this?

|||

Why don't you do a single dataset, with the data you need, as a UNION of the SELECTs that produce each of the single rows in the various current datasets, then? Isn't this really what you are after?

>L<

|||

Well I can do the select in a way that will give me 5 rows of data in the dataset. Each row representing the one year of data (instead of seperate datasets for each row). However, in my financial calculations, I need to use data from both the current year and the previous year (2 different rows in the dataset). I am unclear on how I can use a union statement to achieve this?

Row1 needs data for year 2006 and certain fields for 2005

Row2 needs data for year 2005 and certain fields for 2004

Row3 needs data for year 2004 and certain fields for 2003

and so on

This is now getting int T-SQL and not reporting services specifically. If its possible to use a matrix as the datasource for a chart, that is really what I want to do.

|||

Okey doke, sorry for suggesting something you don't want to get into. But...

*I* am unclear about why you think a matrix could be a "datasource". It's a representation of data, an evaluation of the data, an output, not a "source".

how to visualize this: you can talk to ReportItems("textboxWhatever").Value as a source for something in a chart. But that would be something you might do for a *label* -- an expression to provide *one* value, IOW, not a series of values. If you tried to do it as a value for the x- or y- axis, you wouldn't get what you were after. The matrix is more complex than a single textbox, but it is still an output, not something in the middle of a pipeline.

That's the best I can describe why it doesn't seem like the right approach for you to be taking. I would be trying to do this by handling the details of what I needed in SQL. Sorry if this answer is not what you're after and again I could be completely wrong.

>L<

|||

Lisa,

You are completely correct about this. I ended up making changes in my stored procedure to bring back all of the needed fields in 1 dataset.

The reason I was talking about a matrix being a "datasource" is because that is essentially how Excel works. You can highlight data in the spreadsheet and create a table from that. I wanted to know if it was possible to do the same thing in reporting services. Apparently it is not.

|||

FWIW... you're not the only one with this kind of expectation...

I could write a book about how the surface similarities between what people (users and developers both) see in Excel and what is actually going on in a reporting result (and not just a SQL Server Reporting Services reporting result!) provides unrealistic expectations.

It would be fun (not!) to write another book about how visual design tools mislead developers about the underlying architecture supporting the runtime behavior,so that when those developers try to actually WRITE SOME CODE THEMSELVES they tie themselves up in knots.

I have to stop now before this turns into a rant <g>.

>L<

Creating a chart based on data in a matrix

I am using a matrix to display current and historical financial data. To do this, I am using 5 different datasets. Each dataset contains 1 row of data for a specific year for a specific account.

Now that problem I am having is displaying this data in a chart. When I create a new chart, it asks which dataset I would like to use. I do not want to use any specific dataset, I want to get chart's data from the matrix. Is this possible?

Anybody have any ideas?

If that can't be done. Is it possible to create a chart based on multiple datasets?

|||

I think it should be possible. No matter what dataset it is theoretically pointed to, when you set up the value expressions, you can still use (First(...)) expressions to reference the other datasets, right? Considering that you only have one row per each, I mean.

>L<

|||

I can use the First() function and use multiple datasets, but this doesn't quite work the way I want it.

I have 5 seperate datasets, CurrentData, 1YearOldData, 2YearOldData, 3YearOldData, and 4YearOldData.

Lets say I want to display "Return on Assets" for the current year, and previous 3 years in my chart.

The forumla I need to use for the current ROA is... =First(Fields!NetIncome.Value, "CurrentData") / Avg(First(Fields!Assets, "CurrentData") + First(Fields!Assets, "1YearOldData")

The formula I need to use for the ROA 1 year ago is... =First(Fields!NetIncome.Value, "1YearOldData") / Avg(First(Fields!Assets, "1YearOldData") + First(Fields!Assets, "2YearOldData")

Etc...

I can't dynamically change the "Values" section of the report to display the data based on different datasets. If I enter each "value" seperately, they get grouped funny and the data isn't displayed how I need it. Is there anyway to use the data from a matrix, or any other workaround for this?

|||

Why don't you do a single dataset, with the data you need, as a UNION of the SELECTs that produce each of the single rows in the various current datasets, then? Isn't this really what you are after?

>L<

|||

Well I can do the select in a way that will give me 5 rows of data in the dataset. Each row representing the one year of data (instead of seperate datasets for each row). However, in my financial calculations, I need to use data from both the current year and the previous year (2 different rows in the dataset). I am unclear on how I can use a union statement to achieve this?

Row1 needs data for year 2006 and certain fields for 2005

Row2 needs data for year 2005 and certain fields for 2004

Row3 needs data for year 2004 and certain fields for 2003

and so on

This is now getting int T-SQL and not reporting services specifically. If its possible to use a matrix as the datasource for a chart, that is really what I want to do.

|||

Okey doke, sorry for suggesting something you don't want to get into. But...

*I* am unclear about why you think a matrix could be a "datasource". It's a representation of data, an evaluation of the data, an output, not a "source".

how to visualize this: you can talk to ReportItems("textboxWhatever").Value as a source for something in a chart. But that would be something you might do for a *label* -- an expression to provide *one* value, IOW, not a series of values. If you tried to do it as a value for the x- or y- axis, you wouldn't get what you were after. The matrix is more complex than a single textbox, but it is still an output, not something in the middle of a pipeline.

That's the best I can describe why it doesn't seem like the right approach for you to be taking. I would be trying to do this by handling the details of what I needed in SQL. Sorry if this answer is not what you're after and again I could be completely wrong.

>L<

|||

Lisa,

You are completely correct about this. I ended up making changes in my stored procedure to bring back all of the needed fields in 1 dataset.

The reason I was talking about a matrix being a "datasource" is because that is essentially how Excel works. You can highlight data in the spreadsheet and create a table from that. I wanted to know if it was possible to do the same thing in reporting services. Apparently it is not.

|||

FWIW... you're not the only one with this kind of expectation...

I could write a book about how the surface similarities between what people (users and developers both) see in Excel and what is actually going on in a reporting result (and not just a SQL Server Reporting Services reporting result!) provides unrealistic expectations.

It would be fun (not!) to write another book about how visual design tools mislead developers about the underlying architecture supporting the runtime behavior,so that when those developers try to actually WRITE SOME CODE THEMSELVES they tie themselves up in knots.

I have to stop now before this turns into a rant <g>.

>L<