Report/query parameter default values

I am using a SQL stored procedure for a report. The sproc has a variable in it (@.itemnumber) to be supplied by the user at run time. In the report parameters within Visual Studio Designer, I am trying to set a default value for the parameter of % so that all items will be returned unless the user enters a specific item number. In the Visual Studio preview this works but once the report is deployed to the web server, the default value does NOT show up. Please advise.

thanks!

Martha

Hi Martha,

SQL Server 2005 Books Online contains an example that takes you through

exactly what it sounds like you are trying to accomplish. Please

take a look at it at:

SQL Server 2005 Books Online->SQL Server 2005

Tutorials->Reporting Services Tutorials->Using a Dynamic Query in

a Report

It shows you how to build a dynamic query to return all records when no parameter is passed through.

If you have further problems after looking at that, please continue posting.|||

Only problem is that we are still on SQL RS 2000. Have not gone to 2005 as yet. Would the information you refer to work on 2000?

thanks!

|||Well let's try to tackle it a different way. Is your query

dynamic and includes a union with your default value that you want to

show all? For example:

SELECT 0 AS DepartmentID, 'All' AS Name

UNION

SELECT DepartmentID, Name

FROM HumanResources.Department

ORDER BY Name|||No, it's a non-queried default. I'm simply trying to set it to %. The variable is a string (varchar) in the sproc. In the Visual Studio preview it works, just not on the web once deployed.|||

hi,

iam also not able to use the dynamic query in repoting services,in sql server 2005.

i am trying from the following path

SQL Server 2005 Books Online->SQL Server 2005 Tutorials->Reporting Services Tutorials->Using a Dynamic Query in a Report-->Lesson 6: Updating the Employees Dataset with a Dynamic Query

as given in the boks online the following query

="SELECT c.firstname, c.lastname, e.title, d.departmentID " &

"From HumanResources.EmployeeDepartmentHistory D " &

"INNER JOIN HumanResources.Employee E " &

"ON D.EmployeeID = E.EmployeeID " &

"INNER JOIN Person.Contact C " &

"ON E.ContactID = C.ContactID " &

Iif(Parameters!Department.Value = 0, "", "WHERE D.DepartmentID = " & Parameters!Department.Value) &

"ORDER BY C.LastName" but am getting error from the if clause,so how to use the dynamic query for the above so.please do replysekhar|||

I am not worked on dynamic queries but i suggest you can use stored procedures with a parameter. Create a stored procedue and pass your parameter to stored procedure.

Report/query parameter default values

I am using a SQL stored procedure for a report. The sproc has a variable in it (@.itemnumber) to be supplied by the user at run time. In the report parameters within Visual Studio Designer, I am trying to set a default value for the parameter of % so that all items will be returned unless the user enters a specific item number. In the Visual Studio preview this works but once the report is deployed to the web server, the default value does NOT show up. Please advise.

thanks!

Martha

Hi Martha,
SQL Server 2005 Books Online contains an example that takes you through exactly what it sounds like you are trying to accomplish. Please take a look at it at:
SQL Server 2005 Books Online->SQL Server 2005 Tutorials->Reporting Services Tutorials->Using a Dynamic Query in a Report
It shows you how to build a dynamic query to return all records when no parameter is passed through.
If you have further problems after looking at that, please continue posting.
|||

Only problem is that we are still on SQL RS 2000. Have not gone to 2005 as yet. Would the information you refer to work on 2000?

thanks!

|||Well let's try to tackle it a different way. Is your query dynamic and includes a union with your default value that you want to show all? For example:
SELECT 0 AS DepartmentID, 'All' AS Name
UNION
SELECT DepartmentID, Name
FROM HumanResources.Department
ORDER BY Name
|||No, it's a non-queried default. I'm simply trying to set it to %. The variable is a string (varchar) in the sproc. In the Visual Studio preview it works, just not on the web once deployed.|||

hi,

iam also not able to use the dynamic query in repoting services,in sql server 2005.

i am trying from the following path

SQL Server 2005 Books Online->SQL Server 2005 Tutorials->Reporting Services Tutorials->Using a Dynamic Query in a Report-->Lesson 6: Updating the Employees Dataset with a Dynamic Query

as given in the boks online the following query

="SELECT c.firstname, c.lastname, e.title, d.departmentID " & "From HumanResources.EmployeeDepartmentHistory D " & "INNER JOIN HumanResources.Employee E " & "ON D.EmployeeID = E.EmployeeID " & "INNER JOIN Person.Contact C " & "ON E.ContactID = C.ContactID " & Iif(Parameters!Department.Value = 0, "", "WHERE D.DepartmentID = " & Parameters!Department.Value) & "ORDER BY C.LastName" but am getting error from the if clause,so how to use the dynamic query for the above so.please do replysekhar|||

I am not worked on dynamic queries but i suggest you can use stored procedures with a parameter. Create a stored procedue and pass your parameter to stored procedure.

Report/query parameter default values

I am using a SQL stored procedure for a report. The sproc has a variable in it (@.itemnumber) to be supplied by the user at run time. In the report parameters within Visual Studio Designer, I am trying to set a default value for the parameter of % so that all items will be returned unless the user enters a specific item number. In the Visual Studio preview this works but once the report is deployed to the web server, the default value does NOT show up. Please advise.

thanks!

Martha

Hi Martha,

SQL Server 2005 Books Online contains an example that takes you through

exactly what it sounds like you are trying to accomplish. Please

take a look at it at:

SQL Server 2005 Books Online->SQL Server 2005

Tutorials->Reporting Services Tutorials->Using a Dynamic Query in

a Report

It shows you how to build a dynamic query to return all records when no parameter is passed through.

If you have further problems after looking at that, please continue posting.|||

Only problem is that we are still on SQL RS 2000. Have not gone to 2005 as yet. Would the information you refer to work on 2000?

thanks!

|||Well let's try to tackle it a different way. Is your query

dynamic and includes a union with your default value that you want to

show all? For example:

SELECT 0 AS DepartmentID, 'All' AS Name

UNION

SELECT DepartmentID, Name

FROM HumanResources.Department

ORDER BY Name|||No, it's a non-queried default. I'm simply trying to set it to %. The variable is a string (varchar) in the sproc. In the Visual Studio preview it works, just not on the web once deployed.|||

hi,

iam also not able to use the dynamic query in repoting services,in sql server 2005.

i am trying from the following path

SQL Server 2005 Books Online->SQL Server 2005 Tutorials->Reporting Services Tutorials->Using a Dynamic Query in a Report-->Lesson 6: Updating the Employees Dataset with a Dynamic Query

as given in the boks online the following query

="SELECT c.firstname, c.lastname, e.title, d.departmentID " &

"From HumanResources.EmployeeDepartmentHistory D " &

"INNER JOIN HumanResources.Employee E " &

"ON D.EmployeeID = E.EmployeeID " &

"INNER JOIN Person.Contact C " &

"ON E.ContactID = C.ContactID " &

Iif(Parameters!Department.Value = 0, "", "WHERE D.DepartmentID = " & Parameters!Department.Value) &

"ORDER BY C.LastName" but am getting error from the if clause,so how to use the dynamic query for the above so.please do replysekhar|||

I am not worked on dynamic queries but i suggest you can use stored procedures with a parameter. Create a stored procedue and pass your parameter to stored procedure.

report wont refresh when accessed remotely

I have a report that is based on a straight forward query of
select * from tblx
No parameters ...
tblx gets populated by processes from an application that get invoked from a
command button. In the same process I then invoke the report using my
published url. The report comes up OK. But when I change the data in tblx
and bring up the report, it is still displaying the old data. Do I need to
add parameters to the query for it to refresh the data? Could someone
explain why the report does not refresh with the new data in tblx?
Thanks,
RichThank you for your reply. I am using RS2003 with sql server 2000. I went to
report properties but I don't see a report manager or anything about
execution. I went to the project properties - where I set the URL but I did
not see anything for executive there either. Is there somewhere on the
toolbar I can go to for locating the Report Manager or something about
execution?
Thanks,
Rich
"Chris Conner" wrote:
> This is easily fixed...
> Turn off caching for the report. By default, it is cached for 30 minutes. It
> does this since you will more often view data that is static, i.e. in the
> past, than current real time changes.
> To change the cache setting for the report, go to the report itself and view
> its properties in the report manager. Look to your left and you will see
> "execution settings". click that, next you should see the cache options
> available. Set it to your liking. :)
> Here is what you should see - just select "Do not cache temporary copies of
> this report"
> Always run this report with the most recent data
> Do not cache temporary copies of this report
> Cache a temporary copy of the report. Expire copy of report after
> a number of minutes: Missing expiration value
> The timeout value is not valid. It is either a
> negative integer or it is too large.
>
> Cache a temporary copy of the report. Expire copy of report on
> the following schedule:
> Report-specific schedule
> Shared schedule Select a shared schedule
>
>
> =-Chris
> "Rich" <Rich@.discussions.microsoft.com> wrote in message
> news:10673380-4C29-46B4-BAB0-F3087ED46C87@.microsoft.com...
> >I have a report that is based on a straight forward query of
> >
> > select * from tblx
> >
> > No parameters ...
> >
> > tblx gets populated by processes from an application that get invoked from
> > a
> > command button. In the same process I then invoke the report using my
> > published url. The report comes up OK. But when I change the data in
> > tblx
> > and bring up the report, it is still displaying the old data. Do I need
> > to
> > add parameters to the query for it to refresh the data? Could someone
> > explain why the report does not refresh with the new data in tblx?
> >
> > Thanks,
> > Rich
>
>|||Report manager is the name of the portal. It is the website provided by RS
to display the reports.
http://YourServer/Reports/Pages/Folder.aspx
Click on a report, click on the properties tab, on the left you will see
execution (I assume it is there for 2000). You will definitely see other
items like General, Security, etc.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:F7326EDC-FB18-467F-8BDA-98E351058EB6@.microsoft.com...
> Thank you for your reply. I am using RS2003 with sql server 2000. I went
> to
> report properties but I don't see a report manager or anything about
> execution. I went to the project properties - where I set the URL but I
> did
> not see anything for executive there either. Is there somewhere on the
> toolbar I can go to for locating the Report Manager or something about
> execution?
> Thanks,
> Rich
> "Chris Conner" wrote:
>> This is easily fixed...
>> Turn off caching for the report. By default, it is cached for 30 minutes.
>> It
>> does this since you will more often view data that is static, i.e. in the
>> past, than current real time changes.
>> To change the cache setting for the report, go to the report itself and
>> view
>> its properties in the report manager. Look to your left and you will see
>> "execution settings". click that, next you should see the cache options
>> available. Set it to your liking. :)
>> Here is what you should see - just select "Do not cache temporary copies
>> of
>> this report"
>> Always run this report with the most recent data
>> Do not cache temporary copies of this report
>> Cache a temporary copy of the report. Expire copy of report
>> after
>> a number of minutes: Missing expiration value
>> The timeout value is not valid. It is either a
>> negative integer or it is too large.
>>
>> Cache a temporary copy of the report. Expire copy of report on
>> the following schedule:
>> Report-specific schedule
>> Shared schedule Select a shared schedule
>>
>>
>> =-Chris
>> "Rich" <Rich@.discussions.microsoft.com> wrote in message
>> news:10673380-4C29-46B4-BAB0-F3087ED46C87@.microsoft.com...
>> >I have a report that is based on a straight forward query of
>> >
>> > select * from tblx
>> >
>> > No parameters ...
>> >
>> > tblx gets populated by processes from an application that get invoked
>> > from
>> > a
>> > command button. In the same process I then invoke the report using my
>> > published url. The report comes up OK. But when I change the data in
>> > tblx
>> > and bring up the report, it is still displaying the old data. Do I
>> > need
>> > to
>> > add parameters to the query for it to refresh the data? Could someone
>> > explain why the report does not refresh with the new data in tblx?
>> >
>> > Thanks,
>> > Rich
>>|||Thank you for this advice. Well, to reveal my ignorance,
I typed this in the address bar of my browser
http://myservername/Reports/Pages/Folder.aspx
which did not return anything. So then I tried
http://myservername/ReportServer/Reports/Pages/Folder.aspx
which also did not return anything (good). Then I tried
http://myservername/ReportServer/Reports/Pages/Invoices.aspx
Invoices is the folder where I deploy my report to. But nothing. My
intuitive skills are a little bit less than meager (at best). May I request
if you could show me an example of what specific address I need to type?
Thank again for your reply and for your help.
Rich
"Bruce L-C [MVP]" wrote:
> Report manager is the name of the portal. It is the website provided by RS
> to display the reports.
> http://YourServer/Reports/Pages/Folder.aspx
> Click on a report, click on the properties tab, on the left you will see
> execution (I assume it is there for 2000). You will definitely see other
> items like General, Security, etc.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Rich" <Rich@.discussions.microsoft.com> wrote in message
> news:F7326EDC-FB18-467F-8BDA-98E351058EB6@.microsoft.com...
> > Thank you for your reply. I am using RS2003 with sql server 2000. I went
> > to
> > report properties but I don't see a report manager or anything about
> > execution. I went to the project properties - where I set the URL but I
> > did
> > not see anything for executive there either. Is there somewhere on the
> > toolbar I can go to for locating the Report Manager or something about
> > execution?
> >
> > Thanks,
> > Rich
> >
> > "Chris Conner" wrote:
> >
> >> This is easily fixed...
> >>
> >> Turn off caching for the report. By default, it is cached for 30 minutes.
> >> It
> >> does this since you will more often view data that is static, i.e. in the
> >> past, than current real time changes.
> >>
> >> To change the cache setting for the report, go to the report itself and
> >> view
> >> its properties in the report manager. Look to your left and you will see
> >> "execution settings". click that, next you should see the cache options
> >> available. Set it to your liking. :)
> >>
> >> Here is what you should see - just select "Do not cache temporary copies
> >> of
> >> this report"
> >>
> >> Always run this report with the most recent data
> >> Do not cache temporary copies of this report
> >> Cache a temporary copy of the report. Expire copy of report
> >> after
> >> a number of minutes: Missing expiration value
> >> The timeout value is not valid. It is either a
> >> negative integer or it is too large.
> >>
> >>
> >> Cache a temporary copy of the report. Expire copy of report on
> >> the following schedule:
> >>
> >> Report-specific schedule
> >> Shared schedule Select a shared schedule
> >>
> >>
> >>
> >>
> >> =-Chris
> >>
> >> "Rich" <Rich@.discussions.microsoft.com> wrote in message
> >> news:10673380-4C29-46B4-BAB0-F3087ED46C87@.microsoft.com...
> >> >I have a report that is based on a straight forward query of
> >> >
> >> > select * from tblx
> >> >
> >> > No parameters ...
> >> >
> >> > tblx gets populated by processes from an application that get invoked
> >> > from
> >> > a
> >> > command button. In the same process I then invoke the report using my
> >> > published url. The report comes up OK. But when I change the data in
> >> > tblx
> >> > and bring up the report, it is still displaying the old data. Do I
> >> > need
> >> > to
> >> > add parameters to the query for it to refresh the data? Could someone
> >> > explain why the report does not refresh with the new data in tblx?
> >> >
> >> > Thanks,
> >> > Rich
> >>
> >>
> >>
>
>|||As far as I know every RS installation has report manager. For one thing,
that is how you set up security roles. Plus you use it for other things like
setting credentials for data sources etc.
When you say this did not return anything:
http://myservername/Reports/Pages/Folder.aspx
Did you get any message? Sometimes you need to put in fully qualified server
name http://myservername.whatever.com/Reports/Pages/Folder.aspx
If you have a working Reporting Server then someone in you organization has
to know about this.
Also, even if there, you would need to be in the proper role to be able to
make a change.
If it comes up and there are no folders then that is because you are not in
the correct role.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:D457FC39-F037-4293-9707-452A7B2D27BF@.microsoft.com...
> Thank you for this advice. Well, to reveal my ignorance,
> I typed this in the address bar of my browser
> http://myservername/Reports/Pages/Folder.aspx
> which did not return anything. So then I tried
> http://myservername/ReportServer/Reports/Pages/Folder.aspx
> which also did not return anything (good). Then I tried
> http://myservername/ReportServer/Reports/Pages/Invoices.aspx
> Invoices is the folder where I deploy my report to. But nothing. My
> intuitive skills are a little bit less than meager (at best). May I
> request
> if you could show me an example of what specific address I need to type?
> Thank again for your reply and for your help.
> Rich
>
> "Bruce L-C [MVP]" wrote:
>> Report manager is the name of the portal. It is the website provided by
>> RS
>> to display the reports.
>> http://YourServer/Reports/Pages/Folder.aspx
>> Click on a report, click on the properties tab, on the left you will see
>> execution (I assume it is there for 2000). You will definitely see other
>> items like General, Security, etc.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "Rich" <Rich@.discussions.microsoft.com> wrote in message
>> news:F7326EDC-FB18-467F-8BDA-98E351058EB6@.microsoft.com...
>> > Thank you for your reply. I am using RS2003 with sql server 2000. I
>> > went
>> > to
>> > report properties but I don't see a report manager or anything about
>> > execution. I went to the project properties - where I set the URL but
>> > I
>> > did
>> > not see anything for executive there either. Is there somewhere on the
>> > toolbar I can go to for locating the Report Manager or something about
>> > execution?
>> >
>> > Thanks,
>> > Rich
>> >
>> > "Chris Conner" wrote:
>> >
>> >> This is easily fixed...
>> >>
>> >> Turn off caching for the report. By default, it is cached for 30
>> >> minutes.
>> >> It
>> >> does this since you will more often view data that is static, i.e. in
>> >> the
>> >> past, than current real time changes.
>> >>
>> >> To change the cache setting for the report, go to the report itself
>> >> and
>> >> view
>> >> its properties in the report manager. Look to your left and you will
>> >> see
>> >> "execution settings". click that, next you should see the cache
>> >> options
>> >> available. Set it to your liking. :)
>> >>
>> >> Here is what you should see - just select "Do not cache temporary
>> >> copies
>> >> of
>> >> this report"
>> >>
>> >> Always run this report with the most recent data
>> >> Do not cache temporary copies of this report
>> >> Cache a temporary copy of the report. Expire copy of report
>> >> after
>> >> a number of minutes: Missing expiration value
>> >> The timeout value is not valid. It is either a
>> >> negative integer or it is too large.
>> >>
>> >>
>> >> Cache a temporary copy of the report. Expire copy of report
>> >> on
>> >> the following schedule:
>> >>
>> >> Report-specific schedule
>> >> Shared schedule Select a shared schedule
>> >>
>> >>
>> >>
>> >>
>> >> =-Chris
>> >>
>> >> "Rich" <Rich@.discussions.microsoft.com> wrote in message
>> >> news:10673380-4C29-46B4-BAB0-F3087ED46C87@.microsoft.com...
>> >> >I have a report that is based on a straight forward query of
>> >> >
>> >> > select * from tblx
>> >> >
>> >> > No parameters ...
>> >> >
>> >> > tblx gets populated by processes from an application that get
>> >> > invoked
>> >> > from
>> >> > a
>> >> > command button. In the same process I then invoke the report using
>> >> > my
>> >> > published url. The report comes up OK. But when I change the data
>> >> > in
>> >> > tblx
>> >> > and bring up the report, it is still displaying the old data. Do I
>> >> > need
>> >> > to
>> >> > add parameters to the query for it to refresh the data? Could
>> >> > someone
>> >> > explain why the report does not refresh with the new data in tblx?
>> >> >
>> >> > Thanks,
>> >> > Rich
>> >>
>> >>
>> >>
>>|||Thank you again. So this time I made sure that I typed in
https://myservername/Reports/Pages/Folder.aspx
and now I did get something:
Error:
The request failed with HTTP status 401: Unauthorized.
I also tried https://...
Well, at least this time I got something besides page can't be found...
What do I need to configure for authorization?
Thanks again,
Rich
"Bruce L-C [MVP]" wrote:
> As far as I know every RS installation has report manager. For one thing,
> that is how you set up security roles. Plus you use it for other things like
> setting credentials for data sources etc.
> When you say this did not return anything:
> http://myservername/Reports/Pages/Folder.aspx
> Did you get any message? Sometimes you need to put in fully qualified server
> name http://myservername.whatever.com/Reports/Pages/Folder.aspx
> If you have a working Reporting Server then someone in you organization has
> to know about this.
> Also, even if there, you would need to be in the proper role to be able to
> make a change.
> If it comes up and there are no folders then that is because you are not in
> the correct role.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>
> "Rich" <Rich@.discussions.microsoft.com> wrote in message
> news:D457FC39-F037-4293-9707-452A7B2D27BF@.microsoft.com...
> > Thank you for this advice. Well, to reveal my ignorance,
> >
> > I typed this in the address bar of my browser
> >
> > http://myservername/Reports/Pages/Folder.aspx
> >
> > which did not return anything. So then I tried
> >
> > http://myservername/ReportServer/Reports/Pages/Folder.aspx
> >
> > which also did not return anything (good). Then I tried
> >
> > http://myservername/ReportServer/Reports/Pages/Invoices.aspx
> >
> > Invoices is the folder where I deploy my report to. But nothing. My
> > intuitive skills are a little bit less than meager (at best). May I
> > request
> > if you could show me an example of what specific address I need to type?
> >
> > Thank again for your reply and for your help.
> >
> > Rich
> >
> >
> > "Bruce L-C [MVP]" wrote:
> >
> >> Report manager is the name of the portal. It is the website provided by
> >> RS
> >> to display the reports.
> >> http://YourServer/Reports/Pages/Folder.aspx
> >>
> >> Click on a report, click on the properties tab, on the left you will see
> >> execution (I assume it is there for 2000). You will definitely see other
> >> items like General, Security, etc.
> >>
> >>
> >> --
> >> Bruce Loehle-Conger
> >> MVP SQL Server Reporting Services
> >>
> >> "Rich" <Rich@.discussions.microsoft.com> wrote in message
> >> news:F7326EDC-FB18-467F-8BDA-98E351058EB6@.microsoft.com...
> >> > Thank you for your reply. I am using RS2003 with sql server 2000. I
> >> > went
> >> > to
> >> > report properties but I don't see a report manager or anything about
> >> > execution. I went to the project properties - where I set the URL but
> >> > I
> >> > did
> >> > not see anything for executive there either. Is there somewhere on the
> >> > toolbar I can go to for locating the Report Manager or something about
> >> > execution?
> >> >
> >> > Thanks,
> >> > Rich
> >> >
> >> > "Chris Conner" wrote:
> >> >
> >> >> This is easily fixed...
> >> >>
> >> >> Turn off caching for the report. By default, it is cached for 30
> >> >> minutes.
> >> >> It
> >> >> does this since you will more often view data that is static, i.e. in
> >> >> the
> >> >> past, than current real time changes.
> >> >>
> >> >> To change the cache setting for the report, go to the report itself
> >> >> and
> >> >> view
> >> >> its properties in the report manager. Look to your left and you will
> >> >> see
> >> >> "execution settings". click that, next you should see the cache
> >> >> options
> >> >> available. Set it to your liking. :)
> >> >>
> >> >> Here is what you should see - just select "Do not cache temporary
> >> >> copies
> >> >> of
> >> >> this report"
> >> >>
> >> >> Always run this report with the most recent data
> >> >> Do not cache temporary copies of this report
> >> >> Cache a temporary copy of the report. Expire copy of report
> >> >> after
> >> >> a number of minutes: Missing expiration value
> >> >> The timeout value is not valid. It is either a
> >> >> negative integer or it is too large.
> >> >>
> >> >>
> >> >> Cache a temporary copy of the report. Expire copy of report
> >> >> on
> >> >> the following schedule:
> >> >>
> >> >> Report-specific schedule
> >> >> Shared schedule Select a shared schedule
> >> >>
> >> >>
> >> >>
> >> >>
> >> >> =-Chris
> >> >>
> >> >> "Rich" <Rich@.discussions.microsoft.com> wrote in message
> >> >> news:10673380-4C29-46B4-BAB0-F3087ED46C87@.microsoft.com...
> >> >> >I have a report that is based on a straight forward query of
> >> >> >
> >> >> > select * from tblx
> >> >> >
> >> >> > No parameters ...
> >> >> >
> >> >> > tblx gets populated by processes from an application that get
> >> >> > invoked
> >> >> > from
> >> >> > a
> >> >> > command button. In the same process I then invoke the report using
> >> >> > my
> >> >> > published url. The report comes up OK. But when I change the data
> >> >> > in
> >> >> > tblx
> >> >> > and bring up the report, it is still displaying the old data. Do I
> >> >> > need
> >> >> > to
> >> >> > add parameters to the query for it to refresh the data? Could
> >> >> > someone
> >> >> > explain why the report does not refresh with the new data in tblx?
> >> >> >
> >> >> > Thanks,
> >> >> > Rich
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>|||I'm confused, how do you even have anything working? This thread initially
started because of an issue with execution and caching.
Someone has to be an administrator on your Report Server site. Who is that?
They should be either doing this for you or setting you up with the correct
role. In Books Online look up the word roles filtered by SQL Server
Reporting Services.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:8330836A-A842-4B9E-9AD6-B3011DA6D006@.microsoft.com...
> Thank you again. So this time I made sure that I typed in
> https://myservername/Reports/Pages/Folder.aspx
> and now I did get something:
> Error:
> The request failed with HTTP status 401: Unauthorized.
> I also tried https://...
> Well, at least this time I got something besides page can't be found...
> What do I need to configure for authorization?
> Thanks again,
> Rich
> "Bruce L-C [MVP]" wrote:
>> As far as I know every RS installation has report manager. For one thing,
>> that is how you set up security roles. Plus you use it for other things
>> like
>> setting credentials for data sources etc.
>> When you say this did not return anything:
>> http://myservername/Reports/Pages/Folder.aspx
>> Did you get any message? Sometimes you need to put in fully qualified
>> server
>> name http://myservername.whatever.com/Reports/Pages/Folder.aspx
>> If you have a working Reporting Server then someone in you organization
>> has
>> to know about this.
>> Also, even if there, you would need to be in the proper role to be able
>> to
>> make a change.
>> If it comes up and there are no folders then that is because you are not
>> in
>> the correct role.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>>
>> "Rich" <Rich@.discussions.microsoft.com> wrote in message
>> news:D457FC39-F037-4293-9707-452A7B2D27BF@.microsoft.com...
>> > Thank you for this advice. Well, to reveal my ignorance,
>> >
>> > I typed this in the address bar of my browser
>> >
>> > http://myservername/Reports/Pages/Folder.aspx
>> >
>> > which did not return anything. So then I tried
>> >
>> > http://myservername/ReportServer/Reports/Pages/Folder.aspx
>> >
>> > which also did not return anything (good). Then I tried
>> >
>> > http://myservername/ReportServer/Reports/Pages/Invoices.aspx
>> >
>> > Invoices is the folder where I deploy my report to. But nothing. My
>> > intuitive skills are a little bit less than meager (at best). May I
>> > request
>> > if you could show me an example of what specific address I need to
>> > type?
>> >
>> > Thank again for your reply and for your help.
>> >
>> > Rich
>> >
>> >
>> > "Bruce L-C [MVP]" wrote:
>> >
>> >> Report manager is the name of the portal. It is the website provided
>> >> by
>> >> RS
>> >> to display the reports.
>> >> http://YourServer/Reports/Pages/Folder.aspx
>> >>
>> >> Click on a report, click on the properties tab, on the left you will
>> >> see
>> >> execution (I assume it is there for 2000). You will definitely see
>> >> other
>> >> items like General, Security, etc.
>> >>
>> >>
>> >> --
>> >> Bruce Loehle-Conger
>> >> MVP SQL Server Reporting Services
>> >>
>> >> "Rich" <Rich@.discussions.microsoft.com> wrote in message
>> >> news:F7326EDC-FB18-467F-8BDA-98E351058EB6@.microsoft.com...
>> >> > Thank you for your reply. I am using RS2003 with sql server 2000.
>> >> > I
>> >> > went
>> >> > to
>> >> > report properties but I don't see a report manager or anything about
>> >> > execution. I went to the project properties - where I set the URL
>> >> > but
>> >> > I
>> >> > did
>> >> > not see anything for executive there either. Is there somewhere on
>> >> > the
>> >> > toolbar I can go to for locating the Report Manager or something
>> >> > about
>> >> > execution?
>> >> >
>> >> > Thanks,
>> >> > Rich
>> >> >
>> >> > "Chris Conner" wrote:
>> >> >
>> >> >> This is easily fixed...
>> >> >>
>> >> >> Turn off caching for the report. By default, it is cached for 30
>> >> >> minutes.
>> >> >> It
>> >> >> does this since you will more often view data that is static, i.e.
>> >> >> in
>> >> >> the
>> >> >> past, than current real time changes.
>> >> >>
>> >> >> To change the cache setting for the report, go to the report itself
>> >> >> and
>> >> >> view
>> >> >> its properties in the report manager. Look to your left and you
>> >> >> will
>> >> >> see
>> >> >> "execution settings". click that, next you should see the cache
>> >> >> options
>> >> >> available. Set it to your liking. :)
>> >> >>
>> >> >> Here is what you should see - just select "Do not cache temporary
>> >> >> copies
>> >> >> of
>> >> >> this report"
>> >> >>
>> >> >> Always run this report with the most recent data
>> >> >> Do not cache temporary copies of this report
>> >> >> Cache a temporary copy of the report. Expire copy of
>> >> >> report
>> >> >> after
>> >> >> a number of minutes: Missing expiration value
>> >> >> The timeout value is not valid. It is either
>> >> >> a
>> >> >> negative integer or it is too large.
>> >> >>
>> >> >>
>> >> >> Cache a temporary copy of the report. Expire copy of
>> >> >> report
>> >> >> on
>> >> >> the following schedule:
>> >> >>
>> >> >> Report-specific schedule
>> >> >> Shared schedule Select a shared schedule
>> >> >>
>> >> >>
>> >> >>
>> >> >>
>> >> >> =-Chris
>> >> >>
>> >> >> "Rich" <Rich@.discussions.microsoft.com> wrote in message
>> >> >> news:10673380-4C29-46B4-BAB0-F3087ED46C87@.microsoft.com...
>> >> >> >I have a report that is based on a straight forward query of
>> >> >> >
>> >> >> > select * from tblx
>> >> >> >
>> >> >> > No parameters ...
>> >> >> >
>> >> >> > tblx gets populated by processes from an application that get
>> >> >> > invoked
>> >> >> > from
>> >> >> > a
>> >> >> > command button. In the same process I then invoke the report
>> >> >> > using
>> >> >> > my
>> >> >> > published url. The report comes up OK. But when I change the
>> >> >> > data
>> >> >> > in
>> >> >> > tblx
>> >> >> > and bring up the report, it is still displaying the old data.
>> >> >> > Do I
>> >> >> > need
>> >> >> > to
>> >> >> > add parameters to the query for it to refresh the data? Could
>> >> >> > someone
>> >> >> > explain why the report does not refresh with the new data in
>> >> >> > tblx?
>> >> >> >
>> >> >> > Thanks,
>> >> >> > Rich
>> >> >>
>> >> >>
>> >> >>
>> >>
>> >>
>> >>
>>|||Thanks. I am currently working for a small subscription firm. I am still
relatively new to Reporting Services. The owner's son manages the network
(real young guy), and I did not set up RS. It was already in place. The
only RS I ever set up was the free one from msdn, and I don't recall having
to set up roles. Anwya, I found an introductory article on google about
roles/security for RS. At least I got some direction from this post on which
way to go.
Rich
"Bruce L-C [MVP]" wrote:
> I'm confused, how do you even have anything working? This thread initially
> started because of an issue with execution and caching.
> Someone has to be an administrator on your Report Server site. Who is that?
> They should be either doing this for you or setting you up with the correct
> role. In Books Online look up the word roles filtered by SQL Server
> Reporting Services.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>
> "Rich" <Rich@.discussions.microsoft.com> wrote in message
> news:8330836A-A842-4B9E-9AD6-B3011DA6D006@.microsoft.com...
> > Thank you again. So this time I made sure that I typed in
> >
> > https://myservername/Reports/Pages/Folder.aspx
> >
> > and now I did get something:
> >
> > Error:
> >
> > The request failed with HTTP status 401: Unauthorized.
> >
> > I also tried https://...
> >
> > Well, at least this time I got something besides page can't be found...
> > What do I need to configure for authorization?
> >
> > Thanks again,
> > Rich
> >
> > "Bruce L-C [MVP]" wrote:
> >
> >> As far as I know every RS installation has report manager. For one thing,
> >> that is how you set up security roles. Plus you use it for other things
> >> like
> >> setting credentials for data sources etc.
> >> When you say this did not return anything:
> >> http://myservername/Reports/Pages/Folder.aspx
> >> Did you get any message? Sometimes you need to put in fully qualified
> >> server
> >> name http://myservername.whatever.com/Reports/Pages/Folder.aspx
> >>
> >> If you have a working Reporting Server then someone in you organization
> >> has
> >> to know about this.
> >>
> >> Also, even if there, you would need to be in the proper role to be able
> >> to
> >> make a change.
> >>
> >> If it comes up and there are no folders then that is because you are not
> >> in
> >> the correct role.
> >>
> >>
> >> --
> >> Bruce Loehle-Conger
> >> MVP SQL Server Reporting Services
> >>
> >>
> >> "Rich" <Rich@.discussions.microsoft.com> wrote in message
> >> news:D457FC39-F037-4293-9707-452A7B2D27BF@.microsoft.com...
> >> > Thank you for this advice. Well, to reveal my ignorance,
> >> >
> >> > I typed this in the address bar of my browser
> >> >
> >> > http://myservername/Reports/Pages/Folder.aspx
> >> >
> >> > which did not return anything. So then I tried
> >> >
> >> > http://myservername/ReportServer/Reports/Pages/Folder.aspx
> >> >
> >> > which also did not return anything (good). Then I tried
> >> >
> >> > http://myservername/ReportServer/Reports/Pages/Invoices.aspx
> >> >
> >> > Invoices is the folder where I deploy my report to. But nothing. My
> >> > intuitive skills are a little bit less than meager (at best). May I
> >> > request
> >> > if you could show me an example of what specific address I need to
> >> > type?
> >> >
> >> > Thank again for your reply and for your help.
> >> >
> >> > Rich
> >> >
> >> >
> >> > "Bruce L-C [MVP]" wrote:
> >> >
> >> >> Report manager is the name of the portal. It is the website provided
> >> >> by
> >> >> RS
> >> >> to display the reports.
> >> >> http://YourServer/Reports/Pages/Folder.aspx
> >> >>
> >> >> Click on a report, click on the properties tab, on the left you will
> >> >> see
> >> >> execution (I assume it is there for 2000). You will definitely see
> >> >> other
> >> >> items like General, Security, etc.
> >> >>
> >> >>
> >> >> --
> >> >> Bruce Loehle-Conger
> >> >> MVP SQL Server Reporting Services
> >> >>
> >> >> "Rich" <Rich@.discussions.microsoft.com> wrote in message
> >> >> news:F7326EDC-FB18-467F-8BDA-98E351058EB6@.microsoft.com...
> >> >> > Thank you for your reply. I am using RS2003 with sql server 2000.
> >> >> > I
> >> >> > went
> >> >> > to
> >> >> > report properties but I don't see a report manager or anything about
> >> >> > execution. I went to the project properties - where I set the URL
> >> >> > but
> >> >> > I
> >> >> > did
> >> >> > not see anything for executive there either. Is there somewhere on
> >> >> > the
> >> >> > toolbar I can go to for locating the Report Manager or something
> >> >> > about
> >> >> > execution?
> >> >> >
> >> >> > Thanks,
> >> >> > Rich
> >> >> >
> >> >> > "Chris Conner" wrote:
> >> >> >
> >> >> >> This is easily fixed...
> >> >> >>
> >> >> >> Turn off caching for the report. By default, it is cached for 30
> >> >> >> minutes.
> >> >> >> It
> >> >> >> does this since you will more often view data that is static, i.e.
> >> >> >> in
> >> >> >> the
> >> >> >> past, than current real time changes.
> >> >> >>
> >> >> >> To change the cache setting for the report, go to the report itself
> >> >> >> and
> >> >> >> view
> >> >> >> its properties in the report manager. Look to your left and you
> >> >> >> will
> >> >> >> see
> >> >> >> "execution settings". click that, next you should see the cache
> >> >> >> options
> >> >> >> available. Set it to your liking. :)
> >> >> >>
> >> >> >> Here is what you should see - just select "Do not cache temporary
> >> >> >> copies
> >> >> >> of
> >> >> >> this report"
> >> >> >>
> >> >> >> Always run this report with the most recent data
> >> >> >> Do not cache temporary copies of this report
> >> >> >> Cache a temporary copy of the report. Expire copy of
> >> >> >> report
> >> >> >> after
> >> >> >> a number of minutes: Missing expiration value
> >> >> >> The timeout value is not valid. It is either
> >> >> >> a
> >> >> >> negative integer or it is too large.
> >> >> >>
> >> >> >>
> >> >> >> Cache a temporary copy of the report. Expire copy of
> >> >> >> report
> >> >> >> on
> >> >> >> the following schedule:
> >> >> >>
> >> >> >> Report-specific schedule
> >> >> >> Shared schedule Select a shared schedule
> >> >> >>
> >> >> >>
> >> >> >>
> >> >> >>
> >> >> >> =-Chris
> >> >> >>
> >> >> >> "Rich" <Rich@.discussions.microsoft.com> wrote in message
> >> >> >> news:10673380-4C29-46B4-BAB0-F3087ED46C87@.microsoft.com...
> >> >> >> >I have a report that is based on a straight forward query of
> >> >> >> >
> >> >> >> > select * from tblx
> >> >> >> >
> >> >> >> > No parameters ...
> >> >> >> >
> >> >> >> > tblx gets populated by processes from an application that get
> >> >> >> > invoked
> >> >> >> > from
> >> >> >> > a
> >> >> >> > command button. In the same process I then invoke the report
> >> >> >> > using
> >> >> >> > my
> >> >> >> > published url. The report comes up OK. But when I change the
> >> >> >> > data
> >> >> >> > in
> >> >> >> > tblx
> >> >> >> > and bring up the report, it is still displaying the old data.
> >> >> >> > Do I
> >> >> >> > need
> >> >> >> > to
> >> >> >> > add parameters to the query for it to refresh the data? Could
> >> >> >> > someone
> >> >> >> > explain why the report does not refresh with the new data in
> >> >> >> > tblx?
> >> >> >> >
> >> >> >> > Thanks,
> >> >> >> > Rich
> >> >> >>
> >> >> >>
> >> >> >>
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>|||The reason you didn't have to set up roles before was because if you
installed everything on your machine and you were a local admin for the
machine, then you automatically are in the administrator role for RS.
One easy way, if they add your userid to the local adminstrators account on
the server then you will be good to go.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:A8B42DAE-07A1-4500-873E-8535FB787DDF@.microsoft.com...
> Thanks. I am currently working for a small subscription firm. I am still
> relatively new to Reporting Services. The owner's son manages the network
> (real young guy), and I did not set up RS. It was already in place. The
> only RS I ever set up was the free one from msdn, and I don't recall
> having
> to set up roles. Anwya, I found an introductory article on google about
> roles/security for RS. At least I got some direction from this post on
> which
> way to go.
> Rich
> "Bruce L-C [MVP]" wrote:
>> I'm confused, how do you even have anything working? This thread
>> initially
>> started because of an issue with execution and caching.
>> Someone has to be an administrator on your Report Server site. Who is
>> that?
>> They should be either doing this for you or setting you up with the
>> correct
>> role. In Books Online look up the word roles filtered by SQL Server
>> Reporting Services.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>>
>> "Rich" <Rich@.discussions.microsoft.com> wrote in message
>> news:8330836A-A842-4B9E-9AD6-B3011DA6D006@.microsoft.com...
>> > Thank you again. So this time I made sure that I typed in
>> >
>> > https://myservername/Reports/Pages/Folder.aspx
>> >
>> > and now I did get something:
>> >
>> > Error:
>> >
>> > The request failed with HTTP status 401: Unauthorized.
>> >
>> > I also tried https://...
>> >
>> > Well, at least this time I got something besides page can't be found...
>> > What do I need to configure for authorization?
>> >
>> > Thanks again,
>> > Rich
>> >
>> > "Bruce L-C [MVP]" wrote:
>> >
>> >> As far as I know every RS installation has report manager. For one
>> >> thing,
>> >> that is how you set up security roles. Plus you use it for other
>> >> things
>> >> like
>> >> setting credentials for data sources etc.
>> >> When you say this did not return anything:
>> >> http://myservername/Reports/Pages/Folder.aspx
>> >> Did you get any message? Sometimes you need to put in fully qualified
>> >> server
>> >> name http://myservername.whatever.com/Reports/Pages/Folder.aspx
>> >>
>> >> If you have a working Reporting Server then someone in you
>> >> organization
>> >> has
>> >> to know about this.
>> >>
>> >> Also, even if there, you would need to be in the proper role to be
>> >> able
>> >> to
>> >> make a change.
>> >>
>> >> If it comes up and there are no folders then that is because you are
>> >> not
>> >> in
>> >> the correct role.
>> >>
>> >>
>> >> --
>> >> Bruce Loehle-Conger
>> >> MVP SQL Server Reporting Services
>> >>
>> >>
>> >> "Rich" <Rich@.discussions.microsoft.com> wrote in message
>> >> news:D457FC39-F037-4293-9707-452A7B2D27BF@.microsoft.com...
>> >> > Thank you for this advice. Well, to reveal my ignorance,
>> >> >
>> >> > I typed this in the address bar of my browser
>> >> >
>> >> > http://myservername/Reports/Pages/Folder.aspx
>> >> >
>> >> > which did not return anything. So then I tried
>> >> >
>> >> > http://myservername/ReportServer/Reports/Pages/Folder.aspx
>> >> >
>> >> > which also did not return anything (good). Then I tried
>> >> >
>> >> > http://myservername/ReportServer/Reports/Pages/Invoices.aspx
>> >> >
>> >> > Invoices is the folder where I deploy my report to. But nothing.
>> >> > My
>> >> > intuitive skills are a little bit less than meager (at best). May I
>> >> > request
>> >> > if you could show me an example of what specific address I need to
>> >> > type?
>> >> >
>> >> > Thank again for your reply and for your help.
>> >> >
>> >> > Rich
>> >> >
>> >> >
>> >> > "Bruce L-C [MVP]" wrote:
>> >> >
>> >> >> Report manager is the name of the portal. It is the website
>> >> >> provided
>> >> >> by
>> >> >> RS
>> >> >> to display the reports.
>> >> >> http://YourServer/Reports/Pages/Folder.aspx
>> >> >>
>> >> >> Click on a report, click on the properties tab, on the left you
>> >> >> will
>> >> >> see
>> >> >> execution (I assume it is there for 2000). You will definitely see
>> >> >> other
>> >> >> items like General, Security, etc.
>> >> >>
>> >> >>
>> >> >> --
>> >> >> Bruce Loehle-Conger
>> >> >> MVP SQL Server Reporting Services
>> >> >>
>> >> >> "Rich" <Rich@.discussions.microsoft.com> wrote in message
>> >> >> news:F7326EDC-FB18-467F-8BDA-98E351058EB6@.microsoft.com...
>> >> >> > Thank you for your reply. I am using RS2003 with sql server
>> >> >> > 2000.
>> >> >> > I
>> >> >> > went
>> >> >> > to
>> >> >> > report properties but I don't see a report manager or anything
>> >> >> > about
>> >> >> > execution. I went to the project properties - where I set the
>> >> >> > URL
>> >> >> > but
>> >> >> > I
>> >> >> > did
>> >> >> > not see anything for executive there either. Is there somewhere
>> >> >> > on
>> >> >> > the
>> >> >> > toolbar I can go to for locating the Report Manager or something
>> >> >> > about
>> >> >> > execution?
>> >> >> >
>> >> >> > Thanks,
>> >> >> > Rich
>> >> >> >
>> >> >> > "Chris Conner" wrote:
>> >> >> >
>> >> >> >> This is easily fixed...
>> >> >> >>
>> >> >> >> Turn off caching for the report. By default, it is cached for 30
>> >> >> >> minutes.
>> >> >> >> It
>> >> >> >> does this since you will more often view data that is static,
>> >> >> >> i.e.
>> >> >> >> in
>> >> >> >> the
>> >> >> >> past, than current real time changes.
>> >> >> >>
>> >> >> >> To change the cache setting for the report, go to the report
>> >> >> >> itself
>> >> >> >> and
>> >> >> >> view
>> >> >> >> its properties in the report manager. Look to your left and you
>> >> >> >> will
>> >> >> >> see
>> >> >> >> "execution settings". click that, next you should see the cache
>> >> >> >> options
>> >> >> >> available. Set it to your liking. :)
>> >> >> >>
>> >> >> >> Here is what you should see - just select "Do not cache
>> >> >> >> temporary
>> >> >> >> copies
>> >> >> >> of
>> >> >> >> this report"
>> >> >> >>
>> >> >> >> Always run this report with the most recent data
>> >> >> >> Do not cache temporary copies of this report
>> >> >> >> Cache a temporary copy of the report. Expire copy of
>> >> >> >> report
>> >> >> >> after
>> >> >> >> a number of minutes: Missing expiration value
>> >> >> >> The timeout value is not valid. It is
>> >> >> >> either
>> >> >> >> a
>> >> >> >> negative integer or it is too large.
>> >> >> >>
>> >> >> >>
>> >> >> >> Cache a temporary copy of the report. Expire copy of
>> >> >> >> report
>> >> >> >> on
>> >> >> >> the following schedule:
>> >> >> >>
>> >> >> >> Report-specific schedule
>> >> >> >> Shared schedule Select a shared schedule
>> >> >> >>
>> >> >> >>
>> >> >> >>
>> >> >> >>
>> >> >> >> =-Chris
>> >> >> >>
>> >> >> >> "Rich" <Rich@.discussions.microsoft.com> wrote in message
>> >> >> >> news:10673380-4C29-46B4-BAB0-F3087ED46C87@.microsoft.com...
>> >> >> >> >I have a report that is based on a straight forward query of
>> >> >> >> >
>> >> >> >> > select * from tblx
>> >> >> >> >
>> >> >> >> > No parameters ...
>> >> >> >> >
>> >> >> >> > tblx gets populated by processes from an application that get
>> >> >> >> > invoked
>> >> >> >> > from
>> >> >> >> > a
>> >> >> >> > command button. In the same process I then invoke the report
>> >> >> >> > using
>> >> >> >> > my
>> >> >> >> > published url. The report comes up OK. But when I change the
>> >> >> >> > data
>> >> >> >> > in
>> >> >> >> > tblx
>> >> >> >> > and bring up the report, it is still displaying the old data.
>> >> >> >> > Do I
>> >> >> >> > need
>> >> >> >> > to
>> >> >> >> > add parameters to the query for it to refresh the data? Could
>> >> >> >> > someone
>> >> >> >> > explain why the report does not refresh with the new data in
>> >> >> >> > tblx?
>> >> >> >> >
>> >> >> >> > Thanks,
>> >> >> >> > Rich
>> >> >> >>
>> >> >> >>
>> >> >> >>
>> >> >>
>> >> >>
>> >> >>
>> >>
>> >>
>> >>
>>

Report Wizard Error on SP

Stepping through the Report Wizard to create a new report, the second step
"Design the Query" I entered my SP name. When I click next I get the
following error.
"There is an error in the query. Line 1: Incorrect syntax near
'STORED_PROCEDURE_NAME""
I ran the SP in Query Analyzer and works fine.
The Shared Data Source tested good for the connection.Mike,
Why I think this happens is that the development environment is parsing
the stored procedure to try and determine the resultset that will be
coming back. If it can't, it just gives up and returns the error
message. I've had this happen on procs that use temp tables primarily.
There are a several of ways to handle this.
1. Don't use the wizzard. Manually build your data source, manually key
in the column names for the result set. Some will advocate never using
the wizzard, but don't throw the baby out witht he bathwater...
2. Try entering the proc exec statement with any required parameters:
exec myproc 0, 'parm2'
once you get through the wizzard, change the data set from using the
Command Type of "Text" to use "StoredProcedure", and specify the
parameter(s)/values on the parameter tab.
3. Modify your procedure to return an empty result set that matches
what is normally returned. default your parameter(s) to Null and add a
check for @.parameter = null in the body of the proc. if it is null,
return the empty result set and return. This will give the wizzard
what it is looking for. One technique that worked for me was to
rewrite the proc so that any SELECT ... INTO statements that create and
populate temp tables are changed to explicit table creates and insert
statements. Using table variables instead of temp tables is also an
option, as the development environment parser seems to ignore them when
trying to determine the result set.
Hope this helps!
Regards,
Clayton

Report with measures in row

Hi,
I would like to create a report on a cube with Report Server (Query Builder) with one dimension in columns and the "measures" dimension members in rows:

Jan Feb Mar ...
Measure1 10 12 14
Measure2 20 22 24
..

How to create with either Tabular or Matrix report type?

Thanks,
Marcus

You can either:

1. Use the RS 2005 SSAS provider but change the report format to request measures on columns.

2. Bypass the RS 2005 SSAS provider and use the MS OLE DB 9.0 Provider for Analysis Services to request the measures on rows and Time on columns. However, in this case you can't use the matrix region and your table columns will be fixed. So, you have to create 12 columns (assuming 12 months on columns) and hide the ones that are not returned.

|||Thank you very much Teo,
I had to chose alternative 2 and created a report with measures in rows and tabular structure. Now I got stuck in some details. I need cascading parameters following the whitepaper " Integrating Analysis Services with Reporting Services" for SQL 2000. But I'm working with SQL2005. I easily establish a parameter for the highest parameter level (here ProductCategory in Adventure Works)

="WITH MEMBER [Product].[Category].[Prod] AS
'[Product].[Category].[" + Parameters!ProductCategory.Value + "]'

SELECT

{ [Measures].[Internet Sales-Discount Amount],[Measures].[Internet Sales-Extended Amount] } ON ROWS ,

{[Date].[Calendar Time].[Month].[January 2003]} ON COLUMNS

FROM [Adventure Works UDM]
WHERE [Product].[Category].[Prod] "

but a link to this existing parameter in a cascading parameter yields an empty list and the report is not executable ([Product Categories] is a natural hierachy):

= "WITH MEMBER Measures.NullColumn AS 'Null'

SELECT
{Measures.NullColumn} ON COLUMNS,
{DESCENDANTS({" + Parameters!ProductCategory.Value + " },
[Product].[Product Categories].[Subcategory])} ON ROWS
FROM
[Adventure Works UDM]"

Are cascading parameters with OLE DB 9.0 and SQL 2005 possible, can you provide an example?

Thanks a lot,
Marcus

sql

Report with measures in row

Hi,
I would like to create a report on a cube with Report Server (Query Builder) with one dimension in columns and the "measures" dimension members in rows:

Jan Feb Mar ...
Measure1 10 12 14
Measure2 20 22 24
..

How to create with either Tabular or Matrix report type?

Thanks,
Marcus

You can either:

1. Use the RS 2005 SSAS provider but change the report format to request measures on columns.

2. Bypass the RS 2005 SSAS provider and use the MS OLE DB 9.0 Provider for Analysis Services to request the measures on rows and Time on columns. However, in this case you can't use the matrix region and your table columns will be fixed. So, you have to create 12 columns (assuming 12 months on columns) and hide the ones that are not returned.

|||Thank you very much Teo,
I had to chose alternative 2 and created a report with measures in rows and tabular structure. Now I got stuck in some details. I need cascading parameters following the whitepaper " Integrating Analysis Services with Reporting Services" for SQL 2000. But I'm working with SQL2005. I easily establish a parameter for the highest parameter level (here ProductCategory in Adventure Works)

="WITH MEMBER [Product].[Category].[Prod] AS
'[Product].[Category].[" + Parameters!ProductCategory.Value + "]'

SELECT

{ [Measures].[Internet Sales-Discount Amount],[Measures].[Internet Sales-Extended Amount] } ON ROWS ,

{[Date].[Calendar Time].[Month].[January 2003]} ON COLUMNS

FROM [Adventure Works UDM]
WHERE [Product].[Category].[Prod] "

but a link to this existing parameter in a cascading parameter yields an empty list and the report is not executable ([Product Categories] is a natural hierachy):

= "WITH MEMBER Measures.NullColumn AS 'Null'

SELECT
{Measures.NullColumn} ON COLUMNS,
{DESCENDANTS({" + Parameters!ProductCategory.Value + " },
[Product].[Product Categories].[Subcategory])} ON ROWS
FROM
[Adventure Works UDM]"

Are cascading parameters with OLE DB 9.0 and SQL 2005 possible, can you provide an example?

Thanks a lot,
Marcus

report with groupby

hi,
i have a query working with crystal reports on oracle server.

--There is a table transaction_hist containing 'due_date' column(which is a number to be converted to date). In the report i want the summary of the amounts with current due_date, future due_date, past 1to30 days due_date,31to60,61to90 and 91to180 due_dates.

eg
current future 1_30 31_60 61_90 91_181
sum 99999 99999 99999 9999 9999 9999


PS: i have tried to make formula column for all these due_dates...but could not group by them and summarize them.Try uisng Cross tab reports. Read more in help file

Report Viewer w/SQL DB not showing in viewer?

Morning,

This is driving me daft!

I designed the report and attached to the DB. The Query builder shows me data coming back.

I then attached the local report to the viewer, on a web page, and ran it in the IDE.

The webpage comes up witht the toolbar accross the top but no report! No error msg either.

When I step thru the codebehind. I added in a Try catch just so I could see what was going on.

The viewer object has the rpt and the SQL cmd thats in the report. So it seems to be connecting correctly. But no report. Not even just the text objects. I just get a blank viewer!

Help please!

Thanks

Deasun

I am having the same problem. I am using an object data source and the report is completely blank. I can export the data to excel or acrobat but I don't understand why I can't see any data at all on the page.|||

Did you specify a percentage as the height or width of the viewer? I saw another post that said there was a bug in the viewer so I changed my dimensions to pixels and it works.

|||

I must try that.

More then likly I have %s on it.

I have seen this bug in a few objects to date. One that comes to mind is the Infragistics Tree controll.

Specify a % W/L and it does some strange stuff sometimes. Put in a pixel size or none at all and it works fine.

Thanks

Deasun

Report times out

I have a report based on a complex query that takes a long time to run and is
timing out. I've tuned indexes the best I know how, I've set the "Do not
timeout report execution" in the Home page Site Settings and in the
individual report properties, but it still times out. Any suggestions will be
most appreciated.hi Dan,
I'm also seeing the same behavior that you are. It takes about 60 minutes
for our reports to timeout. How long do your reports run before they timeout?
Thanks,
Matt
If I make any progress on this, I will let you know.
"Dan" wrote:
> I have a report based on a complex query that takes a long time to run and is
> timing out. I've tuned indexes the best I know how, I've set the "Do not
> timeout report execution" in the Home page Site Settings and in the
> individual report properties, but it still times out. Any suggestions will be
> most appreciated.
>

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 taking a long time to render

I have a somewhat complex query that takes about 2 seconds to run in
Query Analyzer. Yet it takes a full 30 seconds to generate the
report. If I remove the document map then it takes ~10 seconds. It
seems that there is just tremendous overhead or some other problem
when generating reports.
Does anyone have any suggestions how I can speed this up? We need up
to the minute data so I can't really use a snapshot.
Thanks!
Matt PennerOn Sep 24, 10:19 am, Matt Penner <mattpenner2...@.gmail.com> wrote:
> I have a somewhat complex query that takes about 2 seconds to run in
> Query Analyzer. Yet it takes a full 30 seconds to generate the
> report. If I remove the document map then it takes ~10 seconds. It
> seems that there is just tremendous overhead or some other problem
> when generating reports.
> Does anyone have any suggestions how I can speed this up? We need up
> to the minute data so I can't really use a snapshot.
> Thanks!
> Matt Penner
In general, a deployed report has to deal w/IIS and the SSRS web
service, which adds overhead. Aside form this fact, if you haven't
already, you will want to save the query for the report as a stored
procedure, this will improve performance and response time: due to the
stored procedure having a predefined query plan. Also, you will want
to run the stored procedure/query against the Database Engine Tuning
Advisor to determine if any table indexes will further improve your
performance based on this query. Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant|||errr... How do you use a stored procedure in SSRS? It doesn't appear to be an
option.
--
Tom Garth
"EMartinez" wrote:
> On Sep 24, 10:19 am, Matt Penner <mattpenner2...@.gmail.com> wrote:
> > I have a somewhat complex query that takes about 2 seconds to run in
> > Query Analyzer. Yet it takes a full 30 seconds to generate the
> > report. If I remove the document map then it takes ~10 seconds. It
> > seems that there is just tremendous overhead or some other problem
> > when generating reports.
> >
> > Does anyone have any suggestions how I can speed this up? We need up
> > to the minute data so I can't really use a snapshot.
> >
> > Thanks!
> > Matt Penner
>
> In general, a deployed report has to deal w/IIS and the SSRS web
> service, which adds overhead. Aside form this fact, if you haven't
> already, you will want to save the query for the report as a stored
> procedure, this will improve performance and response time: due to the
> stored procedure having a predefined query plan. Also, you will want
> to run the stored procedure/query against the Database Engine Tuning
> Advisor to determine if any table indexes will further improve your
> performance based on this query. Hope this helps.
> Regards,
> Enrique Martinez
> Sr. Software Consultant
>|||RS has full and good stored procedure report. The problem is you probably
are just using the wizard. The wizard does not have good stored procedure
report. For some SP if you put in the query and do not use the query builder
to do this.
exec mystoredprocedure @.param1= 'Blah', @.param2 = 'Whatever'
However, depending on how the stored is written it still might have
troubles.
What I sometimes do is create a simple query that returns the fields I want
and use that with the wizard. Then open up the design, go to the dataset
tab. Click on the ..., change the command type to store procedure then in
the next box put in the name of the stored procedure (just the name, do not
put in exec or put in the parameter values). RS recognizes the parameters
and create Report Parameters for each parameter of the SP if it is SQL
Server. Note that you can use SP from other systems but the mechanism is
different (I write lots of SP for Sybase).
If anyone tells you that SP do not work then this is just flat out wrong. In
some cases there are design issues but every single issue can be worked out.
Here is a list of how the SP should be written (most of which is concerning
temp tables)
Temp tables should work for you. I use them a whole lot. Do the following:
1. Click on the refresh fields button (to the right of the ...) if you do
not get field list returned for your stored procedure.
2. Do not use set nocount on
3. Do not explicitly drop the temp tables
4. Have your last statement be a select
If none of these work then add Set FMTONLY Off (the below is from Simon
Sabin a SQL Server MVP). The only time I have had to do this is when one SP
is calling another SP and the first SP has created a temp table that the
second SP puts data into.Here is his explanation: "The issue with RS is that
the rowset of the SP is defined by calling the SP with SET FMTONLY ON
because Temp tables don't get created if you select from the temp table the
metadata from the rowset can't be returned. This can be worked around by
turning FMTONLY OFF in the SP."
I use SP extensively from both SQL Server and Sybase.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Tom Garth" <TomGarth@.discussions.microsoft.com> wrote in message
news:2F03EA77-9F0F-42A3-8E16-AAD9929E0195@.microsoft.com...
> errr... How do you use a stored procedure in SSRS? It doesn't appear to be
> an
> option.
> --
> Tom Garth
>
> "EMartinez" wrote:
>> On Sep 24, 10:19 am, Matt Penner <mattpenner2...@.gmail.com> wrote:
>> > I have a somewhat complex query that takes about 2 seconds to run in
>> > Query Analyzer. Yet it takes a full 30 seconds to generate the
>> > report. If I remove the document map then it takes ~10 seconds. It
>> > seems that there is just tremendous overhead or some other problem
>> > when generating reports.
>> >
>> > Does anyone have any suggestions how I can speed this up? We need up
>> > to the minute data so I can't really use a snapshot.
>> >
>> > Thanks!
>> > Matt Penner
>>
>> In general, a deployed report has to deal w/IIS and the SSRS web
>> service, which adds overhead. Aside form this fact, if you haven't
>> already, you will want to save the query for the report as a stored
>> procedure, this will improve performance and response time: due to the
>> stored procedure having a predefined query plan. Also, you will want
>> to run the stored procedure/query against the Database Engine Tuning
>> Advisor to determine if any table indexes will further improve your
>> performance based on this query. Hope this helps.
>> Regards,
>> Enrique Martinez
>> Sr. Software Consultant
>>|||Bruce,
This is terrific stuff! No one ever told me that SPs wouldn't work. I
surmised it as you said, from using the wizard. I considered it to be a major
drawback to using SSRS, but will have to do some rethinking on that now.
I'm glad you mentioned not using SET NOCOUNT ON. I almost always have used
it in reporting. Especially when using temp tables for a Crystal Report.
Thank you for taking the time to reply to my post.
Tom Garth
Developer / Integration Specialist
R. L. Nelson and Associates, Inc.
1400 Technology Drive
Harrisonburg, VA 22802
Main Office: (888) 313-0647
www.rlninc.com
Tom Garth
"Bruce L-C [MVP]" wrote:
> RS has full and good stored procedure report. The problem is you probably
> are just using the wizard. The wizard does not have good stored procedure
> report. For some SP if you put in the query and do not use the query builder
> to do this.
> exec mystoredprocedure @.param1= 'Blah', @.param2 = 'Whatever'
> However, depending on how the stored is written it still might have
> troubles.
> What I sometimes do is create a simple query that returns the fields I want
> and use that with the wizard. Then open up the design, go to the dataset
> tab. Click on the ..., change the command type to store procedure then in
> the next box put in the name of the stored procedure (just the name, do not
> put in exec or put in the parameter values). RS recognizes the parameters
> and create Report Parameters for each parameter of the SP if it is SQL
> Server. Note that you can use SP from other systems but the mechanism is
> different (I write lots of SP for Sybase).
> If anyone tells you that SP do not work then this is just flat out wrong. In
> some cases there are design issues but every single issue can be worked out.
> Here is a list of how the SP should be written (most of which is concerning
> temp tables)
> Temp tables should work for you. I use them a whole lot. Do the following:
> 1. Click on the refresh fields button (to the right of the ...) if you do
> not get field list returned for your stored procedure.
> 2. Do not use set nocount on
> 3. Do not explicitly drop the temp tables
> 4. Have your last statement be a select
> If none of these work then add Set FMTONLY Off (the below is from Simon
> Sabin a SQL Server MVP). The only time I have had to do this is when one SP
> is calling another SP and the first SP has created a temp table that the
> second SP puts data into.Here is his explanation: "The issue with RS is that
> the rowset of the SP is defined by calling the SP with SET FMTONLY ON
> because Temp tables don't get created if you select from the temp table the
> metadata from the rowset can't be returned. This can be worked around by
> turning FMTONLY OFF in the SP."
> I use SP extensively from both SQL Server and Sybase.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>
> "Tom Garth" <TomGarth@.discussions.microsoft.com> wrote in message
> news:2F03EA77-9F0F-42A3-8E16-AAD9929E0195@.microsoft.com...
> > errr... How do you use a stored procedure in SSRS? It doesn't appear to be
> > an
> > option.
> > --
> > Tom Garth
> >
> >
> > "EMartinez" wrote:
> >
> >> On Sep 24, 10:19 am, Matt Penner <mattpenner2...@.gmail.com> wrote:
> >> > I have a somewhat complex query that takes about 2 seconds to run in
> >> > Query Analyzer. Yet it takes a full 30 seconds to generate the
> >> > report. If I remove the document map then it takes ~10 seconds. It
> >> > seems that there is just tremendous overhead or some other problem
> >> > when generating reports.
> >> >
> >> > Does anyone have any suggestions how I can speed this up? We need up
> >> > to the minute data so I can't really use a snapshot.
> >> >
> >> > Thanks!
> >> > Matt Penner
> >>
> >>
> >> In general, a deployed report has to deal w/IIS and the SSRS web
> >> service, which adds overhead. Aside form this fact, if you haven't
> >> already, you will want to save the query for the report as a stored
> >> procedure, this will improve performance and response time: due to the
> >> stored procedure having a predefined query plan. Also, you will want
> >> to run the stored procedure/query against the Database Engine Tuning
> >> Advisor to determine if any table indexes will further improve your
> >> performance based on this query. Hope this helps.
> >>
> >> Regards,
> >>
> >> Enrique Martinez
> >> Sr. Software Consultant
> >>
> >>
>
>|||No problem. Glad to be of help.
Most of the issues with SP tends to be because of how the SP is written. For
instance, a lot of people drop their temp tables at the end of the SP. This
messes up RS. Instead you just let SQL Server clean up the temp tables when
it goes out of scope.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Tom Garth" <TomGarth@.discussions.microsoft.com> wrote in message
news:DC7C9319-3451-49F6-91F9-2A31EE9A7A9B@.microsoft.com...
> Bruce,
> This is terrific stuff! No one ever told me that SPs wouldn't work. I
> surmised it as you said, from using the wizard. I considered it to be a
> major
> drawback to using SSRS, but will have to do some rethinking on that now.
> I'm glad you mentioned not using SET NOCOUNT ON. I almost always have used
> it in reporting. Especially when using temp tables for a Crystal Report.
> Thank you for taking the time to reply to my post.
> Tom Garth
> Developer / Integration Specialist
> R. L. Nelson and Associates, Inc.
> 1400 Technology Drive
> Harrisonburg, VA 22802
> Main Office: (888) 313-0647
> www.rlninc.com
>
> --
> Tom Garth
>
> "Bruce L-C [MVP]" wrote:
>> RS has full and good stored procedure report. The problem is you probably
>> are just using the wizard. The wizard does not have good stored procedure
>> report. For some SP if you put in the query and do not use the query
>> builder
>> to do this.
>> exec mystoredprocedure @.param1= 'Blah', @.param2 = 'Whatever'
>> However, depending on how the stored is written it still might have
>> troubles.
>> What I sometimes do is create a simple query that returns the fields I
>> want
>> and use that with the wizard. Then open up the design, go to the dataset
>> tab. Click on the ..., change the command type to store procedure then in
>> the next box put in the name of the stored procedure (just the name, do
>> not
>> put in exec or put in the parameter values). RS recognizes the parameters
>> and create Report Parameters for each parameter of the SP if it is SQL
>> Server. Note that you can use SP from other systems but the mechanism is
>> different (I write lots of SP for Sybase).
>> If anyone tells you that SP do not work then this is just flat out wrong.
>> In
>> some cases there are design issues but every single issue can be worked
>> out.
>> Here is a list of how the SP should be written (most of which is
>> concerning
>> temp tables)
>> Temp tables should work for you. I use them a whole lot. Do the
>> following:
>> 1. Click on the refresh fields button (to the right of the ...) if you do
>> not get field list returned for your stored procedure.
>> 2. Do not use set nocount on
>> 3. Do not explicitly drop the temp tables
>> 4. Have your last statement be a select
>> If none of these work then add Set FMTONLY Off (the below is from Simon
>> Sabin a SQL Server MVP). The only time I have had to do this is when one
>> SP
>> is calling another SP and the first SP has created a temp table that the
>> second SP puts data into.Here is his explanation: "The issue with RS is
>> that
>> the rowset of the SP is defined by calling the SP with SET FMTONLY ON
>> because Temp tables don't get created if you select from the temp table
>> the
>> metadata from the rowset can't be returned. This can be worked around by
>> turning FMTONLY OFF in the SP."
>> I use SP extensively from both SQL Server and Sybase.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>>
>> "Tom Garth" <TomGarth@.discussions.microsoft.com> wrote in message
>> news:2F03EA77-9F0F-42A3-8E16-AAD9929E0195@.microsoft.com...
>> > errr... How do you use a stored procedure in SSRS? It doesn't appear to
>> > be
>> > an
>> > option.
>> > --
>> > Tom Garth
>> >
>> >
>> > "EMartinez" wrote:
>> >
>> >> On Sep 24, 10:19 am, Matt Penner <mattpenner2...@.gmail.com> wrote:
>> >> > I have a somewhat complex query that takes about 2 seconds to run in
>> >> > Query Analyzer. Yet it takes a full 30 seconds to generate the
>> >> > report. If I remove the document map then it takes ~10 seconds. It
>> >> > seems that there is just tremendous overhead or some other problem
>> >> > when generating reports.
>> >> >
>> >> > Does anyone have any suggestions how I can speed this up? We need
>> >> > up
>> >> > to the minute data so I can't really use a snapshot.
>> >> >
>> >> > Thanks!
>> >> > Matt Penner
>> >>
>> >>
>> >> In general, a deployed report has to deal w/IIS and the SSRS web
>> >> service, which adds overhead. Aside form this fact, if you haven't
>> >> already, you will want to save the query for the report as a stored
>> >> procedure, this will improve performance and response time: due to the
>> >> stored procedure having a predefined query plan. Also, you will want
>> >> to run the stored procedure/query against the Database Engine Tuning
>> >> Advisor to determine if any table indexes will further improve your
>> >> performance based on this query. Hope this helps.
>> >>
>> >> Regards,
>> >>
>> >> Enrique Martinez
>> >> Sr. Software Consultant
>> >>
>> >>
>>|||Well, as I said, the raw query returns results in <2 seconds. This
purely in development right now and we'll most likely move this to
stored procedures in the future but that doesn't seem to be the issue.
The waiting time seems to be on the rendering side.
If SSRS is fast and the overhead of IIS is causing the issue (seems
unlikely) are there any good sites to look at to help improve this?
All I've been able to find are the MS recommended enterprise rollout
with several servers. We don't have these kind of resources for SSRS,
and currently there are only 4 reports and 4 users. I am disappointed
that it takes this long to render simple reports when we're not even
in production capacity.
Thanks,
Matt
On Sep 24, 6:22 pm, EMartinez <emartinez...@.gmail.com> wrote:
> On Sep 24, 10:19 am, Matt Penner <mattpenner2...@.gmail.com> wrote:
> > I have a somewhat complex query that takes about 2 seconds to run in
> > Query Analyzer. Yet it takes a full 30 seconds to generate the
> > report. If I remove the document map then it takes ~10 seconds. It
> > seems that there is just tremendous overhead or some other problem
> > when generating reports.
> > Does anyone have any suggestions how I can speed this up? We need up
> > to the minute data so I can't really use a snapshot.
> > Thanks!
> > Matt Penner
> In general, a deployed report has to deal w/IIS and the SSRS web
> service, which adds overhead. Aside form this fact, if you haven't
> already, you will want to save the query for the report as a stored
> procedure, this will improve performance and response time: due to the
> stored procedure having a predefined query plan. Also, you will want
> to run the stored procedure/query against the Database Engine Tuning
> Advisor to determine if any table indexes will further improve your
> performance based on this query. Hope this helps.
> Regards,
> Enrique Martinez
> Sr. Software Consultant|||RS currently renders in RAM. The amount of data is what matters. My guess is
that the 2 seconds is enough to bring over the data and the rest is
rendering. The document map must require more compute time.
A couple of questions, what are you rendering to? HTML? PDF? that makes a
difference as well. Also, you didn't mention what version you are on. How
much RAM is on your box?
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Matt Penner" <mattpenner2007@.gmail.com> wrote in message
news:1190843034.956283.72260@.57g2000hsv.googlegroups.com...
> Well, as I said, the raw query returns results in <2 seconds. This
> purely in development right now and we'll most likely move this to
> stored procedures in the future but that doesn't seem to be the issue.
> The waiting time seems to be on the rendering side.
> If SSRS is fast and the overhead of IIS is causing the issue (seems
> unlikely) are there any good sites to look at to help improve this?
> All I've been able to find are the MS recommended enterprise rollout
> with several servers. We don't have these kind of resources for SSRS,
> and currently there are only 4 reports and 4 users. I am disappointed
> that it takes this long to render simple reports when we're not even
> in production capacity.
> Thanks,
> Matt
> On Sep 24, 6:22 pm, EMartinez <emartinez...@.gmail.com> wrote:
>> On Sep 24, 10:19 am, Matt Penner <mattpenner2...@.gmail.com> wrote:
>> > I have a somewhat complex query that takes about 2 seconds to run in
>> > Query Analyzer. Yet it takes a full 30 seconds to generate the
>> > report. If I remove the document map then it takes ~10 seconds. It
>> > seems that there is just tremendous overhead or some other problem
>> > when generating reports.
>> > Does anyone have any suggestions how I can speed this up? We need up
>> > to the minute data so I can't really use a snapshot.
>> > Thanks!
>> > Matt Penner
>> In general, a deployed report has to deal w/IIS and the SSRS web
>> service, which adds overhead. Aside form this fact, if you haven't
>> already, you will want to save the query for the report as a stored
>> procedure, this will improve performance and response time: due to the
>> stored procedure having a predefined query plan. Also, you will want
>> to run the stored procedure/query against the Database Engine Tuning
>> Advisor to determine if any table indexes will further improve your
>> performance based on this query. Hope this helps.
>> Regards,
>> Enrique Martinez
>> Sr. Software Consultant
>|||This is using SQL Server 2005 SP2 and SSRS. I'm rendering straight to
the MS Report Viewer. Viewing the report during testing on my dev
machine as well as from the server after deployment seem to give the
same response times. This would make sense if the rendering is purely
on the client side. I have 1.5GB of ram on a P4 3GHz WinXP machine,
but I usually have several things open like Visual Studio, SQL
Management Studio, etc.
If the Report Viewer is entirely client side then there's not much I
can do about it. I didn't realize this is how it works. We have
several different computer models throughout our network so optimizing
the client side would to be difficult at best.
Thanks, I'll just live with it. :)
If there are any other comments out there that would be great but I'll
just assume this is the way it is until MS develops a better viewer.
On Sep 26, 2:54 pm, "Bruce L-C [MVP]" <bruce_lcNOS...@.hotmail.com>
wrote:
> RS currently renders in RAM. The amount of data is what matters. My guess is
> that the 2 seconds is enough to bring over the data and the rest is
> rendering. The document map must require more compute time.
> A couple of questions, what are you rendering to? HTML? PDF? that makes a
> difference as well. Also, you didn't mention what version you are on. How
> much RAM is on your box?
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Matt Penner" <mattpenner2...@.gmail.com> wrote in message
> news:1190843034.956283.72260@.57g2000hsv.googlegroups.com...
> > Well, as I said, the raw query returns results in <2 seconds. This
> > purely in development right now and we'll most likely move this to
> > stored procedures in the future but that doesn't seem to be the issue.
> > The waiting time seems to be on the rendering side.
> > If SSRS is fast and the overhead of IIS is causing the issue (seems
> > unlikely) are there any good sites to look at to help improve this?
> > All I've been able to find are the MS recommended enterprise rollout
> > with several servers. We don't have these kind of resources for SSRS,
> > and currently there are only 4 reports and 4 users. I am disappointed
> > that it takes this long to render simple reports when we're not even
> > in production capacity.
> > Thanks,
> > Matt
> > On Sep 24, 6:22 pm, EMartinez <emartinez...@.gmail.com> wrote:
> >> On Sep 24, 10:19 am, Matt Penner <mattpenner2...@.gmail.com> wrote:
> >> > I have a somewhat complex query that takes about 2 seconds to run in
> >> > Query Analyzer. Yet it takes a full 30 seconds to generate the
> >> > report. If I remove the document map then it takes ~10 seconds. It
> >> > seems that there is just tremendous overhead or some other problem
> >> > when generating reports.
> >> > Does anyone have any suggestions how I can speed this up? We need up
> >> > to the minute data so I can't really use a snapshot.
> >> > Thanks!
> >> > Matt Penner
> >> In general, a deployed report has to deal w/IIS and the SSRS web
> >> service, which adds overhead. Aside form this fact, if you haven't
> >> already, you will want to save the query for the report as a stored
> >> procedure, this will improve performance and response time: due to the
> >> stored procedure having a predefined query plan. Also, you will want
> >> to run the stored procedure/query against the Database Engine Tuning
> >> Advisor to determine if any table indexes will further improve your
> >> performance based on this query. Hope this helps.
> >> Regards,
> >> Enrique Martinez
> >> Sr. Software Consultant|||The rendering is at the Server. It is NOT at the client.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Matt Penner" <mattpenner2007@.gmail.com> wrote in message
news:1190992965.413678.301900@.g4g2000hsf.googlegroups.com...
> This is using SQL Server 2005 SP2 and SSRS. I'm rendering straight to
> the MS Report Viewer. Viewing the report during testing on my dev
> machine as well as from the server after deployment seem to give the
> same response times. This would make sense if the rendering is purely
> on the client side. I have 1.5GB of ram on a P4 3GHz WinXP machine,
> but I usually have several things open like Visual Studio, SQL
> Management Studio, etc.
> If the Report Viewer is entirely client side then there's not much I
> can do about it. I didn't realize this is how it works. We have
> several different computer models throughout our network so optimizing
> the client side would to be difficult at best.
> Thanks, I'll just live with it. :)
> If there are any other comments out there that would be great but I'll
> just assume this is the way it is until MS develops a better viewer.
> On Sep 26, 2:54 pm, "Bruce L-C [MVP]" <bruce_lcNOS...@.hotmail.com>
> wrote:
>> RS currently renders in RAM. The amount of data is what matters. My guess
>> is
>> that the 2 seconds is enough to bring over the data and the rest is
>> rendering. The document map must require more compute time.
>> A couple of questions, what are you rendering to? HTML? PDF? that makes a
>> difference as well. Also, you didn't mention what version you are on. How
>> much RAM is on your box?
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "Matt Penner" <mattpenner2...@.gmail.com> wrote in message
>> news:1190843034.956283.72260@.57g2000hsv.googlegroups.com...
>> > Well, as I said, the raw query returns results in <2 seconds. This
>> > purely in development right now and we'll most likely move this to
>> > stored procedures in the future but that doesn't seem to be the issue.
>> > The waiting time seems to be on the rendering side.
>> > If SSRS is fast and the overhead of IIS is causing the issue (seems
>> > unlikely) are there any good sites to look at to help improve this?
>> > All I've been able to find are the MS recommended enterprise rollout
>> > with several servers. We don't have these kind of resources for SSRS,
>> > and currently there are only 4 reports and 4 users. I am disappointed
>> > that it takes this long to render simple reports when we're not even
>> > in production capacity.
>> > Thanks,
>> > Matt
>> > On Sep 24, 6:22 pm, EMartinez <emartinez...@.gmail.com> wrote:
>> >> On Sep 24, 10:19 am, Matt Penner <mattpenner2...@.gmail.com> wrote:
>> >> > I have a somewhat complex query that takes about 2 seconds to run in
>> >> > Query Analyzer. Yet it takes a full 30 seconds to generate the
>> >> > report. If I remove the document map then it takes ~10 seconds. It
>> >> > seems that there is just tremendous overhead or some other problem
>> >> > when generating reports.
>> >> > Does anyone have any suggestions how I can speed this up? We need
>> >> > up
>> >> > to the minute data so I can't really use a snapshot.
>> >> > Thanks!
>> >> > Matt Penner
>> >> In general, a deployed report has to deal w/IIS and the SSRS web
>> >> service, which adds overhead. Aside form this fact, if you haven't
>> >> already, you will want to save the query for the report as a stored
>> >> procedure, this will improve performance and response time: due to the
>> >> stored procedure having a predefined query plan. Also, you will want
>> >> to run the stored procedure/query against the Database Engine Tuning
>> >> Advisor to determine if any table indexes will further improve your
>> >> performance based on this query. Hope this helps.
>> >> Regards,
>> >> Enrique Martinez
>> >> Sr. Software Consultant
>|||Ahh, that did seem a little strange but I haven't had the time to look
into it.
Anyway, the server is a dual Xeon 3Ghz Dual Core with 4GB of Ram,
although it is a pretty heavy lifter for one of our SQL databases.
SSRS was more of a test and afterthought on this server. It is quite
possible that SQL 2005 is hogging all the resources. What's the best
way to view the resources being used by SSRS and alter this? Should
SQL server and SSRS play nicely together or do I really need to
manually allocate memory to each?
Thanks,
Matt
On Sep 28, 9:14 am, "Bruce L-C [MVP]" <bruce_lcNOS...@.hotmail.com>
wrote:
> The rendering is at the Server. It is NOT at the client.
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Matt Penner" <mattpenner2...@.gmail.com> wrote in message
> news:1190992965.413678.301900@.g4g2000hsf.googlegroups.com...
> > This is using SQL Server 2005 SP2 and SSRS. I'm rendering straight to
> > the MS Report Viewer. Viewing the report during testing on my dev
> > machine as well as from the server after deployment seem to give the
> > same response times. This would make sense if the rendering is purely
> > on the client side. I have 1.5GB of ram on a P4 3GHz WinXP machine,
> > but I usually have several things open like Visual Studio, SQL
> > Management Studio, etc.
> > If the Report Viewer is entirely client side then there's not much I
> > can do about it. I didn't realize this is how it works. We have
> > several different computer models throughout our network so optimizing
> > the client side would to be difficult at best.
> > Thanks, I'll just live with it. :)
> > If there are any other comments out there that would be great but I'll
> > just assume this is the way it is until MS develops a better viewer.
> > On Sep 26, 2:54 pm, "Bruce L-C [MVP]" <bruce_lcNOS...@.hotmail.com>
> > wrote:
> >> RS currently renders in RAM. The amount of data is what matters. My guess
> >> is
> >> that the 2 seconds is enough to bring over the data and the rest is
> >> rendering. The document map must require more compute time.
> >> A couple of questions, what are you rendering to? HTML? PDF? that makes a
> >> difference as well. Also, you didn't mention what version you are on. How
> >> much RAM is on your box?
> >> --
> >> Bruce Loehle-Conger
> >> MVP SQL Server Reporting Services
> >> "Matt Penner" <mattpenner2...@.gmail.com> wrote in message
> >>news:1190843034.956283.72260@.57g2000hsv.googlegroups.com...
> >> > Well, as I said, the raw query returns results in <2 seconds. This
> >> > purely in development right now and we'll most likely move this to
> >> > stored procedures in the future but that doesn't seem to be the issue.
> >> > The waiting time seems to be on the rendering side.
> >> > If SSRS is fast and the overhead of IIS is causing the issue (seems
> >> > unlikely) are there any good sites to look at to help improve this?
> >> > All I've been able to find are the MS recommended enterprise rollout
> >> > with several servers. We don't have these kind of resources for SSRS,
> >> > and currently there are only 4 reports and 4 users. I am disappointed
> >> > that it takes this long to render simple reports when we're not even
> >> > in production capacity.
> >> > Thanks,
> >> > Matt
> >> > On Sep 24, 6:22 pm, EMartinez <emartinez...@.gmail.com> wrote:
> >> >> On Sep 24, 10:19 am, Matt Penner <mattpenner2...@.gmail.com> wrote:
> >> >> > I have a somewhat complex query that takes about 2 seconds to run in
> >> >> > Query Analyzer. Yet it takes a full 30 seconds to generate the
> >> >> > report. If I remove the document map then it takes ~10 seconds. It
> >> >> > seems that there is just tremendous overhead or some other problem
> >> >> > when generating reports.
> >> >> > Does anyone have any suggestions how I can speed this up? We need
> >> >> > up
> >> >> > to the minute data so I can't really use a snapshot.
> >> >> > Thanks!
> >> >> > Matt Penner
> >> >> In general, a deployed report has to deal w/IIS and the SSRS web
> >> >> service, which adds overhead. Aside form this fact, if you haven't
> >> >> already, you will want to save the query for the report as a stored
> >> >> procedure, this will improve performance and response time: due to the
> >> >> stored procedure having a predefined query plan. Also, you will want
> >> >> to run the stored procedure/query against the Database Engine Tuning
> >> >> Advisor to determine if any table indexes will further improve your
> >> >> performance based on this query. Hope this helps.
> >> >> Regards,
> >> >> Enrique Martinez
> >> >> Sr. Software Consultant|||Try to simplify the report layout as much as possible - I typically
render to PDF, and find the following tips helpful -
= Use tables in place of lists (tables with grouping vs. nested lists)
= Avoid using cell borders - used alternate row shading instead to
differentiate rows
= Set the 'Can Grow' and 'Can Shrink' properties of report objects to
False wherever possible
= minimize the number of objects (merge empty cells in a table, for
instance)
= minimize the number of fonts
I believe the view is rendering in HTML, so think in terms of setting
up a report that will produce the smallest, simplest HTML document
that will correctly present your data.|||Hi Parker,
Thanks. These are great tips. I never thought of rendering straight
to PDF. Since the MS Report viewer doesn't seem to offer much that
pdf can't do, other than toggling sections, this may be a better
option. It makes sense that simplifying the end result may reduce the
render time.
I'll experiment with all this.
Matt
On Sep 29, 6:15 am, Parker <psm...@.iquest.net> wrote:
> Try to simplify the report layout as much as possible - I typically
> render to PDF, and find the following tips helpful -
> = Use tables in place of lists (tables with grouping vs. nested lists)
> = Avoid using cell borders - used alternate row shading instead to
> differentiate rows
> = Set the 'Can Grow' and 'Can Shrink' properties of report objects to
> False wherever possible
> = minimize the number of objects (merge empty cells in a table, for
> instance)
> = minimize the number of fonts
> I believe the view is rendering in HTML, so think in terms of setting
> up a report that will produce the smallest, simplest HTML document
> that will correctly present your data.