Report Services Bug? -Sub Report and Page break!

When the main report calls the meeting sub report, if the content of the sub
report is longer than the pare space on the current page, it will jump to the
next page without filling the rest space on the current page.
Is it a bug of Report Services?Any ideas?
"CEO" wrote:
> When the main report calls the meeting sub report, if the content of the sub
> report is longer than the pare space on the current page, it will jump to the
> next page without filling the rest space on the current page.
> Is it a bug of Report Services?|||No, this is expected behavior.
When you design your sub-reports, you need to make sure that you set them to
be right WIDTH. If in my main report, the width of my sub-report is 5
inches, then in my sub-report, I set my margins to be ZERO for left, right,
top and bottom. Next, I set the body width of my subreport to be 5 inches.
I hope this helps.
=-Chris
"CEO" <CEO@.discussions.microsoft.com> wrote in message
news:3C48CA63-49E1-48DB-99E5-245B1A0144B4@.microsoft.com...
> When the main report calls the meeting sub report, if the content of the
> sub
> report is longer than the pare space on the current page, it will jump to
> the
> next page without filling the rest space on the current page.
> Is it a bug of Report Services?

report server parameters

Hi!

I have a report (*.rdl) in my report server, and this report calls a dataset (by an ObjectDataSource of the reportviewer) that has query parameters. I try to run the report but the following error appears:

An error has occurred during report processing.

Query execution failed for data set 'DataSet3_generar'.

Must declare the scalar variable "@.Param1".

I read that, when you create dataset parameters that the report should inherits these parameters automatically. And when you upload the file into the report server, when you select it in the report manager and see its properties, it should appear a link of parameters.

But my report doesn't inherits the dataset parameters automatically, I tried to do it manually using the properties window of visual studio, but It does show the link of parameters in the report manager.. but doesn't link the report parameters with the dataset query parameters.

I don't know if the problem is that I am using SQL server Express, because the Report Builder doesn't work properly (with all its functions)

So, is there a way of solve this using SQL server express? Or I need SQL server 2005?

If there's nothing to do with the server.. then how can I made the link between dataset and report item using visual studio?

Thanks! Any suggestion will help me a lot!

I also did have that sometimes. There is a refresh button at the dataset icons which will populate the used paramters from the dataset to the reports automatically. I always do the following, executing the query, then refreshing the reports to have the whole thing in sync and thats it. If this is a sql exeception brought to you by the SQL Server, it could be that you are using a variable in a dynamic sql statement which is not defined in the dyna′mic sql scope. Make sure that this does not apply.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de|||

Hi thanks for answering....

I have a query in my dataser similar to this...

select field1, field2 from table where field3 = @.param1

is this a dynamic sql statement? and where do I have to defined it for it to be in a dynamic sql scope?

I already try what you said about refreshing the datasources and then refreshing the report, but it doesn't populate the parameters to the report....

I am using visual basic. net web development I don't know if its diferent....

|||

I already find the problem ... what I had to to was to write in the *.rdl file that the parameter was used in a query and that it was a query parameter.

the code of my report is the following... hope it help someone...

<?xml version="1.0" encoding="utf-8"?>

<Report xmlns="http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition" xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
<DataSources>
<DataSource Name="Def_prescoConnectionString">
<ConnectionProperties>
<ConnectString />
<DataProvider>SQL</DataProvider>
</ConnectionProperties>
<rd:DataSourceID>fb5b58f0-f99b-4f88-9388-56107932e009</rd:DataSourceID>
</DataSource>
</DataSources>
<BottomMargin>1in</BottomMargin>
<RightMargin>1in</RightMargin>
<ReportParameters>
<ReportParameter Name="Fecha1">

<UsedInQuery>Auto</UsedInQuery>

<DataType>DateTime</DataType>
<AllowBlank>true</AllowBlank>
<Prompt>fecha1</Prompt>

</ReportParameter>

**** above -- the part of report parameter used in query

**** below -- the part of query parameters

<DataSets>
<DataSet Name="DataSet1_generar">
<rd:DataSetInfo>
<rd:TableAdapterGetDataMethod>GetData</rd:TableAdapterGetDataMethod>
<rd:DataSetName>DataSet1</rd:DataSetName>
<rd:TableAdapterFillMethod>Fill</rd:TableAdapterFillMethod>
<rd:TableAdapterName>generarTableAdapter</rd:TableAdapterName>
<rd:TableName>generar</rd:TableName>
</rd:DataSetInfo>
<Query>
<rd:UseGenericDesigner>true</rd:UseGenericDesigner>

<QueryParameters>
<QueryParameter Name="@.fecha1">
<Value>=Parameters!Fecha1.Value</Value>
</QueryParameter>
</QueryParameters>

<CommandText>select id_form, sum(cant_latas) from generar where fecha = @.fecha1 group by id_form</CommandText>
<DataSourceName>Def_prescoConnectionString</DataSourceName>
</Query>

|||

It did help me! Thanks!

I had that same problem. I noticed in the Dataset designer you can add fields and parameters. Sometimes it fills them in automatically sometimes it doesn't.

Report Running Slowly

I have a report in SQL Reporting Services 2005 which calls a stored
proc and the report takes a very long time to run and sometimes
returns zero records. But when i run the stored proc in query analyzer
it takes about 4 seconds.
I have checked the execution log on the RS using the below sql:
Select * from ExecutionLog with (nolock) order by TimeStart DESC
It shows that i have a large amount of time for the dataretrieval
(601309ms, about 10mins) and does not return any records.
TimeDataRetrieval TimeProcessing TimeRendering
Source Status ByteCount RowCount
601309 2227
3 1 rsSuccess 4916 0
The weird thing is that when i run it in query analyzer, i get about
400 records in 4 seconds !!
I dont understand what RS is doing to take up so much time like this
to retrieve data.
The report is very simple - it basically returns the records straight
out into a table.
Can anyone suggest where to look or how to troubleshoot this problem?On Jun 27, 9:19 pm, Mark.J.Br...@.gmail.com wrote:
> I have a report in SQL Reporting Services 2005 which calls a stored
> proc and the report takes a very long time to run and sometimes
> returns zero records. But when i run the stored proc in query analyzer
> it takes about 4 seconds.
> I have checked the execution log on the RS using the below sql:
> Select * from ExecutionLog with (nolock) order by TimeStart DESC
> It shows that i have a large amount of time for the dataretrieval
> (601309ms, about 10mins) and does not return any records.
> TimeDataRetrieval TimeProcessing TimeRendering
> Source Status ByteCount RowCount
> 601309 2227
> 3 1 rsSuccess 4916 0
> The weird thing is that when i run it in query analyzer, i get about
> 400 records in 4 seconds !!
> I dont understand what RS is doing to take up so much time like this
> to retrieve data.
> The report is very simple - it basically returns the records straight
> out into a table.
> Can anyone suggest where to look or how to troubleshoot this problem?
Of course, SSRS has some overhead in being a service as opposed to
directly being a part of the database (as w/a query). Also, it
accesses IIS, etc (which adds overhead). The first thing that I can
suggest doing is to run the Database Engine Tuning Advisor against the
query used in the report and implement any indexes that it suggests.
Also if you are using report parameters, you should verify that they
are linked correctly in the datasets (via Data view -> Edit Dataset
[...] -> Parameters tab). Also, verify that there are not other
processes running on the machine that are consuming excessive
resources (though this could be a long shot). Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant|||How long does it take from the development environment versus the server?
Bruce Loehle-Conger
MVP SQL Server Reporting Services
<Mark.J.Brown@.gmail.com> wrote in message
news:1182997161.429465.190340@.a26g2000pre.googlegroups.com...
>I have a report in SQL Reporting Services 2005 which calls a stored
> proc and the report takes a very long time to run and sometimes
> returns zero records. But when i run the stored proc in query analyzer
> it takes about 4 seconds.
> I have checked the execution log on the RS using the below sql:
> Select * from ExecutionLog with (nolock) order by TimeStart DESC
> It shows that i have a large amount of time for the dataretrieval
> (601309ms, about 10mins) and does not return any records.
> TimeDataRetrieval TimeProcessing TimeRendering
> Source Status ByteCount RowCount
> 601309 2227
> 3 1 rsSuccess 4916 0
> The weird thing is that when i run it in query analyzer, i get about
> 400 records in 4 seconds !!
> I dont understand what RS is doing to take up so much time like this
> to retrieve data.
> The report is very simple - it basically returns the records straight
> out into a table.
> Can anyone suggest where to look or how to troubleshoot this problem?
>|||On Jun 28, 1:29 pm, "Bruce L-C [MVP]" <bruce_lcNOS...@.hotmail.com>
wrote:
> How long does it take from the development environment versus the server?
The report is scheduled to run out of hours, when no other tasks are
scheduled to run (in SQL or Reporting Services or Windows).
Most scheduled reports work correctly and contain data. Occassionally
this report will return 0 rows.
The only common denominator between the reports that return 0 rows is
they always exceed 600,000 in the TimeDataRetrieval column.|||Are you able to run it from the development environment as a test?
It could be that you are getting into some locking issues when this is
running.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
<Mark.J.Brown@.gmail.com> wrote in message
news:1183003249.202007.213580@.x35g2000prf.googlegroups.com...
> On Jun 28, 1:29 pm, "Bruce L-C [MVP]" <bruce_lcNOS...@.hotmail.com>
> wrote:
>> How long does it take from the development environment versus the server?
> The report is scheduled to run out of hours, when no other tasks are
> scheduled to run (in SQL or Reporting Services or Windows).
> Most scheduled reports work correctly and contain data. Occassionally
> this report will return 0 rows.
> The only common denominator between the reports that return 0 rows is
> they always exceed 600,000 in the TimeDataRetrieval column.
>|||On Jun 29, 10:53 am, "Bruce L-C [MVP]" <bruce_lcNOS...@.hotmail.com>
wrote:
> Are you able to run it from the development environment as a test?
> It could be that you are getting into some locking issues when this is
> running.
Same problem in development environment.
As to the live data, there should be no other processes running at
that time, or users accessing the data.
Mark|||I have heard about but never seen an issue where SQL from RS ends up using a
different query plan.
Try one of the two methods in this link to force a recompile:
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/b90deb27-0099-4fe7-ba60-726af78f7c18.htm
Bruce Loehle-Conger
MVP SQL Server Reporting Services
<Mark.J.Brown@.gmail.com> wrote in message
news:1183337771.100767.292930@.z28g2000prd.googlegroups.com...
> On Jun 29, 10:53 am, "Bruce L-C [MVP]" <bruce_lcNOS...@.hotmail.com>
> wrote:
>> Are you able to run it from the development environment as a test?
>> It could be that you are getting into some locking issues when this is
>> running.
> Same problem in development environment.
> As to the live data, there should be no other processes running at
> that time, or users accessing the data.
> Mark
>|||On Jul 3, 1:58 am, "Bruce L-C [MVP]" <bruce_lcNOS...@.hotmail.com>
wrote:
> I have heard about but never seen an issue where SQL from RS ends up usin=g a
> different query plan.
> Try one of the two methods in this link to force a recompile:
> ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/b90deb27-0099-4fe7-ba60-7=26=ADaf78f7c18.htm
Can't get that link to work. Are you able to provide a topic title,
or a microsoft.com link?|||This isn't the MS link but it describes someone who had a performance
problem with the stored procedure. What you want to do is use the With
Recompile option with the stored procedure.
http://articles.techrepublic.com.com/5100-9592_11-5662581.html
Bruce Loehle-Conger
MVP SQL Server Reporting Services
<Mark.J.Brown@.gmail.com> wrote in message
news:1183506487.200772.282060@.j4g2000prf.googlegroups.com...
On Jul 3, 1:58 am, "Bruce L-C [MVP]" <bruce_lcNOS...@.hotmail.com>
wrote:
> I have heard about but never seen an issue where SQL from RS ends up using
> a
> different query plan.
> Try one of the two methods in this link to force a recompile:
> ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/b90deb27-0099-4fe7-ba60-726­af78f7c18.htm
Can't get that link to work. Are you able to provide a topic title,
or a microsoft.com link?