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
>

Binding dataset to a reportviewer control

Hi,

I need to bind my custom dataset, which i retrieve by executing some query to a reportviewer control. I don't want to create a typed dataset in the application. Is there any way to do it? The dataset that i bind will be built in a seperate class. If I plan to use a Object Data source, what are the steps to follow. Pls help.

thanks,

Saravanan

www.gotreportviewer.com provides information about how to use object data sources specifically at: http://www.gotreportviewer.com/objectdatasources/index.html

-- Robert

|||

Hi Robert,

Thanks for the reply. I already read through that article. What i really need is to bind a dataset ( which i create in my application) to be bound to the report viewer. The dataset will have some columns from the database ( thro a select query), some columns may be added by me. I want to bind this custom dataset to the report viewer control (local mode processing). Please let me know if this can be done and also some steps to do it.

Thanks in advance,

Saravanan.

Binding dataset to a crystal report

Hi all,

The code I used is as follows.

dim dbconnection
as new oledb.oledbconnection(....conn string...)

Dim SQL As String

SQL = "SELECT * FROM <TABLENAME> "

dbConnection.Open()
Dim objAdapter As New OleDb.OleDbDataAdapter(SQL, dbConnection)
Dim objDataSet As New DataSet
objAdapter.Fill(objDataSet)
Dim oReport As New CrystalDecisions.CrystalReports.Engine.ReportDocument
Dim sRptPath As String = Server.MapPath("CRlMT.rpt")
oReport.Load(sRptPath)
oReport.SetDataSource(objDataSet)
CrystalReportViewer1.ReportSource = oReport

when i ran it I got the following error.

Logon failed. Details: ADO Error Code: 0x Source: Provider Description: Authentication failed. Native Error: Error in File <path>.rpt: Unable to connect: incorrect log on parameters.

what I am doing wrong here. Help is greatly appreciated.

note: asp.net web app.Make sure the file exists at the application path. Also make sure if you have permission to access the databasesql

Bind my own DataSet that I get in my WebApp and show in the Reporting Services - is it pos

All,
Does anybody know if I can bind my own DataSet dynamically getting it in my
WebApp and show it in the Reporting Services using the services as a
formatter/viewer of this DataSet - is it possible?
Or all I can do with the RS 2000 is just a new VD for each new report and I
have to use this URL to show anything from my app? I didn't see anything
helpful allowing us to integrate the Reporting Services into WebApp yet on
the fly. What's the difference if the RS generates the DataSet or it's
generated by my application? The second way is much more flexible and
practical. Is it already implemented in the RS 2005? I saw the controls, but
didn't test them yet since my solution is not convertible to the VS2005 with
the new Reporting Services.
Just D.VS 2005 have two new controls. Webform and winform. You can use them in
local mode and give them the dataset. In local mode you do have more you
have to do (subforms etc).
In 2000 you would have to create a data processing extension (which would
work for either 2000 or 2005).
RS is designed as a service oriented application. You request a service and
RS implements it. You can call stored procedures easily or use SQL
statement. Pass in the parameters from your app and let RS create the
dataset.
Any particular reason you want to be passing a dataset. If you let RS handle
it then it will automatically do a lot for you. For instance, take the issue
of subreports. If you are handling the dataset then for each subreport you
need to respond to an event and generate the datasets for the subreport as
well.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Just D." <no@.spam.please> wrote in message
news:Kk5If.13830$eR.455@.fed1read03...
> All,
> Does anybody know if I can bind my own DataSet dynamically getting it in
> my WebApp and show it in the Reporting Services using the services as a
> formatter/viewer of this DataSet - is it possible?
> Or all I can do with the RS 2000 is just a new VD for each new report and
> I have to use this URL to show anything from my app? I didn't see anything
> helpful allowing us to integrate the Reporting Services into WebApp yet on
> the fly. What's the difference if the RS generates the DataSet or it's
> generated by my application? The second way is much more flexible and
> practical. Is it already implemented in the RS 2005? I saw the controls,
> but didn't test them yet since my solution is not convertible to the
> VS2005 with the new Reporting Services.
> Just D.
>|||I've got a case where I need to bind to a dataset that I fetch. In my
case, I need to get a stored definition of a user-customized query, and
generate RDL from that definition. I then need to fill a weakly-typed
dataset to be used as the data source for the generated report.
Any reference on how to use pre-filled datasets in SRSS? I've been
searching Google and MSDN, and have so far come up short.
Thanks in advance.
Bruce L-C [MVP] wrote:
> VS 2005 have two new controls. Webform and winform. You can use them in
> local mode and give them the dataset. In local mode you do have more you
> have to do (subforms etc).
> In 2000 you would have to create a data processing extension (which would
> work for either 2000 or 2005).
> RS is designed as a service oriented application. You request a service and
> RS implements it. You can call stored procedures easily or use SQL
> statement. Pass in the parameters from your app and let RS create the
> dataset.
> Any particular reason you want to be passing a dataset. If you let RS handle
> it then it will automatically do a lot for you. For instance, take the issue
> of subreports. If you are handling the dataset then for each subreport you
> need to respond to an event and generate the datasets for the subreport as
> well.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Just D." <no@.spam.please> wrote in message
> news:Kk5If.13830$eR.455@.fed1read03...
> > All,
> >
> > Does anybody know if I can bind my own DataSet dynamically getting it in
> > my WebApp and show it in the Reporting Services using the services as a
> > formatter/viewer of this DataSet - is it possible?
> >
> > Or all I can do with the RS 2000 is just a new VD for each new report and
> > I have to use this URL to show anything from my app? I didn't see anything
> > helpful allowing us to integrate the Reporting Services into WebApp yet on
> > the fly. What's the difference if the RS generates the DataSet or it's
> > generated by my application? The second way is much more flexible and
> > practical. Is it already implemented in the RS 2005? I saw the controls,
> > but didn't test them yet since my solution is not convertible to the
> > VS2005 with the new Reporting Services.
> >
> > Just D.
> >
> >|||If you are creating your own RDL then the way to go is to definitely get the
new controls with VS 2005. In local mode you don't need any server. You can
give the report the rdlc and the dataset.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Kevin L." <kevin.lowe0@.gmail.com> wrote in message
news:1139862643.634896.4400@.o13g2000cwo.googlegroups.com...
> I've got a case where I need to bind to a dataset that I fetch. In my
> case, I need to get a stored definition of a user-customized query, and
> generate RDL from that definition. I then need to fill a weakly-typed
> dataset to be used as the data source for the generated report.
> Any reference on how to use pre-filled datasets in SRSS? I've been
> searching Google and MSDN, and have so far come up short.
> Thanks in advance.
>
> Bruce L-C [MVP] wrote:
>> VS 2005 have two new controls. Webform and winform. You can use them in
>> local mode and give them the dataset. In local mode you do have more you
>> have to do (subforms etc).
>> In 2000 you would have to create a data processing extension (which would
>> work for either 2000 or 2005).
>> RS is designed as a service oriented application. You request a service
>> and
>> RS implements it. You can call stored procedures easily or use SQL
>> statement. Pass in the parameters from your app and let RS create the
>> dataset.
>> Any particular reason you want to be passing a dataset. If you let RS
>> handle
>> it then it will automatically do a lot for you. For instance, take the
>> issue
>> of subreports. If you are handling the dataset then for each subreport
>> you
>> need to respond to an event and generate the datasets for the subreport
>> as
>> well.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "Just D." <no@.spam.please> wrote in message
>> news:Kk5If.13830$eR.455@.fed1read03...
>> > All,
>> >
>> > Does anybody know if I can bind my own DataSet dynamically getting it
>> > in
>> > my WebApp and show it in the Reporting Services using the services as a
>> > formatter/viewer of this DataSet - is it possible?
>> >
>> > Or all I can do with the RS 2000 is just a new VD for each new report
>> > and
>> > I have to use this URL to show anything from my app? I didn't see
>> > anything
>> > helpful allowing us to integrate the Reporting Services into WebApp yet
>> > on
>> > the fly. What's the difference if the RS generates the DataSet or it's
>> > generated by my application? The second way is much more flexible and
>> > practical. Is it already implemented in the RS 2005? I saw the
>> > controls,
>> > but didn't test them yet since my solution is not convertible to the
>> > VS2005 with the new Reporting Services.
>> >
>> > Just D.
>> >
>> >
>

Bind multi-table dataset to a datagrid

I have created a SQL procedure that returns a dataset with a varying number of tables like the following example:

RepID-- PhoneUPS
---- ----
3---- 3

RepID-- PhoneUPS
---- ----
4---- 0

RepID-- PhoneUPS
---- ----
5---- 2

No more results.
(9 row(s) returned)
@.RETURN_VALUE = 0

All of the tables have the same header row, but are a seperate table. From my experience, I am not able to bind more than one table to a datagrid. Does anyone have any suggestions on how I can go about displaying this full dataset on my aspx page? I've been going in circles on this for two days, so I'm open to any suggestions :)

Cheers,
AndrewCan you create a UNION query in the stored procedure, rather then seperate resultsets?

Failing that, you can manually add each row of each resultset to the Items collection of the Datagrid.|||Thanks for the tip. I've been working on my SQL procedure trying to incorporate the UNION in the SELECT statement. I am having problems with the logic of working it in, though. Here is the original SQL procedure I've created:

/*BEGIN Procedure */

ALTER PROCEDURE Rep_Status_Array

AS

/* Build array or ID numbers to cycle through */
DECLARE RepIDs_cursor CURSOR
FOR SELECT UserID
FROM TBL_Users
WHERE (TBL_Users.DepartmentID > 0)

OPEN RepIDs_cursor
DECLARE @.userID int
FETCH NEXT FROM RepIDs_cursor INTO @.userID

/* Begin WHILE loop to collect data for each ID */
WHILE (@.@.FETCH_STATUS <> -1)
BEGIN
SELECT
RepID=@.userID,

PhoneUPS=(SELECT Count(*) FROM TBL_UpEntry WHERE (TypeID = 11) AND (SalesmanID = @.userID)),

LOTUPS=(SELECT Count(*) FROM TBL_UpEntry WHERE (TypeID = 1) AND (SalesmanID = @.userID)),

CVR=(SELECT Count(*) FROM TBL_UpEntry WHERE (TypeID = 1) AND (SalesmanID = @.userID)),

FETCH NEXT FROM RepIDs_cursor INTO @.userID
END
/* END WHILE loop */

CLOSE RepIDs_cursor
DEALLOCATE RepIDs_cursor

/* END Procedure */

The problem I'm having with this is that each time through the WHILE loop creates a new table in the dataset that is returned by the procedure. Any suggestions?|||Is it even possible to use UNION or UNION ALL within a WHILE loop?|||To the best of my knowledge, it is not possible to use the UNION statement in conjunction with a WHILE loop.

I'm going to go with Douglas' second recommendation and manually create a table within my page, adding each row one at a time.|||Another alternative:

Create a temporary table, and then INSERT all selected rows into the temp table, and then at the end of the SP,

SELECT * FROM #temp

(with any required ORDER BY).|||Thanks for the help, Douglas, much appreciated!

Andrew

Binary_double Oracle data type support

I'm using RS 2000 and I'm getting an "unsupported Oracle data type 101 encountered" error when attempting to retrieve data from a dataset based on a plsql stored procedure. I'm assuming this is related to a recent update on our Oracle 10g database to convert columns from number to binary_double.

Can anyone confirm if this is the case, and suggest a work-around? Is binary_double supported in RS 2005?

Thanks

RS uses MS .NET provider for Oracle which currently does not support binary_float/double.

The possible workaround is to cast binary_float columns to number in your SQL statement

select cast(my_bin_float_col as number) from ...

BIDS autohide not always working

has anyone else experienced this where you have areas on BIDS set to auto
hide (like dataset or solution or properties) and they DONT autohide always
... I end up having to toggle autohide off and on again to make it hide ...
this is getting really annoying.I have it all the time. I have come across some really bad bugs in this
version. Auto-hide not working being one of them. Seems small at first, but
when you have to do it about 100 times a day, it becomes very frustrating.
They need to get this fixed. I don't remember having it in VS2003.
"MJT" wrote:
> has anyone else experienced this where you have areas on BIDS set to auto
> hide (like dataset or solution or properties) and they DONT autohide always
> ... I end up having to toggle autohide off and on again to make it hide ...
> this is getting really annoying.|||I havent even mentioned how many times I have received the message that
visual studio cannot close because there is a modal dialog active ... when I
have closed absolutely EVERY WINDOW in the tool ... there is nothing but a
SHELL left... so I end up having to force the task to end with task manager
... not a good thing. I am talking about the scaled down version of VS2005
that comes with reporting services ... not the full-blown install of VS2005.
Seems it should be addressed and fixed.
"bsod55" wrote:
> I have it all the time. I have come across some really bad bugs in this
> version. Auto-hide not working being one of them. Seems small at first, but
> when you have to do it about 100 times a day, it becomes very frustrating.
> They need to get this fixed. I don't remember having it in VS2003.
> "MJT" wrote:
> > has anyone else experienced this where you have areas on BIDS set to auto
> > hide (like dataset or solution or properties) and they DONT autohide always
> > ... I end up having to toggle autohide off and on again to make it hide ...
> > this is getting really annoying.

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.