Anyone producing a large number of reports per day?

I'm evaluting Reporting Services for my client. They need to produce 5000+
reports per day, mainly output in PDF format. The PDF's are 6-8 pages long.
Does anyone have any experience of getting this kind of performance out of
SQL-RS?
They have an 8-processor 2.8Ghz server running SQL Server that will be the
Reporting Services server. It has 4gig of memory. Will it be up to the job?
Thanks,
Andy.I am able to render about 70reports (PDF format) in less than 10mins. Each
report has about 200 pages.
"Andy Smith" wrote:
> I'm evaluting Reporting Services for my client. They need to produce 5000+
> reports per day, mainly output in PDF format. The PDF's are 6-8 pages long.
> Does anyone have any experience of getting this kind of performance out of
> SQL-RS?
> They have an 8-processor 2.8Ghz server running SQL Server that will be the
> Reporting Services server. It has 4gig of memory. Will it be up to the job?
> Thanks,
> Andy.|||Don't forget to set boot.ini to use /3GB switch in order to take advantage
of the memory.
To verify RS can handle this load, consider using a test tool like
Application Center Test (ACT), part of Visual Studio, to run a Proof Of
Concept. Below is sample ACT script you might use to run such a test:
' --
CONSTANTS --
const ENABLE_DELAYS = True
const REQUESTBUFFERSIZE = 15000
const REPORTSERVERNAME = "localhost"
' --
' -- Think time variables - examples shows between 2-3 seconds
' --
const MIN_SLEEP_MSEC = 2000
const MAX_SLEEP_MSEC = 3000
' --
' -- Think time logic if desired
' --
Function RandomSleep()
if (NOT ENABLE_DELAYS) then
RandomSleep = 0
return
end if
Dim lMinSleep, lMaxSleep, lSleep
lMaxSleep = MAX_SLEEP_MSEC
lMinSleep = MIN_SLEEP_MSEC
' create a random int within our range
Call Randomize()
lSleep = Int((lMaxSleep - lMinSleep + 1) * Rnd(1) + lMinSleep)
' Test.Trace "Sleeping: " + Cstr(lSleep)
Call Test.Sleep(lSleep)
' return the delay time
RandomSleep = lSleep
End Function
Function SendGetRequest(reportServerUrl)
Dim oConnection, oRequest, oResponse, oHeaders, statusCode
Set oConnection = Test.CreateConnection(REPORTSERVERNAME, 80, false)
If (oConnection is Nothing) Then
Test.Trace "Error: Unable to create connection to server"
Else
' TODO: uncomment this after debugging
' Test.Trace(reportServerUrl)
Set oRequest = Test.CreateRequest
oRequest.ResponseBufferSize = REQUESTBUFFERSIZE
oRequest.Path = reportServerUrl
oRequest.Verb = "GET"
oRequest.HTTPVersion = "HTTP/1.1"
set oHeaders = oRequest.Headers
oHeaders.RemoveAll
oHeaders.Add "Accept", "image/gif, image/x-xbitmap, image/jpeg,
image/pjpeg, application/vnd.ms-excel, application/vnd.ms-powerpoint,
application/msword, */*"
oHeaders.Add "Accept-Language", "en-us"
oHeaders.Add "User-Agent", "Mozilla/4.0 (compatible; MSIE 6.0;
Windows NT 5.1; .NET CLR 1.0.3512; .NET CLR 1.1.4322)"
oHeaders.Add "Host", "(automatic)"
oHeaders.Add "Cookie", "(automatic)"
Set oResponse = oConnection.Send(oRequest)
If (oResponse is Nothing) Then
Test.Trace "Error: Failed to receive response for URL to " +
reportServerUrl
Else
statusCode = oResponse.ResultCode
'DEBUG
Test.Trace "Received: " + CStr(statusCode) + " for: " + REPORTSERVERNAME
+ reportServerUrl
' delay in case of errors - to avoid the snowball effect
' Test.Trace "Response code of: " + CStr(statusCode) + "
recieved for " + reportServerUrl
if (statusCode = 503) Then
Test.Trace "Server too busy error: " + CStr(statusCode) + "
recieved for " + reportServerUrl + " - Process Put To Sleep"
End If
End If
oConnection.Close
End If
End Function
Sub Main()
Dim Url
'--
'-- URL of report to call for test. This one calls report named "Simple"
located in dir /Benchmark
'-- passing in a parm of &RowNumber '--
Url ="/ReportServer?/Benchmark/Simple&RowNumber=20&rs:Command=Render&rc:Toolbar=false&rs:Format=PDF"
SendGetRequest(Url)
RandomSleep()
End Sub
Main
--
-- "This posting is provided 'AS IS' with no warranties, and confers no
rights."
jhmiller@.online.microsoft.com
"Raj Chandra" <RajChandra@.discussions.microsoft.com> wrote in message
news:C23DE604-F4E0-4F3E-BD7B-1C4516C1987C@.microsoft.com...
>I am able to render about 70reports (PDF format) in less than 10mins. Each
> report has about 200 pages.
> "Andy Smith" wrote:
>> I'm evaluting Reporting Services for my client. They need to produce
>> 5000+
>> reports per day, mainly output in PDF format. The PDF's are 6-8 pages
>> long.
>> Does anyone have any experience of getting this kind of performance out
>> of
>> SQL-RS?
>> They have an 8-processor 2.8Ghz server running SQL Server that will be
>> the
>> Reporting Services server. It has 4gig of memory. Will it be up to the
>> job?
>> Thanks,
>> Andy.sql

Anyone heard of Hyperion?

I've been told I have to get involved in Hyperion reporting now. I've never heard of it, and I'm being formally introduced later today. I just thought I'd ask if anybody in here has an impartial opinion!
:)Hi,
I used to work with Brio SQR reports, and I think Brio was bought out by Hyperion, so I think we may be talking about the same product.

I was using Brio SQR v5.5, which was a few years old and lacked some features offered by say JasperReports, but was ok.

The SQR language appeared to be a sort of yucky Basic'ish style, and I generally avoided looking at it whenever I could!

HTH

Paul C.

Anyone else had this error situation?

So, for the past two weeks or so every morning Reporting services has come up
with the report error "Sorting cannot be performed on table1". For some odd
reason though it does the error on all reports with any sorting whatsoever.
I've checked and double checked my sorting expressions
(=iif(Parameters!Direction.Value = "Ascending",
Fields(Parameters!SortBy.Value).Value, 0) and =iif(Parameters!Direction.Value
= "Descending", Fields(Parameters!SortBy.Value).Value, 0)...
Direction is a drop down param which is either Ascending or Descending and
SortBy is a drop down param that contains all table columns) and have had
others look at it as well and the logic is good for it. What ultimately
remedies it is a restart of IIS on the server we run the reports on.
So has anyone else experienced this problem? If so I could use any
information as to the possible cause or fix to this.Please try this - note the CInt() function call:
=iif(Parameters!Direction.Value = "Ascending",
CInt(Fields(Parameters!SortBy.Value).Value), 0)
=iif(Parameters!Direction.Value = "Descending",
CInt(Fields(Parameters!SortBy.Value).Value), 0)
etc.
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Timm" <Timm@.discussions.microsoft.com> wrote in message
news:2DD5CE58-A3DF-41EF-BD47-010A0770E2AE@.microsoft.com...
> So, for the past two weeks or so every morning Reporting services has come
> up
> with the report error "Sorting cannot be performed on table1". For some
> odd
> reason though it does the error on all reports with any sorting
> whatsoever.
> I've checked and double checked my sorting expressions
> (=iif(Parameters!Direction.Value = "Ascending",
> Fields(Parameters!SortBy.Value).Value, 0) and
> =iif(Parameters!Direction.Value
> = "Descending", Fields(Parameters!SortBy.Value).Value, 0)...
> Direction is a drop down param which is either Ascending or Descending and
> SortBy is a drop down param that contains all table columns) and have had
> others look at it as well and the logic is good for it. What ultimately
> remedies it is a restart of IIS on the server we run the reports on.
> So has anyone else experienced this problem? If so I could use any
> information as to the possible cause or fix to this.|||The column names in use are string values (non-numeric) so using CInt throws
back a
different error that will cause the report to not work 100% of the time. I'm
toying with
an idea that this is something to do with the reporting services and not the
reports
themselves since restarting IIS resolves the issue for a day (and the next
day the error
occurs again).
"Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote in message
news:OOY8V1sZFHA.3280@.TK2MSFTNGP09.phx.gbl...
> Please try this - note the CInt() function call:
> =iif(Parameters!Direction.Value = "Ascending",
> CInt(Fields(Parameters!SortBy.Value).Value), 0)
> =iif(Parameters!Direction.Value = "Descending",
> CInt(Fields(Parameters!SortBy.Value).Value), 0)
> etc.
> -- Robert
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>
> "Timm" <Timm@.discussions.microsoft.com> wrote in message
> news:2DD5CE58-A3DF-41EF-BD47-010A0770E2AE@.microsoft.com...
>> So, for the past two weeks or so every morning Reporting services has
>> come up
>> with the report error "Sorting cannot be performed on table1". For some
>> odd
>> reason though it does the error on all reports with any sorting
>> whatsoever.
>> I've checked and double checked my sorting expressions
>> (=iif(Parameters!Direction.Value = "Ascending",
>> Fields(Parameters!SortBy.Value).Value, 0) and
>> =iif(Parameters!Direction.Value
>> = "Descending", Fields(Parameters!SortBy.Value).Value, 0)...
>> Direction is a drop down param which is either Ascending or Descending
>> and
>> SortBy is a drop down param that contains all table columns) and have had
>> others look at it as well and the logic is good for it. What ultimately
>> remedies it is a restart of IIS on the server we run the reports on.
>> So has anyone else experienced this problem? If so I could use any
>> information as to the possible cause or fix to this.
>|||If they are strings, then this explains why it does not work. Use the
following expression then - note that 0 is replaced with an empty string:
=iif(Parameters!Direction.Value = "Ascending",
Fields(Parameters!SortBy.Value).Value, "")
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Timm" <tim@.gumdropbooks.com> wrote in message
news:%23F1YsdtZFHA.720@.TK2MSFTNGP15.phx.gbl...
> The column names in use are string values (non-numeric) so using CInt
> throws back a
> different error that will cause the report to not work 100% of the time.
> I'm toying with
> an idea that this is something to do with the reporting services and not
> the reports
> themselves since restarting IIS resolves the issue for a day (and the next
> day the error
> occurs again).
> "Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote in message
> news:OOY8V1sZFHA.3280@.TK2MSFTNGP09.phx.gbl...
>> Please try this - note the CInt() function call:
>> =iif(Parameters!Direction.Value = "Ascending",
>> CInt(Fields(Parameters!SortBy.Value).Value), 0)
>> =iif(Parameters!Direction.Value = "Descending",
>> CInt(Fields(Parameters!SortBy.Value).Value), 0)
>> etc.
>> -- Robert
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>>
>> "Timm" <Timm@.discussions.microsoft.com> wrote in message
>> news:2DD5CE58-A3DF-41EF-BD47-010A0770E2AE@.microsoft.com...
>> So, for the past two weeks or so every morning Reporting services has
>> come up
>> with the report error "Sorting cannot be performed on table1". For some
>> odd
>> reason though it does the error on all reports with any sorting
>> whatsoever.
>> I've checked and double checked my sorting expressions
>> (=iif(Parameters!Direction.Value = "Ascending",
>> Fields(Parameters!SortBy.Value).Value, 0) and
>> =iif(Parameters!Direction.Value
>> = "Descending", Fields(Parameters!SortBy.Value).Value, 0)...
>> Direction is a drop down param which is either Ascending or Descending
>> and
>> SortBy is a drop down param that contains all table columns) and have
>> had
>> others look at it as well and the logic is good for it. What ultimately
>> remedies it is a restart of IIS on the server we run the reports on.
>> So has anyone else experienced this problem? If so I could use any
>> information as to the possible cause or fix to this.
>>
>|||I did that change and this morning we got that error still. After restarting
IIS it now works again.
Is this a known issue or is this something that may be unique to our system?
"Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote in message
news:%23ggV6FvZFHA.3280@.TK2MSFTNGP09.phx.gbl...
> If they are strings, then this explains why it does not work. Use the
> following expression then - note that 0 is replaced with an empty string:
> =iif(Parameters!Direction.Value = "Ascending",
> Fields(Parameters!SortBy.Value).Value, "")
> --
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>
> "Timm" <tim@.gumdropbooks.com> wrote in message
> news:%23F1YsdtZFHA.720@.TK2MSFTNGP15.phx.gbl...
>> The column names in use are string values (non-numeric) so using CInt
>> throws back a
>> different error that will cause the report to not work 100% of the time.
>> I'm toying with
>> an idea that this is something to do with the reporting services and not
>> the reports
>> themselves since restarting IIS resolves the issue for a day (and the
>> next day the error
>> occurs again).
>> "Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote in message
>> news:OOY8V1sZFHA.3280@.TK2MSFTNGP09.phx.gbl...
>> Please try this - note the CInt() function call:
>> =iif(Parameters!Direction.Value = "Ascending",
>> CInt(Fields(Parameters!SortBy.Value).Value), 0)
>> =iif(Parameters!Direction.Value = "Descending",
>> CInt(Fields(Parameters!SortBy.Value).Value), 0)
>> etc.
>> -- Robert
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>>
>> "Timm" <Timm@.discussions.microsoft.com> wrote in message
>> news:2DD5CE58-A3DF-41EF-BD47-010A0770E2AE@.microsoft.com...
>> So, for the past two weeks or so every morning Reporting services has
>> come up
>> with the report error "Sorting cannot be performed on table1". For some
>> odd
>> reason though it does the error on all reports with any sorting
>> whatsoever.
>> I've checked and double checked my sorting expressions
>> (=iif(Parameters!Direction.Value = "Ascending",
>> Fields(Parameters!SortBy.Value).Value, 0) and
>> =iif(Parameters!Direction.Value
>> = "Descending", Fields(Parameters!SortBy.Value).Value, 0)...
>> Direction is a drop down param which is either Ascending or Descending
>> and
>> SortBy is a drop down param that contains all table columns) and have
>> had
>> others look at it as well and the logic is good for it. What ultimately
>> remedies it is a restart of IIS on the server we run the reports on.
>> So has anyone else experienced this problem? If so I could use any
>> information as to the possible cause or fix to this.
>>
>>
>|||I haven't seen this before. Are you sure you don't have any report execution
caching set up?
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Timm" <tim@.gumdropbooks.com> wrote in message
news:eHjfzC3ZFHA.1040@.TK2MSFTNGP10.phx.gbl...
>I did that change and this morning we got that error still. After
>restarting IIS it now works again.
> Is this a known issue or is this something that may be unique to our
> system?
> "Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote in message
> news:%23ggV6FvZFHA.3280@.TK2MSFTNGP09.phx.gbl...
>> If they are strings, then this explains why it does not work. Use the
>> following expression then - note that 0 is replaced with an empty string:
>> =iif(Parameters!Direction.Value = "Ascending",
>> Fields(Parameters!SortBy.Value).Value, "")
>> --
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>>
>> "Timm" <tim@.gumdropbooks.com> wrote in message
>> news:%23F1YsdtZFHA.720@.TK2MSFTNGP15.phx.gbl...
>> The column names in use are string values (non-numeric) so using CInt
>> throws back a
>> different error that will cause the report to not work 100% of the time.
>> I'm toying with
>> an idea that this is something to do with the reporting services and not
>> the reports
>> themselves since restarting IIS resolves the issue for a day (and the
>> next day the error
>> occurs again).
>> "Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote in message
>> news:OOY8V1sZFHA.3280@.TK2MSFTNGP09.phx.gbl...
>> Please try this - note the CInt() function call:
>> =iif(Parameters!Direction.Value = "Ascending",
>> CInt(Fields(Parameters!SortBy.Value).Value), 0)
>> =iif(Parameters!Direction.Value = "Descending",
>> CInt(Fields(Parameters!SortBy.Value).Value), 0)
>> etc.
>> -- Robert
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>>
>> "Timm" <Timm@.discussions.microsoft.com> wrote in message
>> news:2DD5CE58-A3DF-41EF-BD47-010A0770E2AE@.microsoft.com...
>> So, for the past two weeks or so every morning Reporting services has
>> come up
>> with the report error "Sorting cannot be performed on table1". For
>> some odd
>> reason though it does the error on all reports with any sorting
>> whatsoever.
>> I've checked and double checked my sorting expressions
>> (=iif(Parameters!Direction.Value = "Ascending",
>> Fields(Parameters!SortBy.Value).Value, 0) and
>> =iif(Parameters!Direction.Value
>> = "Descending", Fields(Parameters!SortBy.Value).Value, 0)...
>> Direction is a drop down param which is either Ascending or Descending
>> and
>> SortBy is a drop down param that contains all table columns) and have
>> had
>> others look at it as well and the logic is good for it. What
>> ultimately
>> remedies it is a restart of IIS on the server we run the reports on.
>> So has anyone else experienced this problem? If so I could use any
>> information as to the possible cause or fix to this.
>>
>>
>>
>|||I am new to reporting services, so my answer may not be correct.
I had same error, when 2nd parameter was changed to cint, error went
away.
Something like this -
=iif(Parameters!Direction.Value = "Ascending",
CInt(Fields(Parameters!SortBy.Value).Value), cint(0))
=iif(Parameters!Direction.Value = "Descending",
CInt(Fields(Parameters!SortBy.Value).Value), cint(0))

anyone ?

I've populated reporting DB's in the past using different techniques:

1. Have Transactions in Data Tier Write to OLTP AND Reporting DB Simultaneously

2. Log Shipping

3. Restore BAckups Nightly

4. Replication

5. DTS

My question is what is the BEST Practice in SQL2k5.

I'm all gung ho about using Mirroring but am not certain that this will be the best way to go.

My thoughts are something along the lines of this

1. have OLTP Mirrored to what I'll call a "Staging" DB. This is NOT to be used for Failover just as a means of accessing read only data for the Reporting DB

2. Have 2 DBs on another box that are flattened (Denormalized) for reporting (ReportA and ReportB)

3. Use SSIS to Populate the Report DBs in alternating sequences every XXX minutes (60 minutes let say)

4. Use Logic to let the Reporting Application Figure out which of the Two reporting DB's is "live". The Currently loading DB will be offline while it's being loaded and we'll alternate between the two (Exact method TBD but this wont be rocket science)

Now, I know this will "Work" but is this the best way to go about this ?

I do NOT want to use SSIS to populate the Reporting system directly from our OLTP system due to contention issues etc.

thoughts ?

Hello Gregory,

I've thought of using Database Mirroring for reporting as well, but there were a couple things holding me back. First off, you can't use the mirrored database directly, you have to create database snapshots of the mirrored database that you can use instead. Also, each database snapshot is independent of each other, so any time you create a new one, your applications/SSIS package will have to take this into account and reference the new snapshot. Secondly, the disk space. Unless you manage this well, you could eat up a lot of drive space.

Currently, I am using #3 from your list, restoring nightly backups. The problems I am having with this option is the length of the database restore, during which, the reporting database is offline. I'm up to a 21GB db backup file that I am restoring to another server, and it takes about 3 hours. Another problem I have is that my users would like the data to be more up-to-date.

Eventually, I think, I will be moving to asynchronous replication.

Hope this helps.

Jarret

|||

jarrett,

I guarantee you can speed up your backup "Dramatically" by backing up to multiple files (4 is probably a good # to start with).

Then restore from those 4 files on the target box.

I bet your backup and restore run in about 10 minutes (depending on the disk IO subsystem you're using)

try it out and see what happens.

thanks for the post.

GAJ

|||

Can anyone else comment on this thread\question ?

Anybody looked at LGX Report?

This is at freereporting.com.
It seems to be very similar to Reporting Services.
Has anybody here made a comparison?
JCLooks good, I noticed you have to pay for PDF outputs, which you don't
on RS.
Also one of the biggest advantages of RS is that it is seamless
connection to Visual Studio .Net

Any tutorials (confused!)

Hi,
I'm new to this reporting services technology, but done a far bit of C#
and web services. In the current system I work on we have a complex
typed dataset which I want to use as the source for a reporting
services report.
using c#, can you let me know if the following is possible and point me
in a direction;
Populate the typed dataset in the main c# application.
Call reporting services, passing in this dataset (or XML) as a
parameter
All I've done with reporting services so far is to create a static
report and render this as a PDF and output the result to a physical
file on the local hard drive.
Many thanks
MatMat, have a look at www.gotreportviewer.com
HTH,
Ed
<matpublic@.ntlworld.com> wrote in message
news:1140550512.461086.100440@.g44g2000cwa.googlegroups.com...
> Hi,
> I'm new to this reporting services technology, but done a far bit of C#
> and web services. In the current system I work on we have a complex
> typed dataset which I want to use as the source for a reporting
> services report.
> using c#, can you let me know if the following is possible and point me
> in a direction;
> Populate the typed dataset in the main c# application.
> Call reporting services, passing in this dataset (or XML) as a
> parameter
> All I've done with reporting services so far is to create a static
> report and render this as a PDF and output the result to a physical
> file on the local hard drive.
> Many thanks
> Mat
>

Any SQL Server Guru?

Dear SQL Server Gurus:
Your input and advises are greatly appreciated!
We have a SQL Server 2005 database served for Decision Support and Reporting
purposes and it is about 15 G and growing moderately. The source data is
from DB2, an on line transactional database. Everyday, data (existing or
modified tables with data) was transformed into a SQL server staging database
and got backup on that server. Then the backup was ftp to our produciton
server and the restore process was kick off to restore the database. During
that restore process, our production database got refreshed, however, all
views/functions/store procedures created and edited by the developer will be
gone too. To resolve this problem, everyday our developer before going home,
she would generate all scripts (including views/functions/procedures) thru
Server Management studio and run it thru command line from a job scheduler
right after the restore process was done. We have over 300 views, functions
and store procedures. When the developer generates the scripts, she needs
to manually reset value on the Server Management Studio and it is pretty
risky, for if she did not reset value right (for instance, set dependency to
true), she will not get the correct scripts and the restore process will wipe
out the scripts and the reporting programs would fail. I am looking for a
way to streamline the developer's working procedures.
Is there any way to keep the functions/views/procedure stored on the
database when the tables and data got refreshed? or do you have any other
recommendation?
Thanks,
Jenny1) Create a separate DB for the functions and procs.
2) Use 3-part naming for the objects they access.
3) Create a separate DB with data only. Refresh just that DB. The
objects referred to in #2 are in this DB.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
.
"Jenny" <Jenny@.discussions.microsoft.com> wrote in message
news:63BCA4BD-6889-4157-81BB-AC6B510AEFDB@.microsoft.com...
Dear SQL Server Gurus:
Your input and advises are greatly appreciated!
We have a SQL Server 2005 database served for Decision Support and Reporting
purposes and it is about 15 G and growing moderately. The source data is
from DB2, an on line transactional database. Everyday, data (existing or
modified tables with data) was transformed into a SQL server staging
database
and got backup on that server. Then the backup was ftp to our produciton
server and the restore process was kick off to restore the database. During
that restore process, our production database got refreshed, however, all
views/functions/store procedures created and edited by the developer will be
gone too. To resolve this problem, everyday our developer before going
home,
she would generate all scripts (including views/functions/procedures) thru
Server Management studio and run it thru command line from a job scheduler
right after the restore process was done. We have over 300 views, functions
and store procedures. When the developer generates the scripts, she needs
to manually reset value on the Server Management Studio and it is pretty
risky, for if she did not reset value right (for instance, set dependency to
true), she will not get the correct scripts and the restore process will
wipe
out the scripts and the reporting programs would fail. I am looking for a
way to streamline the developer's working procedures.
Is there any way to keep the functions/views/procedure stored on the
database when the tables and data got refreshed? or do you have any other
recommendation?
Thanks,
Jenny|||Sounds like best option is to create another database for all of the
reporting stored procedures/functions/views. It will take a little bit of
work to go through all of the objects and change the object name to indicate
the database where the data is located. (Check Books Online for "External
Data and Transact-SQL".)
But the end result is that you 'refresh' process would not 'wipe out' the
objects.
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"Jenny" <Jenny@.discussions.microsoft.com> wrote in message
news:63BCA4BD-6889-4157-81BB-AC6B510AEFDB@.microsoft.com...
> Dear SQL Server Gurus:
> Your input and advises are greatly appreciated!
> We have a SQL Server 2005 database served for Decision Support and
> Reporting
> purposes and it is about 15 G and growing moderately. The source data is
> from DB2, an on line transactional database. Everyday, data (existing or
> modified tables with data) was transformed into a SQL server staging
> database
> and got backup on that server. Then the backup was ftp to our produciton
> server and the restore process was kick off to restore the database.
> During
> that restore process, our production database got refreshed, however, all
> views/functions/store procedures created and edited by the developer will
> be
> gone too. To resolve this problem, everyday our developer before going
> home,
> she would generate all scripts (including views/functions/procedures) thru
> Server Management studio and run it thru command line from a job scheduler
> right after the restore process was done. We have over 300 views,
> functions
> and store procedures. When the developer generates the scripts, she
> needs
> to manually reset value on the Server Management Studio and it is pretty
> risky, for if she did not reset value right (for instance, set dependency
> to
> true), she will not get the correct scripts and the restore process will
> wipe
> out the scripts and the reporting programs would fail. I am looking for a
> way to streamline the developer's working procedures.
> Is there any way to keep the functions/views/procedure stored on the
> database when the tables and data got refreshed? or do you have any other
> recommendation?
>
> Thanks,
>
> Jenny
>|||> During
> that restore process, our production database got refreshed, however, all
> views/functions/store procedures created and edited by the developer will
> be
> gone too.
Rather that generate scripts, consider keeping the 'master' scripts under
source control (or at least in the file system). You can then run the
scripts as part of a post-restore process. This is much less risky than
using the database as the source and generating scripts. Keeping scripts
under source control is a Best Practice in SQL Server development.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Jenny" <Jenny@.discussions.microsoft.com> wrote in message
news:63BCA4BD-6889-4157-81BB-AC6B510AEFDB@.microsoft.com...
> Dear SQL Server Gurus:
> Your input and advises are greatly appreciated!
> We have a SQL Server 2005 database served for Decision Support and
> Reporting
> purposes and it is about 15 G and growing moderately. The source data is
> from DB2, an on line transactional database. Everyday, data (existing or
> modified tables with data) was transformed into a SQL server staging
> database
> and got backup on that server. Then the backup was ftp to our produciton
> server and the restore process was kick off to restore the database.
> During
> that restore process, our production database got refreshed, however, all
> views/functions/store procedures created and edited by the developer will
> be
> gone too. To resolve this problem, everyday our developer before going
> home,
> she would generate all scripts (including views/functions/procedures) thru
> Server Management studio and run it thru command line from a job scheduler
> right after the restore process was done. We have over 300 views,
> functions
> and store procedures. When the developer generates the scripts, she
> needs
> to manually reset value on the Server Management Studio and it is pretty
> risky, for if she did not reset value right (for instance, set dependency
> to
> true), she will not get the correct scripts and the restore process will
> wipe
> out the scripts and the reporting programs would fail. I am looking for a
> way to streamline the developer's working procedures.
> Is there any way to keep the functions/views/procedure stored on the
> database when the tables and data got refreshed? or do you have any other
> recommendation?
>
> Thanks,
>
> Jenny
>|||3-part naming of objects is of the form:
SQL 2000: Database.Owner.ObjectName
SQL 2005: Database.Schema.ObjectName
Thus, you would refer to a table in the MyDB database as:
MyDB.dbo.MyTable
You would have 2 DB's, say, DBFunc and DBData. Place your stored procs and
functions in DBFunc. Place your data in DBData. In the functions/procs,
refer to your tables in the form shown above.
HTH
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
.
"Jenny" <Jenny@.discussions.microsoft.com> wrote in message
news:3F2249E6-86F6-454A-9DCA-1C4F194348D4@.microsoft.com...
Hi Tom,
What do you mean in item 2 and 3?
I created a backup db and truncate data, which would fullfilled the first
comments you mentioned. Then what is next? I don't quite understand what
you said on item 2 & 3.
Thanks,
"Tom Moreau" wrote:
> 1) Create a separate DB for the functions and procs.
> 2) Use 3-part naming for the objects they access.
> 3) Create a separate DB with data only. Refresh just that DB. The
> objects referred to in #2 are in this DB.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> ..
> "Jenny" <Jenny@.discussions.microsoft.com> wrote in message
> news:63BCA4BD-6889-4157-81BB-AC6B510AEFDB@.microsoft.com...
> Dear SQL Server Gurus:
> Your input and advises are greatly appreciated!
> We have a SQL Server 2005 database served for Decision Support and
> Reporting
> purposes and it is about 15 G and growing moderately. The source data is
> from DB2, an on line transactional database. Everyday, data (existing or
> modified tables with data) was transformed into a SQL server staging
> database
> and got backup on that server. Then the backup was ftp to our produciton
> server and the restore process was kick off to restore the database.
> During
> that restore process, our production database got refreshed, however, all
> views/functions/store procedures created and edited by the developer will
> be
> gone too. To resolve this problem, everyday our developer before going
> home,
> she would generate all scripts (including views/functions/procedures) thru
> Server Management studio and run it thru command line from a job scheduler
> right after the restore process was done. We have over 300 views,
> functions
> and store procedures. When the developer generates the scripts, she
> needs
> to manually reset value on the Server Management Studio and it is pretty
> risky, for if she did not reset value right (for instance, set dependency
> to
> true), she will not get the correct scripts and the restore process will
> wipe
> out the scripts and the reporting programs would fail. I am looking for a
> way to streamline the developer's working procedures.
> Is there any way to keep the functions/views/procedure stored on the
> database when the tables and data got refreshed? or do you have any other
> recommendation?
>
> Thanks,
>
> Jenny
>

Any Reporting Service Log experts out there?

We are working on developing some statistical reports to help us to better
undertand how our users are (or are not) leveraging our Reporting Services
implementation. We have been writing execution log data to a database based
on the Msft provided SSIS package that pulls data from the Report Server
database and I've discovered a hole in the information that I'm hoping
someone can help me fix.
We use data drive subscriptions pretty heavily which of course require
cached data credentials. In the "ExecutionLogs" table, these entries appear
as being requested by "System" and the user shows our proxy account. If you
review the "ReportServerService_..." log on the report server, you can see
the actual detail surrounding the processing of the subscription, but I can't
find a way to correlate these entries back to the ExecutionLog table. The
"ExecutionLogId" in the ExecutionLog table doesn't reference any of the
uniqueidentifiers that you see in the text ReportServerService log.
My end goal is to be able to update the User column in the ExecutionLog
table with the user who was the actual recipient of the report as identified
in the ReportServerService log file.
Anyone tackle this yet or have any ideas as to how it might be accomplished?I'll give you a big hint.. :)
Remember, you are not limited to just select statements in your datasets for
your reports.
I have a dataset that does INSERTS for my data driven reports... (since it
is data driven, you KNOW who the users are that are going to get the
reports)
cheers!
=-Chris
"KS" <ks@.community.nospam> wrote in message
news:C44736CF-304A-4478-98E9-01BDA5A7C0B2@.microsoft.com...
> We are working on developing some statistical reports to help us to better
> undertand how our users are (or are not) leveraging our Reporting Services
> implementation. We have been writing execution log data to a database
> based
> on the Msft provided SSIS package that pulls data from the Report Server
> database and I've discovered a hole in the information that I'm hoping
> someone can help me fix.
> We use data drive subscriptions pretty heavily which of course require
> cached data credentials. In the "ExecutionLogs" table, these entries
> appear
> as being requested by "System" and the user shows our proxy account. If
> you
> review the "ReportServerService_..." log on the report server, you can
> see
> the actual detail surrounding the processing of the subscription, but I
> can't
> find a way to correlate these entries back to the ExecutionLog table. The
> "ExecutionLogId" in the ExecutionLog table doesn't reference any of the
> uniqueidentifiers that you see in the text ReportServerService log.
> My end goal is to be able to update the User column in the ExecutionLog
> table with the user who was the actual recipient of the report as
> identified
> in the ReportServerService log file.
> Anyone tackle this yet or have any ideas as to how it might be
> accomplished?|||Thanks for the hint, Chris.
Unless I'm missing something, however, that still doesn't provide you with
the ability to cross reference the actual report delivery with the
ExecutionLog record to be able to access the other metrics that are being
captured (TimeDataRetrieval, TimeProcessing, TimeRendering, ByteCount,
RowCount...)
Need to find a way to accurately identify a specific data driven
subscription execution with the corresponding ExecutionLog entry.
Any other ideas? Like I said, all the necessary info is in
ReportServerServices_xxx.log, but I don't see how I can accurately tie that
back to the appropriate ExecutionLogId in the ExecutionLog table. The text
file inlcudes a number of uniqueidentifiers, but none of which match up to
the ExecutionLog. Can't go by exact time either as the text file is not
precise enough in the event of batch data driven subscription processing.
"Chris Conner" wrote:
> I'll give you a big hint.. :)
> Remember, you are not limited to just select statements in your datasets for
> your reports.
> I have a dataset that does INSERTS for my data driven reports... (since it
> is data driven, you KNOW who the users are that are going to get the
> reports)
> cheers!
> =-Chris
>
> "KS" <ks@.community.nospam> wrote in message
> news:C44736CF-304A-4478-98E9-01BDA5A7C0B2@.microsoft.com...
> > We are working on developing some statistical reports to help us to better
> > undertand how our users are (or are not) leveraging our Reporting Services
> > implementation. We have been writing execution log data to a database
> > based
> > on the Msft provided SSIS package that pulls data from the Report Server
> > database and I've discovered a hole in the information that I'm hoping
> > someone can help me fix.
> >
> > We use data drive subscriptions pretty heavily which of course require
> > cached data credentials. In the "ExecutionLogs" table, these entries
> > appear
> > as being requested by "System" and the user shows our proxy account. If
> > you
> > review the "ReportServerService_..." log on the report server, you can
> > see
> > the actual detail surrounding the processing of the subscription, but I
> > can't
> > find a way to correlate these entries back to the ExecutionLog table. The
> > "ExecutionLogId" in the ExecutionLog table doesn't reference any of the
> > uniqueidentifiers that you see in the text ReportServerService log.
> >
> > My end goal is to be able to update the User column in the ExecutionLog
> > table with the user who was the actual recipient of the report as
> > identified
> > in the ReportServerService log file.
> >
> > Anyone tackle this yet or have any ideas as to how it might be
> > accomplished?
>
>|||Hmm... this is a nice challenge.
Let's try this - forget the log for the moment - I know I could look this
up - but I'm not at a report server at the moment - does the report
Globals!ExecutionTime match the time stored in the ExectionLog? I mean, you
know the report name ...
Here is what I was thinking:
select name, b.executiontime
from reportserver.dbo.catalog c
inner join reportserver.dbo.executionlog ex
ON (c.ItemID = ex.ReportID)
inner join BobTable b on (c.name = b.ReportName)
where name = b.ReportName and b.executiontime between c.TimeStart and
c.TimeEnd
Where "BobTable" is your table that you store the report name and report
execution time when the report runs.
=-Chris
"KS" <ks@.community.nospam> wrote in message
news:2E0EE652-8A08-4D89-9DB3-50EB8B367FF6@.microsoft.com...
> Thanks for the hint, Chris.
> Unless I'm missing something, however, that still doesn't provide you with
> the ability to cross reference the actual report delivery with the
> ExecutionLog record to be able to access the other metrics that are being
> captured (TimeDataRetrieval, TimeProcessing, TimeRendering, ByteCount,
> RowCount...)
> Need to find a way to accurately identify a specific data driven
> subscription execution with the corresponding ExecutionLog entry.
> Any other ideas? Like I said, all the necessary info is in
> ReportServerServices_xxx.log, but I don't see how I can accurately tie
> that
> back to the appropriate ExecutionLogId in the ExecutionLog table. The
> text
> file inlcudes a number of uniqueidentifiers, but none of which match up to
> the ExecutionLog. Can't go by exact time either as the text file is not
> precise enough in the event of batch data driven subscription processing.
> "Chris Conner" wrote:
>> I'll give you a big hint.. :)
>> Remember, you are not limited to just select statements in your datasets
>> for
>> your reports.
>> I have a dataset that does INSERTS for my data driven reports... (since
>> it
>> is data driven, you KNOW who the users are that are going to get the
>> reports)
>> cheers!
>> =-Chris
>>
>> "KS" <ks@.community.nospam> wrote in message
>> news:C44736CF-304A-4478-98E9-01BDA5A7C0B2@.microsoft.com...
>> > We are working on developing some statistical reports to help us to
>> > better
>> > undertand how our users are (or are not) leveraging our Reporting
>> > Services
>> > implementation. We have been writing execution log data to a database
>> > based
>> > on the Msft provided SSIS package that pulls data from the Report
>> > Server
>> > database and I've discovered a hole in the information that I'm hoping
>> > someone can help me fix.
>> >
>> > We use data drive subscriptions pretty heavily which of course require
>> > cached data credentials. In the "ExecutionLogs" table, these entries
>> > appear
>> > as being requested by "System" and the user shows our proxy account.
>> > If
>> > you
>> > review the "ReportServerService_..." log on the report server, you can
>> > see
>> > the actual detail surrounding the processing of the subscription, but I
>> > can't
>> > find a way to correlate these entries back to the ExecutionLog table.
>> > The
>> > "ExecutionLogId" in the ExecutionLog table doesn't reference any of the
>> > uniqueidentifiers that you see in the text ReportServerService log.
>> >
>> > My end goal is to be able to update the User column in the ExecutionLog
>> > table with the user who was the actual recipient of the report as
>> > identified
>> > in the ReportServerService log file.
>> >
>> > Anyone tackle this yet or have any ideas as to how it might be
>> > accomplished?
>>

Any one knows the location of the SqlServersamples.msi? plz let me konw

Hi i am going through with the videos on reporting server. in the videos they have given files to download from the particular URL. but now they have changed and i cannot find these files on reporting server installations. plz any one know where the location of these files exists.

i need to download four files from the following link

http://msdn2.microsoft.com/en-us/express/bb410792.aspx

but i can find only two from this link they are:

Install Microsoft SQL Server 2005 Express Edition with Advanced Services

Microsoft SQL Server 2005 Express Edition Toolkit

but i dont find remaining two links which says in this video

i cant find SQLserverSample.MSI. any one know the location of this file plz.

plz click for the video link herehttp://www.asp.net/learn/sql-videos/video-112.aspx

SQL Server code samples and sample databases are now hosted on CodePlex

for more information:

https://www.microsoft.com/downloads/details.aspx?familyid=e719ecf7-9f46-4312-af89-6ad8702e4e6e&displaylang=en

for download:

http://codeplex.com/SqlServerSamples

for the msi files:

http://www.codeplex.com/MSFTDBProdSamples/Release/ProjectReleases.aspx?ReleaseId=4004

thanks

|||

Try this for the msi:

http://www.codeplex.com/MSFTISProdSamples/Release/ProjectReleases.aspx?ReleaseId=4039

Any ideas ?

Unable to encrypt or decrypt data managed by the Report Server instance.
Please refer to the Reporting Services online help for guidance in enabling
this functionality.
...In help there is no help...If I understand you corrrectly, one way to do it would be to use a custom
assembly to encrtpy and decrypt data.
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Michael Vardinghus" <michaelvardinghus@.notexisting.com> wrote in message
news:O3XEwo9oEHA.4004@.TK2MSFTNGP10.phx.gbl...
> Unable to encrypt or decrypt data managed by the Report Server instance.
> Please refer to the Reporting Services online help for guidance in
enabling
> this functionality.
> ...In help there is no help...
>

Any hosting for Report Services

Does anyone know of a commercial hosting service that is supporting the new Reporting Services? The hosters I have talked to don't even know what it is.In case anyone is interested, MaximumASP is now offering Reporting Services along with their SQL 2000. Haven't actually tried it yet, but getting ready to.|||ASPWired also has Reporting Services...

JK|||anyone know a host supporting RS in the United Kingdom ?! not after a dedicated server setup - too expensive ... thanks|||

Don't have the answer that you need, but if you find that you're only option is to use a host in the States, I use RS at both MaximumASP and DiscountASP on shared accounts (not dedicated servers). Works absolutely great. We produce thousands of reports a month.

|||yes thanks - i actually opened an account with DiscountASP.NET last week to test it all and it IS great but the trans-atlantic time lag is a little annoying ... yep, it still exists even today! i am very close to going with them for my client but i may have to bite the bullet and take out a dedicated server instead

Any Help regarding SQL Reporting Services

Hi,
I checked with msdn for help in sql Reporting Services.
Where can i find help for the same.
--
SushiLIf I understand you correctly you are asking where the documentation is?
When you install RS it installs Books OnLine. That is where all the
documentation is located.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Sushil" <Sushil@.discussions.microsoft.com> wrote in message
news:D3072889-4C65-4EAC-A4C5-9AF7FF37A58A@.microsoft.com...
> Hi,
> I checked with msdn for help in sql Reporting Services.
> Where can i find help for the same.
> --
> SushiL|||Thanks a lot Bruce but i'm not able to find it. Is there any other way if i
can find the documentation. It would be a great help if you tell b'coz i have
already installed Reporting Services. i checked it in MSDN.Net as well as
book online of SQL Server but im not able to find it.
--
SushiL
"Bruce L-C [MVP]" wrote:
> If I understand you correctly you are asking where the documentation is?
> When you install RS it installs Books OnLine. That is where all the
> documentation is located.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Sushil" <Sushil@.discussions.microsoft.com> wrote in message
> news:D3072889-4C65-4EAC-A4C5-9AF7FF37A58A@.microsoft.com...
> > Hi,
> >
> > I checked with msdn for help in sql Reporting Services.
> > Where can i find help for the same.
> > --
> > SushiL
>
>|||Reporting Services BOL is found by going to program manager -> sql server ->
reporting services (note the exact naming might be different, I am on a
computer without RS installed). So, you should have the reporting services
showing up, what happens when you go there. If you don't then you need to
reinstall. Did you install the client tools? I have BOL regardless. Both on
my servers and on my development machine.
You could also try this (installing an updated version of BOL).
http://www.microsoft.com/downloads/details.aspx?FamilyID=5e550d73-8f35-435e-bb71-c8573a1cdbdb&DisplayLang=en
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Sushil" <Sushil@.discussions.microsoft.com> wrote in message
news:B2A50B72-652C-40A5-9B05-FAB98E53BE52@.microsoft.com...
> Thanks a lot Bruce but i'm not able to find it. Is there any other way if
> i
> can find the documentation. It would be a great help if you tell b'coz i
> have
> already installed Reporting Services. i checked it in MSDN.Net as well as
> book online of SQL Server but im not able to find it.
> --
> SushiL
>
> "Bruce L-C [MVP]" wrote:
>> If I understand you correctly you are asking where the documentation is?
>> When you install RS it installs Books OnLine. That is where all the
>> documentation is located.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "Sushil" <Sushil@.discussions.microsoft.com> wrote in message
>> news:D3072889-4C65-4EAC-A4C5-9AF7FF37A58A@.microsoft.com...
>> > Hi,
>> >
>> > I checked with msdn for help in sql Reporting Services.
>> > Where can i find help for the same.
>> > --
>> > SushiL
>>

Any good webcasts for sql server 2005 reporting services {Developer perspective}

Can any body please point me to Any good webcasts for sql server 2005 reporting services {Developer perspective}

Also Please recommend me any good book for using and creating SSRS.

Thanks

Hello Kamii,

Here is a link to some of the webcasts on Microsoft's site, there is a section for Reporting Services.

http://www.microsoft.com/events/series/sqlserverbi.mspx

And here is a book on Reporting Services, it got pretty good reviews.

http://www.amazon.com/Microsoft-Server-2005-Reporting-Services/dp/0072262397/ref=pd_bbs_sr_2/103-3827006-9073459?ie=UTF8&s=books&qid=1174049936&sr=8-2

Hope this helps.

Jarret

Any good reporting tool?

Hello!
Our company was looking for good reporting tool... and found
It is here: http://www.perpetuumsoft.com
I just want to know: Does anybody use it?
Is it worth to purchase?
Any comments on this product or good alternatives.
Thanks in advance.And what was exactly wrong with SQL RS?
"ChrisSammer" <u41467@.uwe> wrote in message news:804c3d2282f79@.uwe...
> Hello!
> Our company was looking for good reporting tool... and found
> It is here: http://www.perpetuumsoft.com
> I just want to know: Does anybody use it?
> Is it worth to purchase?
> Any comments on this product or good alternatives.
> Thanks in advance.|||This NG is about SQL Server reporting Services, which comes with its own
reporting designer (or "reporting tool" as your term?), no other
tools/alternatives can be used to generate report for SQL Server RS, AFAIK,
so far.
"ChrisSammer" <u41467@.uwe> wrote in message news:804c3d2282f79@.uwe...
> Hello!
> Our company was looking for good reporting tool... and found
> It is here: http://www.perpetuumsoft.com
> I just want to know: Does anybody use it?
> Is it worth to purchase?
> Any comments on this product or good alternatives.
> Thanks in advance.|||Good alternative - http://www.sisense.com
"ChrisSammer" <u41467@.uwe> wrote in message news:804c3d2282f79@.uwe...
> Hello!
> Our company was looking for good reporting tool... and found
> It is here: http://www.perpetuumsoft.com
> I just want to know: Does anybody use it?
> Is it worth to purchase?
> Any comments on this product or good alternatives.
> Thanks in advance.

Any good books on Microsoft Reporting Services for a Visual Studio developer?

I'm starting to useMicrosoft Reporting Services and coming from a Crystal background. I'm wondering if anyone is aware of a good book that addresses development of Microsoft reports within the Visual Studio context.

Thanks!

BCB

I just ordered Microsoft SQL Server 2005 Reporting Services 2005 by Brian Larson through amazon this morning. It is supposed to be here tomorrow sometime. I can let you know if it is any good. It did get good reviews on the amazon site and I also found amazon to be the cheapest.

I am having problems with my reporting deployment to the server. It keeps telling me that the ASPNET account does not have sufficient privileges to perform the operation.

|||I have been using Brian Larson's book (SQL Server 2005 Reporting Services) for several months and it is wonderful. It reminds me of a good Wrox book in the sense that it uses cumulative examples where a report that was developed earlier may be used as the basis for demonstrating a new feature. (Don't get me wrong - there are a bunch of report types in the book including Web Services.) You can work through the examples yourself or there is downloadable source code. The book covers both the design and administration side of Reporting Services. The book is less than 600 pages (not counting the appendices) and it has many screen shots that make explicit what the author has just described in words. I like that. The examples really work and files that he references are present in the folders where he states they will be found. I like that even more. Larson isn't just a technical guy who knows a lot about this product; he's an accomplished writer as well. I found that the book was a fairly quick read that managed to leave a lot of knowledge behind after I put it down for the final time. Did I mention that I recommend it?|||Hey guyin kalamazoo,

I just recently went through this learning curve. You have to add permissions for ASPNET to the database that the datasource is trying to access. Unless you store the credentials in the datasource, then it will use those credentials to access the database.

Anyway, look into the permissions for ASPNET on the machine with the database, and make sure you have the proper roles setup on the reporting server.

I hope this helps, as I am still relatively new to SSRS2005.

Anthony

Any free reporting tool you recommend?

dun need sophisticated interactive reports, but just simple basic rigid reporting will do :D
looking for a simple tool for my end users. Crystal Reports a bit overkill....MS Access?
MS Excel?|||How about Reporting Services on SQL Server 2005

Regards
Kris Zywczyk|||yah, still trying out to set up the report server in SQL 2005, initial trial on the BI visio studio failed, dunno why.

Is the reporting services included in SQL 2005 express?|||What do you mean you want this for your users?

"are those rocks up ahead?"

"I don't know, but if they are, we all be dead"

OK, what movie.|||"No more rhymes now. I mean it!"

"Does anyone want a peanut?"|||"aaaaaaaaarrrrrrrrrrrrrgggggggggggggghhhhhhhhhh"

"What are you doing?"

"I think someone is following us"

"That would be inconcceivable"|||I personally love it when non-developers write production sql. I have project managers who do it for word merges. i have report writers who do not understand group by. It gives me plenty of things to fix and server slow downs to diagnose.|||"I'm not left-handed either."|||"How do you write women so well?"
"I think of a man, and I take away reason and accountability."|||"How do you write women so well?"
"I think of a man, and I take away reason and accountability."
Great !! :D

Any easy way to create SQL on the fly with RS?

I am very new to reporting services. I am using reporting services
with Visual Web Developer and my database is Oracle 10g.
I have successfully created reports using the MS reporting services.
Data were pulled out from Oracle 10g through pre-defined SQL.
I would like to display on the web a list of fields that my end users
can select, and then my web application creates the SQL command on the
fly according to end users' selection criteria. Any easy way to do
this with the reporting services?
I hope that I have made clear what effect I would like to achieve. If
not, please ask. Thanks.If you are using SSRS 2005 then you got the answer. Just have a look at what
is called "Report Model" where in you can create a view where you can give
the readable column names and the users can drag and drop to get the reports.
Amarnath
"antonyliu2002@.yahoo.com" wrote:
> I am very new to reporting services. I am using reporting services
> with Visual Web Developer and my database is Oracle 10g.
> I have successfully created reports using the MS reporting services.
> Data were pulled out from Oracle 10g through pre-defined SQL.
> I would like to display on the web a list of fields that my end users
> can select, and then my web application creates the SQL command on the
> fly according to end users' selection criteria. Any easy way to do
> this with the reporting services?
> I hope that I have made clear what effect I would like to achieve. If
> not, please ask. Thanks.
>|||Amarnath wrote:
> If you are using SSRS 2005 then you got the answer. Just have a look at what
> is called "Report Model" where in you can create a view where you can give
> the readable column names and the users can drag and drop to get the reports.
> Amarnath
> "antonyliu2002@.yahoo.com" wrote:
> > I am very new to reporting services. I am using reporting services
> > with Visual Web Developer and my database is Oracle 10g.
> >
> > I have successfully created reports using the MS reporting services.
> > Data were pulled out from Oracle 10g through pre-defined SQL.
> >
> > I would like to display on the web a list of fields that my end users
> > can select, and then my web application creates the SQL command on the
> > fly according to end users' selection criteria. Any easy way to do
> > this with the reporting services?
> >
> > I hope that I have made clear what effect I would like to achieve. If
> > not, please ask. Thanks.
> >
That sounds great. I am not sure which version I am using, but I just
downloaded it a few weeks ago from Microsoft as a plug-in for Visual
Web Developer. I'll check it out.|||> Amarnath wrote:
> If you are using SSRS 2005 then you got the answer. Just have a look at what
> is called "Report Model" where in you can create a view where you can give
> the readable column names and the users can drag and drop to get the reports.
> Amarnath
>
Hi, I am using the Reporting Add-in for Visual Web Developer (VWD)
2005. I was not able to see anything called "Report Model" in VWD.
As I mentioned before, I've successfully created reports using this
Add-in. But the reports I created were predefined. I would like to
let my web users select what data they would like to get from the
database, and then create the SQL on the fly. I hope that I can do
this with this Reporting Add-in.
Any idea about how I can do this? Thanks.|||antonyliu2002@.yahoo.com wrote:
> > Amarnath wrote:
> > If you are using SSRS 2005 then you got the answer. Just have a look at what
> > is called "Report Model" where in you can create a view where you can give
> > the readable column names and the users can drag and drop to get the reports.
> >
> > Amarnath
> >
> Hi, I am using the Reporting Add-in for Visual Web Developer (VWD)
> 2005. I was not able to see anything called "Report Model" in VWD.
> As I mentioned before, I've successfully created reports using this
> Add-in. But the reports I created were predefined. I would like to
> let my web users select what data they would like to get from the
> database, and then create the SQL on the fly. I hope that I can do
> this with this Reporting Add-in.
> Any idea about how I can do this? Thanks.
I've googled a little bit, and the tutorials I found on creating
reporting models all use BI Development Studio. Does this mean that I
cannot possibly create a reporting model with the Add-in in Visual Web
Developer 2005?|||The BI tools come with their own version of VS. If you have it, it
integrates with it. If not, then it installs it. This means that VS 2005
does not need to be purchased.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
<antonyliu2002@.yahoo.com> wrote in message
news:1161640436.894263.5660@.i3g2000cwc.googlegroups.com...
> antonyliu2002@.yahoo.com wrote:
>> > Amarnath wrote:
>> > If you are using SSRS 2005 then you got the answer. Just have a look at
>> > what
>> > is called "Report Model" where in you can create a view where you can
>> > give
>> > the readable column names and the users can drag and drop to get the
>> > reports.
>> >
>> > Amarnath
>> >
>> Hi, I am using the Reporting Add-in for Visual Web Developer (VWD)
>> 2005. I was not able to see anything called "Report Model" in VWD.
>> As I mentioned before, I've successfully created reports using this
>> Add-in. But the reports I created were predefined. I would like to
>> let my web users select what data they would like to get from the
>> database, and then create the SQL on the fly. I hope that I can do
>> this with this Reporting Add-in.
>> Any idea about how I can do this? Thanks.
> I've googled a little bit, and the tutorials I found on creating
> reporting models all use BI Development Studio. Does this mean that I
> cannot possibly create a reporting model with the Add-in in Visual Web
> Developer 2005?
>|||> Bruce L-C [MVP] wrote:
> The BI tools come with their own version of VS. If you have it, it
> integrates with it. If not, then it installs it. This means that VS 2005
> does not need to be purchased.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>
Thanks, Bruce, are you suggesting that the BI Development Studio is
free? Where can I get it?
I have no Visual Studio 2005, what I have is the free Visual Web
Developer 2005.|||It is free. It comes on the SQL Server media.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
<antonyliu2002@.yahoo.com> wrote in message
news:1161658217.724868.128170@.b28g2000cwb.googlegroups.com...
>> Bruce L-C [MVP] wrote:
>> The BI tools come with their own version of VS. If you have it, it
>> integrates with it. If not, then it installs it. This means that VS 2005
>> does not need to be purchased.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
> Thanks, Bruce, are you suggesting that the BI Development Studio is
> free? Where can I get it?
> I have no Visual Studio 2005, what I have is the free Visual Web
> Developer 2005.
>|||> Bruce L-C [MVP] wrote:
> It is free. It comes on the SQL Server media.
>
Thanks, Bruce.
Before I proceed, I have a question. We don't use MS SQL Server as our
database behind our web application. Instead, we use Oracle 10g. Will
I be able to create a web-based application that allows users to create
ad-hoc reports using the SQL Server reporting services and Oracle 10g?
Thanks.|||Developers yes, end users no.
Report Designer is for developers and that works against Oracle. For end
users there is a another tool where first a developer/dba creates a model
and that model is used by the end users to create their own reports. Models
can not be created against Oracle data.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
<antonyliu2002@.yahoo.com> wrote in message
news:1161703326.930679.107880@.i3g2000cwc.googlegroups.com...
>> Bruce L-C [MVP] wrote:
>> It is free. It comes on the SQL Server media.
> Thanks, Bruce.
> Before I proceed, I have a question. We don't use MS SQL Server as our
> database behind our web application. Instead, we use Oracle 10g. Will
> I be able to create a web-based application that allows users to create
> ad-hoc reports using the SQL Server reporting services and Oracle 10g?
> Thanks.
>|||> Bruce L-C [MVP] wrote:
> Developers yes, end users no.
> Report Designer is for developers and that works against Oracle. For end
> users there is a another tool where first a developer/dba creates a model
> and that model is used by the end users to create their own reports. Models
> can not be created against Oracle data.
>
Thanks, Bruce.
I am new to Reporting Services. The kind of reports I've managed to
create with the Reporting Services Add-in in Visual Web Developer are
from predefined SQL queries.
Just for the purpose of simplicity, suppose that in my web application,
I present the names of 10 students in a dropdown list. I would like to
let my web users select one of these students and then I construct the
sql query on the fly, and create a report about this selected student
only.
Is this do-able with the Reporting Services Add-in in Visual Web
Developer 2005 Express Edition?
Thanks.