Binding Different DataSets to One Table.

I have a situation where i am binding a Dataset Dset1 to a table.
, I also have a Dset2 which i am not binding. Now as the Rows gets
rendered depending on Dset1 i also want to populate a value from Dset2
(2nd Datset ) in a cell for a particular Row.
How do i do that?
Any help.
ThanksYou cannot bind a data region (in this case a table) to more than one
dataset. You should re-design the report so that it has one dataset that has
a query returning both the data currently in Dset1 and in Dset2. This will
most likely mean writing a JOIN SQL query.
Charles Kangai, MCDBA, MCT
"Rahul" wrote:
> I have a situation where i am binding a Dataset Dset1 to a table.
> , I also have a Dset2 which i am not binding. Now as the Rows gets
> rendered depending on Dset1 i also want to populate a value from Dset2
> (2nd Datset ) in a cell for a particular Row.
>
> How do i do that?
> Any help.
>
> Thanks
>|||That is easier to to in a store proc, identifying to temp tables and
populating your row in there - and THEN returning one data-set back to the
report server.
"Rahul" wrote:
> I have a situation where i am binding a Dataset Dset1 to a table.
> , I also have a Dset2 which i am not binding. Now as the Rows gets
> rendered depending on Dset1 i also want to populate a value from Dset2
> (2nd Datset ) in a cell for a particular Row.
>
> How do i do that?
> Any help.
>
> Thanks
>

beware of avg() for large datasets

Not a question, just a post about something I ran into today that surprised me a little.

I have a huge dataset - about 700m rows, one column of which is a tinyint. I needed to take an average of that column, so I did this:

select avg(mytinyint) from mytable

which returned:

Msg 8115, Level 16, State 2, Line 6
Arithmetic overflow error converting expression to data type int.

It turns out, if the sum() over the column is bigger than MAX_INT = 2147483647, then avg() will throw this error. This surprised me a little - I guess I was assuming sql server would use bigint internally when computing integer avg, but it uses int.

so you have to do this:

select avg(cast(mytinyint as bigint)) from mytable

what's annoying to me is, it's impossible that an average of type T could be larger or smaller than the max/min allowed value of a type T. This means sql server devs could have internally always used bigint for sum and count, and then returned cast(sum/count as T) from avg() without possibility of overflow (unless sum or count was bigger than the max bigint! you are on your own if that happens i guess!).

Because of this, you have to cast your column to a bigint to make it work if your dataset is large enough, even if the column is only 1 measly byte! kind of a waste of space if you ask me. :)

here's a little example of what I'm talking about, that doesn't require you to import a 700m row dataset :)

declare @.t table (id int)
insert into @.t select 1 union all select 2147483647
select avg(cast(id as bigint)) from @.t -- works
select avg(id) from @.t -- failshmm...:rolleyes:|||Very interesting. I will check it out tomorrow. Thanks for posting it.