Queries in mulitiple places

I have many reports in one project and I am using the same query in a few of
the reports. How can I set the query in one place and have my report call
that query? Is that possible? If so, does anyone have an example?Yes, make the query a stored procedure.
Rodney
"David" <dilworth@.newsgroups.nospam> wrote in message
news:AB449D8E-A300-48A8-A3F7-4F8E275BD1DF@.microsoft.com...
>I have many reports in one project and I am using the same query in a few
>of
> the reports. How can I set the query in one place and have my report call
> that query? Is that possible? If so, does anyone have an example?
>|||Unfortunately the answer is no. I too have queries I use multiple places,
especially for parameter lists. The best you can do is create a stored
procedure and use that.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"David" <dilworth@.newsgroups.nospam> wrote in message
news:AB449D8E-A300-48A8-A3F7-4F8E275BD1DF@.microsoft.com...
>I have many reports in one project and I am using the same query in a few
>of
> the reports. How can I set the query in one place and have my report call
> that query? Is that possible? If so, does anyone have an example?
>|||Hi David,
As other members have suggested, we may consider encapsulate the query in a
store procedure to reuse it. As RS report layer, we can only reuse a
certain DataSource/DataSet rather than an individual query.
Regards,
Steven Cheng
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)

queries and reporting from corporative database non IT staff

Hi! we have no Business Intelligence solution in my job and the general manager asked for a query and reports system where he (and other section managers and non IT staff) could query the corporative database (we work in healthcare, so it's mostly about patients, clinic histories, treatments) so I will propose them SQL SERVER 2005. I'll tell you my general idea and maybe you could tell me if I'm right: Through Integration Services we will load our data from the corporative database (it's Borland's InterBase) and generate a datawarehouse (now we don't have a datawarehouse) then through Reporting Services we can query this datawarehouse to generate different reports (this reports can be generated for non-technical staff, but staff who knows quite a lot about for example the different treatments that different groups of patients follow, so this staff may want to query about which patients followed which treatment in a period of time and generate by himself a report about that). And through Analysis Services we could (in a future) generate some OLAP solutions, Data Mining etc

But for the query and report system it could be enough to start with Integration Services and Reporting Services? it's very important for this system to work efficiently in terms of time: the non-technical staff generates the query and in seconds receives the report result... some of this non IT staff who will use the system knows exactly which information we house in each InterBase table, so through this system they would like to generate their own queries and reports (this is a different level of non-IT staff who is in between a Medical doctor and a Software engineer, this staff does have some technical background specially in SQL.

So my idea is to have an SQL SERVER 2005 to which this staff gets connected via LAN (all this system runs locally in one corporative place, will not run through Internet) feeded by the InterBase corporative database and delivering efficiently queries and reports... is this possible? could I test this using the trial free 6 months version? and after that if everything's working fine how much will I have to pay for 15 persons to access the SQL SERVER 2005 for keeping using the system?

This sounds like a good solution. I believe there are three stages of data presentation at a company: Data storage and retrieval, a good reporting strategy, and then Business Intelligence. SQL Server 2005 contains all the tools you need to do all three. The engine (and good application programming) handles number one, SSIS and Reporting Services handles number two, and the new Analysis Services handles number three.

You can read more about what I think on this here:

http://www.informit.com/guides/content.asp?g=sqlserver&seqNum=159&rl=1

If this answers your question, make sure to mark it "answered" so that others can quickly locate it.

Buck Woody
http://www.buckwoody.com

|||

Thanks!! just one question to get the general idea: in the Data storage and retrieval part is when you create the datawarehouse? so the original data is somehow "rearranged" for the Reporting Services to query the data more efficiently..

I don't understand how the data is queried efficiently

|||

Good question - if you'll check out my series of articles I referenced in the last post, you'll see that the data warehouse is a different structure than your data store. I think you can get the reports you're looking for out of the ODS layer I talk about. Check out those articles and I think you'll find what you're looking for.

Buck

Quarter() function in SSRS

I am uisng VS 2005 to build reports for SQL 2005. The problem is with getting the Quarter() function, mentioned in the BOL, to work in the report builder. When I build an expression and try to use the function as noted in the BOL I receive an error stating that the name "Quarter" is not declaired. When I use the expression builder to build the expression the function is not available in the builder, which is probably why I get the error.

I am assuming there is some sort of service pack of hot fix I need to apply to my VS but have not been able to locate which one. I have verified that I have all of the recent service packs applied for VS and SQL server 2005 but I am still unable to see/use this function.

Any help would be greatly appreciated. I need this function for my financial reports.

Using the QUARTER(datetime) function does not seem to be available in SQL 2005 SSRS;

=Quarter(today())

Results in an error.

After playing with the date part function I was able to resolve my problem. If you are having a similar problem use the following function to extract the quarter from a date

=DatePart(DateInterval.Quarter,today())

Replace Today() with the date you want to get the value for. You can test this by creating a report and placing a textbox anywhere on the report and setting the above code as the expression for the value of the textbox.

Q: sample reports

Hello,
I am trying to learn and understand what can be done in reporting services,
is there any site I can download sample professional reports with reporting
services?
Thanks,Jim,
Take a look at
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSAMPLES/htm/rss_samplereports_v1_9pmb.asp.
Andrew Watt
MVP - InfoPath
On Thu, 13 Oct 2005 21:21:28 -0700, JIM.H.
<JIMH@.discussions.microsoft.com> wrote:
>Hello,
>I am trying to learn and understand what can be done in reporting services,
>is there any site I can download sample professional reports with reporting
>services?
>Thanks,

Q: run reports in another machine

Hello,
Can I deploy my reports to another machine which does not have a SQL Server
installed and run it from there?
Thanks,
Jim.Yes. You can install RS on a server that has IIS installed. Then point the
new RS installation to use your designated SQL Server.
--
| Thread-Topic: run reports in another machine
| thread-index: AcVwFODsaBcOS7MHRumvBdJ2Zzyw7w==| X-WBNR-Posting-Host: 216.26.230.88
| From: =?Utf-8?B?SklNLkgu?= <JIMH@.discussions.microsoft.com>
| Subject: Q: run reports in another machine
| Date: Mon, 13 Jun 2005 05:39:02 -0700
| Lines: 8
| Message-ID: <AF088332-758D-4D91-AF56-AE22AB606213@.microsoft.com>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="Utf-8"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Content-Class: urn:content-classes:message
| Importance: normal
| Priority: normal
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| Newsgroups: microsoft.public.sqlserver.reportingsvcs
| NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
| Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGXA03.phx.gbl
| Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.reportingsvcs:45811
| X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
|
| Hello,
|
| Can I deploy my reports to another machine which does not have a SQL
Server
| installed and run it from there?
|
| Thanks,
| Jim.
|
|

q: reporting service in SQL 2005

Hello,
I installed SQL Server 2005 into a Windows 2005 Pro machine, will I be able
to use Reporting service to deploy the reports here? Is there any web site
that show step by step how I can do that?
Thanks,On Sun, 21 May 2006 04:59:02 -0700, JIM.H.
<JIMH@.discussions.microsoft.com> wrote:

>Hello,
>I installed SQL Server 2005 into a Windows 2005 Pro machine, will I be able
>to use Reporting service to deploy the reports here? Is there any web site
>that show step by step how I can do that?
>Thanks,
Jim,
There is no Windows 2005. I guess you mean Windows XP Pro.
XP Pro is really appropriate only for a development machine for SQL
Server 2005.There are technical and potential licensing issues.
Questions on Reporting Services are better directed to the
microsoft.public.sqlserver.reportingsvcs newsgroup.
There is series of webcasts under way on Reporting Services 2005 at
http://www.microsoft.com/events.
Andrew Watt MVP

q: reporting service in SQL 2005

Hello,
I installed SQL Server 2005 into a Windows 2005 Pro machine, will I be able
to use Reporting service to deploy the reports here? Is there any web site
that show step by step how I can do that?
Thanks,On Sun, 21 May 2006 04:59:02 -0700, JIM.H.
<JIMH@.discussions.microsoft.com> wrote:
>Hello,
>I installed SQL Server 2005 into a Windows 2005 Pro machine, will I be able
>to use Reporting service to deploy the reports here? Is there any web site
>that show step by step how I can do that?
>Thanks,
Jim,
There is no Windows 2005. I guess you mean Windows XP Pro.
XP Pro is really appropriate only for a development machine for SQL
Server 2005.There are technical and potential licensing issues.
Questions on Reporting Services are better directed to the
microsoft.public.sqlserver.reportingsvcs newsgroup.
There is series of webcasts under way on Reporting Services 2005 at
http://www.microsoft.com/events.
Andrew Watt MVP

Q: reporthPath

Hello,
I keep my reports c:\Inetpub\wwwroot\MyApp\Reports\ folder, how should I set
my ReportPath for report viewer to see it in my APS.NET application.
Thanks,Are you using RS 2000 or 2005.
If 2005 are you doing local or server mode?
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:57D67BC8-7279-46D7-A8DA-CD8E88F6DB49@.microsoft.com...
> Hello,
> I keep my reports c:\Inetpub\wwwroot\MyApp\Reports\ folder, how should I
> set
> my ReportPath for report viewer to see it in my APS.NET application.
> Thanks,
>|||RS 2000. Can I use RS2005 in my SQL Server 2000?
"Bruce L-C [MVP]" wrote:
> Are you using RS 2000 or 2005.
> If 2005 are you doing local or server mode?
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
> news:57D67BC8-7279-46D7-A8DA-CD8E88F6DB49@.microsoft.com...
> > Hello,
> >
> > I keep my reports c:\Inetpub\wwwroot\MyApp\Reports\ folder, how should I
> > set
> > my ReportPath for report viewer to see it in my APS.NET application.
> > Thanks,
> >
> >
>
>|||The reason I asked is if you are using 2000 then you have a misconception.
You say that your reports are in a particular folder. That statement makes
no sense with 2000. Report RDL files exist in the development environment
(which is no your web server). Then you deploy to RS. The act of deploying
has RS stored the reports in the database. No rdl files exist on the server.
Then to integrate with your app you use URL integration or soap (web
services). The report viewer is an example on how to do this. I believe it
uses URL integration.
VS 2005 has a webform and winform controls. Highly functional versions that
use web services rather than URL integration. These controls require RS
2005. RS 2005 can use SQL Server 2000 for its object/metadata store but you
still have to have a 2005 license.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:D55A0051-48F4-48FC-899A-506BFF964AAD@.microsoft.com...
> RS 2000. Can I use RS2005 in my SQL Server 2000?
> "Bruce L-C [MVP]" wrote:
>> Are you using RS 2000 or 2005.
>> If 2005 are you doing local or server mode?
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
>> news:57D67BC8-7279-46D7-A8DA-CD8E88F6DB49@.microsoft.com...
>> > Hello,
>> >
>> > I keep my reports c:\Inetpub\wwwroot\MyApp\Reports\ folder, how should
>> > I
>> > set
>> > my ReportPath for report viewer to see it in my APS.NET application.
>> > Thanks,
>> >
>> >
>>|||Hi Bruce,
Thank you very much for your response. This part is a little bit confusing
for me. I know I can set ServerUrl and ReportPath properties to render the
report.
string serverUrl="http://MyServer/ReportServer";
this.ReportViewer1.ServerUrl=serverUrl;
this.ReportViewer1.ReportPath="/RepName";
This was working. Now I moved all the report under a folder called â'Reportsâ'
at the same level of RepName folder. So I am assuming
string serverUrl="http://MyServer/ReportServer";
this.ReportViewer1.ServerUrl=serverUrl;
this.ReportViewer1.ReportPath="/Reports/RepName";
this should work. How ever I get error rsItemNotFound. What am I missing?
Thanks,
"Bruce L-C [MVP]" wrote:
> The reason I asked is if you are using 2000 then you have a misconception.
> You say that your reports are in a particular folder. That statement makes
> no sense with 2000. Report RDL files exist in the development environment
> (which is no your web server). Then you deploy to RS. The act of deploying
> has RS stored the reports in the database. No rdl files exist on the server.
> Then to integrate with your app you use URL integration or soap (web
> services). The report viewer is an example on how to do this. I believe it
> uses URL integration.
> VS 2005 has a webform and winform controls. Highly functional versions that
> use web services rather than URL integration. These controls require RS
> 2005. RS 2005 can use SQL Server 2000 for its object/metadata store but you
> still have to have a 2005 license.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
> news:D55A0051-48F4-48FC-899A-506BFF964AAD@.microsoft.com...
> > RS 2000. Can I use RS2005 in my SQL Server 2000?
> >
> > "Bruce L-C [MVP]" wrote:
> >
> >> Are you using RS 2000 or 2005.
> >>
> >> If 2005 are you doing local or server mode?
> >>
> >>
> >> --
> >> Bruce Loehle-Conger
> >> MVP SQL Server Reporting Services
> >>
> >> "JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
> >> news:57D67BC8-7279-46D7-A8DA-CD8E88F6DB49@.microsoft.com...
> >> > Hello,
> >> >
> >> > I keep my reports c:\Inetpub\wwwroot\MyApp\Reports\ folder, how should
> >> > I
> >> > set
> >> > my ReportPath for report viewer to see it in my APS.NET application.
> >> > Thanks,
> >> >
> >> >
> >>
> >>
> >>
>
>|||How did you move the reports. The only way I know that works for this is to
deploy into another folder. Did you go through a deploy process?
I think what you need to do is to first get everything organized and check
that it is working with Report Manager first.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:B79A3821-90E3-4C7E-8527-D175E43FC6BA@.microsoft.com...
> Hi Bruce,
> Thank you very much for your response. This part is a little bit confusing
> for me. I know I can set ServerUrl and ReportPath properties to render the
> report.
> string serverUrl="http://MyServer/ReportServer";
> this.ReportViewer1.ServerUrl=serverUrl;
> this.ReportViewer1.ReportPath="/RepName";
> This was working. Now I moved all the report under a folder called
> "Reports"
> at the same level of RepName folder. So I am assuming
> string serverUrl="http://MyServer/ReportServer";
> this.ReportViewer1.ServerUrl=serverUrl;
> this.ReportViewer1.ReportPath="/Reports/RepName";
> this should work. How ever I get error rsItemNotFound. What am I missing?
> Thanks,
>
> "Bruce L-C [MVP]" wrote:
>> The reason I asked is if you are using 2000 then you have a
>> misconception.
>> You say that your reports are in a particular folder. That statement
>> makes
>> no sense with 2000. Report RDL files exist in the development environment
>> (which is no your web server). Then you deploy to RS. The act of
>> deploying
>> has RS stored the reports in the database. No rdl files exist on the
>> server.
>> Then to integrate with your app you use URL integration or soap (web
>> services). The report viewer is an example on how to do this. I believe
>> it
>> uses URL integration.
>> VS 2005 has a webform and winform controls. Highly functional versions
>> that
>> use web services rather than URL integration. These controls require RS
>> 2005. RS 2005 can use SQL Server 2000 for its object/metadata store but
>> you
>> still have to have a 2005 license.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
>> news:D55A0051-48F4-48FC-899A-506BFF964AAD@.microsoft.com...
>> > RS 2000. Can I use RS2005 in my SQL Server 2000?
>> >
>> > "Bruce L-C [MVP]" wrote:
>> >
>> >> Are you using RS 2000 or 2005.
>> >>
>> >> If 2005 are you doing local or server mode?
>> >>
>> >>
>> >> --
>> >> Bruce Loehle-Conger
>> >> MVP SQL Server Reporting Services
>> >>
>> >> "JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
>> >> news:57D67BC8-7279-46D7-A8DA-CD8E88F6DB49@.microsoft.com...
>> >> > Hello,
>> >> >
>> >> > I keep my reports c:\Inetpub\wwwroot\MyApp\Reports\ folder, how
>> >> > should
>> >> > I
>> >> > set
>> >> > my ReportPath for report viewer to see it in my APS.NET application.
>> >> > Thanks,
>> >> >
>> >> >
>> >>
>> >>
>> >>
>>

Q: Report Deployment

Hello,
I have two questions:
1. When I debug my application it seems it deploys reports to report
manager, how can I prevent that from happening, I would like to deploy when I
chose deploy command only?
2. I have a report which has many sub-report, when I deploy sub-reports are
deployed to report manager to and they become visible in the report manager
screen., so user my chose and try to run them, I would like to make only my
main report be visible on the report manager, is there anyway to accomplish
this?
Thanks,Jim H,
Assuming you're using 2000
1. I'm a little confused about your intent in #1, but you can go into
the project configuration manager and change the Debug Local settings
and configure the target server. Keep in mind, if you build the
project, it will try to deploy the reports somewhere. If you're just
trying check out formatting, use the preview tab.
2. You'll need to deploy reports using a script leveraging rs.exe is
you're looking to automate configuration changes to the reports as part
of deployment. Check out Jasper Smith's tool for generating scripts to
manage RS. http://www.sqldbatips.com/showarticle.asp?ID=62
Andy Potter