Adjusting reportwidth dynamically.. please help me

hi,

I am new to reporting services.I am using sqlserver Reporting services to generate Report.I have designed a static RDL file based on a view which contains 40 columns..i included all the 40 column in rdl file..i have used hidden field expressions for hiding columns..Now at run time if i select three columns for generating report, The remaining hidden column spaces are still there in the report ... The report width is not adjusting to the selected column width... how to eliminate the hidden column spaces in the report.Please help me..it is really urgent for me..

Thanks in advance...

You can change the width of the ReportViewer in the Load() event like so...

ReportViewer1.Width=500

I think if you try to do it in another event it has no effect becasue the size has already been rendered.

Good luck.

|||

try to set the width = "auto"

Adjusting Report width automatically

Hi All,

We are having a table report which has set of columns where the visibility is made false based on report parameters. It works fine and the table automatically shrinks. But, the page width remains as early and has lot of blank space in it. It also causes printer to print many blank pages.

Is there a way to control pagewidth dynamically? Thought of adding a custom assembly to control page width - but how to access the page that is getting displayed. "this" will refer to the the assembly class instead of report as such.

Any help is highly appreciated.

Regards, kart

Sizing is one of the few aspects of RS that cannot be controlled using expressions. Column widths are fixed and only row height supports auto sizing.

Paging is one of the last aspects of the rendering to be determined and it's also specific (and petentially different) to each rendering format. So HTML, Excel and PDF will all treat paging their own way. Hence I don't the possibility of there being an object that exposes a "CurrentPage" property in order to control it's properties at runtime. Maybe in future version MS will exposes some events raised by the renderers that you could hook into, but to my knowledge that's not available right now.

Hence the only way I see of achieveing what you suggest is to write your own rendering extension - which is not trivial - that handles the sizing of each page.

|||

You can try to change all the columns width to zero. (its not a good solution for export to excel but only to HTML)

Adjusting Report width automatically

Hi All,

We are having a table report which has set of columns where the visibility is made false based on report parameters. It works fine and the table automatically shrinks. But, the page width remains as early and has lot of blank space in it. It also causes printer to print many blank pages.

Is there a way to control pagewidth dynamically? Thought of adding a custom assembly to control page width - but how to access the page that is getting displayed. "this" will refer to the the assembly class instead of report as such.

Any help is highly appreciated.

Regards, kart

Sizing is one of the few aspects of RS that cannot be controlled using expressions. Column widths are fixed and only row height supports auto sizing.

Paging is one of the last aspects of the rendering to be determined and it's also specific (and petentially different) to each rendering format. So HTML, Excel and PDF will all treat paging their own way. Hence I don't the possibility of there being an object that exposes a "CurrentPage" property in order to control it's properties at runtime. Maybe in future version MS will exposes some events raised by the renderers that you could hook into, but to my knowledge that's not available right now.

Hence the only way I see of achieveing what you suggest is to write your own rendering extension - which is not trivial - that handles the sizing of each page.

|||

You can try to change all the columns width to zero. (its not a good solution for export to excel but only to HTML)

Ad-hoc reporting

Is the ad-hoc reporting available for SQL Server 2005 reporting
services a web based environment or is it an application that has to be
installed?
Thanks
Robertits a clickonce application.
this required that the user have the .Net Framework 2.0 installed locally.
then the reportbuilder application is downloaded automatically without any
install process.
but its not a webbased application. its a client/server application which
use webservices calls to the server to execute andrender queries.
"rhoenig" <rhoenig@.charter.net> wrote in message
news:1158956048.392781.78140@.m7g2000cwm.googlegroups.com...
> Is the ad-hoc reporting available for SQL Server 2005 reporting
> services a web based environment or is it an application that has to be
> installed?
> Thanks
> Robert
>

Ad-hoc Report Generation Based on the Input Query!

Hi all,

I want to generate the ad-hoc report using SQL Reporting Services 2000 or SQL Reporting Services 2005. But the Database Server will be SQL Server 2000. So, Can I send the query at run-time and then attach the data source to the actual report. In this case there will not be any columns etc. at design time with the value field mentioned in it. I know this is not the requirement for which this report tool is developed for. But still if this is possible through some other way round then pls. let me know. Actually we want one generic report which will satisfy all the tables, with diff. conditions mentioned in the select clause and where clause. So, is this possible with this reporting tool? Is there any way out where we want to come up with only one such report by manipulating the datasource of the report at run-time? Pls. let me know.

Thanks & Regards,

Dipali.

There may be a better way to do this in 2005 but the way I acheived dynamic columns in 2000 was to have a maximum number of columns (say 20) that take the value Column01, Column02....Column20 and make sure your data source returns the values under those names. Then you can also pass in the column names as a parameter. That's the only way I could come up with for producing a completely dynamic report.sql

ad-hoc query printing

Based on the tutorial in
http://msdn2.microsoft.com/en-us/library/ms170667.aspx
We have a prototype that works with our custom data extension in Reporting
Services 2005 such that we can generate a report based on a given query text.
Right now in order to view a report, we always publish the generated report
to a report server instance. Our question is whether we can do it without
using report server.
For example, does ReportViewer control (the local processing mode) help?
Our goal is to create a temporary report on the fly given a query such that
the user can print or export the report, which means that this temporary
report will be discarded as soon as the user is done with it. We would like
to know if this is possible.
Thanks in advance!Hi,
Welcome to use MSDN Managed Newsgroup!
From your descriptions, I understood you would like to whether it is
possible to create a temporary report for print only. If I have
misunderstood your concern, please feel free to point it out.
Unfortunately, Reporting Services do not have such funtionality to do so. I
wonder why do you need a Report on fly?
I have an interesting experience before that might give you some ideas. The
data source in my project was a remote linked server that need a lot of
time executing the complex query (cost about 5 minutes each time). When
perform the Email-Delivery Subscription to 10 or more different users, the
query will be executed 10 or more 5-minutes (around for 1 hours to complete
the email delivery subscription), which is obviously unacceptedable. Hence,
I will use DTS to transfer the data from linked server to local server
(cost 5 minutes) and then do the email-delivery subscription on local
tables, which is pretty cheap.
Hope this helps.
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi,
Oops, sorry that I think I missed something
Here is an interesting article that shows VS 2005's new ReportViewer
control and its built-in Report Designer enable smart-client and Web page
designers to lay out, format, embed, export, and print interactive reports
without running a SQL Server Report Server.
Build Client-Side Reports Easily
http://msforums.ph/forums/91285/ShowPost.aspx
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Thanks for your help. I found this walkthrough sample (similar to the one
you quoted below) from MSDN2 as well
http://msdn2.microsoft.com/en-us/library/ms251724.aspx
This is useful tutorial for us. However, I need to generate such report on
the fly ... that is, the dataset and datatable etc will be generated at
run-time. Is this even possible?
In addition, is there any tutorial on generating RDLC programmatically?
Thanks
Jenny
"Michael Cheng [MSFT]" wrote:
> Hi,
> Oops, sorry that I think I missed something
> Here is an interesting article that shows VS 2005's new ReportViewer
> control and its built-in Report Designer enable smart-client and Web page
> designers to lay out, format, embed, export, and print interactive reports
> without running a SQL Server Report Server.
> Build Client-Side Reports Easily
> http://msforums.ph/forums/91285/ShowPost.aspx
>
> Sincerely yours,
> Michael Cheng
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> =====================================================> This posting is provided "AS IS" with no warranties, and confers no rights.
>
>|||Actually, I just found out about the LocalReport class from
http://msdn2.microsoft.com/en-us/library/microsoft.reporting.winforms.localreport.aspx
Do you know of any example of how to use it? Say I already have a dataset
or a datatable or a collection of business objects (can't seem to find more
about what exactly a business object is).
Thanks in advance!
"yinjennytam@.newsgroup.nospam" wrote:
> Thanks for your help. I found this walkthrough sample (similar to the one
> you quoted below) from MSDN2 as well
> http://msdn2.microsoft.com/en-us/library/ms251724.aspx
> This is useful tutorial for us. However, I need to generate such report on
> the fly ... that is, the dataset and datatable etc will be generated at
> run-time. Is this even possible?
> In addition, is there any tutorial on generating RDLC programmatically?
> Thanks
> Jenny
>
>
> "Michael Cheng [MSFT]" wrote:
> > Hi,
> >
> > Oops, sorry that I think I missed something
> >
> > Here is an interesting article that shows VS 2005's new ReportViewer
> > control and its built-in Report Designer enable smart-client and Web page
> > designers to lay out, format, embed, export, and print interactive reports
> > without running a SQL Server Report Server.
> >
> > Build Client-Side Reports Easily
> > http://msforums.ph/forums/91285/ShowPost.aspx
> >
> >
> >
> > Sincerely yours,
> >
> > Michael Cheng
> > Microsoft Online Partner Support
> >
> > When responding to posts, please "Reply to Group" via your newsreader so
> > that others may learn and benefit from your issue.
> > =====================================================> > This posting is provided "AS IS" with no warranties, and confers no rights.
> >
> >
> >|||Hi,
To create a RDLC file, you may refer the article below
Creating Client Report Definition (.rdlc) Files
http://msdn2.microsoft.com/en-us/library/ms252067(en-US,VS.80).aspx
Converting RDL and RDLC Files
http://msdn2.microsoft.com/en-us/library/ms252109(en-US,VS.80).aspx
If you encounter any difficulties in the walkthrough, please let me know
the detail step you meet the error.
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Thanks Michael for the links. I did take a look at both and did try some
walkthrough tutorials. They are great and now I understand more about
ReportViewer controls in general.
However, my problem is still not resolved. I guess I can rephrase it a bit
to let you understand it more:
(1) I need to generate the RDLC file programmatically because I don't know
the report fields at design time
(2) The same applies to the report data source, and I have a way to run a
query to get a System.Data.DataTable object which I can use as the report
data source, but again this depends on the search query, which I don't know
until run-time
I have attempted to modify the walkthrough from
http://msdn2.microsoft.com/en-us/library/ms251784(en-US,VS.80).aspx
But instead of creating the rdlc file using the UI, I created the RDLC file
based on the tutorial from
http://msdn2.microsoft.com/en-us/library/ms170667(en-US,SQL.90).aspx
Again, I just renamed this file using the *.rdlc extension instead of *.rdl
and save it somewhere in my local system.
As I mentioned above, given a query, I can create a System.Data.DataTable on
the fly and modified the sample code such that I commented out the followings
from Form1.Designer.cs:
//this.reportViewer1.LocalReport.DataSources.Add(reportDataSource1);
//this.reportViewer1.LocalReport.ReportEmbeddedResource ="BusinessObject.Report1.rdlc";
and added the following lines to Form1_Load() just before the line
this.reportViewer1.RefreshReport();
this.reportViewer1.LocalReport.DataSources.Add(new
ReportDataSource("Sales", datatable));
this.reportViewer1.LocalReport.ReportPath =@."C:\Development\trials\BusinessObject\BusinessObject\mysales.rdlc";
When I ran the app, I got the following error:
"A data source instance has not been supplied for the data source 'DataSet1'"
First of all, am I in the wrong track? Can I actually programmatically
create an RDL file and simply renamed it as RDLC. What else do I need to do
programmatically (not through adding it to the project as indicated by the
walkthrough)?
Second of all, what am I missing? Is it possible to supply the DataTable
(report data source) programmatically?
Thanks a lot for reading my long post.
Jenny
"Michael Cheng [MSFT]" wrote:
> Hi,
> To create a RDLC file, you may refer the article below
> Creating Client Report Definition (.rdlc) Files
> http://msdn2.microsoft.com/en-us/library/ms252067(en-US,VS.80).aspx
> Converting RDL and RDLC Files
> http://msdn2.microsoft.com/en-us/library/ms252109(en-US,VS.80).aspx
> If you encounter any difficulties in the walkthrough, please let me know
> the detail step you meet the error.
>
> Sincerely yours,
> Michael Cheng
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> =====================================================> This posting is provided "AS IS" with no warranties, and confers no rights.
>|||Actually I've figured it out myself! :-)
When I added a new ReportDataSource object, I should have named it
'DataSet1' to associate that with my RDLC file.
Jenny
"yinjennytam@.newsgroup.nospam" wrote:
> Thanks Michael for the links. I did take a look at both and did try some
> walkthrough tutorials. They are great and now I understand more about
> ReportViewer controls in general.
> However, my problem is still not resolved. I guess I can rephrase it a bit
> to let you understand it more:
> (1) I need to generate the RDLC file programmatically because I don't know
> the report fields at design time
> (2) The same applies to the report data source, and I have a way to run a
> query to get a System.Data.DataTable object which I can use as the report
> data source, but again this depends on the search query, which I don't know
> until run-time
> I have attempted to modify the walkthrough from
> http://msdn2.microsoft.com/en-us/library/ms251784(en-US,VS.80).aspx
> But instead of creating the rdlc file using the UI, I created the RDLC file
> based on the tutorial from
> http://msdn2.microsoft.com/en-us/library/ms170667(en-US,SQL.90).aspx
> Again, I just renamed this file using the *.rdlc extension instead of *.rdl
> and save it somewhere in my local system.
> As I mentioned above, given a query, I can create a System.Data.DataTable on
> the fly and modified the sample code such that I commented out the followings
> from Form1.Designer.cs:
> //this.reportViewer1.LocalReport.DataSources.Add(reportDataSource1);
> //this.reportViewer1.LocalReport.ReportEmbeddedResource => "BusinessObject.Report1.rdlc";
> and added the following lines to Form1_Load() just before the line
> this.reportViewer1.RefreshReport();
> this.reportViewer1.LocalReport.DataSources.Add(new
> ReportDataSource("Sales", datatable));
> this.reportViewer1.LocalReport.ReportPath => @."C:\Development\trials\BusinessObject\BusinessObject\mysales.rdlc";
>
> When I ran the app, I got the following error:
> "A data source instance has not been supplied for the data source 'DataSet1'"
>
> First of all, am I in the wrong track? Can I actually programmatically
> create an RDL file and simply renamed it as RDLC. What else do I need to do
> programmatically (not through adding it to the project as indicated by the
> walkthrough)?
> Second of all, what am I missing? Is it possible to supply the DataTable
> (report data source) programmatically?
>
> Thanks a lot for reading my long post.
> Jenny
>
>
>
> "Michael Cheng [MSFT]" wrote:
> > Hi,
> >
> > To create a RDLC file, you may refer the article below
> >
> > Creating Client Report Definition (.rdlc) Files
> > http://msdn2.microsoft.com/en-us/library/ms252067(en-US,VS.80).aspx
> >
> > Converting RDL and RDLC Files
> > http://msdn2.microsoft.com/en-us/library/ms252109(en-US,VS.80).aspx
> >
> > If you encounter any difficulties in the walkthrough, please let me know
> > the detail step you meet the error.
> >
> >
> > Sincerely yours,
> >
> > Michael Cheng
> > Microsoft Online Partner Support
> >
> > When responding to posts, please "Reply to Group" via your newsreader so
> > that others may learn and benefit from your issue.
> > =====================================================> > This posting is provided "AS IS" with no warranties, and confers no rights.
> >
> >|||Hi Jenny,
Thanks for your prompt update and it's great to hear you have resovled it
now!! And thanks so much for sharing this information with us.
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================This posting is provided "AS IS" with no warranties, and confers no rights.

Addtion / Sum of Parameter Array Values - SSRS

Hi All,
I want to Sum instead of Join Parameter arrays selected values and use
it within the report to display data based on total sum of all the
values selected. Join concatanates them with comma delimited values
but i want sum of all those values.
Example as below
Value DisplayText
1 - Current Month
2 - Previoius Year
4 - Half Year
8 - Year to date
so if
current month and half year
are selected then i want to read 1 + 4 = 5 and not 1,4 as currently i
get by join function.
Code = SUM(Parameters!dropdownbox.value)
Result = #Error
Code = Join(Parameters!dropdownbox.value,",")
Result = "1,4"
Code = SUM(Join(Parameters!dropdownbox.value,","))
Result = #Error
Code = Parameters!dropdownbox.count
Result = 4 (it gives me length of array)
Code = ?
Result = 5 (This is the result is want...but can make it work)
Any help greatly appreciated
Regards
Nirav Lulla
Yotta ConsultingOn May 14, 1:02 pm, nlulla <nirav.lu...@.gmail.com> wrote:
> Hi All,
> I want to Sum instead ofJoinParameterarrays selected values and use
> it within the report to display data based on total sum of all the
> values selected.Joinconcatanates them with comma delimited values
> but i want sum of all those values.
> Example as below
> Value DisplayText
> 1 - Current Month
> 2 - Previoius Year
> 4 - Half Year
> 8 - Year to date
> so if
> current month and half year
> are selected then i want to read 1 + 4 = 5 and not 1,4 as currently i
> get byjoinfunction.
> Code = SUM(Parameters!dropdownbox.value)
> Result = #Error
> Code =Join(Parameters!dropdownbox.value,",")
> Result = "1,4"
> Code = SUM(Join(Parameters!dropdownbox.value,","))
> Result = #Error
> Code = Parameters!dropdownbox.count
> Result = 4 (it gives me length of array)
> Code = ?
> Result = 5 (This is the result is want...but can make it work)
> Any help greatly appreciated
> Regards
> Nirav Lulla
> Yotta Consulting
I'm not sure how many items are in your dropdown list but this will
work if you only have a few...
=CInt(Parameters!site.Value(0)) + CInt(Parameters!site.Value(1))|||Hi James,
Thanks for posting your reply, you suggesstio would only work if i
have set fixed length of options, but i don't know how this is going
to work in case of unknown number of options.
For now , I have Created a .net class file DLL with following code and
referenced the dll in my .rdl file, it works for me, but would be good
if it can be done within SSRS itself. Any more suggesstions welcome
Nirav Lulla
Yotta Consulting
Public Class ClsCommon
Const bDisplayColumn As Boolean = False
Const bHideColumn As Boolean = True
Shared Function SumOfArrayString(ByVal ArrayString As String) As
Integer
Dim arylist As System.Array
Dim sum As Integer
Try
arylist = ArrayString.Split(",")
For Each item As Integer In arylist
sum += CInt(item)
Next
Catch ex As Exception
Return -1
Finally
Return sum
End Try
End Function
End Class

addlogin into linked server

Hi,
I need to develop a stored procedure to add or drop SQL server logins into
SQL servers based on some logics users give to me. My stored procedure is on
one server. I need to add logins to other servers through linked server.
Does anybody know how to run sp_addlogin to add SQL logins into linked serve
r?
Thanks a lot.BF (BF@.discussions.microsoft.com) writes:
> I need to develop a stored procedure to add or drop SQL server logins
> into SQL servers based on some logics users give to me. My stored
> procedure is on one server. I need to add logins to other servers
> through linked server.
> Does anybody know how to run sp_addlogin to add SQL logins into linked
> server?
Did you try
EXEC SOMESERVER.master.dbo.sp_addlogin=
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||I just tried
exec wsdev.master.dbo.sp_addlogin 'test', 'test'
After this, I checked and saw the new login has been created. But I got the
following error message:
New login created.
Msg 0, Level 11, State 0, Line 0
A severe error occurred on the current command. The results, if any, should
be discarded.
I cannot use this in production system with error message like this. Any
further suggestions?
"Erland Sommarskog" wrote:

> BF (BF@.discussions.microsoft.com) writes:
> Did you try
> EXEC SOMESERVER.master.dbo.sp_addlogin=
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx
>|||BF (BF@.discussions.microsoft.com) writes:
> I just tried
> exec wsdev.master.dbo.sp_addlogin 'test', 'test'
> After this, I checked and saw the new login has been created. But I got
> the following error message:
> New login created.
> Msg 0, Level 11, State 0, Line 0
> A severe error occurred on the current command. The results, if any,
> should be discarded.
> I cannot use this in production system with error message like this. Any
> further suggestions?
I was not able to repeat this. Exactly which versions of SQL Server do
you have on the two servers? Use serverproperty('ProductVersion') to
determine this.
Anyway, the error message looks like you are running SQL 2005. In such
case you can use EXEC() AT:
EXEC ('EXEC master.dbo.sp_addlogin ''test'', ''test''') AT wsdev
If the target server is also running SQL 2005, you should not use
sp_addlogin at all, but rather CREATE LOGIN:
EXEC ('CREATE LOGIN test WITH PASSWORD = ''test''') AT wsdev
Note however, that this command will fail, because SQL 2005 validates
the password according to Windows policy. This mainly happens on SQL 2003,
but SQL 2005 always frowns at username = password.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||I am using SQL Server 2000. The exact version is 8.00.760.
I cannot use EXEC AT on my server. But it's good to know this new feature on
SQL 2005.
Now I am thinking open 2 database connections from my .NET application to
access 2 different servers.
Thanks.
"Erland Sommarskog" wrote:

> BF (BF@.discussions.microsoft.com) writes:
> I was not able to repeat this. Exactly which versions of SQL Server do
> you have on the two servers? Use serverproperty('ProductVersion') to
> determine this.
> Anyway, the error message looks like you are running SQL 2005. In such
> case you can use EXEC() AT:
> EXEC ('EXEC master.dbo.sp_addlogin ''test'', ''test''') AT wsdev
> If the target server is also running SQL 2005, you should not use
> sp_addlogin at all, but rather CREATE LOGIN:
> EXEC ('CREATE LOGIN test WITH PASSWORD = ''test''') AT wsdev
> Note however, that this command will fail, because SQL 2005 validates
> the password according to Windows policy. This mainly happens on SQL 2003,
> but SQL 2005 always frowns at username = password.
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx
>|||BF (BF@.discussions.microsoft.com) writes:
> I am using SQL Server 2000. The exact version is 8.00.760.
> I cannot use EXEC AT on my server. But it's good to know this new
> feature on SQL 2005.
> Now I am thinking open 2 database connections from my .NET application to
> access 2 different servers.
I assumed that you were on SQL 2005, because I took for granted that
you were running this from a query tool, which in that case would
be Management Studio which uses SqlClient.
Moral: please always be specific with the environment you are using.
As for the solution, this what I have recommended in the first place,
if I had known that you were doing this from an application. Relying
on linked servers is fragile, since the definition of a linked server
could change or disappear completely.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Adding Values in a Text Box

I have multiple values in text boxes based on Summed values. I would like to
add the values that are in the text boxes. What is the ref for Boxes? I
know fields are Fields!.
Here is an example of what I am running it the text boxes. They are in the
group footer. I would like to add them in the report footer, which is in a
different scope.
=IIF( Fields!Part_Type_Name.Value = "WoodTruss" ,Sum(
Fields!ItemLoss.Value), 0)
--
Thank You, LeoI would like to sum the values not just add them.
Thanks
"TrussworksLeo" wrote:
> I have multiple values in text boxes based on Summed values. I would like to
> add the values that are in the text boxes. What is the ref for Boxes? I
> know fields are Fields!.
> Here is an example of what I am running it the text boxes. They are in the
> group footer. I would like to add them in the report footer, which is in a
> different scope.
> =IIF( Fields!Part_Type_Name.Value = "WoodTruss" ,Sum(
> Fields!ItemLoss.Value), 0)
> --
> Thank You, Leo|||Take a look a the ReportItems!<TextboxName>.Value syntax. This syntax allows
you to reference values in a textbox.
Bruce Johnson [MSFT]
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"TrussworksLeo" <Leo@.noemail.noemail> wrote in message
news:509843C2-29B9-4B0E-B22A-5C497B99F0AB@.microsoft.com...
> I would like to sum the values not just add them.
> Thanks
> "TrussworksLeo" wrote:
> > I have multiple values in text boxes based on Summed values. I would
like to
> > add the values that are in the text boxes. What is the ref for Boxes?
I
> > know fields are Fields!.
> >
> > Here is an example of what I am running it the text boxes. They are in
the
> > group footer. I would like to add them in the report footer, which is
in a
> > different scope.
> >
> > =IIF( Fields!Part_Type_Name.Value = "WoodTruss" ,Sum(
> > Fields!ItemLoss.Value), 0)
> > --
> > Thank You, Leo|||Yes, But it will not allow me to us a aggregate like sum against it?
Leo
"Bruce Johnson [MSFT]" wrote:
> Take a look a the ReportItems!<TextboxName>.Value syntax. This syntax allows
> you to reference values in a textbox.
>
> --
> Bruce Johnson [MSFT]
> Microsoft SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "TrussworksLeo" <Leo@.noemail.noemail> wrote in message
> news:509843C2-29B9-4B0E-B22A-5C497B99F0AB@.microsoft.com...
> > I would like to sum the values not just add them.
> >
> > Thanks
> >
> > "TrussworksLeo" wrote:
> >
> > > I have multiple values in text boxes based on Summed values. I would
> like to
> > > add the values that are in the text boxes. What is the ref for Boxes?
> I
> > > know fields are Fields!.
> > >
> > > Here is an example of what I am running it the text boxes. They are in
> the
> > > group footer. I would like to add them in the report footer, which is
> in a
> > > different scope.
> > >
> > > =IIF( Fields!Part_Type_Name.Value = "WoodTruss" ,Sum(
> > > Fields!ItemLoss.Value), 0)
> > > --
> > > Thank You, Leo
>
>

Adding Users SqlServer2000

More of a SQL Server problem than a ASP one but hope you can help me :)

Im tryin to do my first data based application using ASP.net and silly me I forgot to add users when I created then database so now Visual Studio can't access it.

Can someone tell me the syntax to add/create a user to MSSQL database so I can then grant them privelidges to the tables.

thanks very much

Tomif you go to enterprise manager, xpand on databases then yourdatabase, theres a tab called users. there you cann add/modify users permissions.

hth

Adding Table Columns

Any help with this will be appreciated:
I have a simple table control in a report that has values in cells, some of
the cells contain values based on values of fields in the dataset i.e.
IIF(Field1 < Field2, Field1, Field2). How would I go about adding together
the actual results of cells in a grouping rather than also specifying the IIF
logic in the group for the sum textbox? The results are not the same compared
to if you had to add the results of the cells. See the table below of what is
happening:
Field1 |Field2 |Cell Value = IIF(Field1 < Field2, Field1, Field2)
Detail |1000 |2000 |1000
Detail |2000 |1500 |1500
Group |
Footer| 3000 |3500 |3000 (I want this toreflect 2500)
Ideally I would use the ReportItems collection but you can only use that in
the page header or footer. Am I doing something stupid or missing something?
Hope this makes sense!Hey just do a sum (Field!txt.value, "Group1") which will sum that group and
need not use cell value.
Amarnath
"WvanWyk" wrote:
> Any help with this will be appreciated:
> I have a simple table control in a report that has values in cells, some of
> the cells contain values based on values of fields in the dataset i.e.
> IIF(Field1 < Field2, Field1, Field2). How would I go about adding together
> the actual results of cells in a grouping rather than also specifying the IIF
> logic in the group for the sum textbox? The results are not the same compared
> to if you had to add the results of the cells. See the table below of what is
> happening:
> Field1 |Field2 |Cell Value = IIF(Field1 < Field2, Field1, Field2)
> Detail |1000 |2000 |1000
> Detail |2000 |1500 |1500
> Group |
> Footer| 3000 |3500 |3000 (I want this toreflect 2500)
> Ideally I would use the ReportItems collection but you can only use that in
> the page header or footer. Am I doing something stupid or missing something?
> Hope this makes sense!sql

Adding Parameters from .ASPX file

HI,
I want to show results in a report based on a variable send from an .aspx file. Is it possible to do this in de report designer. I have checked the possibilities for adding parameters but as far as I know the only way add parameters to queries in the report designer is, a prompt, a text-box or dropdown. Can I also add code like <% Request.Form(variable) %>.
This would really help me alot so I hope somebody knows the answer. Mucho gracias.
capsync

Hi,

It is quite possible to do. I've posted this code in one of my previous post. This should help you quite a bit, place this code in an aspx file's button click code behind event.

I created a 'objReport' object which was a SRS report object. You should focus on the response.write part.
[ReportServer] = SRS reportserver URL
[Path] = Report Location eg. '/folder/myReport'
[ReportParameters] = Well anything
string strFullParameters = string.Format(objReport.Parameters, strParams);
strFullParameters = strFullParameters.Replace("/", "%2f");
strFullParameters = strFullParameters.Replace(":", "%3a");
strFullParameters = strFullParameters.Replace(" ", "+");

Response.Write("<DIV><IFRAME ID=SQLReportsFrame SRC='" + this.ReportServer + "?" + objReport.Path.Replace("/", "%2f") + strFullParameters + "&rc:ReplacementRoot=" + this.Request.Url.AbsoluteUri + "?ServerUrl=' HEIGHT=100% WIDTH=100%></IFRAME></DIV>");

|||Thanks for your answer. Unfortunately, I still didn't manage to fix it. Can you give me some more explanation please? Thanks in advance.
cheers,
capsync|||

All the parameters needs to exist on the SRS report first( Create all possible parameters), but allow blanks. Unless you would like to add paramters dynamically which is a whole diffrent ball game. Then you could use only the ones required by sending the from the aspx page.

Adding Integration Services Node in Server Management Studio

Hi,
Am just getting started with SSIS. Based on an article I saw online, I would expect to find an "Integration Services" node in Server Management Studio, but I don't see this node. I'm trying to create and execute a simple package.
Running...
Microsoft SQL Server 2005 - 9.00.1116 (Intel X86)
Apr 9 2005 20:56:37
Copyright (c) 1988-2004 Microsoft Corporation
Beta Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
...on a named instance.
Thanks,
krog
Have you tried in Object Explorer

Connect | Integration Services

Allan|||There it is. Thank you!|||I am testing this on Windows XP and when I run SSIS, I can connect to server but I don't see a way to connect to Integration Services. I see in the Providers folder DTSPackage but how do I execute this assuming this is what I need. I am new to SQL Express and I am trying to import a DBF file and create a database.

Neil|||I am trying to connnect through Object Browser, but I am getting Access Denied error. Please note I am not the Admin on the box. What kind of permission I need to register Integration Services connection on my Management studio? Finally I want to import some packages I developed on msdb.

Thanks|||Hi,

I am also experiencing Access Denied errors when using object explorer (on my workstation) to connect to SSIS.

I have searched and tried some tips but I am still not able to connect. I am a local administrator on the sql server, i have added myself to db_dtsadmin role in the msdb database.

Can anyone please shed some light on how to connect remotely to SSIS.

Thanks,
Jason|||SSIS only comes with Standard edition and above. SQL Express does not include SSIS.|||

Any additional information about this?

I also recieve "Access denied" when attempting to add an Integration Service connection through the object browser. It works fine on a co-worker's workstation. I have disabled my firewall. I'm on the same subnet as my co-worker who is able to connect.

Best regards,

Shad

Adding Integration Services Node in Server Management Studio

Hi,
Am just getting started with SSIS. Based on an article I saw online, I would expect to find an "Integration Services" node in Server Management Studio, but I don't see this node. I'm trying to create and execute a simple package.
Running...
Microsoft SQL Server 2005 - 9.00.1116 (Intel X86)
Apr 9 2005 20:56:37
Copyright (c) 1988-2004 Microsoft Corporation
Beta Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
...on a named instance.
Thanks,
krog
Have you tried in Object Explorer

Connect | Integration Services

Allan
|||There it is. Thank you!
|||I am testing this on Windows XP and when I run SSIS, I can connect to server but I don't see a way to connect to Integration Services. I see in the Providers folder DTSPackage but how do I execute this assuming this is what I need. I am new to SQL Express and I am trying to import a DBF file and create a database.

Neil|||I am trying to connnect through Object Browser, but I am getting Access Denied error. Please note I am not the Admin on the box. What kind of permission I need to register Integration Services connection on my Management studio? Finally I want to import some packages I developed on msdb.

Thanks|||Hi,

I am also experiencing Access Denied errors when using object explorer (on my workstation) to connect to SSIS.

I have searched and tried some tips but I am still not able to connect. I am a local administrator on the sql server, i have added myself to db_dtsadmin role in the msdb database.

Can anyone please shed some light on how to connect remotely to SSIS.

Thanks,
Jason|||SSIS only comes with Standard edition and above. SQL Express does not include SSIS.|||

Any additional information about this?

I also recieve "Access denied" when attempting to add an Integration Service connection through the object browser. It works fine on a co-worker's workstation. I have disabled my firewall. I'm on the same subnet as my co-worker who is able to connect.

Best regards,

Shad

Adding Integration Services Node in Server Management Studio

Hi,
Am just getting started with SSIS. Based on an article I saw online, I would expect to find an "Integration Services" node in Server Management Studio, but I don't see this node. I'm trying to create and execute a simple package.
Running...
Microsoft SQL Server 2005 - 9.00.1116 (Intel X86)
Apr 9 2005 20:56:37
Copyright (c) 1988-2004 Microsoft Corporation
Beta Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
...on a named instance.
Thanks,
krog
Have you tried in Object Explorer

Connect | Integration Services

Allan|||There it is. Thank you!|||I am testing this on Windows XP and when I run SSIS, I can connect to server but I don't see a way to connect to Integration Services. I see in the Providers folder DTSPackage but how do I execute this assuming this is what I need. I am new to SQL Express and I am trying to import a DBF file and create a database.

Neil|||I am trying to connnect through Object Browser, but I am getting Access Denied error. Please note I am not the Admin on the box. What kind of permission I need to register Integration Services connection on my Management studio? Finally I want to import some packages I developed on msdb.

Thanks|||Hi,

I am also experiencing Access Denied errors when using object explorer (on my workstation) to connect to SSIS.

I have searched and tried some tips but I am still not able to connect. I am a local administrator on the sql server, i have added myself to db_dtsadmin role in the msdb database.

Can anyone please shed some light on how to connect remotely to SSIS.

Thanks,
Jason|||SSIS only comes with Standard edition and above. SQL Express does not include SSIS.|||

Any additional information about this?

I also recieve "Access denied" when attempting to add an Integration Service connection through the object browser. It works fine on a co-worker's workstation. I have disabled my firewall. I'm on the same subnet as my co-worker who is able to connect.

Best regards,

Shad

Adding Integration Services Node in Server Management Studio

Hi,
Am just getting started with SSIS. Based on an article I saw online, I would expect to find an "Integration Services" node in Server Management Studio, but I don't see this node. I'm trying to create and execute a simple package.
Running...
Microsoft SQL Server 2005 - 9.00.1116 (Intel X86)
Apr 9 2005 20:56:37
Copyright (c) 1988-2004 Microsoft Corporation
Beta Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
...on a named instance.
Thanks,
krog
Have you tried in Object Explorer

Connect | Integration Services

Allan|||There it is. Thank you!|||I am testing this on Windows XP and when I run SSIS, I can connect to server but I don't see a way to connect to Integration Services. I see in the Providers folder DTSPackage but how do I execute this assuming this is what I need. I am new to SQL Express and I am trying to import a DBF file and create a database.

Neil|||I am trying to connnect through Object Browser, but I am getting Access Denied error. Please note I am not the Admin on the box. What kind of permission I need to register Integration Services connection on my Management studio? Finally I want to import some packages I developed on msdb.

Thanks|||Hi,

I am also experiencing Access Denied errors when using object explorer (on my workstation) to connect to SSIS.

I have searched and tried some tips but I am still not able to connect. I am a local administrator on the sql server, i have added myself to db_dtsadmin role in the msdb database.

Can anyone please shed some light on how to connect remotely to SSIS.

Thanks,
Jason|||SSIS only comes with Standard edition and above. SQL Express does not include SSIS.|||

Any additional information about this?

I also recieve "Access denied" when attempting to add an Integration Service connection through the object browser. It works fine on a co-worker's workstation. I have disabled my firewall. I'm on the same subnet as my co-worker who is able to connect.

Best regards,

Shad

Adding blank lines to a report -2nd request

I am trying to add additional blank lines to a report based on how much room
is left on a page. If there are 6 lines of datarows, I need there to be 15
lines of blank lines. if there are 15 lines of datarows I need 6 blank lines
added.
I am currenly using a table object with a static number of header rows with
the bottom lined. Any suggestions would be appreciated.Hello Eric,
I would like to suggest you to add another table under your table.
Also, you need to add a dataset which contain 21 lines records. And you
could use a filter to control how many rows to show.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
==================================================(This posting is provided "AS IS", with no warranties, and confers no
rights.)|||That is how I am currently doing the extra lines I have now. I have a table
that is simply all header rows. But I havent figured out how to do a
'filter' when there is not a dataset attached to it.
"Wei Lu [MSFT]" <weilu@.online.microsoft.com> wrote in message
news:XQ4UvMzHHHA.1220@.TK2MSFTNGHUB02.phx.gbl...
> Hello Eric,
> I would like to suggest you to add another table under your table.
> Also, you need to add a dataset which contain 21 lines records. And you
> could use a filter to control how many rows to show.
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ==================================================> Get notification to my posts through email? Please refer to
> http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
> ications.
> Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
> where an initial response from the community or a Microsoft Support
> Engineer within 1 business day is acceptable. Please note that each follow
> up response may take approximately 2 business days as the support
> professional working with you may need further investigation to reach the
> most efficient resolution. The offering is not appropriate for situations
> that require urgent, real-time or phone-based interactions or complex
> project analysis and dump analysis issues. Issues of this nature are best
> handled working with a dedicated Microsoft Support Engineer by contacting
> Microsoft Customer Support Services (CSS) at
> http://msdn.microsoft.com/subscriptions/support/default.aspx.
> ==================================================> (This posting is provided "AS IS", with no warranties, and confers no
> rights.)
>|||Hello Eric,
You could create a table with just one column "ID"
Then, you could insert just 21 rows in the table.
After that, you could use this statement to get the new dataset which
contain only 2 column:
SELECT id, ' ' as Text from <table name>
The filter could be setted like this:
ID <= 21-Rownumber('your first dataset name')
Hope this will be helpful.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||You don't even need a table. Have your dataset be on generic designer mode
(button is to the right of the ...) and use a Union statement
select 1 as ID, ' ' as Text union
select 2 as ID, ' ' as Text union
select 3 as ID, ' ' as Text union
select 4 as ID, ' ' as Text union
select 5 as ID, ' ' as Text union
select 6 as ID, ' ' as Text union
.
.
.
select 25 as ID,' ' as Text
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Wei Lu [MSFT]" <weilu@.online.microsoft.com> wrote in message
news:oZZxARBIHHA.2024@.TK2MSFTNGHUB02.phx.gbl...
> Hello Eric,
> You could create a table with just one column "ID"
> Then, you could insert just 21 rows in the table.
> After that, you could use this statement to get the new dataset which
> contain only 2 column:
> SELECT id, ' ' as Text from <table name>
> The filter could be setted like this:
> ID <= 21-Rownumber('your first dataset name')
> Hope this will be helpful.
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ==================================================> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ==================================================> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>|||Hello Bruce,
Yes, you could use the union in the dataset.
The reason why I recommand the table is for some performance concern and
more efficant management.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi ,
How is everything going? Please feel free to let me know if you need any
assistance.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.

adding additional Blank lines to a report

I am trying to add additional blank lines to a report based on how much room
is left on a page. If there are 6 lines of datarows, I need there to be 15
lines of blank lines. if there are 15 lines of datarows I need 6 blank lines
added.
I am currenly using a table object with a static number of header rows with
the bottom lined. Any suggestions would be appreciated.Eric,
I had this problem as well. What I did or would do in your case is add
20 rows of footers after your detail row. On the first footer set the
visibility of the row = IIF(rownumber<20, false, true). This will
always show this row if less than 20 rows in the dataset are returned.
On the next footer set the visibiitly = IIF(rownumber<19, false, true).
do this to the remaining 18 footers incrementing rownumber by -1 in
each case. THat should work. Let me know if it doesn't
Melissa
Eric Cathell wrote:
> I am trying to add additional blank lines to a report based on how much room
> is left on a page. If there are 6 lines of datarows, I need there to be 15
> lines of blank lines. if there are 15 lines of datarows I need 6 blank lines
> added.
> I am currenly using a table object with a static number of header rows with
> the bottom lined. Any suggestions would be appreciated.