I have reports which are cached to decrease the time it takes to run. Is there anyway to display within the report, when the cache (snapshot) was taken?
So if it's 10:00:00 AM and the report was manually ran and cached for 60 mins. When the report is called again at 10:10:00 AM, I would like to display to the user that the data of the report is from 10:00:00 AM.
Thanks.
Let me say up front that rs:ClearSession=true didn't work :)
I have a report on RS that displays two blocks of data. The intent is to move items from one list to the other. To do so, there's a link on the detail lines which does a drill-through to another report. That report takes parameters (IDs for the record to be moved) and uses a stored procedure as it's datasource. The stored procedure takes in the parameter, does an INSERT INTO into the table that's driving which list items appear on, then returns the ID that was affected. On the report, there's just a text box stating that the item was added to the table and a "Back" text box. This Back item is a drill through back to the original report.
When we go back to the report (not really *back* since we're actually drilling through to the report anew), I want it to show the two lists with the one item moved from the one list to the other. Problem is, since the report was already run, it's showing the data from the cache. If I hit the refresh button, it updates and the item moves as it should.
I've read a number of posting on this and the only thing anyone said that should work was to use the rs:ClearSession=true option in the URL. So I generated a URL to the report, added this option. Tested that my URL worked and then changed the "Back" text box's navigation so that rather than a simple drill-through, it goes to a URL...the one with the ClearSession.
But it still doesn't work. I click the link on one of my lines, it goes to the drill through saying that it added the line to the table (I can even query the table in QA and see that it did), then I click the Back and I can see in the address box that the ClearSession arguement is there but the results still don't refresh.
I've also tried CTRL-F5 while in this state and the data doesn't refresh. The only thing that makes it re-run the stored procedure and pull in new data is the green refresh button.
Any ideas?
Thanks, Tim
Hi,
I created a report model using reporting services, deployed the model locally and then built a report based on that model. The report works fine, but any data updates don't show up, seems like data is cached. I bounced reporting services, cleared cached tables in ReportServerTempDB but of no use. Has anybody faced this problem? How can I get updated data for reports built through a report model. Any help is appreciated.
Thanks,
Sri
Dear Anyone,
View 6 Replies View RelatedI have a report whose parameters are limited by the user who is logged in. Ex. if a user had full access they would see all 100 hospitals in our hospital parameter, if another user had limited access they might only see 5 - this will vary largely on who is logged in. The data source that drives these parameters are set as follows:
Credentials stored securely in the report server, username and password are entered and Impersonate the authenticated user after a connection has been made to the data source is checked.I now want to cache this report for X minutes. So I goto Properties/Execution and select 'Cache a temporary copy of the report. Expire copy of report after a number of minutes'.
Applying this gives me the error: Credentials used to run this report are not stored.
Im getting this error when trying to set up a cache dependency...are there any special permissions etc?From CS:SqlCacheDependency dep = new SqlCacheDependency("MySite-Cache", "Products");Cache.Insert("Products", de.GetAllProductsList(), dep); From connectionStrings.config:<add name="SiteDB" connectionString="Data Source=localhost,[port]SQLEXPRESS;Integrated Security=true;User Instance=true; AttachDBFileName=|DataDirectory|ASPNETDB.MDF" providerName="System.Data.SqlClient" />Also tried this using my machinename<add name="SiteDB" connectionString="Data
Source=<machinename>,[port]SQLEXPRESS;Integrated Security=true;User
Instance=true; AttachDBFileName=|DataDirectory|ASPNETDB.MDF"
providerName="System.Data.SqlClient" /> From web.config: <caching> <sqlCacheDependency enabled="true" pollTime="10000"> <databases> <add name="MySite-Cache" connectionStringName="SiteDB" pollTime="2000"/> </databases> </sqlCacheDependency> </caching> EDIT: So making progress I can't seem to get the table registered for cache dependency:The sample i have says"aspnet_regsql.exe -E -S .SqlExpress -d aspnetdb -t Customers -et"and the command line response is "Enabling the table for SQL cache dependency..An error has happened. Details of the exception:The table 'Customers' cannot be found in the database."Where does this "Customers" table come from? There is obviously not an application specific "Customers" table in aspnetdb I'm confused probably more by the example than anything....
Is there a way to drop clean buffers at the database level instead of the server/instance level like the undocumented €œDBCC FLUSHPROCINDB (@dbid)€??
Is there a workaround for €œdbo€? to be able to flush procedure and data cache without being elevated to €œsysadmin€? server role?
PS: I am aware of the sp_recompile option that can be used to invalidate cached execution plans.
Thx.
Hi guys,
I am looking at the plan caches/cached pages from the perspective of
sys.dm_os_memory_cache_counters and sql serverlan Cache - Cache Pages
For the first one I am using
select (sum(single_pages_kb) + sum(multi_pages_kb) )
from sys.dm_os_memory_cache_counters
where type = 'CACHESTORE_SQLCP' or type = 'CACHESTORE_OBJCP'
a slight change from a query in
http://blogs.msdn.com/sqlprogrammability/
For the second just perfmon.
The first one gives me a count of about 670,000 pages only for the object and query cache and the second one gives me a total of about 100,000 pages for five type of caches including object and query.
If I am using the query from http://blogs.msdn.com/sqlprogrammability/ to determin the plan cache size
select (sum(single_pages_kb) + sum(multi_pages_kb) ) * 8 / (1024.0 * 1024.0) as plan_cache_in_GB
from sys.dm_os_memory_cache_counters
where type = 'CACHESTORE_SQLCP' or type = 'CACHESTORE_OBJCP'
it gives me about 5 GB when in fact my SQL Server it can access only max 2GB with Total and Target Server Memory at about 1.5 GB.
Does anyone have any idea what is going on?
We are generating excel report using report viewer at run time but if excel report having more then 65000 record generating error Microsoft. Reporting Services.OnDemandReportRendering.ReportRenderingException: Excel Rendering Extension: Number of rows exceed.
View 2 Replies View RelatedI have three types of specific reports that i have to create with the input parameters (range) either
1: By date (rdl 1)
2.By Month (rdl 2)
3.By Year (rdl 3)
Is it possible ( or how do I ) to create just one report template ( one rdl) with the three sets of parameters ( hiding/invisible which ever two sets base on user selection) and the output of the report will display the desired type( either by year, month or date).
I ask this because its possible to create a drill down report from year down to date etc in report designer (vs 2005). Not sure if I can create one instead of three rdls and with the 'logic' built within that template.
Thanks
Regards
Alu
I was wondering if there was any way to add a value field to a report, with the time it took for the report to Process.
It would probably be a text field with an Expression, but don't know how that would go.
I know that in Expression there is a value for ExecutionTime (when report began to run), but nothing about when it ends.Can this be done? and if yes, how?
Hi,
I am getting timeout or connection closed error on report server when i try to create snapshot for a large report on my development machine.Snapshot are getting created for small reports.
I have done following changes to rsreportserver.config
<Add Key="SQLCommandTimeoutSeconds" Value="1000000"/>
<Add Key="DatabaseQueryTimeout" Value="1000000"/>
and
IIS time out to some large value.
But everytime it shows timeout or connection closed forcibly.
Any assistance would be greatly appreciated!
I have a very basic report, pretty much just spits out the results from a storage proceedure. When I just run the stored proceed it takes about 36 seconds to run, and returns 532K records. When i run the report it takes a little over three minutes. The SQL Reporting server is on a seperate box, but its database resides on the same system as the database in which i am reporting up against. Any thoughts on what I could do to improve speeds on the reporting side I would appreciate it. Running SQL 2005 SP2, the report just takes in a date for a parameter and spits out financial holdings for that particular day.
View 5 Replies View RelatedThere are three tables Loans, CDs and Account.
I want to create a report based on above three tables
It looks like as follows;
Program Summary
Yesterday/Current
MTD
Previous Month
YTD
Amount
Number
Amount
Number
Amount
Number
Amount
Number
Portfolio
Loans Commenced
$
#
$
#
$
#
$
#
Loans Closed
$
#
$
#
$
#
$
#
CDs Commenced
$
#
$
#
$
#
$
#
CDs Closed
$
#
$
#
$
#
$
#
Loan table - Amount, State
CD - Amount, State
Account - CommencedOnDate(date time), closeondate(date time)
Yesterday - Loans/CDs started yesterday(calander date..not last 24 hours)
MTD - " " First of this month to yesterday
Previous Month - " " during previous month
YTD - " " Loans/CDs started from 1 of this year to yesterday
Loan and cd states are Commenced and Closed
Based on this I have to create a report. But this time format seems to be really complex.
Can anyone help me to write the query pls?
Hi Friends,
Is it possible to give a name at run time to a report when we try to download it in any format.
Do we have any control over the report name for e.g. the report name can be passed at parameter value.
Thanks,
Novin
In a dataset for a report, one of the column is "age group" with 6 different type of 10-year age groupings, <40, 40-49, 50-59, 60-69, 70-79, 80+. I am having a hard time to filter in/out the "<40" and "80+" in the report. SSRS does not seem to understand the "<" and "+" in a string. I need to be able to filter in/out one/both of this two age groupings on some matrix table. Any suggestion. Thanks.
View 1 Replies View RelatedHi there,
Is there anyway of subscribing to a report to run for every 1 hour between the times off 9am to 5pm only ?
I cant seem to find the ability to set time intervals anywhere
Hi,
I have setup report subcription with stored credential using a valid domain account and "Use as Window Credentials..." checked. Everything tested fine and works properly when the report is viewed in a web application. The problem is this report renders fine in the browser 80% of the time and occasionally it gives me Logon failure error:
An error has occurred during report processing (rsProcessingAborted)
Cannot impersonate user for data source 'Pinnacle'. (rsErrorImpersonatingUser)
Logon failed. (rsLoginFailed)
Logon failed: unknown user name or bad password. (Exception from HRESULT: 0x8007052E)
Since the report is working 80% of the time and the user domain account hasn't changed. I am confused why I am getting bad user account error. Please help!
Thanks.
-SouBee
I have a report that is embedded in a .net 2.0 web form. It works. I would like to be able to modify some of the elements in the report as the web form loads. I have tried the following code that did NOT work:
Dim myImage1 As New Image
myImage1 = Me.ReportViewer1.FindControl("Image1")
myImage1.ImageUrl = "Images/small_info_logo.jpg"
Does anyone know of a site that has some godo examples and object models on how to do this?
Thanks
Chuck
I have a Visual Studio 2005 web application Front End.
The front end is basically only used as a series of links to SQL Reports.
It seems that when you first click the links to access the reports, there is a slow response time to first load the report viewer. Then after a while of accessing the page, I suppose some of the components are cached so the page loads faster.
Is there a way to automatically cache the pages or any other way to decrease response time on the report viewers?
Edit: To clarify, it first takes around 10-15 seconds to load the .aspx that contains the report viewer. Then it takes around 5-10 seconds to load the report viewer and the report. Is there any way to minimize these times? It seems to speed up the more the page is accessed, but is there a sure-fire way?
I know I can display the time that the report was generated, but can I display how LONG it took for the report to complete?
From when the first query was executed to the time all the data was returned and/or the time it took for the report to be rendered back to the end user?
Thanks!
I'm trying to clean up our report server and prune reports that are no longer in use by our users. Is there a way to find out the last time a report was accessed?
Thanks.
I was curious to know if there is a way to create a report that will show when the last time all reports on our server were run. We have a lot of reports on our server made by multiple people that may or may not be in use anymore and we're trying to clean this up a bit. I am currently running SQL Server 2000. I realize there is a history that each report has and I'm wondering if it's possible to pull from that if it is stored somewhere that is accessible. Any help or direction would be much appreciated as I am not even sure where to start.....
View 9 Replies View RelatedOn Microsoft performance monitor, what is the difference between SQL Server Cache Manager: Cache Hit Ratio and SQL
Server Buffer Manager: Buffer Cache Hit Ratio? We have a production server where the buffer cache hit ratio is
consistently at 99%, which is normal. However, the cache hit ratio is 73%. What is the difference between the two
hit ratios, and why would we have such a significant difference between the two?
Any help or advice would be much appreciated.
There are three tables Loans, CDs and Account.
I want to create a report based on above three tables
It looks like as follows;
Program Summary
Yesterday/Current
MTD
Previous Month
YTD
Amount
Number
Amount
Number
Amount
Number
Amount
Number
Portfolio
Loans Commenced
$
#
$
#
$
#
$
#
Loans Closed
$
#
$
#
$
#
$
#
CDs Commenced
$
#
$
#
$
#
$
#
CDs Closed
$
#
$
#
$
#
$
#
Loan table - Amount, State
CD - Amount, State
Account - CommencedOnDate(date time), closeondate(date time)
Yesterday - Loans/CDs started yesterday(calander date..not last 24 hours)
MTD - " " First of this month to yesterday
Previous Month - " " during previous month
YTD - " " Loans/CDs started from 1 of this year to yesterday
Loan and cd states are Commenced and Closed
Based on this I have to create a report. But this time format seems to be really complex.
Can anyone help me to write the query pls?
Hi all,
I am using client reports and SQL server 2005. I have created reports that accepts datasets and displays in the reports. I am also displaying the client's product image in the reports.
The problem I am facing is this.
When I export the report to excel for the first time every thing is fine. The issue is when I export after the second time.
From the second time whenever i export the report to EXCEL, I see that the second excel sheet does not follow the same alignment and format the first one had.
I have a report which is fairly simple but takes a very long time..
It involves the incidents being counted by categories hence it has several Union All.
Also the report numbers are generetd through 2 tables hence within every Union All tehre is a left or an Inner join.
sample code:
SELECT
1 Sort_Order,
COUNT(*) AS Call_Count,
'Incident Resolved at Level 1' AS Count_Type
FROM HOUAPPS237.CallsAndIncidents.dbo.PROBSUMMARYM1 T1
INNER JOIN HOUAPPS237.CallsAndIncidents.dbo.PROBSUMMARYM2 T2
ON T1.NUMBERPRGN = T2.NUMBERPRGN
WHERE PROBLEM_STATUS = 'closed'
AND T2.THIRD_ASSIGNEE IS NULL
AND T2.THIRD_ASSIGNMENT IS NULL
AND T1.SECONDARY_ASSIGNEE IS NULL
AND HAL_FIRST_RES='t'
AND DATEPART(mm, DATEADD(hour, -@offset, CAST(T1.OPEN_TIME AS DATETIME))) = @MONTH
AND DATEPART(yy, DATEADD(hour, -@offset, CAST(T1.OPEN_TIME AS DATETIME))) = @YEAR
AND T1.OTI_ORIGINATOR IN (SELECT Userid FROM HOUAPPS286.HALServiceDesk.dbo.ServiceCenterAgents)
UNION ALL
-- Calls RESOLVED BY L2
SELECT
2 Sort_Order,
COUNT(*) AS Call_Count,
'Incidents Resolved at Level 2 or 3' AS Count_Type
FROM HOUAPPS237.CallsAndIncidents.dbo.PROBSUMMARYM1 T1
LEFT JOIN HOUAPPS237.CallsAndIncidents.dbo.PROBSUMMARYM2 T2
ON T1.NUMBERPRGN = T2.NUMBERPRGN
WHERE (HAL_FIRST_RES<>'t' OR HAL_FIRST_RES IS NULL)
AND PROBLEM_STATUS = 'closed'
AND DATEPART(mm, DATEADD(hour, -@offset, CAST(T1.OPEN_TIME AS DATETIME))) = @MONTH
AND DATEPART(yy, DATEADD(hour, -@offset, CAST(T1.OPEN_TIME AS DATETIME))) = @YEAR
AND T1.OTI_ORIGINATOR IN (SELECT Userid FROM HOUAPPS286.HALServiceDesk.dbo.ServiceCenterAgents)
UNION ALL
could you suggest what might be the reason why teh report churns for so long.
thanks,
kiran.
Hi,
When we generate a report with an account that is in the admin group it takes 2-3 seconds but when we do it with another user it takes over 2 minutes... any reason for this?
(The "Report is being generated" thing, by the way)
Not sure if my question is clear, I've been looking and searching for the past 2 hours but can't find anything remotly close to that problem... any help would be appreaciated!
Thanks
I ran a matrix report and chose to export it to excel. It was taking a very long time and then the error message below popped up. I can export other matrix reports to excel. Can someone shed some light on where I might determine is the cause of the problem?
Thread was being aborted.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.Exception: Thread was being aborted.
Source Error:
An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.
Stack Trace:
[Exception: Thread was being aborted.]
[Exception: An error occurred during rendering of the report.]
[Exception: An error occurred during rendering of the report.]
[Exception: The operation has timed out. The report server has canceled the operation.]
Microsoft.Reporting.WebForms.ServerReport.ServerUrlRequest(Boolean isAbortable, String url, Stream outputStream, String& mimeType, String& fileNameExtension) +882
Microsoft.Reporting.WebForms.ServerReport.InternalRender(Boolean isAbortable, String format, String deviceInfo, NameValueCollection urlAccessParameters, Stream reportStream, String& mimeType, String& fileNameExtension) +1312
Microsoft.Reporting.WebForms.ServerReportControlSource.RenderReport(String format, String deviceInfo, NameValueCollection additionalParams, String& mimeType, String& fileExtension) +215
Microsoft.Reporting.WebForms.ExportOperation.PerformOperation(NameValueCollection urlQuery, HttpResponse response) +204
Microsoft.Reporting.WebForms.HttpHandler.ProcessRequest(HttpContext context) +137
System.Web.CallHandlerExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute() +499
System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously) +177
Has anyone been able to get time calculations to show up in Report Builder? I'm using the BI Wizard standard calculations for YTD & Period over period growth, but I can't figure out how to use them within Report Builder. They are available in Report Designer, however. I did find the info about assigning the calculations to a measure group. I tried that, but it didn't have any affect.
Any help is appreciated.
I need to pass the out put from a stored procedure as an input parameter to another stored procedure. I created a data set for each stored procedure. Fron the second data set parameter tab, I added a parameter to refer to the field from the first data set.
I get the error Fiels can not be used in query parameter expressions.
I tried two simple queries instead of stored procedures with first query feeding the second query. I get the same error. Is there any other other way to accomplish this?
Thank You.
Hi, I am using sql server 2005 entreprise edition. I am just wondering
when I have a report with parameters and the report uses stored
procedure to retrieve the result set for use in the report, if two
people from different places are trying to view the report at the same
time, and each of them use different set of parameters.
Will they be able to view their report correctly??
I have a report that I would like to run in the evening due to high database usage that required a user to fill in selected parameters. Is there anyway to allow a user to fill in the report parameters and have it scheduled to run at 3:00 AM.
Thanks,
Chris