How to make data sets of one report also available for other reports?

Hi, all experts here,

Thank you very much for your kind attention.

I am building reports on SQL Server 2005 Reporting Services.

I am having a question about how to make the data sets of one report for other reports as well within the same report project? As we may need to use the same data sets for report parameters settings. Really need help for that.

Thanks a lot in advance for any guidance and help for that.

With best regards,

Yours sincerely,

In one report, you can use the same dataset for parameters and the body of the report. But currently it's not possible to reuse the datasets of one report for other reports. It is on our wishlist for a future release.|||

Hi, Fang, thanks a lot for your information.

With best regards,

Yours sincerely,

how to make an alias here ?

Hi experts,
i am working with SQL-Server 2000 and have a special problem in one of my
stored procedures. To be as fast as possible i use a temp table defined as
variable. And i want to calculate a value in this table depending on values
in the same table. But i can't make it work. Here my problem:
-- can be copied into QueryAnalizer --
declare @.tbl table (
id1 int,
id2 int,
w1 int,
w2 int )
insert into @.tbl values (1, 2, 12, null)
insert into @.tbl values (2, 3, 10, null)
insert into @.tbl values (3, null, 7, null)
-- this is my wish. It doesn't work, because alias t1 is not valid, but i
think you can see what i want to do:
-- update @.tbl t1 set t1.w2 = t1.w1 - IsNull((select IsNull(t2.w1,0) from
@.tbl t2 where t2.id1 = t1.id2),0)
-- and this i all i can do, but with wrong result:
update @.tbl set w2 = w1 - IsNull((select IsNull(w1,0) from @.tbl t2 where
t2.id1 = id2),0)
select * from @.tbl
----
gives me:
1 2 12 12
2 3 10 10
3 NULL 7 7
and this is what it want to have:
1 2 12 2
2 3 10 3
3 NULL 7 7
I think, the problem is because in ... where t2.id1 = id2) the value of id2
is not the value of the atually updating record, it stays on id2 of the
last insert, which means, id2 is always NULL. And i can't use an alias (why
is an alias here not possible? This is not clear to me). What can i do?
thanks,
Helmuthelmut woess wrote:
> Hi experts,
> i am working with SQL-Server 2000 and have a special problem in one
> of my stored procedures. To be as fast as possible i use a temp table
> defined as variable. And i want to calculate a value in this table
> depending on values in the same table. But i can't make it work. Here
> my problem:
> -- can be copied into QueryAnalizer --
> declare @.tbl table (
> id1 int,
> id2 int,
> w1 int,
> w2 int )
> insert into @.tbl values (1, 2, 12, null)
> insert into @.tbl values (2, 3, 10, null)
> insert into @.tbl values (3, null, 7, null)
> -- this is my wish. It doesn't work, because alias t1 is not valid,
> but i think you can see what i want to do:
> -- update @.tbl t1 set t1.w2 = t1.w1 - IsNull((select IsNull(t2.w1,0)
> from @.tbl t2 where t2.id1 = t1.id2),0)
>
update t1 set t1.w2 = t1.w1 - IsNull((select IsNull(t2.w1,0)
from @.tbl t2 where t2.id1 = t1.id2),0)
from @.tbl t1
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.|||Am Thu, 15 Dec 2005 09:16:21 -0500 schrieb Bob Barrows [MVP]:

> helmut woess wrote:
> update t1 set t1.w2 = t1.w1 - IsNull((select IsNull(t2.w1,0)
> from @.tbl t2 where t2.id1 = t1.id2),0)
> from @.tbl t1
Incredible, how fast you came up with the solution!
thousand thanks, Bob, you saved me a lot of time!
Helmutsql

How to make a db readonly to accept tran logs?

Hi experts,
Pls let me know , how to make normal db to readonly + accept tran logs
Rgds
Srinivas Varanasi.1) Backup the database
2) Restore the backup with the appropriate options.

Note that while this does exactly what you asked for, but it makes absolutely no sense. Can you describe what you want in a bit more detail, so that someone can give you a more usable answer?

-PatP|||Srini, you need to do this on the full restore:

RESTORE database_name
FROM DISK = 'blah.bak'
WITH
MOVE 'logical data file' TO 'c:\mssql\data\blah.mdf',
MOVE 'logical log file' TO 'c:\mssql\log\blah.ldf',
STANDBY = 'c:\mssql\standby\blah.ldf'

The standby creates a standby file and makes the database read-only. You can then restore your tlog backups.

How To Load Periodic SnapShot Fact Table With SSIS

I need help from you data warehouse / SSIS experts out there! I have a Transaction Fact Table with dollar amounts as the measurements. The grain is one row per transaction. I want to roll this up into a Monthly Periodic Snapshot based on 5 keys. I am having no problem where there is transaction data for each month.

However, the problem I am having is - how do I gracefully insert the Monthly rows for the five keys where there was no activity in the transaction fact table - I am sure there is a slick way to do this with SSIS but I am definitely having a mental block on how to accomplish this. Any help would be appreciated!

Hopefully you have a method of deriving all possibly combinations of the 5 key columns. Personally I would do this by extracting all possible values from the dimension tabes, unioning them together but where each column creates a new column in the output from the UNION ALL and then use the Aggregate component to produce all of the combinations.

Once you have done that you can use a MERGE JOIN component to join to your dataset and produce nulls/zeros for teh fact values for all of the missing rows.

-Jamie

|||

Thanks Jamie. I will give that a shot.

-Steve

How to know when a job has finished. For Experts I think.,

I have this on my page
Dim backUpDB2 As SqlClient.SqlCommand
backUpDB2 = New SqlClient.SqlCommand
backUpDB2.CommandType = CommandType.StoredProcedure
backUpDB2.CommandText = "msdb.dbo.SP_RESUMENFAC"
backUpDB2.Connection = SqlConnection1
backUpDB2.ExecuteNonQuery()

The SP has this

CREATE PROCEDURE .[SP_RESUMENFAC] AS

EXEC sp_start_job @.job_name = 'TransferirDatos(FACT) '

GO

WHen I execute the page after the SP it fills some datagrid but the data is not updated bacuase the job takes 1 minute or more to finish.

Is there anyway to prevent to show the old data? or to detect when the job has finished?

ThanksI think i would look for a way to get the execution status of the job instead.

If the job is not in an idle state, you can display something appropriate on the page.

look atsp_help_job

@.execution_status will tell you what the job is doing...
0 Returns only those jobs that are not idle or suspended.
1 Executing.
2 Waiting for thread.
3 Between retries.
4 Idle.
5 Suspended.
7 Performing completion actions.|||it says COuldnt Find Procedure.

Should I install something on the server. Its Sql Serrver 7 I think.|||its in MSDB

USE msdb
EXEC sp_help_job|||use msdb
EXEC sp_start_job @.job_name = 'TransferirDatos(FACT) '

This is the job, How can I Know when this has done and show a progress bar?|||side note: Don't prefix your procs with "sp_" this should be reserved for MS procs only and you'll get stung performance wise if you don't remove it.|||if your stored proc looks like this:

USE msdb
EXEC sp_help_job NULL, ''TransferirDatos(FACT)' ,'JOB'

you will get back a single recordset.

Look at thecurrent_execution_status column for the jobs status.
My prev post has the different results for this column.

you wont get progress (like 50% complete etc...)
but you will get Executing or Idle etc...