Help in crystal reports 8.5

Hi all

I am working on Crystal reports 8.5
In one report we need to display weekdays and weekends in every month for the given date range

I mean

Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec
Days of Service
Weekdays Number of weekdays in the month
Weekends Number of weekends in the month

And I need to calculate total of some items from Friday after 3 pm

2) In another report I am using stored procedure to get fields
As per my knowledge we cannot use other than one stored procedure in crystal reports 8.5
So I am using sub report to get some more fields other than stored procedure fields

But in main report I need to get sub- total of fields in sub report.

Could any one please help me out?You would use the Crystal Reports (http://www.saveonsupport.com) shared variable functionality. In your subreport you would have a formula field something like this:

WhilePrintingRecords;
Shared numbervar myTotal;
myTotal := myTotal + {field name}

Add this field to the subreport and suppress it.
Then in the main report you need another formula something like this:

WhilePrintingRecords;
Shared numbervar myTotal;
myTotal

The subreport keeps adjusting the total while printing records and the main report simply displays this value.

Hop this helps!|||Thanks for your reply
it works for me

help in crystal report & dataset

I'm the new beginer in C#,I have some problem in doing crystal report:
In my application I have 2 form named Form1 and Form2.
I create one dataset named ds in Form1 and in Form2 I want to creat a crystal report that displays the data in the dataset ds in Form1.
I relly get stuck.Can anyone help me?
Thanx b4 hand!See if you find answer here
www.asp.net
http://support.businessobjects.com/sql

Help in Crystal Report

Hi,

I am new in crystal report (using vb.net). Currenlty, my report format
looks like below:

School : ABC
Class : A1
======================================
Details Stud1 IC No1 Address1
Stud2 IC No.2 Address2

Page Footer: Page 1

School : ABC
Class : A2
======================================
Details Stud3 IC No1 Address1
Stud4 IC No.2 Address2

Page Footer: Page 2

School : DBF
Class : A1
======================================
Details Stud1 IC No1 Address1
Stud2 IC No.2 Address2

Page Footer: Page 3

School : DBF
Class : A2
======================================
Details Stud3 IC No1 Address1
Stud4 IC No.2 Address2

Page Footer: Page 4

Both School and Class formula were placed in Page Header. Also, I have
two group headers ie. School then follows by Class (before the details
section). Currently, the report prints at new page for every different
Class.

How do I print the all classes of the same School continuously without
page break; and only move to new page if the current page has been
filled up. I need both the Class and School names to appear in every
new page. Also, For every new school, it should be a fresh new page ie.
should not mix the details with other school. Can anyone help in this
problem? Many thanks!Hi,

I hope you are using two groups. That means the grouping is made on "School" field as well as "Class" field. uncheck the "NEW page AFTER" under the "Class " group footer and check the same in "School" group footer.
For heading should be shown in all pages, check the "Repeat Group header on each page" Group Expert option

I hope , you got what you wanted

HELP I CANNOT DEPLOPY WITH REPORT SERVICES AND SQL SERVER 2005 EXPRES ADVANCE

Hi I had installed SQL Server 2005 Express Avanced and busines intellegence development studio express. in my windows XP profesional

I have ready my first report, and When I want deploy , the system say: Reporting Services Loign: User name & Password.

I don't know that user and password must write!!!

Please can someone help me!!!

thanks folks

david

spain

Moving to the Reporting Services forum.

Mike

sql

HELP format Page Heading

I dont usually use page headers for this reason. I usually put my headings
within the body of the report however it doesnt seem to be working properly
with this matrix so I am going to use a true page header and I am having
trouble because I need to reference fields from the database in the header.
Here is my expression:
=eSLRReporting.MultiLanguage.TranslateText( Parameters!Language.Value
,"Title") & Parameters!MetricCode.Label & vbcrlf &
eSLRReporting.MultiLanguage.TranslateText( Parameters!Language.Value
,"ReportPeriod") & format(CDate(Parameters!StartDate.Value), "MM-dd-yyyy")
& " - " & format(First (Fields!MAXDATE.Value, "GR01_10"),"MM-dd-yyyy")
It is complaining about the MAXDATE value however I need that value and it
comes from the query result. There is probably a way around this ... I just
dont deal with headers so I have never encountered this "rule" of fields
cannot be used in headers or footers.You can't directly put values from a dataset into the page header or footer,
but you can use report paramters to do this.
Create a report parameters for each query field that you want to put in the
header. Setup the parameter to be populated by the appriopriate field.
Then use textboxes that get their value form an expression to put the data
in the header.
"MJT" <MJT@.discussions.microsoft.com> wrote in message
news:E0813CA6-F8FA-4EBA-80B6-EA86C613DB98@.microsoft.com...
>I dont usually use page headers for this reason. I usually put my headings
> within the body of the report however it doesnt seem to be working
> properly
> with this matrix so I am going to use a true page header and I am having
> trouble because I need to reference fields from the database in the
> header.
> Here is my expression:
> =eSLRReporting.MultiLanguage.TranslateText( Parameters!Language.Value
> ,"Title") & Parameters!MetricCode.Label & vbcrlf &
> eSLRReporting.MultiLanguage.TranslateText( Parameters!Language.Value
> ,"ReportPeriod") & format(CDate(Parameters!StartDate.Value),
> "MM-dd-yyyy")
> & " - " & format(First (Fields!MAXDATE.Value, "GR01_10"),"MM-dd-yyyy")
>
> It is complaining about the MAXDATE value however I need that value and it
> comes from the query result. There is probably a way around this ... I
> just
> dont deal with headers so I have never encountered this "rule" of fields
> cannot be used in headers or footers.sql

Help for report(rsInvalidItemPath)

Hi
I have beening trying to build a report with 2 parameters. When I run it
under VS.net, it all went well, but after I deploy it to the server, I got
the message as following: **Reporting Services Error--The path of the item ''
is not valid. The full path must be less than 260 characters long, must start
with slash; other restrictions apply. Check the documentation for complete
set of restrictions. (rsInvalidItemPath) Get Online Help **
what went wrong, please help.
the URL as following:
http://localhost/reportserver?%2fRev_Stats%2fRev_Stats&rs:Command=Render
TIAThe url looks correct. Did you get this url by navigating to the
reportserver virtual directory? Is there anything more in the
reportserver_<timestamp>.log file?
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"wd1153" <wd1153@.discussions.microsoft.com> wrote in message
news:2B5DBCB5-B943-4675-997B-145DF0ECDF17@.microsoft.com...
> Hi
> I have beening trying to build a report with 2 parameters. When I run it
> under VS.net, it all went well, but after I deploy it to the server, I got
> the message as following: **Reporting Services Error--The path of the item
> ''
> is not valid. The full path must be less than 260 characters long, must
> start
> with slash; other restrictions apply. Check the documentation for complete
> set of restrictions. (rsInvalidItemPath) Get Online Help **
> what went wrong, please help.
> the URL as following:
> http://localhost/reportserver?%2fRev_Stats%2fRev_Stats&rs:Command=Render
> TIA
>sql

Help for printing reports programatically

Hi.....
I have developed a application which create the report and save it in
user selected location.
All this i done in one class. this application shows all the reports on
report server and render the report for user selected parameters. and
save it with folder browser option on a location. file name is report
name.
Now i have to write another class which will take this saved file name
as input and print that.
i found only printing method for a particular report but i dont want to
again render that report in print class. i have sen the available
articles on net which takes the report name and render it and print
that.
how i can access that saved file in another class and if i have to
again render that report then how i can pass those parameters value
again in render method in print class.
But for my application i have already render that report, so any one
please tell me how i can do that.
Thanks & Regards
DineshDinesh,
This is just a suggestion - what if you saved the report as a pdf file ?
Displaying a pdf file from a location on the hard disk doesn't need the
report engine anymore.
"Dinesh" <dinesht15@.gmail.com> wrote in message
news:1164717797.911571.169550@.j72g2000cwa.googlegroups.com...
> Hi.....
> I have developed a application which create the report and save it in
> user selected location.
> All this i done in one class. this application shows all the reports on
> report server and render the report for user selected parameters. and
> save it with folder browser option on a location. file name is report
> name.
>
> Now i have to write another class which will take this saved file name
> as input and print that.
> i found only printing method for a particular report but i dont want to
> again render that report in print class. i have sen the available
> articles on net which takes the report name and render it and print
> that.
>
> how i can access that saved file in another class and if i have to
> again render that report then how i can pass those parameters value
> again in render method in print class.
>
> But for my application i have already render that report, so any one
> please tell me how i can do that.
>
> Thanks & Regards
> Dinesh
>|||hi Andrei.......
Thanks for your reply.....
Ya you are right......It will not require report engine
again........
i want to know only that how i can print that saved file which is saved
in hard disk.
My problem is that i wrote print function in a seprate class. at that
place i am not able to access that file name with full path.......how
i can do this.......
if you know this please tell me..........
Thanks & Regards
Dinesh
Andrei wrote:
> Dinesh,
> This is just a suggestion - what if you saved the report as a pdf file ?
> Displaying a pdf file from a location on the hard disk doesn't need the
> report engine anymore.
> "Dinesh" <dinesht15@.gmail.com> wrote in message
> news:1164717797.911571.169550@.j72g2000cwa.googlegroups.com...
> > Hi.....
> >
> > I have developed a application which create the report and save it in
> > user selected location.
> >
> > All this i done in one class. this application shows all the reports on
> > report server and render the report for user selected parameters. and
> > save it with folder browser option on a location. file name is report
> > name.
> >
> >
> >
> > Now i have to write another class which will take this saved file name
> > as input and print that.
> >
> > i found only printing method for a particular report but i dont want to
> > again render that report in print class. i have sen the available
> > articles on net which takes the report name and render it and print
> > that.
> >
> >
> >
> > how i can access that saved file in another class and if i have to
> > again render that report then how i can pass those parameters value
> > again in render method in print class.
> >
> >
> >
> > But for my application i have already render that report, so any one
> > please tell me how i can do that.
> >
> >
> >
> > Thanks & Regards
> >
> > Dinesh
> >

Help for Dynamic Grouping!!

Hello to everyone.

I face a problem for gouping. I want to make dynamic grouping in report.

Dynammic Grouping means, I want to provide a parameter named Group By with mutivalues. I can't hide this parameter so that user can select any one value from this dropdown and when he is click View Report, takes that value and group table rows as per parameter selection.

So i want to ask if it is possible to change <groupexpression> in XML code of report dynamically as per selection.

<Grouping Name="table1_CODE">

<GroupExpressions>

<GroupExpression>=Fields!CODE.Value</GroupExpression>

</GroupExpressions>

</Grouping>

This is default grouping, when i select a value from Group By dropdown box from viewer panel it change

<GroupExpression>=Fields!CODE.Value</GroupExpression> value as per parameter value dynamcially.

If it is possible to do then pls reply me soooooooon.

Thanks in advance.

Chris Hays has a Reporting services sleazy hacks blog. He has a post there about dynamic grouping. See if this will solve your problem http://blogs.msdn.com/chrishays/archive/2004/07/15/DynamicGrouping.aspx|||

Thanks a lots.

That's same i want. Thx once again.

When i run report it is ask me to select parameter eventhough i give it default value, is that any one to resolve this problem, so that in default position it shows me report.

If possible then reply,

and U r too good.

thx.

|||

i guess your defaultvalue is invalid

meaning it is not in the parameters source value-column

|||

Thx for reply

I have provide right default value stll asking me to select value,

Reply Me soon

|||

hello,

I am using three parameters for grouing, If one is selected group will be done with that parameter value,

If any two selected grouping will be done with those two parameters in toggle mode.

same for three parameters.

My question is Will anyone tell me expression bywhich i can change visablity of group. means if only one parameter is passed only one group is ativated, if two then respective two groups can be activated, same for three.

Pls reply me soon.

|||

you have to set the initial visibility of your group-headers depend on your parameters

guess you have

group1

group2

group3

details

if you want only to group by group1, set group2 and 3 hidden and

the toggleitem for your details has to be an item in group3

if you want to group by group1 and 3,

set group2 to hidden

toggleitem for group3 has to be an item from group1

the toggleitem for your details has to be an item in group3

you will need many IIFs :)

greets

|||

Thx for replying me.

ur suggetion is helpful to me.

Thx a lot.

Help creating Embedded code Function

How can I create a VB.NET function to run my query below that my SSRS report can run to retreive TotalPostingDays so I can show that in a textbox in my report? I need help creating the function in the Report properties of my SSRS report and not sure how to call this stored procedure to return TotalPostingDays.

I need to do this calculation by using a UDF, not stored procedure maybe. The problem is, I do still need to do a lookup to my Holiday table as part of my UDF though; the rest can proabably be done in Straight VB for the weekend and other calculations:
LTER PROCEDURE SSRS_Return_TotalPostingDays

AS

DECLARE @.TotalDaysInMonth int,
@.today datetime,
@.TotalWeekendDays int,
@.TotalHolidaysThisMonth int,
@.TotalPostingDays int

SET @.today = GETDATE()

-- TOTAL DAYS THIS MONTH
SET @.TotalDaysInMonth = CASE WHEN DatePart(mm,GetDate()) IN (1,3,5,7,8,10,12) THEN
31
ELSE
DateDiff(day,GetDate(),DateAdd(mm, 1, GetDate()))
END


-- TOTAL HOLIDAYS THIS MONTH
SELECT @.TotalHolidaysThisMonth = (SELECT COUNT(*) FROM ReportingServer.dbo.Holidays
WHERE HolidayDate BETWEEN (DATEADD(DAY, -DATEPART(DAY, @.today) + 1, @.today))
AND (DATEADD(DAY, -DATEPART(DAY, @.today), DATEADD(MONTH, 1, @.today))))

-- TOTAL # WEEKEND DAYS THIS MONTH

DECLARE @.date DATETIME
SET @.date = '20060101'

SELECT @.TotalWeekendDays = 8 +
CASE WHEN ISDATE(CONVERT(CHAR(6), @.date, 112) + '29') = 1 THEN
CASE WHEN DATENAME(WEEKDAY, CONVERT(CHAR(6), @.date, 112) + '01') IN ('Saturday', 'Sunday')
THEN 1 ELSE 0 END ELSE 0 END +
CASE WHEN ISDATE(CONVERT(CHAR(6), @.date, 112) + '30') = 1 THEN
CASE WHEN DATENAME(WEEKDAY, CONVERT(CHAR(6), @.date, 112) + '02') IN ('Saturday', 'Sunday')
THEN 1 ELSE 0 END ELSE 0 END +
CASE WHEN ISDATE(CONVERT(CHAR(6), @.date, 112) + '31') = 1 THEN
CASE WHEN DATENAME(WEEKDAY, CONVERT(CHAR(6), @.date, 112) + '03') IN ('Saturday', 'Sunday')
THEN 1 ELSE 0 END ELSE 0 END

SET @.TotalPostingDays = @.TotalDaysInMonth - (@.TotalHolidaysThisMonth + @.TotalWeekendDays)
RETURN @.TotalPostingDays


for the holiday lookup, I can probably do something like this then use the variable below to proceed or something:
Dim intTotalPostingDays As Integer
Dim objConn As New SqlConnection("Data Source=server;Initial Catalog=database; integrated security=SSPI;persist security info=False; Trusted_Connection=Yes")
Dim objComm As New SqlCommand("SSRS_Return_TotalHolidaysThisMonth", objConn)
objComm.CommandType = CommandType.StoredProcedure
Dim returnValueParam As New SqlClient.SqlParameter("@.RETURN_VALUE", SqlDbType.Int)
objComm.Parameters.Add(returnValueParam)
objComm.Connection.Open()
Dim objReader As SqlClient.SqlDataReader = objComm.ExecuteReader()
intTotalHolidays = returnValueParam.Value()

should I use executescalar instead of datareader? I am not sure where to go here for the entire function that I need so I can get this into my SSRS report.

I would do it a little more simply and call the stored procedure using the RS data source and query functionality. You can call the stored procedure and create a one row data set to use in your report.

Forgot to add you can have multiple datasets in your report, so you are not limited to this query.

|||actually, that's not a bad idea...thanks, will try it.|||

For some reason, completely forgot about datasets in my report! I had initially created one to run a Stored Proc as the DataSet...then I just added another to run this stored proc to return the field then added that field to a textbox and that was it!

thanks for refreshing my memory about datasource, which lead me to create a new dataset instead!

Help creating Embedded code Function

How can I create a VB.NET function to run my query below that my SSRS report can run to retreive TotalPostingDays so I can show that in a textbox in my report? I need help creating the function in the Report properties of my SSRS report and not sure how to call this stored procedure to return TotalPostingDays.

I need to do this calculation by using a UDF, not stored procedure maybe. The problem is, I do still need to do a lookup to my Holiday table as part of my UDF though; the rest can proabably be done in Straight VB for the weekend and other calculations:
LTER PROCEDURE SSRS_Return_TotalPostingDays

AS

DECLARE @.TotalDaysInMonth int,
@.today datetime,
@.TotalWeekendDays int,
@.TotalHolidaysThisMonth int,
@.TotalPostingDays int

SET @.today = GETDATE()

-- TOTAL DAYS THIS MONTH
SET @.TotalDaysInMonth = CASE WHEN DatePart(mm,GetDate()) IN (1,3,5,7,8,10,12) THEN
31
ELSE
DateDiff(day,GetDate(),DateAdd(mm, 1, GetDate()))
END


-- TOTAL HOLIDAYS THIS MONTH
SELECT @.TotalHolidaysThisMonth = (SELECT COUNT(*) FROM ReportingServer.dbo.Holidays
WHERE HolidayDate BETWEEN (DATEADD(DAY, -DATEPART(DAY, @.today) + 1, @.today))
AND (DATEADD(DAY, -DATEPART(DAY, @.today), DATEADD(MONTH, 1, @.today))))

-- TOTAL # WEEKEND DAYS THIS MONTH

DECLARE @.date DATETIME
SET @.date = '20060101'

SELECT @.TotalWeekendDays = 8 +
CASE WHEN ISDATE(CONVERT(CHAR(6), @.date, 112) + '29') = 1 THEN
CASE WHEN DATENAME(WEEKDAY, CONVERT(CHAR(6), @.date, 112) + '01') IN ('Saturday', 'Sunday')
THEN 1 ELSE 0 END ELSE 0 END +
CASE WHEN ISDATE(CONVERT(CHAR(6), @.date, 112) + '30') = 1 THEN
CASE WHEN DATENAME(WEEKDAY, CONVERT(CHAR(6), @.date, 112) + '02') IN ('Saturday', 'Sunday')
THEN 1 ELSE 0 END ELSE 0 END +
CASE WHEN ISDATE(CONVERT(CHAR(6), @.date, 112) + '31') = 1 THEN
CASE WHEN DATENAME(WEEKDAY, CONVERT(CHAR(6), @.date, 112) + '03') IN ('Saturday', 'Sunday')
THEN 1 ELSE 0 END ELSE 0 END

SET @.TotalPostingDays = @.TotalDaysInMonth - (@.TotalHolidaysThisMonth + @.TotalWeekendDays)
RETURN @.TotalPostingDays


for the holiday lookup, I can probably do something like this then use the variable below to proceed or something:
Dim intTotalPostingDays As Integer
Dim objConn As New SqlConnection("Data Source=server;Initial Catalog=database; integrated security=SSPI;persist security info=False; Trusted_Connection=Yes")
Dim objComm As New SqlCommand("SSRS_Return_TotalHolidaysThisMonth", objConn)
objComm.CommandType = CommandType.StoredProcedure
Dim returnValueParam As New SqlClient.SqlParameter("@.RETURN_VALUE", SqlDbType.Int)
objComm.Parameters.Add(returnValueParam)
objComm.Connection.Open()
Dim objReader As SqlClient.SqlDataReader = objComm.ExecuteReader()
intTotalHolidays = returnValueParam.Value()

should I use executescalar instead of datareader? I am not sure where to go here for the entire function that I need so I can get this into my SSRS report.

I would do it a little more simply and call the stored procedure using the RS data source and query functionality. You can call the stored procedure and create a one row data set to use in your report.

Forgot to add you can have multiple datasets in your report, so you are not limited to this query.

|||actually, that's not a bad idea...thanks, will try it.|||

For some reason, completely forgot about datasets in my report! I had initially created one to run a Stored Proc as the DataSet...then I just added another to run this stored proc to return the field then added that field to a textbox and that was it!

thanks for refreshing my memory about datasource, which lead me to create a new dataset instead!

sql

Help Creating Dynamic Reports

I can create static report with Report Server, but wanted if anyone can help me create a report that is dynamic and regenerates with new data as it comes in.

Thanks so much!

Jvinsky,

Maybe I missing something in what your asking, but adding a dataset to your report, add those fields on the report will get you a dynamic report as "your data changes". If you add a dataset which returns customers, you will see all of your customer, if customers are added to the table and if you refresh your report then you will see those "new" customers.

I hope this is what you meant by "dynamic".

Ham

Help converting procedural VB code to SQL

I am at the last hurdle on converting a very large chunk of VB code that
massages a recordset to produce a report.
This question relates to my previous question from 3/2 and the ddl that I
posted for that question.
I need to replace the following vb code with SQL and I think I can do it
with a Case statement but would really appreciate some input on this. The V
B
code follows the url for the original message.
http://msdn.microsoft.com/newsgroup...r />
4F24A2-F7
1F-425F-AC2B-DC48AB0DA5C9&dglist=&ptlist=&exp=&sloc=en-us
'***********Code Start************
Dim TempValue As Single
If iFactor <> 0 And iFactor <> 1 Then ' Pursue adjustment
If iGreenRpt Then ' A green report has been requested
If iRunInData Then 'Data or spec is not green so make adjustment
TempValue = iValue * iFactor
Else ' Take data as is "Green"
TempValue = iValue
End If
statAdjustData = TempValue
Else 'Non-green or Runin/"Market Rating" report has been requested
If Not iRunInData Then 'Data or spec is green so make adjustment
TempValue = iValue / iFactor
Else 'Take data as is "Runin"
TempValue = iValue
End If
statAdjustData = TempValue
End If ' Green or Runin/Market data report requested
Else ' iFactor = 1 or 0 therefore no need to adjust
statAdjustData = iValue
End If ' iFactor = To Or <> 1 or 0
'********Code End*****************--BEGIN PGP SIGNED MESSAGE--
Hash: SHA1
Perhaps:
DECLARE @.True TINYINT, @.False TINYINT
SET @.True = 1
Set @.False = 0
SELECT ... ,
CASE WHEN iFactor Not In (0,1)
THEN CASE WHEN iGreenReport = @.True
THEN CASE WHEN iRunInData = @.True
THEN iValue * iFactor
ELSE iValue
END
WHEN iGreenReport = @.False
THEN CASE WHEN iRunInData = @.False
THEN iValue / iFactor
ELSE iValue
END
END
ELSE iValue
END As statAdjustData
FROM ...
WHERE ...
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
--BEGIN PGP SIGNATURE--
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/ AwUBRAzCQoechKqOuFEgEQJEMwCePBFQCl7kq6CW
NHM1LTWmKqgLGf8An29S
Yk4XGtIPaWOgPNdJC8g+Zz1r
=ZTUB
--END PGP SIGNATURE--
StvJston wrote:
> I am at the last hurdle on converting a very large chunk of VB code that
> massages a recordset to produce a report.
> This question relates to my previous question from 3/2 and the ddl that I
> posted for that question.
> I need to replace the following vb code with SQL and I think I can do it
> with a Case statement but would really appreciate some input on this. The
VB
> code follows the url for the original message.
> http://msdn.microsoft.com/newsgroup...76C%2C774F24A2-
F71F-425F-AC2B-DC48AB0DA5C9&dglist=&ptlist=&exp=&sloc=en-us
> '***********Code Start************
> Dim TempValue As Single
> If iFactor <> 0 And iFactor <> 1 Then ' Pursue adjustment
> If iGreenRpt Then ' A green report has been requested
> If iRunInData Then 'Data or spec is not green so make adjustme
nt
> TempValue = iValue * iFactor
> Else ' Take data as is "Green"
> TempValue = iValue
> End If
> statAdjustData = TempValue
> Else 'Non-green or Runin/"Market Rating" report has been requested
> If Not iRunInData Then 'Data or spec is green so make adjustme
nt
> TempValue = iValue / iFactor
> Else 'Take data as is "Runin"
> TempValue = iValue
> End If
> statAdjustData = TempValue
> End If ' Green or Runin/Market data report requested
> Else ' iFactor = 1 or 0 therefore no need to adjust
> statAdjustData = iValue
> End If ' iFactor = To Or <> 1 or 0
> '********Code End*****************|||Thanks for the reply.
I ended up doing this as a function and it seems to work very well and is
fast.
Stvjston
CREATE FUNCTION dbo.StatAdjustData ( @.IVal as FLOAT, @.iFactor as FLOAT,
@.iGreen as BIT, @.iRunnin as bit)
RETURNS FLOAT
BEGIN
DECLARE @.RetVal as FLOAT
IF @.iFactor <> 0 and @.iFactor <> 1
BEGIN
IF @.iGreen = -1
if @.iRunnin = -1
BEGIN
SET @.RetVAL = @.iVal * @.iFactor
END
ELSE
BEGIN
SET @.RetVAl = @.iVal
END
ELSE
IF @.iRunnin <> -1
BEGIN
SET @.RetVal = @.iVal / @.iFactor
END
ELSE
BEGIN
SET @.RetVal = @.iVal
END
END
ELSE
BEGIN
SET @.RETVAL = @.IvAL
END
RETURN (@.RetVal)
END
"MGFoster" wrote:

> --BEGIN PGP SIGNED MESSAGE--
> Hash: SHA1
> Perhaps:
> DECLARE @.True TINYINT, @.False TINYINT
> SET @.True = 1
> Set @.False = 0
> SELECT ... ,
> CASE WHEN iFactor Not In (0,1)
> THEN CASE WHEN iGreenReport = @.True
> THEN CASE WHEN iRunInData = @.True
> THEN iValue * iFactor
> ELSE iValue
> END
> WHEN iGreenReport = @.False
> THEN CASE WHEN iRunInData = @.False
> THEN iValue / iFactor
> ELSE iValue
> END
> END
> ELSE iValue
> END As statAdjustData
> FROM ...
> WHERE ...
> --
> MGFoster:::mgf00 <at> earthlink <decimal-point> net
> Oakland, CA (USA)
> --BEGIN PGP SIGNATURE--
> Version: PGP for Personal Privacy 5.0
> Charset: noconv
> iQA/ AwUBRAzCQoechKqOuFEgEQJEMwCePBFQCl7kq6CW
NHM1LTWmKqgLGf8An29S
> Yk4XGtIPaWOgPNdJC8g+Zz1r
> =ZTUB
> --END PGP SIGNATURE--
>
> StvJston wrote:
2-F71F-425F-AC2B-DC48AB0DA5C9&dglist=&ptlist=&exp=&sloc=en-us
>

Help applying SP2 Beta

Help!!!
After applying SP2 beta to my Test Server I get the following message when
launching the Report Manager:
"The version of the report server database is either in a format that is not
valid, or it cannot be read. The found version is 'Unknown'. The expected
version is 'C.0.6.54'. To continue, update the version of the report server
database and verify access rights. (rsInvalidReportServerDatabase)"
Get Online Help URL:
http://www.microsoft.com/products/ee/transform.aspx?EvtSrc=Microsoft.ReportingServices.Diagnostics.Utilities.ErrorStrings.resources.Strings&EvtID=rsInvalidReportServerDatabase&ProdName=Microsoft+SQL+Server+Reporting+Services&ProdVer=8.00
I tried following the Get Online Help link instructions without success.
I even uninstalled Report Server and re-installed my SP1 version!
Do I need to drop the Report Server Databases before re-installing?
I have back-up of the orignal tables
I need to acces some reports that I had created in the test environment.
--
Application Engineer / DBA
UCLA SOMMake sure that all of your SPs are in the dbo namespace.
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"Marcial" <no_spam@.antispammer.com> wrote in message
news:47A78CC9-CF8F-457B-B45B-91D34EACE1E8@.microsoft.com...
> Help!!!
> After applying SP2 beta to my Test Server I get the following message when
> launching the Report Manager:
> "The version of the report server database is either in a format that is
> not
> valid, or it cannot be read. The found version is 'Unknown'. The expected
> version is 'C.0.6.54'. To continue, update the version of the report
> server
> database and verify access rights. (rsInvalidReportServerDatabase)"
> Get Online Help URL:
> http://www.microsoft.com/products/ee/transform.aspx?EvtSrc=Microsoft.ReportingServices.Diagnostics.Utilities.ErrorStrings.resources.Strings&EvtID=rsInvalidReportServerDatabase&ProdName=Microsoft+SQL+Server+Reporting+Services&ProdVer=8.00
> I tried following the Get Online Help link instructions without success.
> I even uninstalled Report Server and re-installed my SP1 version!
> Do I need to drop the Report Server Databases before re-installing?
> I have back-up of the orignal tables
> I need to acces some reports that I had created in the test environment.
> --
> Application Engineer / DBA
> UCLA SOM|||Thanks Daniel, could you be a bit morer specific on how to ensure SPs in dbo
namespace!
"Daniel Reib [MSFT]" wrote:
> Make sure that all of your SPs are in the dbo namespace.
> --
> -Daniel
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Marcial" <no_spam@.antispammer.com> wrote in message
> news:47A78CC9-CF8F-457B-B45B-91D34EACE1E8@.microsoft.com...
> > Help!!!
> >
> > After applying SP2 beta to my Test Server I get the following message when
> > launching the Report Manager:
> >
> > "The version of the report server database is either in a format that is
> > not
> > valid, or it cannot be read. The found version is 'Unknown'. The expected
> > version is 'C.0.6.54'. To continue, update the version of the report
> > server
> > database and verify access rights. (rsInvalidReportServerDatabase)"
> >
> > Get Online Help URL:
> >
> > http://www.microsoft.com/products/ee/transform.aspx?EvtSrc=Microsoft.ReportingServices.Diagnostics.Utilities.ErrorStrings.resources.Strings&EvtID=rsInvalidReportServerDatabase&ProdName=Microsoft+SQL+Server+Reporting+Services&ProdVer=8.00
> >
> > I tried following the Get Online Help link instructions without success.
> >
> > I even uninstalled Report Server and re-installed my SP1 version!
> > Do I need to drop the Report Server Databases before re-installing?
> >
> > I have back-up of the orignal tables
> >
> > I need to acces some reports that I had created in the test environment.
> >
> > --
> > Application Engineer / DBA
> > UCLA SOM
>
>|||If you go to enterprise manager, find the reportserver database and look at
it's SPs. Who is the owner of the SPs?
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"Marcial" <no_spam@.antispammer.com> wrote in message
news:2759A2F2-7BE6-4C43-9745-3ADCEE9B4AF8@.microsoft.com...
> Thanks Daniel, could you be a bit morer specific on how to ensure SPs in
> dbo
> namespace!
> "Daniel Reib [MSFT]" wrote:
>> Make sure that all of your SPs are in the dbo namespace.
>> --
>> -Daniel
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>>
>> "Marcial" <no_spam@.antispammer.com> wrote in message
>> news:47A78CC9-CF8F-457B-B45B-91D34EACE1E8@.microsoft.com...
>> > Help!!!
>> >
>> > After applying SP2 beta to my Test Server I get the following message
>> > when
>> > launching the Report Manager:
>> >
>> > "The version of the report server database is either in a format that
>> > is
>> > not
>> > valid, or it cannot be read. The found version is 'Unknown'. The
>> > expected
>> > version is 'C.0.6.54'. To continue, update the version of the report
>> > server
>> > database and verify access rights. (rsInvalidReportServerDatabase)"
>> >
>> > Get Online Help URL:
>> >
>> > http://www.microsoft.com/products/ee/transform.aspx?EvtSrc=Microsoft.ReportingServices.Diagnostics.Utilities.ErrorStrings.resources.Strings&EvtID=rsInvalidReportServerDatabase&ProdName=Microsoft+SQL+Server+Reporting+Services&ProdVer=8.00
>> >
>> > I tried following the Get Online Help link instructions without
>> > success.
>> >
>> > I even uninstalled Report Server and re-installed my SP1 version!
>> > Do I need to drop the Report Server Databases before re-installing?
>> >
>> > I have back-up of the orignal tables
>> >
>> > I need to acces some reports that I had created in the test
>> > environment.
>> >
>> > --
>> > Application Engineer / DBA
>> > UCLA SOM
>>|||Ok... I have verified that the dbo is the owner or the Stored Procedures in
both the
ReportServer and ReportServerTembDB. There is also an RSExecute Role which
currently contains no users.
could this be the cause of the error?
I also need to metion that I had installed SQL SERVER SP4 beta.
Could this be cause problems with Reporting Services'
"Daniel Reib [MSFT]" wrote:
> If you go to enterprise manager, find the reportserver database and look at
> it's SPs. Who is the owner of the SPs?
> --
> -Daniel
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Marcial" <no_spam@.antispammer.com> wrote in message
> news:2759A2F2-7BE6-4C43-9745-3ADCEE9B4AF8@.microsoft.com...
> > Thanks Daniel, could you be a bit morer specific on how to ensure SPs in
> > dbo
> > namespace!
> >
> > "Daniel Reib [MSFT]" wrote:
> >
> >> Make sure that all of your SPs are in the dbo namespace.
> >>
> >> --
> >> -Daniel
> >> This posting is provided "AS IS" with no warranties, and confers no
> >> rights.
> >>
> >>
> >> "Marcial" <no_spam@.antispammer.com> wrote in message
> >> news:47A78CC9-CF8F-457B-B45B-91D34EACE1E8@.microsoft.com...
> >> > Help!!!
> >> >
> >> > After applying SP2 beta to my Test Server I get the following message
> >> > when
> >> > launching the Report Manager:
> >> >
> >> > "The version of the report server database is either in a format that
> >> > is
> >> > not
> >> > valid, or it cannot be read. The found version is 'Unknown'. The
> >> > expected
> >> > version is 'C.0.6.54'. To continue, update the version of the report
> >> > server
> >> > database and verify access rights. (rsInvalidReportServerDatabase)"
> >> >
> >> > Get Online Help URL:
> >> >
> >> > http://www.microsoft.com/products/ee/transform.aspx?EvtSrc=Microsoft.ReportingServices.Diagnostics.Utilities.ErrorStrings.resources.Strings&EvtID=rsInvalidReportServerDatabase&ProdName=Microsoft+SQL+Server+Reporting+Services&ProdVer=8.00
> >> >
> >> > I tried following the Get Online Help link instructions without
> >> > success.
> >> >
> >> > I even uninstalled Report Server and re-installed my SP1 version!
> >> > Do I need to drop the Report Server Databases before re-installing?
> >> >
> >> > I have back-up of the orignal tables
> >> >
> >> > I need to acces some reports that I had created in the test
> >> > environment.
> >> >
> >> > --
> >> > Application Engineer / DBA
> >> > UCLA SOM
> >>
> >>
> >>
>
>|||Do you know what user you use to connect to the database server? You may
want to run rsconfig.exe to set the user to a specific user. You would then
need to manually add that user to each RSExecRole. There are four roles, on
in each of the following DB:
ReportServer
ReportServerTempDB
Master
Msdb
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"Marcial" <no_spam@.antispammer.com> wrote in message
news:BC3682DA-89F6-41F4-9B29-98E63EC203D1@.microsoft.com...
> Ok... I have verified that the dbo is the owner or the Stored Procedures
> in
> both the
> ReportServer and ReportServerTembDB. There is also an RSExecute Role which
> currently contains no users.
> could this be the cause of the error?
> I also need to metion that I had installed SQL SERVER SP4 beta.
> Could this be cause problems with Reporting Services'
> "Daniel Reib [MSFT]" wrote:
>> If you go to enterprise manager, find the reportserver database and look
>> at
>> it's SPs. Who is the owner of the SPs?
>> --
>> -Daniel
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>>
>> "Marcial" <no_spam@.antispammer.com> wrote in message
>> news:2759A2F2-7BE6-4C43-9745-3ADCEE9B4AF8@.microsoft.com...
>> > Thanks Daniel, could you be a bit morer specific on how to ensure SPs
>> > in
>> > dbo
>> > namespace!
>> >
>> > "Daniel Reib [MSFT]" wrote:
>> >
>> >> Make sure that all of your SPs are in the dbo namespace.
>> >>
>> >> --
>> >> -Daniel
>> >> This posting is provided "AS IS" with no warranties, and confers no
>> >> rights.
>> >>
>> >>
>> >> "Marcial" <no_spam@.antispammer.com> wrote in message
>> >> news:47A78CC9-CF8F-457B-B45B-91D34EACE1E8@.microsoft.com...
>> >> > Help!!!
>> >> >
>> >> > After applying SP2 beta to my Test Server I get the following
>> >> > message
>> >> > when
>> >> > launching the Report Manager:
>> >> >
>> >> > "The version of the report server database is either in a format
>> >> > that
>> >> > is
>> >> > not
>> >> > valid, or it cannot be read. The found version is 'Unknown'. The
>> >> > expected
>> >> > version is 'C.0.6.54'. To continue, update the version of the report
>> >> > server
>> >> > database and verify access rights. (rsInvalidReportServerDatabase)"
>> >> >
>> >> > Get Online Help URL:
>> >> >
>> >> > http://www.microsoft.com/products/ee/transform.aspx?EvtSrc=Microsoft.ReportingServices.Diagnostics.Utilities.ErrorStrings.resources.Strings&EvtID=rsInvalidReportServerDatabase&ProdName=Microsoft+SQL+Server+Reporting+Services&ProdVer=8.00
>> >> >
>> >> > I tried following the Get Online Help link instructions without
>> >> > success.
>> >> >
>> >> > I even uninstalled Report Server and re-installed my SP1 version!
>> >> > Do I need to drop the Report Server Databases before re-installing?
>> >> >
>> >> > I have back-up of the orignal tables
>> >> >
>> >> > I need to acces some reports that I had created in the test
>> >> > environment.
>> >> >
>> >> > --
>> >> > Application Engineer / DBA
>> >> > UCLA SOM
>> >>
>> >>
>> >>
>>|||May I jump in here? I am having a similar problem, but my error originally
differed slightly.
Instead of "found version 'Unknown'", mine reported "found version
'T.0.6.51'. Cause apparently
due to me using RSCONFIG to try to change the credentials used to connect to
the RS SQL
Server databases. Commands I issued:
rsconfig -c -s KCBSGP12 -d ReportServer2000 -a sql -u RSAdmin -p xxxxxx
rsconfig -c -s KCBSGP12 -d ReportServer2000TempDB -a sql -u RSAdmin -p xxxxxx
RSAdmin was a created as user in ReportServer2000 and ReportServer2000TempDB
with same
permissions as the original account (an AD account versus SQL account, in
case that's important).
Restarted ReportServer service and got the error. Then realized from this
thread I had missed
MASTER and MSDB databases, so added RSAdmin user to those as well, then
issued "rsconfig"
commands as above for those two databases. NOW, the "found version" is
"Unknown", just
as Marcial is getting.
Hoping very much you can help! Thank you.
"Daniel Reib [MSFT]" wrote:
> Do you know what user you use to connect to the database server? You may
> want to run rsconfig.exe to set the user to a specific user. You would then
> need to manually add that user to each RSExecRole. There are four roles, on
> in each of the following DB:
> ReportServer
> ReportServerTempDB
> Master
> Msdb
> --
> -Daniel
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Marcial" <no_spam@.antispammer.com> wrote in message
> news:BC3682DA-89F6-41F4-9B29-98E63EC203D1@.microsoft.com...
> > Ok... I have verified that the dbo is the owner or the Stored Procedures
> > in
> > both the
> > ReportServer and ReportServerTembDB. There is also an RSExecute Role which
> > currently contains no users.
> > could this be the cause of the error?
> >
> > I also need to metion that I had installed SQL SERVER SP4 beta.
> > Could this be cause problems with Reporting Services'
> >
> > "Daniel Reib [MSFT]" wrote:
> >
> >> If you go to enterprise manager, find the reportserver database and look
> >> at
> >> it's SPs. Who is the owner of the SPs?
> >>
> >> --
> >> -Daniel
> >> This posting is provided "AS IS" with no warranties, and confers no
> >> rights.
> >>
> >>
> >> "Marcial" <no_spam@.antispammer.com> wrote in message
> >> news:2759A2F2-7BE6-4C43-9745-3ADCEE9B4AF8@.microsoft.com...
> >> > Thanks Daniel, could you be a bit morer specific on how to ensure SPs
> >> > in
> >> > dbo
> >> > namespace!
> >> >
> >> > "Daniel Reib [MSFT]" wrote:
> >> >
> >> >> Make sure that all of your SPs are in the dbo namespace.
> >> >>
> >> >> --
> >> >> -Daniel
> >> >> This posting is provided "AS IS" with no warranties, and confers no
> >> >> rights.
> >> >>
> >> >>
> >> >> "Marcial" <no_spam@.antispammer.com> wrote in message
> >> >> news:47A78CC9-CF8F-457B-B45B-91D34EACE1E8@.microsoft.com...
> >> >> > Help!!!
> >> >> >
> >> >> > After applying SP2 beta to my Test Server I get the following
> >> >> > message
> >> >> > when
> >> >> > launching the Report Manager:
> >> >> >
> >> >> > "The version of the report server database is either in a format
> >> >> > that
> >> >> > is
> >> >> > not
> >> >> > valid, or it cannot be read. The found version is 'Unknown'. The
> >> >> > expected
> >> >> > version is 'C.0.6.54'. To continue, update the version of the report
> >> >> > server
> >> >> > database and verify access rights. (rsInvalidReportServerDatabase)"
> >> >> >
> >> >> > Get Online Help URL:
> >> >> >
> >> >> > http://www.microsoft.com/products/ee/transform.aspx?EvtSrc=Microsoft.ReportingServices.Diagnostics.Utilities.ErrorStrings.resources.Strings&EvtID=rsInvalidReportServerDatabase&ProdName=Microsoft+SQL+Server+Reporting+Services&ProdVer=8.00
> >> >> >
> >> >> > I tried following the Get Online Help link instructions without
> >> >> > success.
> >> >> >
> >> >> > I even uninstalled Report Server and re-installed my SP1 version!
> >> >> > Do I need to drop the Report Server Databases before re-installing?
> >> >> >
> >> >> > I have back-up of the orignal tables
> >> >> >
> >> >> > I need to acces some reports that I had created in the test
> >> >> > environment.
> >> >> >
> >> >> > --
> >> >> > Application Engineer / DBA
> >> >> > UCLA SOM
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>|||You only want to issue rsconfig for the report server database, not for the
temp or any other. RSConfig.exe does NOT update the RSExecRole that is
present in the 4 databases that you mention, you must do this manually.
So, rerun rsconfig.exe, pointing it to ReportServer2000. Then manually add
RSAdmin to the rsexecrole in the 4 databases mentioned below.
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"J Noble" <JNoble@.discussions.microsoft.com> wrote in message
news:FB973338-95DA-477D-ADB1-4CD33FF84582@.microsoft.com...
> May I jump in here? I am having a similar problem, but my error
> originally
> differed slightly.
> Instead of "found version 'Unknown'", mine reported "found version
> 'T.0.6.51'. Cause apparently
> due to me using RSCONFIG to try to change the credentials used to connect
> to
> the RS SQL
> Server databases. Commands I issued:
> rsconfig -c -s KCBSGP12 -d ReportServer2000 -a sql -u RSAdmin -p xxxxxx
> rsconfig -c -s KCBSGP12 -d ReportServer2000TempDB -a sql -u RSAdmin -p
> xxxxxx
> RSAdmin was a created as user in ReportServer2000 and
> ReportServer2000TempDB
> with same
> permissions as the original account (an AD account versus SQL account, in
> case that's important).
> Restarted ReportServer service and got the error. Then realized from this
> thread I had missed
> MASTER and MSDB databases, so added RSAdmin user to those as well, then
> issued "rsconfig"
> commands as above for those two databases. NOW, the "found version" is
> "Unknown", just
> as Marcial is getting.
> Hoping very much you can help! Thank you.
>
> "Daniel Reib [MSFT]" wrote:
>> Do you know what user you use to connect to the database server? You may
>> want to run rsconfig.exe to set the user to a specific user. You would
>> then
>> need to manually add that user to each RSExecRole. There are four roles,
>> on
>> in each of the following DB:
>> ReportServer
>> ReportServerTempDB
>> Master
>> Msdb
>> --
>> -Daniel
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>>
>> "Marcial" <no_spam@.antispammer.com> wrote in message
>> news:BC3682DA-89F6-41F4-9B29-98E63EC203D1@.microsoft.com...
>> > Ok... I have verified that the dbo is the owner or the Stored
>> > Procedures
>> > in
>> > both the
>> > ReportServer and ReportServerTembDB. There is also an RSExecute Role
>> > which
>> > currently contains no users.
>> > could this be the cause of the error?
>> >
>> > I also need to metion that I had installed SQL SERVER SP4 beta.
>> > Could this be cause problems with Reporting Services'
>> >
>> > "Daniel Reib [MSFT]" wrote:
>> >
>> >> If you go to enterprise manager, find the reportserver database and
>> >> look
>> >> at
>> >> it's SPs. Who is the owner of the SPs?
>> >>
>> >> --
>> >> -Daniel
>> >> This posting is provided "AS IS" with no warranties, and confers no
>> >> rights.
>> >>
>> >>
>> >> "Marcial" <no_spam@.antispammer.com> wrote in message
>> >> news:2759A2F2-7BE6-4C43-9745-3ADCEE9B4AF8@.microsoft.com...
>> >> > Thanks Daniel, could you be a bit morer specific on how to ensure
>> >> > SPs
>> >> > in
>> >> > dbo
>> >> > namespace!
>> >> >
>> >> > "Daniel Reib [MSFT]" wrote:
>> >> >
>> >> >> Make sure that all of your SPs are in the dbo namespace.
>> >> >>
>> >> >> --
>> >> >> -Daniel
>> >> >> This posting is provided "AS IS" with no warranties, and confers no
>> >> >> rights.
>> >> >>
>> >> >>
>> >> >> "Marcial" <no_spam@.antispammer.com> wrote in message
>> >> >> news:47A78CC9-CF8F-457B-B45B-91D34EACE1E8@.microsoft.com...
>> >> >> > Help!!!
>> >> >> >
>> >> >> > After applying SP2 beta to my Test Server I get the following
>> >> >> > message
>> >> >> > when
>> >> >> > launching the Report Manager:
>> >> >> >
>> >> >> > "The version of the report server database is either in a format
>> >> >> > that
>> >> >> > is
>> >> >> > not
>> >> >> > valid, or it cannot be read. The found version is 'Unknown'. The
>> >> >> > expected
>> >> >> > version is 'C.0.6.54'. To continue, update the version of the
>> >> >> > report
>> >> >> > server
>> >> >> > database and verify access rights.
>> >> >> > (rsInvalidReportServerDatabase)"
>> >> >> >
>> >> >> > Get Online Help URL:
>> >> >> >
>> >> >> > http://www.microsoft.com/products/ee/transform.aspx?EvtSrc=Microsoft.ReportingServices.Diagnostics.Utilities.ErrorStrings.resources.Strings&EvtID=rsInvalidReportServerDatabase&ProdName=Microsoft+SQL+Server+Reporting+Services&ProdVer=8.00
>> >> >> >
>> >> >> > I tried following the Get Online Help link instructions without
>> >> >> > success.
>> >> >> >
>> >> >> > I even uninstalled Report Server and re-installed my SP1 version!
>> >> >> > Do I need to drop the Report Server Databases before
>> >> >> > re-installing?
>> >> >> >
>> >> >> > I have back-up of the orignal tables
>> >> >> >
>> >> >> > I need to acces some reports that I had created in the test
>> >> >> > environment.
>> >> >> >
>> >> >> > --
>> >> >> > Application Engineer / DBA
>> >> >> > UCLA SOM
>> >> >>
>> >> >>
>> >> >>
>> >>
>> >>
>> >>
>>|||Fantastic. Thanks very much.
Bad assumption on my part to initially run RSConfig against both db's. I see
some logic in the reported versions now, though. 'C' being the main db,
'T' being the Temp db, and 'Unknown' referring to Master and Msdb.
"Daniel Reib [MSFT]" wrote:
> You only want to issue rsconfig for the report server database, not for the
> temp or any other. RSConfig.exe does NOT update the RSExecRole that is
> present in the 4 databases that you mention, you must do this manually.
> So, rerun rsconfig.exe, pointing it to ReportServer2000. Then manually add
> RSAdmin to the rsexecrole in the 4 databases mentioned below.
> --
> -Daniel
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "J Noble" <JNoble@.discussions.microsoft.com> wrote in message
> news:FB973338-95DA-477D-ADB1-4CD33FF84582@.microsoft.com...
> > May I jump in here? I am having a similar problem, but my error
> > originally
> > differed slightly.
> > Instead of "found version 'Unknown'", mine reported "found version
> > 'T.0.6.51'. Cause apparently
> > due to me using RSCONFIG to try to change the credentials used to connect
> > to
> > the RS SQL
> > Server databases. Commands I issued:
> > rsconfig -c -s KCBSGP12 -d ReportServer2000 -a sql -u RSAdmin -p xxxxxx
> > rsconfig -c -s KCBSGP12 -d ReportServer2000TempDB -a sql -u RSAdmin -p
> > xxxxxx
> > RSAdmin was a created as user in ReportServer2000 and
> > ReportServer2000TempDB
> > with same
> > permissions as the original account (an AD account versus SQL account, in
> > case that's important).
> > Restarted ReportServer service and got the error. Then realized from this
> > thread I had missed
> > MASTER and MSDB databases, so added RSAdmin user to those as well, then
> > issued "rsconfig"
> > commands as above for those two databases. NOW, the "found version" is
> > "Unknown", just
> > as Marcial is getting.
> >
> > Hoping very much you can help! Thank you.
> >
> >
> > "Daniel Reib [MSFT]" wrote:
> >
> >> Do you know what user you use to connect to the database server? You may
> >> want to run rsconfig.exe to set the user to a specific user. You would
> >> then
> >> need to manually add that user to each RSExecRole. There are four roles,
> >> on
> >> in each of the following DB:
> >>
> >> ReportServer
> >> ReportServerTempDB
> >> Master
> >> Msdb
> >>
> >> --
> >> -Daniel
> >> This posting is provided "AS IS" with no warranties, and confers no
> >> rights.
> >>
> >>
> >> "Marcial" <no_spam@.antispammer.com> wrote in message
> >> news:BC3682DA-89F6-41F4-9B29-98E63EC203D1@.microsoft.com...
> >> > Ok... I have verified that the dbo is the owner or the Stored
> >> > Procedures
> >> > in
> >> > both the
> >> > ReportServer and ReportServerTembDB. There is also an RSExecute Role
> >> > which
> >> > currently contains no users.
> >> > could this be the cause of the error?
> >> >
> >> > I also need to metion that I had installed SQL SERVER SP4 beta.
> >> > Could this be cause problems with Reporting Services'
> >> >
> >> > "Daniel Reib [MSFT]" wrote:
> >> >
> >> >> If you go to enterprise manager, find the reportserver database and
> >> >> look
> >> >> at
> >> >> it's SPs. Who is the owner of the SPs?
> >> >>
> >> >> --
> >> >> -Daniel
> >> >> This posting is provided "AS IS" with no warranties, and confers no
> >> >> rights.
> >> >>
> >> >>
> >> >> "Marcial" <no_spam@.antispammer.com> wrote in message
> >> >> news:2759A2F2-7BE6-4C43-9745-3ADCEE9B4AF8@.microsoft.com...
> >> >> > Thanks Daniel, could you be a bit morer specific on how to ensure
> >> >> > SPs
> >> >> > in
> >> >> > dbo
> >> >> > namespace!
> >> >> >
> >> >> > "Daniel Reib [MSFT]" wrote:
> >> >> >
> >> >> >> Make sure that all of your SPs are in the dbo namespace.
> >> >> >>
> >> >> >> --
> >> >> >> -Daniel
> >> >> >> This posting is provided "AS IS" with no warranties, and confers no
> >> >> >> rights.
> >> >> >>
> >> >> >>
> >> >> >> "Marcial" <no_spam@.antispammer.com> wrote in message
> >> >> >> news:47A78CC9-CF8F-457B-B45B-91D34EACE1E8@.microsoft.com...
> >> >> >> > Help!!!
> >> >> >> >
> >> >> >> > After applying SP2 beta to my Test Server I get the following
> >> >> >> > message
> >> >> >> > when
> >> >> >> > launching the Report Manager:
> >> >> >> >
> >> >> >> > "The version of the report server database is either in a format
> >> >> >> > that
> >> >> >> > is
> >> >> >> > not
> >> >> >> > valid, or it cannot be read. The found version is 'Unknown'. The
> >> >> >> > expected
> >> >> >> > version is 'C.0.6.54'. To continue, update the version of the
> >> >> >> > report
> >> >> >> > server
> >> >> >> > database and verify access rights.
> >> >> >> > (rsInvalidReportServerDatabase)"
> >> >> >> >
> >> >> >> > Get Online Help URL:
> >> >> >> >
> >> >> >> > http://www.microsoft.com/products/ee/transform.aspx?EvtSrc=Microsoft.ReportingServices.Diagnostics.Utilities.ErrorStrings.resources.Strings&EvtID=rsInvalidReportServerDatabase&ProdName=Microsoft+SQL+Server+Reporting+Services&ProdVer=8.00
> >> >> >> >
> >> >> >> > I tried following the Get Online Help link instructions without
> >> >> >> > success.
> >> >> >> >
> >> >> >> > I even uninstalled Report Server and re-installed my SP1 version!
> >> >> >> > Do I need to drop the Report Server Databases before
> >> >> >> > re-installing?
> >> >> >> >
> >> >> >> > I have back-up of the orignal tables
> >> >> >> >
> >> >> >> > I need to acces some reports that I had created in the test
> >> >> >> > environment.
> >> >> >> >
> >> >> >> > --
> >> >> >> > Application Engineer / DBA
> >> >> >> > UCLA SOM
> >> >> >>
> >> >> >>
> >> >> >>
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>sql

HELP !! HELP: Trigger to send REPORT !!!

Hi all,

Report Manager is failing to email out the reports !!!

And... I can not use Report Manager to configure the trigger !

How can I trigger manually to have SQL 2005 email out the reports again?

OR how can I trigger to run those emailing jobs again ?

Thanks,

Frank

I don't really understand the question. How are the subscriptions configured? (Timed or Snapshot?)

If they are configured to fire on a snapshot then you just need to update the report execution snapshot. If they are on a schedule, you could fire the SQL Agent job manually or call FireEvent passing in TimedSubscription and the subscriptionId.

Does that answer your question?

|||

Hi,

Yes, they are "TimedSubscription".

Do you mean to run one of the jobs under "SQL Server Agent"?

and how do I call "FireEvent" passing in TimedSubscription or the subscriptionId?

Thanks,

Frank

|||To trigger an subscription you could make your own small app which uses:
http://msdn2.microsoft.com/en-us/library/microsoft.wssux.reportingserviceswebservice.rsmanagementservice2005.reportingservice2005.fireevent.aspx|||Yes, I meant you could use Sql Server Agent or do as BenniG. Suggested and have an app call the FireEvent method|||

Ok... let's say I want to use SQL Server Agent to run the job that can trigger the Report Manager to email out reports, do you guys have any documents that can show me how to do so?

I checked and ran some jobs under the SQL Server Agent, I don't see anyone of the jobs that were created to email out the reports, if there were no jobs created to do such task, how did those subscriptions email out before they started failing one by one?

Thanks,

Frank

|||

All report server jobs are named as Guids in SQL agent. Every job does essentially the same thing, which is add an event to the Report Server queue. You would need to match up which job created the correct event. If the subscription is to a shared schedule, the schedule ID will be the same as the job name, otherwise the only way to figure it out is find the schedule Id via the RS tables or trial and error.

However the recommened way is to call the FireEvent method.

HELP ! Carried Foward / Brought Forward

In my detail section I would like to have a report that has a "Carried
Forward" amount as a total for the page and then on the next page header the
value as "Brought Foward" this will total up at the page footer and be
"Carried Forward" to next page and so on until the last page where "Carried
Foward" wouldn't be shown but "Brought Foward" would.
Does anyone have any expamples as this a pretty common requirement for
finacial reports / billing. I have been racking my brains over this one but
can't get it to work.
Regards
Toby.You can get the Carried Forward by doing an aggregate of report items in the
page footer (=Sum(ReportItems!SalesTextbox.Value))
However, there's currently no way to get the Brought Forward since the
Previous function is not yet supported in the page header/footer.
--
This post is provided 'AS IS' with no warranties, and confers no rights. All
rights reserved. Some assembly required. Batteries not included. Your
mileage may vary. Objects in mirror may be closer than they appear. No user
serviceable parts inside. Opening cover voids warranty. Keep out of reach of
children under 3.
"Toby" <toby.maillist@.exmlsystems.com> wrote in message
news:ufJIaisjEHA.808@.TK2MSFTNGP12.phx.gbl...
> In my detail section I would like to have a report that has a "Carried
> Forward" amount as a total for the page and then on the next page header
the
> value as "Brought Foward" this will total up at the page footer and be
> "Carried Forward" to next page and so on until the last page where
"Carried
> Foward" wouldn't be shown but "Brought Foward" would.
> Does anyone have any expamples as this a pretty common requirement for
> finacial reports / billing. I have been racking my brains over this one
but
> can't get it to work.
> Regards
> Toby.
>|||Could I write a function / code behind to implement this ? This is kind of a
show stopper.
Regards
Toby.
"Chris Hays [MSFT]" <chays@.online.microsoft.com> wrote in message
news:%23VucRJtjEHA.632@.TK2MSFTNGP12.phx.gbl...
> You can get the Carried Forward by doing an aggregate of report items in
the
> page footer (=Sum(ReportItems!SalesTextbox.Value))
> However, there's currently no way to get the Brought Forward since the
> Previous function is not yet supported in the page header/footer.
> --
> This post is provided 'AS IS' with no warranties, and confers no rights.
All
> rights reserved. Some assembly required. Batteries not included. Your
> mileage may vary. Objects in mirror may be closer than they appear. No
user
> serviceable parts inside. Opening cover voids warranty. Keep out of reach
of
> children under 3.
> "Toby" <toby.maillist@.exmlsystems.com> wrote in message
> news:ufJIaisjEHA.808@.TK2MSFTNGP12.phx.gbl...
> > In my detail section I would like to have a report that has a "Carried
> > Forward" amount as a total for the page and then on the next page header
> the
> > value as "Brought Foward" this will total up at the page footer and be
> > "Carried Forward" to next page and so on until the last page where
> "Carried
> > Foward" wouldn't be shown but "Brought Foward" would.
> >
> > Does anyone have any expamples as this a pretty common requirement for
> > finacial reports / billing. I have been racking my brains over this one
> but
> > can't get it to work.
> >
> > Regards
> >
> > Toby.
> >
> >
>|||You could store the current value in a shared member variable and use the
previous value by doing something like this:
public shared Previous as integer
public function Carry(value as integer)
Previous = value
Carry = value
end function
Textbox in page footer: =Code.Carry(Sum(ReportItems!textbox7.Value))
Textbox in page header: =Code.Previous
Since the custom code/classes get instantiated separately for each page
request, you'd have to do this as a shared member variable.
And due to the shared member variable, if two people run the report at the
same time, it would smash the Previous counter.
But if you're really motivated, you could make the Previous shared member
variable a hash table based on user id, at which point you'd only have
problems if the same user ran the report more than once at the same time.
Textbox in page footer: =Code.SetCarry(Sum(ReportItems!textbox7.Value),
User.UserID)
Textbox in page header: =Code.GetCarry(User.UserID)
This post is provided 'AS IS' with no warranties, and confers no rights. All
rights reserved. Some assembly required. Batteries not included. Your
mileage may vary. Objects in mirror may be closer than they appear. No user
serviceable parts inside. Opening cover voids warranty. Keep out of reach of
children under 3.
"Toby" <toby.maillist@.exmlsystems.com> wrote in message
news:ubruZitjEHA.4068@.TK2MSFTNGP10.phx.gbl...
> Could I write a function / code behind to implement this ? This is kind of
a
> show stopper.
> Regards
> Toby.
> "Chris Hays [MSFT]" <chays@.online.microsoft.com> wrote in message
> news:%23VucRJtjEHA.632@.TK2MSFTNGP12.phx.gbl...
> > You can get the Carried Forward by doing an aggregate of report items in
> the
> > page footer (=Sum(ReportItems!SalesTextbox.Value))
> > However, there's currently no way to get the Brought Forward since the
> > Previous function is not yet supported in the page header/footer.
> >
> > --
> > This post is provided 'AS IS' with no warranties, and confers no rights.
> All
> > rights reserved. Some assembly required. Batteries not included. Your
> > mileage may vary. Objects in mirror may be closer than they appear. No
> user
> > serviceable parts inside. Opening cover voids warranty. Keep out of
reach
> of
> > children under 3.
> > "Toby" <toby.maillist@.exmlsystems.com> wrote in message
> > news:ufJIaisjEHA.808@.TK2MSFTNGP12.phx.gbl...
> > > In my detail section I would like to have a report that has a "Carried
> > > Forward" amount as a total for the page and then on the next page
header
> > the
> > > value as "Brought Foward" this will total up at the page footer and be
> > > "Carried Forward" to next page and so on until the last page where
> > "Carried
> > > Foward" wouldn't be shown but "Brought Foward" would.
> > >
> > > Does anyone have any expamples as this a pretty common requirement for
> > > finacial reports / billing. I have been racking my brains over this
one
> > but
> > > can't get it to work.
> > >
> > > Regards
> > >
> > > Toby.
> > >
> > >
> >
> >
>|||Feeling motivated, Thanks
Toby.
"Chris Hays [MSFT]" <chays@.online.microsoft.com> wrote in message
news:ueqKY3tjEHA.556@.tk2msftngp13.phx.gbl...
> You could store the current value in a shared member variable and use the
> previous value by doing something like this:
> public shared Previous as integer
> public function Carry(value as integer)
> Previous = value
> Carry = value
> end function
> Textbox in page footer: =Code.Carry(Sum(ReportItems!textbox7.Value))
> Textbox in page header: =Code.Previous
> Since the custom code/classes get instantiated separately for each page
> request, you'd have to do this as a shared member variable.
> And due to the shared member variable, if two people run the report at the
> same time, it would smash the Previous counter.
> But if you're really motivated, you could make the Previous shared member
> variable a hash table based on user id, at which point you'd only have
> problems if the same user ran the report more than once at the same time.
> Textbox in page footer: =Code.SetCarry(Sum(ReportItems!textbox7.Value),
> User.UserID)
> Textbox in page header: =Code.GetCarry(User.UserID)
>
> --
> This post is provided 'AS IS' with no warranties, and confers no rights.
All
> rights reserved. Some assembly required. Batteries not included. Your
> mileage may vary. Objects in mirror may be closer than they appear. No
user
> serviceable parts inside. Opening cover voids warranty. Keep out of reach
of
> children under 3.
> "Toby" <toby.maillist@.exmlsystems.com> wrote in message
> news:ubruZitjEHA.4068@.TK2MSFTNGP10.phx.gbl...
> > Could I write a function / code behind to implement this ? This is kind
of
> a
> > show stopper.
> >
> > Regards
> >
> > Toby.
> >
> > "Chris Hays [MSFT]" <chays@.online.microsoft.com> wrote in message
> > news:%23VucRJtjEHA.632@.TK2MSFTNGP12.phx.gbl...
> > > You can get the Carried Forward by doing an aggregate of report items
in
> > the
> > > page footer (=Sum(ReportItems!SalesTextbox.Value))
> > > However, there's currently no way to get the Brought Forward since the
> > > Previous function is not yet supported in the page header/footer.
> > >
> > > --
> > > This post is provided 'AS IS' with no warranties, and confers no
rights.
> > All
> > > rights reserved. Some assembly required. Batteries not included. Your
> > > mileage may vary. Objects in mirror may be closer than they appear. No
> > user
> > > serviceable parts inside. Opening cover voids warranty. Keep out of
> reach
> > of
> > > children under 3.
> > > "Toby" <toby.maillist@.exmlsystems.com> wrote in message
> > > news:ufJIaisjEHA.808@.TK2MSFTNGP12.phx.gbl...
> > > > In my detail section I would like to have a report that has a
"Carried
> > > > Forward" amount as a total for the page and then on the next page
> header
> > > the
> > > > value as "Brought Foward" this will total up at the page footer and
be
> > > > "Carried Forward" to next page and so on until the last page where
> > > "Carried
> > > > Foward" wouldn't be shown but "Brought Foward" would.
> > > >
> > > > Does anyone have any expamples as this a pretty common requirement
for
> > > > finacial reports / billing. I have been racking my brains over this
> one
> > > but
> > > > can't get it to work.
> > > >
> > > > Regards
> > > >
> > > > Toby.
> > > >
> > > >
> > >
> > >
> >
> >
>

Help - Unable to Add Column to Report

I have inherited a report that appears to be too big for the VS designer.
The query is 664 lines long and every time I move to the Data tab, Reporting
Services locks up and stops responding. (I am using the generic query
designer)
The report runs fine, my problem is I need to add another data column to the
report. I did this by editing the RDL in a text editor but when I reload in
RS and got to the Layout tab to add the column to the report, it is not
listed in the Fields list. I tried to manually add it but I then get the
'Index out of bounds error'. So it seems the field list needs to be updated
by accessing the Data tab and then moving to the Layout or Preview tab but
every time I try to do this it locks up.
I thought I might try to temporarily remove part of the query so the field
list could be updated but the report has the columns scattered throughout so
I can't locate a large enough block of contiguous query data that does NOT
affect the fields list.
Is there any work around for this?
Many thanksHi Mike,
I'm not sure what the preview issue is, but if you are ok with editing the
RDL, you should be able to do it.
When you added the field to the RDL, did you add it to both the query and to
the fields collection?
The fields collection maps results returned from the query to fields you can
put on the report. If you added a column to the query in the RDL, makes
sure you add a field element (under datasets/fields) that matches the column
name. If you did both of those and still have the problem then I don't
know.
I hope that helps!
Chris
"Mike Harbinger" <MikeH@.Cybervillage.net> wrote in message
news:OO$4Noz3FHA.4080@.tk2msftngp13.phx.gbl...
>I have inherited a report that appears to be too big for the VS designer.
>The query is 664 lines long and every time I move to the Data tab,
>Reporting Services locks up and stops responding. (I am using the generic
>query designer)
> The report runs fine, my problem is I need to add another data column to
> the report. I did this by editing the RDL in a text editor but when I
> reload in RS and got to the Layout tab to add the column to the report, it
> is not listed in the Fields list. I tried to manually add it but I then
> get the 'Index out of bounds error'. So it seems the field list needs to
> be updated by accessing the Data tab and then moving to the Layout or
> Preview tab but every time I try to do this it locks up.
> I thought I might try to temporarily remove part of the query so the field
> list could be updated but the report has the columns scattered throughout
> so I can't locate a large enough block of contiguous query data that does
> NOT affect the fields list.
> Is there any work around for this?
> Many thanks
>

Help - Standard vs. Data-Driven Subscriptions

Hello,
We have a number of reports, all of which have from and to dates/times as
parameters. This allows us to display data on the report only for a certain
time period (the parameter values are passed to the stored procedure to
return the subset of data).

Anyway, we also need to schedule these reports to run automatically at
specified times. So, we're setting up subscriptions for these reports. The
issue that we're confused about is whether to use standard or data-driven
subscriptions.

Why is this important? Well, the from and to dates/times (parameter values)
will need to change depending on the scheduled run time, and will need to
change every time the report is executed on the schedule. For example, say
we have a report that needs to run every day at 6:00 am. This report needs
to display data for the last 24 hours (therefore, the from and to
dates/times are know--from = day before at 6:00 am and to = current day at
6:00 am). But, since it's running every day, the from and to dates/times
change every time the report schedule runs. I hope this makes sense.

So, is it possible to use a standard subscription to accomplish our goal?
Or, do we have to use some sort of data-driven subscription? Either way,
how would we set this up?

Thanks.
Either would probably work for you, but I'd probably use a data-driven subscription. The biggest reason why a data-driven would be better for you is the relative ease you can modify your parameter values each time you run the report. Essentially, all you'd have to do is modify the values of a couple columns of data in a particular row inside SQL Server.

You could build an expression to subtract / add X hours from the current date/time and use that as the basis for a parameter's default value, and you would be OK with this technique as long as the time you execute the report was always the same...you would be dead if you ran the report at 6a one day, and 9a the next, however. Using this technique would let you go with a standard subscription

|||Thanks for the info! However, I'm still a little unsure about the data-driven subscription route.

In reading Books Online, the documentation says that a data-driven subscription provides a way to deliver reports to a list of subscribers that is determined at run time or to support the wide distribution of a report with a fluctuating list of subscribers. This is not what we need. We don't have a list of subscribers. We only have one subscriber (I guess).

The documentation also notes that to set up a data-driven subscription you need to supply a command that gets subscriber data and that the query should produce one row for each subscriber. We don't even have a user table in our database to query in setting up the data-driven subscription.

All we need is a way to schedule reports to run at any frequency (i.e., daily, weekly, monthly, etc.) in which the report start and end dates/times are determined at run-time based on current date/time and passed as parameter values to the report for generation.

So, you also mentioned building an expression to subtract/add hours, what does this mean? And, how could we do that?

Or, do you have any other suggestions?

Thanks again.
|||

Data driven subscriptions (in my opinion, anyway) are much more flexible than standard subscriptions because you store the parameter values that you want to use inside a SQL table. When you want to change any of this information (or add a new subscriber), it's simply a matter of modifying the row in question or adding a new one. Data driven subscriptions DO take more effort to set up (creating a sql table to host the subscriber info and writing a simple query to return this info to the wizard) , but they pay off down the road because they are easy to care for and feed. Changing stuff around in a standard subscription requires a lot more clicking around in the UI and will take you more time.

You can use a data driven subscription regardless of whether you have one or many subscribers.

That being said, any parameter can have a default value, and that default value can be set via code (an expression) versus using a real, hardcoded value. So you could write an expression which gets the current date (using the Date() or Today() function) and then add or subtract a particular amount of time using the DataAdd() function. Set your defaults up like this and create a standard subscription, and you'll have a "sliding window" of time based on the current date/time. Again, you're at the mercy at the point in time the subscription is executed, etc. etc.

Hope this clears things up for you.

|||Thanks again for the info!

I think all this subscription stuff is starting to make a little sense to me. However, now I have another question.

Let's say that we go with the data-driven subscription scenario, and we create a table to hold subscriber info and all that. Can a data-drive subscription be created using the web service and our own UI instead of Report Manager? We are creating our own ASP.NET (C#) front-end for our Reporting Services implementation (we can't/don't want to use Report Manager) and we'll need to code a solution that allows end-users to create these data-driven subscriptions. Is this possible?

Thanks!
|||

Yes, you can do this -- no problem at all. Report Manager isn't magic in any form, and uses the same (documented) web service calls that you can.

|||

Hi,

In such case, if you are using a Stored Procedure (SP) to generate a Report. You can even add one extra input varchar parameter to SP to take ReportDateRange - e.g. Today, Y'day, CurrentWeek, etc. And then let that SP use this parameter to calculate the current date range and use it to generate report.

This will not only avoid complexity in subscription but also make your report SP more flexible and pluggable.

Thanks,
Mahesh

|||

Really, it doesn't matter. You could create a data-driven subscription with a query that gets the right date/time for the start and end parameters.

You could also put this logic in the default value expression for your parameter. Essentially, when you run the report, you can use an expression to obtain the current execution time of the report. From this value you can use standard .Net date/time functions to build the appropriate values.

Using this approach has the advantage that any user can run the report interactively or subscribe to it and get the same behavior.

Typically, people use two parameters for each query parameter to make this work well - first they create a parameter with valid values like 'Today', 'Yesterday', 'Last week', etc. Then they create a second parameter that is set to no-prompt. This parameter has a default value expression that is based on the value of the first parameter. This way you decouple the actual date used for the report from the value supplied by the user. Thus subscriptions will continue to work whether or not the user actually specifies 'use default' or a specific value for the one parameter they do see.

Hope that helps,

-Lukasz

This posting is provided "AS IS" with no warranties, and confers no rights.

|||

I am trying to create a Data-Driven Subscription to query off a value. I have the value in my stored procedure like:

Procedure dbo.sp_ReportMaster @.myid int=null

When I run the report in the Reporting Services, it prompts for myid, which is fine. I then deployed the report to the Reports Manager and tried to create a Data-Driven Subscription. I put in the pertinent information, then it says, "The 'ReportMaster' has no parameters," even though I have the parameter in my stored procedure and I put myid as a parameter in my report in Reporting Services.

What am I missing here?

Thanks, Iris

|||

Data-driven subscriptions using stored procs are not supported well, though you can make them work if you spend a little time playing with code.

The issue is the Report manager and management studio UI don't handle the case of a stored proc.

You can work around this by calling the createdatadrivensubcription method in code and passing in the prameters explicitly. This isn't entirely easy, but it can be made to work.

Sorry, I don't have an example handy. Take a look for the method definition in MSDN and then you can write a VB.Net script that runs in rs.exe to automate it.

-Lukasz

|||

I have a report that uses a stored procedure that pulls all my data, then I saw where I can create a linked report from my orginial report that will change the parameters. When I tried to use that, I got an error message, 'An interal error occurred on the report server.' I searched for this error when using linked reports, and it said to go to http://support.microsoft.com/kb/918222 and to install the hotfix.

The instructions said something about enabling SMO and SQL-DMO extended stored procedures. It didn't give much instructions but to search the SQL Server 2005 Books online. Then gave a note about making sure a default is set to 1 which mine is.

I installed the packages in order like it said, and still got the error.

Thanks, Iris

Help - Standard vs. Data-Driven Subscriptions

Hello,
We have a number of reports, all of which have from and to dates/times as
parameters. This allows us to display data on the report only for a certain
time period (the parameter values are passed to the stored procedure to
return the subset of data).

Anyway, we also need to schedule these reports to run automatically at
specified times. So, we're setting up subscriptions for these reports. The
issue that we're confused about is whether to use standard or data-driven
subscriptions.

Why is this important? Well, the from and to dates/times (parameter values)
will need to change depending on the scheduled run time, and will need to
change every time the report is executed on the schedule. For example, say
we have a report that needs to run every day at 6:00 am. This report needs
to display data for the last 24 hours (therefore, the from and to
dates/times are know--from = day before at 6:00 am and to = current day at
6:00 am). But, since it's running every day, the from and to dates/times
change every time the report schedule runs. I hope this makes sense.

So, is it possible to use a standard subscription to accomplish our goal?
Or, do we have to use some sort of data-driven subscription? Either way,
how would we set this up?

Thanks.
Either would probably work for you, but I'd probably use a data-driven subscription. The biggest reason why a data-driven would be better for you is the relative ease you can modify your parameter values each time you run the report. Essentially, all you'd have to do is modify the values of a couple columns of data in a particular row inside SQL Server.

You could build an expression to subtract / add X hours from the current date/time and use that as the basis for a parameter's default value, and you would be OK with this technique as long as the time you execute the report was always the same...you would be dead if you ran the report at 6a one day, and 9a the next, however. Using this technique would let you go with a standard subscription

|||Thanks for the info! However, I'm still a little unsure about the data-driven subscription route.

In reading Books Online, the documentation says that a data-driven subscription provides a way to deliver reports to a list of subscribers that is determined at run time or to support the wide distribution of a report with a fluctuating list of subscribers. This is not what we need. We don't have a list of subscribers. We only have one subscriber (I guess).

The documentation also notes that to set up a data-driven subscription you need to supply a command that gets subscriber data and that the query should produce one row for each subscriber. We don't even have a user table in our database to query in setting up the data-driven subscription.

All we need is a way to schedule reports to run at any frequency (i.e., daily, weekly, monthly, etc.) in which the report start and end dates/times are determined at run-time based on current date/time and passed as parameter values to the report for generation.

So, you also mentioned building an expression to subtract/add hours, what does this mean? And, how could we do that?

Or, do you have any other suggestions?

Thanks again.
|||

Data driven subscriptions (in my opinion, anyway) are much more flexible than standard subscriptions because you store the parameter values that you want to use inside a SQL table. When you want to change any of this information (or add a new subscriber), it's simply a matter of modifying the row in question or adding a new one. Data driven subscriptions DO take more effort to set up (creating a sql table to host the subscriber info and writing a simple query to return this info to the wizard) , but they pay off down the road because they are easy to care for and feed. Changing stuff around in a standard subscription requires a lot more clicking around in the UI and will take you more time.

You can use a data driven subscription regardless of whether you have one or many subscribers.

That being said, any parameter can have a default value, and that default value can be set via code (an expression) versus using a real, hardcoded value. So you could write an expression which gets the current date (using the Date() or Today() function) and then add or subtract a particular amount of time using the DataAdd() function. Set your defaults up like this and create a standard subscription, and you'll have a "sliding window" of time based on the current date/time. Again, you're at the mercy at the point in time the subscription is executed, etc. etc.

Hope this clears things up for you.

|||Thanks again for the info!

I think all this subscription stuff is starting to make a little sense to me. However, now I have another question.

Let's say that we go with the data-driven subscription scenario, and we create a table to hold subscriber info and all that. Can a data-drive subscription be created using the web service and our own UI instead of Report Manager? We are creating our own ASP.NET (C#) front-end for our Reporting Services implementation (we can't/don't want to use Report Manager) and we'll need to code a solution that allows end-users to create these data-driven subscriptions. Is this possible?

Thanks!
|||

Yes, you can do this -- no problem at all. Report Manager isn't magic in any form, and uses the same (documented) web service calls that you can.

|||

Hi,

In such case, if you are using a Stored Procedure (SP) to generate a Report. You can even add one extra input varchar parameter to SP to take ReportDateRange - e.g. Today, Y'day, CurrentWeek, etc. And then let that SP use this parameter to calculate the current date range and use it to generate report.

This will not only avoid complexity in subscription but also make your report SP more flexible and pluggable.

Thanks,
Mahesh

|||

Really, it doesn't matter. You could create a data-driven subscription with a query that gets the right date/time for the start and end parameters.

You could also put this logic in the default value expression for your parameter. Essentially, when you run the report, you can use an expression to obtain the current execution time of the report. From this value you can use standard .Net date/time functions to build the appropriate values.

Using this approach has the advantage that any user can run the report interactively or subscribe to it and get the same behavior.

Typically, people use two parameters for each query parameter to make this work well - first they create a parameter with valid values like 'Today', 'Yesterday', 'Last week', etc. Then they create a second parameter that is set to no-prompt. This parameter has a default value expression that is based on the value of the first parameter. This way you decouple the actual date used for the report from the value supplied by the user. Thus subscriptions will continue to work whether or not the user actually specifies 'use default' or a specific value for the one parameter they do see.

Hope that helps,

-Lukasz

This posting is provided "AS IS" with no warranties, and confers no rights.

|||

I am trying to create a Data-Driven Subscription to query off a value. I have the value in my stored procedure like:

Procedure dbo.sp_ReportMaster @.myid int=null

When I run the report in the Reporting Services, it prompts for myid, which is fine. I then deployed the report to the Reports Manager and tried to create a Data-Driven Subscription. I put in the pertinent information, then it says, "The 'ReportMaster' has no parameters," even though I have the parameter in my stored procedure and I put myid as a parameter in my report in Reporting Services.

What am I missing here?

Thanks, Iris

|||

Data-driven subscriptions using stored procs are not supported well, though you can make them work if you spend a little time playing with code.

The issue is the Report manager and management studio UI don't handle the case of a stored proc.

You can work around this by calling the createdatadrivensubcription method in code and passing in the prameters explicitly. This isn't entirely easy, but it can be made to work.

Sorry, I don't have an example handy. Take a look for the method definition in MSDN and then you can write a VB.Net script that runs in rs.exe to automate it.

-Lukasz

|||

I have a report that uses a stored procedure that pulls all my data, then I saw where I can create a linked report from my orginial report that will change the parameters. When I tried to use that, I got an error message, 'An interal error occurred on the report server.' I searched for this error when using linked reports, and it said to go to http://support.microsoft.com/kb/918222 and to install the hotfix.

The instructions said something about enabling SMO and SQL-DMO extended stored procedures. It didn't give much instructions but to search the SQL Server 2005 Books online. Then gave a note about making sure a default is set to 1 which mine is.

I installed the packages in order like it said, and still got the error.

Thanks, Iris