Opendatasource

I'm trying to use OPENDATASOURCE in t-sql to update data on our AS/400 from MSSQL but don't know the property value for provider_name. Please help!!!"Control Panel"\"Administrative Tools"\"Data Sources(ODBC)"
Select a tab "File DSN", click "Add..", select server and click "Advanced".
Now you see sub-driver for ODBC (MSDASQL)

For MS SQL Server in MS SQL Server
select *
from OpenRowset('MSDASQL', 'Driver={SQL Server};Server=MyServer;User=MyUser','select * from sysobjects')

I do not have A/400 driver installed, that is why I cannot test this for A/400.

Good luck !|||my diagnosis is the your transactional properties of your neural defilibrator is going to require that you just replace the AS400 with SQL Server.

you better get to work.sql

Open report in new window problem

I am using
="javascript:void(window.open('/Reportserver?%2fwebte%2fExpStmt&rs:Command=Render&stmtnumber="&Fields!Exp_Stmt_Number.Value &"','_blank'))"
to open a new report from a link. This works fine from the report manager.
However, I am using this inside the reportviewer control in an vb.net .aspx
page. I get no new window, no report, and no error messages.
Any ideas?
NeilHi Neil,
I tried the below code.
It is working for the environment (Microsoft SQL Server Reporting Services
Version 8.00.899.00)
It is working both for the Report Manager and ReportViewer component.
But regarding to one of my earlier experiences, it is not working when you
deploy the report to excel
Eralper
http://www.kodyaz.com
"Neil Gould" wrote:
> I am using
> ="javascript:void(window.open('/Reportserver?%2fwebte%2fExpStmt&rs:Command=Render&stmtnumber="&Fields!Exp_Stmt_Number.Value &"','_blank'))"
> to open a new report from a link. This works fine from the report manager.
> However, I am using this inside the reportviewer control in an vb.net .aspx
> page. I get no new window, no report, and no error messages.
> Any ideas?
> Neil

Only select value once

Hello,

How do I go about selecting some things once and others more than once?

This is what I have returning right now:

Pet Name Food

Dog Buster Kibbles and Bits

Dog Buster IAMS

Dog Buddy Kibbles and Bits

How can I return this?

Pet Name Food

Dog Buster Kibbles and Bits

Dog IAMS

Dog Buddy Kibbles and Bits

I just want to the dog's name to appear once. If it appears more than once have all subsequent appearences be NULL.

How would I do this?

You can do this in a query but why are you generating a report using SQL? It is not meant for that. The query will look complex depending on the version of SQL Server and perform slower than a simple SELECT statement. You will get better performance if you use the client to do the reporting functionality. Any standard reporting tool will have options to suppress repeating values like you want. So what are you doing with this data? Is there an application that is issueing the query?|||

I don't have a report returning...just data.

I just want to select the dog's name once if it is the same dog....

It is filling a datagrid.

|||

The following query will work on SQL Server 2005,

But no guarantee on performance...

Code Snippet

Create Table #data (

[Pet] Varchar(100) ,

[Name] Varchar(100) ,

[Food] Varchar(100)

);

Insert Into #data Values('Dog','Buster','KibblesandBits');

Insert Into #data Values('Dog','Buster','IAMS');

Insert Into #data Values('Dog','Buddy','KibblesandBits');

;With Ordered

as

(

Select *, Row_number() Over(order By Pet) RowId from #data

)

, Grouped

as

(

Select * , Row_Number() Over(Partition By Pet,Name Order By RowID) as GroupId from Ordered

)

Select Pet,Case When GroupId=1 Then Name Else '' End,Food from Grouped Order By RowId

only each first item from a select with group ....

i want to get only the first (max sum ) value of each grouped item after a
select
example of the returned records where: two columns (grouped value company
and sum sell price)
in the table there are a nr of entrys of each company
i calculate the sum for the sell price in each company
i want to display only the best of a region
now i get somethink like
nord company33 145678,44
nord company2 34578,44
nord company44 478,44
nord company6 78,44
west company77 645678,44
west company8 35678,44
sud company9 995678,44
sud company4 678,44
but i want to display only the first (max) entry of each group - like
nord company33 145678,44 (the best from region nord)
west company77 645678,44
sud company9 995678,44
thanks
XavierTry,
To make it readable, let us create a view.
create view v1
as
here goes the select statement that does the grouping
go
select *
from v1 as a
where not exists (
select *
from v1 as b
where b.region = a.region
and b.sum_sell_price > a.sum_sell_price
)
-- or
select a.*
from v1 as a
inner join
(
select region, max(sum_sell_price ) as max_sum_sell_price
from v1
group by region
) as b
on a.region = b.region
and a.sum_sell_price = b.max_sum_sell_price
AMB
"Xavier" wrote:

> i want to get only the first (max sum ) value of each grouped item after
a
> select
> example of the returned records where: two columns (grouped value company
> and sum sell price)
> in the table there are a nr of entrys of each company
> i calculate the sum for the sell price in each company
> i want to display only the best of a region
> now i get somethink like
> nord company33 145678,44
> nord company2 34578,44
> nord company44 478,44
> nord company6 78,44
> west company77 645678,44
> west company8 35678,44
> sud company9 995678,44
> sud company4 678,44
> but i want to display only the first (max) entry of each group - like
> nord company33 145678,44 (the best from region nord)
> west company77 645678,44
> sud company9 995678,44
> thanks
> Xavier
>|||it works, thanks for your help
Xavier
"Alejandro Mesa" wrote:
> Try,
> To make it readable, let us create a view.
> create view v1
> as
> here goes the select statement that does the grouping
> go
> select *
> from v1 as a
> where not exists (
> select *
> from v1 as b
> where b.region = a.region
> and b.sum_sell_price > a.sum_sell_price
> )
> -- or
> select a.*
> from v1 as a
> inner join
> (
> select region, max(sum_sell_price ) as max_sum_sell_price
> from v1
> group by region
> ) as b
> on a.region = b.region
> and a.sum_sell_price = b.max_sum_sell_price
>
> AMB
> "Xavier" wrote:
>

Only displaying the subtotal value in a matrix report.

Is there a way to make it so that a data column in a matrix only displays a value for the subtotal of a field?

If you add a toggle to the matrix column/row group, it will enable to switch between the detailed group instances (when expanded) or show the subtotals only (when collapsed). Take a look at the "Company Sales" sample report in the Adventure Works sample project that comes with RS.

-- Robert