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)

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

adjust x,y position of crystal report data field from VB

dear friend,

as you know, to adjust x,y position of any data field on Crystal report,
we just open CR and manually put and adjust the datafield...

But how if our VB application want to change this setting too ?
how to pass the x,y parameter of specific data field to crystal report, so crystal report will adjust automatically before it displays. ??

Thank you,If you use RDC to display or print the report, you can do it by changing the properties of the object.

Report1.Object1.Property1 = Value1

MyRpt.fldDate.Left = 1500
MyRpt.fldDate.Top = 250

Adjust Parameter Dropdown control Size/Length

Dear Anyone,

Is there a way to adjust Parameter Dropdown control Size/Length of a report so that it would no longer display a scrollbar?

Thanks,
JosephSorry, this is not a customizable property.

-Danielsql

Adjust Parameter Dropdown control Size/Length

Dear Anyone,

Is there a way to adjust Parameter Dropdown control Size/Length of a report so that it would no longer display a scrollbar?

Thanks,
JosephSorry, this is not a customizable property.

-Daniel

Adjust number of decimal places

I have a column used in my report that has values such as:
496.1000
These are money values and thus I require them to be in the format:
496.10
How do I get rid of the two extra zeroes?
(my sql statement actually does it, but they still appear in reporting
services for some reason)
Thanks!Don't worry about the above - I found the answer in the SQL 2005
documentation
For those interested, it was under:
Formatting Numeric and Date Values in a Report
Cheers
On Dec 18, 9:27 am, "David Conte" <davco...@.gmail.com> wrote:
> I have a column used in my report that has values such as:
> 496.1000
> These are money values and thus I require them to be in the format:
> 496.10
> How do I get rid of the two extra zeroes?
> (my sql statement actually does it, but they still appear in reporting
> services for some reason)
> Thanks!

Ad-hoc reporting memory consumption

Hi to all, on our project we are facing this problem: our users using report builder are making reports containing several Mb of data (sometimes also 1Gb!). Now it happens that when 3-4 users do a report like that on the server the process w3wp.exe reaches also 10 Gb of memory allocated and then the whole reporting services istance stops working. We want to limit the number of rows that can be extracted from the sql server db using report builder. In the report model reference seems that there is not this kind of option (something like "max rowcount"). Is it possible to set this limit to the user that connects to sql server (something like: whatever is the query this user does, set @.@.rowcount to 10000)? Thanks. Alberto.

Hi Alberto,

We are currently facing your same issue. Did you get some solution to limit the rowcount on reportbuilder models? Thanks in advance for any ideas.

|||

Hi Cato, unluckly it seems that there isn't a good solution. Keep an eye on security filters on report models,this is the only thing that seems to "filter" data.

HTH.

Alberto

Ad-hoc reporting memory consumption

Hi to all, on our project we are facing this problem: our users using report builder are making reports containing several Mb of data (sometimes also 1Gb!). Now it happens that when 3-4 users do a report like that on the server the process w3wp.exe reaches also 10 Gb of memory allocated and then the whole reporting services istance stops working.

We want to limit the number of rows that can be extracted from the sql server db using report builder. In the report model reference seems that there is not this kind of option (something like "max rowcount"). Is it possible to set this limit to the user that connects to sql server (something like: whatever is the query this user does, set @.@.rowcount to 10000)?

Thanks.

Alberto.

Hi Alberto,

We are currently facing your same issue. Did you get some solution to limit the rowcount on reportbuilder models? Thanks in advance for any ideas.

|||

Hi Cato, unluckly it seems that there isn't a good solution. Keep an eye on security filters on report models,this is the only thing that seems to "filter" data.

HTH.

Alberto

Adhoc Reporting

Couple of Questions regarding Reporting Services
1) Does SQL Server Reporting Services support adhoc reporting like dynamic
generation of report files rather than basing it on a template rdl
2) How long does it take to do a manual conversion of Crystal report with
say sub reports and say 30 formula fields and 4 paramters?
Thanks in Advance,
RdjeethRdjeeth,
1) Version 1.0 of RS doesn't support ad-hoc reporting features. However, the
next version of RS will come with a Report Builder which will support
comprehensive ad-hoc functionality. I've seen a short demo of the Report
Builder and I can say only good words about it. Meanwhile, you can evaluate
third-party products, such as the Cizer Quick Query.
2) Time will be in reverse proportion to your RS skills. As a side note,
instead of subreports you should gravitate towards using side-by-side
regions for performance reasons.
--
Hope this helps.
----
Teo Lachev, MVP [SQL Server], MCSD, MCT
Author: "Microsoft Reporting Services in Action"
Publisher website: http://www.manning.com/lachev
Buy it from Amazon.com: http://shrinkster.com/eq
Home page and blog: http://www.prologika.com/
----
"RDJEETH" <RDJEETH@.discussions.microsoft.com> wrote in message
news:1243B393-52ED-4CD4-B61A-F6C129B65912@.microsoft.com...
> Couple of Questions regarding Reporting Services
> 1) Does SQL Server Reporting Services support adhoc reporting like dynamic
> generation of report files rather than basing it on a template rdl
> 2) How long does it take to do a manual conversion of Crystal report with
> say sub reports and say 30 formula fields and 4 paramters?
> Thanks in Advance,
> Rdjeeth

Adhoc report with join tables on

Does anyone know if user has the capability to join two entities with
additional field which is not a predefined primary key or foreign key at the
design time? User want to run the report by join tables with certain fields
on the fly. I have not found a way to allow them to do that unless they have
Microsoft visual studio 2005 or SQL server business intelligence development
studio installed. Is SQL server reporting service a right candidate to serve
user's request? Does anyone know any other tool with this capability?
Thanks!1. You can do this if you define the sql statement dynamically and allow the
user parameter choices that provide the ability to specify the tables and
joins expressions. (for how to define the sql statement dynamically,
building it up as an expression, just treat the command as an expression
like you would the expression to display in a text box. IOW, start it with
an = sign and build up the string or invoke a code function)
However, I don't think it's really the best way.
2. Does the user have access and understanding to create views on the
server? It might be best to define the report based on a view, and have the
report basically remain ignorant of the joins and table information. The
report would have a single parameter -- the name of the view to invoke --
and would send that information to a stored procedure which would validate
that the view exists and has appropriate columns, and then run the view or
error-handle as described in choice #3 below.
3. If the user does not have that ability or access, I think I would build
this report to run a stored procedure, passing the parameter information as
described in #1, and have the stored procedure built and execute the sql. I
could do better validation in the stored procedure (for example, validate
that the tables and fields chosen by the user actually exist, assuming these
elements cannot be a dropdown in the report interface). I would have the
sproc send back a default data set of one record with every item showing
appropriate error text (or something) if I couldn't handle it another way.
Basically I think choice #2 is the right way to go here and if the user
doesn't have that ability and access I'm wondering whether that user should
be specifying this information at all...
>L<
"Daisy" <diyfan@.msnews.group.post> wrote in message
news:DE6C8C52-FBE3-48D4-9EB6-F37BBD8C40E4@.microsoft.com...
> Does anyone know if user has the capability to join two entities with
> additional field which is not a predefined primary key or foreign key at
> the
> design time? User want to run the report by join tables with certain
> fields
> on the fly. I have not found a way to allow them to do that unless they
> have
> Microsoft visual studio 2005 or SQL server business intelligence
> development
> studio installed. Is SQL server reporting service a right candidate to
> serve
> user's request? Does anyone know any other tool with this capability?
> Thanks!|||Thank you very much for the idea! I had built the model with report builder
to let user chose any fields from the tables given for the report. Now user
wanted to join the table not based on the primary key field that specified in
the design time. They wanted to join the table by certain non key fields at
the run time. I was stucked. I was only thinking use report builder model to
let user do this. And the report model could not change the key field (join
relation) on the fly. I have not found anything online or in MSDN library
telling me how to define the key at run time. As you suggested by using the
reprot designer and the sql stored procedure it is feasible to achieve the
task. Hope I did not misunderstood your post. If so please let me know.
Thanks!
"Lisa Slater Nicholls" wrote:
> 1. You can do this if you define the sql statement dynamically and allow the
> user parameter choices that provide the ability to specify the tables and
> joins expressions. (for how to define the sql statement dynamically,
> building it up as an expression, just treat the command as an expression
> like you would the expression to display in a text box. IOW, start it with
> an = sign and build up the string or invoke a code function)
> However, I don't think it's really the best way.
> 2. Does the user have access and understanding to create views on the
> server? It might be best to define the report based on a view, and have the
> report basically remain ignorant of the joins and table information. The
> report would have a single parameter -- the name of the view to invoke --
> and would send that information to a stored procedure which would validate
> that the view exists and has appropriate columns, and then run the view or
> error-handle as described in choice #3 below.
> 3. If the user does not have that ability or access, I think I would build
> this report to run a stored procedure, passing the parameter information as
> described in #1, and have the stored procedure built and execute the sql. I
> could do better validation in the stored procedure (for example, validate
> that the tables and fields chosen by the user actually exist, assuming these
> elements cannot be a dropdown in the report interface). I would have the
> sproc send back a default data set of one record with every item showing
> appropriate error text (or something) if I couldn't handle it another way.
> Basically I think choice #2 is the right way to go here and if the user
> doesn't have that ability and access I'm wondering whether that user should
> be specifying this information at all...
> >L<
>
> "Daisy" <diyfan@.msnews.group.post> wrote in message
> news:DE6C8C52-FBE3-48D4-9EB6-F37BBD8C40E4@.microsoft.com...
> > Does anyone know if user has the capability to join two entities with
> > additional field which is not a predefined primary key or foreign key at
> > the
> > design time? User want to run the report by join tables with certain
> > fields
> > on the fly. I have not found a way to allow them to do that unless they
> > have
> > Microsoft visual studio 2005 or SQL server business intelligence
> > development
> > studio installed. Is SQL server reporting service a right candidate to
> > serve
> > user's request? Does anyone know any other tool with this capability?
> >
> > Thanks!
>

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 Matrix report calculations

Is there a way to change the value in an ad-hoc matrix report to be the percent of the row an not just the raw number?

unfortunatly not

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

Address layout-no blank line

I'm using SQL Server Reporting Services and need to generate a report listing
addresses. I have to accomodate two address lines. How can I set it up so
that if there is no second address no blank line appears on the reportOn Oct 18, 11:14 am, Jeannie <Jean...@.discussions.microsoft.com>
wrote:
> I'm using SQL Server Reporting Services and need to generate a report listing
> addresses. I have to accomodate two address lines. How can I set it up so
> that if there is no second address no blank line appears on the report
I'm assuming you are using a table data region. Set the Hidden
property for the second line/row to somthing like this:
=IIF(addressline2 = Nothing, True, False)
Make sure you set the property for the row and not individual cells
otherwise it will not close the white space left by the hidden row.
HTH
toolman|||I'm VERY NEW to this product. How do you set up a table data region?
Otherwise your reply makes perfect sense.
Thanks!
"toolman" wrote:
> On Oct 18, 11:14 am, Jeannie <Jean...@.discussions.microsoft.com>
> wrote:
> > I'm using SQL Server Reporting Services and need to generate a report listing
> > addresses. I have to accomodate two address lines. How can I set it up so
> > that if there is no second address no blank line appears on the report
> I'm assuming you are using a table data region. Set the Hidden
> property for the second line/row to somthing like this:
> =IIF(addressline2 = Nothing, True, False)
> Make sure you set the property for the row and not individual cells
> otherwise it will not close the white space left by the hidden row.
> HTH
> toolman
>|||I have figured out how to set up a data table, and placed the iif statement
on the address2 line. But when I preview the report I get this message: The
table is in a list that has no group expressions defined for it. The ous a
data region in a list, the list must have group expressions".
Can you advise me which property sets the group expressions?
Thanks!
"Jeannie" wrote:
> I'm using SQL Server Reporting Services and need to generate a report listing
> addresses. I have to accomodate two address lines. How can I set it up so
> that if there is no second address no blank line appears on the report|||On Oct 22, 10:34 am, Jeannie <Jean...@.discussions.microsoft.com>
wrote:
> I have figured out how to set up a data table, and placed the iif statement
> on the address2 line. But when I preview the report I get this message: The
> table is in a list that has no group expressions defined for it. The ous a
> data region in a list, the list must have group expressions".
> Can you advise me which property sets the group expressions?
> Thanks!
>
> "Jeannie" wrote:
> > I'm using SQL Server Reporting Services and need to generate a report listing
> > addresses. I have to accomodate two address lines. How can I set it up so
> > that if there is no second address no blank line appears on the report- Hide quoted text -
> - Show quoted text -
Jeanie,
This would be much easier if you based your entire report on a table
as opposed to a bunch of text boxes in a list. In my experience with
reporting, tables are the way to go. If you're not sure whether you
can do this, post an example of how you want the report to look and
maybe I can help.
Assuming we can do this in just a table:
Let's start from a blank slate.
In Layout mode, delete the table, then delete the list so that your
report body is empty.
Now, instead of placing your table inside a list, just place a table
directly into the report body.
Drag and drop your fields from the Datasets pane into the table. You
can add additional rows and columns as necessary.
Once you've got all the fields in place, right click the detail row
handle (gray box with three horizontal lines in it on the far left
side of the table) that contains your second address.
>From the drop down, select Properties to open the Properties pane.
Find and expand the Visibility property by clicking the plus sign
box.
Now click the right side of the Hidden property where it says False.
Then click the drop down arrow and select the <Expression> option.
In the text window of the resulting Edit Expression dialog, overwrite
'False' with the IIF statement. Make sure to include the = sign.
Click OK and then preview the report.
With any kind of luck it should perform as we hope.|||I've managed to create the table, but the blank address lines are still
printing.
Here is the formula I've used: =IIF(Fields!partneraddress2.Value = Nothing,
True, False). I've attached this to the row property, not the field.
I want the report to look like this
ABC Recycling
555 Adams Drive
Marietta, GA 30303 USA
Since there is no partneraddress2, the line is hidden( There is actually a
partneraddress3, but I assume I would follow the same procedure as address2)
Thanks for your help
Jeannie
"toolman" wrote:
> On Oct 22, 10:34 am, Jeannie <Jean...@.discussions.microsoft.com>
> wrote:
> > I have figured out how to set up a data table, and placed the iif statement
> > on the address2 line. But when I preview the report I get this message: The
> > table is in a list that has no group expressions defined for it. The ous a
> > data region in a list, the list must have group expressions".
> >
> > Can you advise me which property sets the group expressions?
> > Thanks!
> >
> >
> >
> > "Jeannie" wrote:
> > > I'm using SQL Server Reporting Services and need to generate a report listing
> > > addresses. I have to accomodate two address lines. How can I set it up so
> > > that if there is no second address no blank line appears on the report- Hide quoted text -
> >
> > - Show quoted text -
> Jeanie,
> This would be much easier if you based your entire report on a table
> as opposed to a bunch of text boxes in a list. In my experience with
> reporting, tables are the way to go. If you're not sure whether you
> can do this, post an example of how you want the report to look and
> maybe I can help.
> Assuming we can do this in just a table:
> Let's start from a blank slate.
> In Layout mode, delete the table, then delete the list so that your
> report body is empty.
> Now, instead of placing your table inside a list, just place a table
> directly into the report body.
> Drag and drop your fields from the Datasets pane into the table. You
> can add additional rows and columns as necessary.
> Once you've got all the fields in place, right click the detail row
> handle (gray box with three horizontal lines in it on the far left
> side of the table) that contains your second address.
> >From the drop down, select Properties to open the Properties pane.
> Find and expand the Visibility property by clicking the plus sign
> box.
> Now click the right side of the Hidden property where it says False.
> Then click the drop down arrow and select the <Expression> option.
> In the text window of the resulting Edit Expression dialog, overwrite
> 'False' with the IIF statement. Make sure to include the = sign.
> Click OK and then preview the report.
> With any kind of luck it should perform as we hope.
>|||On Oct 23, 1:04 pm, Jeannie <Jean...@.discussions.microsoft.com> wrote:
> I've managed to create the table, but the blank address lines are still
> printing.
> Here is the formula I've used: =IIF(Fields!partneraddress2.Value = Nothing,
> True, False). I've attached this to the row property, not the field.
> I want the report to look like this
> ABC Recycling
> 555 Adams Drive
> Marietta, GA 30303 USA
> Since there is no partneraddress2, the line is hidden( There is actually a
> partneraddress3, but I assume I would follow the same procedure as address2)
> Thanks for your help
> Jeannie
>
> "toolman" wrote:
> > On Oct 22, 10:34 am, Jeannie <Jean...@.discussions.microsoft.com>
> > wrote:
> > > I have figured out how to set up a data table, and placed the iif statement
> > > on the address2 line. But when I preview the report I get this message: The
> > > table is in a list that has no group expressions defined for it. The ous a
> > > data region in a list, the list must have group expressions".
> > > Can you advise me which property sets the group expressions?
> > > Thanks!
> > > "Jeannie" wrote:
> > > > I'm using SQL Server Reporting Services and need to generate a report listing
> > > > addresses. I have to accomodate two address lines. How can I set it up so
> > > > that if there is no second address no blank line appears on the report- Hide quoted text -
> > > - Show quoted text -
> > Jeanie,
> > This would be much easier if you based your entire report on a table
> > as opposed to a bunch of text boxes in a list. In my experience with
> > reporting, tables are the way to go. If you're not sure whether you
> > can do this, post an example of how you want the report to look and
> > maybe I can help.
> > Assuming we can do this in just a table:
> > Let's start from a blank slate.
> > In Layout mode, delete the table, then delete the list so that your
> > report body is empty.
> > Now, instead of placing your table inside a list, just place a table
> > directly into the report body.
> > Drag and drop your fields from the Datasets pane into the table. You
> > can add additional rows and columns as necessary.
> > Once you've got all the fields in place, right click the detail row
> > handle (gray box with three horizontal lines in it on the far left
> > side of the table) that contains your second address.
> > >From the drop down, select Properties to open the Properties pane.
> > Find and expand the Visibility property by clicking the plus sign
> > box.
> > Now click the right side of the Hidden property where it says False.
> > Then click the drop down arrow and select the <Expression> option.
> > In the text window of the resulting Edit Expression dialog, overwrite
> > 'False' with the IIF statement. Make sure to include the = sign.
> > Click OK and then preview the report.
> > With any kind of luck it should perform as we hope.- Hide quoted text -
> - Show quoted text -
Now I'm perplexed. I know it works, having done it in most of my
address list reports. I even copied and pasted your expression
directly into one of my existing reports and it worked (all I changed
was the field name). You might try ="" instead of =Nothing but if
your field is truly empty, this shouldn't make a difference. Beyond
that I'm stumped. Sorry|||Sory about the delay in responding. But i didn't return to the client's site
until today. I am happy to say that with your help, I figured it out. The
reason it didn't work is because the cells, that appeared to be null,
actually had 40 spaces in them! Today I ran a query and looked at the data
and noticed that all the 'empty" cells had a block highlighted when I
positioned my cursor on the cell. So I changed the Iif statement to
acknowledge the spaces and it worked.
Thank you so much for your help!
"toolman" wrote:
> On Oct 23, 1:04 pm, Jeannie <Jean...@.discussions.microsoft.com> wrote:
> > I've managed to create the table, but the blank address lines are still
> > printing.
> >
> > Here is the formula I've used: =IIF(Fields!partneraddress2.Value = Nothing,
> > True, False). I've attached this to the row property, not the field.
> >
> > I want the report to look like this
> > ABC Recycling
> > 555 Adams Drive
> > Marietta, GA 30303 USA
> >
> > Since there is no partneraddress2, the line is hidden( There is actually a
> > partneraddress3, but I assume I would follow the same procedure as address2)
> >
> > Thanks for your help
> > Jeannie
> >
> >
> >
> > "toolman" wrote:
> > > On Oct 22, 10:34 am, Jeannie <Jean...@.discussions.microsoft.com>
> > > wrote:
> > > > I have figured out how to set up a data table, and placed the iif statement
> > > > on the address2 line. But when I preview the report I get this message: The
> > > > table is in a list that has no group expressions defined for it. The ous a
> > > > data region in a list, the list must have group expressions".
> >
> > > > Can you advise me which property sets the group expressions?
> > > > Thanks!
> >
> > > > "Jeannie" wrote:
> > > > > I'm using SQL Server Reporting Services and need to generate a report listing
> > > > > addresses. I have to accomodate two address lines. How can I set it up so
> > > > > that if there is no second address no blank line appears on the report- Hide quoted text -
> >
> > > > - Show quoted text -
> >
> > > Jeanie,
> > > This would be much easier if you based your entire report on a table
> > > as opposed to a bunch of text boxes in a list. In my experience with
> > > reporting, tables are the way to go. If you're not sure whether you
> > > can do this, post an example of how you want the report to look and
> > > maybe I can help.
> > > Assuming we can do this in just a table:
> > > Let's start from a blank slate.
> > > In Layout mode, delete the table, then delete the list so that your
> > > report body is empty.
> > > Now, instead of placing your table inside a list, just place a table
> > > directly into the report body.
> > > Drag and drop your fields from the Datasets pane into the table. You
> > > can add additional rows and columns as necessary.
> > > Once you've got all the fields in place, right click the detail row
> > > handle (gray box with three horizontal lines in it on the far left
> > > side of the table) that contains your second address.
> > > >From the drop down, select Properties to open the Properties pane.
> > > Find and expand the Visibility property by clicking the plus sign
> > > box.
> > > Now click the right side of the Hidden property where it says False.
> > > Then click the drop down arrow and select the <Expression> option.
> > > In the text window of the resulting Edit Expression dialog, overwrite
> > > 'False' with the IIF statement. Make sure to include the = sign.
> > > Click OK and then preview the report.
> > > With any kind of luck it should perform as we hope.- Hide quoted text -
> >
> > - Show quoted text -
> Now I'm perplexed. I know it works, having done it in most of my
> address list reports. I even copied and pasted your expression
> directly into one of my existing reports and it worked (all I changed
> was the field name). You might try ="" instead of =Nothing but if
> your field is truly empty, this shouldn't make a difference. Beyond
> that I'm stumped. Sorry
>

Address field problem.

I have a number of text boxes (one on top of the other) to display the addresses in my report. I have Address_name, Address1, Address2, Address_city, Address_State, Address_PostalCode - Each with its own text box except for the city, state, and postal code for which I just combined the fields. However, I have many who do not have any data for Address2 and don't want an empty line in the address. Is there an expression I can use to display the next line or move/shift up the remaining parts of the address? Would the iif and isnothing expressions be helpful, and if so, how do I write it correctly?

If it is ok to put them all into one textbox, you could do this:

=Fields!Address_name.Value
& chr(10) & Fields!Address1.Value
& IIf(Len(Fields!Address2.Value) > 0, chr(10) & Fields!Address2.Value, "")
& chr(10) & Fields!Address_city.Value & ", " & Fields!Address_State.Value & " " & Fields!Address_PostalCode.Value

If they have to have their own textbox and you just want to move it up if the Address2 is empty, then you could try something like this.

In the expression for the Address2 textbox:

=IIf(Len(Fields!Address2.Value) > 0, Fields!Address2.Value, Fields!Address_city.Value & ", " & Fields!Address_State.Value & " " & Fields!Address_PostalCode.Value)

Then, in the expression for the City/State/Postal Code:

=IIf(Len(Fields!Address2.Value) > 0, Fields!Address_city.Value & ", " & Fields!Address_State.Value & " " & Fields!Address_PostalCode.Value, "")

Hope this helps.

Jarret

|||

Thanks mate, I'll give it a try.!

What does "Len" do?

|||

Len gives you then length of the object you pass in. In this case, if the Address2 is NULL or an empty string, Len will return 0 and it will be skipped with the logic in the code.

Let me know if that fixes your issue.

Jarret

|||

I see, thanks for the info. I will try this in about an hour or so, I'll post back and give you an update.

Thanks again Jarret,

Bill

AddParameter to SubReport ? <VbScript>

Hello,

Can i pass parameter to a subreport inside a report with addParameter ?
Like that:

Dim webSource0
Set webSource0 = CreateObject("WebReportSource.WebReportSource")
webSource0.ReportSource = webBroker
webSource0.URL = "http://localhost/product.rpt"
webSource0.PromptOnRefresh = True
webSource0.AddParameter "password0", "cria"
webSource0.AddParameter "user0", "sa"
webSource0.AddParameter "prompt0", "1"
webSource0.AddParameter "subreport.password0", "cria" ?
webSource0.AddParameter "subreport.user0", "sa" ?

when i try to view the report with crystal viewer, it prompts for the
database settings of the subreport !

i'm using cr8.5 with oledb connection and vbscriptsee thread http://www.dev-archive.com/forum/showthread.php?t=315611

I think you have the same problem, caused by not understanding that each subreport's query must establish its own permissions with the server.

Dave

Additional area in main report after sub-report

I have a main report that has 17 sub-reports in it. I have had some problems with additional space in the main report, after sub-reports that have no data in them. In the actual sub reports I have expressions set up for each line in the tables to hide the row if there is an empty string in it. (IIF(Fields!FieldName.Value = "", True, False)).

Is there a way to set up in the main report is the sub report has no data in it to hide the sub-report, and not show the additional space in them. I have tried putting the sub reports in the main report by themselves, and also put them into a table, and have encountered the same results each time.

I did find a site that suggests referencing a Field in the expression for that row, but this would involve us changing the stored procedure and adding additional Fields into the main report stored procedure. Also each sub-report has its own stored procedure to return the data needed for that sub report.

Any ideas?

Hi,

Though the post is a year old and I dint find any answer to ur post so I was just wondering if you were able to find out a fix for this. I am facing a similar problem. I also have a subreport that has some fields where as their visibility is dependent on a parameter. If that's set to yes then the field will show up else not. I can acheive this in the subreport but not in the main report. THe main report doesn't show the field at all. Any suggestions?

Thanks,

Rashi

|||

Another way is to create a custom assembly in .net and have a shared variable. In the subreport, set the shared variable value to 0 based on your condition. In your main report, refer to that shared variable and set the visibility of the subreport based on that.

Shyam

|||

Thanks!

Rgds,

Rashi

|||Can you please mark the post as answer?|||Sure, I can but I dint implement it since I had some other problem related to my report..

Additional area in main report after sub-report

I have a main report that has 17 sub-reports in it. I have had some problems with additional space in the main report, after sub-reports that have no data in them. In the actual sub reports I have expressions set up for each line in the tables to hide the row if there is an empty string in it. (IIF(Fields!FieldName.Value = "", True, False)).

Is there a way to set up in the main report is the sub report has no data in it to hide the sub-report, and not show the additional space in them. I have tried putting the sub reports in the main report by themselves, and also put them into a table, and have encountered the same results each time.

I did find a site that suggests referencing a Field in the expression for that row, but this would involve us changing the stored procedure and adding additional Fields into the main report stored procedure. Also each sub-report has its own stored procedure to return the data needed for that sub report.

Any ideas?

Hi,

Though the post is a year old and I dint find any answer to ur post so I was just wondering if you were able to find out a fix for this. I am facing a similar problem. I also have a subreport that has some fields where as their visibility is dependent on a parameter. If that's set to yes then the field will show up else not. I can acheive this in the subreport but not in the main report. THe main report doesn't show the field at all. Any suggestions?

Thanks,

Rashi

|||

Another way is to create a custom assembly in .net and have a shared variable. In the subreport, set the shared variable value to 0 based on your condition. In your main report, refer to that shared variable and set the visibility of the subreport based on that.

Shyam

|||

Thanks!

Rgds,

Rashi

|||Can you please mark the post as answer?|||Sure, I can but I dint implement it since I had some other problem related to my report..