Showing posts with label instead. Show all posts
Showing posts with label instead. Show all posts

Tuesday, March 20, 2012

Reports on a stored procedure result-set instead of table

Hi all,
Is it possible to run reports on stored procedure result-set instead of a
table or view? If so, how can it be done.
AviOn Nov 25, 9:26 am, "Avi" <m...@.m.com> wrote:
> Hi all,
> Is it possible to run reports on stored procedure result-set instead of a
> table or view? If so, how can it be done.
> Avi
Yes. If you are using the Report Wizard, first use the main query in
the stored procedure to get you started and then once you have
finished creating the report, select the Data tab and change the
'Command type:' from 'Text' to 'StoredProcedure' and replace the query
in the window with the stored procedure name. Then select the Refresh
button and exclamation point (!) to run it. The Report Wizard was just
created this way (most likely due to the query having a set number of
columns and structure and a stored procedure not having this until it
is ran). Otherwise, the same kind of process can be used if the report
has already been created. Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant|||Don't forget to set your parameters if your stored proc has any. To do so,
go to the parameters tab in the edit dataset window.
--
Alain Quesnel
alainsansspam@.logiquel.com
www.logiquel.com
"EMartinez" <emartinez.pr1@.gmail.com> wrote in message
news:4d7d840c-981f-4d61-985f-29ee99d75df9@.l1g2000hsa.googlegroups.com...
> On Nov 25, 9:26 am, "Avi" <m...@.m.com> wrote:
>> Hi all,
>> Is it possible to run reports on stored procedure result-set instead of a
>> table or view? If so, how can it be done.
>> Avi
>
> Yes. If you are using the Report Wizard, first use the main query in
> the stored procedure to get you started and then once you have
> finished creating the report, select the Data tab and change the
> 'Command type:' from 'Text' to 'StoredProcedure' and replace the query
> in the window with the stored procedure name. Then select the Refresh
> button and exclamation point (!) to run it. The Report Wizard was just
> created this way (most likely due to the query having a set number of
> columns and structure and a stored procedure not having this until it
> is ran). Otherwise, the same kind of process can be used if the report
> has already been created. Hope this helps.
> Regards,
> Enrique Martinez
> Sr. Software Consultant|||If you use temp tables here is a handy list on how to write your SP to use
temp tables in a RS compatible way:
1. Click on the refresh fields button (to the right of the ...) if your
field list is not showing up.
2. Do not use set nocount on
3. Do not explicitly drop the temp tables. Let the temp tables just fall out
of scope. SQL Server will properly dispose of them when they are no longer
needed. I think people explicitly drop them due to habit. Most likely at one
time it was the proper thing to do. It is not necessary and if you drop them
then stored procedures will not work with RS.
4. Have your last statement be a select
If none of these work then add Set FMTONLY Off (the below is from Simon
Sabin a SQL Server MVP). Here is his explanation: "The issue with RS is that
the rowset of the SP is defined by calling the SP with SET FMTONLY ON
because Temp tables don't get created if you select from the temp table the
metadata from the rowset can't be returned. This can be worked around by
turning FMTONLY OFF in the SP."
I have found this to only be an issue when you create a temp table in your
stored procedure that is then filled with data from another stored
procedure.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Avi" <m@.m.com> wrote in message news:fic421$8h3$1@.news2.netvision.net.il...
> Hi all,
> Is it possible to run reports on stored procedure result-set instead of a
> table or view? If so, how can it be done.
> Avi
>

Monday, March 12, 2012

Reports in an application

I have VS2003, I can create reports but am not sure how to run them in a
stand alone fashion ie someone just goes to a URL instead of having to use a
reporting server to display the reports.
Does any one know how to do this, thanks for any help
Sammy.You mean just URL from where you will give the URL ? so two ways
1. Just give the URL each and everytime when they want to access.
(e.g. http://servername/reportserver?/SampleReports/Employee Sales
Summary&rs:Command=Render&rs:format=HTML4.0 )
You can go to the help for various options you can give along with URL.
search for "URL access" in the help.
2. Create a report page with "Action" as URL so something like menu of
reports, will be available for the users to select.
Amarnath
"Sammy" wrote:
> I have VS2003, I can create reports but am not sure how to run them in a
> stand alone fashion ie someone just goes to a URL instead of having to use a
> reporting server to display the reports.
> Does any one know how to do this, thanks for any help
> Sammy.|||I think Sammy is thinking the VS2003 is the report server.
Sammy, are you deploying the reports to a webserver, as this will also
give you an URL for users to click. As well as the navigation from
http://servername/reportserver
I'll shut up now.
Amarnath wrote:
> You mean just URL from where you will give the URL ? so two ways
> 1. Just give the URL each and everytime when they want to access.
> (e.g. http://servername/reportserver?/SampleReports/Employee Sales
> Summary&rs:Command=Render&rs:format=HTML4.0 )
> You can go to the help for various options you can give along with URL.
> search for "URL access" in the help.
> 2. Create a report page with "Action" as URL so something like menu of
> reports, will be available for the users to select.
> Amarnath
> "Sammy" wrote:
> > I have VS2003, I can create reports but am not sure how to run them in a
> > stand alone fashion ie someone just goes to a URL instead of having to use a
> > reporting server to display the reports.
> >
> > Does any one know how to do this, thanks for any help
> >
> > Sammy.|||Well I wanted to be able to just copy the files the report uses straight into
the wwwroot$ web app ...but have installed report serverand uploaded the rdl
file there and have now used the report server URL in the app instead
"VB" wrote:
> I think Sammy is thinking the VS2003 is the report server.
> Sammy, are you deploying the reports to a webserver, as this will also
> give you an URL for users to click. As well as the navigation from
> http://servername/reportserver
> I'll shut up now.
>
> Amarnath wrote:
> > You mean just URL from where you will give the URL ? so two ways
> >
> > 1. Just give the URL each and everytime when they want to access.
> > (e.g. http://servername/reportserver?/SampleReports/Employee Sales
> > Summary&rs:Command=Render&rs:format=HTML4.0 )
> >
> > You can go to the help for various options you can give along with URL.
> > search for "URL access" in the help.
> >
> > 2. Create a report page with "Action" as URL so something like menu of
> > reports, will be available for the users to select.
> >
> > Amarnath
> >
> > "Sammy" wrote:
> >
> > > I have VS2003, I can create reports but am not sure how to run them in a
> > > stand alone fashion ie someone just goes to a URL instead of having to use a
> > > reporting server to display the reports.
> > >
> > > Does any one know how to do this, thanks for any help
> > >
> > > Sammy.
>

Reports based on OLAP?

We have a large number of (Crystal) Reports ( based on Stor Proc ) running
against our OLTP database (SQL Server).
Instead of reports running against OLTP database I would like to create
Cubes and create production reports against them.
Is it possible to do that? Are there any client applications which allows to
create these types of reports? We don't need real time information.
Any help is greatly appreciated.Is it possible to do that?
--Yes
Are there any client applications which allows to create these types of
reports?
--Yes, you create the cubes in Analysis Services and you can use Excel Pivot
tables to connect, then if you are looking for more functionality and better
graphing capability you have a whole host of options. Search on google for
OLAP viewers, there's a bunch. I use ProClarity (www.proclarity.com )
You will want to look into using DTS to copy your data to a store where you
can build your cubes off.
HTH
Ray Higdon MCSE, MCDBA, CCNA
--
"Atul Mehta" <amehta@.oaktreecap.com> wrote in message
news:u%23ov$HCvDHA.1744@.TK2MSFTNGP12.phx.gbl...
quote:

> We have a large number of (Crystal) Reports ( based on Stor Proc ) running
> against our OLTP database (SQL Server).
> Instead of reports running against OLTP database I would like to create
> Cubes and create production reports against them.
> Is it possible to do that? Are there any client applications which allows

to
quote:

> create these types of reports? We don't need real time information.
> Any help is greatly appreciated.
>

Monday, February 20, 2012

reporting services works over internet ?

Is it possible to access reports from internet, using a public address like
http://www.mydomain.com/reports instead of http://myserver/reports ? How can
I do it ?
Thanks
MorenoChange the <ReportServerUrl> entry in C:\Program Files\Microsoft SQL
Server\MSSQL\Reporting Services\ReportManager\RSWebApplication.config
-Steve
"Moreno" <movendra@.yahoo.com> wrote in message
news:%23$u5fFQXEHA.3716@.TK2MSFTNGP11.phx.gbl...
> Is it possible to access reports from internet, using a public address
like
> http://www.mydomain.com/reports instead of http://myserver/reports ? How
can
> I do it ?
> Thanks
> Moreno
>|||I did that, restarted Reporting services as well. But doesnt work.
It says "Directory Listing Denied"
"Stephen Walch" wrote:
> Change the <ReportServerUrl> entry in C:\Program Files\Microsoft SQL
> Server\MSSQL\Reporting Services\ReportManager\RSWebApplication.config
> -Steve
> "Moreno" <movendra@.yahoo.com> wrote in message
> news:%23$u5fFQXEHA.3716@.TK2MSFTNGP11.phx.gbl...
> > Is it possible to access reports from internet, using a public address
> like
> > http://www.mydomain.com/reports instead of http://myserver/reports ? How
> can
> > I do it ?
> >
> > Thanks
> > Moreno
> >
> >
>
>|||Hello,
Have you tried the url http://www.mydomain.com/reportserver ?
you will be independant of web application parameters ReportManager.
Have you set Directory Security of your Web Application under IIS ?
Is your client authenticated against your application ?
HTH
Jerome Berthaud (MCSD)
http://www.winsight.fr
"AbiBaby" <AbiBaby@.discussions.microsoft.com> wrote in message
news:05359046-2E28-4402-BED3-8F3F3FC70B1A@.microsoft.com...
> I did that, restarted Reporting services as well. But doesnt work.
> It says "Directory Listing Denied"
> "Stephen Walch" wrote:
> > Change the <ReportServerUrl> entry in C:\Program Files\Microsoft SQL
> > Server\MSSQL\Reporting Services\ReportManager\RSWebApplication.config
> >
> > -Steve
> >
> > "Moreno" <movendra@.yahoo.com> wrote in message
> > news:%23$u5fFQXEHA.3716@.TK2MSFTNGP11.phx.gbl...
> > > Is it possible to access reports from internet, using a public address
> > like
> > > http://www.mydomain.com/reports instead of http://myserver/reports ?
How
> > can
> > > I do it ?
> > >
> > > Thanks
> > > Moreno
> > >
> > >
> >
> >
> >|||Jerome,
I have the same problem in earlier post of 10/15 that received no replies.
I've had 5 folks email me with the same problem.
I have Server2003/SQL2000/IIS6 and have just installed Reporting services. I
have two websites on the server. Access to reports is fine using the
http://servername/reportServer, and using IUSR_servername, but even when
adding the following:
<Configuration>
<UI>
<ReportServerUrl>http://servername/ReportServer</ReportServerUrl
<ReportServerExternalURL>http://www.domain.org/ReportServer</ReportServerExternalURL>
</UI>
and
I've added virtual directories under above domain for reportManager and
reportServer giving appropriate read/execute permissions and security under
IIS to IUSR account only - no clear no windows authentication.
I continue to get: HTTP Error 403.8 - Forbidden
I've reset permission on website with ISUR account, flushed dns on
webserver, and flushed dns on domain controller.
Multiple websites continue to work fine, but same error whenever I try to
use a domain instead of server name.
Thanks,
Janet
"Jerome BERTHAUD" wrote:
> Hello,
> Have you tried the url http://www.mydomain.com/reportserver ?
> you will be independant of web application parameters ReportManager.
> Have you set Directory Security of your Web Application under IIS ?
> Is your client authenticated against your application ?
> HTH
> Jerome Berthaud (MCSD)
> http://www.winsight.fr
> "AbiBaby" <AbiBaby@.discussions.microsoft.com> wrote in message
> news:05359046-2E28-4402-BED3-8F3F3FC70B1A@.microsoft.com...
> > I did that, restarted Reporting services as well. But doesnt work.
> > It says "Directory Listing Denied"
> >
> > "Stephen Walch" wrote:
> >
> > > Change the <ReportServerUrl> entry in C:\Program Files\Microsoft SQL
> > > Server\MSSQL\Reporting Services\ReportManager\RSWebApplication.config
> > >
> > > -Steve
> > >
> > > "Moreno" <movendra@.yahoo.com> wrote in message
> > > news:%23$u5fFQXEHA.3716@.TK2MSFTNGP11.phx.gbl...
> > > > Is it possible to access reports from internet, using a public address
> > > like
> > > > http://www.mydomain.com/reports instead of http://myserver/reports ?
> How
> > > can
> > > > I do it ?
> > > >
> > > > Thanks
> > > > Moreno
> > > >
> > > >
> > >
> > >
> > >
>
>