How to format numerics with the sign on the right?

I've got an amount field in a report where I want the values to have the sign
(if negative) on the right. But I also want the numbers to line up properly.
I can't seem to find a way to format this. In Excel, you'd use a format like
#,##0.00_);#,##0.00-. But that does not quite do it in Reporting Services. I
see you can use custom formats and the syntax is similar to Excel but not
quite the same. I tried:
#,##0.00 ;#,##0.00-
but RS seems to ignore the whitespace. I also tried:
#,##0.00' ';#,##0.00-
also to no avail.
Any suggestions?What about using double quotes: " " ?
"virtualfergy" wrote:
> I've got an amount field in a report where I want the values to have the sign
> (if negative) on the right. But I also want the numbers to line up properly.
> I can't seem to find a way to format this. In Excel, you'd use a format like
> #,##0.00_);#,##0.00-. But that does not quite do it in Reporting Services. I
> see you can use custom formats and the syntax is similar to Excel but not
> quite the same. I tried:
> #,##0.00 ;#,##0.00-
> but RS seems to ignore the whitespace. I also tried:
> #,##0.00' ';#,##0.00-
> also to no avail.
> Any suggestions?|||Tried it. No luck I'm afraid. Works the same as single quotes. Any other
advice out there?
"Albert" wrote:
> What about using double quotes: " " ?
> "virtualfergy" wrote:
> > I've got an amount field in a report where I want the values to have the sign
> > (if negative) on the right. But I also want the numbers to line up properly.
> > I can't seem to find a way to format this. In Excel, you'd use a format like
> > #,##0.00_);#,##0.00-. But that does not quite do it in Reporting Services. I
> > see you can use custom formats and the syntax is similar to Excel but not
> > quite the same. I tried:
> >
> > #,##0.00 ;#,##0.00-
> >
> > but RS seems to ignore the whitespace. I also tried:
> >
> > #,##0.00' ';#,##0.00-
> >
> > also to no avail.
> >
> > Any suggestions?

How to format KPI values in KPI definition?

Hi, experts,

How can we format KPI values in the KPI value expression? (e.g. format KPI_Name with format of 2 decimal places?).

Hope it is clear for your help. I am looking forward to hearing from you shortly.

Thanks a lot in advance.

With kindest regards,

Yours sincerely,

Hi Helen! I do not think you can format KPI. You can use a calculated member as the value expression /source for the KPI and format it instead.

HTH

Thomas Ivarsson

|||

Hi, Thomas,

Thanks for your help. I also found a strange problem that when I build the report with KPI values displayed in SSRS 2005, the values are with many decimal places instead of the formats I have already set up in the Analysis services cubes? (e.g, I formated the KPI values by using a calculated member in calculations with 2 decimal places, but the KPI values displayed on SSRS2005 are with many decimal places? e.g. 2.455879..?)

I have no idea what is going on. I am looking forward to hearing from you.

With kindest regards,

Yours sincerely,

|||

Hi Helen! I have seen the same behaviour in SSRS2005. You are aware of that Reporting Services do not show SSAS2005 KPI status and trends graphically? You will only see numbers.

SSRS2005 do not seem to use the formats from relational sources nore SSAS2005. I think you will have to apply formats once again in SSRS2005 like "### ####,##"

HTH

Thomas Ivarsson

|||

Hi, Thomas,

Thanks very much for your patient advices and help.

Yes, I am aware of this problem that SSRS2005 is not able to show KPI status and trend graphically.

With kindest regards,

Yours sincerely,

how to format in to dd/mm/yyyy ?

hi all,

i have table field name call

Start_date varchar(16)

when i select data from that filed values it gives me

Eg:

select Start_date from Customer

20011224 00:00:0
20011004 00:00:0

but i want to convert this data in to dd/mm/yyyy format ?

like ! 24/12/2001

04/10/2001

how do i do this task ?

regards

sujithf

create table #format (

start_Date_time varchar(16)

)

insert into #format values('20011224 00:00:0')

select convert(varchar(16), cast(start_date_time as datetime), 103) from #format

--103 is a British/French date format "dd/mm/yyyy"

|||

thanks very much.....

regards

sujithf

How to force 0's in place of null values..

Hi,
I am using an MDX query which generates null values,Instead of null values I
need to display 0 's.
I have tried the below options but none is working fine for me,
please give me any help/suggestion/URL.Its really urgent !!
=Iif( Fields!colname.Value = NULL,0,Fields!colname.Value)
=iif(IsDbNull(Fields!colname.Value) = True,0,Fields!colname.Value)
=Iif( Fields!colname.Value = Nothing,0,Fields!colname.Value)
Thanks and Regards,
Rajesh Yennam.
HA India.This what I use and it works fine.
Good Luck!
=iif(Sum(Fields!average.Value) is Nothing,0,Sum(Fields!average.Value))
"Rajesh Yennam" <RajeshYennam@.discussions.microsoft.com> wrote in
message news:RajeshYennam@.discussions.microsoft.com:
> Hi,
> I am using an MDX query which generates null values,Instead of null values I
> need to display 0 's.
> I have tried the below options but none is working fine for me,
> please give me any help/suggestion/URL.Its really urgent !!
> =Iif( Fields!colname.Value = NULL,0,Fields!colname.Value)
> =iif(IsDbNull(Fields!colname.Value) = True,0,Fields!colname.Value)
> =Iif( Fields!colname.Value = Nothing,0,Fields!colname.Value)
> Thanks and Regards,
> Rajesh Yennam.
> HA India.|||I asked the same thing in the Olap-news group
(microsoft.public.sqlserver.olap). Here are the answers I got:
***
tawargerip@.hotmail.com <tawargerip@.hotmail.com>:
use IIF( isempty(<Measure name>),0,<Measure name>)
you may need to create a calculated member for each measure like this
and
Cymryr <Cymryr@.hotmail.com>:
Function CoalesceEmpty is the right way to do it, see BOL
***
The CoalesceEmpty looks promising, but I haven't got around to implement it
yet.
Kaisa M. Lindahl
"Rajesh Yennam" <RajeshYennam@.discussions.microsoft.com> wrote in message
news:BE2BF79D-CDBC-488D-9B31-6F9132507717@.microsoft.com...
> Hi,
> I am using an MDX query which generates null values,Instead of null values
I
> need to display 0 's.
> I have tried the below options but none is working fine for me,
> please give me any help/suggestion/URL.Its really urgent !!
> =Iif( Fields!colname.Value = NULL,0,Fields!colname.Value)
> =iif(IsDbNull(Fields!colname.Value) = True,0,Fields!colname.Value)
> =Iif( Fields!colname.Value = Nothing,0,Fields!colname.Value)
> Thanks and Regards,
> Rajesh Yennam.
> HA India.|||Thanks for your quick response John.Actually this also not working for me. If
you find any alternative solution please post it.
Thanks & Regards,
Rajesh Yennam,
HA India.
"John Geddes" wrote:
> This what I use and it works fine.
> Good Luck!
> =iif(Sum(Fields!average.Value) is Nothing,0,Sum(Fields!average.Value))
>
>
> "Rajesh Yennam" <RajeshYennam@.discussions.microsoft.com> wrote in
> message news:RajeshYennam@.discussions.microsoft.com:
> > Hi,
> > I am using an MDX query which generates null values,Instead of null values I
> > need to display 0 's.
> > I have tried the below options but none is working fine for me,
> > please give me any help/suggestion/URL.Its really urgent !!
> >
> > =Iif( Fields!colname.Value = NULL,0,Fields!colname.Value)
> > =iif(IsDbNull(Fields!colname.Value) = True,0,Fields!colname.Value)
> > =Iif( Fields!colname.Value = Nothing,0,Fields!colname.Value)
> >
> > Thanks and Regards,
> > Rajesh Yennam.
> > HA India.
>
>|||Put the null detection code in a function in the Code element of the report.
The problem with IIF is that it evaluates all conditions and throws on NULL.
As simple if then else statement will work.
-Lukasz
This posting is provided "AS IS" with no warranties, and confers no rights.
"Rajesh Yennam" <RajeshYennam@.discussions.microsoft.com> wrote in message
news:CBD98285-010A-4EB9-8DFE-2DBF0875C898@.microsoft.com...
> Thanks for your quick response John.Actually this also not working for me.
> If
> you find any alternative solution please post it.
> Thanks & Regards,
> Rajesh Yennam,
> HA India.
> "John Geddes" wrote:
>> This what I use and it works fine.
>> Good Luck!
>> =iif(Sum(Fields!average.Value) is Nothing,0,Sum(Fields!average.Value))
>>
>>
>> "Rajesh Yennam" <RajeshYennam@.discussions.microsoft.com> wrote in
>> message news:RajeshYennam@.discussions.microsoft.com:
>> > Hi,
>> > I am using an MDX query which generates null values,Instead of null
>> > values I
>> > need to display 0 's.
>> > I have tried the below options but none is working fine for me,
>> > please give me any help/suggestion/URL.Its really urgent !!
>> >
>> > =Iif( Fields!colname.Value = NULL,0,Fields!colname.Value)
>> > =iif(IsDbNull(Fields!colname.Value) = True,0,Fields!colname.Value)
>> > =Iif( Fields!colname.Value = Nothing,0,Fields!colname.Value)
>> >
>> > Thanks and Regards,
>> > Rajesh Yennam.
>> > HA India.
>>

how to find which line has error

I am running a script which inserts large number of rows thru
INSERT INTO VALUES statement.
One of them is giving some error. While running it in Query Analyzer I am not able to know
which line is giving the problem. How do I make QA show me the offending line.
The script has lot of GO statements, usuall one after every 200 lines.
TIAData Cruncher wrote:
> I am running a script which inserts large number of rows thru
> INSERT INTO VALUES statement.
> One of them is giving some error. While running it in Query Analyzer
> I am not able to know which line is giving the problem. How do I make
> QA show me the offending line.
> The script has lot of GO statements, usuall one after every 200 lines.
> TIA
Try running them as separate batches; one at a time until you find the
batch with the error.
--
David Gugick
Quest Software
www.imceda.com
www.quest.com|||If you put each insert into its own batch, on error, you can just double
click on the error message in the result pane which QA should bring you to
the line that fails.
--
-oj
"Data Cruncher" <dcruncher4@.netscape.net> wrote in message
news:3g6ttvFb1038U1@.individual.net...
>I am running a script which inserts large number of rows thru INSERT INTO
>VALUES statement.
> One of them is giving some error. While running it in Query Analyzer I am
> not able to know which line is giving the problem. How do I make QA show
> me the offending line.
> The script has lot of GO statements, usuall one after every 200 lines.
> TIA

how to find which line has error

I am running a script which inserts large number of rows thru
INSERT INTO VALUES statement.
One of them is giving some error. While running it in Query Analyzer I am not able to know
which line is giving the problem. How do I make QA show me the offending line.
The script has lot of GO statements, usuall one after every 200 lines.
TIA
Data Cruncher wrote:
> I am running a script which inserts large number of rows thru
> INSERT INTO VALUES statement.
> One of them is giving some error. While running it in Query Analyzer
> I am not able to know which line is giving the problem. How do I make
> QA show me the offending line.
> The script has lot of GO statements, usuall one after every 200 lines.
> TIA
Try running them as separate batches; one at a time until you find the
batch with the error.
David Gugick
Quest Software
www.imceda.com
www.quest.com
|||If you put each insert into its own batch, on error, you can just double
click on the error message in the result pane which QA should bring you to
the line that fails.
-oj
"Data Cruncher" <dcruncher4@.netscape.net> wrote in message
news:3g6ttvFb1038U1@.individual.net...
>I am running a script which inserts large number of rows thru INSERT INTO
>VALUES statement.
> One of them is giving some error. While running it in Query Analyzer I am
> not able to know which line is giving the problem. How do I make QA show
> me the offending line.
> The script has lot of GO statements, usuall one after every 200 lines.
> TIA

how to find which line has error

I am running a script which inserts large number of rows thru
INSERT INTO VALUES statement.
One of them is giving some error. While running it in Query Analyzer I am no
t able to know
which line is giving the problem. How do I make QA show me the offending lin
e.
The script has lot of GO statements, usuall one after every 200 lines.
TIAData Cruncher wrote:
> I am running a script which inserts large number of rows thru
> INSERT INTO VALUES statement.
> One of them is giving some error. While running it in Query Analyzer
> I am not able to know which line is giving the problem. How do I make
> QA show me the offending line.
> The script has lot of GO statements, usuall one after every 200 lines.
> TIA
Try running them as separate batches; one at a time until you find the
batch with the error.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||If you put each insert into its own batch, on error, you can just double
click on the error message in the result pane which QA should bring you to
the line that fails.
-oj
"Data Cruncher" <dcruncher4@.netscape.net> wrote in message
news:3g6ttvFb1038U1@.individual.net...
>I am running a script which inserts large number of rows thru INSERT INTO
>VALUES statement.
> One of them is giving some error. While running it in Query Analyzer I am
> not able to know which line is giving the problem. How do I make QA show
> me the offending line.
> The script has lot of GO statements, usuall one after every 200 lines.
> TIAsql

how to find the windows path of custom assembly using c# code?

I am using a custom assembly. My assembly has to read a particular file and
take some values from it. I set the required permissions and everything works
good. However I dont want to hardcode the location of my text file. I decided
to put the text file in the same location as dll file i.e in ..\Program
Files\Reporting Services\ReportServer\bin folder and thought of finding the
path in my class library using something like
String pathval=System.Reflection.Assembly.GetExecutingAssembly().CodeBase;
But this method always gives me #Error in my report expression. I also tried
by removing all other code and just returning the path. Also if I try to
return System.Reflection.Assembly.GetExecutingAssembly().FullName; it works
good. I am totally confused as how one property will work and another one
does not. Is there anything I am missing like setting some reference or
changing some permission etc.
I want to know the location of the dll i.e windows path(C:\Program
Files\Reporting Services\ReportServer\bin) using c# code which will work with
reporting services.
Thanks in advance.Probably the exception you are getting is FileIOPermission. If you look at
the Assembly.CodeBase Property topic in BOL, you will see that it requires
FileIOPermission. To confirm this, disable CAS in RS (comment the
<securityPolicy> element in Report Server web.config) and see if it will
execute fine.
See
http://groups.google.com/group/microsoft.public.sqlserver.reportingsvcs/browse_frm/thread/2bd7a109fd6dbe4b/df80503c4773a1b0?lnk=st&q=fileiopermission+assert+group%3A*.reportingsvcs&rnum=1&hl=en#df80503c4773a1b0
or increase the expression CAS policy to FullTrust by changing the
MyComputer GodeGroup in rssrvpolicy.config as follows
<CodeGroup
class="FirstMatchCodeGroup"
version="1"
PermissionSetName="FullTrust"
Description="This code group grants
MyComputer code Execution permission. ">
<IMembershipCondition
class="ZoneMembershipCondition"
version="1"
Zone="MyComputer" />
--
HTH,
---
Teo Lachev, MVP, MCSD, MCT
"Microsoft Reporting Services in Action"
"Applied Microsoft Analysis Services 2005"
Home page and blog: http://www.prologika.com/
---
"msdnuser" <ringt@.nospam.nospam> wrote in message
news:3469D9D8-469F-4A2C-A7D9-A3CE022FCE72@.microsoft.com...
> I am using a custom assembly. My assembly has to read a particular file
> and
> take some values from it. I set the required permissions and everything
> works
> good. However I dont want to hardcode the location of my text file. I
> decided
> to put the text file in the same location as dll file i.e in ..\Program
> Files\Reporting Services\ReportServer\bin folder and thought of finding
> the
> path in my class library using something like
> String pathval=System.Reflection.Assembly.GetExecutingAssembly().CodeBase;
> But this method always gives me #Error in my report expression. I also
> tried
> by removing all other code and just returning the path. Also if I try to
> return System.Reflection.Assembly.GetExecutingAssembly().FullName; it
> works
> good. I am totally confused as how one property will work and another one
> does not. Is there anything I am missing like setting some reference or
> changing some permission etc.
> I want to know the location of the dll i.e windows path(C:\Program
> Files\Reporting Services\ReportServer\bin) using c# code which will work
> with
> reporting services.
> Thanks in advance.
>
>|||Thanks for Teo's inputs,
Hi Ringt,
I think you can try checking the CAS permission as Teo suggested first. To
temporarly turn off .NET code access security, we can also execute the:
caspol -s off under the VS.NET command console.....
In addition, as for getting assembly path, we don't quite suggest use
codebase since this is not always correctly in some certain scenario (when
assemblies are added into GAC and the original path not in application's
directory...). I think you can also try
AppDomain.BaseDirectory , this will return the base physical location of
our application(if we execute it on the main appdomain......)..
Thanks,
Steven Cheng
Microsoft Online Support
Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)
| From: "Teo Lachev [MVP]" <teo.lachev@.nospam.prologika.com>
| References: <3469D9D8-469F-4A2C-A7D9-A3CE022FCE72@.microsoft.com>
| Subject: Re: how to find the windows path of custom assembly using c#
code?
| Date: Tue, 13 Dec 2005 16:27:58 -0500
| Lines: 67
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.2900.2180
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180
| X-RFC2646: Format=Flowed; Original
| Message-ID: <e5Y7SwCAGHA.3864@.tk2msftngp13.phx.gbl>
| Newsgroups: microsoft.public.sqlserver.reportingsvcs
| NNTP-Posting-Host: mail01-ips.ips-sendero.com 12.163.123.194
| Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGP08.phx.gbl!tk2msftngp13.phx.gbl
| Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.reportingsvcs:64985
| X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
|
| Probably the exception you are getting is FileIOPermission. If you look
at
| the Assembly.CodeBase Property topic in BOL, you will see that it
requires
| FileIOPermission. To confirm this, disable CAS in RS (comment the
| <securityPolicy> element in Report Server web.config) and see if it will
| execute fine.
|
| See
|
http://groups.google.com/group/microsoft.public.sqlserver.reportingsvcs/brow
se_frm/thread/2bd7a109fd6dbe4b/df80503c4773a1b0?lnk=st&q=fileiopermission+as
sert+group%3A*.reportingsvcs&rnum=1&hl=en#df80503c4773a1b0
|
| or increase the expression CAS policy to FullTrust by changing the
| MyComputer GodeGroup in rssrvpolicy.config as follows
| <CodeGroup
| class="FirstMatchCodeGroup"
| version="1"
| PermissionSetName="FullTrust"
| Description="This code group grants
| MyComputer code Execution permission. ">
| <IMembershipCondition
| class="ZoneMembershipCondition"
| version="1"
| Zone="MyComputer" />
|
| --
| HTH,
| ---
| Teo Lachev, MVP, MCSD, MCT
| "Microsoft Reporting Services in Action"
| "Applied Microsoft Analysis Services 2005"
| Home page and blog: http://www.prologika.com/
|
| ---
| "msdnuser" <ringt@.nospam.nospam> wrote in message
| news:3469D9D8-469F-4A2C-A7D9-A3CE022FCE72@.microsoft.com...
| >
| > I am using a custom assembly. My assembly has to read a particular file
| > and
| > take some values from it. I set the required permissions and everything
| > works
| > good. However I dont want to hardcode the location of my text file. I
| > decided
| > to put the text file in the same location as dll file i.e in ..\Program
| > Files\Reporting Services\ReportServer\bin folder and thought of finding
| > the
| > path in my class library using something like
| >
| > String
pathval=System.Reflection.Assembly.GetExecutingAssembly().CodeBase;
| >
| > But this method always gives me #Error in my report expression. I also
| > tried
| > by removing all other code and just returning the path. Also if I try to
| > return System.Reflection.Assembly.GetExecutingAssembly().FullName; it
| > works
| > good. I am totally confused as how one property will work and another
one
| > does not. Is there anything I am missing like setting some reference or
| > changing some permission etc.
| >
| > I want to know the location of the dll i.e windows path(C:\Program
| > Files\Reporting Services\ReportServer\bin) using c# code which will
work
| > with
| > reporting services.
| >
| > Thanks in advance.
| >
| >
| >
|
|
||||Hi Teo and Steven,
Thank you both for the replies. I am able to get the executing path of
assembly using System.AppDomain.CurrentDomain.BaseDirectory only if I give
FullTrust permissionset to "MyComputer" and
"Report_Expressions_Default_Permissions" Code Groups. Is there a way I can
reduce the permission and make them work?
Note: I also have my own permission set and code group to give my dll, read
permission to my text file which is not a config file. I am using Reporting
Services 2005.
Thanks,
ringt|||The links I included in my previous post should get you started although in
your case it is like catch-22. You need to grant access to a path which is
known only at runtime. You have to do some reasearch to find if this is
possible at all.
Good luck!
--
HTH,
---
Teo Lachev, MVP, MCSD, MCT
"Microsoft Reporting Services in Action"
"Applied Microsoft Analysis Services 2005"
Home page and blog: http://www.prologika.com/
---
"msdnuser" <ringt@.nospam.nospam> wrote in message
news:8F143CDF-66BE-4E12-AC74-7322855E18E3@.microsoft.com...
> Hi Teo and Steven,
> Thank you both for the replies. I am able to get the executing path of
> assembly using System.AppDomain.CurrentDomain.BaseDirectory only if I give
> FullTrust permissionset to "MyComputer" and
> "Report_Expressions_Default_Permissions" Code Groups. Is there a way I can
> reduce the permission and make them work?
> Note: I also have my own permission set and code group to give my dll,
> read
> permission to my text file which is not a config file. I am using
> Reporting
> Services 2005.
> Thanks,
> ringt
>|||Hi ringt,
I'm afraid grant the sufficient CAS permission is necessary. However, I
agree with you that it's not necessary to grant the FullTrust to
LocalComputer zone... You can choose to adjust the existing PermissionSet
, e.g the "Execution" or add a new named PermissionSet and add those
FileIOPermissions in that PermissionSet. To grant the custom PermssionSet
to your own assembly, you can consider strong-named your assemtly so that
you can grant permissionSet through Strong-named condition.....
So far I haven't found any resource directly describing modifying the
reporting service's security policy file, but there is on good article
discussing customize ASP.NET 2.0 security policys:
#How To: Use Code Access Security in ASP.NET 2.0
http://msdn.microsoft.com/library/en-us/dnpag2/html/paght000017.asp?frame=tr
ue
this article mentioned the similiar ideas on customizing existing security
policy file and add FileIOPermission for certain CodeGroup....
Hope helps. Thanks,
Steven Cheng
Microsoft Online Support
Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)
| From: "Teo Lachev [MVP]" <teo.lachev@.nospam.prologika.com>
| References: <3469D9D8-469F-4A2C-A7D9-A3CE022FCE72@.microsoft.com>
<e5Y7SwCAGHA.3864@.tk2msftngp13.phx.gbl>
<P8Vq8jFAGHA.3764@.TK2MSFTNGXA02.phx.gbl>
<8F143CDF-66BE-4E12-AC74-7322855E18E3@.microsoft.com>
| Subject: Re: how to find the windows path of custom assembly using c#
code?
| Date: Wed, 14 Dec 2005 21:27:19 -0500
| Lines: 38
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.2900.2180
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180
| X-RFC2646: Format=Flowed; Original
| Message-ID: <uiwOM8RAGHA.2040@.TK2MSFTNGP14.phx.gbl>
| Newsgroups: microsoft.public.sqlserver.reportingsvcs
| NNTP-Posting-Host: c-66-56-76-188.hsd1.ga.comcast.net 66.56.76.188
| Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP14.phx.gbl
| Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.reportingsvcs:65108
| X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
|
| The links I included in my previous post should get you started although
in
| your case it is like catch-22. You need to grant access to a path which
is
| known only at runtime. You have to do some reasearch to find if this is
| possible at all.
|
| Good luck!
|
| --
| HTH,
| ---
| Teo Lachev, MVP, MCSD, MCT
| "Microsoft Reporting Services in Action"
| "Applied Microsoft Analysis Services 2005"
| Home page and blog: http://www.prologika.com/
|
| ---
| "msdnuser" <ringt@.nospam.nospam> wrote in message
| news:8F143CDF-66BE-4E12-AC74-7322855E18E3@.microsoft.com...
| > Hi Teo and Steven,
| >
| > Thank you both for the replies. I am able to get the executing path of
| > assembly using System.AppDomain.CurrentDomain.BaseDirectory only if I
give
| > FullTrust permissionset to "MyComputer" and
| > "Report_Expressions_Default_Permissions" Code Groups. Is there a way I
can
| > reduce the permission and make them work?
| >
| > Note: I also have my own permission set and code group to give my dll,
| > read
| > permission to my text file which is not a config file. I am using
| > Reporting
| > Services 2005.
| >
| > Thanks,
| > ringt
| >
| >
|
|
||||Hi there,
As Steven mentioned, I have to give fullTrust permission to get the path
during runtime. Even then as Toe have mentioned asserting permissions to a
path known at runtime is not successful to me. It throws me #Error again. Is
there any other way (even by reducing or modifying some permissions) to
implement this?
Expecting your help.
Thanks.
"Steven Cheng[MSFT]" wrote:
> Hi ringt,
> I'm afraid grant the sufficient CAS permission is necessary. However, I
> agree with you that it's not necessary to grant the FullTrust to
> LocalComputer zone... You can choose to adjust the existing PermissionSet
> , e.g the "Execution" or add a new named PermissionSet and add those
> FileIOPermissions in that PermissionSet. To grant the custom PermssionSet
> to your own assembly, you can consider strong-named your assemtly so that
> you can grant permissionSet through Strong-named condition.....
> So far I haven't found any resource directly describing modifying the
> reporting service's security policy file, but there is on good article
> discussing customize ASP.NET 2.0 security policys:
>
> #How To: Use Code Access Security in ASP.NET 2.0
> http://msdn.microsoft.com/library/en-us/dnpag2/html/paght000017.asp?frame=tr
> ue
> this article mentioned the similiar ideas on customizing existing security
> policy file and add FileIOPermission for certain CodeGroup....
>
> Hope helps. Thanks,
> Steven Cheng
> Microsoft Online Support
> Get Secure! www.microsoft.com/security
> (This posting is provided "AS IS", with no warranties, and confers no
> rights.)
>
>
>
> --
> | From: "Teo Lachev [MVP]" <teo.lachev@.nospam.prologika.com>
> | References: <3469D9D8-469F-4A2C-A7D9-A3CE022FCE72@.microsoft.com>
> <e5Y7SwCAGHA.3864@.tk2msftngp13.phx.gbl>
> <P8Vq8jFAGHA.3764@.TK2MSFTNGXA02.phx.gbl>
> <8F143CDF-66BE-4E12-AC74-7322855E18E3@.microsoft.com>
> | Subject: Re: how to find the windows path of custom assembly using c#
> code?
> | Date: Wed, 14 Dec 2005 21:27:19 -0500
> | Lines: 38
> | X-Priority: 3
> | X-MSMail-Priority: Normal
> | X-Newsreader: Microsoft Outlook Express 6.00.2900.2180
> | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180
> | X-RFC2646: Format=Flowed; Original
> | Message-ID: <uiwOM8RAGHA.2040@.TK2MSFTNGP14.phx.gbl>
> | Newsgroups: microsoft.public.sqlserver.reportingsvcs
> | NNTP-Posting-Host: c-66-56-76-188.hsd1.ga.comcast.net 66.56.76.188
> | Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP14.phx.gbl
> | Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.reportingsvcs:65108
> | X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
> |
> | The links I included in my previous post should get you started although
> in
> | your case it is like catch-22. You need to grant access to a path which
> is
> | known only at runtime. You have to do some reasearch to find if this is
> | possible at all.
> |
> | Good luck!
> |
> | --
> | HTH,
> | ---
> | Teo Lachev, MVP, MCSD, MCT
> | "Microsoft Reporting Services in Action"
> | "Applied Microsoft Analysis Services 2005"
> | Home page and blog: http://www.prologika.com/
> |
> | ---
> | "msdnuser" <ringt@.nospam.nospam> wrote in message
> | news:8F143CDF-66BE-4E12-AC74-7322855E18E3@.microsoft.com...
> | > Hi Teo and Steven,
> | >
> | > Thank you both for the replies. I am able to get the executing path of
> | > assembly using System.AppDomain.CurrentDomain.BaseDirectory only if I
> give
> | > FullTrust permissionset to "MyComputer" and
> | > "Report_Expressions_Default_Permissions" Code Groups. Is there a way I
> can
> | > reduce the permission and make them work?
> | >
> | > Note: I also have my own permission set and code group to give my dll,
> | > read
> | > permission to my text file which is not a config file. I am using
> | > Reporting
> | > Services 2005.
> | >
> | > Thanks,
> | > ringt
> | >
> | >
> |
> |
> |
>|||Hi Teo and Steven,
Please ignore my last reply in this thread. I made a mistake of not
appending my file name after I find the path using
System.AppDomain.CurrentDomain.BaseDirectory. Thus my problem is solved now.
Thank you very much.
"msdnuser" wrote:
> Hi there,
> As Steven mentioned, I have to give fullTrust permission to get the path
> during runtime. Even then as Toe have mentioned asserting permissions to a
> path known at runtime is not successful to me. It throws me #Error again. Is
> there any other way (even by reducing or modifying some permissions) to
> implement this?
> Expecting your help.
> Thanks.
>
> "Steven Cheng[MSFT]" wrote:
> > Hi ringt,
> >
> > I'm afraid grant the sufficient CAS permission is necessary. However, I
> > agree with you that it's not necessary to grant the FullTrust to
> > LocalComputer zone... You can choose to adjust the existing PermissionSet
> > , e.g the "Execution" or add a new named PermissionSet and add those
> > FileIOPermissions in that PermissionSet. To grant the custom PermssionSet
> > to your own assembly, you can consider strong-named your assemtly so that
> > you can grant permissionSet through Strong-named condition.....
> >
> > So far I haven't found any resource directly describing modifying the
> > reporting service's security policy file, but there is on good article
> > discussing customize ASP.NET 2.0 security policys:
> >
> >
> > #How To: Use Code Access Security in ASP.NET 2.0
> > http://msdn.microsoft.com/library/en-us/dnpag2/html/paght000017.asp?frame=tr
> > ue
> >
> > this article mentioned the similiar ideas on customizing existing security
> > policy file and add FileIOPermission for certain CodeGroup....
> >
> >
> > Hope helps. Thanks,
> >
> > Steven Cheng
> > Microsoft Online Support
> >
> > Get Secure! www.microsoft.com/security
> > (This posting is provided "AS IS", with no warranties, and confers no
> > rights.)
> >
> >
> >
> >
> >
> >
> > --
> > | From: "Teo Lachev [MVP]" <teo.lachev@.nospam.prologika.com>
> > | References: <3469D9D8-469F-4A2C-A7D9-A3CE022FCE72@.microsoft.com>
> > <e5Y7SwCAGHA.3864@.tk2msftngp13.phx.gbl>
> > <P8Vq8jFAGHA.3764@.TK2MSFTNGXA02.phx.gbl>
> > <8F143CDF-66BE-4E12-AC74-7322855E18E3@.microsoft.com>
> > | Subject: Re: how to find the windows path of custom assembly using c#
> > code?
> > | Date: Wed, 14 Dec 2005 21:27:19 -0500
> > | Lines: 38
> > | X-Priority: 3
> > | X-MSMail-Priority: Normal
> > | X-Newsreader: Microsoft Outlook Express 6.00.2900.2180
> > | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180
> > | X-RFC2646: Format=Flowed; Original
> > | Message-ID: <uiwOM8RAGHA.2040@.TK2MSFTNGP14.phx.gbl>
> > | Newsgroups: microsoft.public.sqlserver.reportingsvcs
> > | NNTP-Posting-Host: c-66-56-76-188.hsd1.ga.comcast.net 66.56.76.188
> > | Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP14.phx.gbl
> > | Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.reportingsvcs:65108
> > | X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
> > |
> > | The links I included in my previous post should get you started although
> > in
> > | your case it is like catch-22. You need to grant access to a path which
> > is
> > | known only at runtime. You have to do some reasearch to find if this is
> > | possible at all.
> > |
> > | Good luck!
> > |
> > | --
> > | HTH,
> > | ---
> > | Teo Lachev, MVP, MCSD, MCT
> > | "Microsoft Reporting Services in Action"
> > | "Applied Microsoft Analysis Services 2005"
> > | Home page and blog: http://www.prologika.com/
> > |
> > | ---
> > | "msdnuser" <ringt@.nospam.nospam> wrote in message
> > | news:8F143CDF-66BE-4E12-AC74-7322855E18E3@.microsoft.com...
> > | > Hi Teo and Steven,
> > | >
> > | > Thank you both for the replies. I am able to get the executing path of
> > | > assembly using System.AppDomain.CurrentDomain.BaseDirectory only if I
> > give
> > | > FullTrust permissionset to "MyComputer" and
> > | > "Report_Expressions_Default_Permissions" Code Groups. Is there a way I
> > can
> > | > reduce the permission and make them work?
> > | >
> > | > Note: I also have my own permission set and code group to give my dll,
> > | > read
> > | > permission to my text file which is not a config file. I am using
> > | > Reporting
> > | > Services 2005.
> > | >
> > | > Thanks,
> > | > ringt
> > | >
> > | >
> > |
> > |
> > |
> >
> >|||You're welcome Ringt,
Also, I didn't mean that we have to grant FullTrust permission , but should
grant whatever permission that is necessary for our custom operations. and
for your scenario, this is the FileIOPermission... :-)
Anyway, glad that you've got it working....
Regards,
Steven Cheng
Microsoft Online Support
Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)
--
| Thread-Topic: how to find the windows path of custom assembly using c#
code?
| thread-index: AcYCXj/lieGZBwSnQZaVOJeA9HQJ8Q==| X-WBNR-Posting-Host: 38.113.18.195
| From: "=?Utf-8?B?bXNkbnVzZXI=?=" <ringt@.nospam.nospam>
| References: <3469D9D8-469F-4A2C-A7D9-A3CE022FCE72@.microsoft.com>
<e5Y7SwCAGHA.3864@.tk2msftngp13.phx.gbl>
<P8Vq8jFAGHA.3764@.TK2MSFTNGXA02.phx.gbl>
<8F143CDF-66BE-4E12-AC74-7322855E18E3@.microsoft.com>
<uiwOM8RAGHA.2040@.TK2MSFTNGP14.phx.gbl>
<5s9mSKWAGHA.1504@.TK2MSFTNGXA02.phx.gbl>
<92BCD84D-F5A4-4E8E-AF01-463F71D0FB24@.microsoft.com>
| Subject: Re: how to find the windows path of custom assembly using c#
code?
| Date: Fri, 16 Dec 2005 08:32:04 -0800
| Lines: 131
| Message-ID: <A04C0A67-0357-46A7-B39B-793FBC570C33@.microsoft.com>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="Utf-8"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Content-Class: urn:content-classes:message
| Importance: normal
| Priority: normal
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| Newsgroups: microsoft.public.sqlserver.reportingsvcs
| NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
| Path:
TK2MSFTNGXA02.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGXA01.phx.gbl!TK2MSFTNGXA
03.phx.gbl
| Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.reportingsvcs:65287
| X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
|
| Hi Teo and Steven,
|
| Please ignore my last reply in this thread. I made a mistake of not
| appending my file name after I find the path using
| System.AppDomain.CurrentDomain.BaseDirectory. Thus my problem is solved
now.
| Thank you very much.
|
|
|
| "msdnuser" wrote:
|
| > Hi there,
| >
| > As Steven mentioned, I have to give fullTrust permission to get the
path
| > during runtime. Even then as Toe have mentioned asserting permissions
to a
| > path known at runtime is not successful to me. It throws me #Error
again. Is
| > there any other way (even by reducing or modifying some permissions) to
| > implement this?
| >
| > Expecting your help.
| >
| > Thanks.
| >
| >
| >
| > "Steven Cheng[MSFT]" wrote:
| >
| > > Hi ringt,
| > >
| > > I'm afraid grant the sufficient CAS permission is necessary. However,
I
| > > agree with you that it's not necessary to grant the FullTrust to
| > > LocalComputer zone... You can choose to adjust the existing
PermissionSet
| > > , e.g the "Execution" or add a new named PermissionSet and add
those
| > > FileIOPermissions in that PermissionSet. To grant the custom
PermssionSet
| > > to your own assembly, you can consider strong-named your assemtly so
that
| > > you can grant permissionSet through Strong-named condition.....
| > >
| > > So far I haven't found any resource directly describing modifying
the
| > > reporting service's security policy file, but there is on good
article
| > > discussing customize ASP.NET 2.0 security policys:
| > >
| > >
| > > #How To: Use Code Access Security in ASP.NET 2.0
| > >
http://msdn.microsoft.com/library/en-us/dnpag2/html/paght000017.asp?frame=tr
| > > ue
| > >
| > > this article mentioned the similiar ideas on customizing existing
security
| > > policy file and add FileIOPermission for certain CodeGroup....
| > >
| > >
| > > Hope helps. Thanks,
| > >
| > > Steven Cheng
| > > Microsoft Online Support
| > >
| > > Get Secure! www.microsoft.com/security
| > > (This posting is provided "AS IS", with no warranties, and confers no
| > > rights.)
| > >
| > >
| > >
| > >
| > >
| > >
| > > --
| > > | From: "Teo Lachev [MVP]" <teo.lachev@.nospam.prologika.com>
| > > | References: <3469D9D8-469F-4A2C-A7D9-A3CE022FCE72@.microsoft.com>
| > > <e5Y7SwCAGHA.3864@.tk2msftngp13.phx.gbl>
| > > <P8Vq8jFAGHA.3764@.TK2MSFTNGXA02.phx.gbl>
| > > <8F143CDF-66BE-4E12-AC74-7322855E18E3@.microsoft.com>
| > > | Subject: Re: how to find the windows path of custom assembly using
c#
| > > code?
| > > | Date: Wed, 14 Dec 2005 21:27:19 -0500
| > > | Lines: 38
| > > | X-Priority: 3
| > > | X-MSMail-Priority: Normal
| > > | X-Newsreader: Microsoft Outlook Express 6.00.2900.2180
| > > | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180
| > > | X-RFC2646: Format=Flowed; Original
| > > | Message-ID: <uiwOM8RAGHA.2040@.TK2MSFTNGP14.phx.gbl>
| > > | Newsgroups: microsoft.public.sqlserver.reportingsvcs
| > > | NNTP-Posting-Host: c-66-56-76-188.hsd1.ga.comcast.net 66.56.76.188
| > > | Path:
TK2MSFTNGXA02.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP14.phx.gbl
| > > | Xref: TK2MSFTNGXA02.phx.gbl
microsoft.public.sqlserver.reportingsvcs:65108
| > > | X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
| > > |
| > > | The links I included in my previous post should get you started
although
| > > in
| > > | your case it is like catch-22. You need to grant access to a path
which
| > > is
| > > | known only at runtime. You have to do some reasearch to find if
this is
| > > | possible at all.
| > > |
| > > | Good luck!
| > > |
| > > | --
| > > | HTH,
| > > | ---
| > > | Teo Lachev, MVP, MCSD, MCT
| > > | "Microsoft Reporting Services in Action"
| > > | "Applied Microsoft Analysis Services 2005"
| > > | Home page and blog: http://www.prologika.com/
| > > |
| > > | ---
| > > | "msdnuser" <ringt@.nospam.nospam> wrote in message
| > > | news:8F143CDF-66BE-4E12-AC74-7322855E18E3@.microsoft.com...
| > > | > Hi Teo and Steven,
| > > | >
| > > | > Thank you both for the replies. I am able to get the executing
path of
| > > | > assembly using System.AppDomain.CurrentDomain.BaseDirectory only
if I
| > > give
| > > | > FullTrust permissionset to "MyComputer" and
| > > | > "Report_Expressions_Default_Permissions" Code Groups. Is there a
way I
| > > can
| > > | > reduce the permission and make them work?
| > > | >
| > > | > Note: I also have my own permission set and code group to give my
dll,
| > > | > read
| > > | > permission to my text file which is not a config file. I am using
| > > | > Reporting
| > > | > Services 2005.
| > > | >
| > > | > Thanks,
| > > | > ringt
| > > | >
| > > | >
| > > |
| > > |
| > > |
| > >
| > >
|

How to find the second largest value in a field !

Hi,

i am taking the values from four tables ,

I am showing the Salesman in the descending order. according to their Sale Amount. by displaying in Descending, user can able to view the
salesman who sold for the highest amount.

Now I want to find the second highest Amount in the field.

for the highest and lowest we can use the Max and Min funtion.

for the second highest value, How can I write the query.

I already check the previous forums. But i couldn't get the idea.

Kindly reply me

Thank you very much,
Chock.Originally posted by chock
Hi,

i am taking the values from four tables ,

I am showing the Salesman in the descending order. according to their Sale Amount. by displaying in Descending, user can able to view the
salesman who sold for the highest amount.

Now I want to find the second highest Amount in the field.

for the highest and lowest we can use the Max and Min funtion.

for the second highest value, How can I write the query.

I already check the previous forums. But i couldn't get the idea.

Kindly reply me

Thank you very much,
Chock.
Well one way would be to say: what is the highest value after the highest value has been excluded (if you follow me):

SELECT MAX(amount)
FROM mytab
WHERE amount != (SELECT MAX(amount) FROM mytab);

Of course, you wouldn't want to use this recursive approach to get the 5th highest amount! For that, you could do:

SELECT amount FROM mytab m1
WHERE 4 =
(SELECT COUNT(DISTINCT amount) FROM mytab m2
WHERE m2.amount > m1.amount
);

i.e. get the amount for which there are exactly 4 higher amounts in the table.|||Hi,

You send me two queries, the first query I understand it. But in the second query

SELECT amount FROM mytab m1
WHERE 4 =
(SELECT COUNT(DISTINCT amount) FROM mytab m2
WHERE m2.amount > m1.amount
);
what's m1 and what's m2. In the previous query you didn't use the m1.

Actually Amount is the Field name we are going to compare and select.
and mytab is the Table Name.
I am new to this so I think i need some more o understand. can you please tell about the m1 and m2.

Thank you very much,
Chock.|||Originally posted by chock
Hi,

You send me two queries, the first query I understand it. But in the second query

SELECT amount FROM mytab m1
WHERE 4 =
(SELECT COUNT(DISTINCT amount) FROM mytab m2
WHERE m2.amount > m1.amount
);
what's m1 and what's m2. In the previous query you didn't use the m1.

Actually Amount is the Field name we are going to compare and select.
and mytab is the Table Name.
I am new to this so I think i need some more o understand. can you please tell about the m1 and m2.

Thank you very much,
Chock.
m1 and m2 are "aliases". I made them up, because I wanted to use the same table "mytab" twice in the same query and compare values. Without aliases the query would be:

SELECT amount FROM mytab
WHERE 4 =
(SELECT COUNT(DISTINCT amount) FROM mytab
WHERE mytab.amount > mytab.amount
);

... which will return no data, because the condition "WHERE mytab.amount > mytab.amount" is nonsense. What I want to say is "WHERE mytab.amount (in this subquery) > mytab.amount (in the main query)". Aliases allow you to do that.|||tony, you may have confused the issue by jumping from the second highest to the fifth

here's another way to get the row with the second highest value:select Salesman, SaleAmount
from SalesTable
where SaleAmount =
( select max(SaleAmount)
from SalesTable
where SaleAmount <
( select max(SaleAmount)
from SalesTable
)
)in english, "get the row where the SaleAmount is the highest SaleAmount that is less than the highest overall SaleAmount"

wouldn't want to nest that too deeply, eh

i believe a good optimiser will evaluate the innermost first (it is not correlated), then the next inner, then do a straight retrieval -- i could be wrong, though (it has happened, and optimizer performance is not my long suit)

rudy
http://r937.com

how to find size of the index

sp_spaceused <tablename>
select dpages,reserved,used from sysindexes where id=357576312
These values seem to be different. Can somebody explan?
sp_spaceused <tablename>
namerowsreserveddataindex_sizeunused
<tablename>1998 136 KB72 KB8 KB56 KB
select id from sysobjects where name = <table_name>
357576312
select dpages,reserved,used from sysindexes where id=357576312
dpagesreservedused
91710
select (9*8)+(17*8)+(10*8) 288
select (136+72+8+56) 272
"mani" wrote:

> sp_spaceused <tablename>
> select dpages,reserved,used from sysindexes where id=357576312
>
> These values seem to be different. Can somebody explan?
|||You need a little math help:
Reserved = Data + Index + Unused = Used + Unused
17 Reserved Pages * 8 KB/page = 136 KB =
9 Data Pages * 8 KB/page = 72 KB +
1 Index Page * 8 KB/page = 10 Used Pages - 9 Data Pages = 8 KB
(9 Data Pages + 1 Index Page) * 8 KB/page = 10 Used Pages * 8 KB/page = 80 KB
Now, (17 Reserved Pages - 10 Used Pages) * 8 KB/page =
7 Unused Pages * 8 KB/page = 56 KB.
You just have to figure out which pieces are which but it is all consistent.
Your problem was that you added Reserved to what was used: reserved is
already the grand total you were aiming for.
Hope this helps. What also can help is to read what the authors wrote: take
a look at the sp_spaceused stored procedure,
USE master
GO
EXEC dbo.sp_helptext 'dbo.sp_spaceused'
GO
You can find a lot of interesting information by examining the code for the
system procedures.
Sincerely,
Anthony Thomas
"mani" wrote:
[vbcol=seagreen]
> sp_spaceused <tablename>
> namerowsreserveddataindex_sizeunused
> <tablename>1998 136 KB72 KB8 KB56 KB
>
> select id from sysobjects where name = <table_name>
> 357576312
> select dpages,reserved,used from sysindexes where id=357576312
> dpagesreservedused
> 91710
>
> select (9*8)+(17*8)+(10*8) 288
> select (136+72+8+56) 272
> "mani" wrote:

how to find size of the index

sp_spaceused <tablename>
select dpages,reserved,used from sysindexes where id=357576312
These values seem to be different. Can somebody explan?sp_spaceused <tablename>
name rows reserved data index_size unus
ed
<tablename> 1998 136 KB 72 KB 8 KB 56 KB
select id from sysobjects where name = <table_name>
357576312
select dpages,reserved,used from sysindexes where id=357576312
dpages reserved used
9 17 10
select (9*8)+(17*8)+(10*8) 288
select (136+72+8+56) 272
"mani" wrote:

> sp_spaceused <tablename>
> select dpages,reserved,used from sysindexes where id=357576312
>
> These values seem to be different. Can somebody explan?|||You need a little math help:
Reserved = Data + Index + Unused = Used + Unused
17 Reserved Pages * 8 KB/page = 136 KB =
9 Data Pages * 8 KB/page = 72 KB +
1 Index Page * 8 KB/page = 10 Used Pages - 9 Data Pages = 8 KB
(9 Data Pages + 1 Index Page) * 8 KB/page = 10 Used Pages * 8 KB/page = 80 K
B
Now, (17 Reserved Pages - 10 Used Pages) * 8 KB/page =
7 Unused Pages * 8 KB/page = 56 KB.
You just have to figure out which pieces are which but it is all consistent.
Your problem was that you added Reserved to what was used: reserved is
already the grand total you were aiming for.
Hope this helps. What also can help is to read what the authors wrote: take
a look at the sp_spaceused stored procedure,
USE master
GO
EXEC dbo.sp_helptext 'dbo.sp_spaceused'
GO
You can find a lot of interesting information by examining the code for the
system procedures.
Sincerely,
Anthony Thomas
"mani" wrote:
[vbcol=seagreen]
> sp_spaceused <tablename>
> name rows reserved data index_size unus
ed
> <tablename> 1998 136 KB 72 KB 8 KB 56 KB
>
> select id from sysobjects where name = <table_name>
> 357576312
> select dpages,reserved,used from sysindexes where id=357576312
> dpages reserved used
> 9 17 10
>
> select (9*8)+(17*8)+(10*8) 288
> select (136+72+8+56) 272
> "mani" wrote:
>

how to find size of the index

sp_spaceused <tablename>
select dpages,reserved,used from sysindexes where id=357576312
These values seem to be different. Can somebody explan?sp_spaceused <tablename>
name rows reserved data index_size unused
<tablename> 1998 136 KB 72 KB 8 KB 56 KB
select id from sysobjects where name = <table_name>
357576312
select dpages,reserved,used from sysindexes where id=357576312
dpages reserved used
9 17 10
select (9*8)+(17*8)+(10*8) 288
select (136+72+8+56) 272
"mani" wrote:
> sp_spaceused <tablename>
> select dpages,reserved,used from sysindexes where id=357576312
>
> These values seem to be different. Can somebody explan?|||You need a little math help:
Reserved = Data + Index + Unused = Used + Unused
17 Reserved Pages * 8 KB/page = 136 KB =9 Data Pages * 8 KB/page = 72 KB +
1 Index Page * 8 KB/page = 10 Used Pages - 9 Data Pages = 8 KB
(9 Data Pages + 1 Index Page) * 8 KB/page = 10 Used Pages * 8 KB/page = 80 KB
Now, (17 Reserved Pages - 10 Used Pages) * 8 KB/page =7 Unused Pages * 8 KB/page = 56 KB.
You just have to figure out which pieces are which but it is all consistent.
Your problem was that you added Reserved to what was used: reserved is
already the grand total you were aiming for.
Hope this helps. What also can help is to read what the authors wrote: take
a look at the sp_spaceused stored procedure,
USE master
GO
EXEC dbo.sp_helptext 'dbo.sp_spaceused'
GO
You can find a lot of interesting information by examining the code for the
system procedures.
Sincerely,
Anthony Thomas
"mani" wrote:
> sp_spaceused <tablename>
> name rows reserved data index_size unused
> <tablename> 1998 136 KB 72 KB 8 KB 56 KB
>
> select id from sysobjects where name = <table_name>
> 357576312
> select dpages,reserved,used from sysindexes where id=357576312
> dpages reserved used
> 9 17 10
>
> select (9*8)+(17*8)+(10*8) 288
> select (136+72+8+56) 272
> "mani" wrote:
> > sp_spaceused <tablename>
> >
> > select dpages,reserved,used from sysindexes where id=357576312
> >
> >
> > These values seem to be different. Can somebody explan?

How to find nth lowest value in a row

Morning all,
I have a table with 500+ fields of numeric data (a time series of values) I
import from an external system. I need to find the 5th and 6th lowest values
in a given row to perform some calculations.
I currently do this by transposing the data row ie turning the row into a
column sorting it and then using row_num to get the 5th and 6th lowest
values. This transpose is a real bottleneck on my process.
Does anyone have any alternatives to my method ? All suggestions gratefully
received.
Thanks
WOn Thu, 16 Aug 2007 17:56:35 -0700, willcas wrote:
>Morning all,
>I have a table with 500+ fields of numeric data (a time series of values) I
>import from an external system. I need to find the 5th and 6th lowest values
>in a given row to perform some calculations.
>I currently do this by transposing the data row ie turning the row into a
>column sorting it and then using row_num to get the 5th and 6th lowest
>values. This transpose is a real bottleneck on my process.
>Does anyone have any alternatives to my method ? All suggestions gratefully
>received.
Hi W,
Perhaps you could transpose the data during the import and store it in a
more relational way?
The only other alternative would be a really very nasty (and long!) CASE
epxression that I won't even begin to think about <shudder>.
--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis|||"willcas" <willcas@.discussions.microsoft.com> wrote in message
news:6658592C-0F0A-45C6-BB9C-DFDDF0263FF2@.microsoft.com...
> Morning all,
> I have a table with 500+ fields of numeric data (a time series of values)
> I
> import from an external system. I need to find the 5th and 6th lowest
> values
> in a given row to perform some calculations.
> I currently do this by transposing the data row ie turning the row into a
> column sorting it and then using row_num to get the 5th and 6th lowest
> values. This transpose is a real bottleneck on my process.
> Does anyone have any alternatives to my method ? All suggestions
> gratefully
> received.
> Thanks
> W
I would treat this as a staging table and transform the data to a properly
normalized model. Then write your query against that new model.
Perhaps better still, do the transformation before the load (using
Integration Services for example).
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Can't you use unpivot for the transposition?
"willcas" <willcas@.discussions.microsoft.com> wrote in message
news:6658592C-0F0A-45C6-BB9C-DFDDF0263FF2@.microsoft.com...
> Morning all,
> I have a table with 500+ fields of numeric data (a time series of values)
> I
> import from an external system. I need to find the 5th and 6th lowest
> values
> in a given row to perform some calculations.
> I currently do this by transposing the data row ie turning the row into a
> column sorting it and then using row_num to get the 5th and 6th lowest
> values. This transpose is a real bottleneck on my process.
> Does anyone have any alternatives to my method ? All suggestions
> gratefully
> received.
> Thanks
> W

How to find Median Values in SQL

Hello,
Is there any way or function to find Median value in Sql
Server 2000?.
Thanks,
NilayYes, refer to
http://groups.google.com/groups?dq=&hl=nl&lr=&ie=UTF-8&threadm=3BC75285.8A38CC6%40toomuchspamalready.nl&rnum=1&prev=/groups%3Fq%3Dg:thl2156744539d%26dq%3D%26hl%3Dnl%26lr%3D%26ie%3DUTF-8%26selm%3D3BC75285.8A38CC6%2540toomuchspamalready.nl
(url may wrap)
This thread gives many solutions.
Gert-Jan
Nilay wrote:
> Hello,
> Is there any way or function to find Median value in Sql
> Server 2000?.
> Thanks,
> Nilay

How to find matching profiles?

Users have to answer 17 simple yes/no questions and the answers are stored in an column for each question as tinyint 0/1 values.

At least that's what seems reasonable to me at the moment.

The table is under my control so I could change it if needed.

Now from several tenthousend or maybe hundreds of thousends of entries I need to find those with the closest match. Of course, I need all of the entries that have the exact same answers and this is no problem. But - at least if there are not enough full matches - then I need all records that have maybe 16,15,14... matches out of the 17 answers.

I have not yet the idea on how to handle this without quering 17*16 different answer schemes.

Hi,

I wouldn′t store the data denormalized. The better way to store it IMHO is to normalize the data, if you want closer machtes you can also setup a score for each answered question: Here is an extract of a possible solution:

CREATE TABLE Question
(
QuestionId INT
QuestionText VARCHAR(100)
)

CREATE TABLE Anwers
(
QuestionId INT
AnswerId INT
AnswerValue VARCHAR(10) --indicates the right answer
Score INT
)

CREATE TABLE AnweredQuestions
(
QuestionId INT
AnswerId INT
ParticipantId INT
AnswerValue
)

Thats just a quick one, could be sure more normalized, but these tables could be easy joined and scored as well in one query rather than using the different columns and its even more extensible than your current one.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||I actually have set up the tables more close to what you recommended, like this and then of course the user table. I first have to do some frontenmd stuff and then will come back to see how to handle this.

Actually there are no right or wrong answers since questions have the form like: Iwrite down what I need to buy before I go shopping. true/false.

CREATE TABLE [dbo].[tCmsElementCustomPartnermatchQuestion] (
[question_id] [int] IDENTITY (1, 1) NOT NULL ,
[question_categoryID] [int] NOT NULL ,
[question_weight] [int] NULL ,
[question_text_DE] [nvarchar] (255) ,
[question_text_FR] [nvarchar] (255) ,
[question_text_IT] [nvarchar] (255) ,
[question_active] [tinyint] NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tCmsElementCustomPartnermatchQuestionUser] (
[question_user_id] [int] IDENTITY (1, 1) NOT NULL ,
[question_user_userID] [int] NOT NULL ,
[question_user_questionID] [int] NOT NULL ,
[question_user_questionanswer] [int] NOT NULL
) ON [PRIMARY]
GO|||

Hi,

glad to hear that you picked up some ideas. There sure could be more normalized (but I do not want to exaggerate :-) ). Come back if you have any more questions, you are welcome :-)

I keep an eye on the post I answered, anyway if I overlook your answer or rerequest, feel free to contact me through my website which is mentioned below.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||thanks, very friendly.

I actually I became totally lost.

I received a hint from my boss but this one needs a totally denormalized table and only allows answers as yes or no.

Something like this:

From this kind of table

CREATE TABLE #test (

[person_id] [smallint] IDENTITY(1,1),
[person_name] [varchar] (20),
[answer001] [tinyint],
[answer002] [tinyint],
[answer003] [tinyint],
[answer004] [tinyint],
[answer005] [tinyint],
[answer006] [tinyint],
[answer007] [tinyint],
[answer008] [tinyint],
[answer009] [tinyint]
)

I should do a query like this:

SELECT t1.person_name AS p1_name, t2.person_name AS p2_name
, '''' AS person_rank
, t1.answer009*1+t1.answer008*2+t1.answer007*4+t1.answer006*8+t1.answer005*16+t1.answer004*32+t1.answer003*64+t1.answer002*128+t1.answer001*256 AS p1_value
, t2.answer009*1+t2.answer008*2+t2.answer007*4+t2.answer006*8+t2.answer005*16+t2.answer004*32+t2.answer003*64+t2.answer002*128+t2.answer001*256 AS p2_value

FROM #test t1, #test t2
WHERE t1.person_id <> t2.person_id
AND t1.person_name = 'Fritz' AND t2.person_name <> t1.person_name

and then find matches with something like this (in ColdFusion since we couldn't find the respectively SQL functions)

<cfoutput query="qgetmatchesprodandtype">
<cfset tmp = QuerySetCell(qTest, 'person_rank', 9- Len(Replace(FormatBaseN(BitXor(qTest.p1_value,qTest.p2_value),2), "0", "", "ALL")), qTest.CurrentRow)>
</cfoutput>

So, I could really need some enlightening examples

I'd prefer to match answers by counting the number of matches for each question and user undependently if the answer can only be 0/1 or any value between 0-9 (ore anything else)|||I might look at doing it this way:

Let's assume that you have a table of traits of people and you have a candidate and want to find the closest matches.... Kind of like a dating service.

If I take the absolute value of (person1.trait1 - person2.trait1) then if that is 0 they are a match on that trait, if it is 1 then they are not a match....

It follows that if I sum up the abs values of the subtracted trait pairs then the lower the overall sum the more "compatible" the two individual are:

That would lead me to look at:

Select
p1.name,
p2.name,
Sum(
abs(p1.trait1 - p2.trait1) +
abs(p1.trait2 - p2.trait2) +
abs(p1.trait3 - p2.trait3) +
.....
abs(p1.trait17 - p2.trait17)
) As matchfactor
From members as p1, members.p2
Where p1.id <> p2.id And p1.name = 'smith'
Order by matchfactor

|||

Jens Sü?meyers answer below exactly answered my problem if I added a
ORDER BY Numberofmatches DESC

the following query will solve your described problem:

SELECT

t2.[question_user_UserId],COUNT(*) AS Numberofmatches

FROM [dbo].[tCmsElementCustomPartnermatchQuestionUser] t1

INNER JOIN

tCmsElementCustomPartnermatchQuestionUser t2

ON t1.[question_user_questionID] = t2.[question_user_questionID] AND

t1.[question_user_questionanswer] = t2.[question_user_questionanswer] AND NOT

t1.[question_user_userid] = t2.[question_user_userid] --to eliminate the actual user which has the best match with himself :-)

Where T1.[question_user_userid] = 15

Group by t2.[question_user_UserId]

Let me know if that worked for you.

-Jens.

How to find matching profiles?

Users have to answer 17 simple yes/no questions and the answers are stored in an column for each question as tinyint 0/1 values.

At least that's what seems reasonable to me at the moment.

The table is under my control so I could change it if needed.

Now from several tenthousend or maybe hundreds of thousends of entries I need to find those with the closest match. Of course, I need all of the entries that have the exact same answers and this is no problem. But - at least if there are not enough full matches - then I need all records that have maybe 16,15,14... matches out of the 17 answers.

I have not yet the idea on how to handle this without quering 17*16 different answer schemes.

Hi,

I wouldn′t store the data denormalized. The better way to store it IMHO is to normalize the data, if you want closer machtes you can also setup a score for each answered question: Here is an extract of a possible solution:

CREATE TABLE Question
(
QuestionId INT
QuestionText VARCHAR(100)
)

CREATE TABLE Anwers
(
QuestionId INT
AnswerId INT
AnswerValue VARCHAR(10) --indicates the right answer
Score INT
)

CREATE TABLE AnweredQuestions
(
QuestionId INT
AnswerId INT
ParticipantId INT
AnswerValue
)

Thats just a quick one, could be sure more normalized, but these tables could be easy joined and scored as well in one query rather than using the different columns and its even more extensible than your current one.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||I actually have set up the tables more close to what you recommended, like this and then of course the user table. I first have to do some frontenmd stuff and then will come back to see how to handle this.

Actually there are no right or wrong answers since questions have the form like: Iwrite down what I need to buy before I go shopping. true/false.

CREATE TABLE [dbo].[tCmsElementCustomPartnermatchQuestion] (
[question_id] [int] IDENTITY (1, 1) NOT NULL ,
[question_categoryID] [int] NOT NULL ,
[question_weight] [int] NULL ,
[question_text_DE] [nvarchar] (255) ,
[question_text_FR] [nvarchar] (255) ,
[question_text_IT] [nvarchar] (255) ,
[question_active] [tinyint] NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tCmsElementCustomPartnermatchQuestionUser] (
[question_user_id] [int] IDENTITY (1, 1) NOT NULL ,
[question_user_userID] [int] NOT NULL ,
[question_user_questionID] [int] NOT NULL ,
[question_user_questionanswer] [int] NOT NULL
) ON [PRIMARY]
GO|||

Hi,

glad to hear that you picked up some ideas. There sure could be more normalized (but I do not want to exaggerate :-) ). Come back if you have any more questions, you are welcome :-)

I keep an eye on the post I answered, anyway if I overlook your answer or rerequest, feel free to contact me through my website which is mentioned below.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||thanks, very friendly.

I actually I became totally lost.

I received a hint from my boss but this one needs a totally denormalized table and only allows answers as yes or no.

Something like this:

From this kind of table

CREATE TABLE #test (

[person_id] [smallint] IDENTITY(1,1),
[person_name] [varchar] (20),
[answer001] [tinyint],
[answer002] [tinyint],
[answer003] [tinyint],
[answer004] [tinyint],
[answer005] [tinyint],
[answer006] [tinyint],
[answer007] [tinyint],
[answer008] [tinyint],
[answer009] [tinyint]
)

I should do a query like this:

SELECT t1.person_name AS p1_name, t2.person_name AS p2_name
, '''' AS person_rank
, t1.answer009*1+t1.answer008*2+t1.answer007*4+t1.answer006*8+t1.answer005*16+t1.answer004*32+t1.answer003*64+t1.answer002*128+t1.answer001*256 AS p1_value
, t2.answer009*1+t2.answer008*2+t2.answer007*4+t2.answer006*8+t2.answer005*16+t2.answer004*32+t2.answer003*64+t2.answer002*128+t2.answer001*256 AS p2_value

FROM #test t1, #test t2
WHERE t1.person_id <> t2.person_id
AND t1.person_name = 'Fritz' AND t2.person_name <> t1.person_name

and then find matches with something like this (in ColdFusion since we couldn't find the respectively SQL functions)

<cfoutput query="qgetmatchesprodandtype">
<cfset tmp = QuerySetCell(qTest, 'person_rank', 9- Len(Replace(FormatBaseN(BitXor(qTest.p1_value,qTest.p2_value),2), "0", "", "ALL")), qTest.CurrentRow)>
</cfoutput>

So, I could really need some enlightening examples

I'd prefer to match answers by counting the number of matches for each question and user undependently if the answer can only be 0/1 or any value between 0-9 (ore anything else)|||I might look at doing it this way:

Let's assume that you have a table of traits of people and you have a candidate and want to find the closest matches.... Kind of like a dating service.

If I take the absolute value of (person1.trait1 - person2.trait1) then if that is 0 they are a match on that trait, if it is 1 then they are not a match....

It follows that if I sum up the abs values of the subtracted trait pairs then the lower the overall sum the more "compatible" the two individual are:

That would lead me to look at:

Select
p1.name,
p2.name,
Sum(
abs(p1.trait1 - p2.trait1) +
abs(p1.trait2 - p2.trait2) +
abs(p1.trait3 - p2.trait3) +
.....
abs(p1.trait17 - p2.trait17)
) As matchfactor
From members as p1, members.p2
Where p1.id <> p2.id And p1.name = 'smith'
Order by matchfactor

|||

Jens Sü?meyers answer below exactly answered my problem if I added a
ORDER BY Numberofmatches DESC

the following query will solve your described problem:

SELECT

t2.[question_user_UserId],COUNT(*) AS Numberofmatches

FROM [dbo].[tCmsElementCustomPartnermatchQuestionUser] t1

INNER JOIN

tCmsElementCustomPartnermatchQuestionUser t2

ON t1.[question_user_questionID] = t2.[question_user_questionID] AND

t1.[question_user_questionanswer] = t2.[question_user_questionanswer] AND NOT

t1.[question_user_userid] = t2.[question_user_userid] --to eliminate the actual user which has the best match with himself :-)

Where T1.[question_user_userid] = 15

Group by t2.[question_user_UserId]

Let me know if that worked for you.

-Jens.

How to filter out unwanted nodes?

I would like to retrieve the values of ProductIDs nodes but the condition is to ignore any ProductIDs that is a child of <AlternativeState> node.

Please keep in mind that xml data comes from table and the value of ProductIDs could appear anywhere else as well so I am afraid using exact XPath is not a choice. The only time when ProductIDs is not returned when its a child of AlternativeState.

Currently I have something like this but it returns all of the productIDs from the xml instance

SELECT UsageID, xmlState.query('

for $productIDs in //*/ProductIDs

order by $productIDs

return string($productIDs)') AS ProductIDs

FROM Usage.UsageAnalysis

WHERE UsageID = 91923

<State>

<StatsState>

<CurrentState>

<OtherEvents>

<ProductIDs>46420</ProductIDs>

<AlternativeState><!-- Ignore anything in this element-->

<OtherEvents>

<ProductIDs>46420</ProductIDs>

</OtherEvents>

<MarketSize>

<ProductIDs>46431,46440,46469,46470</ProductIDs>

</MarketSize>

</AlternativeState>

</OtherEvents>

<CurrentEvent>MarketShare</CurrentEvent>

<MarketSize>

<ProductIDs>2128,2459,46420,46440,46470</ProductIDs>

</MarketSize>

</CurrentState>

</StatsState>

</State>

In your sample XML the AlternativeState element does not have a ProductIDs child, only a grand child. So for that XML sample the expression

Code Snippet

for $productIDs in //*[not(self::AlternativeState)]/*/ProductIDs

order by $productIDs

return string($productIDs)

should filter out what you want to filter out. Using an ancestor axis test should solve the problem in general but that axis is not currently supported in XQuery for SQL Server 2005.