MS 2005 Reporting Services Matrix - % Change Issue ....Help!

Hello Everyone,

I have been tasked with creating a report using Reporting Services from an excel report that was wasting a lot of manual effort and time. I have searched boards and I am having the hardest time trying to find a way to do the following in Reporting Services within a Matrix:

Spring Summer Fall

2005 2006 2007 %Change 2005 2006 2007 % Change 2005 2006 2007 %Change

Reponses 219 310 410 100 100 200 300 100 2000 3000 200 -2800

FRC% 102% 103% 200% 97% 23% 25% 10% -15% 20% 32% 10% -22%

===========================================================================================

I can not find out how to do a percent change (the column in red) subtracting just the final or most right two columns of the resulting matrix columns, not an average or some other function over the entire row....

Please help, I know others have had the same issue...

Thank You,

I would think you would just need to incorporate this into your select statement. Select the difference between the two most recent years as percentChange. Then in your report, add a column with the percentChange field as the data value.|||Thank You for your reply...I will try that and post back...|||

Yep that was the answer...

Thanks...

MPG calculation in CR 9, simple? not for me!

I have what I thought was going to be a fairly simple task of writing a report to calculate Miles Per Gallon in a vehicle fleet. The data format goes like this:

Trans_Date Gals Odometer
09/13/2006 16.3 4827
09/25/2006 14.6 5035
09/29/2006 12.3 5220

I need to calculate the MPG for a given time period. I have written a formula to calculate MPG for each fuel transaction but when I try to sum or average those results I get the dreaded "Cannot summerize field" message. I have also tried calculating on the given data but in the example given I cannot figure out how to exclude the 16.3 entry from the calculation since those gallons are not a factor for this time period.I have what I thought was going to be a fairly simple task of writing a report to calculate Miles Per Gallon in a vehicle fleet. The data format goes like this:

Trans_Date Gals Odometer
09/13/2006 16.3 4827
09/25/2006 14.6 5035
09/29/2006 12.3 5220

I need to calculate the MPG for a given time period. I have written a formula to calculate MPG for each fuel transaction but when I try to sum or average those results I get the dreaded "Cannot summerize field" message. I have also tried calculating on the given data but in the example given I cannot figure out how to exclude the 16.3 entry from the calculation since those gallons are not a factor for this time period.

Format the Gals field. At the suppress formula: isnull (previous({galsfield}))

new formula, @.MPG: ({milesfield}-previous({milesfield}))/{galsfield)

new summary, grand total average @.MPG|||Jeffro308,

Thanks for responding to my post!

I am trying your approach but CR 9 does not like the isnull statement as written. I am getting the "A field is required here" message as long as I include the 'previous' function. Any further ideas?|||I'm using 8.5 so there might be a difference. I need to try a couple things at work monday.

Jeffro308,

Thanks for responding to my post!

I am trying your approach but CR 9 does not like the isnull statement as written. I am getting the "A field is required here" message as long as I include the 'previous' function. Any further ideas?|||bump|||Anybody else want to take a stab at this?|||I'll assume you're grouping by vehicle and ordering by odo, and that your 3 rows of sample data are for one vehicle.
I guess you want, per vehicle, the number of gals to be the sum of all records except the first, and the number of miles to be the last record minus the first.

So you could put a formula in the group header to subtract the gals and store the first odo, a formula in the details to add the gals, and a formula in the footer to subtract the stored first odo from the current odo.

e.g.
group header:
whileprintingrecords;
numbervar total_gals:= total_gals - {table.gals};
numbervar vehicle_gals := -{table.gals}; --or maybe {table.gals} * -1
numbervar first_vehicle_odo := {table.odo};

details:
whileprintingrecords;
numbervar total_gals := total_gals + {table.gals};
numbervar vehicle_gals := vehicle_gals + {table.gals};

group footer:
whileprintingrecords;
numbervar first_vehicle_odo;
numbervar vehicle_miles := {table.odo} - first_vehicle_odo;
numbervar total_miles := total_miles + vehicle_miles;
--and display miles/gallon for the vehicle
vehicle_miles / vehicle_gals --You should add a divide by zero check here for when there's only one record

report footer:
whileprintingrecords;
numbervar total_gals;
numbervar total_miles;
--and display miles/gallon for the report
total_miles / total_gals --divide by zero check again

By the way, I've not tried this explicitely but it might give you a start / ideas.sql

Mozilla Firefox and Report Viewer Print control

Has anyone gotten the print control in report viewer to work with Firefox?
One of our clients insists on using firefox and we would like to use
reporting services. I found and downloaded the Mozilla Activex control and
plugin support, added the CLSID for the RSClientPrint control, but have not
had any success in having the control show up in the report viewer.
Microsoft's knowledge base talks about what does and doesn't work in Firefox,
but mentions only that the print control does not work in Safari. Any help
would be greatly appreciated!Hello HK,
Based on my research, the RSClinetPrint ActiveX Control also did not
support for the FireFox browser.
As an alternate, you could export the Report to PDF or Excel and then print
it.
Also, you may print the report in the FireFox directly.
Hope this helps.
Sincerely,
Wei Lu
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.|||On Fri, 6 Jul 2007 06:56:02 -0700, HK <hk@.newsgroup.nospam> wrote:
>Has anyone gotten the print control in report viewer to work with Firefox?
>One of our clients insists on using firefox and we would like to use
>reporting services. I found and downloaded the Mozilla Activex control and
>plugin support, added the CLSID for the RSClientPrint control, but have not
>had any success in having the control show up in the report viewer.
>Microsoft's knowledge base talks about what does and doesn't work in Firefox,
>but mentions only that the print control does not work in Safari. Any help
>would be greatly appreciated!
Just a thought. Perhaps you could check the IETab plugin?
B.|||Thank you for your response.
After searching through the knowledge base, we decided to programatically
render the reports in pdf through the web service. This works well in firefox
and provides us with printing capability without having a two step process to
print (using report viewer and then exporting to pdf).
"Wei Lu [MSFT]" wrote:
> Hello HK,
> Based on my research, the RSClinetPrint ActiveX Control also did not
> support for the FireFox browser.
> As an alternate, you could export the Report to PDF or Excel and then print
> it.
> Also, you may print the report in the FireFox directly.
> Hope this helps.
>
> Sincerely,
> Wei Lu
> 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.
>|||Thanks - we did. But since it runs IE and our clients may not have IE
installed, we couldn't use it.
"Brian Tkatch" wrote:
> On Fri, 6 Jul 2007 06:56:02 -0700, HK <hk@.newsgroup.nospam> wrote:
> >Has anyone gotten the print control in report viewer to work with Firefox?
> >One of our clients insists on using firefox and we would like to use
> >reporting services. I found and downloaded the Mozilla Activex control and
> >plugin support, added the CLSID for the RSClientPrint control, but have not
> >had any success in having the control show up in the report viewer.
> >Microsoft's knowledge base talks about what does and doesn't work in Firefox,
> >but mentions only that the print control does not work in Safari. Any help
> >would be greatly appreciated!
> Just a thought. Perhaps you could check the IETab plugin?
> B.
>

Moving SQL Server/Report Server off public IP

We are planning on moving our SQL Server off of the same machine as our web
server and securing it behind a firewall, removing all accessibility by
public IP. However, our web app uses the report viewer control to render
reports, and it points to <default IP of the web server>/ReportServer, which
was set up by Reporting Services. The client does in fact make a connection
to that IP. If SQL Server (and consequently Reporting Services) moves to
where it cannot be accessed by a client, how can they view reports?So, does the lack of answer here mean that I must have SQL Server on a
public IP, or does it mean the answer's already somewhere else, waiting for
me to look?
"DJM" <msnews@.puddlestheshark.com> wrote in message
news:ePnImS54EHA.1192@.tk2msftngp13.phx.gbl...
> We are planning on moving our SQL Server off of the same machine as our
> web server and securing it behind a firewall, removing all accessibility
> by public IP. However, our web app uses the report viewer control to
> render reports, and it points to <default IP of the web
> server>/ReportServer, which was set up by Reporting Services. The client
> does in fact make a connection to that IP. If SQL Server (and
> consequently Reporting Services) moves to where it cannot be accessed by a
> client, how can they view reports?
>|||There are two ways to integrate with RS. URL integration and web services. I
have not used the report viewer control but my impression (and your
description of the issue you are having confirms it) is that it uses URL
integration. The advantage of URL integration is simplicity plus drill
through (jump to report) works as well. With web services you basically have
to implement your own jump to report (drill through). Web services takes
more work but you have more control. With web services you can create an
application where your web server integrates to reporting services behind
the firewall where the client PCs never directly access the server behind
the firewall. That is one option for you.
Another option for you is to have the SQL Server Database behind the
firewall and the Reporting Services application on the public IP. You have
to buy another license but it resolves your problem. Plus, given the cost to
rework your solution to use web services would most likely cost more than
purchasing another license. Also, keep in mind that your data you report
against (which most likely is what you want behind the firewall) does not
have to be on the same SQL Server that is used by RS. So you could still
have SQL Server on the public IP but use it only for RS and have all the
data used by the reports on a second SQL Server that is behind the firewall.
HTH,
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"DJM" <msnews@.puddlestheshark.com> wrote in message
news:OC73ygQ6EHA.1120@.TK2MSFTNGP11.phx.gbl...
> So, does the lack of answer here mean that I must have SQL Server on a
> public IP, or does it mean the answer's already somewhere else, waiting
for
> me to look?
> "DJM" <msnews@.puddlestheshark.com> wrote in message
> news:ePnImS54EHA.1192@.tk2msftngp13.phx.gbl...
> > We are planning on moving our SQL Server off of the same machine as our
> > web server and securing it behind a firewall, removing all accessibility
> > by public IP. However, our web app uses the report viewer control to
> > render reports, and it points to <default IP of the web
> > server>/ReportServer, which was set up by Reporting Services. The
client
> > does in fact make a connection to that IP. If SQL Server (and
> > consequently Reporting Services) moves to where it cannot be accessed by
a
> > client, how can they view reports?
> >
>|||Thank you so much for the response. I was hoping to avoid having to get
another SQL Server license for the front end simply for Reporting Services
(not to mention it would mean pushing reports to the web server[s] instead
of keeping it on the SQL Server close to the data), but that looks like it
would be the easiest.
Thanks again for the info.
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:ewzXvrQ6EHA.1408@.TK2MSFTNGP10.phx.gbl...
> There are two ways to integrate with RS. URL integration and web services.
> I
> have not used the report viewer control but my impression (and your
> description of the issue you are having confirms it) is that it uses URL
> integration. The advantage of URL integration is simplicity plus drill
> through (jump to report) works as well. With web services you basically
> have
> to implement your own jump to report (drill through). Web services takes
> more work but you have more control. With web services you can create an
> application where your web server integrates to reporting services behind
> the firewall where the client PCs never directly access the server behind
> the firewall. That is one option for you.
> Another option for you is to have the SQL Server Database behind the
> firewall and the Reporting Services application on the public IP. You have
> to buy another license but it resolves your problem. Plus, given the cost
> to
> rework your solution to use web services would most likely cost more than
> purchasing another license. Also, keep in mind that your data you report
> against (which most likely is what you want behind the firewall) does not
> have to be on the same SQL Server that is used by RS. So you could still
> have SQL Server on the public IP but use it only for RS and have all the
> data used by the reports on a second SQL Server that is behind the
> firewall.
> HTH,
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "DJM" <msnews@.puddlestheshark.com> wrote in message
> news:OC73ygQ6EHA.1120@.TK2MSFTNGP11.phx.gbl...
>> So, does the lack of answer here mean that I must have SQL Server on a
>> public IP, or does it mean the answer's already somewhere else, waiting
> for
>> me to look?
>> "DJM" <msnews@.puddlestheshark.com> wrote in message
>> news:ePnImS54EHA.1192@.tk2msftngp13.phx.gbl...
>> > We are planning on moving our SQL Server off of the same machine as our
>> > web server and securing it behind a firewall, removing all
>> > accessibility
>> > by public IP. However, our web app uses the report viewer control to
>> > render reports, and it points to <default IP of the web
>> > server>/ReportServer, which was set up by Reporting Services. The
> client
>> > does in fact make a connection to that IP. If SQL Server (and
>> > consequently Reporting Services) moves to where it cannot be accessed
>> > by
> a
>> > client, how can they view reports?
>> >
>>
>

Moving ReportServices databases. Failed!

We tried to move the two 2005 ReportServer databases to another platform. The actual db move worked fine (detatch/copy/attach) but when the report guy tried to change the connection info in Report Server he could not seem to make the change. So we backed-out the changes and regrouped.

This should be very cook-book. I have no experience with Report Server but I know some of you guys could to this with your eyes closed. After the databases are moved I think all we have to do is change the server name and credentials in Report Server. Is that correct? Do we have to bounce any services? Are there any tricks I can impress the other guys with?

Thanks,

Michael

Follow this link and look for SQL 2005 specific part i am sure it will help

http://support.microsoft.com/kb/842425

Thank s

Moving ReportServer databases example in error?

The following link shows an example of backing-up and restoring the databases supporting Repor Server. My question is not so much Report Server-related but I question the restore syntax for restoring the logs.

http://msdn2.microsoft.com/en-us/library/ms156421.aspx

I honestly do not see how the following code would work. At least, I was not able to until I deleted everything after the "NORECOVERY". Then it seemed to work fine but maybe I missed something!

-- Restore the report server log file to new instance folder
RESTORE LOG ReportServer
FROM DISK='C:\ReportServerData.bak'
WITH NORECOVERY, FILE=2
MOVE 'ReportServer' TO
'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\ReportServer.mdf',
MOVE 'ReportServer_log' TO
'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\ReportServer_Log.ldf';
GO

Thanks a lot!

Your database is not ready if you stop after "with norecovery" restore. This basically puts the database in "recovering..." state (i.e. waiting to recover or roll forward/back transaction). You will have to run the following to recover the database.

-- Perform final restore
RESTORE DATABASE ReportServer
WITH RECOVERY
GO

-- Perform final restore
RESTORE DATABASE ReportServerTempDB
WITH RECOVERY
GO

|||

Everything you said is true and is actually included in the code sample on the link. I did not include it because it is not directly related to my question.

thanks

|||

you mean if you remove this portion of code

<code>

FILE=2
MOVE 'ReportServer' TO
'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\ReportServer.mdf',
MOVE 'ReportServer_log' TO
'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\ReportServer_Log.ldf';

</code>

then it works? Well, there is a missing comma after "FILE=2" and before "MOVE". The correct syntax would be

<code>

FILE=2,
MOVE 'ReportServer' TO
'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\ReportServer.mdf',
MOVE 'ReportServer_log' TO
'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\ReportServer_Log.ldf';

</code>

moving reports in report manager

When we move reports from one folder to another in Report Manager we have to delete the report in the destination folder first before moving it over if a version is already there. If not we get the error that an item with that name already exists. Is there a way around this? Is there a setting which will allow over-write of a report?I guess there is non in the graphical tool.

Jens K. Suessmeyer.

http://www.sqlserver2005.de

Moving reports and their histories from SQL2000-RS to SQL2005-RS

I have SQL 2000 Reporting Server with 30+ reports scheduled to be generated periodically and their snapshots are maintained in respective report history. Few weeks ago; we got SQL 2005 Reporting Server and we started using it for all the new work.

Now we want to retire the old SQL 2000 RS machine but wants to copy the reports along with their histories on to the new server. Can someone guide or give pointers where I can find information on this topic?

Preserving report histories is very critical. Report generation schedule may be re-configured.

You could (backup and then) directly upgrade your existing RS 2000 installation.

Alternatively, you can backup the encryption keys and the ReportServer / ReportServerTempDB of the RS 2000 installation, and move them to another database server. Then install a new RS 2005 instance (with a files-only install). Finally, you can use the RS configuration tool to point the RS 2005 instance to the old databases, upgrade them to the RS 2005 schema and also apply the encryption keys. You can find more information about this in BOL - specifically under migrating Reporting Services: http://msdn2.microsoft.com/en-us/library/ms143724.aspx

-- Robert

|||

Thanks Robert for the reply, the glitch is; the SQL2k5-RS instance where I wish to have my older reports; is already in use for few weeks now.

If we “upgrade” SQL2k-RS to SQL2k5-RS; can we then be able to “migrate” reports from one SQL2k5-RS instance to another SQL2k5-RS instance? (Along with report histories; as its critical)

|||

In that case you may want to look at the RSScripter tool: http://www.sqldbatips.com/showarticle.asp?ID=62

It can definitely move reports, security settings, and subscription settings. I'm not sure about history snapshots though.

-- Robert

|||

I have checked that tool; and my understanding is; that it doesnt copy/script the report histories. However one can script history/execution setting.

I have not gone through Report Server API (Web Services); but can we retreive report history instances (snapshots) using it?

Moving report server to new virtual server

Hi
I've about had it trying to mix WSS and report server so I'd like to create
a new virtual server and put report server on it. Do I have to reinstall
the whole thing or can I just move it?
TIA
BillYou can move them. The following article tells you how:
http://www.sqljunkies.com/HowTo/525B575A-7F61-483A-AC8F-FEC700C34674.scuk
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
<sutphinwb@.nospam.nospam> wrote in message
news:OjOS%23dHEFHA.3580@.TK2MSFTNGP10.phx.gbl...
> Hi
> I've about had it trying to mix WSS and report server so I'd like to
> create a new virtual server and put report server on it. Do I have to
> reinstall the whole thing or can I just move it?
> TIA
> Bill
>

Moving Report Server Log Files

I am setting up a new SQL Server 2005 EE server with Reprting Services
installed. The log files have all been moved to a seperate drive. However, I
have never moved log files for Reporting Services and can find no
documentation for this procedure. Is there anything unique about moving the
ReportServer_log.LDF and ReportServerTempDB_log.LDF or can they be moved just
like a normal user database?
Thanks,
Mike C.
I haven't done this myself, but I'd be *very* surprised if RS cares about the physical location of
the database files. That would be a really crappy idea, if it did.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"MACason" <MACason@.discussions.microsoft.com> wrote in message
news:EADD349C-1278-480E-B5AD-17E33A18BCB8@.microsoft.com...
>I am setting up a new SQL Server 2005 EE server with Reprting Services
> installed. The log files have all been moved to a seperate drive. However, I
> have never moved log files for Reporting Services and can find no
> documentation for this procedure. Is there anything unique about moving the
> ReportServer_log.LDF and ReportServerTempDB_log.LDF or can they be moved just
> like a normal user database?
> Thanks,
> Mike C.

Moving Report Server Log Files

I am setting up a new SQL Server 2005 EE server with Reprting Services
installed. The log files have all been moved to a seperate drive. However, I
have never moved log files for Reporting Services and can find no
documentation for this procedure. Is there anything unique about moving the
ReportServer_log.LDF and ReportServerTempDB_log.LDF or can they be moved just
like a normal user database?
Thanks,
Mike C.I haven't done this myself, but I'd be *very* surprised if RS cares about the physical location of
the database files. That would be a really crappy idea, if it did.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"MACason" <MACason@.discussions.microsoft.com> wrote in message
news:EADD349C-1278-480E-B5AD-17E33A18BCB8@.microsoft.com...
>I am setting up a new SQL Server 2005 EE server with Reprting Services
> installed. The log files have all been moved to a seperate drive. However, I
> have never moved log files for Reporting Services and can find no
> documentation for this procedure. Is there anything unique about moving the
> ReportServer_log.LDF and ReportServerTempDB_log.LDF or can they be moved just
> like a normal user database?
> Thanks,
> Mike C.

Moving Report Server Log Files

I am setting up a new SQL Server 2005 EE server with Reprting Services
installed. The log files have all been moved to a seperate drive. However, I
have never moved log files for Reporting Services and can find no
documentation for this procedure. Is there anything unique about moving the
ReportServer_log.LDF and ReportServerTempDB_log.LDF or can they be moved jus
t
like a normal user database?
Thanks,
Mike C.I haven't done this myself, but I'd be *very* surprised if RS cares about th
e physical location of
the database files. That would be a really crappy idea, if it did.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"MACason" <MACason@.discussions.microsoft.com> wrote in message
news:EADD349C-1278-480E-B5AD-17E33A18BCB8@.microsoft.com...
>I am setting up a new SQL Server 2005 EE server with Reprting Services
> installed. The log files have all been moved to a seperate drive. However,
I
> have never moved log files for Reporting Services and can find no
> documentation for this procedure. Is there anything unique about moving th
e
> ReportServer_log.LDF and ReportServerTempDB_log.LDF or can they be moved j
ust
> like a normal user database?
> Thanks,
> Mike C.

Moving report server database from one server to another

I am using reporting services 2000 with SQL server 2000.
The report server is on my machine and report server database is on another sql server.

I need to move report server database from server1 to server2.I have tried the method which i found on net i.e
1-Stop the reporting services
2-detach the report server database from server1 and attach it to server2
3-reconfigure reporting services using RSconfig wherein i specified the new server name , database name and authentication.
4-Start the reporting services
5-Restart IIS

After following all these steps when i browse the report server i get the following error :

"The report server cannot open a connection to the report server database. A connection to the database is required for all requests and processing. (rsReportServerDatabaseUnavailable) Get Online Help "

Kindly help me on this.

Did you set up a login on Server2 for SSRS?

|||

i have simply moved the report server database to server2 and given the same authentication which was there for the database.

Moving Report Manager

I need to move the report manager from one computer to another computer.
Currently the RS database is on a db server, separate from the report
manager. This will stay the same. I need to know what the steps are for
moving the Report Manager to this new server, and keep all my
folders/schedules intact.Hello dachrist,
The first step would be to install Reporting Services on the new server. If
you are installing Standard Edition you can not choose the database during
the installation, however, you can use a combination of two command line
utilities to connect to the existing database post install. These are:
rsconfig and rsactivate. rsconfig sets the connection information to the
database and a basic syntax would be:
rsconfig -c -s DBServerName -d ReportServer -a SQL -u SQLUsername -P
Password
The "-d" option specifies the ReportServer database and "-a" sets the
authentication type to SQL authentication. rsconfig will be run on the new
Reporting Services Server.
You then run rsactivate on the dbserver to activate the new Reporting
Services Server you just installed, what you referred to as the Report
Manager. .
rsactivate -m NewReportingServicesServer -u Username -p Password
Username and Password will be the Windows account with administrative
priveleges to the Reporting Services Web Service.
I hope this helps.
Rodney Landrum
Author : "Pro SQL Reporting Services" (Apress)
www.apress.com
"dachrist" <dachrist@.discussions.microsoft.com> wrote in message
news:5FAE2190-1179-4FE6-BA4D-FB552C6D9D8F@.microsoft.com...
>I need to move the report manager from one computer to another computer.
> Currently the RS database is on a db server, separate from the report
> manager. This will stay the same. I need to know what the steps are for
> moving the Report Manager to this new server, and keep all my
> folders/schedules intact.

Moving report from Access to Reporting Services

I created 2 stand alone queries and another that combines/joins the 2 for a
cumulative repoirt in MS Access. I need to recreate the combined report in
Reporting Services but do no know how:
Query 1 MON _is Down Karen 10_05
SELECT call_req.ref_num, First(act_log.time_stamp) AS FirstOftime_stamp,
First(CvrtFromUnixTime([time_stamp])) AS [Time Stamp Down]
FROM act_log RIGHT JOIN call_req ON act_log.call_req_id = call_req.persid
WHERE (((act_log.action_desc) Like "% to 'MON-System Down'%"))
GROUP BY call_req.ref_num
ORDER BY call_req.ref_num;
Query 2 MON_is Up Karen 10_05
SELECT call_req.ref_num, First(act_log.time_stamp) AS FirstOftime_stamp,
First(CvrtFromUnixTime([time_stamp])) AS [Time Stamp Up]
FROM act_log RIGHT JOIN call_req ON act_log.call_req_id = call_req.persid
WHERE (((act_log.action_desc) Like "% to 'MON-System Up'%"))
GROUP BY call_req.ref_num
ORDER BY call_req.ref_num;
Combined Query
SELECT [MON _is Down Karen 10_05].ref_num, [MON _is Down Karen 10_05].[Time
Stamp Down], [MON_is Up Karen 10_05].[Time Stamp Up],
AHD_net_res.nr_prim_search_key
FROM (([MON_is Up Karen 10_05] INNER JOIN [MON _is Down Karen 10_05] ON
[MON_is Up Karen 10_05].ref_num = [MON _is Down Karen 10_05].ref_num) INNER
JOIN call_req ON [MON _is Down Karen 10_05].ref_num = call_req.ref_num) LEFT
JOIN AHD_net_res ON call_req.affected_rc = AHD_net_res.id;
Thank you,
KarenI wanted to add the query in Reporting Services I have created:
SELECT DISTINCT
AHD.call_req.ref_num, MIN(DISTINCT
AHD.act_log.time_stamp) AS MINOftime_stamp, MIN(DISTINCT
AHD.act_log.time_stamp) AS [Time Stamp Down],
DATEADD(ss, AHD.act_log.time_stamp - 18000,
CONVERT(DATETIME, '1970-01-01 00:00:00', 102)) AS [Time Down], DATEADD(ss,
act_log_1.time_stamp - 18000, CONVERT(DATETIME,
'1970-01-01 00:00:00', 102)) AS [Time Up], MAX(DISTINCT act_log_1.time_stamp)
AS [Time Stamp Up], MAX(DISTINCT act_log_1.time_stamp)
AS MaxOftime_stamp, AHD.net_res.nr_prim_search_key
FROM AHD.net_res RIGHT OUTER JOIN
AHD.call_req ON AHD.net_res.id =AHD.call_req.affected_rc LEFT OUTER JOIN
AHD.act_log act_log_1 ON AHD.call_req.persid =act_log_1.call_req_id LEFT OUTER JOIN
AHD.act_log ON AHD.call_req.persid =AHD.act_log.call_req_id
WHERE (AHD.act_log.action_desc LIKE '%to ''MON-System Down%') AND
(act_log_1.action_desc LIKE '%to ''MON-System Up%')
GROUP BY AHD.call_req.ref_num, DATEADD(ss, AHD.act_log.time_stamp - 18000,
CONVERT(DATETIME, '1970-01-01 00:00:00', 102)), DATEADD(ss,
act_log_1.time_stamp - 18000, CONVERT(DATETIME,
'1970-01-01 00:00:00', 102)), AHD.net_res.nr_prim_search_key
ORDER BY AHD.net_res.nr_prim_search_key, AHD.call_req.ref_num
--The query works up to a point. I changed "First" to "Min" and "Max". I
am trying to weed out multiple instances of time_stamp when action_desc
inlcludes Mon is Up or Mon is Down. In a perfect world the user would enter
each only once but you know how that goes. So, I want the earliest time_stamp
when action_desc includes "to Mon-System Down" and Max time_stamp when
action_desc includes "to Mon-System Up".
Thanks in advance for any help with this.
Karen
"Moving rpts from Access to Rptg Services" wrote:
> I created 2 stand alone queries and another that combines/joins the 2 for a
> cumulative repoirt in MS Access. I need to recreate the combined report in
> Reporting Services but do no know how:
> Query 1 MON _is Down Karen 10_05
> SELECT call_req.ref_num, First(act_log.time_stamp) AS FirstOftime_stamp,
> First(CvrtFromUnixTime([time_stamp])) AS [Time Stamp Down]
> FROM act_log RIGHT JOIN call_req ON act_log.call_req_id = call_req.persid
> WHERE (((act_log.action_desc) Like "% to 'MON-System Down'%"))
> GROUP BY call_req.ref_num
> ORDER BY call_req.ref_num;
>
> Query 2 MON_is Up Karen 10_05
> SELECT call_req.ref_num, First(act_log.time_stamp) AS FirstOftime_stamp,
> First(CvrtFromUnixTime([time_stamp])) AS [Time Stamp Up]
> FROM act_log RIGHT JOIN call_req ON act_log.call_req_id = call_req.persid
> WHERE (((act_log.action_desc) Like "% to 'MON-System Up'%"))
> GROUP BY call_req.ref_num
> ORDER BY call_req.ref_num;
>
> Combined Query
> SELECT [MON _is Down Karen 10_05].ref_num, [MON _is Down Karen 10_05].[Time
> Stamp Down], [MON_is Up Karen 10_05].[Time Stamp Up],
> AHD_net_res.nr_prim_search_key
> FROM (([MON_is Up Karen 10_05] INNER JOIN [MON _is Down Karen 10_05] ON
> [MON_is Up Karen 10_05].ref_num = [MON _is Down Karen 10_05].ref_num) INNER
> JOIN call_req ON [MON _is Down Karen 10_05].ref_num = call_req.ref_num) LEFT
> JOIN AHD_net_res ON call_req.affected_rc = AHD_net_res.id;
> Thank you,
> Karen

Moving Report Builder reports to new server

I have a development server setup with Report Builder that our users have been testing out. They have a number of reports that they have created on this dev server.

We now have our live environment all setup and I'd like to move their Report Builder reports to the new server for them so they don't have to recreate them.

Is this possible? If so, how?

Thanks.

The Report Builder reports are stored in the report catalog just like the Report Designer reports. There are several options available for moving report definitions ranging from manually exporting and uploading them one by one to handling this programatically. I would recommend you try the Jasper Smith's Report Scripter to automate the process. Remember to script and deploy not only the report definitions but the the data source and the model.