Determine slow running reports

Take a look at the Execution Log topics in Books Online. All of the informaiton you want is stored in the ReportServer database. There is even a DTS package to extract the data and a set of reports which show you statistics.
Brian Welcker
Group Program Manager
SQL Server Reporting Services
--Original Message--
From: Randy Knight
Posted At: Thursday, October 06, 2005 2:53 PM
Posted To: microsoft.public.sqlserver.reportingsvcs
Conversation: Determine slow running reports
Subject: Re: Determine slow running reports
That would be great if I had control of the reports but as I said, the reports are published by analysts from all over the company. I just wondered if RS put some statistics or anything like that in the ReportServer database. It would seem to be useful ... how many times report is run, avg execution times, etc.
Guess I'll resort to good ol' SQL Profiler :)Just what I was looking for. Thanks.

Determine slow running reports

I've been tasked with optimizing our reporting environment and want to
target the worst performers first. The problem is that our reports are
published directly to the reporting server by reporting analysts
throughout the organization so I have very little knowled as to what's
out there, who uses them, etc. Is there a way to query reporting
services to determine historical exectuion times? IOW, give me the top
10 reports ordered by avg exectuion time descending or something of
that nature.In your data source defenition - instead of single query use a stored
procedure or just type in something like that:
declare @.start smalldatetime
declare @.end smalldatetime
set @.start = getdate()
-- your query
set @.end=getdate()
-- and at the end log the execution time like that
insert logtable values (reportname, username, getdate(), @.end-@.start)
"Randy Knight" <knight.randy@.gmail.com> wrote in message
news:1128523651.640818.14420@.o13g2000cwo.googlegroups.com...
> I've been tasked with optimizing our reporting environment and want to
> target the worst performers first. The problem is that our reports are
> published directly to the reporting server by reporting analysts
> throughout the organization so I have very little knowled as to what's
> out there, who uses them, etc. Is there a way to query reporting
> services to determine historical exectuion times? IOW, give me the top
> 10 reports ordered by avg exectuion time descending or something of
> that nature.
>|||That would be great if I had control of the reports but as I said, the
reports are published by analysts from all over the company. I just
wondered if RS put some statistics or anything like that in the
ReportServer database. It would seem to be useful ... how many times
report is run, avg execution times, etc.
Guess I'll resort to good ol' SQL Profiler :)|||SQL 2005 has just what you need... You must install the samples from the SQL
install CD.. This will give you a directory in program files/microsoft sql
server 2005/Report samples/Server management Samples. --
THe directions are included... You can get all kinds of performance related
reports, who runs what, how long it takes, how many times each report has
been run...
Hope this helps
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
I support the Professional Association for SQL Server ( PASS) and it''s
community of SQL Professionals.
"Randy Knight" wrote:
> I've been tasked with optimizing our reporting environment and want to
> target the worst performers first. The problem is that our reports are
> published directly to the reporting server by reporting analysts
> throughout the organization so I have very little knowled as to what's
> out there, who uses them, etc. Is there a way to query reporting
> services to determine historical exectuion times? IOW, give me the top
> 10 reports ordered by avg exectuion time descending or something of
> that nature.
>

Detecting maximum database size

I need a way of programmatically detecting the maximum size of a database
when being used under MSDE. The stored procedure sp_helpdb reports the
maxsize value as being 'unlimited', which in the case of MSDE is untrue.
I could look at the product name and infer the max size *but* this would be
my last alternative since I would like it also to work for say the upcoming
2005 version, that I understand has had its max db size increased to 4gigs.
Regards
Lee
There is no function for that, if you want to know the limit for the MSDE
you have to use that via the Selection of the prduct name.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
"Lee Alexander" <lee@.NoSpamPlease_Digita.com> wrote in message
news:eXwJQSigFHA.1416@.TK2MSFTNGP09.phx.gbl...
>I need a way of programmatically detecting the maximum size of a database
> when being used under MSDE. The stored procedure sp_helpdb reports the
> maxsize value as being 'unlimited', which in the case of MSDE is untrue.
> I could look at the product name and infer the max size *but* this would
> be
> my last alternative since I would like it also to work for say the
> upcoming
> 2005 version, that I understand has had its max db size increased to
> 4gigs.
> Regards
> Lee
>
|||Thanks for the response, i thought that might be the case.
Regards
Lee
"Jens Smeyer" <Jens@.remove_this_for_contacting_sqlserver2005.de> wrote in
message news:%23F68UpigFHA.1612@.TK2MSFTNGP12.phx.gbl...
> There is no function for that, if you want to know the limit for the MSDE
> you have to use that via the Selection of the prduct name.
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
> "Lee Alexander" <lee@.NoSpamPlease_Digita.com> wrote in message
> news:eXwJQSigFHA.1416@.TK2MSFTNGP09.phx.gbl...
>

Designing SQL2K RS reports using VS 2005 Express

Anyone:
Is it possible to install the SQL Server 2000 Reporting Services design
component for Visual Studio 2003 into Visual Studio 2005 Express to design
reports that will be deployed to Reporting Services running on SQL Server
2000? Thank you in advance for your time.No, this is not possible. RS 2000 Report Designer requires VS 2003.
--
Albert Yen
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Nikolai Sonin" <NikolaiSonin@.discussions.microsoft.com> wrote in message
news:B5728FFD-6C1A-4C59-A3A7-C415C4436DFD@.microsoft.com...
> Anyone:
> Is it possible to install the SQL Server 2000 Reporting Services
> design
> component for Visual Studio 2003 into Visual Studio 2005 Express to design
> reports that will be deployed to Reporting Services running on SQL Server
> 2000? Thank you in advance for your time.|||I have an MSDN subscription and just installed both SQL Server 2005 Beta
tools and Visual Studio 2005 Beta 2 - I'm assuming:
1.) That SQL Server 2005 has a Report Designer
2.) That the SQL Server 2005 Report Designer will plug into Visual
Studio 2005
3.) That the SQL Server 2005 Report Designer inside of Visual Studio
2005 will work on SQL Server 2000 Databases
4.) That there is some way of importing reports designed in SQL Server
2000 Report Designer into the SQL Server 2005 Report Designer.
Could you tell me which of these are true?
"Albert Yen [MSFT]" wrote:
> No, this is not possible. RS 2000 Report Designer requires VS 2003.
> --
> Albert Yen
> SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "Nikolai Sonin" <NikolaiSonin@.discussions.microsoft.com> wrote in message
> news:B5728FFD-6C1A-4C59-A3A7-C415C4436DFD@.microsoft.com...
> > Anyone:
> > Is it possible to install the SQL Server 2000 Reporting Services
> > design
> > component for Visual Studio 2003 into Visual Studio 2005 Express to design
> > reports that will be deployed to Reporting Services running on SQL Server
> > 2000? Thank you in advance for your time.
>
>

Designing reports without Visual Studio .NET 2003

I'm really interested in Reporting Services, but I can't seem to find any
documentation on how to write reports and utilize it without Visual Studio
.NET 2003. Is there any way to do that?
--
Chris Shipley
HSI System Administrator
CAFCA, Inc.
Hartford, CT
http://www.cafca.org
chriss@.crtct.orgHere is some information.
http://www.ReportingServicesFAQ.com/ow.asp?ReportingWithoutVisualStudio
Chris Shipley wrote:
> I'm really interested in Reporting Services, but I can't seem to find any
> documentation on how to write reports and utilize it without Visual Studio
> .NET 2003. Is there any way to do that?
> --
> Chris Shipley
> HSI System Administrator
> CAFCA, Inc.
> Hartford, CT
> http://www.cafca.org
> chriss@.crtct.org
>|||Microsoft also have a sample app for creating RDL.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rsamples/htm/rss_tutorials_v1_02ua.asp
Combine this with the sample code for accessing the reporting services web
servic
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rsamples/htm/rss_sampleapps_v1_002q.asp
With these two set of examples you can write a program to create rdl,
publish it to the web service and view it within an application. The
complexity will be trying to generate more complex reports as the sample
provided on msdn only generates simple rdl. Easiest way I found to start
writing code to generate more complex rdl was to use the sample RDL program
to generate a report, load this report in vs.net and make some changes then
compare the modified rdl with your original and retro-fit the changes in to
the code. With a lot of time (and hair pulling) you can ultimately generate
fairly complex RDL.
Hope this helps,
Richard
"Jerry" wrote:
> Here is some information.
> http://www.ReportingServicesFAQ.com/ow.asp?ReportingWithoutVisualStudio
>
> Chris Shipley wrote:
> > I'm really interested in Reporting Services, but I can't seem to find any
> > documentation on how to write reports and utilize it without Visual Studio
> > .NET 2003. Is there any way to do that?
> >
> > --
> > Chris Shipley
> > HSI System Administrator
> > CAFCA, Inc.
> > Hartford, CT
> > http://www.cafca.org
> > chriss@.crtct.org
> >
> >
>|||Hey, thanks for that! See, I have this huge database (1.3+ gb) with over
500 users, but there's no good reporting tool for them. I'm looking for a
way to give them a visual report writing tool, perhaps on the web secured
through SSL. But if they have to use VS.NET to create a report, its not
very cost effective.
Anyone out there know of any resources I can look at to achieve my
objective? I'd utilize SQL Reporting Services, but I don't have VS.NET
either. I suppose if I had to, I could learn RDL, but that doesn't seem an
effective method for users. Maybe someone out there is already writing a
VS.NET program for that purpose! :)
--
Chris Shipley
HSI System Administrator
CAFCA
Hartford, CT
Ph: 860-560-5455
http://www.cafca.org/helpdesk
chriss@.crtct.org
"Jerry" <JerryNY32513@.hotmail.com> wrote in message
news:uvPSGLkBFHA.2568@.TK2MSFTNGP11.phx.gbl...
> Here is some information.
> http://www.ReportingServicesFAQ.com/ow.asp?ReportingWithoutVisualStudio
>
> Chris Shipley wrote:
> > I'm really interested in Reporting Services, but I can't seem to find
any
> > documentation on how to write reports and utilize it without Visual
Studio
> > .NET 2003. Is there any way to do that?
> >
> > --
> > Chris Shipley
> > HSI System Administrator
> > CAFCA, Inc.
> > Hartford, CT
> > http://www.cafca.org
> > chriss@.crtct.org
> >
> >|||Several partners provide applications for RS 2000 that will allow the
creation of reports without using VS.NET. Some of them provide web-based
designers, some of them provide tools integrated e.g. into Office. You may
want to check:
http://www.microsoft.com/sql/reporting/partners/softwareapps.asp
If you can wait till RS 2005, it will provide an additional report design
tool called "Report Builder". It is a stand-alone application (independent
of the VS.NET report designer) and is focused on end-users. It allows them
to easily create reports without needing to design queries or writing
expressions. Report Builder was briefly shown as one of the new features in
RS 2005 in this webcast:
http://msevents.microsoft.com/cui/eventdetail.aspx?eventid=1032263305&culture=en-us
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Chris Shipley" <chriss@.crtct.org> wrote in message
news:%23HOyS7sBFHA.1408@.TK2MSFTNGP10.phx.gbl...
> Hey, thanks for that! See, I have this huge database (1.3+ gb) with over
> 500 users, but there's no good reporting tool for them. I'm looking for a
> way to give them a visual report writing tool, perhaps on the web secured
> through SSL. But if they have to use VS.NET to create a report, its not
> very cost effective.
> Anyone out there know of any resources I can look at to achieve my
> objective? I'd utilize SQL Reporting Services, but I don't have VS.NET
> either. I suppose if I had to, I could learn RDL, but that doesn't seem
an
> effective method for users. Maybe someone out there is already writing a
> VS.NET program for that purpose! :)
> --
> Chris Shipley
> HSI System Administrator
> CAFCA
> Hartford, CT
> Ph: 860-560-5455
> http://www.cafca.org/helpdesk
> chriss@.crtct.org
> "Jerry" <JerryNY32513@.hotmail.com> wrote in message
> news:uvPSGLkBFHA.2568@.TK2MSFTNGP11.phx.gbl...
> > Here is some information.
> >
> > http://www.ReportingServicesFAQ.com/ow.asp?ReportingWithoutVisualStudio
> >
> >
> >
> > Chris Shipley wrote:
> > > I'm really interested in Reporting Services, but I can't seem to find
> any
> > > documentation on how to write reports and utilize it without Visual
> Studio
> > > .NET 2003. Is there any way to do that?
> > >
> > > --
> > > Chris Shipley
> > > HSI System Administrator
> > > CAFCA, Inc.
> > > Hartford, CT
> > > http://www.cafca.org
> > > chriss@.crtct.org
> > >
> > >
>|||Thanks Robert.
Is there a beta version of RS 2005?
--
Chris Shipley
HSI System Administrator
CAFCA
Hartford, CT
Ph: 860-560-5455
http://www.cafca.org
chriss@.crtct.org
"Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote in message
news:uN%23$chyBFHA.1452@.TK2MSFTNGP11.phx.gbl...
> Several partners provide applications for RS 2000 that will allow the
> creation of reports without using VS.NET. Some of them provide web-based
> designers, some of them provide tools integrated e.g. into Office. You may
> want to check:
> http://www.microsoft.com/sql/reporting/partners/softwareapps.asp
> If you can wait till RS 2005, it will provide an additional report design
> tool called "Report Builder". It is a stand-alone application (independent
> of the VS.NET report designer) and is focused on end-users. It allows them
> to easily create reports without needing to design queries or writing
> expressions. Report Builder was briefly shown as one of the new features
in
> RS 2005 in this webcast:
>
http://msevents.microsoft.com/cui/eventdetail.aspx?eventid=1032263305&culture=en-us
> --
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "Chris Shipley" <chriss@.crtct.org> wrote in message
> news:%23HOyS7sBFHA.1408@.TK2MSFTNGP10.phx.gbl...
> > Hey, thanks for that! See, I have this huge database (1.3+ gb) with
over
> > 500 users, but there's no good reporting tool for them. I'm looking for
a
> > way to give them a visual report writing tool, perhaps on the web
secured
> > through SSL. But if they have to use VS.NET to create a report, its not
> > very cost effective.
> >
> > Anyone out there know of any resources I can look at to achieve my
> > objective? I'd utilize SQL Reporting Services, but I don't have VS.NET
> > either. I suppose if I had to, I could learn RDL, but that doesn't seem
> an
> > effective method for users. Maybe someone out there is already writing
a
> > VS.NET program for that purpose! :)
> >
> > --
> > Chris Shipley
> > HSI System Administrator
> > CAFCA
> > Hartford, CT
> > Ph: 860-560-5455
> > http://www.cafca.org/helpdesk
> > chriss@.crtct.org
> >
> > "Jerry" <JerryNY32513@.hotmail.com> wrote in message
> > news:uvPSGLkBFHA.2568@.TK2MSFTNGP11.phx.gbl...
> > > Here is some information.
> > >
> > >
http://www.ReportingServicesFAQ.com/ow.asp?ReportingWithoutVisualStudio
> > >
> > >
> > >
> > > Chris Shipley wrote:
> > > > I'm really interested in Reporting Services, but I can't seem to
find
> > any
> > > > documentation on how to write reports and utilize it without Visual
> > Studio
> > > > .NET 2003. Is there any way to do that?
> > > >
> > > > --
> > > > Chris Shipley
> > > > HSI System Administrator
> > > > CAFCA, Inc.
> > > > Hartford, CT
> > > > http://www.cafca.org
> > > > chriss@.crtct.org
> > > >
> > > >
> >
> >
>|||Yes, there is a Beta program for SQL Server 2005 (which includes RS 2005).
More information can be found here:
http://www.microsoft.com/sql/2005/default.asp
If you are a MSDN subscriber you should also have access to SQL Server CTPs
(Community Technology Previews). Report Builder is a completely new product
and not yet available in the December CTP, but it will be included in SQL
Server 2005 Beta 3 and very likely already in one of the upcoming CTPs.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Chris Shipley" <chriss@.crtct.org> wrote in message
news:eApnSOMCFHA.328@.tk2msftngp13.phx.gbl...
> Thanks Robert.
> Is there a beta version of RS 2005?
> --
> Chris Shipley
> HSI System Administrator
> CAFCA
> Hartford, CT
> Ph: 860-560-5455
> http://www.cafca.org
> chriss@.crtct.org
> "Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote in message
> news:uN%23$chyBFHA.1452@.TK2MSFTNGP11.phx.gbl...
> > Several partners provide applications for RS 2000 that will allow the
> > creation of reports without using VS.NET. Some of them provide web-based
> > designers, some of them provide tools integrated e.g. into Office. You
may
> > want to check:
> > http://www.microsoft.com/sql/reporting/partners/softwareapps.asp
> >
> > If you can wait till RS 2005, it will provide an additional report
design
> > tool called "Report Builder". It is a stand-alone application
(independent
> > of the VS.NET report designer) and is focused on end-users. It allows
them
> > to easily create reports without needing to design queries or writing
> > expressions. Report Builder was briefly shown as one of the new features
> in
> > RS 2005 in this webcast:
> >
>
http://msevents.microsoft.com/cui/eventdetail.aspx?eventid=1032263305&culture=en-us
> >
> > --
> > This posting is provided "AS IS" with no warranties, and confers no
> rights.
> >
> > "Chris Shipley" <chriss@.crtct.org> wrote in message
> > news:%23HOyS7sBFHA.1408@.TK2MSFTNGP10.phx.gbl...
> > > Hey, thanks for that! See, I have this huge database (1.3+ gb) with
> over
> > > 500 users, but there's no good reporting tool for them. I'm looking
for
> a
> > > way to give them a visual report writing tool, perhaps on the web
> secured
> > > through SSL. But if they have to use VS.NET to create a report, its
not
> > > very cost effective.
> > >
> > > Anyone out there know of any resources I can look at to achieve my
> > > objective? I'd utilize SQL Reporting Services, but I don't have
VS.NET
> > > either. I suppose if I had to, I could learn RDL, but that doesn't
seem
> > an
> > > effective method for users. Maybe someone out there is already
writing
> a
> > > VS.NET program for that purpose! :)
> > >
> > > --
> > > Chris Shipley
> > > HSI System Administrator
> > > CAFCA
> > > Hartford, CT
> > > Ph: 860-560-5455
> > > http://www.cafca.org/helpdesk
> > > chriss@.crtct.org
> > >
> > > "Jerry" <JerryNY32513@.hotmail.com> wrote in message
> > > news:uvPSGLkBFHA.2568@.TK2MSFTNGP11.phx.gbl...
> > > > Here is some information.
> > > >
> > > >
> http://www.ReportingServicesFAQ.com/ow.asp?ReportingWithoutVisualStudio
> > > >
> > > >
> > > >
> > > > Chris Shipley wrote:
> > > > > I'm really interested in Reporting Services, but I can't seem to
> find
> > > any
> > > > > documentation on how to write reports and utilize it without
Visual
> > > Studio
> > > > > .NET 2003. Is there any way to do that?
> > > > >
> > > > > --
> > > > > Chris Shipley
> > > > > HSI System Administrator
> > > > > CAFCA, Inc.
> > > > > Hartford, CT
> > > > > http://www.cafca.org
> > > > > chriss@.crtct.org
> > > > >
> > > > >
> > >
> > >
> >
> >
>

Designing reports with Reproting Services

I'm no graphic designer but I am really struggling with making a decent looking report.

Does anyone have any ideas or tips when working with reporting services?

Hi,

I'm no expert either, but what works for me is designing a report template which has the basics, and use that as a starting place for all subsequent reports. The template contains a page footer which contains the date the report was run, the name of our office and "page # out of total pages".

Starting with this generic template I add a page header which contains the report name and any selection criteria which is input by the user at run-time. On to content: I output data in the order specified by users and use bold for all headings to increase readability. Where appropriate the Reporting Services' "groupby" feature also increases clarity by allowing you to suppress duplicate values on the print-out.

Hope this helps!

Designing Reports for SQL Reporting Services 2000

My customer has SQL Reporting Services on a server with SQL 2000 Standard Edition. The customer wants to develop his own Reporting Services reports. We have been recommending Visual Basic.NET Standard 2003 for this purpose in that it costs less than $100 (usually). However, with the advent of Visual Studio 2005, the VB.NET 2003 is becoming difficult or impossible to obtain. One option is Visual C#.NET Standard 2003 - I assume it will work - does anyone know for sure?

More importantly, when Visual C#.NET 2003 becomes unvailable, what options are left for developing RS 2000 reports?

Thanks for any suggestions.

Mark

>More importantly, when Visual C#.NET 2003 becomes unvailable, what options are left for developing RS 2000 reports?

None from Microsoft. There are third-party solutions, e.g. Cizer.

|||

You could also write a small tool that performs a RDL structure "downgrade" conversion (see e.g. http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=988366&SiteID=1). However note that RS 2000 doesn't support a number of RS 2005 features (e.g. multi value parameters).

-- Robert

designing reports

Hi - we are just putting a proposal/costs together to implement reporting
services.
Might seem daft (!), I know i need the report designer and it works with the
visual studio .net 2003, but does that mean we need to buy visual studio .net
2003 (std/dev/enterprise?) and the designer comes free? Or vice versa? Or
both!
Thanks PaulThe designer comes free with SQL Server (you need a license for SQL Server
anywhere you have the server part of RS installed). But, the designer needs
some version (any version) of VS to install into. If you don't have VS 2003
then the cheapest thing to do is to buy VB.Net ($100).
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:ED6BE78D-8085-4A3A-8F01-6BD0408734D9@.microsoft.com...
> Hi - we are just putting a proposal/costs together to implement reporting
> services.
> Might seem daft (!), I know i need the report designer and it works with
the
> visual studio .net 2003, but does that mean we need to buy visual studio
.net
> 2003 (std/dev/enterprise?) and the designer comes free? Or vice versa? Or
> both!
> Thanks Paul|||Thanks for the info Bruce! We have SQL server but are hoping to migrate from
Actuate...
"Bruce L-C [MVP]" wrote:
> The designer comes free with SQL Server (you need a license for SQL Server
> anywhere you have the server part of RS installed). But, the designer needs
> some version (any version) of VS to install into. If you don't have VS 2003
> then the cheapest thing to do is to buy VB.Net ($100).
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:ED6BE78D-8085-4A3A-8F01-6BD0408734D9@.microsoft.com...
> > Hi - we are just putting a proposal/costs together to implement reporting
> > services.
> >
> > Might seem daft (!), I know i need the report designer and it works with
> the
> > visual studio .net 2003, but does that mean we need to buy visual studio
> ..net
> > 2003 (std/dev/enterprise?) and the designer comes free? Or vice versa? Or
> > both!
> >
> > Thanks Paul
>
>|||Hi Paul,
We too are evaluating products to migrate from Actuate. I can share our
findings if you're interested.
Bill
"Paul" wrote:
> Thanks for the info Bruce! We have SQL server but are hoping to migrate from
> Actuate...
> "Bruce L-C [MVP]" wrote:
> > The designer comes free with SQL Server (you need a license for SQL Server
> > anywhere you have the server part of RS installed). But, the designer needs
> > some version (any version) of VS to install into. If you don't have VS 2003
> > then the cheapest thing to do is to buy VB.Net ($100).
> >
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> > "Paul" <Paul@.discussions.microsoft.com> wrote in message
> > news:ED6BE78D-8085-4A3A-8F01-6BD0408734D9@.microsoft.com...
> > > Hi - we are just putting a proposal/costs together to implement reporting
> > > services.
> > >
> > > Might seem daft (!), I know i need the report designer and it works with
> > the
> > > visual studio .net 2003, but does that mean we need to buy visual studio
> > ..net
> > > 2003 (std/dev/enterprise?) and the designer comes free? Or vice versa? Or
> > > both!
> > >
> > > Thanks Paul
> >
> >
> >|||Hi Paul,
I have a lot of experience with Actuate (8 years) and am currently getting
up to speed on Reporting Services.
Let me know if you'd like to exchange information/experiences on migrating
from Actuate to Reporting Services.
-- Chris
--
Chris, SSSI
"Paul" wrote:
> Thanks for the info Bruce! We have SQL server but are hoping to migrate from
> Actuate...
> "Bruce L-C [MVP]" wrote:
> > The designer comes free with SQL Server (you need a license for SQL Server
> > anywhere you have the server part of RS installed). But, the designer needs
> > some version (any version) of VS to install into. If you don't have VS 2003
> > then the cheapest thing to do is to buy VB.Net ($100).
> >
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> > "Paul" <Paul@.discussions.microsoft.com> wrote in message
> > news:ED6BE78D-8085-4A3A-8F01-6BD0408734D9@.microsoft.com...
> > > Hi - we are just putting a proposal/costs together to implement reporting
> > > services.
> > >
> > > Might seem daft (!), I know i need the report designer and it works with
> > the
> > > visual studio .net 2003, but does that mean we need to buy visual studio
> > ..net
> > > 2003 (std/dev/enterprise?) and the designer comes free? Or vice versa? Or
> > > both!
> > >
> > > Thanks Paul
> >
> >
> >|||Hi Bill,
I have a lot of experience with Actuate (8 years) and am currently getting
up to speed on Reporting Services.
Let me know if you'd like to exchange information/experiences on migrating
from Actuate to Reporting Services.
-- Chris
Chris, SSSI
"Bill" wrote:
> Hi Paul,
> We too are evaluating products to migrate from Actuate. I can share our
> findings if you're interested.
> Bill
> "Paul" wrote:
> > Thanks for the info Bruce! We have SQL server but are hoping to migrate from
> > Actuate...
> >
> > "Bruce L-C [MVP]" wrote:
> >
> > > The designer comes free with SQL Server (you need a license for SQL Server
> > > anywhere you have the server part of RS installed). But, the designer needs
> > > some version (any version) of VS to install into. If you don't have VS 2003
> > > then the cheapest thing to do is to buy VB.Net ($100).
> > >
> > >
> > > --
> > > Bruce Loehle-Conger
> > > MVP SQL Server Reporting Services
> > >
> > > "Paul" <Paul@.discussions.microsoft.com> wrote in message
> > > news:ED6BE78D-8085-4A3A-8F01-6BD0408734D9@.microsoft.com...
> > > > Hi - we are just putting a proposal/costs together to implement reporting
> > > > services.
> > > >
> > > > Might seem daft (!), I know i need the report designer and it works with
> > > the
> > > > visual studio .net 2003, but does that mean we need to buy visual studio
> > > ..net
> > > > 2003 (std/dev/enterprise?) and the designer comes free? Or vice versa? Or
> > > > both!
> > > >
> > > > Thanks Paul
> > >
> > >
> > >

Designing Report in VS.NET2003 - studio is crashing

I am using reporting services 2000 already for 2 years. This problem is occurring on 2-3 reports, I can't say exactly what is the reason.

How I use the report designer is pretty simple and consistent. Report data is usualy produced by calling a stored procedure (on SLQ server 2000), I have a few parameters. Some of Paramaters are dates with a default values calculated to give yesterday's date or today - 7 days. And that is the only code in the report itselft. As soon as I click on Yes/No paramater, that doesn't have default, and is int in proc and report, then complete Visual Studio Environment starts flashing, making bip-bip noise, and is blocked, no access and reponse. I can't close it for a while, I can't bring up task manager, and in general this crash is very much resource intensive, Outlook is freezing, can't open IE, new spreadsheet. I am working on windows Xp platform. Eventually I would manage to close VS thru task manager.

As soon as this report is uploaded to Report Manager, the place where all the reports are available to users, on different server, the same report works fine.

Has anyone experienced this and what would be the reason.

Thanks in advance,
Elizabeta R

did you get out of memory exception.. check ur database may be it contains huge data..

run the query in sql server..

Designing Date and Time Dimension

I need to display reports based on an SSAS cube which requires both date and time as input parameters.

I'd like to know what is the best practice while designing dimensions in such a scenario. Do i need to design a separate dimension for date ( quarter, year, month, week, day) and time ( hour, minute, second) ?

Or will having just the date dimension with the date key pointing to the full datetime in the fact table suffice?

I guess there is no correct answer to this question, but in my opinion you should design two dimensions - one for the dates and one for the seconds (with related attributes, of course). If you put it all in one dimension, your time dimension would consist of 60 x 60 x 24 x 365 = 31,536,000 members for each year (!!!), whereas if you make two separate dimensions, you have a date dimension with 365 members for each year and a time dimension with 86,400 members (constant). This will be much more performant and simple to work with in most cases.

Designing Crystal Report with Brought forward value

Please i am new to crystal report design even though I have been able to design some simple reports. I would appreciate your kind assistance.

I have a crystal report from VB.Net that has series of columns (fields) generated from the following two tables.

1. tblAgt - Table
fconsole (float field)
fagent
B1

2. tblDebit - Table
fmending (Date field)
ActNo
fagent
ftrans
fgrssprem (float field)
Comm
The report has the following format

Date: 01/01/2004 To 31/12/2004
Account No: 38000 To 38092

Balance bf: 00.00

fmending ActNo Fagent ftr fgrssprem Comm. Net Amt
02/02/04 38837 db001 DR 5,000.00 350.00 4,650
02/07/04 38838 db002 CR 15,000.30 200.00 15,200.30
03/02/02 29388 db003 pyt 2,000.00 100.00 1,900.00
--------------------------

The Date and the Accounts Group header were generated with the parameter fields (Range)

The Date Range (StartDate & EndDate) header
The Account Range (StartAcct & EndAcct) header

The DB or CR or Pyt entry determines whether the fgrssprm is negative or positive e.g. To get the Netamt

if ftrans = CR, then Comm is added
if ftrans = DR, then Comm is deducted

The Balance bf: is where I am having a problem with because it is suppose
to be generated from all fgrssprem that falls before StartDate eg. 1/01/2004
ie. fmending(tablefield) < startdate (parameter field)

Balance bf = sum(fgrssprem(tbldebit) is added to fconsole in table (tblagt) ie. Balance bf = fgrssprem + fconsole

I am trying to filter for all the dates that is less than startdate and to get the sum of fgrssprem is giving me 0.00 as my result.

I would be greatful if you could put me through.

Thanks

fieYou need to write a query in the Back end and design the report using that query which you should store it as Stored Procedure. What is the Database you are using?

Design without SSL, publish with SSL

Riddle me this...
Can I design my reports onto a development server that does not have SSL in
the mix and then use the rs utility to publish them to a live server that has
SSL?
Well I did but I had to change \Program Files\Microsoft SQL
Server\MSSQL\Reporting Services\ReportServer\RSReportServer.config
file with the attribute SecureConnectionLevel from 2 to 0 in order to do this.
Otherwise I received an error in the cmd window:
"The operation you are attempting requires a secure connection. (HTTPS)."
So, I can see the reports if I leave the SecureConnectionLevel setting at 0
but not when it is set to 2 (which I want to utilize SSL).Hmm.. setting SecureConnectionLevel to 0 stops rs from requireing SSL
connection at all. You don't want to do this :-).
Regariding rs.exe - you can simply specify in on your command https:// for
the path to report server. The tool should the estabilish an SSL
connection.
-Lukasz
This posting is provided "AS IS" with no warranties, and confers no rights.
"Greg Allan" <GregAllan@.discussions.microsoft.com> wrote in message
news:519624D0-C307-45EF-867A-D2F1DA8ADA78@.microsoft.com...
> Riddle me this...
> Can I design my reports onto a development server that does not have SSL
> in
> the mix and then use the rs utility to publish them to a live server that
> has
> SSL?
> Well I did but I had to change \Program Files\Microsoft SQL
> Server\MSSQL\Reporting Services\ReportServer\RSReportServer.config
> file with the attribute SecureConnectionLevel from 2 to 0 in order to do
> this.
> Otherwise I received an error in the cmd window:
> "The operation you are attempting requires a secure connection. (HTTPS)."
> So, I can see the reports if I leave the SecureConnectionLevel setting at
> 0
> but not when it is set to 2 (which I want to utilize SSL).

Design question - Change multiply reports

Hello.
This is a general design question about reports.

Is there a way to change the layout of a few report together?
I have 20 reports and i want to add a logo to all of them, can i do itin a simple way or should I open them one after the other and add thelogo to each report individually?
And if afterwards I want to change the logo? How can i do it with out open each report and change it again?

(I'm using rs2005 with Visual studio 2005).

Thanks,
Roy.

You can use the subreport, put the logo into another report, and add a subreport to each report, then if you want to change it again, just change the subreport.

|||

O.K.

thats a nice idea.

And its the only good idea i got so far :)

Thanks!

Design question - Change multiply reports

Hello.
This is a general design question about reports.

Is there a way to change the layout of a few report together?
I have 20 reports and i want to add a logo to all of them, can i do it in a simple way or should I open them one after the other and add the logo to each report individually?
And if afterwards I want to change the logo? How can i do it with out open each report and change it again?

(I'm using rs2005 with Visual studio 2005).

Thanks,
Roy.I believe you will need to open each report to add the image, but if you use an image from a database, URL, or Project, you should be able to change the image linked to and have it change on all of the reports. Be careful that the images are the same size, you could see some 'unexpected' results.

Design question - Change multiply reports

Hello.
This is a general design question about reports.
Is there a way to change the layout of a few report together?
I have 20 reports and i want to add a logo to all of them, can i do it
in a simple way or should I open them one after the other and add the
logo to each report individually?
And if afterwards I want to change the logo? How can i do it with out
open each report and change it again?
(I'm using rs2005 with Visual studio 2005).
Thanks,
Roy.What you can do is that you can create a template with logo and keep in the
report folder and when ever you are creating a new report you can use it. so
it will be consistent. Otherwise you can't replace with logo in all the
existing report automatically. Need to do it manually.
Amarnath
"nicknack" wrote:
> Hello.
> This is a general design question about reports.
> Is there a way to change the layout of a few report together?
> I have 20 reports and i want to add a logo to all of them, can i do it
> in a simple way or should I open them one after the other and add the
> logo to each report individually?
> And if afterwards I want to change the logo? How can i do it with out
> open each report and change it again?
> (I'm using rs2005 with Visual studio 2005).
> Thanks,
> Roy.
>|||Thanks.
Sad to hear it but thanks for the reply...
:)
Amarnath =D7=9B=D7=AA=D7=91:
> What you can do is that you can create a template with logo and keep in t=he
> report folder and when ever you are creating a new report you can use it.= so
> it will be consistent. Otherwise you can't replace with logo in all the
> existing report automatically. Need to do it manually.
> Amarnath
> "nicknack" wrote:
> > Hello.
> > This is a general design question about reports.
> >
> > Is there a way to change the layout of a few report together?
> > I have 20 reports and i want to add a logo to all of them, can i do it
> > in a simple way or should I open them one after the other and add the
> > logo to each report individually?
> > And if afterwards I want to change the logo? How can i do it with out
> > open each report and change it again?
> >
> > (I'm using rs2005 with Visual studio 2005).
> > > > Thanks,
> > Roy.
> > > >|||Changing an element that is already in a report is easier than to add
something new.
If you add the logo, you could do a Find and replace on all the code files,
replacing "logo.jpg" with "newlogo.jpg".
To replace the logo
Right click on a report file, and click View Code.
Start the Find / Replace control by typing ctrl+H
In the "Look in" list, select "Current Project".
Type in the name of the current logo in the "Find what" textbox, and type
the name of the new logo in the "Replace with" textbox.
Click Replace if you want to control what is changed, and Replace all if you
just want it to go through it all.
This usually opens up all the files that are changed. Remember to save them
all before testing or deploying them.
You might try to do a more expanded version of this to add a header control
as well.
In one report you add the page header and whatever you want to put there.
Make sure that the name of the textbox is very probably unique, to make sure
that no textboxes in any of your reports are called the same. Copy the code
for the whole header and all controls in it to notepad. Format your code so
that everything is on the same line. (Turn off Word Wrap before you start.)
You should end up with something like this:
<PageHeader> <ReportItems> <Textbox Name="textbox4">
<rd:DefaultName>textbox4</rd:DefaultName> <Width>6.25cm</Width>
<Style> <PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom> <PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop> </Style> <CanGrow>true</CanGrow>
<Height>0.63492cm</Height> <Value>=Globals!PageNumber</Value>
</Textbox> </ReportItems> <Height>0.75cm</Height>
<PrintOnLastPage>true</PrintOnLastPage>
<PrintOnFirstPage>true</PrintOnFirstPage> </PageHeader>
Now, replace </Body> with
</Body> <PageHeader> <ReportItems> <Textbox Name="textbox4">
<rd:DefaultName>textbox4</rd:DefaultName> <Width>6.25cm</Width>
<Style> <PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom> <PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop> </Style> <CanGrow>true</CanGrow>
<Height>0.63492cm</Height> <Value>=Globals!PageNumber</Value>
</Textbox> </ReportItems> <Height>0.75cm</Height>
<PrintOnLastPage>true</PrintOnLastPage>
<PrintOnFirstPage>true</PrintOnFirstPage> </PageHeader>
in all your reports.
Kaisa M. Lindahl Lervik
"nicknack" <roezohar@.gmail.com> wrote in message
news:1162707523.683103.312020@.f16g2000cwb.googlegroups.com...
> Hello.
> This is a general design question about reports.
> Is there a way to change the layout of a few report together?
> I have 20 reports and i want to add a logo to all of them, can i do it
> in a simple way or should I open them one after the other and add the
> logo to each report individually?
> And if afterwards I want to change the logo? How can i do it with out
> open each report and change it again?
> (I'm using rs2005 with Visual studio 2005).
> Thanks,
> Roy.
>|||You can also leave the logo file name the same - "logo.jpg" just change
the jpg :)
(Maybe thats just too obvious)
Kaisa M. Lindahl Lervik wrote:
> Changing an element that is already in a report is easier than to add
> something new.
> If you add the logo, you could do a Find and replace on all the code files,
> replacing "logo.jpg" with "newlogo.jpg".
> To replace the logo
> Right click on a report file, and click View Code.
> Start the Find / Replace control by typing ctrl+H
> In the "Look in" list, select "Current Project".
> Type in the name of the current logo in the "Find what" textbox, and type
> the name of the new logo in the "Replace with" textbox.
> Click Replace if you want to control what is changed, and Replace all if you
> just want it to go through it all.
> This usually opens up all the files that are changed. Remember to save them
> all before testing or deploying them.
> You might try to do a more expanded version of this to add a header control
> as well.
> In one report you add the page header and whatever you want to put there.
> Make sure that the name of the textbox is very probably unique, to make sure
> that no textboxes in any of your reports are called the same. Copy the code
> for the whole header and all controls in it to notepad. Format your code so
> that everything is on the same line. (Turn off Word Wrap before you start.)
> You should end up with something like this:
> <PageHeader> <ReportItems> <Textbox Name="textbox4">
> <rd:DefaultName>textbox4</rd:DefaultName> <Width>6.25cm</Width>
> <Style> <PaddingLeft>2pt</PaddingLeft>
> <PaddingBottom>2pt</PaddingBottom> <PaddingRight>2pt</PaddingRight>
> <PaddingTop>2pt</PaddingTop> </Style> <CanGrow>true</CanGrow>
> <Height>0.63492cm</Height> <Value>=Globals!PageNumber</Value>
> </Textbox> </ReportItems> <Height>0.75cm</Height>
> <PrintOnLastPage>true</PrintOnLastPage>
> <PrintOnFirstPage>true</PrintOnFirstPage> </PageHeader>
> Now, replace </Body> with
> </Body> <PageHeader> <ReportItems> <Textbox Name="textbox4">
> <rd:DefaultName>textbox4</rd:DefaultName> <Width>6.25cm</Width>
> <Style> <PaddingLeft>2pt</PaddingLeft>
> <PaddingBottom>2pt</PaddingBottom> <PaddingRight>2pt</PaddingRight>
> <PaddingTop>2pt</PaddingTop> </Style> <CanGrow>true</CanGrow>
> <Height>0.63492cm</Height> <Value>=Globals!PageNumber</Value>
> </Textbox> </ReportItems> <Height>0.75cm</Height>
> <PrintOnLastPage>true</PrintOnLastPage>
> <PrintOnFirstPage>true</PrintOnFirstPage> </PageHeader>
> in all your reports.
> Kaisa M. Lindahl Lervik
>
> "nicknack" <roezohar@.gmail.com> wrote in message
> news:1162707523.683103.312020@.f16g2000cwb.googlegroups.com...
> > Hello.
> > This is a general design question about reports.
> >
> > Is there a way to change the layout of a few report together?
> > I have 20 reports and i want to add a logo to all of them, can i do it
> > in a simple way or should I open them one after the other and add the
> > logo to each report individually?
> > And if afterwards I want to change the logo? How can i do it with out
> > open each report and change it again?
> >
> > (I'm using rs2005 with Visual studio 2005).
> >
> > Thanks,
> > Roy.
> >

Design question

We currently have an ASP.NET app that includes some reports. I'm
considering
making the data available in RS and I'm looking for some opinions
about
the feasibility and difficulty in doing this.
The app uses ODBC to connect to DB2. The users use their DB2 userid
and password, and this is what is used in the connection string.
The current solution includes a lot of manipulation of datatables
which are then bound to ASP.NET datagrids.
My thought was to create a web service that would serve as an RS Data
Extension, but I'm just taking a first look at this and don't know how
practical it would be. The idea is that the data retrieval and
manipulation logic could be in the web service, which would return a
dataset (or whatever).
The solution would have to include drilling down / linking to other
reports, so the userid and password would have to be maintained behind
the scenes so they could be passed, along with other parameters, to
the web service for each report/subreport.
Any thoughts are welcome.
TIA,
JimSounds like a winner to me :-). It doesn't have to be a web service of
course, you can write a data extension which would itself query your data
source and massage the data.
--
Hope this helps.
----
Teo Lachev, MCSD, MCT
Author: "Microsoft Reporting Services in Action"
Publisher website: http://www.manning.com/lachev
Buy it from Amazon.com: http://shrinkster.com/eq
Home page and blog: http://www.prologika.com/
----
"jim corey" <jhcorey@.yahoo.com> wrote in message
news:1c4f8dcf.0409210702.587ac17d@.posting.google.com...
> We currently have an ASP.NET app that includes some reports. I'm
> considering
> making the data available in RS and I'm looking for some opinions
> about
> the feasibility and difficulty in doing this.
> The app uses ODBC to connect to DB2. The users use their DB2 userid
> and password, and this is what is used in the connection string.
> The current solution includes a lot of manipulation of datatables
> which are then bound to ASP.NET datagrids.
> My thought was to create a web service that would serve as an RS Data
> Extension, but I'm just taking a first look at this and don't know how
> practical it would be. The idea is that the data retrieval and
> manipulation logic could be in the web service, which would return a
> dataset (or whatever).
> The solution would have to include drilling down / linking to other
> reports, so the userid and password would have to be maintained behind
> the scenes so they could be passed, along with other parameters, to
> the web service for each report/subreport.
> Any thoughts are welcome.
> TIA,
> Jim

Design Question

I am just getting into SQL reporting services and I am looking into upgrading some Access reports with SQL reporting services. In the Access reports there was an interface that allowed the user to select some different options and then generate the report based on some of those options. To generate the report there was a large amount of vba code that created some collector tables that were then used by the report. My question is what would be the best way to recreate this functionality in reporting services. Would it be best to just have an external assembly that I called and passed in the parameters, this assembly would then create and a populate any tables on the sql server that could then be used by the report to display the data? If this is the case how would I go about connecting to the database? Or would it be better to create a custom data processing extension to do this? I have not looked into the data processing extensions much yet so I am not sure if it is even possible to do this.
Thanks for your suggestions
--
Message posted via http://www.sqlmonster.comI am assuming here that the data you are reporting against is in SQL Server.
The way you have solved it today in Access essentially allows you to have
your own temporary tables. Your recordsets in your VBA code acted as
temporary tables. I think you would be better off to create a stored
procedure than to go either of the paths you mention below. A custom data
processing extension is not easy. Doable but non-trivial. One point, when
creating a stored procedure don't try to do a port of your vba code, rewrite
it. Your VBA code is most likely cursor based (walking through recordsets).
When doing SP you should try to do set based logic. Fill in temp tables,
doing joins with the temp table etc. I once took 64 pages of C code and
turned it into 2 pages of a stored procedure. You can use cursors in SP but
that should be a last resort.
With the next beta of Widbey their will be winform and webform controls that
might make this easier.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Jason DeWeeese via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:6626b9ec55f6440c865863d7d4c984dd@.SQLMonster.com...
> I am just getting into SQL reporting services and I am looking into
upgrading some Access reports with SQL reporting services. In the Access
reports there was an interface that allowed the user to select some
different options and then generate the report based on some of those
options. To generate the report there was a large amount of vba code that
created some collector tables that were then used by the report. My question
is what would be the best way to recreate this functionality in reporting
services. Would it be best to just have an external assembly that I called
and passed in the parameters, this assembly would then create and a populate
any tables on the sql server that could then be used by the report to
display the data? If this is the case how would I go about connecting to the
database? Or would it be better to create a custom data processing extension
to do this? I have not looked into the data processing extensions much yet
so I am not sure if it is even possible to do this.
> Thanks for your suggestions
> --
> Message posted via http://www.sqlmonster.com|||Thanks for your suggestion, that is one other alternative that I had briefly considered, but had not really looked into. Thanks
--
Message posted via http://www.sqlmonster.com

Design of 'age' ( dimension ? )

my reports are run over a period of time of certain dates, let s say :

Period 1) DateStartPeriod1 - DateEndPeridod1

Period 2) DateStartPeriod1 - DateEndPeridod1

Period n ) .....

The subject is born in DBORN, so when i need to rollup to the age at the end of the period 1 it is going to be

DateEndPeriod1 - DBORN and the same when the second date is used, i.e. DateEndPeridod1 - DBORN.

QUESTION : How do i model this in Analasys services ? In other words how do i explain AS that when i use Period 1

on the columns i want the age DateEndPeriod1 - DBORN on the rows

I tried to use calculated memebrs AgePeriod1 and AgePeriod2 ... something like

MEMBER AgendPeriod1 as 'DateEndPeriod1 - DBORN'

but it does not seem to work.

I m pretty sure that the answer is straightforward but because i m new to OLAP i just can t think of it.

Thanks

Lui

One possible solution: define a calculated measure.

Formula could be the following:

'[Period].CurrentMember.MemberValue - [Subject].[LastLevel].CurrentMember.Properties("DBORN")'

When designing your cube, make sure ValueColumn for the [Period] hierarchy points to a relational column that has 'date' type, and actually contains the date for the end of each period.

I am also assuming you will have a hierarchy named [Subject], and each member at the last level of this hierarchy has a member property called DBORN, that is also of type date.