Report: same

Hi There

We created a report from an Informix database stored procedure (with parameters). When we execute the stored procedure in the data page, it returns correct result set with column headings blank. In the datasets page, the field names are: ID, ID_1, ID_2, …

When I preview a report created from the dataset, all the fields shows the same value as field1 (ID). I have no idea on what happened, why it happened, and how to resolve it? Could you please help?

Thanks in advance.

Jane

It's been awhile since I have run into this, so apologies if this isn't exactly right. I believe this occurs when Reporting Services can't get the column metadata from the database, or when all the columns have the same base name (the table.column name may be unique, but the column name is not). So the mapping of values to columns doesn't work correctly.

The workaround for us was to make sure the database query aliased all columns to a unique name.

|||

Thanks for your reply.

You are right, our problem is Reporting Services can't get the column metadata from the stored procedure return result. In the data page, we select Text, and type:

execute procedure proc_name (?)

Because we use stored procedures instead of queries, not sure how to set the column names. Any ideas? Thanks a lot.

Cheers

Jane

Report timeout - long running SPROC

Hi all,
I have a SPROC that returns 773,923 rows and takes 13mins to execute in SQL
Query Analyzer.
I think I have increased all available timeout and session settings, but the
report returns page cannot be displayed.
The same happens when I try to create a snapshot.
Any ideas?
Thank you in advance.
Cheers
ShaneThis is a very bad idea. RS renders everything in RAM. Depending on the
format it can take varying amounts of time. If you try to render to PDF or
Excel (can Excel even take that many rows, I'm not sure what the limit is
now, it was 64K in Excel 2000) it will most likely never finish. What are
you trying to do, a human will never look at that many rows. If you are
trying to do data extraction then there are better technologies to use. HTML
is fast and might be possible but not sure what it buys you since this is
probably 100,000 rows of data. I suggest reading up on drill through, that
is a very good technique for navigating large amounts of data. If you
explain what you are trying to do there might be some other way to
accomplish it.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Shane" <s.dixon@.hia.asn.auNOSPAM> wrote in message
news:86D96692-5AD5-4BC0-953D-99A53D623AE3@.microsoft.com...
> Hi all,
> I have a SPROC that returns 773,923 rows and takes 13mins to execute in
> SQL
> Query Analyzer.
> I think I have increased all available timeout and session settings, but
> the
> report returns page cannot be displayed.
> The same happens when I try to create a snapshot.
> Any ideas?
> Thank you in advance.
> Cheers
> Shane|||Thanks for taking the time to read my post Bruce.
The report is an age trial balance for our entire organisation. So each line
(all 773,923 rows) are invoice lines.
It allows our snr management to see what debt are owed to our company.
I use a drill down so when the report is initially loaded you see each of my
company's regions (10 of them) and the sum of the money owed by age 30 Days,
60-90 Days, 120+ Days...
If you click on the + it expands to reveal the different general leger
accounts and the sum of the money owed by age 30 Days, 60-90 Days, 120+
Days...
If you click on the + again, it will display the person who owes the money
and the sum of the money owed by age 30 Days, 60-90 Days, 120+ Days...
If you click on the + again, it will display each of the invoice details..
The report is being rendered in HTML and I also want the execution snapshot
to be rendered in HTML as well.
Cheers,
Shane
"Bruce L-C [MVP]" wrote:
> This is a very bad idea. RS renders everything in RAM. Depending on the
> format it can take varying amounts of time. If you try to render to PDF or
> Excel (can Excel even take that many rows, I'm not sure what the limit is
> now, it was 64K in Excel 2000) it will most likely never finish. What are
> you trying to do, a human will never look at that many rows. If you are
> trying to do data extraction then there are better technologies to use. HTML
> is fast and might be possible but not sure what it buys you since this is
> probably 100,000 rows of data. I suggest reading up on drill through, that
> is a very good technique for navigating large amounts of data. If you
> explain what you are trying to do there might be some other way to
> accomplish it.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Shane" <s.dixon@.hia.asn.auNOSPAM> wrote in message
> news:86D96692-5AD5-4BC0-953D-99A53D623AE3@.microsoft.com...
> > Hi all,
> >
> > I have a SPROC that returns 773,923 rows and takes 13mins to execute in
> > SQL
> > Query Analyzer.
> >
> > I think I have increased all available timeout and session settings, but
> > the
> > report returns page cannot be displayed.
> >
> > The same happens when I try to create a snapshot.
> >
> > Any ideas?
> >
> > Thank you in advance.
> >
> > Cheers
> >
> > Shane
>
>|||OK, that is what I thought. Drill downs are great. Very intuitive.
Unfortunately they require all the data to be present. So although it is
great for the user it is not so a great a solution for speed. What I suggest
is to change this to limit the amount of data. Perhaps make it a combination
of drilldown and drillthrough. For instance, how much data is decreased if
you do a drillthrough for the involice details. Or perhaps the Person and
the invoice details. Make that it's own report with the appropriate
parameters and then drill through to it. My guess is that the sp would speed
up tremendously and the amount of data being dealt with by RS would drop to
a manageable level. My users (a very wide range from production operators to
management) have found drillthough to be very intuitive.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Shane" <s.dixon@.hia.asn.auNOSPAM> wrote in message
news:3A3D9583-DF23-43B9-806F-0B4DD3A68F12@.microsoft.com...
> Thanks for taking the time to read my post Bruce.
> The report is an age trial balance for our entire organisation. So each
> line
> (all 773,923 rows) are invoice lines.
> It allows our snr management to see what debt are owed to our company.
> I use a drill down so when the report is initially loaded you see each of
> my
> company's regions (10 of them) and the sum of the money owed by age 30
> Days,
> 60-90 Days, 120+ Days...
> If you click on the + it expands to reveal the different general leger
> accounts and the sum of the money owed by age 30 Days, 60-90 Days, 120+
> Days...
> If you click on the + again, it will display the person who owes the money
> and the sum of the money owed by age 30 Days, 60-90 Days, 120+ Days...
> If you click on the + again, it will display each of the invoice details..
> The report is being rendered in HTML and I also want the execution
> snapshot
> to be rendered in HTML as well.
> Cheers,
> Shane
> "Bruce L-C [MVP]" wrote:
>> This is a very bad idea. RS renders everything in RAM. Depending on the
>> format it can take varying amounts of time. If you try to render to PDF
>> or
>> Excel (can Excel even take that many rows, I'm not sure what the limit is
>> now, it was 64K in Excel 2000) it will most likely never finish. What are
>> you trying to do, a human will never look at that many rows. If you are
>> trying to do data extraction then there are better technologies to use.
>> HTML
>> is fast and might be possible but not sure what it buys you since this is
>> probably 100,000 rows of data. I suggest reading up on drill through,
>> that
>> is a very good technique for navigating large amounts of data. If you
>> explain what you are trying to do there might be some other way to
>> accomplish it.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "Shane" <s.dixon@.hia.asn.auNOSPAM> wrote in message
>> news:86D96692-5AD5-4BC0-953D-99A53D623AE3@.microsoft.com...
>> > Hi all,
>> >
>> > I have a SPROC that returns 773,923 rows and takes 13mins to execute in
>> > SQL
>> > Query Analyzer.
>> >
>> > I think I have increased all available timeout and session settings,
>> > but
>> > the
>> > report returns page cannot be displayed.
>> >
>> > The same happens when I try to create a snapshot.
>> >
>> > Any ideas?
>> >
>> > Thank you in advance.
>> >
>> > Cheers
>> >
>> > Shane
>>|||Thanks again Bruce.
I think the drillthrough idea sounds like a good one. Similar to this Sample
report:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSAMPLES/htm/rss_samplereports_v1_4z95.asp
I'll give it a go and let you know how I go! :)
Cheers,
Shane
"Bruce L-C [MVP]" wrote:
> OK, that is what I thought. Drill downs are great. Very intuitive.
> Unfortunately they require all the data to be present. So although it is
> great for the user it is not so a great a solution for speed. What I suggest
> is to change this to limit the amount of data. Perhaps make it a combination
> of drilldown and drillthrough. For instance, how much data is decreased if
> you do a drillthrough for the involice details. Or perhaps the Person and
> the invoice details. Make that it's own report with the appropriate
> parameters and then drill through to it. My guess is that the sp would speed
> up tremendously and the amount of data being dealt with by RS would drop to
> a manageable level. My users (a very wide range from production operators to
> management) have found drillthough to be very intuitive.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Shane" <s.dixon@.hia.asn.auNOSPAM> wrote in message
> news:3A3D9583-DF23-43B9-806F-0B4DD3A68F12@.microsoft.com...
> > Thanks for taking the time to read my post Bruce.
> >
> > The report is an age trial balance for our entire organisation. So each
> > line
> > (all 773,923 rows) are invoice lines.
> >
> > It allows our snr management to see what debt are owed to our company.
> >
> > I use a drill down so when the report is initially loaded you see each of
> > my
> > company's regions (10 of them) and the sum of the money owed by age 30
> > Days,
> > 60-90 Days, 120+ Days...
> >
> > If you click on the + it expands to reveal the different general leger
> > accounts and the sum of the money owed by age 30 Days, 60-90 Days, 120+
> > Days...
> >
> > If you click on the + again, it will display the person who owes the money
> > and the sum of the money owed by age 30 Days, 60-90 Days, 120+ Days...
> >
> > If you click on the + again, it will display each of the invoice details..
> >
> > The report is being rendered in HTML and I also want the execution
> > snapshot
> > to be rendered in HTML as well.
> >
> > Cheers,
> >
> > Shane
> >
> > "Bruce L-C [MVP]" wrote:
> >
> >> This is a very bad idea. RS renders everything in RAM. Depending on the
> >> format it can take varying amounts of time. If you try to render to PDF
> >> or
> >> Excel (can Excel even take that many rows, I'm not sure what the limit is
> >> now, it was 64K in Excel 2000) it will most likely never finish. What are
> >> you trying to do, a human will never look at that many rows. If you are
> >> trying to do data extraction then there are better technologies to use.
> >> HTML
> >> is fast and might be possible but not sure what it buys you since this is
> >> probably 100,000 rows of data. I suggest reading up on drill through,
> >> that
> >> is a very good technique for navigating large amounts of data. If you
> >> explain what you are trying to do there might be some other way to
> >> accomplish it.
> >>
> >>
> >> --
> >> Bruce Loehle-Conger
> >> MVP SQL Server Reporting Services
> >>
> >> "Shane" <s.dixon@.hia.asn.auNOSPAM> wrote in message
> >> news:86D96692-5AD5-4BC0-953D-99A53D623AE3@.microsoft.com...
> >> > Hi all,
> >> >
> >> > I have a SPROC that returns 773,923 rows and takes 13mins to execute in
> >> > SQL
> >> > Query Analyzer.
> >> >
> >> > I think I have increased all available timeout and session settings,
> >> > but
> >> > the
> >> > report returns page cannot be displayed.
> >> >
> >> > The same happens when I try to create a snapshot.
> >> >
> >> > Any ideas?
> >> >
> >> > Thank you in advance.
> >> >
> >> > Cheers
> >> >
> >> > Shane
> >>
> >>
> >>
>
>

Report Timeout

I have a report I execute from an Asp.Net front-end. The report is rendered
directly to PDF via a web services call to the Reporting Services web
service. For this particular report, at exactly 3 minutes, the report always
aborts processing and redirects to a "Page Cannot Be Displayed". The Report
Server log file at this time shows IsClientConnected...Orphan Request
detected or something like that. When I run the report via Report Manager,
everything works fine. I do specify a timeout value of 2400000 in my Asp.Net
where I'm consuming the web service. I've looked at almost every other
timeout but since it works fine in Report Manager, I am inclined to believe
the problem is in my Asp.Net code. Any ideas?Open your Reporing service's web service reference file and in the
constructor
add: "this.Timeout=......"
Works for web service access method.
"AdamB" <AdamB@.discussions.microsoft.com> wrote in message
news:290174E9-D359-4376-9EC7-591FC3421D74@.microsoft.com...
>I have a report I execute from an Asp.Net front-end. The report is
>rendered
> directly to PDF via a web services call to the Reporting Services web
> service. For this particular report, at exactly 3 minutes, the report
> always
> aborts processing and redirects to a "Page Cannot Be Displayed". The
> Report
> Server log file at this time shows IsClientConnected...Orphan Request
> detected or something like that. When I run the report via Report
> Manager,
> everything works fine. I do specify a timeout value of 2400000 in my
> Asp.Net
> where I'm consuming the web service. I've looked at almost every other
> timeout but since it works fine in Report Manager, I am inclined to
> believe
> the problem is in my Asp.Net code. Any ideas?|||I'm not sure where this file is that you are referring to. Also, in my
Asp.Net where I am consuming the web service, I am doing the following:
Dim rs As New WebService (where WebService is the name of my reference to
the Reporting Service ws)
rs.Timeout = 2400000
"Oleg Yevteyev" wrote:
> Open your Reporing service's web service reference file and in the
> constructor
> add: "this.Timeout=......"
> Works for web service access method.
> "AdamB" <AdamB@.discussions.microsoft.com> wrote in message
> news:290174E9-D359-4376-9EC7-591FC3421D74@.microsoft.com...
> >I have a report I execute from an Asp.Net front-end. The report is
> >rendered
> > directly to PDF via a web services call to the Reporting Services web
> > service. For this particular report, at exactly 3 minutes, the report
> > always
> > aborts processing and redirects to a "Page Cannot Be Displayed". The
> > Report
> > Server log file at this time shows IsClientConnected...Orphan Request
> > detected or something like that. When I run the report via Report
> > Manager,
> > everything works fine. I do specify a timeout value of 2400000 in my
> > Asp.Net
> > where I'm consuming the web service. I've looked at almost every other
> > timeout but since it works fine in Report Manager, I am inclined to
> > believe
> > the problem is in my Asp.Net code. Any ideas?
>
>|||Ok, I think you're talking about the Reference.vb file but I have no idea
where this timeout setting is supposed to go.
Adam
"Oleg Yevteyev" wrote:
> Open your Reporing service's web service reference file and in the
> constructor
> add: "this.Timeout=......"
> Works for web service access method.
> "AdamB" <AdamB@.discussions.microsoft.com> wrote in message
> news:290174E9-D359-4376-9EC7-591FC3421D74@.microsoft.com...
> >I have a report I execute from an Asp.Net front-end. The report is
> >rendered
> > directly to PDF via a web services call to the Reporting Services web
> > service. For this particular report, at exactly 3 minutes, the report
> > always
> > aborts processing and redirects to a "Page Cannot Be Displayed". The
> > Report
> > Server log file at this time shows IsClientConnected...Orphan Request
> > detected or something like that. When I run the report via Report
> > Manager,
> > everything works fine. I do specify a timeout value of 2400000 in my
> > Asp.Net
> > where I'm consuming the web service. I've looked at almost every other
> > timeout but since it works fine in Report Manager, I am inclined to
> > believe
> > the problem is in my Asp.Net code. Any ideas?
>
>|||"AdamB" <AdamB@.discussions.microsoft.com> wrote in message
news:D19C1C10-7F24-4834-9EAC-7A20650CE348@.microsoft.com...
> I'm not sure where this file is that you are referring to. Also, in my
> Asp.Net where I am consuming the web service, I am doing the following:
> Dim rs As New WebService (where WebService is the name of my reference to
> the Reporting Service ws)
> rs.Timeout = 2400000
Ok - seems like it is correct - just be sure to set that Timeout property on
every instance of "ws" you are creating.
More simple way is to open the reference file you correctly identified in
another post and place "rs.Timeout = 2400000"
in the constructor. In that case you wouldn't have to set that property on
every instance of "ws" you're creating.
I hope, you know what the constructor is.
There are a couple more places to check.
1. You can set in Page_Load method of your page
Server.ScriptTimeout=2400
2. Do it globally in machine.config or web.config
<configuration>
<system.web>
<httpRuntime executionTimeout="2400"
................................/>
in addtion of what you have already done.

Report SQL Server 2005 - Parameters

I am having difficulties with my parameters. In the layout field I am able to make parameters, but when I execute the report it is not just pulling certain parameters it is pulling all.

Example

If I wanted to pull all of the case that are closed, but it is still adding in the open ones as well.

Can someone guide me in the right direction?

Thanks

did you set the parameters from Report Parameter dialog box. If not select Report | Report Parameters fro the Main menu and set the fields.

kushpaw