How do I add todays date?

I am new to Reporting Services and I need to kno how to add todays date to my
reports.
ThanksI found it in , Expressions, Globals, Execution Time
"Vincel2k2" wrote:
> I am new to Reporting Services and I need to kno how to add todays date to my
> reports.
> Thanks|||Just add a TextBox field and set its value to :
=System.DateTime.Now.ToString()
If you only want the date, you shoud use :
=System.DateTime.Now.ToShortDateString()

How do I add a Percentage Column after the Total on a Matrix?

Hi. I am looking to create a Matrix-based Report in SQL Server 2005 Reporting Services. I have my query, and a dynamic number of both rows and columns. I have Totals on the "bottom" and "right".

What I'd like to do is add a Percentage column to the right of the right-based Totals column. So the columns would read Col1, Col2, .., ColN, Total, Percentage.

Is this possible?

Thanks.

You could do something like - matrixcoltotal / matrixtotal and set your Format Code to P0 on the properties window.

Your expression will look something like this. =Sum(Fields!ReportField.Value,"MatrixGroup")/Sum(Fields!ReportField.Value,"dataset"). Dont forget to change the FormatCode.

|||How would I place this column to the RIGHT of the Subtotal column?

|||

Add an invisible column group which will be grouped by the percentage expression and add the subtotal to this colum group have the which will give you the sum of all individual percentages.

Shyam

|||Here is the error message I am receiving:

"A group expression for the matrix 'matrix1' includes an aggregate function. Aggregate functions cannot be used in group expressions."

I created a new Column group, outside of my month-by-month column group, and made it invisible. I do see a new "Total" to the right....however when I put in my sum(value, "InnerRowGroup")/sum(value, "OuterRowGroup") as the group expression, I get the above error.

Thank you both greatly for your help. I feel about 75% towards getting this to work.

Jason

|||

No, you dont have to group by the aggregate which is an obvious logical error. Just group by the expression without the sum function which would be:

Fields!InnerGroupField/Fields!OuterGroupField

Then using the subtoal would automatically mean a sum which is what you want I guess.

Shyam

|||What if the percentage I want to calculate is the ratio between two columns? Is this possible, or will I have to pivot the data in the database before I send it to SSRS? This would suck since I have to completely revamp the stored proc.

How do I add a Percentage Column after the Total on a Matrix?

Hi. I am looking to create a Matrix-based Report in SQL Server 2005 Reporting Services. I have my query, and a dynamic number of both rows and columns. I have Totals on the "bottom" and "right".

What I'd like to do is add a Percentage column to the right of the right-based Totals column. So the columns would read Col1, Col2, .., ColN, Total, Percentage.

Is this possible?

Thanks.

You could do something like - matrixcoltotal / matrixtotal and set your Format Code to P0 on the properties window.

Your expression will look something like this. =Sum(Fields!ReportField.Value,"MatrixGroup")/Sum(Fields!ReportField.Value,"dataset"). Dont forget to change the FormatCode.

|||How would I place this column to the RIGHT of the Subtotal column?

|||

Add an invisible column group which will be grouped by the percentage expression and add the subtotal to this colum group have the which will give you the sum of all individual percentages.

Shyam

|||Here is the error message I am receiving:

"A group expression for the matrix 'matrix1' includes an aggregate function. Aggregate functions cannot be used in group expressions."

I created a new Column group, outside of my month-by-month column group, and made it invisible. I do see a new "Total" to the right....however when I put in my sum(value, "InnerRowGroup")/sum(value, "OuterRowGroup") as the group expression, I get the above error.

Thank you both greatly for your help. I feel about 75% towards getting this to work.

Jason

|||

No, you dont have to group by the aggregate which is an obvious logical error. Just group by the expression without the sum function which would be:

Fields!InnerGroupField/Fields!OuterGroupField

Then using the subtoal would automatically mean a sum which is what you want I guess.

Shyam

|||What if the percentage I want to calculate is the ratio between two columns? Is this possible, or will I have to pivot the data in the database before I send it to SSRS? This would suck since I have to completely revamp the stored proc.

How do I add a Percentage Column after the Total on a Matrix?

Hi. I am looking to create a Matrix-based Report in SQL Server 2005 Reporting Services. I have my query, and a dynamic number of both rows and columns. I have Totals on the "bottom" and "right".

What I'd like to do is add a Percentage column to the right of the right-based Totals column. So the columns would read Col1, Col2, .., ColN, Total, Percentage.

Is this possible?

Thanks.

You could do something like - matrixcoltotal / matrixtotal and set your Format Code to P0 on the properties window.

Your expression will look something like this. =Sum(Fields!ReportField.Value,"MatrixGroup")/Sum(Fields!ReportField.Value,"dataset"). Dont forget to change the FormatCode.

|||How would I place this column to the RIGHT of the Subtotal column?|||

Add an invisible column group which will be grouped by the percentage expression and add the subtotal to this colum group have the which will give you the sum of all individual percentages.

Shyam

|||Here is the error message I am receiving:

"A group expression for the matrix 'matrix1' includes an aggregate function. Aggregate functions cannot be used in group expressions."

I created a new Column group, outside of my month-by-month column group, and made it invisible. I do see a new "Total" to the right....however when I put in my sum(value, "InnerRowGroup")/sum(value, "OuterRowGroup") as the group expression, I get the above error.

Thank you both greatly for your help. I feel about 75% towards getting this to work.

Jason|||

No, you dont have to group by the aggregate which is an obvious logical error. Just group by the expression without the sum function which would be:

Fields!InnerGroupField/Fields!OuterGroupField

Then using the subtoal would automatically mean a sum which is what you want I guess.

Shyam

|||What if the percentage I want to calculate is the ratio between two columns? Is this possible, or will I have to pivot the data in the database before I send it to SSRS? This would suck since I have to completely revamp the stored proc.

how did you upgrade from analysis services 2000 to 2005?

i have followed the instructions online ,on how to upgrade from analysis server 2000 to 2005. i install a new instance of analysis services 2005, called instance 1, migrate all the cubes over, but when it comes to using the rename instance tool, i get errors. there is a reg key missing i have found that stops this tool from working. how have you guys gotten around the migration from 2000 to 2005?

What machine are you is it 64bit platform?

There are 2 versions of ASInstanceRename utilities. You should use one that is installed along with 64bit version of Analysis Services in the C:\Program Files\Microsoft SQL Server\90\Tools\binn\VSShell\Common7\IDE folder.

Edward Melomed.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

thats the version of the instanceRename tool but when i try to rename an instance i get this error:

Cannot open regestry key SOFTWARE\Wow6432Node\Microsoft\Microsoft SQL Server\90\Sql Bin under HKEY_LOCAL_MACHINE

Thats the only version of the tool i have on my server. I installed sql server 2005 enterprise edition, not the 64bit version though as the server im using is an old server ( used for testing) and is only a 32 bit server.

How could I know the version of Reporting Services in my machine?

How could I know the version of Reporting Services in my machine?

thanks

SELECT @.@.version

Or take a look at this:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=445611&SiteID=1

How convert an named instance to a non named instance

Hello:
Our Application Vendor has now indicated that we need to have a non-named
instance because of their software and Analysis Services. How do we do this?
A named instance is a distinct copy of all of the sql server binaries, data
files, services, etc. If you want to put your data on the default instance,
you will need to backup your databases on the named instnace and restore them
onto the default instance. You can then use the sql server installation tool
to remove the named instance.
AndyP,
Sr. Database Administrator,
MCDBA 2003
"Victoria Morrison" wrote:

> Hello:
> Our Application Vendor has now indicated that we need to have a non-named
> instance because of their software and Analysis Services. How do we do this?
>
|||How do I create the default instance which is on the same server. I tried
it with the sql server installation and only lets you do a named instance.
When you say sql server installation tool you mean using the CD
"AndyP" wrote:
[vbcol=seagreen]
> A named instance is a distinct copy of all of the sql server binaries, data
> files, services, etc. If you want to put your data on the default instance,
> you will need to backup your databases on the named instnace and restore them
> onto the default instance. You can then use the sql server installation tool
> to remove the named instance.
> --
> AndyP,
> Sr. Database Administrator,
> MCDBA 2003
>
> "Victoria Morrison" wrote:
|||> How do I create the default instance which is on the same server.
By installing a default instance, i.e., the same setup process as when you installed the named
instance.

> I tried
> it with the sql server installation and only lets you do a named instance.
Then you already have a default instance.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Victoria Morrison" <VictoriaMorrison@.discussions.microsoft.com> wrote in message
news:4154AD5D-8E4A-420E-B013-9F0B94DFB787@.microsoft.com...[vbcol=seagreen]
> How do I create the default instance which is on the same server. I tried
> it with the sql server installation and only lets you do a named instance.
> When you say sql server installation tool you mean using the CD
> "AndyP" wrote:
|||That is what I tried, I put in the SQL Server CD and when it got to piece the
default was grayed out and would not let me click it. We have the instance
presently called "srvsql02\mptp" the vendor needs as a default "srvsql02". I
think 1) backup 2)uninstall 3) reinstall 4) restore each database starting
with master??
"Tibor Karaszi" wrote:

> By installing a default instance, i.e., the same setup process as when you installed the named
> instance.
>
> Then you already have a default instance.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Victoria Morrison" <VictoriaMorrison@.discussions.microsoft.com> wrote in message
> news:4154AD5D-8E4A-420E-B013-9F0B94DFB787@.microsoft.com...
>
>
|||> That is what I tried, I put in the SQL Server CD and when it got to piece the
> default was grayed out and would not let me click it.
That would happen if you already have a default instance. You can only have one default instance.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Victoria Morrison" <VictoriaMorrison@.discussions.microsoft.com> wrote in message
news:80C97B24-AC86-426E-955E-F57E2DBB9476@.microsoft.com...[vbcol=seagreen]
> That is what I tried, I put in the SQL Server CD and when it got to piece the
> default was grayed out and would not let me click it. We have the instance
> presently called "srvsql02\mptp" the vendor needs as a default "srvsql02". I
> think 1) backup 2)uninstall 3) reinstall 4) restore each database starting
> with master??
> "Tibor Karaszi" wrote:
|||Ok, so how do a rename it from srvsql01\mptp to simply srvsql01
"Tibor Karaszi" wrote:

> That would happen if you already have a default instance. You can only have one default instance.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Victoria Morrison" <VictoriaMorrison@.discussions.microsoft.com> wrote in message
> news:80C97B24-AC86-426E-955E-F57E2DBB9476@.microsoft.com...
>
>
|||Victoria Morrison wrote:
> Ok, so how do a rename it from srvsql01\mptp to simply srvsql01
>
You can't rename an instance, but in your case it sounds like you
already has a default instance. If you haven't got one already, you
should be able to install one. Since you say that this option is greyed
out, it indicates that it's already there.
Have you tried to connect to just srvsql01 in your application,
Enterprise Manager or Query Analyser? If you have, do you then get an
error message of some kind?
Regards
Steen
|||Thank all of you. It was grayed out on my Personnel copy, but when I tried
in on the test real enterprise version the options was not grayed out. And
thank for how to removed an instance that worked also. The only thing to
remember is that you have to put back the packs since the used the CD which
is at spack2. I did not know how else to get the install started. Now I
am going to detach and attach the the user databases and then add the login
user and my jobs. Since that path do not match restoring master did not
work.
"Steen Persson (DK)" wrote:

> Victoria Morrison wrote:
> You can't rename an instance, but in your case it sounds like you
> already has a default instance. If you haven't got one already, you
> should be able to install one. Since you say that this option is greyed
> out, it indicates that it's already there.
> Have you tried to connect to just srvsql01 in your application,
> Enterprise Manager or Query Analyser? If you have, do you then get an
> error message of some kind?
> Regards
> Steen
>
sql

How convert an named instance to a non named instance

Hello:
Our Application Vendor has now indicated that we need to have a non-named
instance because of their software and Analysis Services. How do we do this
?A named instance is a distinct copy of all of the sql server binaries, data
files, services, etc. If you want to put your data on the default instance,
you will need to backup your databases on the named instnace and restore the
m
onto the default instance. You can then use the sql server installation tool
to remove the named instance.
AndyP,
Sr. Database Administrator,
MCDBA 2003
"Victoria Morrison" wrote:

> Hello:
> Our Application Vendor has now indicated that we need to have a non-named
> instance because of their software and Analysis Services. How do we do th
is?
>|||How do I create the default instance which is on the same server. I tried
it with the sql server installation and only lets you do a named instance.
When you say sql server installation tool you mean using the CD
"AndyP" wrote:
[vbcol=seagreen]
> A named instance is a distinct copy of all of the sql server binaries, dat
a
> files, services, etc. If you want to put your data on the default instance
,
> you will need to backup your databases on the named instnace and restore t
hem
> onto the default instance. You can then use the sql server installation to
ol
> to remove the named instance.
> --
> AndyP,
> Sr. Database Administrator,
> MCDBA 2003
>
> "Victoria Morrison" wrote:
>|||> How do I create the default instance which is on the same server.
By installing a default instance, i.e., the same setup process as when you i
nstalled the named
instance.

> I tried
> it with the sql server installation and only lets you do a named instance.
Then you already have a default instance.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Victoria Morrison" <VictoriaMorrison@.discussions.microsoft.com> wrote in me
ssage
news:4154AD5D-8E4A-420E-B013-9F0B94DFB787@.microsoft.com...[vbcol=seagreen]
> How do I create the default instance which is on the same server. I trie
d
> it with the sql server installation and only lets you do a named instance.
> When you say sql server installation tool you mean using the CD
> "AndyP" wrote:
>|||That is what I tried, I put in the SQL Server CD and when it got to piece th
e
default was grayed out and would not let me click it. We have the instance
presently called "srvsql02\mptp" the vendor needs as a default "srvsql02".
I
think 1) backup 2)uninstall 3) reinstall 4) restore each database starting
with master'?
"Tibor Karaszi" wrote:

> By installing a default instance, i.e., the same setup process as when you
installed the named
> instance.
>
> Then you already have a default instance.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Victoria Morrison" <VictoriaMorrison@.discussions.microsoft.com> wrote in
message
> news:4154AD5D-8E4A-420E-B013-9F0B94DFB787@.microsoft.com...
>
>|||> That is what I tried, I put in the SQL Server CD and when it got to piece theen">
> default was grayed out and would not let me click it.
That would happen if you already have a default instance. You can only have
one default instance.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Victoria Morrison" <VictoriaMorrison@.discussions.microsoft.com> wrote in me
ssage
news:80C97B24-AC86-426E-955E-F57E2DBB9476@.microsoft.com...[vbcol=seagreen]
> That is what I tried, I put in the SQL Server CD and when it got to piece
the
> default was grayed out and would not let me click it. We have the instan
ce
> presently called "srvsql02\mptp" the vendor needs as a default "srvsql02".
I
> think 1) backup 2)uninstall 3) reinstall 4) restore each database starting
> with master'?
> "Tibor Karaszi" wrote:
>|||Ok, so how do a rename it from srvsql01\mptp to simply srvsql01
"Tibor Karaszi" wrote:

> That would happen if you already have a default instance. You can only hav
e one default instance.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Victoria Morrison" <VictoriaMorrison@.discussions.microsoft.com> wrote in
message
> news:80C97B24-AC86-426E-955E-F57E2DBB9476@.microsoft.com...
>
>|||Victoria Morrison wrote:
> Ok, so how do a rename it from srvsql01\mptp to simply srvsql01
>
You can't rename an instance, but in your case it sounds like you
already has a default instance. If you haven't got one already, you
should be able to install one. Since you say that this option is greyed
out, it indicates that it's already there.
Have you tried to connect to just srvsql01 in your application,
Enterprise Manager or Query Analyser? If you have, do you then get an
error message of some kind?
Regards
Steen|||Thank all of you. It was grayed out on my Personnel copy, but when I tried
in on the test real enterprise version the options was not grayed out. And
thank for how to removed an instance that worked also. The only thing to
remember is that you have to put back the packs since the used the CD which
is at spack2. I did not know how else to get the install started. Now
I
am going to detach and attach the the user databases and then add the login
user and my jobs. Since that path do not match restoring master did not
work.
"Steen Persson (DK)" wrote:

> Victoria Morrison wrote:
> You can't rename an instance, but in your case it sounds like you
> already has a default instance. If you haven't got one already, you
> should be able to install one. Since you say that this option is greyed
> out, it indicates that it's already there.
> Have you tried to connect to just srvsql01 in your application,
> Enterprise Manager or Query Analyser? If you have, do you then get an
> error message of some kind?
> Regards
> Steen
>

How convert an named instance to a non named instance

Hello:
Our Application Vendor has now indicated that we need to have a non-named
instance because of their software and Analysis Services. How do we do this?A named instance is a distinct copy of all of the sql server binaries, data
files, services, etc. If you want to put your data on the default instance,
you will need to backup your databases on the named instnace and restore them
onto the default instance. You can then use the sql server installation tool
to remove the named instance.
--
AndyP,
Sr. Database Administrator,
MCDBA 2003
"Victoria Morrison" wrote:
> Hello:
> Our Application Vendor has now indicated that we need to have a non-named
> instance because of their software and Analysis Services. How do we do this?
>|||How do I create the default instance which is on the same server. I tried
it with the sql server installation and only lets you do a named instance.
When you say sql server installation tool you mean using the CD
"AndyP" wrote:
> A named instance is a distinct copy of all of the sql server binaries, data
> files, services, etc. If you want to put your data on the default instance,
> you will need to backup your databases on the named instnace and restore them
> onto the default instance. You can then use the sql server installation tool
> to remove the named instance.
> --
> AndyP,
> Sr. Database Administrator,
> MCDBA 2003
>
> "Victoria Morrison" wrote:
> > Hello:
> > Our Application Vendor has now indicated that we need to have a non-named
> > instance because of their software and Analysis Services. How do we do this?
> >|||> How do I create the default instance which is on the same server.
By installing a default instance, i.e., the same setup process as when you installed the named
instance.
> I tried
> it with the sql server installation and only lets you do a named instance.
Then you already have a default instance.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Victoria Morrison" <VictoriaMorrison@.discussions.microsoft.com> wrote in message
news:4154AD5D-8E4A-420E-B013-9F0B94DFB787@.microsoft.com...
> How do I create the default instance which is on the same server. I tried
> it with the sql server installation and only lets you do a named instance.
> When you say sql server installation tool you mean using the CD
> "AndyP" wrote:
>> A named instance is a distinct copy of all of the sql server binaries, data
>> files, services, etc. If you want to put your data on the default instance,
>> you will need to backup your databases on the named instnace and restore them
>> onto the default instance. You can then use the sql server installation tool
>> to remove the named instance.
>> --
>> AndyP,
>> Sr. Database Administrator,
>> MCDBA 2003
>>
>> "Victoria Morrison" wrote:
>> > Hello:
>> > Our Application Vendor has now indicated that we need to have a non-named
>> > instance because of their software and Analysis Services. How do we do this?
>> >|||That is what I tried, I put in the SQL Server CD and when it got to piece the
default was grayed out and would not let me click it. We have the instance
presently called "srvsql02\mptp" the vendor needs as a default "srvsql02". I
think 1) backup 2)uninstall 3) reinstall 4) restore each database starting
with master'?
"Tibor Karaszi" wrote:
> > How do I create the default instance which is on the same server.
> By installing a default instance, i.e., the same setup process as when you installed the named
> instance.
>
> > I tried
> > it with the sql server installation and only lets you do a named instance.
> Then you already have a default instance.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Victoria Morrison" <VictoriaMorrison@.discussions.microsoft.com> wrote in message
> news:4154AD5D-8E4A-420E-B013-9F0B94DFB787@.microsoft.com...
> > How do I create the default instance which is on the same server. I tried
> > it with the sql server installation and only lets you do a named instance.
> > When you say sql server installation tool you mean using the CD
> >
> > "AndyP" wrote:
> >
> >> A named instance is a distinct copy of all of the sql server binaries, data
> >> files, services, etc. If you want to put your data on the default instance,
> >> you will need to backup your databases on the named instnace and restore them
> >> onto the default instance. You can then use the sql server installation tool
> >> to remove the named instance.
> >>
> >> --
> >> AndyP,
> >> Sr. Database Administrator,
> >> MCDBA 2003
> >>
> >>
> >> "Victoria Morrison" wrote:
> >>
> >> > Hello:
> >> > Our Application Vendor has now indicated that we need to have a non-named
> >> > instance because of their software and Analysis Services. How do we do this?
> >> >
>
>|||> That is what I tried, I put in the SQL Server CD and when it got to piece the
> default was grayed out and would not let me click it.
That would happen if you already have a default instance. You can only have one default instance.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Victoria Morrison" <VictoriaMorrison@.discussions.microsoft.com> wrote in message
news:80C97B24-AC86-426E-955E-F57E2DBB9476@.microsoft.com...
> That is what I tried, I put in the SQL Server CD and when it got to piece the
> default was grayed out and would not let me click it. We have the instance
> presently called "srvsql02\mptp" the vendor needs as a default "srvsql02". I
> think 1) backup 2)uninstall 3) reinstall 4) restore each database starting
> with master'?
> "Tibor Karaszi" wrote:
>> > How do I create the default instance which is on the same server.
>> By installing a default instance, i.e., the same setup process as when you installed the named
>> instance.
>>
>> > I tried
>> > it with the sql server installation and only lets you do a named instance.
>> Then you already have a default instance.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Victoria Morrison" <VictoriaMorrison@.discussions.microsoft.com> wrote in message
>> news:4154AD5D-8E4A-420E-B013-9F0B94DFB787@.microsoft.com...
>> > How do I create the default instance which is on the same server. I tried
>> > it with the sql server installation and only lets you do a named instance.
>> > When you say sql server installation tool you mean using the CD
>> >
>> > "AndyP" wrote:
>> >
>> >> A named instance is a distinct copy of all of the sql server binaries, data
>> >> files, services, etc. If you want to put your data on the default instance,
>> >> you will need to backup your databases on the named instnace and restore them
>> >> onto the default instance. You can then use the sql server installation tool
>> >> to remove the named instance.
>> >>
>> >> --
>> >> AndyP,
>> >> Sr. Database Administrator,
>> >> MCDBA 2003
>> >>
>> >>
>> >> "Victoria Morrison" wrote:
>> >>
>> >> > Hello:
>> >> > Our Application Vendor has now indicated that we need to have a non-named
>> >> > instance because of their software and Analysis Services. How do we do this?
>> >> >
>>|||Ok, so how do a rename it from srvsql01\mptp to simply srvsql01
"Tibor Karaszi" wrote:
> > That is what I tried, I put in the SQL Server CD and when it got to piece the
> > default was grayed out and would not let me click it.
> That would happen if you already have a default instance. You can only have one default instance.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Victoria Morrison" <VictoriaMorrison@.discussions.microsoft.com> wrote in message
> news:80C97B24-AC86-426E-955E-F57E2DBB9476@.microsoft.com...
> > That is what I tried, I put in the SQL Server CD and when it got to piece the
> > default was grayed out and would not let me click it. We have the instance
> > presently called "srvsql02\mptp" the vendor needs as a default "srvsql02". I
> > think 1) backup 2)uninstall 3) reinstall 4) restore each database starting
> > with master'?
> >
> > "Tibor Karaszi" wrote:
> >
> >> > How do I create the default instance which is on the same server.
> >>
> >> By installing a default instance, i.e., the same setup process as when you installed the named
> >> instance.
> >>
> >>
> >> > I tried
> >> > it with the sql server installation and only lets you do a named instance.
> >>
> >> Then you already have a default instance.
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://www.solidqualitylearning.com/
> >>
> >>
> >> "Victoria Morrison" <VictoriaMorrison@.discussions.microsoft.com> wrote in message
> >> news:4154AD5D-8E4A-420E-B013-9F0B94DFB787@.microsoft.com...
> >> > How do I create the default instance which is on the same server. I tried
> >> > it with the sql server installation and only lets you do a named instance.
> >> > When you say sql server installation tool you mean using the CD
> >> >
> >> > "AndyP" wrote:
> >> >
> >> >> A named instance is a distinct copy of all of the sql server binaries, data
> >> >> files, services, etc. If you want to put your data on the default instance,
> >> >> you will need to backup your databases on the named instnace and restore them
> >> >> onto the default instance. You can then use the sql server installation tool
> >> >> to remove the named instance.
> >> >>
> >> >> --
> >> >> AndyP,
> >> >> Sr. Database Administrator,
> >> >> MCDBA 2003
> >> >>
> >> >>
> >> >> "Victoria Morrison" wrote:
> >> >>
> >> >> > Hello:
> >> >> > Our Application Vendor has now indicated that we need to have a non-named
> >> >> > instance because of their software and Analysis Services. How do we do this?
> >> >> >
> >>
> >>
> >>
>
>|||Victoria Morrison wrote:
> Ok, so how do a rename it from srvsql01\mptp to simply srvsql01
>
You can't rename an instance, but in your case it sounds like you
already has a default instance. If you haven't got one already, you
should be able to install one. Since you say that this option is greyed
out, it indicates that it's already there.
Have you tried to connect to just srvsql01 in your application,
Enterprise Manager or Query Analyser? If you have, do you then get an
error message of some kind?
Regards
Steen|||Thank all of you. It was grayed out on my Personnel copy, but when I tried
in on the test real enterprise version the options was not grayed out. And
thank for how to removed an instance that worked also. The only thing to
remember is that you have to put back the packs since the used the CD which
is at spack2. I did not know how else to get the install started. Now I
am going to detach and attach the the user databases and then add the login
user and my jobs. Since that path do not match restoring master did not
work.
"Steen Persson (DK)" wrote:
> Victoria Morrison wrote:
> > Ok, so how do a rename it from srvsql01\mptp to simply srvsql01
> >
> You can't rename an instance, but in your case it sounds like you
> already has a default instance. If you haven't got one already, you
> should be able to install one. Since you say that this option is greyed
> out, it indicates that it's already there.
> Have you tried to connect to just srvsql01 in your application,
> Enterprise Manager or Query Analyser? If you have, do you then get an
> error message of some kind?
> Regards
> Steen
>

How can you programatically export from Reporting Services via C# using VS.NET 2003 + SQL Server

Hi,

I'm not sure if this is possible, i've been looking round various sites for the past couple of days and have only found information on how to export reports in Reporting Services 2005.

What i am wanting to do is create a front end in which the user can specify where the report is to be exported to and the click a button which will export the report to that location into a pdf format.

ThanksYOu can simply get the report as a byte[] which is the return type of the Render method and save this as a filestream to the designated path.

HTH, jens Suessmeyer.

http://www.sqlserver2005.de|||Thanks for the reply. However, which object has the Render method?

I only have reporting services for visual studio 2003.|||

A simple one can be found here: http://support.microsoft.com/?kbid=875447

HTH, Jens SUessmeyer.

http://www.sqlserver2005.de

How can you programatically export from Reporting Services via C# using VS.NET 2003 + SQL Se

Hi,

I'm not sure if this is possible, i've been looking round various sites for the past couple of days and have only found information on how to export reports in Reporting Services 2005.

What i am wanting to do is create a front end in which the user can specify where the report is to be exported to and the click a button which will export the report to that location into a pdf format.

ThanksYOu can simply get the report as a byte[] which is the return type of the Render method and save this as a filestream to the designated path.

HTH, jens Suessmeyer.

http://www.sqlserver2005.de|||Thanks for the reply. However, which object has the Render method?

I only have reporting services for visual studio 2003.|||

A simple one can be found here: http://support.microsoft.com/?kbid=875447

HTH, Jens SUessmeyer.

http://www.sqlserver2005.de

How can we determine status of defined aggregates

We have a large Analysis Services 2005 installation with many partitions.

Is there an XMLA command or MDX query that will list all of the defined aggregations and their current status? We would like to run this command after an incremental update to make sure that we have not had an inadvertant dropping of aggregates.

Thanks,

Marty

I think you want the DISCOVER_PARTITION_STAT command. It should return a list of the aggs that are processed. If the list it returns is missing any, then you know

Code Snippet

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

<RequestType>DISCOVER_PARTITION_STAT</RequestType>

<Restrictions>

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

<DATABASE_NAME>Adventure Works DW</DATABASE_NAME>

<CUBE_NAME>Adventure Works</CUBE_NAME>

<MEASURE_GROUP_NAME>Internet Sales</MEASURE_GROUP_NAME>

<PARTITION_NAME>Internet_Sales_2003</PARTITION_NAME>

</RestrictionList>

</Restrictions>

<Properties>

</Properties>

</Discover>

That's a bit difficult to read because it returns XML, so I'd suggest you check out a stored proc Darren Gosbell wrote at http://www.codeplex.com/ASStoredProcedures/Wiki/View.aspx?title=XmlaDiscover

It returns a nice table which is easier to read. The equivalent command would be:

Code Snippet

CALL ASSP.Discover("DISCOVER_PARTITION_STAT","<DATABASE_NAME>Adventure Works DW</DATABASE_NAME><CUBE_NAME>Adventure Works</CUBE_NAME><MEASURE_GROUP_NAME>Internet Sales</MEASURE_GROUP_NAME><PARTITION_NAME>Internet_Sales_2003</PARTITION_NAME>")

If anyone else has other suggestions for better ways to quickly identify which aggs aren't processed, I'd like to hear it.

|||This worked great. Thanks.|||

We have a large number of aggregates that are showing a size of 0. What exactly does that mean? I've been unable to find any reference to aggregation_size.

<row>

<DATABASE_NAME>Phase 1</DATABASE_NAME>

<CUBE_NAME>Sales</CUBE_NAME>

<MEASURE_GROUP_NAME>Sales</MEASURE_GROUP_NAME>

<PARTITION_NAME>S200511</PARTITION_NAME>

<AGGREGATION_NAME>Aggregation 54</AGGREGATION_NAME>

<AGGREGATION_SIZE>0</AGGREGATION_SIZE>

</row>

Thanks,

Marty

|||

AGGREGATION_SIZE means the number of rows in that agg. For instance, if you have a partition for 2006 that has 1000 rows in the fact table then the XMLA query I mentioned above will give show you one <row> tag with no AGGREGATION_NAME showing AGGREGATION_SIZE of 1000. Then if you have a simple agg built on calendar month and nothing else, you should see another <row> showing an AGGREGATION_SIZE of 12 (meaning there are 12 rows in that agg, one for each month). Make sense?

Because you're seeing sizes of 0, I assume that means that there are no rows in your fact table for that partition. Can you confirm this?

How can we determine status of defined aggregates

We have a large Analysis Services 2005 installation with many partitions.

Is there an XMLA command or MDX query that will list all of the defined aggregations and their current status? We would like to run this command after an incremental update to make sure that we have not had an inadvertant dropping of aggregates.

Thanks,

Marty

I think you want the DISCOVER_PARTITION_STAT command. It should return a list of the aggs that are processed. If the list it returns is missing any, then you know

Code Snippet

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

<RequestType>DISCOVER_PARTITION_STAT</RequestType>

<Restrictions>

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

<DATABASE_NAME>Adventure Works DW</DATABASE_NAME>

<CUBE_NAME>Adventure Works</CUBE_NAME>

<MEASURE_GROUP_NAME>Internet Sales</MEASURE_GROUP_NAME>

<PARTITION_NAME>Internet_Sales_2003</PARTITION_NAME>

</RestrictionList>

</Restrictions>

<Properties>

</Properties>

</Discover>

That's a bit difficult to read because it returns XML, so I'd suggest you check out a stored proc Darren Gosbell wrote at http://www.codeplex.com/ASStoredProcedures/Wiki/View.aspx?title=XmlaDiscover

It returns a nice table which is easier to read. The equivalent command would be:

Code Snippet

CALL ASSP.Discover("DISCOVER_PARTITION_STAT","<DATABASE_NAME>Adventure Works DW</DATABASE_NAME><CUBE_NAME>Adventure Works</CUBE_NAME><MEASURE_GROUP_NAME>Internet Sales</MEASURE_GROUP_NAME><PARTITION_NAME>Internet_Sales_2003</PARTITION_NAME>")

If anyone else has other suggestions for better ways to quickly identify which aggs aren't processed, I'd like to hear it.

|||This worked great. Thanks.|||

We have a large number of aggregates that are showing a size of 0. What exactly does that mean? I've been unable to find any reference to aggregation_size.

<row>

<DATABASE_NAME>Phase 1</DATABASE_NAME>

<CUBE_NAME>Sales</CUBE_NAME>

<MEASURE_GROUP_NAME>Sales</MEASURE_GROUP_NAME>

<PARTITION_NAME>S200511</PARTITION_NAME>

<AGGREGATION_NAME>Aggregation 54</AGGREGATION_NAME>

<AGGREGATION_SIZE>0</AGGREGATION_SIZE>

</row>

Thanks,

Marty

|||

AGGREGATION_SIZE means the number of rows in that agg. For instance, if you have a partition for 2006 that has 1000 rows in the fact table then the XMLA query I mentioned above will give show you one <row> tag with no AGGREGATION_NAME showing AGGREGATION_SIZE of 1000. Then if you have a simple agg built on calendar month and nothing else, you should see another <row> showing an AGGREGATION_SIZE of 12 (meaning there are 12 rows in that agg, one for each month). Make sense?

Because you're seeing sizes of 0, I assume that means that there are no rows in your fact table for that partition. Can you confirm this?

How can use 2 instance of SSRS?(WMI error code 80040219)

When I try to connect through SQL Server Management Studio,
the browser in the connect dialog can see the Reporting Services 2005 instance,
but when I try to connect, I get an error dialog with the message,
"An Unknown error has occured in WMI provider. error code 80040219."

it gives me no options for which instance to access
and connects to the default instance.
How do I connect to a 2nd named instance of SSRS via Management Studio?

i must use 2 different instance. (ex : 1) default instance 2) named instance) on the same physical server.
physical server os is cluster.
so i cant't remove one instance.

How can use 2 instance of SSRS via Management Studio?

Hi Jisungmon,

If you have installed on your machine named <Machine_Name> a second instance of Reporting Services named <Named_Instance>, you can connect to it using SQL Management Studio by following these steps:

- launch SQL Management Studio

- for Server type select: Reporting Services

- for Server name type: <Machine_Name>\<Named_Instance> ; or you can select <Browse for more ...>, expand the Reporting Services node, and select the same name from the list provided to you, then press OK

- for Authentication select what you prefer and eventually specify the parameters

- press Connect

Please let me know whether this is working for you.

Mihaela

This posting is provided "AS IS" with no warranties, and confers no rights.

how can i view the design of the ssis package after migration

OK, I was able to successfully migrate all of my DTS packages to SSIS, for SQL 2005. I can log into intergration services and see my packages listed under:

servername --> stored packages --> msdb. Now my question is, how can I open these packages, not run them open them in a design mode like you can in SQL 2000, you can double click on the package name and view the design of the package. how can i do that now that I have them in SQL 2005?

Right click on the package and select "Export..."

Chose File System as the destination and once done filling in the fields, you'll be able to open that package up in Business Intelligence Developer Studio.|||

Thanks, I tell ya, I should have walked away from this project when it was brought to me.

Coming from coding .NET apps for the pass 6 years, this SQL style work is a whole new world to me.

|||It gives it a .dstx extension, is that correct?|||Yep, it sure is correct.|||

ok, thanks

How can I use SQL reporting Services to get a dynamic dataset from another web service as my rep

I found out the data I need for my SQL Report is already defined in a dynamic dataset on another web service. Is there a way to use web services to call another web service to get the dataset I need to generate a report? Examples would help if you have any, thanks for looking

I think this technical article may help you.

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

Simone

|||Thank you very much, this is almost exactly what I was looking for, not 100% but closer then I have been for weeks now. I just need to try and figure out now if I can send a parameter to a web service now to get the result set I need. Again, thanks...

How can I use Reporting Services on ASP.NET Pages?

Hi all,

Could you tell the correct way to use Reporting Services on ASP.NET pages?

Now I wanna create an ASP.NET page to show some reports, and I hope to control all logic by program, so I need know the API between ASP.NET and Reporting Services.

For example, I wanna pass some data to Reporting Services via API, and then Reporting Services handle the data and return reports to me, and then I can show the reports on my web pages.

Please tell me how?

Hi Xinwen.

You can use the Report control and give hime the link to your report so it will show in side the control.

You don't interact with the report like you described - You can interact with the report with PARAMETERS passing through the URL.

So whan you give the report control the report url you can add to that url parameters that the report should get (like person id or project number) and also to change things in the report (like open it in diffrent ways, change the zoom, automaticlly export to pdf and so on) with the url command parameters.

for exm:
ttp://reportserver/myreportfolder/reportname.rdl?rc:format=PDF will export it to pdf.

search google about "report service url parameters"

good luck,
Roy.|||Thanks Roy!

How can I use Reporting Services on ASP.NET Pages?

Hi all,

Could you tell the correct way to use Reporting Services on ASP.NET pages?

Now I wanna create an ASP.NET page to show some reports, and I hope to control all logic by program, so I need know the API between ASP.NET and Reporting Services.

For example, I wanna pass some data to Reporting Services via API, and then Reporting Services handle the data and return reports to me, and then I can show the reports on my web pages.

Please tell me how?

Hi Xinwen.

You can use the Report control and give hime the link to your report so it will show in side the control.

You don't interact with the report like you described - You can interact with the report with PARAMETERS passing through the URL.

So whan you give the report control the report url you can add to that url parameters that the report should get (like person id or project number) and also to change things in the report (like open it in diffrent ways, change the zoom, automaticlly export to pdf and so on) with the url command parameters.

for exm:
ttp://reportserver/myreportfolder/reportname.rdl?rc:format=PDF will export it to pdf.

search google about "report service url parameters"

good luck,
Roy.
|||Thanks Roy!|||

Hi,

I tried with this but instead of prompting to save Excel File,it is displaying the report

http://localhost/Reports/Pages/Report.aspx?ItemPath=NameOfReport&rs:Command=Renderl&rs:Format=Excel

-Thanks,

Digant Desai

|||

In Visual studio 2005,there is a report veiwer contrlo to view your RDL reports.

U need to link report viewer control to corresponding report in Reporting service project.This u can do

by setting Propertis of report viewer.

You can also pass parameters form ASP page to rdl file.

using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;

public partial class Default2 : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
this.ReportViewer2.Visible = false;
}
protected void Button1_Click(object sender, EventArgs e)
{

this.ReportViewer2.Visible = true;
this.ReportViewer2.ServerReport.ReportServerUrl = new System.Uri("http://localhost/ReportServer");

string strReport = "/ReportProject14/Report1";

this.ReportViewer2.ServerReport.ReportPath = strReport;

Microsoft.Reporting.WebForms.ReportParameter[] RptParameters =
new Microsoft.Reporting.WebForms.ReportParameter[3];

string strComp = this.DropDownList1.SelectedItem.Value;
RptParameters[0] =
new Microsoft.Reporting.WebForms.ReportParameter("INTCOMPANYKEY", strComp);

string strFacility = this.DropDownList3.SelectedItem.Value;
RptParameters[1] =
new Microsoft.Reporting.WebForms.ReportParameter("INTFACILITYKEY", strFacility);

string strActivity = this.DropDownList2.SelectedItem.Value;
RptParameters[2] =
new Microsoft.Reporting.WebForms.ReportParameter("Activity", strActivity);

this.ReportViewer2.ServerReport.SetParameters(RptParameters);
this.ReportViewer2.ServerReport.Refresh();
}
}

How can I tell which version is installed?

One of the server admins recently installed SQL Server 2005 on a new Itamium server. I am trying to set the parameter on the services to indicate they are running on a 64 bit machine, however the changes are not taking...yes I'm stopping and restarting the services. I'm wondering if did not install the right version. Does anyone know how I can tell if the IA 64 version was installed?

I don't think you could install version other than ia64 on a Itanium machine.

If you could start the server, try following sql, it should return a version string which indicate whether is x86/amd64/ia64.

select @.@.version

|||

Duh! It never occurred to me that it might list the processor. Thanks!

Oh...I wish I had a brain!