creating a time dimension

this is my first cube using 2005 analysis services. i have been using 2000 Analysis Services for about five years.

i have a cube with about 5 small dimensions and am trying to add a time dimension to include some of the Year to Year and Month to Month calculations.

i have a table with Year in column 1 and Month in column 2 mapped to the fact table.

before i add the time dimension everyting processes fine. after i add the Year-Month Hierarchy, the processing starts and gets to the SQL query to populate the cube but seems to go into a loop and never completes.

i don't have a clue what is going on.

does anyone have a suggestion?

Just want to make sure I understand the scenario ....

You have a Time dimension table with a structure similar to this:

create table DIM.Time (

TimeID int not null identity(1,1),

Year int null,

Month int null

...

)

Your fact table has a structure similar to this:

create table FACT.MyFact (

DimID int not null,

OtherDimID int not null,

TimeID int not null,

Measure1 money null,

...

)

Is this correct?

If so, do you get the "loop" when you process the Time dimension or just when you process the cube?

Thanks,

Bryan

|||

the dim time does not have a time id just year and month.

the fact table also has year and month associated with each fact.

the fact year and month are mapped to the dim year and month.

and the dim table is used for the dimension generation.

i also tried using the fact table year and month for the dimension generation with the same result.

the loop happens when i process the cube.

|||

I'd recommend using a single key for all foreign key references. If you don't have a surogate key for time, at least have a single smart key of YYYYMM in the fact table that references a single key in the dimension. Worst case, you can combine the year and month values in the fact table and the time dimension tables through the DSV.

Not 100% certain that's the source of your exact problem, but its worth giving it a shot.

B.

|||

That worked!

thanks.

i guess this version of Analysis Services is more traditionally Relational.

sql

creating a time dimension

this is my first cube using 2005 analysis services. i have been using 2000 Analysis Services for about five years.

i have a cube with about 5 small dimensions and am trying to add a time dimension to include some of the Year to Year and Month to Month calculations.

i have a table with Year in column 1 and Month in column 2 mapped to the fact table.

before i add the time dimension everyting processes fine. after i add the Year-Month Hierarchy, the processing starts and gets to the SQL query to populate the cube but seems to go into a loop and never completes.

i don't have a clue what is going on.

does anyone have a suggestion?

Just want to make sure I understand the scenario ....

You have a Time dimension table with a structure similar to this:

create table DIM.Time (

TimeID int not null identity(1,1),

Year int null,

Month int null

...

)

Your fact table has a structure similar to this:

create table FACT.MyFact (

DimID int not null,

OtherDimID int not null,

TimeID int not null,

Measure1 money null,

...

)

Is this correct?

If so, do you get the "loop" when you process the Time dimension or just when you process the cube?

Thanks,

Bryan

|||

the dim time does not have a time id just year and month.

the fact table also has year and month associated with each fact.

the fact year and month are mapped to the dim year and month.

and the dim table is used for the dimension generation.

i also tried using the fact table year and month for the dimension generation with the same result.

the loop happens when i process the cube.

|||

I'd recommend using a single key for all foreign key references. If you don't have a surogate key for time, at least have a single smart key of YYYYMM in the fact table that references a single key in the dimension. Worst case, you can combine the year and month values in the fact table and the time dimension tables through the DSV.

Not 100% certain that's the source of your exact problem, but its worth giving it a shot.

B.

|||

That worked!

thanks.

i guess this version of Analysis Services is more traditionally Relational.

Creating a template

Can anybody help me how to create a template report i SQL Reporting Services 2005

Masi,

Create a SQL sample report of what you want your templete to be and then copy the rdl from to this local directory C:\Program Files\Microsoft Visual Studio 8\Common7\IDE\PrivateAssemblies\ProjectItems\ReportProject

all rdl files in this directory show up when you add new item in BIDS.

Hammer

|||

Thanks

It works

|||Not a problem.

creating a template

Hi Guys
I have developed a new report in reporting services 2005. now i want to
use this report as a template. Where do i store this report in sql
server sub directory so that it is available in the template option
whenever i try to create a new report.
Any help would be great.
PassxSearch for "report.rdl" or report project in your HDD. Because I dont
remember the exact directory infact i have done it lot of times. or may be
just right click on the report and see the property, so that you can get
complete directory.
Amarnath
"Passx" wrote:
> Hi Guys
> I have developed a new report in reporting services 2005. now i want to
> use this report as a template. Where do i store this report in sql
> server sub directory so that it is available in the template option
> whenever i try to create a new report.
> Any help would be great.
>
> Passx
>|||Hi
I tried doing the same thing but was not able to find it. Found one in
vs8 folder. But when i placed my report in that folder was not able to
find that in the report template menu.
Any help would be great.
Passx
Amarnath wrote:
> Search for "report.rdl" or report project in your HDD. Because I dont
> remember the exact directory infact i have done it lot of times. or may be
> just right click on the report and see the property, so that you can get
> complete directory.
> Amarnath
> "Passx" wrote:
> > Hi Guys
> >
> > I have developed a new report in reporting services 2005. now i want to
> > use this report as a template. Where do i store this report in sql
> > server sub directory so that it is available in the template option
> > whenever i try to create a new report.
> >
> > Any help would be great.
> >
> >
> > Passx
> >
> >

Creating a Subtotal of select Groups

Hi,

I am working on a new reporting system using reporting services, but I cannot figure out how to create a footer row which will only subtotal select group totals. If anyone has a method to do this please help!

Nathan

If you have a matrix report right click on the group and select the option subtotal.
If you have a tabular report right click on the left side of the table and select table footer. Then in each field you want to summarize put = SUM(Fields!FieldName.Value)
That's all|||

I've been able to do that for individual groups, but what I want to do is make footer subtotal of a select set of groups. So say I have data grouped by Credit card type. I have a group for MC, and another for Visa, and another for American express.

I want a footer total of just the MC and Visa groups, excluding the total for American express.

|||You could use an expression similar to this in the footer (it will add 0 instead of the actual amount if the card was Amex):
=Sum(iif(Fields!CardType.Value = "Amex", 0, Fields!TransactionAmount.Value))

--Robert|||Alright!

Thank you Robert. you made my day |||

When running this selective sum, i get a scope error. I have tried giving it a group name and a dataset. What am i doing wrong? here is my code:

=Sum(iff(Fields!CardType.Value = "Visa/Mc" OR Fields!cardType.Value = "Diner" OR Fields!cardType.Value = "JCB", Fields!amount.Value, 0))
This is my error:

"The value expression for the textbox ‘textbox9’ refers to the field ‘CardType’. Report item expressions can only refer to fields within the current data set scope or, if inside an aggregate, the specified data set scope."

Whats wrong?

|||I have the same problem. I don't want to sum a select number of groups, but all groups within the report. The principle is the same as the above, and I get the same scope error.
In my case I have a bunch of items grouped by customer. Each customer has a subtotal, and I want to have a grand total of all the customers.
Any ideas/workarounds?|||Note: Field names are case-sensitive. In your expression it seems like you have upper-case and lower-case "CardType" fields.
Also, are the cardType field and the amount field in the same dataset?

-- Robert|||If you want to get the grand total, you just need to specify either the data region name (i.e. table, list, or matrix report item name) or the data set name.
E.g.
=Sum(Fields!Amount.Value, "DataSet1")

-- Robert|||Thanks, Robert! I knew it had to be something simple. |||Hi, somehow related with the topic:
Is posible to have something like: the sum of the ValueField from all the rows of DataSet2 that have CompareFiled equal with the current value of ComparedWithField from DataSet1?
In other words: in the expression of the SUM function can be used more then one scope?
=Sum(iff( DS2!Fields!CardType.Value = Fields!CT.Value, DS2!Fields!amount.Value, 0))

creating a subscription via an application

Hello,

I am wondering if there is some sample code out there that shows how to create a subscription for a report on reporting services via a win app or if anyone has a better suggestion. We are wanting to have a report that resides on reporting services server be sent to a client via email subscription, but do not want the client to goto the actual website that host reporting services. Thanks in advance.

John

There are SOAP APIs that lets you create subscriptions programmatically. Is that what you are looking for? See the following link for details.

http://msdn2.microsoft.com/en-us/library/microsoft.wssux.reportingserviceswebservice.rsmanagementservice2005.reportingservice2005.createsubscription.aspx

Thanks,

Sharmila

|||This was it. Thank you!!

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 report using a stored procedure

Hi All,

Is there a way to create a report in SQL2005 reporting services to call a stored procedure that has parameters? I keep getting an error saying i am not supplying the params, I can't seem to find where to put my parameters for the report to use.

Thanks, Joe

You would be better posting this on the reporting services forum, as this is mainly for T-SQL issues

Creating a report using a Stored Procedure

Hi All,

Is there a way to create a report in SQL2005 reporting services to call a stored procedure that has parameters? I keep getting an error saying i am not supplying the params, I can't seem to find where to put my parameters for the report to use.

Thanks,

Joe

How about something like

exec uspTest @.parm1, @.parm2

If I remember right it will provide a couple of boxes for you to type the parm values when you view the report in Report Manager

|||the parameters (Report Parameters) you have in RS should have the same name as the parameters you declare in stored procedure.sql

Creating a Report Model on a UDB datasource

Hi all,

I'm just getting started with Reporting Services and have a question.

I'm trying to set up a report model based on a UDB data source. However when I use the report model wizard, I get presented with:

[DB2/SUN] SQL0104N An unexpected token "SET TRANSACTION" was found following "BEGIN-OF-STATEMENT" ... SQLSTATE 42601.

I think that this is trying to set transaction isolation levels however this is not valid SQL for a UDB database.

Is there something that I'm missing with my configuration to make RS know that this is a non-SQLServer database?

Many thanks,

JK

Hello,

Did you ever receive an answer to this issue?

Thanks.

Creating a Report Model on a UDB datasource

Hi all,

I'm just getting started with Reporting Services and have a question.

I'm trying to set up a report model based on a UDB data source. However when I use the report model wizard, I get presented with:

[DB2/SUN] SQL0104N An unexpected token "SET TRANSACTION" was found following "BEGIN-OF-STATEMENT" ... SQLSTATE 42601.

I think that this is trying to set transaction isolation levels however this is not valid SQL for a UDB database.

Is there something that I'm missing with my configuration to make RS know that this is a non-SQLServer database?

Many thanks,

JK

Hello,

Did you ever receive an answer to this issue?

Thanks.

Creating a Report Model from Analysis Services Cube then Editing

I have been told that you can create a Report Model from an Analysis Services
Cube in the Model Designer even though the documentation says you can not.
I can import the Analysis Project that contains the cube into BI Development
Studio but I can not create a model from that project.
Does anyone know how to create then edit a model in the Model Designer(BI
Development Studio not Management Studio or Report Manager) that is based on
an AS Cube?
--
New user - jhpuddyhave you noticed ? both while creating report model and cube you can see
something called "Data Source View" nothing but building relationships
between tables. so same refer your dw database and create view and you can
use report model to create reports using cube and view.
Amarnath
"jhpuddy" wrote:
> I have been told that you can create a Report Model from an Analysis Services
> Cube in the Model Designer even though the documentation says you can not.
> I can import the Analysis Project that contains the cube into BI Development
> Studio but I can not create a model from that project.
> Does anyone know how to create then edit a model in the Model Designer(BI
> Development Studio not Management Studio or Report Manager) that is based on
> an AS Cube?
> --
> New user - jhpuddy|||Amarnath,
Thank you for responding.
But the problem is that I can't create a model from a cube in BI Development
Studio. I can create one in Management Studio but I can not edit the Model
after it is created. We were planning on doing all of the formatting,
grouping and filtering in the model but we can not do that if we can not edit
it. The only thing that comes up for the model is the source code, can not
edit the code.
--
New user - jhpuddy
"Amarnath" wrote:
> have you noticed ? both while creating report model and cube you can see
> something called "Data Source View" nothing but building relationships
> between tables. so same refer your dw database and create view and you can
> use report model to create reports using cube and view.
> Amarnath
>
> "jhpuddy" wrote:
> > I have been told that you can create a Report Model from an Analysis Services
> > Cube in the Model Designer even though the documentation says you can not.
> > I can import the Analysis Project that contains the cube into BI Development
> > Studio but I can not create a model from that project.
> > Does anyone know how to create then edit a model in the Model Designer(BI
> > Development Studio not Management Studio or Report Manager) that is based on
> > an AS Cube?
> >
> > --
> > New user - jhpuddy|||You can do one thing If you have created a Analysis services project ie cube.
you can create one using "create datasource from other object" from add data
source.
Amarnath
"jhpuddy" wrote:
> Amarnath,
> Thank you for responding.
> But the problem is that I can't create a model from a cube in BI Development
> Studio. I can create one in Management Studio but I can not edit the Model
> after it is created. We were planning on doing all of the formatting,
> grouping and filtering in the model but we can not do that if we can not edit
> it. The only thing that comes up for the model is the source code, can not
> edit the code.
> --
> New user - jhpuddy
>
> "Amarnath" wrote:
> > have you noticed ? both while creating report model and cube you can see
> > something called "Data Source View" nothing but building relationships
> > between tables. so same refer your dw database and create view and you can
> > use report model to create reports using cube and view.
> >
> > Amarnath
> >
> >
> > "jhpuddy" wrote:
> >
> > > I have been told that you can create a Report Model from an Analysis Services
> > > Cube in the Model Designer even though the documentation says you can not.
> > > I can import the Analysis Project that contains the cube into BI Development
> > > Studio but I can not create a model from that project.
> > > Does anyone know how to create then edit a model in the Model Designer(BI
> > > Development Studio not Management Studio or Report Manager) that is based on
> > > an AS Cube?
> > >
> > > --
> > > New user - jhpuddy|||Yes, I know I have done that. It lets you create a data source but does not
recognize that data source when you try to create a data source view. It
puts you into a sort of loop.
--
New user - jhpuddy
"Amarnath" wrote:
> You can do one thing If you have created a Analysis services project ie cube.
> you can create one using "create datasource from other object" from add data
> source.
> Amarnath
> "jhpuddy" wrote:
> > Amarnath,
> >
> > Thank you for responding.
> >
> > But the problem is that I can't create a model from a cube in BI Development
> > Studio. I can create one in Management Studio but I can not edit the Model
> > after it is created. We were planning on doing all of the formatting,
> > grouping and filtering in the model but we can not do that if we can not edit
> > it. The only thing that comes up for the model is the source code, can not
> > edit the code.
> > --
> > New user - jhpuddy
> >
> >
> > "Amarnath" wrote:
> >
> > > have you noticed ? both while creating report model and cube you can see
> > > something called "Data Source View" nothing but building relationships
> > > between tables. so same refer your dw database and create view and you can
> > > use report model to create reports using cube and view.
> > >
> > > Amarnath
> > >
> > >
> > > "jhpuddy" wrote:
> > >
> > > > I have been told that you can create a Report Model from an Analysis Services
> > > > Cube in the Model Designer even though the documentation says you can not.
> > > > I can import the Analysis Project that contains the cube into BI Development
> > > > Studio but I can not create a model from that project.
> > > > Does anyone know how to create then edit a model in the Model Designer(BI
> > > > Development Studio not Management Studio or Report Manager) that is based on
> > > > an AS Cube?
> > > >
> > > > --
> > > > New user - jhpuddy

Creating a PowerPoint Slide/PPT from Reporting Services Output

Hello,
I am trying to copy the rendered output from Reporting Services to create a
PowerPoint presentation. I understand that it is not possible to directly
render to a PPT and I am able to do so by saving the output IMAGE stream to a
file, and then loading PPT sildes from that file. However, my server will
have numerous hits and this is a very costly operation (in terms of disk
usage and performance). Is there any way or 3rd party tool by which I can
convert the Reporting Services output to a PPT without using an intermediate
file?
Thanks,
Ajay.It would take a lot of coding, but you could write a custom rendering
extension. PowerPoint is easy to automate; its object model is quite
straightforward. But from what I hear, custom rendering extensions are very
very (very very?) difficult to write! Your idea of saving to an image and
loading the image is probably the shortest path to a PPT solution.
Alternatively, you may be able to write or find a third-party solution that
will take HTML output and convert it to PPT. Either way, you're probably
either going to do some streaming or intermediate file handling.
--
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"Ata" <ajay.abraham@.wipro.com> wrote in message
news:55EEA9F0-3618-4F3A-9BF8-3AD1C50EC89C@.microsoft.com...
> Hello,
> I am trying to copy the rendered output from Reporting Services to create
> a
> PowerPoint presentation. I understand that it is not possible to directly
> render to a PPT and I am able to do so by saving the output IMAGE stream
> to a
> file, and then loading PPT sildes from that file. However, my server will
> have numerous hits and this is a very costly operation (in terms of disk
> usage and performance). Is there any way or 3rd party tool by which I can
> convert the Reporting Services output to a PPT without using an
> intermediate
> file?
> Thanks,
> Ajay.|||I just finished doing a ppt presentation using print screen to capture the
window. Then I used the cropping and drawing tools within powerpoint to clip
out areas I wished to focus on, or render the whole window if that's what I
wanted. It was a simple process. You many want something different, but
there's a lot to be said for easy.
"Ata" wrote:
> Hello,
> I am trying to copy the rendered output from Reporting Services to create a
> PowerPoint presentation. I understand that it is not possible to directly
> render to a PPT and I am able to do so by saving the output IMAGE stream to a
> file, and then loading PPT sildes from that file. However, my server will
> have numerous hits and this is a very costly operation (in terms of disk
> usage and performance). Is there any way or 3rd party tool by which I can
> convert the Reporting Services output to a PPT without using an intermediate
> file?
> Thanks,
> Ajay.|||I'm working on copying the RS output image stream to the clipboard and from
there to PPT. However, there are some format-related issues as the RS output
is binary data but PPT expects a bit more sophistication I guess. Thanks for
the information.
Regards,
Ajay.
"Jeff A. Stucker" wrote:
> It would take a lot of coding, but you could write a custom rendering
> extension. PowerPoint is easy to automate; its object model is quite
> straightforward. But from what I hear, custom rendering extensions are very
> very (very very?) difficult to write! Your idea of saving to an image and
> loading the image is probably the shortest path to a PPT solution.
> Alternatively, you may be able to write or find a third-party solution that
> will take HTML output and convert it to PPT. Either way, you're probably
> either going to do some streaming or intermediate file handling.
> --
> '(' Jeff A. Stucker
> \
> Business Intelligence
> www.criadvantage.com|||Sorry! I'm not sure I understood you properly. Are you using the Print Screen
key on the keyboard or doing this programmatically in some way?
"CGW" wrote:
> I just finished doing a ppt presentation using print screen to capture the
> window. Then I used the cropping and drawing tools within powerpoint to clip
> out areas I wished to focus on, or render the whole window if that's what I
> wanted. It was a simple process. You many want something different, but
> there's a lot to be said for easy.

Creating a Page Index and table of contents

This is the problem I am facing with the SQL server reporting services,
I am trying to create a report where in we have to
display Page index and the table of contents Along with the Page
Number. This report contains the list of products under a subcategory
which in turn are under particular Categories. The Page Index should
display the products names in the alphabetical order with the page
number where It falls, this is similar to the appendix at the end of
any textbook and the table of contents display the category and
its subcategories with page numbers Now, the problem is reading the
report dynamically to find out the page numbers where this product
falls and the categories falls . I want a solution for displaying the
Page index and the table of contents in SQL server reporting services
2005 version.
Waiting for quick sujjestions or help in this regardAre you using web service approach?
If so,you can always get page content before displaying it and by analyzing
the underlying HTML get all information you need -
page number, total number of pages, any internal error occurred, etc. Based
on that information you can build your own page header with a custom page
index.
"Aparna" <aparna.cirigiri@.gmail.com> wrote in message
news:1135255357.206805.159890@.g44g2000cwa.googlegroups.com...
> This is the problem I am facing with the SQL server reporting services,
>
> I am trying to create a report where in we have to
> display Page index and the table of contents Along with the Page
> Number. This report contains the list of products under a subcategory
> which in turn are under particular Categories. The Page Index should
> display the products names in the alphabetical order with the page
> number where It falls, this is similar to the appendix at the end of
> any textbook and the table of contents display the category and
> its subcategories with page numbers Now, the problem is reading the
> report dynamically to find out the page numbers where this product
> falls and the categories falls . I want a solution for displaying the
> Page index and the table of contents in SQL server reporting services
> 2005 version.
> Waiting for quick sujjestions or help in this regard
>

Creating a new variable with SQL Server Analysis Services

Hi!

I have just started working with SQL Server Analysis Services and I have already expierenced some problems:
I am working with the Adventure Works data and I want to create a new variable (Customer Value) out of the following data:

"Customer ID" and "Order Number"

I.e. a customer with the "customer ID" 00001 has ordered two products (so two order numbers are linked to this customer id) --> the new variable should identify the customer as a "C" customer.

therefore -->

"D" customer value: 0 orders
"C" customer value: 1-2 orders
"B" customer value: 3-4 orders
"A" customer value: >5 orders

Do you have any ideas to solve this problem?

Thanks

Cemens

Hello Cemens,

Do you want to create another attribute for the dimension Customers? Then I would suggest to create a named calculation in the DataSourceView (DSV) of your project with SQL

Creating a credential

Hi All,
I am attempting to do a "two hop" report using Reporting Services. I created a credential on the reporting server and created a user on the data server with the same name and password as the credential.
When I try to run the report I get an error that says logon failed.
Is there a step by step document on creating and implementing credentials?
I really would like to get this running.
Thanks to all,
RoyWhat is the type of data source? What types of credentials? Are you using SQL or Windows?|||The type is sqlClient; persit security info = true.
Type of credentials? I created a credential under security > credentials. This was done on the Reporting Services Server. I created a data source and from properties the radio button Credentials stored securely on the report server is selected. The login name and password is filled out. I have selected and unselected Use as Windows credentials when connecting to the data source but neither worked.
On the server with the data, I created a user "resolution" to match the credentials name.
I really need to get this working in order to prove to upper management that Reporting Services is the tool we need.
Thanks very much,
Roy
PS If there is an article about how to setyp credentials I sure would like to know about it.

CreateReport method delivers empty response

Hello,
I use the web service for the reporting services with Delphi 7.
I call the CreateReport method and it works fine (the RDL is uploaded to the
server), but the SOAP response raises in exception because the answer is
empty:
'<?xml version="1.0" encoding="utf-8"?>
<soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<soap:Header>
<ServerInfoHeader
xmlns="http://schemas.microsoft.com/sqlserver/2003/12/reporting/reportingservices">
<ReportServerVersionNumber>Microsoft SQL Server Reporting Services, Version
8.00.1038.00</ReportServerVersionNumber>
<ReportServerEdition>Enterprise</ReportServerEdition></ServerInfoHeader>
</soap:Header>
<soap:Body>
<CreateReportResponse
xmlns="http://schemas.microsoft.com/sqlserver/2003/12/reporting/reportingservices" />
</soap:Body></soap:Envelope>'
What can I do to circumvent this? I mean, CreateReport returns an array of
warnings if there are some. But when there are no warnings this error occurs.
What can I do?Hello,
I solved the problem. I wrote a function which edits the SOAP-Packet after
receiving it in Delphi.
I killed the empty response node and now it is working fine, because the
returned value is nil.
"Sandra Geisler" <SandraGeisler@.discussions.microsoft.com> schrieb im
Newsbeitrag news:2BBFAC0E-4DA3-4A3B-9E68-9A73D76D47E5@.microsoft.com...
> Hello,
> I use the web service for the reporting services with Delphi 7.
> I call the CreateReport method and it works fine (the RDL is uploaded to
> the
> server), but the SOAP response raises in exception because the answer is
> empty:
> '<?xml version="1.0" encoding="utf-8"?>
> <soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/"
> xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
> xmlns:xsd="">http://www.w3.org/2001/XMLSchema">
> <soap:Header>
> <ServerInfoHeader
> xmlns="">http://schemas.microsoft.com/sqlserver/2003/12/reporting/reportingservices">
> <ReportServerVersionNumber>Microsoft SQL Server Reporting Services,
> Version
> 8.00.1038.00</ReportServerVersionNumber>
> <ReportServerEdition>Enterprise</ReportServerEdition></ServerInfoHeader>
> </soap:Header>
> <soap:Body>
> <CreateReportResponse
> xmlns="http://schemas.microsoft.com/sqlserver/2003/12/reporting/reportingservices"
> />
> </soap:Body></soap:Envelope>'
> What can I do to circumvent this? I mean, CreateReport returns an array of
> warnings if there are some. But when there are no warnings this error
> occurs.
> What can I do?
>

Created a subscription but doesn't run

I recently installed Reporting Services on my compute
I created Reporting Services subscriptions, but they doesn't run a
al
My reports are ok, I can display them without any problem on m
browser, and export them on pdf format. But subscription seems to b
totaly inactive
- reporting services SP1 and SP2 installe
- I tried File Share (with correct credentials) and e-mai
subscriptio
- selected schedule is "hourly", every 5 minutes (for testing
- on "Subscription Details" page, status is "New subscription" an
"Last run" column is empt
Thank you for any helHi
Is SQL Server Agent Running on your SQL Server?
This is requited at this places the jobs into the RS queue when a report is
scheduled.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"MacFly" <macfly92@.tiscali-dot-fr.no-spam.invalid> wrote in message
news:Le6dnVH9WcniQXzfRVn_vQ@.giganews.com...
>I recently installed Reporting Services on my computer
> I created Reporting Services subscriptions, but they doesn't run at
> all
> My reports are ok, I can display them without any problem on my
> browser, and export them on pdf format. But subscription seems to be
> totaly inactive.
> - reporting services SP1 and SP2 installed
> - I tried File Share (with correct credentials) and e-mail
> subscription
> - selected schedule is "hourly", every 5 minutes (for testing)
> - on "Subscription Details" page, status is "New subscription" and
> "Last run" column is empty
> Thank you for any help
>|||Yes, SQL Server Agent is Running on my SQL Server
After multiple attempts, I didn't succeed
My configuration was
SQL Server was on a server running under Windows 2003 Server
Report Server, Report Manager, Report Designer were on my own compute
(for testing), under Win X
I uninstalled Report Server + Report Manager from my XP computer, an
installed them on server running with windows 2003 server (beside SQ
Server) => it works fine
So I still have no answer, I only bypassed the problem..
If anyone has an idea, it still would be welcom|||when you change the login name the service runs under the encrypted data no
longer works. This includes the scheduling since it uses encrypted logins to
login and run the report. you will have to delete encrypted data and restart
the service.
MacFly wrote:
>Yes, SQL Server Agent is Running on my SQL Server
>After multiple attempts, I didn't succeed
>My configuration was
>SQL Server was on a server running under Windows 2003 Server
>Report Server, Report Manager, Report Designer were on my own compute
>(for testing), under Win X
>I uninstalled Report Server + Report Manager from my XP computer, an
>installed them on server running with windows 2003 server (beside SQ
>Server) => it works fine
>So I still have no answer, I only bypassed the problem..
>If anyone has an idea, it still would be welcom
--
Gene Hunter
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-reporting/200508/1