Custom Select Statment

Hello,

Why when I make a custom select statment with multiple tables, that when I test my query I'm shown the same rolls multiple times. When I make a custom query with just one table everything works just fine. I don't have any primary keys or restraints or whatever.

SELECT * FROM table1, table2, table3 Does not work(shows each row multiple times)

SELECT * FROM table1 works just fine (shows each row just one time)

Thanks

Steve

By not specifying a Join, you end up with a cross join which will return a tremendous number of duplicates.
see:http://en.wikipedia.org/wiki/Join_%28SQL%29

You need to select which columns to use to join your tables together.

For example:

SELECT table1.*, table2.*, table3.*FROM table1INNERJOIN table2ON table1.SomeKey = table2.SomeKeyINNERJOIN table3ON table1.SomeKey = table3.SomeKey

Custom Report Item: Textbox

Hi all,
If it is possible, how I can extend the Textbox item with a DataSet
property. In order to display a field from multiple rows, with a separator ?
Example:
a dataset with 10 rows (and 1 field "Name")
and I want to display in the Textbox
"Name1, Name2, Name3, Name4, Name5, Name6, Name7, Name8, Name9, Name10"
ThanksI'm sure this is possible, just to give you a point in one possible
direction...
From a Database side of things, you could pivot the data in your SQL
statement. SQL Server 2005 has new SQL commands called PIVOT and UNPIVOT...
Once your rows values are pivotted to columns in a single row, then you
could specify them easily enough in the text box.
Hope that helps.
Dan.
"gbouzebra" <gbouzebra@.discussions.microsoft.com> wrote in message
news:D8959BBA-3778-4129-9E3F-AB4443A58C5F@.microsoft.com...
> Hi all,
> If it is possible, how I can extend the Textbox item with a DataSet
> property. In order to display a field from multiple rows, with a separator
> ?
> Example:
> a dataset with 10 rows (and 1 field "Name")
> and I want to display in the Textbox
> "Name1, Name2, Name3, Name4, Name5, Name6, Name7, Name8, Name9, Name10"
> Thanks

Custom function with multiple variables

Hello,
I must say I am not too experienced in writing custom functions in
TSQL. Perhaps I am asking impossible
I have run into problems that the client platform is not able to
transmit the unicode characters properly, so I need a solution that
woul allow to call insert/update queries with ASCII charset only. The
current solution is that every unicode string literal is replaced with
a long sum of NCHAR(n) and string literals containing only ASCII chars.
That made me run into some wierd 'query optimisation' error.
I am now thinking maybe I could create a function where I could pass
all the unicode code values and string literals and what would
concatenate all them into one string. So, the question is -
1) Is it possible to create a function with variable number of
arguments
2) Can these arguments be of different types - mixed integer and string
3) Is TSQL strong enough to provide means of creating program that
loops over all the arguments and concatenates all of them into one
string, converting the int arguments uzing NCHAR() function.
Example:
MyFn(256,1000,'ABC',300,400,'1234')
returns ''ABC?' (The funny unicode chars replaced with ? for the
sake of example)
Thank you for any hints,
Pavils> 1) Is it possible to create a function with variable number of arguments ?
No. The number of arguments is defined when you create the function.
Even if you specify default values for some arguments, when you call
the UDF you must specify the DEFAULT keyword in the place of the values
for those arguments.

> 2) Can these arguments be of different types - mixed integer and string ?
No, but in SQL Server 2000 (or later) you can use the sql_variant
datatype, which can represent any other scalar datatype (except text,
ntext, image and timestamp).

> 3) Is TSQL strong enough to [...] ?
Maybe, but I don't think that would be a good idea.
You should think about another way of dealing with this issue.
Razvan

Custom extensions and multiple tables

Is it possible to create a custom extension (DLL) that will push multiple
tables (or datasetes) into SQL report? If not what is a way to push
multiple datasets into report?
Thanksyou can use 1 data set in each data region and have multiple data regions in
a single report..
But if you are talking about having multiple tables in a single data region,
you might try using the SQL UNION statement ie
select name, address, zip from employees
union
select name, address, zip from customers
If the tables are dis-similar you might think of using a join in your
query..
hope this helps.
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Mark Goldin" <markgoldin@.comcast.net> wrote in message
news:OOmhR2lwEHA.2012@.TK2MSFTNGP15.phx.gbl...
> Is it possible to create a custom extension (DLL) that will push multiple
> tables (or datasetes) into SQL report? If not what is a way to push
> multiple datasets into report?
> Thanks
>|||I guess my question is how to push (not to pull) into RS multiple datasets
using custom extentions.
"Wayne Snyder" wrote:
> you can use 1 data set in each data region and have multiple data regions in
> a single report..
> But if you are talking about having multiple tables in a single data region,
> you might try using the SQL UNION statement ie
> select name, address, zip from employees
> union
> select name, address, zip from customers
> If the tables are dis-similar you might think of using a join in your
> query..
> hope this helps.
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Mark Goldin" <markgoldin@.comcast.net> wrote in message
> news:OOmhR2lwEHA.2012@.TK2MSFTNGP15.phx.gbl...
> > Is it possible to create a custom extension (DLL) that will push multiple
> > tables (or datasetes) into SQL report? If not what is a way to push
> > multiple datasets into report?
> >
> > Thanks
> >
> >
>
>

Custom Data Processing Extensions/Custom Data Providers

Hello!

I'm trying to understand the limitations that SSRS places on a developing reports involving multiple data sources and how I can possibly create a custom data provider for a report that will consolidate data from multiple data sources..

thanks in advance.

Doug.

If you want to see what is all involved in creating a Custom Data Processing extension, I used the FSI example that comes with the SQL Server install. It gives you a good idea of what needs to be involved.

I extended and added to it a great deal but the basis for what you need to do is in there. It's non-trivial, but very do-able.

|||

I'll look into that!

THanks!!!!

doug

|||

Instead of implementing a data processing extension, you should look at the T-SQL OPENROWSET command

http://msdn2.microsoft.com/en-us/library/ms190312.aspx

Or linking servers

http://msdn2.microsoft.com/en-us/library/ms188279.aspx

These give you the ability to consolidate multiple data sources and it is a lot easier than implementing your own data processing extension to do the same. The only caveat is that it requires SQL Server.

|||

We're using Oracle...so that kind of bites...

well, we've hit a snag in our development of our extension..

I cannot figure out why I get this exception when we try to generate a DataSet in our SSRS IDE:

A connection cannot be made to the database,
Set and test the connection string.
Additional Information:
The data extension FOCUS could not be loaded. Check the the configuration files RSReportDesigner.config(Microsoft.ReportingServices.Designer)

We added the following to the RSReportDesigner.config file:

<Extension Name="FOCUS" Type="PPD.Focus.FocusDPE.FocusDbConn, PPD.Focus.FocusDPE"/>

And the dll's complete with the proper namespaces are all in the proper directories:

C:\Program Files\Microsoft SQL Server\MSSQL.4\Reporting Services\ReportServer\bin\PPD.Focus.FocusDPE.dll
C:\Program Files\Microsoft Visual Studio 8\Common7\IDE\PrivateAssemblies\PPD.Focus.FocusDPE.dll

We've tried to step into the extension code using a techique outlined in MSDN without any luck. We've also attempted to write out errors the Event Viewer, but it doesn't even seem to get into the connection object,

Any Idea of where I'm going wrong?

thanks!

Doug

Custom Data Processing Extensions/Custom Data Providers

Hello!

I'm trying to understand the limitations that SSRS places on a developing reports involving multiple data sources and how I can possibly create a custom data provider for a report that will consolidate data from multiple data sources..

thanks in advance.

Doug.

If you want to see what is all involved in creating a Custom Data Processing extension, I used the FSI example that comes with the SQL Server install. It gives you a good idea of what needs to be involved.

I extended and added to it a great deal but the basis for what you need to do is in there. It's non-trivial, but very do-able.

|||

I'll look into that!

THanks!!!!

doug

|||

Instead of implementing a data processing extension, you should look at the T-SQL OPENROWSET command

http://msdn2.microsoft.com/en-us/library/ms190312.aspx

Or linking servers

http://msdn2.microsoft.com/en-us/library/ms188279.aspx

These give you the ability to consolidate multiple data sources and it is a lot easier than implementing your own data processing extension to do the same. The only caveat is that it requires SQL Server.

|||

We're using Oracle...so that kind of bites...

well, we've hit a snag in our development of our extension..

I cannot figure out why I get this exception when we try to generate a DataSet in our SSRS IDE:

A connection cannot be made to the database,
Set and test the connection string.
Additional Information:
The data extension FOCUS could not be loaded. Check the the configuration files RSReportDesigner.config(Microsoft.ReportingServices.Designer)

We added the following to the RSReportDesigner.config file:

<Extension Name="FOCUS" Type="PPD.Focus.FocusDPE.FocusDbConn, PPD.Focus.FocusDPE"/>

And the dll's complete with the proper namespaces are all in the proper directories:

C:\Program Files\Microsoft SQL Server\MSSQL.4\Reporting Services\ReportServer\bin\PPD.Focus.FocusDPE.dll
C:\Program Files\Microsoft Visual Studio 8\Common7\IDE\PrivateAssemblies\PPD.Focus.FocusDPE.dll

We've tried to step into the extension code using a techique outlined in MSDN without any luck. We've also attempted to write out errors the Event Viewer, but it doesn't even seem to get into the connection object,

Any Idea of where I'm going wrong?

thanks!

Doug

Custom Data Flow

To all the SSIS gurus,

I have a situation where I have multiple source tables, I need to populate the destination tables that have the same schema as the source ones. I dont want to do the repetitive task of creating a Dataflow for each source-destination load.

I want to create one custom dataflow component and loop through all my source tables and provide destination tables dynamically.

Is there a way to do that? Any custom data flow component out there?

Your ideas are greatly appreciated.

Regards,

ssiscoder wrote:

To all the SSIS gurus,

I have a situation where I have multiple source tables, I need to populate the destination tables that have the same schema as the source ones. I dont want to do the repetitive task of creating a Dataflow for each source-destination load.

I want to create one custom dataflow component and loop through all my source tables and provide destination tables dynamically.

Is there a way to do that? Any custom data flow component out there?

Your ideas are greatly appreciated.

Regards,

No you can't do this currently. Well, not with the out-of0the-box components anyway.

What you CAN do is build a package programatically in dotnet using the SSIS API. That dotnet code can be called from any place that can execute dotnet code. One of those places happens to be a SSIS package.

HTH.

-Jamie

|||

Thanks for the info Jamie!

Can you please post some articles here of how to go about doing the data flow part programitically?

Regards,

|||

ssiscoder wrote:

Thanks for the info Jamie!

Can you please post some articles here of how to go about doing the data flow part programitically?

Regards,

I can do better than that. here's a sample:

http://msdn2.microsoft.com/en-us/library/ms161541.aspx

-Jamie

|||

This might help as well: http://msdn2.microsoft.com/en-us/library/ms345167.aspx

-Jamie

Custom Data Extensions - Multiple Datasets.

Hi,
I know your probably going to tell me that this is not possible, but I am a
whisker away from getting it to work.. So there must be a way.
I have managed to extend reporting services with my own Data Processing
Extension (DPE).
With the combination of a dynamic web service proxy generator and the RDL
params provided by the Report Designer wizard i.e., Connectionstring & Query
I am able to call a web service dynamically and return a serialized dataset
from a web method.
This is then used by reporting services report designer wizard to provide a
list of fields in the dataset which are then used to layout the report.
Connectionstring = WebService URL e.g. http://local/mywebservice.asmx
Query = WebMethod e.g. GetDataset
It works great with a single dataset.
Now,
All this is done at run-time, and I know at design time it is possible to
add multiple datasets which gives you the nifty drop down list of datasets in
the fields window.
However, I have a dataset which contains multiple datasets and I wish to do
the same thing that reporting services does at design-time but at run-time.
Structure:
========== DataSet 1
DataSet A
DataSet B
DataSet C
Just for further information, I am extending the IDataReader Interface and
passing back the dataset through the Systems.Collections.IEnumerator
Interface.we are doing the same thing in my company please contact me at
andrescampos99@.hotmail.com with the source code and we will glad to assist you
"eskimo" wrote:
> Hi,
> I know your probably going to tell me that this is not possible, but I am a
> whisker away from getting it to work.. So there must be a way.
> I have managed to extend reporting services with my own Data Processing
> Extension (DPE).
> With the combination of a dynamic web service proxy generator and the RDL
> params provided by the Report Designer wizard i.e., Connectionstring & Query
> I am able to call a web service dynamically and return a serialized dataset
> from a web method.
> This is then used by reporting services report designer wizard to provide a
> list of fields in the dataset which are then used to layout the report.
> Connectionstring = WebService URL e.g. http://local/mywebservice.asmx
> Query = WebMethod e.g. GetDataset
> It works great with a single dataset.
> Now,
> All this is done at run-time, and I know at design time it is possible to
> add multiple datasets which gives you the nifty drop down list of datasets in
> the fields window.
> However, I have a dataset which contains multiple datasets and I wish to do
> the same thing that reporting services does at design-time but at run-time.
> Structure:
> ==========> DataSet 1
> DataSet A
> DataSet B
> DataSet C
> Just for further information, I am extending the IDataReader Interface and
> passing back the dataset through the Systems.Collections.IEnumerator
> Interface.

Custom Code....

Hey guys I have a problem I am trying to solve using custom code but I am open to other solutions.

Situation: I have multiple reports that the user can see. Example (Move In Corp. report, Move In Region report,Move In Division report, Mid Year Corp. report, Mid Year Region report,Mid Year Division report etc.) I have a stored procedure that returns all the information needed based upon the UserID being passed from the report to the stored procedure.

Goal: When the user logs in I want to be able to foward the user to a specific report based upon their userid (global in RS). I currently have three textboxes(Move In, Mid Year, Year End) with navigation enabled to jump to another report. The report to which the user is passed to is determined by an expression which is something like this for the Move In text box =IIF(Fields!orgtypeid.Value = 1,"Move In Corp","Move In Region"). The orgtypeid.value is returned by the stored procedure. I want to be able to forward the user a specific Move In report (Move In Corp, Move In Region, etc) without having the user select the textbox.

Any suggestions are greatly appreciated.

TTT|||

You can try using subreports.

Make a topmost "switch" report that hosts three subreports and make Hidden value on each subreport an expression that returns true depending on the user type. This will not eliminate execution of all three reports but it will show only one that's needed.
The second step would be to make a switch report such that is executes only the needed report. To do this you will need to wrap each subreport in a separate dataregion (List for example) and make sure that datasets for each data region are built in such a way that only one dataset returns a row, while other two return empty sets.
I have not tried the second step myself, but it might be worth exploring

|||

Thanks for the response the first option is out simply because of performance issues.

This will be a dashboard used by a rather large company. I will look into the second option, in the mean time if anyone else has any other ideas I would welcome them now.

Thanks Alexandre!

Custom authorization with multiple authorities

The LogonUser function takes a third parameter used to identify an
authority for the user name (I assume this is the same intention as the
realm in HTTP authentication). I assumed the idea was to allow
Reporting Services to enable log-ins from users in multiple user
databases. For example, if we are using RS to serve reports on each of
our customers' virtual web sites, then each customer has its own user
database.
The problem is, the IAuthorizationExtension interface does not use
authority strings. I assume the idea is that user names must be unique
amongst all the authorities, perhaps by adding the authority name as a
prefix (e.g., the user "bill" in the user database of customer "Acme"
might have a user name acme.bill for the purposes of authorization. I
assume further that the way this is is achieved is that, after LogonUser
is called as LogonUser("bill", password, "Acme"), then GetUserInfo
returns a userIdentity of "acme.bill" rather than plain "bill". Does
this sound plausible?I wrote:
> I
> assume further that the way this is is achieved is that, after LogonUser
> is called as LogonUser("bill", password, "Acme"), then GetUserInfo
> returns a userIdentity of "acme.bill" rather than plain "bill". Does
> this sound plausible?
After some experimentation, it seems to me that GetUserInfo is *not*
consulted when generating the ticket cookie. I guess this means I need
to feed the qualified user names in to LogonUser, and always use the
same authority string.