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)

adjusting parameter controls of a report

Hi,
I'm making use of parameters to filter data in an report.
But the parameters appear in fixed-width listboxes in the report. Some
values of the parameters are longer than the width of the listboxes. My
question is how to adjust the width of these parameter listboxes. Any ideas?
Regards, Harry.Hi Harry,
apparently this is not possible.. I had the same question yesterday and I
got the following reply:
"On Jul 17, 3:54 am, mischa <mis...@.discussions.microsoft.com> wrote:
> I have designed a few reports in VS2005 with multi-parameter controls. When I
> view these reports in VS they look fine, however once deployed and viewed in
> the webbrowser (MS Internet Explorer) the drop-down boxes are too small (or
> the font size is too big).
> Is there a way to adjust (resize) the control boxes so you can make them
> larger so it's easier for people to actually read where they can choose
> between?
> thanks,
> mischa
As far as I know, there is not. This has been a common request on this
news group. Hopefully, this will be addressed in SSRS 2008. Otherwise,
you could try to inject CSS; however, I think that styling is only
allowed at the Report Manager page and report border level (though I'm
not sure). Sorry that I could not be of greater assistance.
Regards,
Enrique Martinez
Sr. Software Consultant
"
best regards,
mischa
"Harry2007" wrote:
> Hi,
> I'm making use of parameters to filter data in an report.
> But the parameters appear in fixed-width listboxes in the report. Some
> values of the parameters are longer than the width of the listboxes. My
> question is how to adjust the width of these parameter listboxes. Any ideas?
> Regards, Harry.sql

addlinkedserver to 2005

-- To use named parameters:
EXEC sp_addlinkedserver
@.server = 'LONDON Payroll',
@.provider = 'MSDASQL',
@.provstr = 'DRIVER={SQL
Server};SERVER=MyServer;UID=sa;PWD=;'
GO
Is there anything different to link to SQL2005? How can
create a link to 2005?
Thx.Probably better to post this question in the SQL Server 2005 groups?
--
http://www.aspfaq.com/
(Reverse address to reply.)
"C TO" <anonymous@.discussions.microsoft.com> wrote in message
news:98cc01c478b5$b0382620$a301280a@.phx.gbl...
> -- To use named parameters:
> EXEC sp_addlinkedserver
> @.server = 'LONDON Payroll',
> @.provider = 'MSDASQL',
> @.provstr = 'DRIVER={SQL
> Server};SERVER=MyServer;UID=sa;PWD=;'
> GO
> Is there anything different to link to SQL2005? How can
> create a link to 2005?
> Thx.
>

Adding the parameters dynamicaly to SP

In our website we have created the reportengine which passes the parameters dynamicaly means whatever parameters are provided in Stored procedure is automaticaly binded to grid from where values are passed to sp as follows
.Actualy this code is written by some other developer
If Not ParameterTable Is Nothing Then
RecordCount = ParameterTable.Rows.Count
If RecordCount >= 1 Then
For Index = 0 to (RecordCount - 1)
ParameterRow = ParameterTable.Rows(Index)
Select Case ParameterRow(1)
Case "231"
RecordType = "SQlDbType.NVarChar"
Case "60"
RecordType = "SQlDbType.Money"
Case "61"
RecordType = "SQlDbType.DateTime"
Case "62"
RecordType = "SQlDbType.Int"
Case "59"
RecordType = "SQlDbType.Float"
Case "104"
RecordType = "SQlDbType.Bit"
Case Else
RecordType = "SQlDbType.Int"

End Select
myCommand.Parameters.Add(ParameterRow(0), RecordType).Value = (ParameterRow(2))

[red]When Sp is executed with link[/red]<A href="http://links.10026.com/?link=Main.aspx?ReportID=1075443551&ReportName=RptAPandARComparative&ReportDescription=RptAPandARComparative"
target="main">

I get the error that nvarchar can not be converted int

Why is is So?
Is anybody having same type of experiance
In above link I got reportid as queries
select id from sysobjects where name='RptAPandARComparative'

Swati Jain:

I get the error that nvarchar can not be converted int

Is your stored procedure parameter declared as an int or varchar?

|||

I tried all datatypes still I got the same error with sp as well as code

|||

Swati Jain:

I tried all datatypes still I got the same error with sp as well as code

Can you show us your input parameters for the stored procedures?

You would also have to specify the parameter value size as well to make sure there are no data type mismatch errors.

|||

alterPROCEDURE [enterprise].[RptAPandARComparative]

@.Year1Int,--nvarchar(8), I tried various datatypes

@.Year2Int--nvarchar(8)

AS

No Code is written for specifying the size of the datatype

|||

Swati Jain:

alterPROCEDURE [enterprise].[RptAPandARComparative]

@.Year1Int,--nvarchar(8), I tried various datatypes

@.Year2Int--nvarchar(8)

your stored procedures are declared with INTEGER input paramters, so you have to set the type for your parameters toSqlDbType.Int for both input parameters.

You should also set the size as well, I believe int datatypes in SQL Server is 8 bytes.

adding sql parameter in VB

I have a details view with several parameters set up in myasp.net 2.0 code, I want to add a parameter before the sql parameter is executed. I need to use the find control of the details view because I am using items/edit item templates in my details view control.
I tried this(see below) as well as the detailsview item command event args to no avail. It doesn't see the other parameters that have already been declared in my asp.net code. I don't want to have to declare all my varibles that are already in my asp.net code. I just want to add another parameter.

Sub InsertNew(ByVal sender As Object, ByVal e As DetailsViewInsertEventArgs) _
Handles dvEvents.ItemInserting

Dim dvr As DetailsViewRow

For Each dvr In dvEvents.Rows

Dim CatIDup As Integer = CType(dvr.FindControl("ddlCat"), DropDownList).SelectedValue
sdsevents.InsertParameters.Add("evCatID", CatIDup)
sdsevents.Insert()

Handle the sdsevents.Inserting event.

e.Command.Parameters.Add("@.evCatID",SqlDbType.Int).Value={something}

Also, in your prior code, it's confusing, because you are iterating a set of rows in dvr, and dvr hasn't been set. Even if it was, why would you want to add a parameter for each row?

|||

I tried what you suggested and i get sqldbtype is not declared. see below

As far as iiterating through the rows, I could not find a better way to do that if i did not iterate the rows i would get the error

about my dvr not being set if i did the for each i did not get that error.
I am happy to hear a better suggestion of how to do this.
Thanks

Sub InsertNew(ByVal senderAsObject,ByVal eAs SqlDataSourceCommandEventArgs) _

Handles sdsevents.Inserting

Dim dvrAs DetailsViewRow

ForEach dvrIn dvEvents.Rows

' Find the Selected Category Name ID Value

Dim CatIDupAsInteger =CType(dvr.FindControl("ddlCat"), DropDownList).SelectedValue

e.Command.Parameters.Add("@.evCatID",SqlDbType.Int).Value={CatIDup}

Next

EndSub

|||Sub InsertNew(ByVal senderAsObject,ByVal eAs SqlDataSourceCommandEventArgs) _

Handles sdsevents.Inserting

Dim dvAs DetailsView=detailsview1

' Find the Selected Category Name ID Value

Dim CatIDupAsInteger =CType(dv.FindControl("ddlCat"), DropDownList).SelectedValue

e.Command.Parameters.Add("@.evCatID",System.Data.SqlDbType.Int).Value=CatIDup

EndSub

That assumes your detailsview is called detailsview1.

You can also do it from the detailsview events, but you need to reference the parameter collection that is passed to you. In the ItemInserting event, it would be e.Values. In the ItemUpdating event, it would be e.NewValues. Normally I would declaritively define the parameter (In design view... To assign the type, nullability, default value, etc etc). Then I would either set the value in e.Values/e.NewValues or e.Command.Parameters("@.evCatID").Value= to set the value of the already existing parameter.

|||

I got it I did it this way

thanks for your help!!

PublicSub InsertNew(ByVal senderAsObject,ByVal eAs DetailsViewInsertEventArgs) _

Handles dvEvents.ItemInserting

Dim dvAs DetailsView = dvEvents

' 'Find the Selected Category Name ID Value

Dim CatIDupAsInteger =CType(dv.FindControl("ddlCat"), DropDownList).SelectedValue

sdsevents.InsertParameters.Add("evCatID", CatIDup)

EndSub

Adding Parameters to the ORDER BY Clause

What I'm wanting to do is create a report which allows you to sort by a
few different columns, and also choose if you want it sorted ASC or
DESC.
Basically, I'll have two parameters...
1) OrderBy (a number of different columns)
2) OrderDirection (ASC or DESC)
and I'll want to do a
SELECT -
FROM -
WHERE -
ORDER BY @.OrderBy @.OrderDirection
Obviously it doesn't work like this, or I wouldn't be asking for help!
I know you can use things like "Parameters!OrderBy.Value" but I was
unable to get this to work. Would somebody mind helping out?
Thanks!
-ScottI have always done this from within the RDL file during creation. It is in
one of the property dialog boxes, you may have to choose and advanced button
somewhere...It will be within the group header properties I think...
--
--Eric Cathell, MCSA
<ScottWMcCarter@.gmail.com> wrote in message
news:1108158684.310041.54770@.g14g2000cwa.googlegroups.com...
> What I'm wanting to do is create a report which allows you to sort by a
> few different columns, and also choose if you want it sorted ASC or
> DESC.
> Basically, I'll have two parameters...
> 1) OrderBy (a number of different columns)
> 2) OrderDirection (ASC or DESC)
> and I'll want to do a
> SELECT -
> FROM -
> WHERE -
> ORDER BY @.OrderBy @.OrderDirection
> Obviously it doesn't work like this, or I wouldn't be asking for help!
> I know you can use things like "Parameters!OrderBy.Value" but I was
> unable to get this to work. Would somebody mind helping out?
> Thanks!
> -Scott
>|||SELECT dbo.DatabaseName.*
FROM dbo.DatabaseName
WHERE (FieldName LIKE @.FieldName)
ORDER BY FieldName
Note: @.FieldName is "assigned" from the Report...Report Parameters menu bar
--
Message posted via http://www.sqlmonster.com|||I will upload 3 or 4 samples of dynamic sorts to www.MSBICentral.com...
hope this helps
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
<ScottWMcCarter@.gmail.com> wrote in message
news:1108158684.310041.54770@.g14g2000cwa.googlegroups.com...
> What I'm wanting to do is create a report which allows you to sort by a
> few different columns, and also choose if you want it sorted ASC or
> DESC.
> Basically, I'll have two parameters...
> 1) OrderBy (a number of different columns)
> 2) OrderDirection (ASC or DESC)
> and I'll want to do a
> SELECT -
> FROM -
> WHERE -
> ORDER BY @.OrderBy @.OrderDirection
> Obviously it doesn't work like this, or I wouldn't be asking for help!
> I know you can use things like "Parameters!OrderBy.Value" but I was
> unable to get this to work. Would somebody mind helping out?
> Thanks!
> -Scott
>|||When using filters on a table/matrix group there's a ASC or DESC. But, can
you make that an expression? When I select it, there's no option to make it
an expression.
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:eeNwO3pEFHA.1188@.tk2msftngp13.phx.gbl...
> I will upload 3 or 4 samples of dynamic sorts to www.MSBICentral.com...
> hope this helps
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> <ScottWMcCarter@.gmail.com> wrote in message
> news:1108158684.310041.54770@.g14g2000cwa.googlegroups.com...
> > What I'm wanting to do is create a report which allows you to sort by a
> > few different columns, and also choose if you want it sorted ASC or
> > DESC.
> >
> > Basically, I'll have two parameters...
> >
> > 1) OrderBy (a number of different columns)
> > 2) OrderDirection (ASC or DESC)
> >
> > and I'll want to do a
> > SELECT -
> > FROM -
> > WHERE -
> > ORDER BY @.OrderBy @.OrderDirection
> >
> > Obviously it doesn't work like this, or I wouldn't be asking for help!
> > I know you can use things like "Parameters!OrderBy.Value" but I was
> > unable to get this to work. Would somebody mind helping out?
> >
> > Thanks!
> >
> > -Scott
> >
>|||I'm not sure if my original question has been answered - In the SQL
query, how am I going to add my parameters so that you can dynamically
change if it is being sorted by ASC or DESC? Thanks for all the help
so far, you guys are great!|||You can certainly do that in the query by using a dynamic query string
="SELECT ... " & IIF(Fields!Order.Value="Asc", "ASC", "DESC"). However, is
there any reason why you feel you need to do it in the query and not in the
report engine (post query)?
--
Brian Welcker
Group Program Manager
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
<ScottWMcCarter@.gmail.com> wrote in message
news:1108412814.884740.156270@.g14g2000cwa.googlegroups.com...
> I'm not sure if my original question has been answered - In the SQL
> query, how am I going to add my parameters so that you can dynamically
> change if it is being sorted by ASC or DESC? Thanks for all the help
> so far, you guys are great!
>|||No, I just don't know how to do it in the report engine!! That is what
I'm looking for. Thanks!

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 parameters during .updating event handler

Not receiving any errors. The update runs perfectly on all fields, except for the added parameter during the sub. I need to change the hidden field's value after parsing out several fields in the form and generating a logfile entry of sorts. This needs to happen after all the form fields are updated, but before the update is executed.

aspx: (relevant parts only...)
<asp:SqlDataSourceID="TicketDetails"runat="server"ConnectionString="<%$ ConnectionStrings:myConnectionString %>"
UpdateCommand="UPDATE Tickets SET TicketSuspense = @.TicketSuspense, TicketPriority = @.TicketPriority, TicketLastUpdated = CURRENT_TIMESTAMP, TicketStatus = 'Assigned', TicketTechnicianNotes = @.TicketTechnicianNotes WHERE (TicketID = @.TicketID)">
<UpdateParameters>
<asp:ParameterName="TicketPriority"Type="String"/>
<asp:ParameterName="TicketSuspense"Type="DateTime"/>
<asp:ParameterName="TicketID"Type="Int32"/>
</UpdateParameters>
</asp:SqlDataSource>

codebehind: (once again, the relevant parts only)
ProtectedSub TicketDetails_Updating(ByVal senderAsObject,ByVal eAs System.Web.UI.WebControls.SqlDataSourceCommandEventArgs)Handles TicketDetails.Updating
TicketTechnicianNotesHiddenField.Value ="some text..."& TicketTechnicianNotesHiddenField.Value
TicketDetails.UpdateParameters.Add(New Parameter("TicketTechnicianNotes", TypeCode.String, TicketTechnicianNotesHiddenField.Value))
EndSub

Thanks,

- Brad

What about this:

ProtectedSub TicketDetails_Updating(ByVal senderAsObject,ByVal eAs System.Web.UI.WebControls.SqlDataSourceCommandEventArgs)Handles TicketDetails.Updating
TicketTechnicianNotesHiddenField.Value ="some text..."& TicketTechnicianNotesHiddenField.Value

e.Command.Parameters("@.TicketTechnicianNotes").Value = TicketTechnicianNotesHiddenField.Value

EndSub

|||

I think that was the only variation I hadn't yet tried. It worked.

I have to ask... what caused the "Add" not to work?

Thanks,

- Brad

|||

"TheSqlDataSource control will also automatically create parameters based on values passed by a data-bound control... "

You can find more information from this link:

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

Adding Global Parameters

Is there a way to access global parameters in Reporting Services Reports?..currently all we can usee is the Globals collection...is there a way to add keys to global collection?
can we write something in web.config ( as ReportServer is also a web application) and use the ConfigurationManager class?. The ConfigurationManager class did not work for me when I used it in the expression editor as the editor did not recognise it.

Any help is appreciated (or pointers for that matter).

Thanks.

Hello, have you found any solution yet? I'd like to use shared parameter of whole .net solution or project too. I could imagine to write assembly that expose a constant, but I wish it would exist an easier way. Jirka Nouza

Adding Global Parameters

Is there a way to access global parameters in Reporting Services Reports?..currently all we can usee is the Globals collection...is there a way to add keys to global collection?
can we write something in web.config ( as ReportServer is also a web application) and use the ConfigurationManager class?. The ConfigurationManager class did not work for me when I used it in the expression editor as the editor did not recognise it.

Any help is appreciated (or pointers for that matter).

Thanks.

Hello, have you found any solution yet? I'd like to use shared parameter of whole .net solution or project too. I could imagine to write assembly that expose a constant, but I wish it would exist an easier way. Jirka Nouza

Adding custom buttons to the parameters toolbox?

Hi,

I am interested in adding custom buttons to the parameters toolbox (under "View Report"). I'm trying to provde a way for users viewing the report to go "Back" or up a level (in the drill through reports). Does anyone have any information on this topic, or know if it's even possible?

Thanks in advance,

-Brandon

It is not possible to change the built in parameter bar.

As far as addressing your issue, you can put a hyperlink on each report and in the Jump To URL section use an expression like =“javascript:history.back()”|||I figured as much, but it never hurts to ask.... thanks for the sugestion, I'll just rig up something as usual.

Adding Comments to Query

Since I compose my query in Query Analyzer and
paste it into RS, I am trying to add comments to the top of my query
to preserve my QA parameters as follows:
/*Testing parameters
declare @.FromPostDate varchar(10), @.AgingDate varchar(10)
Set @.FromPostDate='1/1/2005'
Set @.AgingDate= '11/1/2005'
End Testing Parameters */
But when I paste the query into the RS editor and hit 'Refresh Fields'
I get an error saying 'Must declare the variable @.AgingDate'
If I delete the comment block, it assigns the 2 dates as report parameters
as I would expect. I have tried using '--' instead of /**/ and placing the
dates
on separate lines but it just ignores the comment markers.
Am I missing something?
ThanksIt seems I had a problem like this.. Perhaps try placing any executable
statement prior to the first comment...
--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
I support the Professional Association for SQL Server ( PASS) and it''s
community of SQL Professionals.
"Mike Harbinger" wrote:
> Since I compose my query in Query Analyzer and
> paste it into RS, I am trying to add comments to the top of my query
> to preserve my QA parameters as follows:
> /*Testing parameters
> declare @.FromPostDate varchar(10), @.AgingDate varchar(10)
> Set @.FromPostDate='1/1/2005'
> Set @.AgingDate= '11/1/2005'
> End Testing Parameters */
> But when I paste the query into the RS editor and hit 'Refresh Fields'
> I get an error saying 'Must declare the variable @.AgingDate'
> If I delete the comment block, it assigns the 2 dates as report parameters
> as I would expect. I have tried using '--' instead of /**/ and placing the
> dates
> on separate lines but it just ignores the comment markers.
> Am I missing something?
> Thanks
>
>

Adding Button in Report

I would like to know whether there is any way to add a button in report. I have 3 report parameters. On clicking the button, the current values of the reports are to be obtained and saved in database i.e saving the report configuration. I will use this saved configuration to form the url and view report directly.

Thanks,

Pradeep

No, actually not. YOu could put alink with the parameters in your report, calling another report which then executes stored procedure to store the values.

Jens K. Suessmeyer.

-
http://www.sqlserver2005.de
-|||Or you could set up a subscription with the specified parameters. Also if you want to get at the parameters, RS saves them in its own database as part of its logging.