How to manage click-through

How will I possibly do click through on this type of chart when basically I'll have to bring in just Grand Total values to do what they want to do here in this mock-up:

SSRS 2005

Expected/Desired Outcome (user drew this using Excel and later wants click-through capability through SSRS 2005 on the bars later on):

http://www.webfound.net/chart3.jpg

I don't see how this is possible, can y ou shed some light? because basically what I'll have to do to get that few of bars is literally bring in just Grand Totals for the series....static Grand Totals from my dataset's stored proc calculation since I cannot perform really a GT by using SUM in the expressions for any fields in the chart or I'll get an error

Hi,

In SSRS 2005 charts you can do the click through. In the chart layout. Just go to "Drop Data fields here" right click on the button for properties and on the Action tab you can select Jump to report.

Before that you need to create a seperate report so that when you click on the value this particular report comes up.

Any doubts let me know.

Amarnath.

|||

thanks much. What about infinite click-through?

Can I even create charts that do this just like inifinte click-through for reports using models?

How to make an insert table lock

Hi There

i Have some t-sql that basically does something like :

INSERT INTO TABLEA SELECT * FROM TABLEB

There are alot of rows involved and sp_lock tell me that a table lock is being put on TABLEA during this insert.

This is a big problem for us. I have often used locking hints on selects etc but never an insert, how can i get the insert to never use a table lock ?

Thanx

You always have a lock on insert on something and have no control over the escalation of locks. Hints are only a SUGGESTION of how the server should handle the query. The server determines the locks used based on the statement.

In your case, it is locking the table, because it has no idea how many inserts will be done or where they will be placed in the physical table.

You can set your read level to "READ UNCOMMITTED" and that will allow people to read the table during the insert process.
|||

Hi Tom

All good suggestions we have thought of but not possible.

Here is the situation, we are converting in new branch data into a central database, so we know that all rows inserted will be new data and at the end of the clustered index.

The other process doing the read is ETL which will never be interested in this new data, however the way it works combined with slowly changing dimensions we have to set it to read committed or the ETL will not work properly. The inserts are essentially locking an entire table and will never touch any of the rows that the ETL is trying to read (there are no updates) , however obviously the table lock pretty much stops all ETL.

So basically we are not sure what to do?

ALl i can do so far is commit as often as possible , but this only slightly helps the situation, bottom line is i wanted to know if there was anyway to control locking with an insert but you have answered that.

Thanx

|||With the more information you provided, I would change your INSERT into a BCP or BULK INSERT.

The INSERT...SELECT * is pretty slow. If you export the data and use BCP or BULK INSERT, you will be 100 times (or more) faster and will lock the target table for a much shorter time. Also set the batch size to a number like 10,000 or 50,000 which will insert that many records as 1 transaction and drop the lock. That might let the ETL lock the table, and delay the import, but the ETL will finish.

This might fix your problem.

|||

Hi Tom

Thanx i will try that.

However the process must be automated, so i guess i will have to write say an ssis package that first exports the data to file and then uses bcp to import it back into the other tables?

Thanx

|||

Hi, Tom.

Is it possible to perform a BULK INSERT from one table to another in the same SS05 database, where both tables have the same structure, and one has been TRUNCATED to accept the replacement data from the other?

I have been studying the T-SQL documentation and I keep noticing "glimmers of hope" that make it appear as if it is possible (without having to export the data and then import it), but I am unable to discern the syntax for the INSERT statement.

Does this look anywhere close to being correct?

INSERT INTO table_a (IGNORE_CONSTRAINTS)

SELECT *

FROM OPENROWSET(BULK

(And that's where I get stuck, since BULK seems to want a reference to an external data file, NOT a table within the same database on the same server, with only a different table name.)

Dan

P.S. Maybe it must be done outside the T-SQL environment, such as shown in VB and C# examples at http://msdn2.microsoft.com/en-us/library/system.data.sqlclient.sqlbulkcopy.aspx ? I was hoping there was a T-SQL solution.

(I just noticed an interesting comment in the C# example:

// Open the destination connection. In the real world you would
// not use SqlBulkCopy to move data from one table to the other
// in the same database. This is for demonstration purposes only.

So what is the "real world" solution for bulk copying data from one table to another in the same database?)

|||

Tom

Any ideas ?

To uswe bulk insert i would first require a process to export to file and then back in again the time again on bulk insert would perhaps be lost on exporting to data to file and re-importing with bulk insert using SSIS or something ?

Is there a way to bulk insert data from 1 table to another using just tsql , and no exported data to file ?

Thanx

|||It is ALWAYS faster to export the file and bulk insert again, even with the added overhead of exporting it to file. The other methods require tons of writes to tempdb and log files, etc.

You should really try it as bulk insert. You wil be much happier.

The "real world" solution, as you would see from the MS copy data options in DTS and SSIS, are exporting the data and using BCP/BULK INSERT to reinsert them, because it is much faster.

|||

Tom,

Thanks.

Dan

|||

Hi Tom

Thanx, i was hoping to be able to do it in tsql and avoid ssis.

By the way is there now ay to bulk insert between tables ? Must bulk insert have a flat file source, seems that way from BOL.

Cheers

|||Look at the "bcp out" command. You can do that from tsql using EXEC.

how to loop through my workflow by looking at the file system?

Hi, not sure my subject title makes it clear what I want but here it is.

I have a workflow which basically looks at an excel file in a folder on the local drive and then does loads of stuff to it. Everytime I want to process a different excel file that is in the same location al I have to do is change the value of a single local variable, which is just the name of the excel file.

Is there a way to make this automatic? For exmaple....could I somehow put my whole workflow inside a loop that looks inside that local folder and one by one, get the name of the file, assigns the name to that global variable, and then runs the flow...and continues to do that until it gets to the end?

any help would be greatly appreciated...thanks!!!!

andy

Have you look at the ForEachLoop container in the control flow? you can use it to loop through each excel file in a specific folder.

|||awesome, thanks!