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)
> >>
> >>
> >>
>
>

Defining Analysis Services Named Calculation

Hi,

I am creating a new Named Caluclation for time dimension in dsv and added in the time dimension cube from the data source view in the time dimension structre.

While processing it is giving the error message that

"Memory error: The operation cannot be completed because the memory quota estimate exceeds the available system memory"

Can you please help me.

Thanks

Dinesh

What does the named calculation look like? And does it process if you take it out?

Cheers

Matt

|||

Show us the named calculation statment...

Works ok without the Named Calculation?!

Regards

|||

Hi

I have used the named calculation "ReportDate + ' ' + Session" in time dimension.

can you let me know how to add this namedcalculation to the cube after creating the named calcualtion in dsv.

Thanks

Dinesh

|||

Hi,

ReportDate and Session are both strings? If not you probably get problems, you will need to cast them as a string/varchar in order for your named calculation to work.

Adding to a dimension, just edit your dimension and drag it onto the attributes list. If it is a new measure, edit your cube and right click, add new measure, select the new named calculation.

Hope that helps

Matt

|||

You already created the NC in the DSV? Correct?

The cube processing generate errors only after your created the NC, correct?

Regards

|||

Hi,

ReportDate and Session are both strings. I added that into atribute list and processed.

It is giving the error message like

"Memory error: The operation cannot be completed because the memory quota estimate (1976MB) exceeds the available system memory (659MB). "

So, what to do for this type of error

Thanks

Dinesh

|||

Hi,

Looks like this is a known bug with SSAS, I haven't seen it before, but it looks like there is a hotfix for it.

http://support.microsoft.com/kb/914595

Hope that helps

Matt

|||

Hi,

Yes after creating the NC only this problem came. before i tested the cube there is no problem.

can you please let me know what is the problem.

Thanks

Dinesh

|||

Check the Matt link and fix the bug, if you will still need some help, tell us!

regards!

Define MySQL data source in a SQL Server Analysis Service Project

I want to use MySQL database as a data source fo an Analysis Service Project.

But there is no Provider that support MySQL Databases.

If anybody have any experiance about this, Please help me.

Thanks

Hi,

I know there are some providers out there, but I don't have any experience using them, but I do know they exist. Here is one that I found doing a quick search.

http://sourceforge.net/projects/myoledb/

David

|||

Loading data from MySQL database is not officially supported by Analysis Services.

Edward Melomed.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Just because its not supported, doesn't mean it can't be done.

They only problem you may face in trying this provider is that it may not support all of the properties that Analysis Services requires to be implemented. But its worth a shot.

David

DefaultCodePage issue from Db2 source

Hi everyone,

We're stuck with this. We've got a Db2 source connection and when we try 'Preview' button appears the following error:

TITLE: Microsoft Visual Studio

The component reported the following warnings:

Warning at {420E9420-175B-4C2F-856A-EA5D51C23627} [OLE DB Source [1]]: Cannot retrieve the column code page info from the OLE DB provider. If the component supports the "DefaultCodePage" property, the code page from that property will be used. Change the value of the property if the current string code page values are incorrect. If the component does not support the property, the code page from the component's locale ID will be used.

Choose OK if you want to continue with the operation.
Choose Cancel if you want to stop the operation.

Does anyone have ever used DB2 driver from a dtsx package? If so, let me know if you had any related problem with.

Another day it happens with Oracle.

Thanks in advance for your time,

The DB2 OLE DB provider does expose code page information, so it is warning you of this fact. It also suggests a workaround/fix. The OLE-DB Source has a AlwaysUseDefaultCodePage property, it is exposed in the property grid, not the custom UI. Set this to true. You could also just ignore the error, it will work, even in the preview window.

The same applies to Oracle, again they have not implemented code page support in the provider, same solution.

|||

DarrenSQLIS wrote:

The DB2 OLE DB provider does expose code page information, so it is warning you of this fact. It also suggests a workaround/fix. The OLE-DB Source has a AlwaysUseDefaultCodePage property, it is exposed in the property grid, not the custom UI. Set this to true. You could also just ignore the error, it will work, even in the preview window.

The same applies to Oracle, again they have not implemented code page support in the provider, same solution.

Follow Darren's advice. Set the "AlwaysUseDefaultCodePage" to true and your warning will go away.|||

hi guys, not tested yet. That's for a colleague of mine.

thanks

defaultcodepage is set to false

when ever i drop a ole db source or destination control on data flow, upon clicking to edit it complains about -- defaultcodepage is set to false. When i check in the properties and set it to true; i get no error. What is it all about?

kushpaw

codepage has to do with whether your columns in the table have data that are in other languages.... seting it to true will ask SSIS to take the defualt translation that it thinks fits your data.