Place Unsorted row at end of detail

Hi There

I have a row returned from my stored procedure called 'Total', I would like it to appear at the bottom of the detail section rows.

I have placed the below expression on the sort on the table, trouble is now that 'Total' is not being sorted it appears at the top. Is there a way to get it to the bottom?

=iif(Fields!Item.Value <> "Total", true ,false)

Thanks in advance

Dave

several possible options

reverse the true and false part of your expression|||

Thanks for the reply,

-I cant reverse the true and false, as i need the rest of the rows sorted alphabetically

-need it sorted ascending

-Use an inetger, not sure whta you mean here?

Thanks

Dave

|||

Then put 2 things in your sort of the table:

firstly sort by: =iif(Fields!Item.Value <> "Total", 1 ,2)
then sort by: =Fields!Item.Value

So all your detail rows will get a 1 in the first sort expression and be sorted by name in the second expression and your total filed will get a 2 in the first expression and hence come after your detail rows.

|||

Legend,

Thanks fella that worked a treat!

Place results in Colmn rather than rows

I have a few tables that i need to run a query on and instead of having them appear in multiple rows how do i return teh results in columns instead.

eg: System Name

1 Mr A

1 Mr B

2 Mr C

2 Mr D

INTO System Name1 Name2

1 Mr A Mr B

2 Mr C Mr D

SELECT CASE WHEN THEN ELSE END

Adamus

|||

SELECT CASE Name

WHEN System_ID = '1',

THEN

Name2

ELSE

Name3

END

Not sure i get you?

|||declare @.table table
(
[System] int,
[Name] varchar(5)
)

insert into @.table
select 1, 'Mr A' union all
select 1, 'Mr B' union all
select 2, 'Mr C' union all
select 2, 'Mr D'

select a.[System], [Name 1] = min(a.[Name]), [Name 2]= max(a.[Name])
from @.table a
group by a.[System]
|||

Thanks

The names Mr A, Mr B etc will be in the hundreds so don't really fancy typing them all out. There could be up to 4 or 5 different names per system.

i have tried to adapt to this but doesn;t work:-

declare @.table table

(

[System] int,

[Name] varchar(5)

)

insert into @.table

select System_ID, (firstname + ' ' + surname) as Name union all

select System_ID, (firstname + ' ' + surname) as [Name 1] union all

select System_ID, (firstname + ' ' + surname) as [Name 2]

where system = 1

From ((((dbo.System as S..........followed by my joins....

Select a.[System], [Name 1] = min(a.[Name]), [Name 2]= max(a.[Name])

from @.table a

group by a.[System]

How do i do this when i need to search for the criterea?

|||the table variable is for demonstrating the script.

use the query and change to your actual table name.

select a.[System], [Name 1] = min(a.[Name]), [Name 2]= max(a.[Name])
from @.table a
group by a.[System]|||

ok . got it working partially,

The Min and Max just returns 2 results? Some have 3 or 4 names?

|||do this in your front end application. It can be done in T-SQL but it will not be clean

Pivot Table with SSAS 2005

hi

i facing problem when i tring to browse cube created in SSAS 2005 from pivot table in FP appear this error

The query could not be processed:

o An error was encountered in the transport layer.

o The peer prematurely closed the connection.

any one have solutions for this problem

thanks

Ensure that you have the Microsoft OLE DB Provider for Analysis Services 9.0 installed, as well as Microsoft Core XML Services 6.0. They can be downloaded here

http://www.microsoft.com/downloads/details.aspx?FamilyID=df0ba5aa-b4bd-4705-aa0a-b477ba72a9cb&DisplayLang=en

Once they are both installed, run regedit and look under HKEY_CLASSES_ROOT/MSOLAP - there should be a key called CLSID which should have the same value as the CLSID under HKEY_CLASSES_ROOT/MSOLAP.3

Now try to connect to the SSAS cube again...some people have found they need to specify their username as <Domain>\<Username> to connect correctly