Get back data from Reports

I have just started working with RS 2005 and was wondering if / how I can do the following.

I created an asp.net website that has a report viewer to show my reports on the web.
In addition there is a page that lists all of the reports a user has access to, which is pretty much a series of links:

Report 1
Report 2
Report 3
.....

Now in my reports I generally create a grand total of one of the column and was wondering if there was some way to get that information back into an asp page, ie:

Report 1 - $125,713
Report 2 - ($23,111)
Report 3 - $1,762,142

I was thinking if there was some way to send a parameter to the report like Summary=True which would tell it to just return a XML bit of data holding the total for that report (Anything will work, doesn't have to be XML). So the page with the report links would call each of the reports.

I realize that it would probably be possible to do this through a stored procedure, but that would just create another point of maintenace. Instead I could create some function in all of my reports and have a standardized way of doing this so my webserver or database wouldnt have to be updated all the time.

Thanks, and let me know if there is any other information I can provide to make this clearer.

Try using the XML renderer. You will get a "report" which is really just an XML stream with the data. You can then XPath into it to get the values you are interested in.|||

Sounds promising. Being rather new to reports, are there any pages you can think of off hand that provide examples or explanations?

Get All Possible Values of a Report Parameter

Hi,
I need an application that displays reports, and prompts the user
for the parameter values. I have used the GetReportParameters() to retrieve
the list of parameters. However, i could not find a way to get the available
values when the parameter is of a list type. For example a countries
parameter would include a number of countries to choose from ( retrieved
from a dataset ). How could i retrieve the list of all the countries
dynamically? Can someone help me out.
Thanks,
Loui MerciecaHi Loui:
It sounds as if you are looking for the ValidValues property of a
parameter. See my article here for some tips:
Using GetReportParameters
http://odetocode.com/Articles/123.aspx
--
Scott
http://www.OdeToCode.com/blogs/scott/
On Wed, 29 Sep 2004 12:20:06 +0200, "Loui Mercieca" <loui@.gfi.com>
wrote:
>Hi,
> I need an application that displays reports, and prompts the user
>for the parameter values. I have used the GetReportParameters() to retrieve
>the list of parameters. However, i could not find a way to get the available
>values when the parameter is of a list type. For example a countries
>parameter would include a number of countries to choose from ( retrieved
>from a dataset ). How could i retrieve the list of all the countries
>dynamically? Can someone help me out.
>Thanks,
>Loui Mercieca
>|||Make sure to pass in true for the forRendering parameter. Check here for
more information:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rsprog/htm/rsp_ref_soapapi_service_ak_6x4n.asp
Here is the relevant text from that page:
If ForRendering has a value of false, the parameter meta data returned
represents the parameter data that is currently associated with the
specified report. If any parameters values are based on a query and you are
interested in returning the query-based parameters valid values list, you
need to set ForRendering to true, In addition, for query based parameters,
you need to ensure that you have passed in all of the credential information
required to return the query parameters.
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"Loui Mercieca" <loui@.gfi.com> wrote in message
news:eeseq3gpEHA.1300@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I need an application that displays reports, and prompts the user
> for the parameter values. I have used the GetReportParameters() to
retrieve
> the list of parameters. However, i could not find a way to get the
available
> values when the parameter is of a list type. For example a countries
> parameter would include a number of countries to choose from ( retrieved
> from a dataset ). How could i retrieve the list of all the countries
> dynamically? Can someone help me out.
> Thanks,
> Loui Mercieca
>|||THANK YOU! That ForRendering = true worked great for me. I was having
a similar problem. I wish they explained better what all of these
setting were. But thanks to you, I bypassed this problem.

Gerating Excel Reports from SSIS

I want to be able to create an Excel report in SSIS after querying the data from a SQLSERVER table.

I have a IS package where I'm loading all the data required in the report and the final step of the IS package I would like to build the reports. I think it makes sense to take this approach instead of setting up a RSS package.

AS anyone seen any Blogs which explains such a flow?

You can use an Excel destination in your data flow, but beyond that, you won't be able to apply unique formatting rules, grid lines, images, charts, etc...

Not without programming a script component, anyway.

Generic way to style reports (CSS Style)

I want to make a CSS Style solution for my reports. I have few ideas related to this, changing the report properties on the fly, but I am not sure for which I should go for.

- We know that we can put expression on reports almost everywhere, and can also call custom code for setting up those expressions. i.e.

Public Shared Function GetValue(ByVal Key As String) As String

Dim myDataReader As SqlDataReader
Dim mySqlConnection As SqlConnection
Dim mySqlCommand As SqlCommand

mySqlConnection = New SqlConnection("server=localhost;Trusted_Connection=yes;database=tempMIQB")
mySqlCommand = New SqlCommand("SELECT * FROM [Properties] Where PropertyName='" & Key & "'", mySqlConnection)
mySqlConnection.Open()
myDataReader = mySqlCommand.ExecuteReader(CommandBehavior.CloseConnection)

If myDataReader.Read() Then
GetValue = myDataReader.Item("PropertyValue").ToString
Else
GetValue = "None"
End If

End Function

and in textbox or any where I can say =Code.GetValue("BGColor") or from .NET dll.

For going further on with this idea I can create an .NET dll and get the list from database or XML file. (Please give suggestions for performance/scalability issue.)

- second idea is to write a custom application (script) which can go through all the reports and change the color and fonts and every thing.

- third is to use parameters and use Array.IndexOf method to search for the value, but in this case i need to add dataset to all reports and, I think we cannot access report properties i.e. Parameters from custom code, just to make a generic function to access parameter value.

Hi Noordin,

I'm also trying to do this. Could you give me some leads as to how you accomplished it in the end?

Thanks,
Phil

Generic way to style reports (CSS Style)

I want to make a CSS Style solution for my reports. I have few ideas related to this, changing the report properties on the fly, but I am not sure for which I should go for.

- We know that we can put expression on reports almost everywhere, and can also call custom code for setting up those expressions. i.e.

PublicSharedFunction GetValue(ByVal Key AsString) AsString

Dim myDataReader As SqlDataReader
Dim mySqlConnection As SqlConnection
Dim mySqlCommand As SqlCommand

mySqlConnection = New SqlConnection("server=localhost;Trusted_Connection=yes;database=tempMIQB")
mySqlCommand = New SqlCommand("SELECT * FROM [Properties] Where PropertyName='" & Key & "'", mySqlConnection)
mySqlConnection.Open()
myDataReader = mySqlCommand.ExecuteReader(CommandBehavior.CloseConnection)

If myDataReader.Read() Then
GetValue = myDataReader.Item("PropertyValue").ToString
Else
GetValue = "None"
EndIf

EndFunction

and in textbox or any where I can say =Code.GetValue("BGColor") or from .NET dll.

For going further on with this idea I can create an .NET dll and get the list from database or XML file. (Please give suggestions for performance/scalability issue.)

- second idea is to write a custom application (script) which can go through all the reports and change the color and fonts and every thing.

- third is to use parameters and use Array.IndexOf method to search for the value, but in this case i need to add dataset to all reports and, I think we cannot access report properties i.e. Parameters from custom code, just to make a generic function to access parameter value.

Hi Noordin,

I'm also trying to do this. Could you give me some leads as to how you accomplished it in the end?

Thanks,
Phil

Generating reports using data feeds from other systems

Dear all,
I've been following this group with interest for a while now but haven't
seen any firm pointers for a solution to meet my problems.
Essentially I would like to be able to
1) Create reports, acknowledgements, delivery notes, invoices, etc using
data supplied from my legacy system Unix.
2) Output them to PDF - some of the parameters of this would be in the data
feed.
3) Email or fax these PDFs to our customers.
It's not possible Reporting Services to query directly our system due to
other limitations, but can we feed RS with data either via uploading a file
to a "watch" directory or via a print spool queue on the windows system - we
already use both methods for other applications.
The reason for a print queue is it's easy to implement from Unix to
Windows - no messing with FTP etc, and print queues already exist in our
application.
The format of the data feed can be XML or indeed anything as we are source
code holders for our product.
The questions are:
1) Is this vaguely possible. ?
2) Can RS take it's feed from a print queue - with possibly us putting
together the software to accept the data on the print queue.
3) Could it handle files dropped into a "watch directory"
4) Can RS create PDFs using parameters in the feed file.
5) Could RS, with perhaps additional development, distribute the PDFs based
on parameters contained with the data feed.
If anyone could shed any light on my thoughts, or perhaps, they've done
something like this already, I would greatly appreciate some feedback.
How about this as a product Microsoft - all those legacy systems out there
suddenly able to print something a little better than the old dot-matrix
print-outs on pre-printed stationary. !!!
Any pointers would be much appreciated.
Kind Rgds
Graham GoodeyThis is possible but you will need to write a custom data extension for both
the data in the report as well as driving the delivery of reports. I think
the file-based approach will be easier than the print queue.
--
Brian Welcker
Group Program Manager
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Graham Goodey" <graham_goodey@.yahoo.com> wrote in message
news:O4b500PVEHA.1012@.TK2MSFTNGP09.phx.gbl...
> Dear all,
> I've been following this group with interest for a while now but haven't
> seen any firm pointers for a solution to meet my problems.
> Essentially I would like to be able to
> 1) Create reports, acknowledgements, delivery notes, invoices, etc using
> data supplied from my legacy system Unix.
> 2) Output them to PDF - some of the parameters of this would be in the
data
> feed.
> 3) Email or fax these PDFs to our customers.
> It's not possible Reporting Services to query directly our system due to
> other limitations, but can we feed RS with data either via uploading a
file
> to a "watch" directory or via a print spool queue on the windows system -
we
> already use both methods for other applications.
> The reason for a print queue is it's easy to implement from Unix to
> Windows - no messing with FTP etc, and print queues already exist in our
> application.
> The format of the data feed can be XML or indeed anything as we are source
> code holders for our product.
> The questions are:
> 1) Is this vaguely possible. ?
> 2) Can RS take it's feed from a print queue - with possibly us putting
> together the software to accept the data on the print queue.
> 3) Could it handle files dropped into a "watch directory"
> 4) Can RS create PDFs using parameters in the feed file.
> 5) Could RS, with perhaps additional development, distribute the PDFs
based
> on parameters contained with the data feed.
> If anyone could shed any light on my thoughts, or perhaps, they've done
> something like this already, I would greatly appreciate some feedback.
>
> How about this as a product Microsoft - all those legacy systems out there
> suddenly able to print something a little better than the old dot-matrix
> print-outs on pre-printed stationary. !!!
>
> Any pointers would be much appreciated.
>
> Kind Rgds
>
> Graham Goodey
>
>
>

Generating reports on very large and "live" databases

Hello All,
I am working on a application which has a table with 100 million
records.
A background process keeps inserting 5 rows per second into this
table.
My client is generating reports from that table. They are interested
in seeing reports with real time data. This means that as soon as a
record is inserted into the table, the reports should process that
record as well.
At the max around 20K records are processed to generate a report. When
this happens it takes simply 30 min to generate a report. Upon
analysis I find that since the table is so huge, the indexes on those
table is also very huge.
What can I do to fix this, I have thought about denormalizing the
table. But the some programmers say that picking up data from one
table is better because doing joins on multiple tables will be even
slower.
Another approach is to bring in a data warehouse but I don't know much
about this except what I learnt in MSDN session about creating of data
cubes. But I suppose cube can be created only when data is static. but
in my case new records are inserted every second and they are to be
included in the report.
The 3rd approach is that I create report specific tables and create a
trigger (or a C programm which polls for changes in main table) and
every time new records are inserted into the main table, I preprocess
them. Then when the users make a request for the report I generate my
report from the preprossed table.
But I feel the trigger will be fired to many times and if the number
of reports are significant (> 35) then trigger/C program could become
a bottleneck itself.
What should I do? it is such a tricky problem.
Please help me and give me some advice. Thank you for your help.
regards,
Abhishektable size (or row count) is irrelevent unless a
table/index scan is involved.
5 rows/sec insert is a negligible load on the system.
however, a report that involves 20k rows with good indexes
should not take 30min to run.
1) what is the query,
2) what are the indexes on this table
3) what does the execution plan show?
(indexes used, type of operation, rows involved, costs)
>--Original Message--
>Hello All,
>I am working on a application which has a table with 100
million
>records.
>A background process keeps inserting 5 rows per second
into this
>table.
>My client is generating reports from that table. They are
interested
>in seeing reports with real time data. This means that as
soon as a
>record is inserted into the table, the reports should
process that
>record as well.
>At the max around 20K records are processed to generate a
report. When
>this happens it takes simply 30 min to generate a report.
Upon
>analysis I find that since the table is so huge, the
indexes on those
>table is also very huge.
>What can I do to fix this, I have thought about
denormalizing the
>table. But the some programmers say that picking up data
from one
>table is better because doing joins on multiple tables
will be even
>slower.
>Another approach is to bring in a data warehouse but I
don't know much
>about this except what I learnt in MSDN session about
creating of data
>cubes. But I suppose cube can be created only when data
is static. but
>in my case new records are inserted every second and they
are to be
>included in the report.
>The 3rd approach is that I create report specific tables
and create a
>trigger (or a C programm which polls for changes in main
table) and
>every time new records are inserted into the main table,
I preprocess
>them. Then when the users make a request for the report
I generate my
>report from the preprossed table.
>But I feel the trigger will be fired to many times and if
the number
>of reports are significant (> 35) then trigger/C program
could become
>a bottleneck itself.
>What should I do? it is such a tricky problem.
>Please help me and give me some advice. Thank you for
your help.
>regards,
>Abhishek
>.
>

Generating reports on fly

Hello,

SRS 2005 provides functionality for loading and rendering reports on fly using LoadReportDefinition and Render methods defined in ReportExecutionService webservice.

I was wondering if the same/similar behaviour can be accomplished in Reporting Services 2000.

Thanks in advance,

Kobi

LoadReportDefinition API was introduced in 2005 for the Report Builder. With SSRS 2000 you have to explicitly upload the report defnition.

Generating Reports From LoadTest Database

Dear All,

I have configured reproting services in Sql Server 2005.

I have also created the LoadTest Database.

I have seen Sean Lumley's Blog sample report also visited the link provided by him 'http://blogs.msdn.com/slumley/archive/2006/02/16/533356.aspx' however i am not able to figure out what to do next with this link ,how togenerate report in the format as he has suggested from my load test store.

Regards,

Follow the link to gotdotnet, the report are downloadable from there and can be easily customized to your needs.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

Generating reports from flat files

Hi

I got two text files with data.I got to compare two files and if there is any inconsistancy between two files I need to dispaly as a report using sql reporting services.I do not know how to do that?

Any source code or suggestion.

Thanx in advance

What is the format of the data files? By "comparing", is this line-for-line, character-for-character, or is the structure of the files regular data rows?

>L<

|||line-for-line|||

Different people will give you different advice about the best thing to do here, depending on which environment they are comfortable working in. All the different types of advice will boil down to the following steps:

consume both files into the same structure, which has one row for each row of the file(s).

It doesn't matter whether this is an XML structure or a database table, or how the data gets into that structure, that is a detail of implementation we can discuss when you tell us more about your background. You could use an SSIS package, read the files in CLR code a line at a time, etc.

Here are some thoughts about exactly what you should store:
I think it would be useful to have an integer value representing line number in this case as well, for reporting purposes.
Make some determinations about what represents a significant change between the files. This will make some differences in how you store the rows. For example: do you omit "whitespace" lines -- can they be safely skipped? Suppose that (for instance) the two files are identical but row 80 has an extra carriage return in one file. Does that mean that all the rows from 81 down are counted as different from each other, working on line number basis only? Or are they the same, even though the line numbers are offset by 1? is punctuation significant? Is case-sensitivity?
|||

Thanx for you advice.

I have done like this but how will you give the xml structure generated from flat files to the reporting engine.

Any idea

Thanx

|||

You can use the XML files as a datasource directly, if you design the XML to look like ADO dataset XML.

You can also have a "real" database consume the XML (regardless of its schema) and then do the report from there.

What does your XML look like?

>L<

Generating Reports Error, dependent on local groups?

Hi,
I finished creating my reports and have had an issue with generating the
reports on http://localhost/reports. When I login to
http://localhost/reports with a user that is part of the Local Administrators
Group the reports run perfectly correct. But when I login with a user that
is not part of the Local Administrators Group I get the Reporting Services
Error:
An error has occurred during report processing. (rsProcessingAborted) Get
Online Help
Cannot create a connection to data source 'OVSDPROD'.
(rsErrorOpeningConnection) Get Online Help
ERROR [08004] [Oracle][ODBC][Ora]Error while trying to retrieve text for
error ORA-12154 ERROR [IM006] [Microsoft][ODBC Driver Manager] Driver's
SQLSetConnectAttr failed
The user that I am logged in with has the same exact item-level and
system-level roles as the BUILTIN\Administrators and can navigate throughout
the site to get to the reports but when its time to actually generate the
reports I get the error above.
MikeThis is a problem of the user connecting to Oracle, not to Report Server. The
error is coming form ODBC.
Test this by reconfiguring the data source and storing credentials on the
report server so that the credentials you use are the same as those being
used by the other person.
HTH
Charles Kangai, MCT, MCDBA
"mtam79" wrote:
> Hi,
> I finished creating my reports and have had an issue with generating the
> reports on http://localhost/reports. When I login to
> http://localhost/reports with a user that is part of the Local Administrators
> Group the reports run perfectly correct. But when I login with a user that
> is not part of the Local Administrators Group I get the Reporting Services
> Error:
> An error has occurred during report processing. (rsProcessingAborted) Get
> Online Help
> Cannot create a connection to data source 'OVSDPROD'.
> (rsErrorOpeningConnection) Get Online Help
> ERROR [08004] [Oracle][ODBC][Ora]Error while trying to retrieve text for
> error ORA-12154 ERROR [IM006] [Microsoft][ODBC Driver Manager] Driver's
> SQLSetConnectAttr failed
> The user that I am logged in with has the same exact item-level and
> system-level roles as the BUILTIN\Administrators and can navigate throughout
> the site to get to the reports but when its time to actually generate the
> reports I get the error above.
> Mike|||The data source is configured with "Credentials stored securely in the report
server"...
Even though the error states that this is an ODBC issue, it confuses me
since I am able to generate reports with users that are part of the Local
Administrator Group, but anyone outside of that group can not Generate
Reports. If everyone was having problems generating reports then I would
definitely see how it is an ODBC issue.
Any other suggestions?
Thanks,
Mike
"Charles Kangai" wrote:
> This is a problem of the user connecting to Oracle, not to Report Server. The
> error is coming form ODBC.
> Test this by reconfiguring the data source and storing credentials on the
> report server so that the credentials you use are the same as those being
> used by the other person.
> HTH
> Charles Kangai, MCT, MCDBA
> "mtam79" wrote:
> > Hi,
> >
> > I finished creating my reports and have had an issue with generating the
> > reports on http://localhost/reports. When I login to
> > http://localhost/reports with a user that is part of the Local Administrators
> > Group the reports run perfectly correct. But when I login with a user that
> > is not part of the Local Administrators Group I get the Reporting Services
> > Error:
> >
> > An error has occurred during report processing. (rsProcessingAborted) Get
> > Online Help
> > Cannot create a connection to data source 'OVSDPROD'.
> > (rsErrorOpeningConnection) Get Online Help
> > ERROR [08004] [Oracle][ODBC][Ora]Error while trying to retrieve text for
> > error ORA-12154 ERROR [IM006] [Microsoft][ODBC Driver Manager] Driver's
> > SQLSetConnectAttr failed
> >
> > The user that I am logged in with has the same exact item-level and
> > system-level roles as the BUILTIN\Administrators and can navigate throughout
> > the site to get to the reports but when its time to actually generate the
> > reports I get the error above.
> >
> > Mike|||I figured out my problem had to do with the security permissions that were
assigned to the folder where my tnsnames.ora file was located. I needed to
set permissions for Everyone to be able to read that folder so they can
access the file and therefore run the reports.
"mtam79" wrote:
> The data source is configured with "Credentials stored securely in the report
> server"...
> Even though the error states that this is an ODBC issue, it confuses me
> since I am able to generate reports with users that are part of the Local
> Administrator Group, but anyone outside of that group can not Generate
> Reports. If everyone was having problems generating reports then I would
> definitely see how it is an ODBC issue.
> Any other suggestions?
> Thanks,
> Mike
> "Charles Kangai" wrote:
> > This is a problem of the user connecting to Oracle, not to Report Server. The
> > error is coming form ODBC.
> >
> > Test this by reconfiguring the data source and storing credentials on the
> > report server so that the credentials you use are the same as those being
> > used by the other person.
> >
> > HTH
> >
> > Charles Kangai, MCT, MCDBA
> >
> > "mtam79" wrote:
> >
> > > Hi,
> > >
> > > I finished creating my reports and have had an issue with generating the
> > > reports on http://localhost/reports. When I login to
> > > http://localhost/reports with a user that is part of the Local Administrators
> > > Group the reports run perfectly correct. But when I login with a user that
> > > is not part of the Local Administrators Group I get the Reporting Services
> > > Error:
> > >
> > > An error has occurred during report processing. (rsProcessingAborted) Get
> > > Online Help
> > > Cannot create a connection to data source 'OVSDPROD'.
> > > (rsErrorOpeningConnection) Get Online Help
> > > ERROR [08004] [Oracle][ODBC][Ora]Error while trying to retrieve text for
> > > error ORA-12154 ERROR [IM006] [Microsoft][ODBC Driver Manager] Driver's
> > > SQLSetConnectAttr failed
> > >
> > > The user that I am logged in with has the same exact item-level and
> > > system-level roles as the BUILTIN\Administrators and can navigate throughout
> > > the site to get to the reports but when its time to actually generate the
> > > reports I get the error above.
> > >
> > > Mike|||Please also check this KB article, because you may run into the same
security permission issues when trying to setup subscriptions on these
reports: http://support.microsoft.com/default.aspx?scid=kb;en-us;870668
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"mtam79" <mtam79@.discussions.microsoft.com> wrote in message
news:E1E6AF79-39B8-4E1B-AFAA-69A00E5C4B19@.microsoft.com...
> I figured out my problem had to do with the security permissions that were
> assigned to the folder where my tnsnames.ora file was located. I needed
to
> set permissions for Everyone to be able to read that folder so they can
> access the file and therefore run the reports.
>
> "mtam79" wrote:
> > The data source is configured with "Credentials stored securely in the
report
> > server"...
> >
> > Even though the error states that this is an ODBC issue, it confuses me
> > since I am able to generate reports with users that are part of the
Local
> > Administrator Group, but anyone outside of that group can not Generate
> > Reports. If everyone was having problems generating reports then I
would
> > definitely see how it is an ODBC issue.
> >
> > Any other suggestions?
> >
> > Thanks,
> > Mike
> >
> > "Charles Kangai" wrote:
> >
> > > This is a problem of the user connecting to Oracle, not to Report
Server. The
> > > error is coming form ODBC.
> > >
> > > Test this by reconfiguring the data source and storing credentials on
the
> > > report server so that the credentials you use are the same as those
being
> > > used by the other person.
> > >
> > > HTH
> > >
> > > Charles Kangai, MCT, MCDBA
> > >
> > > "mtam79" wrote:
> > >
> > > > Hi,
> > > >
> > > > I finished creating my reports and have had an issue with generating
the
> > > > reports on http://localhost/reports. When I login to
> > > > http://localhost/reports with a user that is part of the Local
Administrators
> > > > Group the reports run perfectly correct. But when I login with a
user that
> > > > is not part of the Local Administrators Group I get the Reporting
Services
> > > > Error:
> > > >
> > > > An error has occurred during report processing.
(rsProcessingAborted) Get
> > > > Online Help
> > > > Cannot create a connection to data source 'OVSDPROD'.
> > > > (rsErrorOpeningConnection) Get Online Help
> > > > ERROR [08004] [Oracle][ODBC][Ora]Error while trying to retrieve text
for
> > > > error ORA-12154 ERROR [IM006] [Microsoft][ODBC Driver Manager]
Driver's
> > > > SQLSetConnectAttr failed
> > > >
> > > > The user that I am logged in with has the same exact item-level and
> > > > system-level roles as the BUILTIN\Administrators and can navigate
throughout
> > > > the site to get to the reports but when its time to actually
generate the
> > > > reports I get the error above.
> > > >
> > > > Mike

Generating reports dynamically

I am new to reporting and Yukon and trying to understand reporting services.
I am trying
to determine if it is possible to define reports programmatically and send
them to a yukon server (or SQL server 2000) and have it generate an Excel
report and place it at the specified location on the file server. What
complicates this picture is that the reports to be generated/queries to be
performed are very complex. Can such complex queries be defined dynamically
and sent to the server via an API call ... can we use a stored procedure, if
necessary, to return the data?
I looked briefly at RDL and the web services but could not determine if they
could be leveraged for our solution. Any input you have will be greatly
appreciated.Yes, you can generate reports dynamically via the API... you can also run
them,,, you can create subscriptions to run immediately, and send the report
to a file share..
--
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.
"IP" wrote:
> I am new to reporting and Yukon and trying to understand reporting services.
> I am trying
> to determine if it is possible to define reports programmatically and send
> them to a yukon server (or SQL server 2000) and have it generate an Excel
> report and place it at the specified location on the file server. What
> complicates this picture is that the reports to be generated/queries to be
> performed are very complex. Can such complex queries be defined dynamically
> and sent to the server via an API call ... can we use a stored procedure, if
> necessary, to return the data?
> I looked briefly at RDL and the web services but could not determine if they
> could be leveraged for our solution. Any input you have will be greatly
> appreciated.|||Thank you Wayne. Can you please point me to which API can be used for this
purpose and any websites that I can go to for more information on this.
Thanks in advance,
IP
"Wayne Snyder" wrote:
> Yes, you can generate reports dynamically via the API... you can also run
> them,,, you can create subscriptions to run immediately, and send the report
> to a file share..
> --
> 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.
>
> "IP" wrote:
> > I am new to reporting and Yukon and trying to understand reporting services.
> > I am trying
> > to determine if it is possible to define reports programmatically and send
> > them to a yukon server (or SQL server 2000) and have it generate an Excel
> > report and place it at the specified location on the file server. What
> > complicates this picture is that the reports to be generated/queries to be
> > performed are very complex. Can such complex queries be defined dynamically
> > and sent to the server via an API call ... can we use a stored procedure, if
> > necessary, to return the data?
> >
> > I looked briefly at RDL and the web services but could not determine if they
> > could be leveraged for our solution. Any input you have will be greatly
> > appreciated.

generating reports and saving them on the disk

Hi,

I want to know how to generate reports as pdf and save them on the disk 'automatically' at regular intervals. I have seen some links by googling but none satisfactory. If any of you have already done this and it works well for you, please share the info.

Can they be saved to the database automatically?

Please give you answer considering that all this is very new to me :)

I appreciate the help. thanks

ROn.

A step by step generation of rdlc as pdf and saving it on a given folder location is given in the foll post.

http://technoblab.blogspot.com

Do u want the rdlc itself to be generated and saved periodically w/o user intervention then ?

Then probablly I'll try ..

.......................................................................

Please "Mark As Answer" on the post that helped you.

|||

Hi,

Thanks. I need to automate the process. How to do that. Plz help.

thanks

|||

Anyone who could help me here!!

|||

hey,

Implement windows service which will run in the background and generate the report each interval time you specify in thetime ticker in the service app.config

to do this you can read the full example in this link http://en.csharp-online.net/Creating_a_.NET_Windows_Service%E2%80%94The_Timer_Approach

each time the timer method execute you call the reporting service web service and execute the report then stream the result and save it on your local server

sql

Generating Report Error, Local Administrator Group

Hi,
I finished creating my reports and have had an issue with generating the
reports on http://localhost/reports. When I login to
http://localhost/reports with a user that is part of the Local Administrators
Group the reports run perfectly correct. But when I login with a user that
is not part of the Local Administrators Group I get the Reporting Services
Error:
An error has occurred during report processing. (rsProcessingAborted) Get
Online Help
Cannot create a connection to data source 'OVSDPROD'.
(rsErrorOpeningConnection) Get Online Help
ERROR [08004] [Oracle][ODBC][Ora]Error while trying to retrieve text for
error ORA-12154 ERROR [IM006] [Microsoft][ODBC Driver Manager] Driver's
SQLSetConnectAttr failed
The user that I am logged in with has the same exact item-level and
system-level roles as the BUILTIN\Administrators and can navigate throughout
the site to get to the reports but when its time to actually generate the
reports I get the error above.
The data source is configured with "Credentials stored securely in the report
server"...
Even though the error states that this is an ODBC issue, it confuses me
since I am able to generate reports with users that are part of the Local
Administrator Group, but anyone outside of that group can not Generate
Reports. If everyone was having problems generating reports then I would
definitely see how it is an ODBC issue.
I am using ODBC Drivers from Oracle.....
Thanks,
MikeI figured out my problem had to do with the security permissions that were
assigned to the folder where my tnsnames.ora file was located. I needed to
set permissions for Everyone to be able to read that folder so they can
access the file and therefore run the reports.
"mtam79" wrote:
> Hi,
> I finished creating my reports and have had an issue with generating the
> reports on http://localhost/reports. When I login to
> http://localhost/reports with a user that is part of the Local Administrators
> Group the reports run perfectly correct. But when I login with a user that
> is not part of the Local Administrators Group I get the Reporting Services
> Error:
> An error has occurred during report processing. (rsProcessingAborted) Get
> Online Help
> Cannot create a connection to data source 'OVSDPROD'.
> (rsErrorOpeningConnection) Get Online Help
> ERROR [08004] [Oracle][ODBC][Ora]Error while trying to retrieve text for
> error ORA-12154 ERROR [IM006] [Microsoft][ODBC Driver Manager] Driver's
> SQLSetConnectAttr failed
> The user that I am logged in with has the same exact item-level and
> system-level roles as the BUILTIN\Administrators and can navigate throughout
> the site to get to the reports but when its time to actually generate the
> reports I get the error above.
> The data source is configured with "Credentials stored securely in the report
> server"...
> Even though the error states that this is an ODBC issue, it confuses me
> since I am able to generate reports with users that are part of the Local
> Administrator Group, but anyone outside of that group can not Generate
> Reports. If everyone was having problems generating reports then I would
> definitely see how it is an ODBC issue.
> I am using ODBC Drivers from Oracle.....
> Thanks,
> Mike|||I
"mtam79" wrote:
> Hi,
> I finished creating my reports and have had an issue with generating the
> reports on http://localhost/reports. When I login to
> http://localhost/reports with a user that is part of the Local Administrators
> Group the reports run perfectly correct. But when I login with a user that
> is not part of the Local Administrators Group I get the Reporting Services
> Error:
> An error has occurred during report processing. (rsProcessingAborted) Get
> Online Help
> Cannot create a connection to data source 'OVSDPROD'.
> (rsErrorOpeningConnection) Get Online Help
> ERROR [08004] [Oracle][ODBC][Ora]Error while trying to retrieve text for
> error ORA-12154 ERROR [IM006] [Microsoft][ODBC Driver Manager] Driver's
> SQLSetConnectAttr failed
> The user that I am logged in with has the same exact item-level and
> system-level roles as the BUILTIN\Administrators and can navigate throughout
> the site to get to the reports but when its time to actually generate the
> reports I get the error above.
> The data source is configured with "Credentials stored securely in the report
> server"...
> Even though the error states that this is an ODBC issue, it confuses me
> since I am able to generate reports with users that are part of the Local
> Administrator Group, but anyone outside of that group can not Generate
> Reports. If everyone was having problems generating reports then I would
> definitely see how it is an ODBC issue.
> I am using ODBC Drivers from Oracle.....
> Thanks,
> Mikesql

Generating parameters from a list

I have a number of fiscal reports that I am writing. Currently, the
parameter for year is hard-coded. Ideally, I would like to be able to
generate the parameter list so that it offers years in descending from the
current year back 5 years or some defined period. In this manner, the
reports would not need to be updated each new year to modify the parameter
list. Is this possible, in v. 2000?One solution could be to create a dataset that pulls the year of a date field
in your report (group on the year) and use that as your query for the
parameter.
"Sarah" wrote:
> I have a number of fiscal reports that I am writing. Currently, the
> parameter for year is hard-coded. Ideally, I would like to be able to
> generate the parameter list so that it offers years in descending from the
> current year back 5 years or some defined period. In this manner, the
> reports would not need to be updated each new year to modify the parameter
> list. Is this possible, in v. 2000?|||This query will do it... Setup your parameter and set this query to be the
default value
select datename(yyyy,getdate())
+',' + datename(yyyy,dateadd(yy,-1,getdate()))
+',' + datename(yyyy,dateadd(yy,-2,getdate()))
+',' + datename(yyyy,dateadd(yy,-3,getdate()))
+',' + datename(yyyy,dateadd(yy,-4,getdate()))
--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
(Please respond only to the newsgroup.)
I support the Professional Association for SQL Server ( PASS) and it's
community of SQL Professionals.
"Sarah" <Sarah@.discussions.microsoft.com> wrote in message
news:4F379DE9-9B9D-454A-9ABA-41CFB39C41C9@.microsoft.com...
>I have a number of fiscal reports that I am writing. Currently, the
> parameter for year is hard-coded. Ideally, I would like to be able to
> generate the parameter list so that it offers years in descending from the
> current year back 5 years or some defined period. In this manner, the
> reports would not need to be updated each new year to modify the parameter
> list. Is this possible, in v. 2000?

Generating overnight data vs Live - suggestions needed.

We have an MIS system which has approx 100 reports. Each of these
reports can take up to several minutes to run due to the complexity of
the queries (hundreds of lines each in most cases). Each report can be
run by many users, so in effect we have a slow system.

I want to seperate the complex part of the queries into a process that
is generated each night. Then the reports will only have to query
pre-formatted data with minimal parameters as the hard part will have
been completed for the users when they are not in. Ideally we will
generate (stored procedure possibly) a set of data for each report and
hold this on the server. We can then query with simpler parameters
such as by date and get the data back quite quickly.

The whole process of how we obtain the data is very complex. There are
various views which gather data from the back office system. These are
very complex and when queries are run against them including other
tables to bring in more data, it gets nicely complicated.

The only problem is that the users want to have access to LIVE data
from the back office system, specifically the Sales team who want to
access this remotely. My method only allows for data from the night
before, so is there an option available to me which will allow me to
do this ? The queries can't be improved on an awful lot, so they will
take as long as they take. The idea of running them once is the only
way I can see to improve the performance in any significant way.

True I could just let them carry on as they are and let them suffer
with the performance on live data, but I'd like to do something to
improve the situation for them.

Any advice would be appreciated.

Thanks

RyanYep, that definitely makes a difference. Taken me a while to get back
to this, but we're looking at a solution along these lines.

By taking a copy of the data from the view into a table and then
replicating this onto another database (for generating) and then
running stored procedures (from each complex query) against this,
we've been able to reduce the time from 5 minutes for a test generate
query to about 3 seconds. We've timed the other parts and can probably
generate all of our data in less than 5 minutes for 100 complex
queries that would normally take hours.

Once this data is generated we'll simply swap it for the current data
which should be pretty quick. Our report now loads in less than a
second as opposed to 5 mins or so.

This means we can probably update the data once an hour and push it
onto the query database which will be more than quick enough for the
users. This has the knock on effect of being able to produce the
reports in seconds instead of minutes.

Erland, thanks for the advice on this it should make quite a
difference.

Erland Sommarskog <sommar@.algonet.se> wrote in message news:<Xns93DEF27F53F61Yazorman@.127.0.0.1>...
> Ryan (ryanofford@.hotmail.com) writes:
> > The only problem is that the users want to have access to LIVE data
> > from the back office system, specifically the Sales team who want to
> > access this remotely. My method only allows for data from the night
> > before, so is there an option available to me which will allow me to
> > do this ? The queries can't be improved on an awful lot, so they will
> > take as long as they take. The idea of running them once is the only
> > way I can see to improve the performance in any significant way.
> If users want current data, you cannot pre-compute it for them. You
> know, have the cake and eat it.
> What you could to do is to set up a report server which you replicate
> to, so that you take the load of the back-office system.
> Yet another alternative, is to have three databases:
> A - the source-system.
> B - the view system that the users use.-
> C - a computation system.
> You keep the computation database updated by means of log shipping
> (this is better than replication in this case). One you have applied
> a log, you pre-compute all data. When this step is done, you flush the
> pre-computed tables on B, and insert the data from C. Now you go and
> pick up a new log from A. A more fancy variant is to have a seamless
> switch between B and C which you flip constantly. I believe there are
> such things.|||Ryan (ryanofford@.hotmail.com) writes:
> By taking a copy of the data from the view into a table and then
> replicating this onto another database (for generating) and then
> running stored procedures (from each complex query) against this,
> we've been able to reduce the time from 5 minutes for a test generate
> query to about 3 seconds. We've timed the other parts and can probably
> generate all of our data in less than 5 minutes for 100 complex
> queries that would normally take hours.

300 seconds down to three! That's not bad!

> Erland, thanks for the advice on this it should make quite a
> difference.

I'm only glad to have helped! And thanks for reporing back!

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Generating Multiple Reports

I have a report that I pass parameters of a clientID. I run the report and export it to a .pdf file. I would like to be able to do this for multiple clients without manually having to enter the ID each time and exporting it.

I have thought about creating a report that calls this report so that I can pass in the clientID one at a time. The problem is trying to export each one.

Does anyone have any ideas? Thanks in advance!

I beleive you can use the reportviewer.render method to export directly to a PDF. I've done a similar app going straight to printer.|||

Let me quote myself from http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2048825&SiteID=1

Two methods come to mind:

1. You can run reports from the command line using the rs command (documentation) as described in the following SQLJunkies article:

http://sqljunkies.com/Article/B197B9E7-EF3D-4D70-9B5E-47B74E57EF38.scuk

which doesn't seem to be up for me so here is the Google cache link:

http://64.233.167.104/search?q=cache:XvoyQYWNs50Jqljunkies.com/Article/B197B9E7-EF3D-4D70-9B5E-47B74E57EF38.scuk&hl=en&gl=us&strip=1

Within the VB script that you run, query your tables and feed the parameters.

2. You could use a variant of a Data Driven Subscription. You will need the Enterprise version of SSRS to use Data Driven Subscriptions. Here is a tutorial on building one:

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

I came accross this article by Jason Selburg which claims to allow you to build your own data driven subscriptions using Standard Edition:

http://www.sqlservercentral.com/columnists/jselburg/datadrivensubscriptions.asp

and his follow up:

http://www.sqlservercentral.com/columnists/jselburg/2824.asp

and I believe either method will work for you here. If you need a more specific answer, let me know.

Good luck,

Larry Smithmier

Generating and Printing Reports

I've created a data flow where I have linked to an OLE DB Source then created a Flat File Destination. My file is now on my c: drive. I'd like to use that data to create a report, then print to a PDF. How would I do that?

Currently I was doing this in Access, but I am moving my processes to SSIS.

You wouldn't use SSIS to do that. SSIS is an ETL tool, not a reporting tool.

-Jamie

|||But if I created my report in Reporting Services for instance, how could I put that in my data flow in SSIS?|||I was also assuming that I could put this in a Script Task, but I didn't know how far I could go with generating a report from there.|||

I don't know what you mean by "put a report in the data flow" or "put it in a script task". Can you expand on that?

There is a way that you can consume a SSIS dataflow from within SSRS but that doesn't seem to be what you're after here. You seem to want to build a report on top of a text file (that just happens to have been created by SSIS). I'm sorry, I don't understand what it is you actually want SSIS to do.

-Jamie

|||

I've gotten my answer:

Write an application in VB using the Report Viewer from the toolbar to create and print the report

Then execute that application from SSIS.

|||

So you're calling the SSRS API, is that correct? If so, you don't have to write another app to do that - you can embed it into SSIS.

-Jamie

|||What do you mean by calling the SSRS API? I'm using Visual Studio 2005.|||

ifaber wrote:

What do you mean by calling the SSRS API? I'm using Visual Studio 2005.

Shall we start again. What exactly is your VB app going to do?

|||My application is going to pull data from many tables, then export to a flat file. Our network person will pick up the files, and put the data on a website. Then from 2 of those tables, I pulled a list of instructors that currently is on our website in PDF reports. In Access, I have pulled the data, created a report and exported to a PDF. Our network person pulls those reports and puts them on the website. It's pretty simple compared to some other stuff I have done.|||

Fair enough, if it works for you that's good. I was just wondering if there was an option to utilise SQL Server Reporting Services (SSRS) seeing as that is a reporting application. I mistakenly assumed that seeing as you were using SSIS that you would be trying to use SSRS for reporting - my mistake. Sorry.

I also thought you were trying to automate the creation of PDF files - something that I suspect could be done from SSRS and ergo from SSIS if there is an API available for doing that. Again it looks like my assumption was wrong.

To me, it seems strange that you would use a differrent application to do all this when it can all be achieved with SSRS but then again I am a SQL Server zealot so I would say that :)

Good luck with it.

-Jamie

|||Wellllll, I know how to use SQL Server Reporting Services too. In fact, I have a lot of reports using SSRS. Pretty much, after I have updated the flat file, I'd like to run something that would create a report and would print to PDF.|||

Right. Well what I was driving at is if SSRS can create PDF reports (and I'm reliably informed that it can) then you can call the SSRS API from SSIS in order to create those reports for you. I haven't actually done it but I'm sure its possible.

-Jamie

|||Bingo!! Now how do I call the API :)|||

ifaber wrote:

Bingo!! Now how do I call the API :)

I was worried you were about to ask that.

I don't actually know, but I've no doubt that you can do it. To know more about the SSRS API you;d be best checking out the SSRS forum.

What I DO know is that SSRS itself is a web service which means that everything that can be done in SSRS can be called via that web service. essentially that web service IS the SSRS API.

You can calls web services from SSIS using the Web Service task. HOWEVER, I am aware that there are limitations with SSIS's web service task (search this forum to find out more) but don't despair because you have another optionavailable to you. You can call web services from within the SSIS script task.using dotnet APIs.

I hope that is useful and I hope you didn't mind me continuing the thread. Its just that you seemed to want to do all from within one package and I figured it would be possible. Anything that can be accessed via dotnet code can be done from SSIS's Script Task..

-Jamie

Generating a Group of Reports

We are thinking of a possible requirement for our custom application (which
will use Reporting Services), but we are unsure if it is possible.
Say you have 20 individual reports. And, these 20 reports are split up into
4 report groups (5 reports per report group). This is more of a logical
grouping (or maybe the report groups represent folders on the report
server). Anyhow, is it possible to generate the entire report group (thus
generating all 5 reports in the report group at one time) instead of having
to generate each individual report? Does Reporting Services support such an
idea?
Thanks.Yeah, I have a similar question(s).
Is it possible to have 5 separate reports (RDLs) and when they are executed
have the output of each report concatenated into 1 long report (probably
with a page break between each report)?
Or, does one have to loop through each report, calling it via the URL
method, and displaying each report in a separate browser instance (thus 5
separate windows opened to see all five reports)?
Or'?
Any help is appreciated. Thanks in advance.
"Dex" <dex@.yahoo.com> wrote in message
news:%23dePSvpmEHA.3396@.tk2msftngp13.phx.gbl...
> We are thinking of a possible requirement for our custom application
(which
> will use Reporting Services), but we are unsure if it is possible.
> Say you have 20 individual reports. And, these 20 reports are split up
into
> 4 report groups (5 reports per report group). This is more of a logical
> grouping (or maybe the report groups represent folders on the report
> server). Anyhow, is it possible to generate the entire report group (thus
> generating all 5 reports in the report group at one time) instead of
having
> to generate each individual report? Does Reporting Services support such
an
> idea?
> Thanks.
>

Generated RDL Causes Elements to Overlap

Howdy,
I'm generating a series of RDL reports using Python and Cheetah. It
seems to work great with one, huge problem: when I go to view the
reports in Visual Studio or when they're deployed, the report elements
overlap or are out of order.
Is there any non-absolute way to position elements using RDL? I can't
figure out how to even take just 3 textboxes and get them to flow down
a page without specifying their TOP coordinates. Sounds basic, but I'm
butting my head against the wall here. At best, they seem to flow in
reverse order.
Here's an example of some simple RDL that ends up overlapping (in
Visual Studio, which also warns me about overlapping) - any help at
all, conceptually or otherwise, would be much appreciated:
<ReportItems>
<Textbox Name="textbox1">
<rd:DefaultName>textbox1</rd:DefaultName>
<Width>1in</Width>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Height>0.25in</Height>
<Value>Test box 1</Value>
</Textbox>
<Textbox Name="textbox2">
<rd:DefaultName>textbox2</rd:DefaultName>
<Width>1in</Width>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Height>0.25in</Height>
<Value>Test Box 2</Value>
</Textbox>
<Textbox Name="textbox3">
<Width>1in</Width>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Height>0.25in</Height>
<Value>Test Box 3</Value>
</Textbox>
</ReportItems>My suggestion is to post this in the web forum. The newsgroup is pretty much
devoid of MS people and this is a pretty specific and unusual question, very
few people have generated RDL themselves. If you go to the web forum not
only might a MS person be hanging out there and provide an answer, the other
RS MVP hangs out there and he might be able to help you.
http://forums.microsoft.com/msdn/showforum.aspx?forumid=82&siteid=1
Bruce Loehle-Conger
MVP SQL Server Reporting Services
<moviegoer22@.gmail.com> wrote in message
news:1185472600.783495.246480@.22g2000hsm.googlegroups.com...
> Howdy,
> I'm generating a series of RDL reports using Python and Cheetah. It
> seems to work great with one, huge problem: when I go to view the
> reports in Visual Studio or when they're deployed, the report elements
> overlap or are out of order.
> Is there any non-absolute way to position elements using RDL? I can't
> figure out how to even take just 3 textboxes and get them to flow down
> a page without specifying their TOP coordinates. Sounds basic, but I'm
> butting my head against the wall here. At best, they seem to flow in
> reverse order.
> Here's an example of some simple RDL that ends up overlapping (in
> Visual Studio, which also warns me about overlapping) - any help at
> all, conceptually or otherwise, would be much appreciated:
> <ReportItems>
> <Textbox Name="textbox1">
> <rd:DefaultName>textbox1</rd:DefaultName>
> <Width>1in</Width>
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingRight>2pt</PaddingRight>
> <PaddingTop>2pt</PaddingTop>
> </Style>
> <CanGrow>true</CanGrow>
> <Height>0.25in</Height>
> <Value>Test box 1</Value>
> </Textbox>
> <Textbox Name="textbox2">
> <rd:DefaultName>textbox2</rd:DefaultName>
> <Width>1in</Width>
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingRight>2pt</PaddingRight>
> <PaddingTop>2pt</PaddingTop>
> </Style>
> <CanGrow>true</CanGrow>
> <Height>0.25in</Height>
> <Value>Test Box 2</Value>
> </Textbox>
> <Textbox Name="textbox3">
> <Width>1in</Width>
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingRight>2pt</PaddingRight>
> <PaddingTop>2pt</PaddingTop>
> </Style>
> <CanGrow>true</CanGrow>
> <Height>0.25in</Height>
> <Value>Test Box 3</Value>
> </Textbox>
> </ReportItems>
>