Automatically Generating PDFs With Reports Requiring Parameters

Hi,
I'm working on many reports that are generated using SQL Server
Reporting Services SP 1 from an ASP.NET 1.1 web application. One of the
things the users would like to do is have the report automatically
generate a PDF when they click on the View Report button after entering
the parameters for the report. Parameters could be a date, a city, a
state, etcetera. However, they would like to avoid having to choose the
format and click the Export link. Is there a way to manipulate the
functionality of the View Report button such that it does this
automatically? Thank you for any insights you can provide.
JabooHow are you accessing the reports?
1) Report Manager
2) URL parameters
3) Web Service
Kulgan.sql

Automatically Export reports in SQL Reporting services 2000

Is it possible to have a report automatically export the results to Excel, when the report is generated? ...rather than having to select the format and click Export? .Pl let me know-Thanks.

I found this article some days ago!

http://ryanfarley.com/blog/archive/2006/01/27/15689.aspx

sql

Automatically Export reports in SQL Reporting services 2000

Is it possible to have a report automatically export the results to Excel, when the report is generated? ...rather than having to select the format and click Export? .Pl let me know-Thanks.

I found this article some days ago!

http://ryanfarley.com/blog/archive/2006/01/27/15689.aspx

automaticall render as pdf after parameter selection

I am using reporting services and have a report that requires three
parameters. After the parameters are selected and the report is generated by
clicking the View Report button, I want the report to default to pdf view
instead of having to select that format and export after running it. Is this
possible? This way users can print or save from the pdf view without having
to take the extra step of exporting.
Thank you for your help!
SharlynI'm also interested in it.
tnx.
Tom.sql

Automatic refreshing

I am wondering if it is possible to have a report generated by RS refresh periodically automatically. This could be realized by inserting a few lines of JavaScript to the report including the reload() function, but I do not know if there is anyway to do such thing.

Thanks in advance for any tip!

You could create an HTML page with an <iframe> in it and set the iframe src property to point to the report. Then add a http-equiv to the html page to refresh it however often you need. - RKS|||

That is a good tip. Thanks!

I have just realized there is another issue. The page refreshing is different from clicking the "Refresh" button of the report when there are parameters. Page refreshing uses the default values of paramters, but the report "Refresh" button uses the chosen values. For example, suppose patient ID is a parameter and we would like to allow the user to choose a patient ID and the report would refresh itself every minute using that particular patient ID. Using page refreshing, we would need to create a report for each patient to achieve this. Any idea to address this?

|||There is an Autorefresh property of the report that refreshes an HTML view based on the entered property.

In your designer, open the report, look at the report properties and you'll see it.|||

Perfect!!! Thanks a lot, Andy!

I have just tested it and it worked exactly the way we want.

Automated creation

Hello,
I was curious if, through code or some other process, if you can automate the creation of a report. I have reports I need generated after a DTS package imports data from the data dictionary. I want the reports in excel format. Is there an automated way of creating these reports? I'm sure there is, but I'm not that familiar with Reporting Services.

Hi bmains

Try using subscriptions with default permissions. You could then output the reports to a share or mail it to users.

,l0n3i200n

Auto-generating a DateTime in SQL Server Express 2005

Is it possible to have a DateTime field in a row automatically generated when a row is created?

ie:

INSERT INTO [TableName] ([DateTimeStamp], [UserId], [Title], [Explanation]) VALUES (DateTime.Now(), @.UserId, @.Title, @.Explanation)

or something like that?

You can set up a default value of GetDate() to the column in the design view of the table.

|||

Thanks! Exactly the solution I was looking for.

Autogenerate reply or ServerProc generated message............

Hello,

I got my sample application to work that I am building my proof of concept out of. I need to be able to auto generate a reply message that would normally be in the run routine. The only way I see to do this is pull it from a table but I do not want to do that. I have tried tests where I change the code to see if I can send a message back but I have to reinstall the assembly into the database which is not what I am looking for. I am looking for a way to change the message by either accessing the GUI and getting the string, calling another function to do this, or something like that. I want it so I can change the code in the run routine in VS 2005 but this does not work. I am sure their is a trick to do this but am not sure what that trick is. What is a good way to do this other than accessing a table in the database?

Thanks,

Scott Allison...

What do you mean by auto-generate a reply? A reply could either be static (i.e. independent of the request and state), a stateless (i.e. dependent only on the request) or stateful (i.e. dependent on both the request as well as some state... the state would normally be stored in the database, but may also be stored separately). I cannot see why you need to recompile and redeploy your app to meet any of the above patterns.

Rushi

|||

Hello Rushi,

It calls the ServiceProc in my code and that is loaded into the database. I am writing this for applications that will need to report the status of a current event. If this is not stored in the database then they would not want to write anything to the database. This means they will get the request then need to reply to that request with a text message that they generate. In the worst case situation it can come from the database but would rather just be retrieved from a function call. It seems as though from your message this can be done so I am sure I have a setting incorrect. What I have is currently static and I want the response dependent on the request. What I meant from Autogenerated is retrieved from another assembly or another part of that service.

Thanks,

Scott Allison...

|||If the function that generates a response from request is a method in a different assembly, you could deploy that assembly into the database and simply invoke the method from ServiceProc.|||

Hello Rushi,

This is a remote system and will not be able to deploy all of the assemblies to the database. It consists of several services and needs dynamic access to what the message. Are you telling me that the only way to get a dynamic message is to write it to a database and have the ServiceProc read it? This is a draw back beacuse it will slow things down but I want to make sure before I tell people that they have to do that.

Thanks,

Scott Allison...

AUTO_CREATE_STATISTICS & AUTO_UPDATE_STATISTICS are Null

What is the real net affect of these 2 parameters set to NULL?

How can I tell if stats have been generated for all tables in a db?

I am guessing that stats don't exist for the db and this accounts for the high degree of scans I am seeing on a 1.5gb db running on a 4 gb memory machine.

Any thoughts?
MikeThe net effect is that you have to create the stats your self and also update then on a timely basis.

Personnaly would turn them both on. Some people would argue to turn on the create but do the update on a scheduled basis, the thought is that you can get by with slightly out of date stats rather than take the performace hit to auto update.|||so the bottom line is the optimizer will not have any information on the tables and therefore do scans to find the data?

Is this an accurate description?

Mike|||basically, Yes.|||and what is the most expedient mthod of generating the stats for all objects?|||BTW, once you create statistics, you should recompile all views, triggers, stored procs and re-build all indexes starting with any clustered indexes.|||turn AUTO_CREATE_STASTISTICS & AUTO_UPDATE_STATISTICS on.|||Originally posted by Paul Young
turn AUTO_CREATE_STASTISTICS & AUTO_UPDATE_STATISTICS on.

I just found out each weekend the indexes get rebuilt. Dropped and recreated. I read that whenever an index is created on a table containing data the optimizer collects stats and stores them. Does that still occur when these options are not on?

Mike|||Still recommend to turn those two options on. The major purpose of rebuilding index is not for updating statistics. Even if rebuild index will
update statistics, but the statistics may not be updated during the week.

BTW, never recreate index by drop-and-create strategy. Try to use DBCC DBREINDEX, or CREATE INDEX with DROP_EXISTING, or DBCC INDEXDEFAG.

Auto Number

Hi,
I want to create a random unique auto generated number. dont want
to use GUIDs. Any other way to create it in isert statement itself,
apart from storing a number in a table and increment while inserting.
Help me!On 3 Apr 2006 13:48:16 -0700, Ramaarneh wrote:

>Hi,
> I want to create a random unique auto generated number. dont want
>to use GUIDs. Any other way to create it in isert statement itself,
>apart from storing a number in a table and increment while inserting.
>Help me!

Hi Ramaarneh,

Strange requirements. Why not a GUID? Why not store it in a table?

But if you must, then you might consider using the RAND() function. Put
it in a loop and iterate until an unused value is found. (Make sure that
the range of numbers that the RAND() can generate is at least 1000 times
bigger than the expected number of rows, to make sure that the loop will
iterate just once in most cases - otherwise, it'll be a performance
hog).

--
Hugo Kornelis, SQL Server MVP|||Ramaarneh wrote:
> Hi,
> I want to create a random unique auto generated number. dont want
> to use GUIDs. Any other way to create it in isert statement itself,
> apart from storing a number in a table and increment while inserting.
> Help me!

Let's assume you can create a table of numbers from 0 upto the largest
number you need.

CREATE TABLE numbers (num INTEGER NOT NULL PRIMARY KEY);

INSERT INTO numbers VALUES (0)
WHILE (SELECT MAX(num) FROM numbers)<32768
INSERT INTO numbers
SELECT num+(SELECT MAX(num)+1 FROM numbers)
FROM numbers ;

Now you can easily populate another table with random selections from
your Numbers table:

CREATE TABLE your_tbl (num INTEGER NOT NULL PRIMARY KEY);

INSERT INTO your_tbl (num)
SELECT TOP 1 N.num
FROM numbers AS N
LEFT JOIN your_tbl AS T
ON N.num = T.num
WHERE T.num IS NULL
ORDER BY NEWID() ;

The beauty of this is that you can periodically delete the used ones
from Numbers and you can repopulate it as often as you like. True, it
probably won't scale well to millions of rows but it has worked pretty
well for me with smaller data sets.

Hope this helps.

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/...US,SQL.90).aspx
--|||Here is an implementation of the additive congruential method of
generating values in pseudo-random order and is due to Roy Hann of
Rational Commerce Limited, a CA-Ingres consulting firm. It is based on
a shift-register and an XOR-gate, and it has its origins in
cryptography. While there are other ways to do this, this code is nice
because:

1) The algorithm can be written in C or another low level language for
speed. But math is fairly simple even in base ten.

2) The algorithm tends to generate successive values that are (usually)
"far apart", which is handy for improving the performance of tree
indexes. You will tend to put data on separate physical data pages in
storage.

3) The algorithm does not cycle until it has generated every possible
value, so we don't have to worry about duplicates. Just count how many
calls have been made to the generator.

4) The algorithm produces uniformly distributed values, which is a nice
mathematical property to have. It also does not include zero.

Generalizing the algorithm to arbitrary binary word sizes, and
therefore longer number sequences, is not as easy as you might think.
Finding the "tap" positions where bits are extracted for feedback
varies according to the word-size in an extremely non-obvious way.
Choosing incorrect tap positions results in an incomplete and usually
very short cycle, which is unusable. If you want the details and tap
positions for words of one to 100 bits, see E. J. Watson, "Primitive
Polynomials (Mod 2)", Mathematics of Computation, v.16, 1962,
p.368-369.

We need to tap bits 0 and 3 to construct the 31-bit random-order
generated value Generator (which is the one most people would want to
use in practice):

UPDATE Generator31
SET keyval =
keyval/2 + MOD(MOD(keyval, 2) + MOD(keyval/8, 2), 2)* 8;

Or if you prefer, the algorithm in C:

int Generator31 ()
{static int n = 1;
n = n >> 1 | ((n^n >> 3) & 1) << 30;
return n;
}|||Ramaarneh (ramkumar.saranathan@.gmail.com) writes:
> I want to create a random unique auto generated number. dont want
> to use GUIDs. Any other way to create it in isert statement itself,
> apart from storing a number in a table and increment while inserting.

In addition to the other suggestions is to use checksum(newid()). However,
beware that this will occassionally give you duplicates, so you must be
able to handle this some way.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

auto increment in SQL

Hi.
Am creating a database in SQL and one of the field of my table is customer_id. I want this customer_id to be auto generated and auto increment. I was reading about Identity but it was too complicated to grasp. So how can that be done in a very simple way.
Thanks

As you create a table you specify the identity attribute. Then whenever you insert a row into the table, you will not need to specify a value for the identity column rather the database will do it for you.

Code Snippet

CREATE TABLE customers ( cust_id int identity(1,1), Name char(20) )

The first value is the seed and the second value is the increment. The seed is what value the sequence starts at and the increment is how much the identity column gets incremented after each insert.

Code Snippet

insert customers (Name) values ('Bill')
insert customers (Name) values ('Hank')
insert customers (Name) values ('Joe')
select *
from customers

Notice how you didn't need to specify a value for the identity column.

Code Snippet

cust_id Name
--
1 Bill
2 Hank

3 Joe

Bill gets an id of 1 since he was the first row inserted and thus gets the seed value. The identity value is now at 1. Hank gets a 2 because he gets the current identity value + increment value (1+1). The identity value is now at 2. Joe gets a 3 because he gets the current identity value + increment value (2+1).

|||Hi.
Thanks for replying.
Well to be more precise i am creating the SQL database within Visual Basic Express Edition(http://msdn2.microsoft.com/en-us/library/ms172599(VS.80).aspx).
As such am not typing any code, just creating table like in access.
So how should i do from there. Dont know where to find the coding when i have creating the table.|||

I believe that if you examine the properties for each column (in the lower part of the table design window), you will see that there is a property for IDENTITY. Set that property to Yes/True.

|||Hi.
thanks for replying. so stupid from my part.

auto generated indexes

when viewing a database in the taskpad view from
enterprise manager, i notice there are a lot of indexes on
tables that have been automatically generated. they are
named like _WA_Sys_TransactionID_479C827A (where
TransactionID is a column in the table). Does someone
know where they come from and what they are used for?
There are more than one on some tables.
ThanksMaria
These are not indexes, they are just statistics on columns that are
auto-generated when the optimizer is trying to come up with a plan involving
the columns. Knowing the data distribution in the column can help the
optimizer come up with a better plan. By default, they are created on any
unindexed column used in a query. This is controlled by a database option
'auto create statistics'.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Maria" <mariac@.rcspf.co.za> wrote in message
news:2334101c38c21$f4de13b0$a601280a@.phx.gbl...
> when viewing a database in the taskpad view from
> enterprise manager, i notice there are a lot of indexes on
> tables that have been automatically generated. they are
> named like _WA_Sys_TransactionID_479C827A (where
> TransactionID is a column in the table). Does someone
> know where they come from and what they are used for?
> There are more than one on some tables.
> Thanks

Auto generated CRUD in Sql 2005 issue

Here is our problem. If you right click on a table in Management studio it gives you the option of creating The Delete, insert, select and Update stored procedures for any table. The problem is that the auto generation script includes the database name in the stored procedures. So if we have a database called DB_DEV and we move the stored procedures over to database DB_QA these stored procedures are now trying to access the wrong database. Is there a way to make sure that the database name is not included?

If I am reproducing your steps correctly, I see that SSMS will 'auto-magically' write a query for one of the CRUD actions -but it's not a stored procedure.

You may wish to combine that action with using a Stored Procedure template -but as far as I can determine, you'll have to manually remove the dbname.

|||

Could you please post the script you see, and the version of SQL Server you are using?

You can try it on a simple table and not necessary your primary one.

When I try to reproduce your problem, the CRUD script created has a "use [<dbname>]" at the beginning of it. If this is the case for you, you can simply remove this line from the script and it will be applicable to any database.

|||Why not use Edit / Find and Replace after you generate the script?