Defining dataset query in vb.net

Hi all,
there are lots of objects about defining the report data source ,data set,
query etc.
but is there any examples of defining them and assign to a report?
i feel confuse cos i dun quite know how to use them.(i want to construct the
Query in web form first then pass to the report.)
thanks in advance!hi all,
may be i should post my questions clearly,
i know how to assign a datasource to the report using setreportdatasource(),
but|||BUT
HOW TO ASSIGN A DATASET TO A DATASOURCE, AND
HOW TO ASSIGN A QUERY TO A DATASET?
i dun know if my concept is corroect or not, please let me know if i have
said something wrong
thanks in advance.|||Two points. First, the way you are envisioning it today can not easily be
done. People do this but it is not an out of the box experience. By that I
mean, it is not a built in capability but it is possible to use the
extensions and write your own data extension to do this.
The way it is really designed today is for you to create the reports and the
dataset from within Reporting Services. The name matches the design. It is
designed to be a service that you can invoke. You can use the product three
ways. First, it ships with its own portal. This is the quickest way to use
it and is the best way to at least get your feet wet. Then you can integrate
with your own web app by using either URL integration (your app creates the
URL string passing any parameters). You can also integrate with soap (web
services).
Version 2 (Yukon and Widbey) will come with new controls that should allow
you to do this more the way you are envisioning: create a recordset and
passing it to the control. There will be both a winform and a webform
control.
Bruce L-C
"Jasonymk" <Jasonymk@.discussions.microsoft.com> wrote in message
news:D4798A3A-E8D1-4A8E-9DD2-14E8A9725B6E@.microsoft.com...
> BUT
> HOW TO ASSIGN A DATASET TO A DATASOURCE, AND
> HOW TO ASSIGN A QUERY TO A DATASET?
> i dun know if my concept is corroect or not, please let me know if i have
> said something wrong
> thanks in advance.|||thank you so much bruce,
Does it mean that what i can do for the time being is just passing
paramenters to the query built in the rs designer, not passing a query to a
dataset?
"Bruce Loehle-Conger" wrote:
> Two points. First, the way you are envisioning it today can not easily be
> done. People do this but it is not an out of the box experience. By that I
> mean, it is not a built in capability but it is possible to use the
> extensions and write your own data extension to do this.
> The way it is really designed today is for you to create the reports and the
> dataset from within Reporting Services. The name matches the design. It is
> designed to be a service that you can invoke. You can use the product three
> ways. First, it ships with its own portal. This is the quickest way to use
> it and is the best way to at least get your feet wet. Then you can integrate
> with your own web app by using either URL integration (your app creates the
> URL string passing any parameters). You can also integrate with soap (web
> services).
> Version 2 (Yukon and Widbey) will come with new controls that should allow
> you to do this more the way you are envisioning: create a recordset and
> passing it to the control. There will be both a winform and a webform
> control.
> Bruce L-C
> "Jasonymk" <Jasonymk@.discussions.microsoft.com> wrote in message
> news:D4798A3A-E8D1-4A8E-9DD2-14E8A9725B6E@.microsoft.com...
> > BUT
> > HOW TO ASSIGN A DATASET TO A DATASOURCE, AND
> > HOW TO ASSIGN A QUERY TO A DATASET?
> >
> > i dun know if my concept is corroect or not, please let me know if i have
> > said something wrong
> >
> > thanks in advance.
>
>|||by the way, could you please explain more about the method you suggest?
as i really have to make a dynamic query string (not just for the data
value, also the whole query)|||RS has a lot of flexibility in how the query is designed. You can have query
parameters (which map to report parameters). You can refer to global
variable (for instance user!userid), you can base the query on an expression
(expressions are very powerful so there is a whole lot you can do once you
do that).
I think before you jump all the way to where you want to end up you should
play around with it a little bit so you understand the capabilities. Create
a few simple reports, have a query parameter, try out a simple query based
on an expression.
After you understand the capabilities you can decide if it meets your needs.
As I said you can integrate with URL if you want to have your own web site
or you can also use a very full featured web services (soap). Then there are
also extentions possible (data extensions, security extensions).
Bruce L-C
"Jasonymk" <Jasonymk@.discussions.microsoft.com> wrote in message
news:131AC930-6A8F-497B-9E0B-D5CD0B554FF1@.microsoft.com...
> by the way, could you please explain more about the method you suggest?
> as i really have to make a dynamic query string (not just for the data
> value, also the whole query)|||Thanks a lot Bruce,
you said by expression, do you mean by SQL command?
my situation is like this , i use the Render() function to generate reports
with the query "SELECT * FROM table WHERE value=@.parameter" in the report
project, i have tried to pass the parameter to the query by inputting it to
the render function.
thats the most i can do, but the parameters are only limited to a value, not
a clause(esp WHERE clause)...what if i wanna get the data with "WHERE value2
= @.para2 AND value3 =@.para3"'
will URL solve my problem? i have found a method , which is about editing
the rdl (xml)file in vb...but then i will have to edit the whole rdl file.
"Bruce Loehle-Conger" wrote:
> RS has a lot of flexibility in how the query is designed. You can have query
> parameters (which map to report parameters). You can refer to global
> variable (for instance user!userid), you can base the query on an expression
> (expressions are very powerful so there is a whole lot you can do once you
> do that).
> I think before you jump all the way to where you want to end up you should
> play around with it a little bit so you understand the capabilities. Create
> a few simple reports, have a query parameter, try out a simple query based
> on an expression.
> After you understand the capabilities you can decide if it meets your needs.
> As I said you can integrate with URL if you want to have your own web site
> or you can also use a very full featured web services (soap). Then there are
> also extentions possible (data extensions, security extensions).
> Bruce L-C
> "Jasonymk" <Jasonymk@.discussions.microsoft.com> wrote in message
> news:131AC930-6A8F-497B-9E0B-D5CD0B554FF1@.microsoft.com...
> > by the way, could you please explain more about the method you suggest?
> >
> > as i really have to make a dynamic query string (not just for the data
> > value, also the whole query)
>
>|||My suggestion is to back up and first learn the capabilities of Reporting
Services before you start doing integration work. Spend a little time
experimenting. Do some sample reports. Although you can edit RDl and deploy
when you deploy it it will be accesible to all users so you would have to
come up with a unique naming scenario. Plus you have the performance hit of
deploying every time the user requests the report.
Read up on dynamic SQL. Read up and play with expressions and make sure you
understand the power of expressions. Be sure you understand the difference
between query parameter and report parameter. Read up and try a creating a
url to pass a simple parameter to a report. Then get into creating a dynamic
query.
And no, an expression is not a SQL command. It will evaluate to an sql
command but it can have iif statements, nested iif statements etc so you can
create a SQL command with a varying amount of clauses in the where command.
So far I have not heard anything you are trying to do that requires editing
the rdl file directly or writing a data extension. Custom assemblies are
also possible but again, in most cases are not necessary. I write a little
code behind but have found that normally an expression solves my needs.
Bruce L-C
MVP Reporting Services
Again, you are jumping to fast to the end result without first learning
Reporting Services.
"Jasonymk" <Jasonymk@.discussions.microsoft.com> wrote in message
news:ECCC7777-8D8D-42A4-80FB-EB84CA933851@.microsoft.com...
> Thanks a lot Bruce,
> you said by expression, do you mean by SQL command?
> my situation is like this , i use the Render() function to generate
> reports
> with the query "SELECT * FROM table WHERE value=@.parameter" in the report
> project, i have tried to pass the parameter to the query by inputting it
> to
> the render function.
> thats the most i can do, but the parameters are only limited to a value,
> not
> a clause(esp WHERE clause)...what if i wanna get the data with "WHERE
> value2
> = @.para2 AND value3 =@.para3"'
> will URL solve my problem? i have found a method , which is about editing
> the rdl (xml)file in vb...but then i will have to edit the whole rdl file.
>
> "Bruce Loehle-Conger" wrote:
>> RS has a lot of flexibility in how the query is designed. You can have
>> query
>> parameters (which map to report parameters). You can refer to global
>> variable (for instance user!userid), you can base the query on an
>> expression
>> (expressions are very powerful so there is a whole lot you can do once
>> you
>> do that).
>> I think before you jump all the way to where you want to end up you
>> should
>> play around with it a little bit so you understand the capabilities.
>> Create
>> a few simple reports, have a query parameter, try out a simple query
>> based
>> on an expression.
>> After you understand the capabilities you can decide if it meets your
>> needs.
>> As I said you can integrate with URL if you want to have your own web
>> site
>> or you can also use a very full featured web services (soap). Then there
>> are
>> also extentions possible (data extensions, security extensions).
>> Bruce L-C
>> "Jasonymk" <Jasonymk@.discussions.microsoft.com> wrote in message
>> news:131AC930-6A8F-497B-9E0B-D5CD0B554FF1@.microsoft.com...
>> > by the way, could you please explain more about the method you suggest?
>> >
>> > as i really have to make a dynamic query string (not just for the data
>> > value, also the whole query)
>>|||thank you so much bruce,
after a day's study, i start to understand now, and i think nested iif maybe
the direction that i am heading to~~~
thank you sooo much for showing me the way!
"Bruce Loehle-Conger" wrote:
> My suggestion is to back up and first learn the capabilities of Reporting
> Services before you start doing integration work. Spend a little time
> experimenting. Do some sample reports. Although you can edit RDl and deploy
> when you deploy it it will be accesible to all users so you would have to
> come up with a unique naming scenario. Plus you have the performance hit of
> deploying every time the user requests the report.
> Read up on dynamic SQL. Read up and play with expressions and make sure you
> understand the power of expressions. Be sure you understand the difference
> between query parameter and report parameter. Read up and try a creating a
> url to pass a simple parameter to a report. Then get into creating a dynamic
> query.
> And no, an expression is not a SQL command. It will evaluate to an sql
> command but it can have iif statements, nested iif statements etc so you can
> create a SQL command with a varying amount of clauses in the where command.
> So far I have not heard anything you are trying to do that requires editing
> the rdl file directly or writing a data extension. Custom assemblies are
> also possible but again, in most cases are not necessary. I write a little
> code behind but have found that normally an expression solves my needs.
> Bruce L-C
> MVP Reporting Services
> Again, you are jumping to fast to the end result without first learning
> Reporting Services.
> "Jasonymk" <Jasonymk@.discussions.microsoft.com> wrote in message
> news:ECCC7777-8D8D-42A4-80FB-EB84CA933851@.microsoft.com...
> > Thanks a lot Bruce,
> >
> > you said by expression, do you mean by SQL command?
> >
> > my situation is like this , i use the Render() function to generate
> > reports
> > with the query "SELECT * FROM table WHERE value=@.parameter" in the report
> > project, i have tried to pass the parameter to the query by inputting it
> > to
> > the render function.
> >
> > thats the most i can do, but the parameters are only limited to a value,
> > not
> > a clause(esp WHERE clause)...what if i wanna get the data with "WHERE
> > value2
> > = @.para2 AND value3 =@.para3"'
> >
> > will URL solve my problem? i have found a method , which is about editing
> > the rdl (xml)file in vb...but then i will have to edit the whole rdl file.
> >
> >
> >
> > "Bruce Loehle-Conger" wrote:
> >
> >> RS has a lot of flexibility in how the query is designed. You can have
> >> query
> >> parameters (which map to report parameters). You can refer to global
> >> variable (for instance user!userid), you can base the query on an
> >> expression
> >> (expressions are very powerful so there is a whole lot you can do once
> >> you
> >> do that).
> >>
> >> I think before you jump all the way to where you want to end up you
> >> should
> >> play around with it a little bit so you understand the capabilities.
> >> Create
> >> a few simple reports, have a query parameter, try out a simple query
> >> based
> >> on an expression.
> >>
> >> After you understand the capabilities you can decide if it meets your
> >> needs.
> >> As I said you can integrate with URL if you want to have your own web
> >> site
> >> or you can also use a very full featured web services (soap). Then there
> >> are
> >> also extentions possible (data extensions, security extensions).
> >>
> >> Bruce L-C
> >>
> >> "Jasonymk" <Jasonymk@.discussions.microsoft.com> wrote in message
> >> news:131AC930-6A8F-497B-9E0B-D5CD0B554FF1@.microsoft.com...
> >> > by the way, could you please explain more about the method you suggest?
> >> >
> >> > as i really have to make a dynamic query string (not just for the data
> >> > value, also the whole query)
> >>
> >>
> >>
>
>

define query parameters ?

when I'm defining a dataset based on stored procedure I type in the name of
my stored proc and hit the !
then define query parameters comes up. My stored proc has default values
defined for the parameter. Why not have the define query parameters fill in
those default values? It's really anoying to type in 15 parameters almost
everytime I need to re-run the query.HI,
"letuce dance" <letucedance@.discussions.microsoft.com> schrieb im
Newsbeitrag news:C5B2E705-D5B4-47F7-8595-FDC9266B917F@.microsoft.com...
> when I'm defining a dataset based on stored procedure I type in the name
> of
> my stored proc and hit the !
> then define query parameters comes up. My stored proc has default values
> defined for the parameter. Why not have the define query parameters fill
> in
> those default values? It's really anoying to type in 15 parameters almost
> everytime I need to re-run the query.
you can set the default values in the report itself.
witch to layout, click on the upper left corner (report properties), context
menu "report parameters" and there you can set the default settings
"not-queried" and store all your default-parameter values.
hth, Tony

Default Value for Parameter with Query

I am trying to use the User!UserID field as a default value for a parameter. The list for the parameter is populated using a dataset that contains a list of names and User IDs. The value field for the query is User_ID. This field matches exactly with the User!UserID field, but it won't seem to default to that value. I'm sure I'm missing something obvious here. How can I get that list to default to my default value.If you keep User_ID in a field of fixed length (like char[X], not
varchar[X]), you may have spaces appended at the end of values which are
hard to notice. If you have trailing spaces in valid values and no trailing
spaces in the default value, default value won't be selected. See if this is
the case.
--
Dmitry Vasilevsky, SQL Server Reporting Services Developer
This posting is provided "AS IS" with no warranties, and confers no rights.
--
---
"leehaak" <leehaak@.discussions.microsoft.com> wrote in message
news:59867920-83DC-4248-8C90-F0DED42B7E12@.microsoft.com...
> I am trying to use the User!UserID field as a default value for a
parameter. The list for the parameter is populated using a dataset that
contains a list of names and User IDs. The value field for the query is
User_ID. This field matches exactly with the User!UserID field, but it
won't seem to default to that value. I'm sure I'm missing something obvious
here. How can I get that list to default to my default value.|||Thanks, unfortunately, that doesn't appear to be the problem, my User_ID field is a varchar field. I know that I can use User!UserID as a parameter in my query, and it works exactly as intended and has no trailing spaces. The value I'm setting as the default value should match my value field, correct? Are there any other requirements I'm missing to make those synch up?
"Dmitry Vasilevsky [MSFT]" wrote:
> If you keep User_ID in a field of fixed length (like char[X], not
> varchar[X]), you may have spaces appended at the end of values which are
> hard to notice. If you have trailing spaces in valid values and no trailing
> spaces in the default value, default value won't be selected. See if this is
> the case.
> --
> Dmitry Vasilevsky, SQL Server Reporting Services Developer
> This posting is provided "AS IS" with no warranties, and confers no rights.
> --
> ---
> "leehaak" <leehaak@.discussions.microsoft.com> wrote in message
> news:59867920-83DC-4248-8C90-F0DED42B7E12@.microsoft.com...
> > I am trying to use the User!UserID field as a default value for a
> parameter. The list for the parameter is populated using a dataset that
> contains a list of names and User IDs. The value field for the query is
> User_ID. This field matches exactly with the User!UserID field, but it
> won't seem to default to that value. I'm sure I'm missing something obvious
> here. How can I get that list to default to my default value.
>
>|||Here are a few things you can try.
1. Create a report with three text boxes. First text box should show value
of parameter. Second textbox should show value of Users!UserID. Last textbox
should show expression like (Parameters!Param.Value == Users!UserID). Run
this report and select the user you think is current, see if expression
returns true.
2. Make default value a query that would return first value from your table.
See if it is selected in this case.
Please, tell me you findings.
--
Dmitry Vasilevsky, SQL Server Reporting Services Developer
This posting is provided "AS IS" with no warranties, and confers no rights.
--
---
"leehaak" <leehaak@.discussions.microsoft.com> wrote in message
news:69A7ECF1-3BC4-4B5D-B411-81DC6347C44A@.microsoft.com...
> Thanks, unfortunately, that doesn't appear to be the problem, my User_ID
field is a varchar field. I know that I can use User!UserID as a parameter
in my query, and it works exactly as intended and has no trailing spaces.
The value I'm setting as the default value should match my value field,
correct? Are there any other requirements I'm missing to make those synch
up?
> "Dmitry Vasilevsky [MSFT]" wrote:
> > If you keep User_ID in a field of fixed length (like char[X], not
> > varchar[X]), you may have spaces appended at the end of values which are
> > hard to notice. If you have trailing spaces in valid values and no
trailing
> > spaces in the default value, default value won't be selected. See if
this is
> > the case.
> >
> > --
> > Dmitry Vasilevsky, SQL Server Reporting Services Developer
> > This posting is provided "AS IS" with no warranties, and confers no
rights.
> > --
> >
> > ---
> > "leehaak" <leehaak@.discussions.microsoft.com> wrote in message
> > news:59867920-83DC-4248-8C90-F0DED42B7E12@.microsoft.com...
> > > I am trying to use the User!UserID field as a default value for a
> > parameter. The list for the parameter is populated using a dataset that
> > contains a list of names and User IDs. The value field for the query is
> > User_ID. This field matches exactly with the User!UserID field, but it
> > won't seem to default to that value. I'm sure I'm missing something
obvious
> > here. How can I get that list to default to my default value.
> >
> >
> >|||Thanks, I did this as a diagnostic, and discovered that the problem was case.
Apparently, the when determining whether the value matches a value in the
list, case is evaluated. I changed the case of the default value, and it
worked exactly as expected.
"Dmitry Vasilevsky [MSFT]" wrote:
> Here are a few things you can try.
> 1. Create a report with three text boxes. First text box should show value
> of parameter. Second textbox should show value of Users!UserID. Last textbox
> should show expression like (Parameters!Param.Value == Users!UserID). Run
> this report and select the user you think is current, see if expression
> returns true.
> 2. Make default value a query that would return first value from your table.
> See if it is selected in this case.
> Please, tell me you findings.
> --
> Dmitry Vasilevsky, SQL Server Reporting Services Developer
> This posting is provided "AS IS" with no warranties, and confers no rights.
> --
> ---
> "leehaak" <leehaak@.discussions.microsoft.com> wrote in message
> news:69A7ECF1-3BC4-4B5D-B411-81DC6347C44A@.microsoft.com...
> > Thanks, unfortunately, that doesn't appear to be the problem, my User_ID
> field is a varchar field. I know that I can use User!UserID as a parameter
> in my query, and it works exactly as intended and has no trailing spaces.
> The value I'm setting as the default value should match my value field,
> correct? Are there any other requirements I'm missing to make those synch
> up?
> >
> > "Dmitry Vasilevsky [MSFT]" wrote:
> >
> > > If you keep User_ID in a field of fixed length (like char[X], not
> > > varchar[X]), you may have spaces appended at the end of values which are
> > > hard to notice. If you have trailing spaces in valid values and no
> trailing
> > > spaces in the default value, default value won't be selected. See if
> this is
> > > the case.
> > >
> > > --
> > > Dmitry Vasilevsky, SQL Server Reporting Services Developer
> > > This posting is provided "AS IS" with no warranties, and confers no
> rights.
> > > --
> > >
> > > ---
> > > "leehaak" <leehaak@.discussions.microsoft.com> wrote in message
> > > news:59867920-83DC-4248-8C90-F0DED42B7E12@.microsoft.com...
> > > > I am trying to use the User!UserID field as a default value for a
> > > parameter. The list for the parameter is populated using a dataset that
> > > contains a list of names and User IDs. The value field for the query is
> > > User_ID. This field matches exactly with the User!UserID field, but it
> > > won't seem to default to that value. I'm sure I'm missing something
> obvious
> > > here. How can I get that list to default to my default value.
> > >
> > >
> > >
>
>

Default sort order when using SELECT with no ORDER BY clause

For SQL Server 2000 and 7.0:

What is the default sort order of a dataset when you perform a SELECT * FROM [tablename] without using the ORDER BY clause?

I've been told it is ordered by the clustered index (my testing does not bear this out) and I've been told that it is completely unpredictable (closer to what I'm seeing). I believe the latter to be true but cannot find this outlined anywhere in Microsoft's BOL, TechNet, KnowledgeBase, MSDN, etc.

Any pointers on where to find the definitive answer is appreciated.

TIA. RickIf there is clustered index - sorting by clustered index, else sorting by inserting records.

BOL says:

SQL Server 7.0 tables use one of two methods to organize their data pages:

Clustered tables are tables that have a clustered index.
The data rows are stored in order based on the clustered index key. The data pages are linked in a doubly-linked list. The index is implemented as a B-tree index structure that supports fast retrieval of the rows based on their clustered index key values.

Heaps are tables that have no clustered index.
The data rows are not stored in any particular order, and there is no particular order to the sequence of the data pages. The data pages are not linked in a linked list.

SQL Server also supports up to 249 nonclustered indexes on each table. The nonclustered indexes have a B-tree index structure similar to the one in clustered indexes. The difference is that nonclustered indexes have no effect on the order of the data rows. Clustered tables keep their data rows in order based on the clustered index key. The collection of data pages for a heap is not affected if nonclustered indexes are defined for the table. The data pages remain in a heap unless a clustered index is defined.|||Hi ,

If there is no ORDER BY clause in the Query , then the data will be in the same order as inserted.

If you mention ORDER BY Column name , then the dafault sort order will be ASCending if SQL Server is installed by default collation.

Ex. Select * from authors order by authorname

FYI:
--
Use the locale identified by Setup, and then choose the desired binary, case, or other options.

For the release of SQL Server 2000, when Setup detects that the computer is running the U.S. English locale, Setup automatically selects the SQL collation: Dictionary order, case-insensitive, for use with 1252 character set.

To select the equivalent Windows collation, select Collation designator, choose the Latin1_General collation designator, do not select case-sensitive, and select accent-sensitive.

For more information , see BOL - Collation|||"If there is no ORDER BY clause in the Query , then the data will be in the same order as inserted"

if you're lucky

records can get physically stored not in insertion sequence (e.g. a row happens to be too big for the current physical database page, but the next one inserted after that isn't)

i believe records are returned in physical sequence, all things being equal

but what if some of the rows are already in cache, and the remaining rows need to be fetched from disk? it's a safe bet that the ones in cache are pumped out first

i too have heard that the sort order is unpredictable, but i too have been unable to find this on microsoft's site

a "definitive" answer will be found, of course, only on microsoft's site

rudy
http://rudy.ca/|||Hi,

I dont agree with "R937". Sorry to say that.

To his Question" but what if some of the rows are already in cache, and the remaining rows need to be fetched from disk? it's a safe bet that the ones in cache are pumped out first"

This is absolutely wrong. Thats not the concept of caching or Query Optimization.

For Example, if I excute the query as below,

Use pubs
go
/* This will return rows where job_id > 5 */
Select * from jobs where job_id > 5
go

Select * from jobs
go
AS per your statement, this has to return the jobid>5 from cache first and then job_id<4 from disk.

Thats not true.

Concept:
----
As per SQL Server, both the query are entirely different.

First it will just look in cache whether the same SQL Query is available in cache not the data. If the SQL Query is not avaiable , then it will be excuted for optimization and return the rows from disk.

Hope you agree with me.

Any constructive criticism will be appreciated.

FYI: Concepts on Query Optimization will explain well.

Have Fun :)
Varad