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 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 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 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 same query be way slower on an identical database?

Hi,
I have SQL 2000 server on a P4 box. There are 8 - 10 databases total
and they are all backup/reporting purposes. So there are no other
users.
I have 2 identical databases with identical tables and identical
stored procedures. Only the data is different. Let me name them DB1
and DB2.
I execute the SP1 on DB1, which has more records than DB2, and it
gives me the results in 2 - 5 minutes.
I execute the SP2 (which is the exact copy of SP1) on DB2, which has
less records than the DB1, and it never gives me the result. After
hours it throws an error that is not specific.
I put indexes, tried to tune it etc. But no use. I still add some
records on both and run the query on Query Analyzer, and get results
for DB1 but not for DB2.
Why would it do this? Is this a database issue or the server issue you
think?
Thanks in advance.
- Denwell... they're not really identical if the rows are different... how have
you verified that schema is the same? Are you just 'sure' or have you used a
tool like DBCompare from redgate?
Also... have you looked at the estimate plans? Do they come back the same?
If not... you might have a statistics difference between the DB's... even if
schema is the same it's certainly possible that auto created stats could be
differernt between the db's...
hope this helps a bit,
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"DenoxiS" <google@.deniznet.com> wrote in message
news:d2478899.0402061716.7ffce2d2@.posting.google.com...
> Hi,
> I have SQL 2000 server on a P4 box. There are 8 - 10 databases total
> and they are all backup/reporting purposes. So there are no other
> users.
> I have 2 identical databases with identical tables and identical
> stored procedures. Only the data is different. Let me name them DB1
> and DB2.
> I execute the SP1 on DB1, which has more records than DB2, and it
> gives me the results in 2 - 5 minutes.
> I execute the SP2 (which is the exact copy of SP1) on DB2, which has
> less records than the DB1, and it never gives me the result. After
> hours it throws an error that is not specific.
> I put indexes, tried to tune it etc. But no use. I still add some
> records on both and run the query on Query Analyzer, and get results
> for DB1 but not for DB2.
> Why would it do this? Is this a database issue or the server issue you
> think?
> Thanks in advance.
> - Den|||If you are performing any kind of iterative calculation in the stored
procedure it could be that there is data on DB2 that is causing an infinite
loop. If the data on DB1 is "good" it would not have this problem. I have
run into this most often when handling date calculations.
"Brian Moran" <brian@.solidqualitylearning.com> wrote in message
news:u51B%23TS7DHA.3420@.TK2MSFTNGP11.phx.gbl...
> well... they're not really identical if the rows are different... how have
> you verified that schema is the same? Are you just 'sure' or have you used
a
> tool like DBCompare from redgate?
> Also... have you looked at the estimate plans? Do they come back the same?
> If not... you might have a statistics difference between the DB's... even
if
> schema is the same it's certainly possible that auto created stats could
be
> differernt between the db's...
> hope this helps a bit,
> --
> Brian Moran
> Principal Mentor
> Solid Quality Learning
> SQL Server MVP
> http://www.solidqualitylearning.com
>
> "DenoxiS" <google@.deniznet.com> wrote in message
> news:d2478899.0402061716.7ffce2d2@.posting.google.com...
>|||Take a look at the selectivity of each set of tables. Is the data very simi
lar ie is the distribution of data similar between the tables? Taking a bet
ter look at the generated query plans will provide you with the answers you
are seeking.

How can same query be way slower on an identical database?

Hi,
I have SQL 2000 server on a P4 box. There are 8 - 10 databases total
and they are all backup/reporting purposes. So there are no other
users.
I have 2 identical databases with identical tables and identical
stored procedures. Only the data is different. Let me name them DB1
and DB2.
I execute the SP1 on DB1, which has more records than DB2, and it
gives me the results in 2 - 5 minutes.
I execute the SP2 (which is the exact copy of SP1) on DB2, which has
less records than the DB1, and it never gives me the result. After
hours it throws an error that is not specific.
I put indexes, tried to tune it etc. But no use. I still add some
records on both and run the query on Query Analyzer, and get results
for DB1 but not for DB2.
Why would it do this? Is this a database issue or the server issue you
think?
Thanks in advance.
- Denwell... they're not really identical if the rows are different... how have
you verified that schema is the same? Are you just 'sure' or have you used a
tool like DBCompare from redgate?
Also... have you looked at the estimate plans? Do they come back the same?
If not... you might have a statistics difference between the DB's... even if
schema is the same it's certainly possible that auto created stats could be
differernt between the db's...
hope this helps a bit,
--
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"DenoxiS" <google@.deniznet.com> wrote in message
news:d2478899.0402061716.7ffce2d2@.posting.google.com...
> Hi,
> I have SQL 2000 server on a P4 box. There are 8 - 10 databases total
> and they are all backup/reporting purposes. So there are no other
> users.
> I have 2 identical databases with identical tables and identical
> stored procedures. Only the data is different. Let me name them DB1
> and DB2.
> I execute the SP1 on DB1, which has more records than DB2, and it
> gives me the results in 2 - 5 minutes.
> I execute the SP2 (which is the exact copy of SP1) on DB2, which has
> less records than the DB1, and it never gives me the result. After
> hours it throws an error that is not specific.
> I put indexes, tried to tune it etc. But no use. I still add some
> records on both and run the query on Query Analyzer, and get results
> for DB1 but not for DB2.
> Why would it do this? Is this a database issue or the server issue you
> think?
> Thanks in advance.
> - Den|||If you are performing any kind of iterative calculation in the stored
procedure it could be that there is data on DB2 that is causing an infinite
loop. If the data on DB1 is "good" it would not have this problem. I have
run into this most often when handling date calculations.
"Brian Moran" <brian@.solidqualitylearning.com> wrote in message
news:u51B%23TS7DHA.3420@.TK2MSFTNGP11.phx.gbl...
> well... they're not really identical if the rows are different... how have
> you verified that schema is the same? Are you just 'sure' or have you used
a
> tool like DBCompare from redgate?
> Also... have you looked at the estimate plans? Do they come back the same?
> If not... you might have a statistics difference between the DB's... even
if
> schema is the same it's certainly possible that auto created stats could
be
> differernt between the db's...
> hope this helps a bit,
> --
> Brian Moran
> Principal Mentor
> Solid Quality Learning
> SQL Server MVP
> http://www.solidqualitylearning.com
>
> "DenoxiS" <google@.deniznet.com> wrote in message
> news:d2478899.0402061716.7ffce2d2@.posting.google.com...
> > Hi,
> >
> > I have SQL 2000 server on a P4 box. There are 8 - 10 databases total
> > and they are all backup/reporting purposes. So there are no other
> > users.
> >
> > I have 2 identical databases with identical tables and identical
> > stored procedures. Only the data is different. Let me name them DB1
> > and DB2.
> >
> > I execute the SP1 on DB1, which has more records than DB2, and it
> > gives me the results in 2 - 5 minutes.
> >
> > I execute the SP2 (which is the exact copy of SP1) on DB2, which has
> > less records than the DB1, and it never gives me the result. After
> > hours it throws an error that is not specific.
> >
> > I put indexes, tried to tune it etc. But no use. I still add some
> > records on both and run the query on Query Analyzer, and get results
> > for DB1 but not for DB2.
> >
> > Why would it do this? Is this a database issue or the server issue you
> > think?
> >
> > Thanks in advance.
> >
> > - Den
>|||Take a look at the selectivity of each set of tables. Is the data very similar ie is the distribution of data similar between the tables? Taking a better look at the generated query plans will provide you with the answers you are seeking.

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();
}
}