distributed queries

Hi all,
Do you know that can we use 'distributed queries' in reporting services to
create rdl report or use linked server as datasource.
please advise if any direction, many thanks.
e.g.
SELECT s.sales_amount
FROM access_server...region AS r,
oracle_server..sales_owner.sale AS s,
sql_server.customer_db.dbo.customer AS c
where r.region_id=s.region_id
and s.customer_id=c.customer_id
and r.region_name='Southwest'
and c.customer_name='ABC Steel'Any legal queries can be used. To do something like this you need to switch
from graphical mode to non-graphical mode. There is a button to the right of
the ... that accomplishes this. Just hover your mouse over the buttons to
find the right one.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"frankie lo" <frankiecblo@.hotmail.com> wrote in message
news:uuPcIYvFIHA.536@.TK2MSFTNGP06.phx.gbl...
> Hi all,
> Do you know that can we use 'distributed queries' in reporting services to
> create rdl report or use linked server as datasource.
> please advise if any direction, many thanks.
> e.g.
> SELECT s.sales_amount
> FROM access_server...region AS r,
> oracle_server..sales_owner.sale AS s,
> sql_server.customer_db.dbo.customer AS c
> where r.region_id=s.region_id
> and s.customer_id=c.customer_id
> and r.region_name='Southwest'
> and c.customer_name='ABC Steel'
>|||Hi Bruce,
I am not sure I undeestand the step(s) to generate the report. do you know
any example or document from microsoft.
Any way, thanks a lot for your help.
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:OyxhCWwFIHA.4808@.TK2MSFTNGP05.phx.gbl...
> Any legal queries can be used. To do something like this you need to
> switch from graphical mode to non-graphical mode. There is a button to the
> right of the ... that accomplishes this. Just hover your mouse over the
> buttons to find the right one.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "frankie lo" <frankiecblo@.hotmail.com> wrote in message
> news:uuPcIYvFIHA.536@.TK2MSFTNGP06.phx.gbl...
>> Hi all,
>> Do you know that can we use 'distributed queries' in reporting services
>> to create rdl report or use linked server as datasource.
>> please advise if any direction, many thanks.
>> e.g.
>> SELECT s.sales_amount
>> FROM access_server...region AS r,
>> oracle_server..sales_owner.sale AS s,
>> sql_server.customer_db.dbo.customer AS c
>> where r.region_id=s.region_id
>> and s.customer_id=c.customer_id
>> and r.region_name='Southwest'
>> and c.customer_name='ABC Steel'
>>
>|||Your problem is that RS graphical editor does not support this sort of
thing.
What you need to do is two steps. First, you cannot use the Report Wizard.
You need to create an empty report. Right mouse click on Reports, Add -> New
Item. Select Report. You get a blank report.
Second, Click on the dataset tab. Add a new dataset (drop down
combobox),Switch to non-graphical mode. Put in your SQL code.
Go to the dataset tab in the report designer, then do as I mentioned.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"frankie lo" <frankiecblo@.hotmail.com> wrote in message
news:O1xpvIyFIHA.4584@.TK2MSFTNGP03.phx.gbl...
> Hi Bruce,
> I am not sure I undeestand the step(s) to generate the report. do you know
> any example or document from microsoft.
> Any way, thanks a lot for your help.
> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
> news:OyxhCWwFIHA.4808@.TK2MSFTNGP05.phx.gbl...
>> Any legal queries can be used. To do something like this you need to
>> switch from graphical mode to non-graphical mode. There is a button to
>> the right of the ... that accomplishes this. Just hover your mouse over
>> the buttons to find the right one.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "frankie lo" <frankiecblo@.hotmail.com> wrote in message
>> news:uuPcIYvFIHA.536@.TK2MSFTNGP06.phx.gbl...
>> Hi all,
>> Do you know that can we use 'distributed queries' in reporting services
>> to create rdl report or use linked server as datasource.
>> please advise if any direction, many thanks.
>> e.g.
>> SELECT s.sales_amount
>> FROM access_server...region AS r,
>> oracle_server..sales_owner.sale AS s,
>> sql_server.customer_db.dbo.customer AS c
>> where r.region_id=s.region_id
>> and s.customer_id=c.customer_id
>> and r.region_name='Southwest'
>> and c.customer_name='ABC Steel'
>>
>>
>|||Ho Bruce,
Thanks for your direction, i have tried it, it real works ;-)
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:eNLHfnyFIHA.4140@.TK2MSFTNGP03.phx.gbl...
> Your problem is that RS graphical editor does not support this sort of
> thing.
> What you need to do is two steps. First, you cannot use the Report Wizard.
> You need to create an empty report. Right mouse click on Reports, Add ->
> New Item. Select Report. You get a blank report.
> Second, Click on the dataset tab. Add a new dataset (drop down
> combobox),Switch to non-graphical mode. Put in your SQL code.
> Go to the dataset tab in the report designer, then do as I mentioned.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "frankie lo" <frankiecblo@.hotmail.com> wrote in message
> news:O1xpvIyFIHA.4584@.TK2MSFTNGP03.phx.gbl...
>> Hi Bruce,
>> I am not sure I undeestand the step(s) to generate the report. do you
>> know any example or document from microsoft.
>> Any way, thanks a lot for your help.
>> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
>> news:OyxhCWwFIHA.4808@.TK2MSFTNGP05.phx.gbl...
>> Any legal queries can be used. To do something like this you need to
>> switch from graphical mode to non-graphical mode. There is a button to
>> the right of the ... that accomplishes this. Just hover your mouse over
>> the buttons to find the right one.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "frankie lo" <frankiecblo@.hotmail.com> wrote in message
>> news:uuPcIYvFIHA.536@.TK2MSFTNGP06.phx.gbl...
>> Hi all,
>> Do you know that can we use 'distributed queries' in reporting services
>> to create rdl report or use linked server as datasource.
>> please advise if any direction, many thanks.
>> e.g.
>> SELECT s.sales_amount
>> FROM access_server...region AS r,
>> oracle_server..sales_owner.sale AS s,
>> sql_server.customer_db.dbo.customer AS c
>> where r.region_id=s.region_id
>> and s.customer_id=c.customer_id
>> and r.region_name='Southwest'
>> and c.customer_name='ABC Steel'
>>
>>
>>
>

distributed partitioned views

Hi everyone,
I have some doubts about distributed partitioned views.
When we create a distributed partitioned view whcih include three server, do we have tocreate this same distributed partitioned view in that three server in order to make each server to see adn especially modify it ?

Thanks

Yes, you need to create the DPV on each server if you want to be able to modify the data on one or more of the servers from each one. Ex:

-- server1

create view dpv

as

select ... from dbo.tbl1

union all

select ... from server2.dbo.tbl2

union all

select ... from server3.dbo.tbl3

-- server2

create view dpv

as

select ... from server1.dbo.tbl1

union all

select ... from dbo.tbl2

union all

select ... from server3.dbo.tbl3

-- server3

create view dpv

as

select ... from server1.dbo.tbl1

union all

select ... from server2.dbo.tbl2

union all

select ... from dbo.tbl3

distributed partitioned view + procedure

If i have a view such as
Create view Viewall
as
select * from server1.db.dbo.abc
union all
select * from server2.db.dbo.abc
union all
select * from server3.db.dbo.abc
union all
select * from server4.db.dbo.abc
And if one server say server 2 is unavailable, will the view fail to run ?
If so , how can i still let the stored proc run
and same for a stored procedure
Create proc Viewall
as
select * from server1.db.dbo.abc
union all
select * from server2.db.dbo.abc
union all
select * from server3.db.dbo.abc
union all
select * from server4.db.dbo.abc
What happens in this case if server2 is unavailable ? And also a way to let
it run should any server be made unavailable> And if one server say server 2 is unavailable, will the view fail to run ?
For a query where the optimizer realizes it has to hit server 2 will fail.
> If so , how can i still let the stored proc run
Have redundancy on the servers.
Same goes for stored procedures.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"Hassan" <fatima_ja@.hotmail.com> wrote in message news:OHe5POhkDHA.1284@.TK2MSFTNGP09.phx.gbl...
> If i have a view such as
> Create view Viewall
> as
> select * from server1.db.dbo.abc
> union all
> select * from server2.db.dbo.abc
> union all
> select * from server3.db.dbo.abc
> union all
> select * from server4.db.dbo.abc
>
> And if one server say server 2 is unavailable, will the view fail to run ?
> If so , how can i still let the stored proc run
> and same for a stored procedure
> Create proc Viewall
> as
> select * from server1.db.dbo.abc
> union all
> select * from server2.db.dbo.abc
> union all
> select * from server3.db.dbo.abc
> union all
> select * from server4.db.dbo.abc
> What happens in this case if server2 is unavailable ? And also a way to let
> it run should any server be made unavailable
>
>

Distinct Value of each column !

Hi,
I've table with following structre
create table #test
(a int,
b varchar(10),
c varchar(10)
)
insert into #Test values ('1','a','x')
insert into #Test values ('2','b','y')
insert into #Test values ('3','c','y')
insert into #Test values ('3','b','1')
insert into #Test values ('4','a',null)
insert into #Test values ('1',null,null)
now i want distinct value of
each column like
A B C
--
1 a x
2 b y
3 c 1
4 null null
How do i get this type of resultset ?
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200509/1alter table #test add row_id int identity(1,1)
go
select * from
(
select *,(select count(*) from #test t
where t.row_id<=#test.row_id and t.a=#test.a)as num
from #test
) as d where num=1
"Malkesh S via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:53B523BC4BB04@.SQLMonster.com...
> Hi,
> I've table with following structre
> create table #test
> (a int,
> b varchar(10),
> c varchar(10)
> )
> insert into #Test values ('1','a','x')
> insert into #Test values ('2','b','y')
> insert into #Test values ('3','c','y')
> insert into #Test values ('3','b','1')
> insert into #Test values ('4','a',null)
> insert into #Test values ('1',null,null)
> now i want distinct value of
> each column like
> A B C
> --
> 1 a x
> 2 b y
> 3 c 1
> 4 null null
> How do i get this type of resultset ?
>
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200509/1

DISTINCT First Name and Last Name for Mail Merge

I'm trying to create a mail merge on my MS SQL database. Many of my owners are entered more than once as they might own multiple properties. I'm trying to write a query where each is only pulled one time and I'm having some trouble. I think I need to use SELECT DISTINCT, but I need to make sure I don't filter out people with the same first name and different last names and vice versa. Thanks!One of my old bosses insisted this could be done using soundex

don't know, but maybe that is an area to start?

http://www.4guysfromrolla.com/webtech/tips/t103101-1.shtml

good luck|||It doesn't need to be that deep.

I thought of another idea. How do I use SELECT DISTINCT to return all rows in my table and only unique address fields?|||I think that a group statement might accomplish this??

as in

select f_name from table group by f_name

I re-read your post...

address validation is a trick...

the address field can be set up many ways, and the street
type designation can also be setup in a whole slew
of different ways...

what does your address field look like (sample data)

thanks
tony|||Soundex is a practically useless algorithm.

You can select distinct First/Last names in a subquery, but then which address would you use if the two records do not agree?

Here is one of the simplest methods:select YourTable.*
from YourTable
inner join --DistinctNames
(select Max(PrimaryKey) as PrimaryKey
from YourTable
group by FirstName,
LastName) DistinctNames
on YourTable.PrimaryKey = DistinctNames.PrimaryKey|||You da' Blindman! Thanks, that's exactly what I needed.|||I hate to post an extension to the question in the same place, but here goes. My owners are listed more than once if they own more than once parcel of land. What I need to do next is continue the merge to each only once, but add up their land acreage amounts and total for each person. Thoughts anyone? Thanks in advance.sql

Distinct count problem

Hi,

I'm trying to create a measure (distinct count) for counting the number of stores which sell product. I used to fact table to count store_key but I've just realized this measure returns a count including stores which do not sell the product.

Daily Sales fact : store_key, net sales dollars .....

How should I define the calc in this case? I'm thinking conditional statements but not clear yet.

Please give me some comments. Thanks.

We do something similar - a count of invoices that have a coupon applied to them. There are two ways (that I know of) to do this:

1) at the fact level. Have a measure in your fact table that contains a 1 or 0 for each store that sells the product. Not a very efficient way, but it will get the job done. Do a sum on this measure. Depending on how you want to count your products, you could have a measure group that is store_key, product_key, sells_product (int). This will work, but definitely not efficient.

2) The way we do it is to make the item you are measuring an attribute of the dimension itself. In our case we have an invoice dimension which contains a "has_coupon" attribute. As part of the ETL we determine if the value of has_coupon shoud be "Yes" or "No". You then can query according to the values of this attribute. We then can do [Invoice].[Has Coupon].Children.Count, or any other derivation of it.

I would recommend option #2, but it all depends on your situation. If anyone else knows of other ways to model, I'm curious too.

Good luck,

John Hennesey

|||Not totally clear, but is it counting a NULL value when you don't want it to?

Just create another fact table in the DSV based off of the fact table where the key is not null:

select *
from fact
where
key is not null

And create the DISTINCT COUNT measure based of this new new table.
|||

thanks.

it's easy and it works fine

sql

Distinct Count Custom Rollup

I have a vehicleID in my fact table. I want to create a measure called VehicleCount. This would be a distinct count of vehicles per day, but would be a sum of the per day counts for months, years, etc. If I just use distinct count on the VehicleID field will that give me the per day count and how do I make it so that the rollup is a sum instead of doing a distinct count for the higher levels (month, year, etc?)

I'm using SSAS2005.

Thanks for any help.

One approach would be to create a "distinct count" measure like [DistinctVehicles] on the VehicleID field, then sum its daily values in [VehicleCount] measure:

Create [VehicleCount] as a "count" measure on the fact table, and [DistinctVehicles] as a "distinct count" measure on the VehicleID field. Add an assignment to the cube MDX script like: ([Measures].[VehicleCount], [Date].[Date].[Date]) = [Measures].[DistinctVehicles];|||

Thanks for the response.

I'm a slightly confused though. You said, "Create [VehicleCount] as a "count" measure on the fact table." Shouldn't this be a "Sum" measure? Otherwise wouldn't you just end up with the count of days?

Thanks for your help.

|||Should work either way (I did test with a "count" measure), because the [Day] level is overwrtten by the "distinct count" assignment; and those daily values should get summed up at higher [Date] levels ...sql

distibuted transations in linked server

please solve this problem.

when i am executing any transaction from one server to update record in other linked server database.

create PROCEDURE [dbo].[aTest]

@.iNum as int

AS

BEGIN

SET NOCOUNT ON;

begin tran

update aaa set sName='Deepak'

where iNum=@.iNum

exec [REPORTSRVR\SQL2005].HDFCCUSTOMER.dbo.aProc @.iNum

if @.@.error <> 0

begin

rollback tran

return

end

else

begin

commit tran

end

SET NOCOUNT OFF;

END

it shows following error

The operation could not be performed because OLE DB provider "SQLNCLI" for linked server "REPORTSRVR\SQL2005" was unable to begin a distributed transaction.

1. Check/change your server option "remote proc trans" via sp_configure or Connections tab in SQL Server Instance Properties window

2. If you need MS DTC for distributed transactions: http://blogs.msdn.com/mab/archive/2005/12/30/508273.aspx

|||

thanks for response.

i m using transaction statement. is it possible that single transaction can talk from two server?

Disregard: SSIS as data source for reporting?

I'm trying to develop a report using an SSIS DataReaderDestination as the reports data souce. When I attempt to create the report DataSet, SSIS is not an option for the Data Source parameter....

Is there an OLE DB or .NET provider for SSIS that I need to install apart from installing reporting services?Had the wrong edition. Never let your architects download things for you :)|||

Hello,
I am experiencing the same problem you described. I have Visual Studio Team Suite installed with SQL Server 2005 Developer Edition. I would expect this setup should allow me to use any of the functionality available in Integration Services. What edition did you have to install to get the SSIS option to appear?

Thanks for your help.

Disregard: SSIS as data source for reporting?

I'm trying to develop a report using an SSIS DataReaderDestination as the reports data souce. When I attempt to create the report DataSet, SSIS is not an option for the Data Source parameter....

Is there an OLE DB or .NET provider for SSIS that I need to install apart from installing reporting services?Had the wrong edition. Never let your architects download things for you :)|||

Hello,
I am experiencing the same problem you described. I have Visual Studio Team Suite installed with SQL Server 2005 Developer Edition. I would expect this setup should allow me to use any of the functionality available in Integration Services. What edition did you have to install to get the SSIS option to appear?

Thanks for your help.

Displaying Series Group Total for a RS 2005 Chart

Hi there,

I'm trying to create a chart in RS 2005 that will split one set of
data into two separate lines while at the same time, creating a line
that will also display the average of all records. Some background
may be useful:

TABLE

Student_IdAttended_Meeting_CountTest_ScoreTest_ID11090125851315011109522580231652

What I would like to do is create 1 line that shows the average test score for all students with the test id as the x-axis value, 1 line that shows the average test score for all students who attended at least 5 meetings, and 1 line that does the same for all students who attended less than 5 meetings. In the Values box, I am using "=cint(AVG(Fields!Test_Score.Value))", and I have set a series group with the group on expression as "=Fields!Attended_Meetings_Count.Value > 5". This works great for splitting the line representing all values into the two lines I want, however, the test_score for all students line goes away. Anyone have any ideas on how to generate this line again? I've tried adding it as another value, but the series group then applies itself to the new values line as well and I end up with 4 lines.

Help!
-Rich

Hi,

From your description, it seems that you want to show three average values in three different conditions, right?

Why don't you calculate these three values separately? See the following sample:

=CINT(AVG(IIF(Fields!Attended_Meetings_Count.Value > 5, Fields!Test_Score.Value,0)))

=CINT(AVG(IIF(Fields!Attended_Meetings_Count.Value < 5, Fields!Test_Score.Value,0)))

=CINT(AVG(Fields!Test_Score.Value,0))

Thanks.

|||

Hi there,

Thanks for the reply. I did try the values you had suggested above, however, I get the following error when trying to view the preview:

The Value expression for the chart 'chart1' has a scope parameter that is not valid for an aggregate function. The scope parameter must be set to a string constant that is equal to either the name of a containing group, the name of a containing data region, or the name of a data set.

Any other suggestions?

Thanks again!

Rich

Displaying image from db in crystal report

hello there

I am using VB.Net 2003 with oracle 9i. My table contains a BLOB field that contains images. I tried to create a report (crystal report that is packed with VB.Net 2003) by including the table and dragging the BLOB field onto the report. But when i try to run the report, it gives me "Failed to open a rowset" error. If i run the report without the BLOB field, the report works fine.

Any idea?!?!

Thanks in advance
AndyHow did you store the images in the table?
Sounds that some images are corrupted
Visist this site and see if you find solution
www.businessobjects.com

Displaying data - question

Dont laugh;

How do I create a simple sqlcommand in C# that shows data. I have the code for VB but I a missing something in the converstion. I know SQL but I dont get the simple steps of displaying data. I have got all of the Visual Basic stuff down I just need help with doing it by hand in C#.

or point my to a URL so that I can get the code.

Thanks

VB.Net and C# connect to databases the same way so if you can hook VB.Net up to a database then you should be able to with C#. It must be a syntax error. Here's a link to some code for setting up a simple connection with C# below.

http://www.csharpfriends.com/Articles/getArticle.aspx?articleID=1

Display subtotals and grand total

Hi,

I have a table:

CREATE TABLE [dbo].[TBL_REPORT1](

[Source] [varchar](3) NULL,

[Contract No] [varchar](15) NOT NULL,

[Business Group] [varchar](4) NULL,

[Customer Name] [varchar](50) NULL,

[Equipment Description] [varchar](20) NULL,

[Lease Type] [varchar](2) NULL,

[Term] [int] NULL,

[Booking Date] [datetime] NULL,

[# of Assets] [int] NULL,

[Equipment Cost] [money] NULL,

[Restructured] [varchar](3) NOT NULL

)

Sample Data

INSERT INTO [TBL_REPORT1] VALUES('SFS','319-0010146-001','SEF','NorthBay Healthcare Group','SBT Performance Cont','LP',132,'Apr 4 2007 12:00:00:000AM',1,2612000.0000,'No')
INSERT INTO [TBL_REPORT1] VALUES('SFS','729-0015625-023','SEF','Black Diamond Properties, Inc.','Kubota L48 TLB Tract','OL',60,'Apr 3 2007 12:00:00:000AM',1,36000.0000,'No')
INSERT INTO [TBL_REPORT1] VALUES('SFS','729-0015648-007','SEF','The River Wilderness Club, Inc.','Honda Salsco Greens','OL',48,'Apr 5 2007 12:00:00:000AM',1,11401.0000,'No')
INSERT INTO [TBL_REPORT1] VALUES('SFS','749-0013599-020','VEN','THYSSENKRUPP BUDD COMPANY','COMPUTER GEAR','CS',30,'Apr 5 2007 12:00:00:000AM',1,232965.0300,'No')
INSERT INTO [TBL_REPORT1] VALUES('SFS','749-0016965-002','VEN','GREEN OAK TOWNSHIP','COMPUTER GEAR','CS',33,'Apr 5 2007 12:00:00:000AM',1,56789.9100,'No')
INSERT INTO [TBL_REPORT1] VALUES('SFS','749-0052401-001','VEN','Zircon Corp.','INJECTION MOLDING MC','CS',70,'Apr 11 2007 12:00:00:000AM',1,74380.0300,'No')
INSERT INTO [TBL_REPORT1] VALUES('SFS','766-0001804-007','IGP','Helena Chemical Company','1800 GAL','TL',36,'Apr 18 2007 12:00:00:000AM',17,292147.7000,'No')
INSERT INTO [TBL_REPORT1] VALUES('SFS','769-0002040-001','CBF','Ball Packaging Corp.','Second Filler/Seamer','CS',1,'Apr 13 2007 12:00:00:000AM',1,276928.4500,'No')
INSERT INTO [TBL_REPORT1] VALUES('SFS','769-0002040-002','CBF','Ball Packaging Corp.','Second Filler/Seamer','CS',1,'Apr 13 2007 12:00:00:000AM',1,377415.3500,'No')
INSERT INTO [TBL_REPORT1] VALUES('SFS','769-0002053-001','CBF','VIH Helicopters USA, Inc.','Sikorsky S-61N','CS',84,'Apr 6 2007 12:00:00:000AM',1,4612500.0000,'No')
INSERT INTO [TBL_REPORT1] VALUES('SFS','778-0014680-024','CPM','SUN MICROSYSTEMS, INC.','trade receivable','CS',2,'Apr 5 2007 12:00:00:000AM',1,20177632.2300,'No')
INSERT INTO [TBL_REPORT1] VALUES('SFS','778-0015956-014','CPM','Autozone Inc.','trade receivable','CS',11,'Apr 3 2007 12:00:00:000AM',1,2128173.3600,'No')

I want to display subtotals - sum of [Equipment Cost] for each [Business Group] and also the grand total.

Sample Output:

SFS 769-0002040-001 CBF Ball Packaging Corp. Second Filler/Seamer CS 1 00:00.0 1 276928.5 No SFS 769-0002040-002 CBF Ball Packaging Corp. Second Filler/Seamer CS 1 00:00.0 1 377415.4 No SFS 769-0002053-001 CBF VIH Helicopters USA, Inc. Sikorsky S-61N CS 84 00:00.0 1 4612500 No CBF Count 3 SFS 778-0015956-014 CPM Autozone Inc. trade receivable CS 11 00:00.0 1 2128173 No SFS 778-0014680-024 CPM SUN MICROSYSTEMS, INC. trade receivable CS 2 00:00.0 1 20177632 No CPM Count 2

I tried with CUBE and ROLLUP but then with multiple fields it was not giving me the right output. Can anyone help. Thanks.

Posted above was the sample count subtotal generated in Excel. I am interested in getting only the subtotal count/sum and the grand total. Labels like CBF Count, CPM Count and not required.

SFS 769-0002040-001 CBF Ball Packaging Corp. Second Filler/Seamer CS 1 00:00.0 1 276928.5 No SFS 769-0002040-002 CBF Ball Packaging Corp. Second Filler/Seamer CS 1 00:00.0 1 377415.4 No SFS 769-0002053-001 CBF VIH Helicopters USA, Inc. Sikorsky S-61N CS 84 00:00.0 1 4612500 No CBF Total 5266844 SFS 778-0015956-014 CPM Autozone Inc. trade receivable CS 11 00:00.0 1 2128173 No SFS 778-0014680-024 CPM SUN MICROSYSTEMS, INC. trade receivable CS 2 00:00.0 1 20177632 No CPM Total 22305806 SFS 766-0001804-007 IGP Helena Chemical Company 1800 GAL TL 36 00:00.0 17 292147.7 No IGP Total 292147.7 Grand Total 27864797

|||

If using SQL Server 2005, have you looked at COMPUTE?

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

Dan

|||Thanks.|||I hope it does what you need!

display string having length greater than 255

I have a created a table and entered data into the table as follows:

CREATE TABLE t ( id INT , txtcol varchar(1000) )

INSERT INTO t ( id , txtcol ) VALUES ( 1 , 'ATXR_SOURCE_ID,CDDL_AG_PRICE,CDDL_ALLOW,CDDL_ALTDP_EXCD_ID,CDDL_CAP_IND,CDDL_CHG_AMT,CDDL_COINS_AMT,CDDL_CONSIDER_CHG,CDDL_COPAY_AMT,CDDL_DED_AC_NO,CDDL_DED_AMT,CDDL_DIS_PA_LIAB,CDDL_DISALL_AMT,CDDL_DISALL_EXCD,CDDL_DISC_AMT,CDDL_DP_PRICE,CDDL_FROM_DT,CDDL_PAID_AMT,CDDL_PF_PRICE,CDDL_PR_PYMT_AMT,CDDL_PRICE_IND,CDDL_REF_IND,CDDL_RISK_WH_AMT,CDDL_SB_PYMT_AMT,CDDL_SURF,CDDL_TOOTH_BEG,CDDL_TOOTH_END,CDDL_TOOTH_NO,CDDL_TOT_PA_LIAB,CDDL_UNITS,CDDL_UNITS_ALLOW,CGCG_ID,CGCG_RULE,DPCG_DP_ID_ALT,DPDP_ID,DPTC_CD,PDVC_LOBD_PTR,PSDC_ID,UTUT_CD' )

Now if i select data using the query below the txtcol field displays only 255 characters :

SELECT * FROM t

Y is this happening?

This is happening because of your client application (QA, SSMS or something else) has a setting not to display field values grater than 255 bytes. Inspect application options, then.

Display SQL 2005 KPI status graphic using visual studio 2005

Dear All,

Could anyone help send me a sample mdx code on how i could get the KPI status graphics from MS SQL 2005. I create a cube and add a few KPI's into the cube, on the management studio 2005 I am able to view the graphics e.g. smiley but when i use the mdx command I could only display the KPI status -1, 0 or 1 not the graphics.

The MDX functions does not include the KPI_status_graphics.

Thank you in advance.

Mike Siow siowm@.metierview.com

You can retrieve the KPI_STATUS_GRAPHIC and KPI_TREND_GRAPHIC for a KPI using the MDSCHEMA_KPIS Rowset:

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

>>

MDSCHEMA_KPIS Rowset

Describes the key performance indicators (KPIs) within a database.

...

>>

For example, for the Adventure Works Internet Revenue KPI:

<Discover xmlns="urn:schemas-microsoft-com:xml-analysis">

<RequestType>MDSCHEMA_KPIS</RequestType>

<Restrictions>

<RestrictionList>

<CATALOG_NAME>Adventure Works DW</CATALOG_NAME>

<CUBE_NAME>Adventure Works</CUBE_NAME>

<KPI_NAME>Internet Revenue</KPI_NAME>

</RestrictionList>

</Restrictions>

<Properties>

<PropertyList>

<Catalog>Adventure Works DW</Catalog>

<Format>Tabular</Format>

</PropertyList>

</Properties>

</Discover>

--

<return xmlns="urn:schemas-microsoft-com:xml-analysis">

<root xmlns="urn:schemas-microsoft-com:xml-analysis:rowset" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">

<xsd:schema targetNamespace="urn:schemas-microsoft-com:xml-analysis:rowset" xmlns:sql="urn:schemas-microsoft-com:xml-sql" elementFormDefault="qualified">

<xsd:element name="root">

<xsd:complexType>

<xsd:sequence minOccurs="0" maxOccurs="unbounded">

<xsd:element name="row" type="row" />

</xsd:sequence>

</xsd:complexType>

</xsd:element>

<xsd:simpleType name="uuid">

<xsd:restriction base="xsd:string">

<xsd:pattern value="[0-9a-zA-Z]{8}-[0-9a-zA-Z]{4}-[0-9a-zA-Z]{4}-[0-9a-zA-Z]{4}-[0-9a-zA-Z]{12}" />

</xsd:restriction>

</xsd:simpleType>

<xsd:complexType name="xmlDocument">

<xsd:sequence>

<xsd:any />

</xsd:sequence>

</xsd:complexType>

<xsd:complexType name="row">

<xsd:sequence>

<xsd:element sql:field="CATALOG_NAME" name="CATALOG_NAME" type="xsd:string" minOccurs="0" />

<xsd:element sql:field="SCHEMA_NAME" name="SCHEMA_NAME" type="xsd:string" minOccurs="0" />

<xsd:element sql:field="CUBE_NAME" name="CUBE_NAME" type="xsd:string" minOccurs="0" />

<xsd:element sql:field="MEASUREGROUP_NAME" name="MEASUREGROUP_NAME" type="xsd:string" minOccurs="0" />

<xsd:element sql:field="KPI_NAME" name="KPI_NAME" type="xsd:string" minOccurs="0" />

<xsd:element sql:field="KPI_CAPTION" name="KPI_CAPTION" type="xsd:string" minOccurs="0" />

<xsd:element sql:field="KPI_DESCRIPTION" name="KPI_DESCRIPTION" type="xsd:string" minOccurs="0" />

<xsd:element sql:field="KPI_DISPLAY_FOLDER" name="KPI_DISPLAY_FOLDER" type="xsd:string" minOccurs="0" />

<xsd:element sql:field="KPI_VALUE" name="KPI_VALUE" type="xsd:string" minOccurs="0" />

<xsd:element sql:field="KPI_GOAL" name="KPI_GOAL" type="xsd:string" minOccurs="0" />

<xsd:element sql:field="KPI_STATUS" name="KPI_STATUS" type="xsd:string" minOccurs="0" />

<xsd:element sql:field="KPI_TREND" name="KPI_TREND" type="xsd:string" minOccurs="0" />

<xsd:element sql:field="KPI_STATUS_GRAPHIC" name="KPI_STATUS_GRAPHIC" type="xsd:string" minOccurs="0" />

<xsd:element sql:field="KPI_TREND_GRAPHIC" name="KPI_TREND_GRAPHIC" type="xsd:string" minOccurs="0" />

<xsd:element sql:field="KPI_WEIGHT" name="KPI_WEIGHT" type="xsd:string" minOccurs="0" />

<xsd:element sql:field="KPI_CURRENT_TIME_MEMBER" name="KPI_CURRENT_TIME_MEMBER" type="xsd:string" minOccurs="0" />

<xsd:element sql:field="KPI_PARENT_KPI_NAME" name="KPI_PARENT_KPI_NAME" type="xsd:string" minOccurs="0" />

<xsd:element sql:field="ANNOTATIONS" name="ANNOTATIONS" type="xsd:string" minOccurs="0" />

</xsd:sequence>

</xsd:complexType>

</xsd:schema>

<row>

<CATALOG_NAME>Adventure Works DW</CATALOG_NAME>

<CUBE_NAME>Adventure Works</CUBE_NAME>

<MEASUREGROUP_NAME>Internet Sales</MEASUREGROUP_NAME>

<KPI_NAME>Internet Revenue</KPI_NAME>

<KPI_CAPTION>Internet Revenue</KPI_CAPTION>

<KPI_DESCRIPTION>Revenue realized through direct sales via the internet.</KPI_DESCRIPTION>

<KPI_DISPLAY_FOLDER>Financial Perspective\Grow Revenue</KPI_DISPLAY_FOLDER>

<KPI_VALUE>[Measures].[Internet Sales Amount]</KPI_VALUE>

<KPI_GOAL>[Measures].[Internet Revenue Goal]</KPI_GOAL>

<KPI_STATUS>[Measures].[Internet Revenue Status]</KPI_STATUS>

<KPI_TREND>[Measures].[Internet Revenue Trend]</KPI_TREND>

<KPI_STATUS_GRAPHIC>Cylinder</KPI_STATUS_GRAPHIC>

<KPI_TREND_GRAPHIC>Standard Arrow</KPI_TREND_GRAPHIC>

<KPI_WEIGHT />

<KPI_PARENT_KPI_NAME />

<ANNOTATIONS />

</row>

</root>

</return>

|||

Hi Deepak,

Thank you.

Mike Siow

Display list of databases in App_Data folder

Hi All,

The web site I'm working on will allow admin users to create a new database for each new client. I'm hoping to be able to keep all of the databases in the local App_Data folder, but I'm finding that I can't even find a way of generating a list of the databases in the App_Data folder.

The code I have is:

Dim serverConnAs New ServerConnection(".\SQLEXPRESS")serverConn.LoginSecure =TrueserverConn.ConnectTimeout = 30serverConn.Connect()Dim sqlServerAs Server =New Server(serverConn)For Each dbAs DatabaseIn sqlServer.Databases ListView1.Items.Add(db.Name)Next

This does populate a list of databases, but not the ones in the App_Data folder - just those attached directly to the server.

Any help on this will be greatly appreciated as it's starting to drive me nuts!

Thanks,

Paul

Hi Paul,

What you are getting is correct. You will get a list of databases running on the server.

If you want to create databases on a particular directory you have to override the default directory

C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data or something similar depending on where is your default data directory.

You have to specify that in your create database script

USE MASTER

CREATE DATABASE DataBaseName
ON
(
NAME = LogicalName_Data,
FILENAME = 'c:\YourPath\App_Data\DataBaseName_Data.mdf',
SIZE = 4,
MAXSIZE = 10,
FILEGROWTH = 1
)
LOG ON
(
NAME = 'LogicalName_Log',
FILENAME = 'c:\YourPath\App_Data\DataBaseName_Log.ldf',
SIZE = 5MB,
MAXSIZE = 25MB,
FILEGROWTH = 5MB
)

Use

Server server = new Server(new ServerConnection(cnnDb));
server.ConnectionContext.ExecuteNonQuery(sqlString);

to create the new database.

If want to find all databases that are present in the directory.

You have to do a naming convention trick of naming the database starting with

GeneratedDb_ and filter the names based on it or

You can use DirectoryInfo class and read the App_Data folder and get all files having extension .mdf

Or loop through the filegroups in Database and check for App_Data folder

Happy programming,
Anton

|||

Hello,

this may be helpful to u

http://www.codeproject.com/aspnet/dbadmin.asp

http://www.codeproject.com/aspnet/MyDbViewerSite.asp

|||

Hi guys,

Just wanted to say a big thank you for your replies. It looks like this is tougher than I expected as I don't think I can get away with updating the master database to force the creation of the databases (ultimately, the web app won't be loaded on my server), and I had loved the idea of using '|DataDirectory|' so that I didn't need to explicitly give the server name for the connection strings already created. However, I think I've read enough now to believe that I am better off setting things to work directly with the server rather than locally - at least I seem to be able to create new databases and copy data as I need to.

Thanks again,

Paul

Display date

Hi all

column "datetime" in table i need to create view and display only date

any formate do this

thanks&regards

Code Snippet

SELECT CONVERT(varchar(10), datecolumn, 101)

See CONVERT in BOL for more options than 101.

|||Look up CAST and CONVERT in books online.

Display data in Reports based on Active Directory Group Membership...

Hello,

I am fairly new to SQL 2005 and Reporting Services.

We are trying to create a report that will display sales data based on group membership from Active Directory.

For example, if USER1 logs in and looks at a Year to Date Sales report, it will only show data that pertains to his group. If USER2 logs in and accesses the same report, it will display different Year to Date information because he is in a different group.

Background Information: We are running SQL 2005 Enterprise Edition Service Pack 2 with Analysis and Reporting Services. We are delivering the reports through a Sharepoint site.

Please let me know if anyone has a good way to tackle this.

Thanks,

Justin

Well, I'm not sure that this is the answer that you want to hear, but I would say that you would need to incorporate a "group" attribute in your SQL tables. How you implement that would be up to you, but I will tell you that bad database design can be quite chaotic.

If you are asking whether there is a magical function in SQL that will know how to interpret Active Directory membership without any extra design or programming, I would say definately no.

|||

What is the source of the data you want to limit by user account? SQL or Analysis Services.

If it's Analysis Services, you should be able to sort out the limitations by using Cube Roles connected to AD user groups.

See this article in SS2005 Books Online http://msdn2.microsoft.com/en-us/library/ms175408.aspx Granting User Access.

If it's from SQL Server 2005, it's not that simple. You can query Active Directory from SQL Server, so you can use some account data. Unfortunately, it's a bit of a hassle to create the right LDAP query. And you need to add some kind of group info to the database in any case, as you need to create a table containing the what data which group has access to, like GregSQL writes.

This article is on how to connect to AD from SQL Server

http://codebetter.com/blogs/brendan.tompkins/archive/2003/12/19/4746.aspx

Kaisa Lervik

|||

I am working on this project with Justin - rather than limit access by the name of the group, we would like to pass the group name to the Analysis Services query in SSRS to determine which data is delivered to the logged in user.

Thanks

Bob