Report not using pagination properly

I have a portrait report that has 1 table with 1 hidden group header (for the Page Header to reference fields from) and 36 detail rows. My problem is that pagination is not working properly. Depending on the length of data in the detail rows, the report tends to push majority of data on the next page(s) which leaves a lot of blank lines or blank page at the beginning of each group of data. I want the data to complete the first page before rolling over to the next page. That's why I have 36 individual detail rows instead of all the data in 1 detail row. I've tried adjusting the page length, but it doesn't seem to work all that well. I either get all the report on one page (which is fine when viewing online, but data is cut off when printing) or inadequate pagination. I do not have any white space at the bottom of the table and the page footer. Anyone have any ideas?

Thanks,

T

Have you tried setting KeepTogether=false on your table? The behavior your describing sounds like it's set to true, which would mean that if the table can't fit completely on the current page, but will fit on the next page, it will move the table to the next page.

You shouldn't need to specify the detail rows individually if you're populating from a data source!|||Yes, KeepTogether is false and yes I am using a shared data source and data is queried using Stored Procedure.|||Well it seems the problem lies within the Group Header row. I removed that row and pagination works properly now, but I lose the ability to call fields in my page header when the report spans more than one page.|||

Ok, I found a solution for showing Field values in Page Header even when the report spans more than one page(but you don't know at what point the report spans to the next page). It's a combination of several solutions:

1) Create hidden textbox(es) with the field value you're wanting on every line where the page might span to the next page.

textbox1

textbox2

textbox3

2) Create a function in code that will reference each hidden textbox(es) by name

Shared Function Header(reportItems as ReportItems) as string
Dim final as string
If ReportItems!textbox1.value <> "" Then
final = ReportItems!textbox1.value
Else If ReportItems!textbox2.value <> "" Then
final = ReportItems!textbox2.value
Else If ReportItems!textbox3.value <> "" Then
final = ReportItems!textbox3.value
End If
Return final
End Function

3) In Page Header call function, pass (ReportItems)

=Code.Header(ReportItems)

The reason for the function is because you can't reference multiple ReportItems in Page Header textbox, so you have to determine which hidden textbox has a value on that page and return that value to the calling Header textbox. Hope this helps....

sql

Report Model - Custom Code or Assembly Reference

Hi, I'm trying to create a new Report Model Project, but I have a problem. The problem is that I have a one custom column in the database table, lets say column's name is 'Test', with encoded data, so I need to add a new field to the generated model, lets say 'Test Decoded', and use a some method from my custom assembly to decode the data in the expression designer. I don't see how to use a method from my added custom assembly in the expression designer, also I was browsing Google many hours without an result. Also Data - > Add New Datasources -> Object throws an exception in the VS2005 (Report Model Project). Maybe someone can help me. Thanks

HI,Maryan:

Not possible as far as i know. The Report Model used for the Report Builder to generate ad-hoc report.

I don't think you could use custom assembly in the Report Model. I suggest the you to do the decode job in the SQL Server side.

If i misunderstand you about your question, please feel free to correct me and i will try to help you with more information.

I hope the above information will be helpful. If you have any issues or concerns, please let me know. It's my pleasure to be of assistance

|||

Hi Rex Lin, thank you for a reply. Exactly - I need to create a Models to enable my customers to create own specific reports by the Report Builder (nice feature of the SSRS 2005 from the first view). That is why we have selected this solution from Microsoft.

Actually this column is implementation of the Custom Fields of the some model, so in this column are decoded X custom columns, as result from the real column in the table - ColumnA, we need to create dynamic columns ColumnA1, ColumnA2, ColumnA3... based on the decoded content of the column ColumnA. So Model on the server side (SSRS) should contain all these dynamic columns (if some doesn't exist in the decoded column it will be Null). Unfortunaly we received this DB from the other team and we can't change the architecture of database, because there are real projects that already use it. It is possible to implement Custom Fields in the more accurate way, but it can't be a subject of discussion...

Ok, you wrote that it is not possible. But, do you know that if we will create Report Server Project, create some datasource based on a some query, then add Reference to the custom assembly, then we can add custom fields to the datasource and use methods from the custom assembly in the Expression and it works! It also requires to deploy (copy) custom assembly to the both folders in 'C:\Program Files\...', one for the Visual Studio and the next one for the SSRS 2005. So, the SSRS support it - it can execute a some set of the predefined functions in the Expression or to call a custom assembly without problems. So, why it doesn't work in the Report Model - I don't know. It is more - Report Model project contains context menu "New Assembly Reference..." in the solution explorer, also there is menu at the top Data->Add Datasource->Object Datasource (this is exactly what I need), but it always crashes after the first click on the Next button. I'm sure - this is a bug. An any report enginemust supports custom code - it is clear. Maybe development team already working on the Sql Server 2008, 2011... and do not have a time to complete existing services...

Please let me know if you have a some ideas, you have more detailed description of my problem now. Also maybe you are related to the development team and you can ask them directly ;) Please note, the database server uses Sql Server 2000, so we can not use a some features like Table-Valued Functions or Managed Code on the server side. Also a custom Data Processing Extension isn't good solution for me (theoretically it is possible to parse command query, and if it is related to the table with decoded column..., but I do not think it is good). Thank you again

|||

Also I have a new question. We moved our database to the SQL Server 2005. We can not use Table-Valued Function (TVF), because our origin table contains more than 200000 records, and TVF haven't access to the Select/Query Parameters (it can be much more powerful in this case), so to create a dynamic table with 200k records and then select 10-20 records for report will be really very bad idea. I created CLR based stored procedure and deployed it to the server, it works, but I don't know how to use it in the Report Model Project, because Data Source View asks only for tables and views... You suggested me to decode columns on the Server Side, my quesion is - how? I already have lost one week and I'm trying to to break an impasse.

|||

Ok, I found solution by myself. As usually it is not possible to receive an answer for non standard/simple question on these forums, even from Microsoft guys. If someone have the same problem - my solution is based on the one CLR based Table-Valued Function (TVF) that accept one parameter (ID of the row) and do all required custom logic, one additional Transact-SQL TVF that use first TVF and feature of the SQL server '... CROSS APPLY ...', actually it selects ID from the real DB table and pass it as the input parameter to the first TVF through CROSS APPLY, and one DB View that selects data from the second Transac-SQL TVF. In the model designer I used this view - it works ;)

Report Manager Error - Object reference not set to an instance of an object.

Hi,
I can browse to my ReportServer without a problem. But when I try to
browse to the Report Manager I get the following error - Object
reference not set to an instance of an object.
I checked the log file and I'm seeing the following (snippet)
Object reference not set to an instance of an object
at
Microsoft.ReportingServices.UI.RSWebServiceWrapper.GetReportServerAuthentic= ation()
I'm Running SQL Server 2000 SP4
SQL Reporting Services 2000 SP1 (I know...)
This has been working fine up to know but suddenly seems to have broken
down. I've seen 2 other posts of this nature on google groups but
neither with a resolution.
Can anyone help me out please'
Thanks,
Gear=F3idok, I got it.
It was a default HTTP header in the root folder in IIS that was set
that caused this. Removed this and left it blank and it worked fine
for me.
Gear=F3id
Gear=F3id wrote:
> Hi,
> I can browse to my ReportServer without a problem. But when I try to
> browse to the Report Manager I get the following error - Object
> reference not set to an instance of an object.
> I checked the log file and I'm seeing the following (snippet)
> Object reference not set to an instance of an object
> at
> Microsoft.ReportingServices.UI.RSWebServiceWrapper.GetReportServerAuthent=ication()
> I'm Running SQL Server 2000 SP4
> SQL Reporting Services 2000 SP1 (I know...)
> This has been working fine up to know but suddenly seems to have broken
> down. I've seen 2 other posts of this nature on google groups but
> neither with a resolution.
> > Can anyone help me out please'
> > Thanks,
> Gear=F3id

Report item expressions can only refer to other report items within the same grouping scope or a

I still don't get this. I want to reference another textbox. I've tried these expressions below and get the same error. For instance, I'm referencing this from textbox6. I've put the data in a group, doesn't work. How do I reference different reportitems? Thanks

=ReportItems!textbox15

=ReportItems!ThresholdIncome.value

=ReportItems!ThresholdIncome.value,"table1_Group1"

You should be able to reference textboxes in the same or a containing scope. This first expression would return you the textbox object. The second one should work if ThresholdIncome exists in the same or a containing scope. You can't explicitly specify the scope of a report item. Therefore the 3rd one wouldn't work. What error did you get with these expressions?|||

Fang - Thanks for your help. Error I'm getting is what is in the subject -

Report item expressions can only refer to other report items within the same grouping scope or a containing grouping scope.

So does that mean I need to add a group and the textboxes can only know about each other in that group, but not any other textboxes on the report? That is what I don't understand. If it's all the same xml, why don't the controls know about each other?

|||You can refer to textboxes in that group, and the ones in the containing scope of that group (i.e. outer groups all the way up to the report level). But you can't refer to textboxes in an inner group. The reason is that when the report is rendered (combining layout information with data), there will be multiple instances of the inner groups. For example, if the outer grouping is by country and the inner grouping is by state, you'll get multiple states for USA. We wouldn't know from which inner group instance you want to get the textbox value.

Report Headers

I'm new to Reporting Services, but am familiar with other reporting tools. My question is, is there really no way to reference a field in a page header? If so, I suppose you could repeat some header information on subsequent pages from the detail section of the report using a text box, but how is that done because when I check the property box "Repeat report item with data region on every page" nothing happens. What is the best way to repeat header information on every page that needs to contain field values/information?

Thanks for any help you can offer.

Referencing a field from page header/footer is not supported currently. When you set a textbox to repeat on each page, you need to pick a data region for the textbox to repeat with. Suppose you have a table that'd span multiple pages, set the data region to the table. Or you can select the table header/footer row, and set it to repeat on each page.|||

You can use a field in the report header. Drop a field in the report body and make it invisible, if you don’t need it in the report body. Then drop a textbox on a header and type =ReportItems!TextboxName.Value (The textboxname is where your field is in the body)

Regards

|||

Thanks, GNDSP, but this solution seems to be unsuitable when the data you need repeated on each page comes from a different data set.
What do you do if the data being shown in the report body comes from a different data set than the header text?

I hope the next version of RS fixes this limitation of repeating data on multiple pages, as well as introducing a better way of integrating reports with your own code... the current "Code" tab in the report properties dialog box only supports Visual Basic and is hardly a suitable code editing window... how about "Code Behind" for reports, that also supports C#?

Thanks

Graham

Report Headers

I'm new to Reporting Services, but am familiar with other reporting tools. My question is, is there really no way to reference a field in a page header? If so, I suppose you could repeat some header information on subsequent pages from the detail section of the report using a text box, but how is that done because when I check the property box "Repeat report item with data region on every page" nothing happens. What is the best way to repeat header information on every page that needs to contain field values/information?

Thanks for any help you can offer.

Referencing a field from page header/footer is not supported currently. When you set a textbox to repeat on each page, you need to pick a data region for the textbox to repeat with. Suppose you have a table that'd span multiple pages, set the data region to the table. Or you can select the table header/footer row, and set it to repeat on each page.|||

You can use a field in the report header. Drop a field in the report body and make it invisible, if you don’t need it in the report body. Then drop a textbox on a header and type =ReportItems!TextboxName.Value (The textboxname is where your field is in the body)

Regards

|||

Thanks, GNDSP, but this solution seems to be unsuitable when the data you need repeated on each page comes from a different data set.
What do you do if the data being shown in the report body comes from a different data set than the header text?

I hope the next version of RS fixes this limitation of repeating data on multiple pages, as well as introducing a better way of integrating reports with your own code... the current "Code" tab in the report properties dialog box only supports Visual Basic and is hardly a suitable code editing window... how about "Code Behind" for reports, that also supports C#?

Thanks

Graham

Report GenerationObject reference not set to an instance of an object

I started a new thread as I was able to get more information on the error Im getting when I attempt to generate the report in report manager. Ive seen some solutions but nothing seems to work for my particular case. I am hopefull that this can be resolved "WITHOUT" ininstalling and reinstalling SSRS so any information would be helpful.

Here is the Soure Error and Stack Trace info:

Server Error in '/Reports' Application.

---

Object reference not set to an instance of an object.

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.NullReferenceException: Object reference not set to an instance of an object.

Source Error:

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.

Stack Trace:

[NullReferenceException: Object reference not set to an instance of an object.]

Microsoft.Reporting.WebForms.WebRequestHelper.GetExceptionForMoreInformationNode(XmlNode moreInfo, XmlNamespaceManager namespaces) +18

Microsoft.Reporting.WebForms.WebRequestHelper.ExceptionFromWebResponse(Exception e) +358

Microsoft.Reporting.WebForms.ServerReport.ServerUrlRequest(Boolean isAbortable, String url, Stream outputStream, String& mimeType, String& fileNameExtension) +482

Microsoft.Reporting.WebForms.ServerReport.InternalRender(Boolean isAbortable, String format, String deviceInfo, NameValueCollection urlAccessParameters, Stream reportStream, String& mimeType, String& fileNameExtension) +958

Microsoft.Reporting.WebForms.ServerReportControlSource.RenderReport(String format, String deviceInfo, NameValueCollection additionalParams, String& mimeType, String& fileExtension) +84

Microsoft.Reporting.WebForms.ExportOperation.PerformOperation(NameValueCollection urlQuery, HttpResponse response) +143

Microsoft.Reporting.WebForms.HttpHandler.ProcessRequest(HttpContext context) +75

System.Web.CallHandlerExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute() +154

System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously) +64

Hi,

Can you tell us what you are exactly trying to do, this way we can find out were you could be potentially going wrong...

Can you also look at your event log or the reporting services log files -this will give you more detail of the error caused...

One possible cause could be your connection and dataset, check these out.

Hope this helps.

|||

I can log into the report manager no problem-- its when I try to generate any report that I have this issue. The only thing that changed is I had to take the database offline for about a week and then brought it back online. All other permissions, roles etc have remained the same. When I try and generate the report I get the error in the middle of the screen and things are out of place -- print icon is where the report should be so is the refresh icon. I have included the info from the event viewer-- thanks in advance for any info that may help me avoid having to uninstall and reinstall ssrs

Event code: 3005

Event message: An unhandled exception has occurred.

Event time: 3/27/2007 8:48:19 AM

Event time (UTC): 3/27/2007 2:48:19 PM

Event ID: 68829be2d597449ca27166b54403c3a1

Event sequence: 2677

Event occurrence: 98

Event detail code: 0

Application information:

Application domain: /LM/W3SVC/1/root/Reports-2-128189799508346487

Trust level: RosettaMgr

Application Virtual Path: /Reports

Application Path: C:\Program Files\Microsoft SQL Server\MSSQL.1\Reporting Services\ReportManager\

Machine name: HDSWEBK

Process information:

Process ID: 5360

Process name: w3wp.exe

Account name: NT AUTHORITY\NETWORK SERVICE

Exception information:

Exception type: NullReferenceException

Exception message: Object reference not set to an instance of an object.

Request information:

Request URL: http://............./Reports/Reserved.ReportViewerWebControl.axd?OpType=StyleSheet&Version=2005.090.2047.00

Request path: /Reports/Reserved.ReportViewerWebControl.axd

User host address: 128.138.29.6

User: AD\mcdermk

Is authenticated: True

Authentication Type: Negotiate

Thread account name: NT AUTHORITY\NETWORK SERVICE

Thread information:

Thread ID: 6

Thread account name: NT AUTHORITY\NETWORK SERVICE

Is impersonating: True

Stack trace: at Microsoft.Reporting.WebForms.WebRequestHelper.GetExceptionForMoreInformationNode(XmlNode moreInfo, XmlNamespaceManager namespaces)

at Microsoft.Reporting.WebForms.WebRequestHelper.ExceptionFromWebResponse(Exception e)

at Microsoft.Reporting.WebForms.ServerReport.ServerUrlRequest(Boolean isAbortable, String url, Stream outputStream, String& mimeType, String& fileNameExtension)

at Microsoft.Reporting.WebForms.ServerReport.GetStyleSheet(String styleSheetName)

at Microsoft.Reporting.WebForms.ReportServerStyleSheetOperation.PerformOperation(NameValueCollection urlQuery, HttpResponse response)

at Microsoft.Reporting.WebForms.HttpHandler.ProcessRequest(HttpContext context)

at System.Web.HttpApplication.CallHandlerExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute()

at System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously)

Custom event details:

For more information, see Help and Support Center at