Calculated member (summing quarters)

Hi,

I have a minor problem. I need to make calculated member because of reporting in Quartal basis.

I have fiscal month, fiscal year and fiscal quarter. I'd like to sum those figures like: where fiscal year is 2006,2005 and fiscal month on 1 and quarter is Q1

Thanks for your help.

Dear GHano,

Check this example and tell me if it help's you.

Code Snippet

CREATE MEMBER CURRENTCUBE.[MEASURES].CM_ContaLiquidar_CRT7
AS
IIF(((ANCESTOR
(
[DimStructure].[Entidade_ID - Carteira_ID - Mesa_ID].CurrentMember,
[DimStructure].[Entidade_ID - Carteira_ID - Mesa_ID].[Carteira_ID]
),
[Measures].[CM_ContaLiquidar]))<0,0,[Measures].[CM_ContaLiquidar])
,
VISIBLE = 1;

regards!|||

Dear Pedro,

Thank you for your help. I need to test this.

B.R

GHano

Calculated member (summing quarters)

Hi,

I have a minor problem. I need to make calculated member because of reporting in Quartal basis.

I have fiscal month, fiscal year and fiscal quarter. I'd like to sum those figures like: where fiscal year is 2006,2005 and fiscal month on 1 and quarter is Q1

Thanks for your help.

Dear GHano,

Check this example and tell me if it help's you.

Code Snippet

CREATE MEMBER CURRENTCUBE.[MEASURES].CM_ContaLiquidar_CRT7
AS
IIF(((ANCESTOR
(
[DimStructure].[Entidade_ID - Carteira_ID - Mesa_ID].CurrentMember,
[DimStructure].[Entidade_ID - Carteira_ID - Mesa_ID].[Carteira_ID]
),
[Measures].[CM_ContaLiquidar]))<0,0,[Measures].[CM_ContaLiquidar])
,
VISIBLE = 1;

regards!|||

Dear Pedro,

Thank you for your help. I need to test this.

B.R

GHano

Calculated fields not displaying in the layout

I am continually seeing an issue where I write a query with some calculations
in it and when I put that same query in reporting services those calculated
fields do not display in the report. I get a warning saying those particular
fields is not part of the result set.
I can usually get around this by creating a calulated field in Reporting
Services but I'd much rather do the math in my query. Here's an example of
my latest issue:
SELECT *
FROM
( SELECT dense_rank() over (partition by openbusinessdate order by
(NVL(GUEST_CHECK_HIST.closeDatetime, GUEST_CHECK_HIST.openDatetime) -
GUEST_CHECK_HIST.openDatetime) desc) dr,
GUEST_CHECK_HIST.openbusinessdate,
GUEST_CHECK_HIST.checkNum as checkNum,
GUEST_CHECK_HIST.tableRef as Tablenum,
GUEST_CHECK_HIST.openDatetime as opentime,
GUEST_CHECK_HIST.closeDatetime as closetime,
(NVL(GUEST_CHECK_HIST.closeDatetime, GUEST_CHECK_HIST.openDatetime) -
GUEST_CHECK_HIST.openDatetime) * 1440 AS duration,
GUEST_CHECK_HIST.numGuests as numguests,
substr(LOCATION_HIERARCHY_ITEM.name,3,4) AS locName,
REVENUE_CENTER.nameMaster AS rvcName,
initcap(EMPLOYEE.firstName) as fname,
initcap(EMPLOYEE.lastName) as lname
FROM EMPLOYEE RIGHT OUTER JOIN REVENUE_CENTER RIGHT OUTER JOIN
GUEST_CHECK_HIST
LEFT OUTER JOIN LOCATION_HIERARCHY_ITEM ON GUEST_CHECK_HIST.locationID = LOCATION_HIERARCHY_ITEM.locationID
ON GUEST_CHECK_HIST.revenueCenterID = REVENUE_CENTER.revenueCenterID
ON GUEST_CHECK_HIST.employeeID = EMPLOYEE.employeeID
WHERE (GUEST_CHECK_HIST.organizationID = 2000) AND
(GUEST_CHECK_HIST.openBusinessDate = :begindate))
where dr <=100
as you can guess what I'm calling duration does do display in the report.
Any help would be greatly appreciated.Never mind. I resolved the issue.
Thanks
"Zach" wrote:
> I am continually seeing an issue where I write a query with some calculations
> in it and when I put that same query in reporting services those calculated
> fields do not display in the report. I get a warning saying those particular
> fields is not part of the result set.
> I can usually get around this by creating a calulated field in Reporting
> Services but I'd much rather do the math in my query. Here's an example of
> my latest issue:
> SELECT *
> FROM
> ( SELECT dense_rank() over (partition by openbusinessdate order by
> (NVL(GUEST_CHECK_HIST.closeDatetime, GUEST_CHECK_HIST.openDatetime) -
> GUEST_CHECK_HIST.openDatetime) desc) dr,
> GUEST_CHECK_HIST.openbusinessdate,
> GUEST_CHECK_HIST.checkNum as checkNum,
> GUEST_CHECK_HIST.tableRef as Tablenum,
> GUEST_CHECK_HIST.openDatetime as opentime,
> GUEST_CHECK_HIST.closeDatetime as closetime,
> (NVL(GUEST_CHECK_HIST.closeDatetime, GUEST_CHECK_HIST.openDatetime) -
> GUEST_CHECK_HIST.openDatetime) * 1440 AS duration,
> GUEST_CHECK_HIST.numGuests as numguests,
> substr(LOCATION_HIERARCHY_ITEM.name,3,4) AS locName,
> REVENUE_CENTER.nameMaster AS rvcName,
> initcap(EMPLOYEE.firstName) as fname,
> initcap(EMPLOYEE.lastName) as lname
> FROM EMPLOYEE RIGHT OUTER JOIN REVENUE_CENTER RIGHT OUTER JOIN
> GUEST_CHECK_HIST
> LEFT OUTER JOIN LOCATION_HIERARCHY_ITEM ON GUEST_CHECK_HIST.locationID => LOCATION_HIERARCHY_ITEM.locationID
> ON GUEST_CHECK_HIST.revenueCenterID = REVENUE_CENTER.revenueCenterID
> ON GUEST_CHECK_HIST.employeeID = EMPLOYEE.employeeID
> WHERE (GUEST_CHECK_HIST.organizationID = 2000) AND
> (GUEST_CHECK_HIST.openBusinessDate = :begindate))
> where dr <=100
> as you can guess what I'm calling duration does do display in the report.
> Any help would be greatly appreciated.

calculated fields

Hey All,
I wondering how you do a calculated field in Reporting
Services. I need to be able to iterate through two
seperate tables and figure out the activation rate. So I
thought I could use a calcuated field but when I do it, it
tries to apply the sum function when I drag that field out
of the fields list. When I remove the sum function I get
an error.
= Fields!ID.Value, "MTReg"/ Fields!ID.Value, "MTAct" I
don't get it how do I do this?I'm afraid that's not possible.
You'll need to calculate it in a query
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"bmurtha" <anonymous@.discussions.microsoft.com> wrote in message
news:3c9f01c472c5$eb5b27c0$a401280a@.phx.gbl...
> Hey All,
> I wondering how you do a calculated field in Reporting
> Services. I need to be able to iterate through two
> seperate tables and figure out the activation rate. So I
> thought I could use a calcuated field but when I do it, it
> tries to apply the sum function when I drag that field out
> of the fields list. When I remove the sum function I get
> an error.
> = Fields!ID.Value, "MTReg"/ Fields!ID.Value, "MTAct" I
> don't get it how do I do this?

Calculated DateTime Parameters Format Problem

Hi
I am having a lot of trouble getting reporting services to accept the
format of my datetime parameters. I keep getting a cannot convert char
string to small datetime errors either in visual studio when I preview
the report or if builds correctly there then it errrors out when run
through the report manager. I am developing these reports on a New
Zealand server which follows the British DateTime Format.
Initially I calculated the dates using datasets that calculated
various dates to populate a dropdown list by running the following
SQL statement, excuse the mess, This statement runs in query analyser
and in the reporting dataset editor without problems.
set DATEFIRST 1
select "StartDate"= Convert(VARCHAR,GetDate(),112), "Label" = 'Today',
"Sort" = 1
UNION
Select Convert(VARCHAR,GetDate()-1,112),'Yesterday',2
UNION
select CONVERT(VARCHAR,DATEADD(dd, 1 - DATEPART(dw, getdate()),
getdate()),112),'Current Week', 3
UNION
select (Convert(VarChar, (DATEADD(dd, 1 - (DATEPART(dw, getdate())+7),
getdate())),112)),'Previous Week', 4
UNION
Select Convert(DateTime,DATEADD(mm, DATEDIFF(mm,0,getdate()),
0),112),'Current Month',5
UNION
Select Convert(DateTime,DATEADD(mm, DATEDIFF(mm,0,getdate())-1,
0),112),'Previous Month',6
UNION
Select Convert(DateTime,DATEADD(yy, DATEDIFF(yy,0,getdate()),
0),112),'Current Year',7
Order By Sort
It even runs in preview but when run from the report manager I get the
cannot convert varchar to small datetime error.
I retreated from this method and thought I would just use .net
calculated default values for the dates ie =DateTime.Today , this also
works in visual studio but falls over in the report manager on the
previously mentioned error.
All the dates are stored in tables in standard sqlserver datetime
format.
Could someone please provide a list of suggestions/bestpractices for
working with date parameters in SQL Server that are not in US format?
Thankyou
Michael BlackI would not convert the dates to varchar in your SQL. Instead I would format
them in the report in the format that you want.
"Mike" wrote:
> Hi
> I am having a lot of trouble getting reporting services to accept the
> format of my datetime parameters. I keep getting a cannot convert char
> string to small datetime errors either in visual studio when I preview
> the report or if builds correctly there then it errrors out when run
> through the report manager. I am developing these reports on a New
> Zealand server which follows the British DateTime Format.
> Initially I calculated the dates using datasets that calculated
> various dates to populate a dropdown list by running the following
> SQL statement, excuse the mess, This statement runs in query analyser
> and in the reporting dataset editor without problems.
> set DATEFIRST 1
> select "StartDate"= Convert(VARCHAR,GetDate(),112), "Label" = 'Today',
> "Sort" = 1
> UNION
> Select Convert(VARCHAR,GetDate()-1,112),'Yesterday',2
> UNION
> select CONVERT(VARCHAR,DATEADD(dd, 1 - DATEPART(dw, getdate()),
> getdate()),112),'Current Week', 3
> UNION
> select (Convert(VarChar, (DATEADD(dd, 1 - (DATEPART(dw, getdate())+7),
> getdate())),112)),'Previous Week', 4
> UNION
> Select Convert(DateTime,DATEADD(mm, DATEDIFF(mm,0,getdate()),
> 0),112),'Current Month',5
> UNION
> Select Convert(DateTime,DATEADD(mm, DATEDIFF(mm,0,getdate())-1,
> 0),112),'Previous Month',6
> UNION
> Select Convert(DateTime,DATEADD(yy, DATEDIFF(yy,0,getdate()),
> 0),112),'Current Year',7
> Order By Sort
> It even runs in preview but when run from the report manager I get the
> cannot convert varchar to small datetime error.
> I retreated from this method and thought I would just use .net
> calculated default values for the dates ie =DateTime.Today , this also
> works in visual studio but falls over in the report manager on the
> previously mentioned error.
> All the dates are stored in tables in standard sqlserver datetime
> format.
> Could someone please provide a list of suggestions/bestpractices for
> working with date parameters in SQL Server that are not in US format?
> Thankyou
> Michael Black
>

Calculated cells in Excel?

Hi,
We're evaluating Reporting Services for a client. I like the functionality,
but it's unclear whether we can manipulate Excel output. For example, our
client will want to generate or populate a spreadsheet that has multiple
worksheets, with calculated values that pull values from one or more of
those sheets.
Does RS support this level of complexity?
Thanks in advance,
Ian LurieI don't know about creating calculated fields, but you can put tables
onto different sheets by setting the page break after table property
to true. The only reporting product I have seen that allows you to
export formulas is actuate. Should you find a way to do it with
reporting services, *please* let me know! We are interested in it
too-
Thanks,
John Hennesey
johnhennesey82@.hotmail.com
"Ian Lurie" <ian@.portentinteractive.com> wrote in message news:<#eXuLqRXEHA.1356@.TK2MSFTNGP09.phx.gbl>...
> Hi,
> We're evaluating Reporting Services for a client. I like the functionality,
> but it's unclear whether we can manipulate Excel output. For example, our
> client will want to generate or populate a spreadsheet that has multiple
> worksheets, with calculated values that pull values from one or more of
> those sheets.
> Does RS support this level of complexity?
> Thanks in advance,
> Ian Lurie|||We export formulas as along as they contain only report item references.
--
Nico Cristache [MSFT]
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"John Hennesey" <johnhennesey82@.hotmail.com> wrote in message
news:9f049b3b.0406281103.54baf474@.posting.google.com...
> I don't know about creating calculated fields, but you can put tables
> onto different sheets by setting the page break after table property
> to true. The only reporting product I have seen that allows you to
> export formulas is actuate. Should you find a way to do it with
> reporting services, *please* let me know! We are interested in it
> too-
> Thanks,
> John Hennesey
> johnhennesey82@.hotmail.com
> "Ian Lurie" <ian@.portentinteractive.com> wrote in message
news:<#eXuLqRXEHA.1356@.TK2MSFTNGP09.phx.gbl>...
> > Hi,
> >
> > We're evaluating Reporting Services for a client. I like the
functionality,
> > but it's unclear whether we can manipulate Excel output. For example,
our
> > client will want to generate or populate a spreadsheet that has multiple
> > worksheets, with calculated values that pull values from one or more of
> > those sheets.
> >
> > Does RS support this level of complexity?
> >
> > Thanks in advance,
> >
> > Ian Luriesql

calculate durations from start/stop datetimes (was "reporting...")

gday, how do i see what i wanted to seeTry looking at what you want to look at.|||database is fubar again, blindman, there are two posts missing from this thread

Calculate Age

I need to caluclate the age using the birth date. how do i go about calculating the age using reporting service?

your regrads
Angela Goh

Hello:
There are two(2) ways that I use and they are as follows - but I prefer the second method.....
1st method - in Report Properties "paste" the following code:
Public Function Age(ByVal Date_Parm)
Dim date1 as string
date1 = Date_Parm.insert(4,"/")
Dim date2 as string
date2 = date1.insert(7,"/")
Dim DateOfBirth as DateTime
DateofBirth = Date2
Dim AgeCalc as integer

Dim intDOB As Integer = _
DateOfBirth.Day + DateOfBirth.Month * 100 + DateOfBirth.Year * 10000
Dim intToday As Integer = _
DateTime.Now.Day + DateTime.Now.Month * 100 + DateTime.Now.Year * 10000
AgeCalc = (intToday - intDOB) / 10000

Return AgeCalc
End Function

Then within the field in Layout where you want to display AGE use =Code.Age(whatever the birthdate field name is)
But I prefer to (not trying to create any cotroversy here) do everything within Stored Procedures and limit any custom code within Reporting Services - So I
Create a User Defined Function within SQL - the code is as follows:
CREATE function dbo.fn_GetAge
(@.in_DOB AS datetime,@.now as datetime)
returns int
as
begin
DECLARE @.age int
IF cast(datepart(m,@.now) as int) > cast(datepart(m,@.in_DOB) as int)
SET @.age = cast(datediff(yyyy,@.in_DOB,@.now) as int)
else
IF cast(datepart(m,@.now) as int) = cast(datepart(m,@.in_DOB) as int)
IF datepart(d,@.now) >= datepart(d,@.in_DOB)
SET @.age = cast(datediff(yyyy,@.in_DOB,@.now) as int)
ELSE
SET @.age = cast(datediff(yyyy,@.in_DOB,@.now) as int) -1
ELSE
SET @.age = cast(datediff(yyyy,@.in_DOB,@.now) as int) - 1
RETURN @.age
end

Then within the stored procedure I use as the data set within Reporting Services my SQL example is:
Select PM_Birth_Date, PM_Age from Patient_Master
Where PM_Age =
Case when isdate(Pm_Birth_Date) = 1 then dbo.fn_getage(PM_Birth_Date, getdate()) else '999' end
The isdate determines if PM_Birth_Date is a valid date and if so I execute the UDF GetAge to compute the age, if Birth date is not a valid date I assign 999 as an invalid age.
Just my personal preference, but I prefer to do everything within Stored Procedures within SQL and present all data, calculations, etc. to reporting services. The only conditional formatting I conduct in RS is color of fields.
Hope this helps!
Best Regards - Joe

|||

hi Joe,

for the 1st method, where do you paste the code into the report properties, at the custom code?

your regards

Angela

|||Yes,|||

hi joe,

i try using ur SP method. but encounter some error. so like to check with you did u create a field in the table for the age? because for my db i don't store age i jux need to generate the age.

thanks

Angie

|||Hello:
Yes, we keep all kind of things in the DB (kind of inherited) which we re-compute on the "fly".
The following will work in your situation and Age ends up being a calculated "work field"
Select PM_Birth_Date,
Age = Case When isdate(PM_Birth_Date) = 1 then dbo.fn_getage (PM_Birth_Date, getdate()) else '999' END
From Patient_Master
Best Regards,
Joe|||

hi joe,
thanks for the sp. i try another way by calling the function and not using the case to check the date. bcause i encounter some error if i try using case to check for the date. therefor i skip that step n use the function directly.
thanks
Angela

caching questions

I am new to Reporting Services and would like to know if it can do the
following:
1. If I cache a report with timed expiration such as 48 hours, can I
programatically expire a specific report before it would have expired
normally?
2. When a report is generated from cache, is it possible for it to get any
"superficial" customizations at render time, such as colors or a gif?
Thanks for any help!Hi,
> 1. If I cache a report with timed expiration such as 48 hours, can I
> programatically expire a specific report before it would have expired
> normally?
We do not have straightforward method available. To do so, you could use
SetCacheOptions Method set the CacheReport false and then reset the
expiration time.
> 2. When a report is generated from cache, is it possible for it to get
any
> "superficial" customizations at render time, such as colors or a gif?
No, it's not possible
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

caching problem in sql reporting services

Hi all,
I have developed a web application which displays records stored in the database based on certain criteria supplied by the user. I have used SQL Reporting services for generating a report coresponding to the displayed records. The problem I faces is as follows.
I generated a report for the records displayed in the application. Then, I updated one of the records and again generated the report. But the change I made is not reflected in it. The report is the same as the one previously generated. But the change is reflected if I logged out of the application, again logged in and generated the report.
I checked the 'Execution properties' of the report in the Report Manager and I found that the 'Do not cache temporary copies of this report' option remains selected. But the caching problem exists. Please help if anyone knows a solution. I am desperately in need of a solution for the above problem.

Thanks,

Renju.

Had similar problem with SRS 2000 (are you using same version).

Resolved by creating a dummy parameter that passed the current date/time - forcing a new dataset everytime...

Caching in Reporting Services

Consider the following:
I have a report that takes in 2 parameters: Start Date and End Date.
If I run the reporet twice, thefirst time passing 1/1/1900-1/1/2007 as
the parameters
and then I run it again with date range 1/1/2000-1/1/2001
Will RS go back to the Database to retrieve that data? Or is it cached
and it will just generate the results immidietly.
regards,
Stas K.No caching occurs if a parameter changes in any way.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Sorcerdon" <sorcerdon@.gmail.com> wrote in message
news:1136313076.592719.46480@.g44g2000cwa.googlegroups.com...
> Consider the following:
> I have a report that takes in 2 parameters: Start Date and End Date.
> If I run the reporet twice, thefirst time passing 1/1/1900-1/1/2007 as
> the parameters
> and then I run it again with date range 1/1/2000-1/1/2001
> Will RS go back to the Database to retrieve that data? Or is it cached
> and it will just generate the results immidietly.
> regards,
> Stas K.
>|||Alright.
I heard there is a Snapshot functionality...
Using Filters Instead of Query Parameters
Reporting Services has several methods for dynamically filtering report
contents, including the following:
=B7Query parameters filter data at the source as it is retrieved.
=B7Report filters, applied to a dataset or data region, limit the data
that is displayed from a generated report.
Using filters retrieves all data, but only data that is relevant to the
user is displayed. This may be less efficient on an individual report
basis than filtering at the source. However, it lets you retrieve the
data once from the source and store in it a snapshot to serve many
different user communities. On the other hand, when using query
parameters, you must revisit the data source for each new value of the
query parameters. Filters enable you to use execution snapshots and
still get full parameterization.
This is where I got stuck - perhaps im missunderstadning the statement.
What does this mean?
regards,
Stas K.|||If you want to use snapshots combined with a filter then you could do this.
Note though that all the data is returned to reporting services and stored
for filtering later. If your data is large at all this could be problematic.
My opinion is that snapshots should be a last resort and only after a lot of
thought.
So, are you using filters or query paraemeters. You are using query
parameters if you query definition looks something like this:
select * from mytable where mydatefield > @.StartDate and mydatefield <
@.EndDate
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Sorcerdon" <sorcerdon@.gmail.com> wrote in message
news:1136315942.418004.219830@.g44g2000cwa.googlegroups.com...
Alright.
I heard there is a Snapshot functionality...
Using Filters Instead of Query Parameters
Reporting Services has several methods for dynamically filtering report
contents, including the following:
·Query parameters filter data at the source as it is retrieved.
·Report filters, applied to a dataset or data region, limit the data
that is displayed from a generated report.
Using filters retrieves all data, but only data that is relevant to the
user is displayed. This may be less efficient on an individual report
basis than filtering at the source. However, it lets you retrieve the
data once from the source and store in it a snapshot to serve many
different user communities. On the other hand, when using query
parameters, you must revisit the data source for each new value of the
query parameters. Filters enable you to use execution snapshots and
still get full parameterization.
This is where I got stuck - perhaps im missunderstadning the statement.
What does this mean?
regards,
Stas K.

Cached results and sorting.

We're doing sorting by jumping to the report, and by changing some
parameters, I change the sorting. All the sorting is done by the reporting
services tables, not the MDX queries.
It takes a long time to rerun these reports. All the MDX queries are the
same. Shouldn't these queries, or the data be cached?On report manager open up the report, properties, execution and see if you
are caching the result.
Caching behavior is set on the server.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Cindy Lee" <cindylee@.hotmail.com> wrote in message
news:%23Qf2kj18EHA.2156@.TK2MSFTNGP10.phx.gbl...
> We're doing sorting by jumping to the report, and by changing some
> parameters, I change the sorting. All the sorting is done by the
reporting
> services tables, not the MDX queries.
> It takes a long time to rerun these reports. All the MDX queries are the
> same. Shouldn't these queries, or the data be cached?
>|||I tried that, but it didn't work.
It's has some different parameters, but all the MDX queries are the same.
The parameters make the sorting different.
Is there anyway to tell it to use the same data?
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:%23FRV7118EHA.3828@.TK2MSFTNGP09.phx.gbl...
> On report manager open up the report, properties, execution and see if you
> are caching the result.
> Caching behavior is set on the server.
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Cindy Lee" <cindylee@.hotmail.com> wrote in message
> news:%23Qf2kj18EHA.2156@.TK2MSFTNGP10.phx.gbl...
> > We're doing sorting by jumping to the report, and by changing some
> > parameters, I change the sorting. All the sorting is done by the
> reporting
> > services tables, not the MDX queries.
> >
> > It takes a long time to rerun these reports. All the MDX queries are
the
> > same. Shouldn't these queries, or the data be cached?
> >
> >
>|||Oh, the different parameters make it decide not to use the cache is what I
guess is happening. It'll be nice when sorting will be part of the product
and not something we have to munge together (version 2 will have sortable
columns). Sorry, I am out of ideas here.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Cindy Lee" <cindylee@.hotmail.com> wrote in message
news:OIUlP538EHA.4028@.TK2MSFTNGP15.phx.gbl...
>I tried that, but it didn't work.
> It's has some different parameters, but all the MDX queries are the same.
> The parameters make the sorting different.
> Is there anyway to tell it to use the same data?
>
> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
> news:%23FRV7118EHA.3828@.TK2MSFTNGP09.phx.gbl...
>> On report manager open up the report, properties, execution and see if
>> you
>> are caching the result.
>> Caching behavior is set on the server.
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "Cindy Lee" <cindylee@.hotmail.com> wrote in message
>> news:%23Qf2kj18EHA.2156@.TK2MSFTNGP10.phx.gbl...
>> > We're doing sorting by jumping to the report, and by changing some
>> > parameters, I change the sorting. All the sorting is done by the
>> reporting
>> > services tables, not the MDX queries.
>> >
>> > It takes a long time to rerun these reports. All the MDX queries are
> the
>> > same. Shouldn't these queries, or the data be cached?
>> >
>> >
>>
>|||Adding
<UsedInQuery>False</UsedInQuery> Didn't help. That only works in a snapshot
execution, but then i can't change parameters from the default.
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:%239nzGa68EHA.936@.TK2MSFTNGP12.phx.gbl...
> Oh, the different parameters make it decide not to use the cache is what I
> guess is happening. It'll be nice when sorting will be part of the product
> and not something we have to munge together (version 2 will have sortable
> columns). Sorry, I am out of ideas here.
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Cindy Lee" <cindylee@.hotmail.com> wrote in message
> news:OIUlP538EHA.4028@.TK2MSFTNGP15.phx.gbl...
> >I tried that, but it didn't work.
> > It's has some different parameters, but all the MDX queries are the
same.
> > The parameters make the sorting different.
> >
> > Is there anyway to tell it to use the same data?
> >
> >
> > "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
> > news:%23FRV7118EHA.3828@.TK2MSFTNGP09.phx.gbl...
> >> On report manager open up the report, properties, execution and see if
> >> you
> >> are caching the result.
> >>
> >> Caching behavior is set on the server.
> >>
> >> --
> >> Bruce Loehle-Conger
> >> MVP SQL Server Reporting Services
> >>
> >> "Cindy Lee" <cindylee@.hotmail.com> wrote in message
> >> news:%23Qf2kj18EHA.2156@.TK2MSFTNGP10.phx.gbl...
> >> > We're doing sorting by jumping to the report, and by changing some
> >> > parameters, I change the sorting. All the sorting is done by the
> >> reporting
> >> > services tables, not the MDX queries.
> >> >
> >> > It takes a long time to rerun these reports. All the MDX queries are
> > the
> >> > same. Shouldn't these queries, or the data be cached?
> >> >
> >> >
> >>
> >>
> >
> >
>

Cached Report and Windows Authentication

I want to use cached reports with Reporting Services 2005. But when I edit the execution properties of a report, an error message answser that I can't use cache because it has a connexion to Analysis Services with Windows Authentication.

I understand why I can't do that, but I still want to do so, because the reports take too much time to load. How can I do that ?

Thanks.

You can configure the datasource in the report to run using stored credentials. In report manager, go the report and then click on the DataSource tab on the left side, you can store the credentials using that UI.|||

Thanks. I tried this and it works, but I don't see better performance...

I thought that I would not see "Generating Report, please wait" any more...

|||

Query the ExecutionLog table in the report server catalog database to see if we are submitting the queries. You can tell by looking at the "rowcount" column. You will also see how much time we are spending in processing and rendering respectively. Processing is the time that it takes for us to create the snapshot, and should be minimized by rendering from the execution cache.

|||

TimeDataRetrieval 645 0
TimeProcessing 4855 347
TimeRendering 23518 1090
Source 1 2
ByteCount 10249 10249
RowCount 183 21

Thank you, it seems that the cache is used. The first column is created the first time when the cache is generated, and the second column is created when I open the report a second time. I still got a RowCount > 0, I don't know why... Isn't there a way to tell the ReportServer the generate a .html file as cache, I think it would be much faster.

But then again, it works, so I thank you for your help.

|||We will automatically cache whatever we think is safe to cache. HTML caching can be problematic because there is some session specific information which is embedded in the HTML, so we cannot simply serve up the same HTML to multiple users.

Cached Report and Windows Authentication

I want to use cached reports with Reporting Services 2005. But when I edit the execution properties of a report, an error message answser that I can't use cache because it has a connexion to Analysis Services with Windows Authentication.

I understand why I can't do that, but I still want to do so, because the reports take too much time to load. How can I do that ?

Thanks.

You can configure the datasource in the report to run using stored credentials. In report manager, go the report and then click on the DataSource tab on the left side, you can store the credentials using that UI.|||

Thanks. I tried this and it works, but I don't see better performance...

I thought that I would not see "Generating Report, please wait" any more...

|||

Query the ExecutionLog table in the report server catalog database to see if we are submitting the queries. You can tell by looking at the "rowcount" column. You will also see how much time we are spending in processing and rendering respectively. Processing is the time that it takes for us to create the snapshot, and should be minimized by rendering from the execution cache.

|||

TimeDataRetrieval 645 0
TimeProcessing 4855 347
TimeRendering 23518 1090
Source 1 2
ByteCount 10249 10249
RowCount 183 21

Thank you, it seems that the cache is used. The first column is created the first time when the cache is generated, and the second column is created when I open the report a second time. I still got a RowCount > 0, I don't know why... Isn't there a way to tell the ReportServer the generate a .html file as cache, I think it would be much faster.

But then again, it works, so I thank you for your help.

|||We will automatically cache whatever we think is safe to cache. HTML caching can be problematic because there is some session specific information which is embedded in the HTML, so we cannot simply serve up the same HTML to multiple users.

Cached Datasets

Doing a profiler on our sql box during the generation of a SQL reporting
service report, I noticed that three queries were taking place. We have
three datasets in our report. One is to get information for paramater A,
another for Parameter B and yet the final dataset which returns the
information used to populate the report. Each time the report is run, the
three queries are run, one for each dataset.
Is it possible to cache a dataset using reporting services or SQL or some
other means to reduce hits to the database while still getting the live data
from the third dataset which is used to populate the report?
I am aware of the cached reports which we plan to use, but is there a way to
just cache one dataset? Or two?
ThanksThis is a multi-part message in MIME format.
--=_NextPart_000_000D_01C622C8.CBF0B030
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Yes, ASP.NET 2.0 introduced a built-in cache invalidation mechanism =that can work with SQL Server 7.0 and above.
And it's easy to setup. Google aspnet_regsql for more information
However this caching mechanism is only supported at the table level for =SQL Server 7 and 2000
SQL Server 2005 has more invalidation possibilities
Med Bouchenafa
"isicoder" <isicoder@.newsgroups.nospam> a =E9crit dans le message de =news: u7Cr2YqIGHA.3000@.TK2MSFTNGP14.phx.gbl...
> Doing a profiler on our sql box during the generation of a SQL =reporting > service report, I noticed that three queries were taking place. We =have > three datasets in our report. One is to get information for paramater =A, > another for Parameter B and yet the final dataset which returns the > information used to populate the report. Each time the report is run, =the > three queries are run, one for each dataset.
> > Is it possible to cache a dataset using reporting services or SQL or =some > other means to reduce hits to the database while still getting the =live data > from the third dataset which is used to populate the report?
> > I am aware of the cached reports which we plan to use, but is there a =way to > just cache one dataset? Or two?
> > Thanks
> >
--=_NextPart_000_000D_01C622C8.CBF0B030
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

Yes, ASP.NET 2.0 introduced a =built-in cache invalidation mechanism that can work with SQL Server 7.0 and =above.
And it's easy to setup. Google aspnet_regsql for more information
However this caching mechanism is only supported at the table level for SQL Server 7 and =2000
SQL Server 2005 has more invalidation possibilities
Med Bouchenafa
"isicoder" = a =E9crit dans le message de news: =u7Cr2YqIGHA.3000@.TK2MSFTNGP14.phx.gbl...> Doing a profiler on our sql box during the generation of a SQL reporting => service report, I noticed that three queries were taking =place. We have > three datasets in our report. One is to get =information for paramater A, > another for Parameter B and yet the final =dataset which returns the > information used to populate the =report. Each time the report is run, the > three queries are run, one for each =dataset.> > Is it possible to cache a dataset using =reporting services or SQL or some > other means to reduce hits to the =database while still getting the live data > from the third dataset which =is used to populate the report?> > I am aware of the cached =reports which we plan to use, but is there a way to > just cache one dataset? =Or two?> > Thanks> =>

---

--=_NextPart_000_000D_01C622C8.CBF0B030--

Cache vs Snapshot

I am new to reporting services and have created a few reports. I have
noticed that I can cache or create a snapshot of the reports.
I currently use a Crystal Command line tool to run crystal reports around
4:00 am, and I would like to do the same for some of my reports.
What is the difference and which peroforms faster?
Thanks for your help.I figured it out and thought I would put it here for someone else to learn
from.
A cache is only created when the report is first run, then it can expire
when you want it to. A snapshot runs on your schedule, and stays in history.
I would like a mix, the ability to load the report the first time from the
snapshot, but give the users the ability to run it again with date ranges.
Oh well :)
"John_g" wrote:
> I am new to reporting services and have created a few reports. I have
> noticed that I can cache or create a snapshot of the reports.
> I currently use a Crystal Command line tool to run crystal reports around
> 4:00 am, and I would like to do the same for some of my reports.
> What is the difference and which peroforms faster?
> Thanks for your help.
>|||You could implement this with a report filter. You filter the data after
it's returned, not in the query. If you want to have a cache preloaded, you
can set up a process to call the report with a NULL rendering extension.
That will execute the query without returning the results.
--
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"John_g" <John_g@.discussions.microsoft.com> wrote in message
news:71D9E727-49EA-447B-A55B-21A26592EA2F@.microsoft.com...
>I figured it out and thought I would put it here for someone else to learn
> from.
> A cache is only created when the report is first run, then it can expire
> when you want it to. A snapshot runs on your schedule, and stays in
> history.
> I would like a mix, the ability to load the report the first time from the
> snapshot, but give the users the ability to run it again with date ranges.
> Oh well :)
> "John_g" wrote:
>> I am new to reporting services and have created a few reports. I have
>> noticed that I can cache or create a snapshot of the reports.
>> I currently use a Crystal Command line tool to run crystal reports around
>> 4:00 am, and I would like to do the same for some of my reports.
>> What is the difference and which peroforms faster?
>> Thanks for your help.|||Thanks. That sounds like a little more than I currently know how to do.
You are basically saying that I can query the data with a larger date range,
or none at all, cache it, then when the user queries the information, they
are querying the info from the cache?
If so, it this easy, or am I going to have to do some aspx programming?
Thanks for your reply.
"Jeff A. Stucker" wrote:
> You could implement this with a report filter. You filter the data after
> it's returned, not in the query. If you want to have a cache preloaded, you
> can set up a process to call the report with a NULL rendering extension.
> That will execute the query without returning the results.
> --
> '(' Jeff A. Stucker
> \
> Business Intelligence
> www.criadvantage.com
> ---
> "John_g" <John_g@.discussions.microsoft.com> wrote in message
> news:71D9E727-49EA-447B-A55B-21A26592EA2F@.microsoft.com...
> >I figured it out and thought I would put it here for someone else to learn
> > from.
> >
> > A cache is only created when the report is first run, then it can expire
> > when you want it to. A snapshot runs on your schedule, and stays in
> > history.
> >
> > I would like a mix, the ability to load the report the first time from the
> > snapshot, but give the users the ability to run it again with date ranges.
> >
> > Oh well :)
> >
> > "John_g" wrote:
> >
> >> I am new to reporting services and have created a few reports. I have
> >> noticed that I can cache or create a snapshot of the reports.
> >>
> >> I currently use a Crystal Command line tool to run crystal reports around
> >> 4:00 am, and I would like to do the same for some of my reports.
> >>
> >> What is the difference and which peroforms faster?
> >>
> >> Thanks for your help.
> >>
>
>|||Probably not aspx programming. The report filter is just part of the report
design; you can filter groups (check the properties of your List, Table,
Matrix, etc.). To kick off the null report, you may be able to simply
launch a URL using the Windows scheduler. I think the syntax is this:
&rs:Format=NULL
--
Cheers,
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"John_g" <Johng@.discussions.microsoft.com> wrote in message
news:FD00DCEA-369A-4B2B-844E-C8BB1C3D509A@.microsoft.com...
> Thanks. That sounds like a little more than I currently know how to do.
> You are basically saying that I can query the data with a larger date
> range,
> or none at all, cache it, then when the user queries the information, they
> are querying the info from the cache?
> If so, it this easy, or am I going to have to do some aspx programming?
> Thanks for your reply.
>
> "Jeff A. Stucker" wrote:
>> You could implement this with a report filter. You filter the data after
>> it's returned, not in the query. If you want to have a cache preloaded,
>> you
>> can set up a process to call the report with a NULL rendering extension.
>> That will execute the query without returning the results.
>> --
>> '(' Jeff A. Stucker
>> \
>> Business Intelligence
>> www.criadvantage.com
>> ---
>> "John_g" <John_g@.discussions.microsoft.com> wrote in message
>> news:71D9E727-49EA-447B-A55B-21A26592EA2F@.microsoft.com...
>> >I figured it out and thought I would put it here for someone else to
>> >learn
>> > from.
>> >
>> > A cache is only created when the report is first run, then it can
>> > expire
>> > when you want it to. A snapshot runs on your schedule, and stays in
>> > history.
>> >
>> > I would like a mix, the ability to load the report the first time from
>> > the
>> > snapshot, but give the users the ability to run it again with date
>> > ranges.
>> >
>> > Oh well :)
>> >
>> > "John_g" wrote:
>> >
>> >> I am new to reporting services and have created a few reports. I have
>> >> noticed that I can cache or create a snapshot of the reports.
>> >>
>> >> I currently use a Crystal Command line tool to run crystal reports
>> >> around
>> >> 4:00 am, and I would like to do the same for some of my reports.
>> >>
>> >> What is the difference and which peroforms faster?
>> >>
>> >> Thanks for your help.
>> >>
>>|||To be more specfiic, launch this programmatically:
http://RSSRV/ReportServer?/Path/Report&rs:Format=NULL
--
Cheers,
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"Jeff A. Stucker" <jeff@.mobilize.net> wrote in message
news:enleZEI3EHA.1524@.TK2MSFTNGP09.phx.gbl...
> Probably not aspx programming. The report filter is just part of the
> report design; you can filter groups (check the properties of your List,
> Table, Matrix, etc.). To kick off the null report, you may be able to
> simply launch a URL using the Windows scheduler. I think the syntax is
> this:
> &rs:Format=NULL
> --
> Cheers,
> '(' Jeff A. Stucker
> \
> Business Intelligence
> www.criadvantage.com
> ---
> "John_g" <Johng@.discussions.microsoft.com> wrote in message
> news:FD00DCEA-369A-4B2B-844E-C8BB1C3D509A@.microsoft.com...
>> Thanks. That sounds like a little more than I currently know how to do.
>> You are basically saying that I can query the data with a larger date
>> range,
>> or none at all, cache it, then when the user queries the information,
>> they
>> are querying the info from the cache?
>> If so, it this easy, or am I going to have to do some aspx programming?
>> Thanks for your reply.
>>
>> "Jeff A. Stucker" wrote:
>> You could implement this with a report filter. You filter the data
>> after
>> it's returned, not in the query. If you want to have a cache preloaded,
>> you
>> can set up a process to call the report with a NULL rendering extension.
>> That will execute the query without returning the results.
>> --
>> '(' Jeff A. Stucker
>> \
>> Business Intelligence
>> www.criadvantage.com
>> ---
>> "John_g" <John_g@.discussions.microsoft.com> wrote in message
>> news:71D9E727-49EA-447B-A55B-21A26592EA2F@.microsoft.com...
>> >I figured it out and thought I would put it here for someone else to
>> >learn
>> > from.
>> >
>> > A cache is only created when the report is first run, then it can
>> > expire
>> > when you want it to. A snapshot runs on your schedule, and stays in
>> > history.
>> >
>> > I would like a mix, the ability to load the report the first time from
>> > the
>> > snapshot, but give the users the ability to run it again with date
>> > ranges.
>> >
>> > Oh well :)
>> >
>> > "John_g" wrote:
>> >
>> >> I am new to reporting services and have created a few reports. I
>> >> have
>> >> noticed that I can cache or create a snapshot of the reports.
>> >>
>> >> I currently use a Crystal Command line tool to run crystal reports
>> >> around
>> >> 4:00 am, and I would like to do the same for some of my reports.
>> >>
>> >> What is the difference and which peroforms faster?
>> >>
>> >> Thanks for your help.
>> >>
>>
>|||When I run this, what data ranges does it use? My variables are StartDate
and EndDate.
I am just wandering since I am not giving it dates, does it run for all dates?
That could be time consuming.
THanks.
"Jeff A. Stucker" wrote:
> To be more specfiic, launch this programmatically:
> http://RSSRV/ReportServer?/Path/Report&rs:Format=NULL
> --
> Cheers,
> '(' Jeff A. Stucker
> \
> Business Intelligence
> www.criadvantage.com
> ---
> "Jeff A. Stucker" <jeff@.mobilize.net> wrote in message
> news:enleZEI3EHA.1524@.TK2MSFTNGP09.phx.gbl...
> > Probably not aspx programming. The report filter is just part of the
> > report design; you can filter groups (check the properties of your List,
> > Table, Matrix, etc.). To kick off the null report, you may be able to
> > simply launch a URL using the Windows scheduler. I think the syntax is
> > this:
> >
> > &rs:Format=NULL
> >
> > --
> > Cheers,
> >
> > '(' Jeff A. Stucker
> > \
> >
> > Business Intelligence
> > www.criadvantage.com
> > ---
> > "John_g" <Johng@.discussions.microsoft.com> wrote in message
> > news:FD00DCEA-369A-4B2B-844E-C8BB1C3D509A@.microsoft.com...
> >> Thanks. That sounds like a little more than I currently know how to do.
> >>
> >> You are basically saying that I can query the data with a larger date
> >> range,
> >> or none at all, cache it, then when the user queries the information,
> >> they
> >> are querying the info from the cache?
> >>
> >> If so, it this easy, or am I going to have to do some aspx programming?
> >>
> >> Thanks for your reply.
> >>
> >>
> >> "Jeff A. Stucker" wrote:
> >>
> >> You could implement this with a report filter. You filter the data
> >> after
> >> it's returned, not in the query. If you want to have a cache preloaded,
> >> you
> >> can set up a process to call the report with a NULL rendering extension.
> >> That will execute the query without returning the results.
> >>
> >> --
> >> '(' Jeff A. Stucker
> >> \
> >>
> >> Business Intelligence
> >> www.criadvantage.com
> >> ---
> >> "John_g" <John_g@.discussions.microsoft.com> wrote in message
> >> news:71D9E727-49EA-447B-A55B-21A26592EA2F@.microsoft.com...
> >> >I figured it out and thought I would put it here for someone else to
> >> >learn
> >> > from.
> >> >
> >> > A cache is only created when the report is first run, then it can
> >> > expire
> >> > when you want it to. A snapshot runs on your schedule, and stays in
> >> > history.
> >> >
> >> > I would like a mix, the ability to load the report the first time from
> >> > the
> >> > snapshot, but give the users the ability to run it again with date
> >> > ranges.
> >> >
> >> > Oh well :)
> >> >
> >> > "John_g" wrote:
> >> >
> >> >> I am new to reporting services and have created a few reports. I
> >> >> have
> >> >> noticed that I can cache or create a snapshot of the reports.
> >> >>
> >> >> I currently use a Crystal Command line tool to run crystal reports
> >> >> around
> >> >> 4:00 am, and I would like to do the same for some of my reports.
> >> >>
> >> >> What is the difference and which peroforms faster?
> >> >>
> >> >> Thanks for your help.
> >> >>
> >>
> >>
> >>
> >
> >
>
>|||It runs for whatever the default dates are specified.
--
Cheers,
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"John_g" <Johng@.discussions.microsoft.com> wrote in message
news:26547C04-1BEA-4438-A37D-FFFE6886979C@.microsoft.com...
> When I run this, what data ranges does it use? My variables are StartDate
> and EndDate.
> I am just wandering since I am not giving it dates, does it run for all
> dates?
> That could be time consuming.
> THanks.
>
> "Jeff A. Stucker" wrote:
>> To be more specfiic, launch this programmatically:
>> http://RSSRV/ReportServer?/Path/Report&rs:Format=NULL
>> --
>> Cheers,
>> '(' Jeff A. Stucker
>> \
>> Business Intelligence
>> www.criadvantage.com
>> ---
>> "Jeff A. Stucker" <jeff@.mobilize.net> wrote in message
>> news:enleZEI3EHA.1524@.TK2MSFTNGP09.phx.gbl...
>> > Probably not aspx programming. The report filter is just part of the
>> > report design; you can filter groups (check the properties of your
>> > List,
>> > Table, Matrix, etc.). To kick off the null report, you may be able to
>> > simply launch a URL using the Windows scheduler. I think the syntax is
>> > this:
>> >
>> > &rs:Format=NULL
>> >
>> > --
>> > Cheers,
>> >
>> > '(' Jeff A. Stucker
>> > \
>> >
>> > Business Intelligence
>> > www.criadvantage.com
>> > ---
>> > "John_g" <Johng@.discussions.microsoft.com> wrote in message
>> > news:FD00DCEA-369A-4B2B-844E-C8BB1C3D509A@.microsoft.com...
>> >> Thanks. That sounds like a little more than I currently know how to
>> >> do.
>> >>
>> >> You are basically saying that I can query the data with a larger date
>> >> range,
>> >> or none at all, cache it, then when the user queries the information,
>> >> they
>> >> are querying the info from the cache?
>> >>
>> >> If so, it this easy, or am I going to have to do some aspx
>> >> programming?
>> >>
>> >> Thanks for your reply.
>> >>
>> >>
>> >> "Jeff A. Stucker" wrote:
>> >>
>> >> You could implement this with a report filter. You filter the data
>> >> after
>> >> it's returned, not in the query. If you want to have a cache
>> >> preloaded,
>> >> you
>> >> can set up a process to call the report with a NULL rendering
>> >> extension.
>> >> That will execute the query without returning the results.
>> >>
>> >> --
>> >> '(' Jeff A. Stucker
>> >> \
>> >>
>> >> Business Intelligence
>> >> www.criadvantage.com
>> >> ---
>> >> "John_g" <John_g@.discussions.microsoft.com> wrote in message
>> >> news:71D9E727-49EA-447B-A55B-21A26592EA2F@.microsoft.com...
>> >> >I figured it out and thought I would put it here for someone else to
>> >> >learn
>> >> > from.
>> >> >
>> >> > A cache is only created when the report is first run, then it can
>> >> > expire
>> >> > when you want it to. A snapshot runs on your schedule, and stays
>> >> > in
>> >> > history.
>> >> >
>> >> > I would like a mix, the ability to load the report the first time
>> >> > from
>> >> > the
>> >> > snapshot, but give the users the ability to run it again with date
>> >> > ranges.
>> >> >
>> >> > Oh well :)
>> >> >
>> >> > "John_g" wrote:
>> >> >
>> >> >> I am new to reporting services and have created a few reports. I
>> >> >> have
>> >> >> noticed that I can cache or create a snapshot of the reports.
>> >> >>
>> >> >> I currently use a Crystal Command line tool to run crystal reports
>> >> >> around
>> >> >> 4:00 am, and I would like to do the same for some of my reports.
>> >> >>
>> >> >> What is the difference and which peroforms faster?
>> >> >>
>> >> >> Thanks for your help.
>> >> >>
>> >>
>> >>
>> >>
>> >
>> >
>>|||Thanks for your patience. I think I am finally catching on.
I can run the report and leave my variables off of the query, just get the
whole mess. Then I can use parameters and they can be entered by the user at
run-time.
So the cache will simply sit on the server, and when the user hits the query
and passes parms, the report will be run off of the cache, and not directly
hit the SQL Server.
Am I close?
"Jeff A. Stucker" wrote:
> It runs for whatever the default dates are specified.
> --
> Cheers,
> '(' Jeff A. Stucker
> \
> Business Intelligence
> www.criadvantage.com
> ---
> "John_g" <Johng@.discussions.microsoft.com> wrote in message
> news:26547C04-1BEA-4438-A37D-FFFE6886979C@.microsoft.com...
> > When I run this, what data ranges does it use? My variables are StartDate
> > and EndDate.
> >
> > I am just wandering since I am not giving it dates, does it run for all
> > dates?
> > That could be time consuming.
> >
> > THanks.
> >
> >
> > "Jeff A. Stucker" wrote:
> >
> >> To be more specfiic, launch this programmatically:
> >>
> >> http://RSSRV/ReportServer?/Path/Report&rs:Format=NULL
> >>
> >> --
> >> Cheers,
> >>
> >> '(' Jeff A. Stucker
> >> \
> >>
> >> Business Intelligence
> >> www.criadvantage.com
> >> ---
> >> "Jeff A. Stucker" <jeff@.mobilize.net> wrote in message
> >> news:enleZEI3EHA.1524@.TK2MSFTNGP09.phx.gbl...
> >> > Probably not aspx programming. The report filter is just part of the
> >> > report design; you can filter groups (check the properties of your
> >> > List,
> >> > Table, Matrix, etc.). To kick off the null report, you may be able to
> >> > simply launch a URL using the Windows scheduler. I think the syntax is
> >> > this:
> >> >
> >> > &rs:Format=NULL
> >> >
> >> > --
> >> > Cheers,
> >> >
> >> > '(' Jeff A. Stucker
> >> > \
> >> >
> >> > Business Intelligence
> >> > www.criadvantage.com
> >> > ---
> >> > "John_g" <Johng@.discussions.microsoft.com> wrote in message
> >> > news:FD00DCEA-369A-4B2B-844E-C8BB1C3D509A@.microsoft.com...
> >> >> Thanks. That sounds like a little more than I currently know how to
> >> >> do.
> >> >>
> >> >> You are basically saying that I can query the data with a larger date
> >> >> range,
> >> >> or none at all, cache it, then when the user queries the information,
> >> >> they
> >> >> are querying the info from the cache?
> >> >>
> >> >> If so, it this easy, or am I going to have to do some aspx
> >> >> programming?
> >> >>
> >> >> Thanks for your reply.
> >> >>
> >> >>
> >> >> "Jeff A. Stucker" wrote:
> >> >>
> >> >> You could implement this with a report filter. You filter the data
> >> >> after
> >> >> it's returned, not in the query. If you want to have a cache
> >> >> preloaded,
> >> >> you
> >> >> can set up a process to call the report with a NULL rendering
> >> >> extension.
> >> >> That will execute the query without returning the results.
> >> >>
> >> >> --
> >> >> '(' Jeff A. Stucker
> >> >> \
> >> >>
> >> >> Business Intelligence
> >> >> www.criadvantage.com
> >> >> ---
> >> >> "John_g" <John_g@.discussions.microsoft.com> wrote in message
> >> >> news:71D9E727-49EA-447B-A55B-21A26592EA2F@.microsoft.com...
> >> >> >I figured it out and thought I would put it here for someone else to
> >> >> >learn
> >> >> > from.
> >> >> >
> >> >> > A cache is only created when the report is first run, then it can
> >> >> > expire
> >> >> > when you want it to. A snapshot runs on your schedule, and stays
> >> >> > in
> >> >> > history.
> >> >> >
> >> >> > I would like a mix, the ability to load the report the first time
> >> >> > from
> >> >> > the
> >> >> > snapshot, but give the users the ability to run it again with date
> >> >> > ranges.
> >> >> >
> >> >> > Oh well :)
> >> >> >
> >> >> > "John_g" wrote:
> >> >> >
> >> >> >> I am new to reporting services and have created a few reports. I
> >> >> >> have
> >> >> >> noticed that I can cache or create a snapshot of the reports.
> >> >> >>
> >> >> >> I currently use a Crystal Command line tool to run crystal reports
> >> >> >> around
> >> >> >> 4:00 am, and I would like to do the same for some of my reports.
> >> >> >>
> >> >> >> What is the difference and which peroforms faster?
> >> >> >>
> >> >> >> Thanks for your help.
> >> >> >>
> >> >>
> >> >>
> >> >>
> >> >
> >> >
> >>
> >>
> >>
>
>|||That's it! Perfect summary.
Since you're running off the cache, and filtering that, then at rendering
time it puts more load on the reporting engine, but zero load on the
back-end database. And that's perfectly fine if the back-end query was
where the thing was bogging down to begin with. So your cache will have a
superset of all the various data your end-users will see on the same report.
--
Cheers,
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"John_g" <Johng@.discussions.microsoft.com> wrote in message
news:0E1D42DA-702A-41F9-8277-0F5C42B26A5F@.microsoft.com...
> Thanks for your patience. I think I am finally catching on.
> I can run the report and leave my variables off of the query, just get the
> whole mess. Then I can use parameters and they can be entered by the user
> at
> run-time.
> So the cache will simply sit on the server, and when the user hits the
> query
> and passes parms, the report will be run off of the cache, and not
> directly
> hit the SQL Server.
> Am I close?
> "Jeff A. Stucker" wrote:
>> It runs for whatever the default dates are specified.
>> --
>> Cheers,
>> '(' Jeff A. Stucker
>> \
>> Business Intelligence
>> www.criadvantage.com
>> ---
>> "John_g" <Johng@.discussions.microsoft.com> wrote in message
>> news:26547C04-1BEA-4438-A37D-FFFE6886979C@.microsoft.com...
>> > When I run this, what data ranges does it use? My variables are
>> > StartDate
>> > and EndDate.
>> >
>> > I am just wandering since I am not giving it dates, does it run for all
>> > dates?
>> > That could be time consuming.
>> >
>> > THanks.
>> >
>> >
>> > "Jeff A. Stucker" wrote:
>> >
>> >> To be more specfiic, launch this programmatically:
>> >>
>> >> http://RSSRV/ReportServer?/Path/Report&rs:Format=NULL
>> >>
>> >> --
>> >> Cheers,
>> >>
>> >> '(' Jeff A. Stucker
>> >> \
>> >>
>> >> Business Intelligence
>> >> www.criadvantage.com
>> >> ---
>> >> "Jeff A. Stucker" <jeff@.mobilize.net> wrote in message
>> >> news:enleZEI3EHA.1524@.TK2MSFTNGP09.phx.gbl...
>> >> > Probably not aspx programming. The report filter is just part of
>> >> > the
>> >> > report design; you can filter groups (check the properties of your
>> >> > List,
>> >> > Table, Matrix, etc.). To kick off the null report, you may be able
>> >> > to
>> >> > simply launch a URL using the Windows scheduler. I think the syntax
>> >> > is
>> >> > this:
>> >> >
>> >> > &rs:Format=NULL
>> >> >
>> >> > --
>> >> > Cheers,
>> >> >
>> >> > '(' Jeff A. Stucker
>> >> > \
>> >> >
>> >> > Business Intelligence
>> >> > www.criadvantage.com
>> >> > ---
>> >> > "John_g" <Johng@.discussions.microsoft.com> wrote in message
>> >> > news:FD00DCEA-369A-4B2B-844E-C8BB1C3D509A@.microsoft.com...
>> >> >> Thanks. That sounds like a little more than I currently know how
>> >> >> to
>> >> >> do.
>> >> >>
>> >> >> You are basically saying that I can query the data with a larger
>> >> >> date
>> >> >> range,
>> >> >> or none at all, cache it, then when the user queries the
>> >> >> information,
>> >> >> they
>> >> >> are querying the info from the cache?
>> >> >>
>> >> >> If so, it this easy, or am I going to have to do some aspx
>> >> >> programming?
>> >> >>
>> >> >> Thanks for your reply.
>> >> >>
>> >> >>
>> >> >> "Jeff A. Stucker" wrote:
>> >> >>
>> >> >> You could implement this with a report filter. You filter the
>> >> >> data
>> >> >> after
>> >> >> it's returned, not in the query. If you want to have a cache
>> >> >> preloaded,
>> >> >> you
>> >> >> can set up a process to call the report with a NULL rendering
>> >> >> extension.
>> >> >> That will execute the query without returning the results.
>> >> >>
>> >> >> --
>> >> >> '(' Jeff A. Stucker
>> >> >> \
>> >> >>
>> >> >> Business Intelligence
>> >> >> www.criadvantage.com
>> >> >> ---
>> >> >> "John_g" <John_g@.discussions.microsoft.com> wrote in message
>> >> >> news:71D9E727-49EA-447B-A55B-21A26592EA2F@.microsoft.com...
>> >> >> >I figured it out and thought I would put it here for someone else
>> >> >> >to
>> >> >> >learn
>> >> >> > from.
>> >> >> >
>> >> >> > A cache is only created when the report is first run, then it
>> >> >> > can
>> >> >> > expire
>> >> >> > when you want it to. A snapshot runs on your schedule, and
>> >> >> > stays
>> >> >> > in
>> >> >> > history.
>> >> >> >
>> >> >> > I would like a mix, the ability to load the report the first
>> >> >> > time
>> >> >> > from
>> >> >> > the
>> >> >> > snapshot, but give the users the ability to run it again with
>> >> >> > date
>> >> >> > ranges.
>> >> >> >
>> >> >> > Oh well :)
>> >> >> >
>> >> >> > "John_g" wrote:
>> >> >> >
>> >> >> >> I am new to reporting services and have created a few reports.
>> >> >> >> I
>> >> >> >> have
>> >> >> >> noticed that I can cache or create a snapshot of the reports.
>> >> >> >>
>> >> >> >> I currently use a Crystal Command line tool to run crystal
>> >> >> >> reports
>> >> >> >> around
>> >> >> >> 4:00 am, and I would like to do the same for some of my
>> >> >> >> reports.
>> >> >> >>
>> >> >> >> What is the difference and which peroforms faster?
>> >> >> >>
>> >> >> >> Thanks for your help.
>> >> >> >>
>> >> >>
>> >> >>
>> >> >>
>> >> >
>> >> >
>> >>
>> >>
>> >>
>>|||Thanks Jeff! I truly appreciate your help.
One last question... I did as you said, but I cannot get my date to
filter. The date field is datetime, but when I try '10/1/2004' or without
the quotes, or even add time, it says that the data is not comparable.
Also, I have been having a hard time scheduling the null report, but I have
not given up yet.
Thanks again
"Jeff A. Stucker" wrote:
> That's it! Perfect summary.
> Since you're running off the cache, and filtering that, then at rendering
> time it puts more load on the reporting engine, but zero load on the
> back-end database. And that's perfectly fine if the back-end query was
> where the thing was bogging down to begin with. So your cache will have a
> superset of all the various data your end-users will see on the same report.
> --
> Cheers,
> '(' Jeff A. Stucker
> \
> Business Intelligence
> www.criadvantage.com
> ---
> "John_g" <Johng@.discussions.microsoft.com> wrote in message
> news:0E1D42DA-702A-41F9-8277-0F5C42B26A5F@.microsoft.com...
> > Thanks for your patience. I think I am finally catching on.
> >
> > I can run the report and leave my variables off of the query, just get the
> > whole mess. Then I can use parameters and they can be entered by the user
> > at
> > run-time.
> >
> > So the cache will simply sit on the server, and when the user hits the
> > query
> > and passes parms, the report will be run off of the cache, and not
> > directly
> > hit the SQL Server.
> >
> > Am I close?
> >
> > "Jeff A. Stucker" wrote:
> >
> >> It runs for whatever the default dates are specified.
> >>
> >> --
> >> Cheers,
> >>
> >> '(' Jeff A. Stucker
> >> \
> >>
> >> Business Intelligence
> >> www.criadvantage.com
> >> ---
> >> "John_g" <Johng@.discussions.microsoft.com> wrote in message
> >> news:26547C04-1BEA-4438-A37D-FFFE6886979C@.microsoft.com...
> >> > When I run this, what data ranges does it use? My variables are
> >> > StartDate
> >> > and EndDate.
> >> >
> >> > I am just wandering since I am not giving it dates, does it run for all
> >> > dates?
> >> > That could be time consuming.
> >> >
> >> > THanks.
> >> >
> >> >
> >> > "Jeff A. Stucker" wrote:
> >> >
> >> >> To be more specfiic, launch this programmatically:
> >> >>
> >> >> http://RSSRV/ReportServer?/Path/Report&rs:Format=NULL
> >> >>
> >> >> --
> >> >> Cheers,
> >> >>
> >> >> '(' Jeff A. Stucker
> >> >> \
> >> >>
> >> >> Business Intelligence
> >> >> www.criadvantage.com
> >> >> ---
> >> >> "Jeff A. Stucker" <jeff@.mobilize.net> wrote in message
> >> >> news:enleZEI3EHA.1524@.TK2MSFTNGP09.phx.gbl...
> >> >> > Probably not aspx programming. The report filter is just part of
> >> >> > the
> >> >> > report design; you can filter groups (check the properties of your
> >> >> > List,
> >> >> > Table, Matrix, etc.). To kick off the null report, you may be able
> >> >> > to
> >> >> > simply launch a URL using the Windows scheduler. I think the syntax
> >> >> > is
> >> >> > this:
> >> >> >
> >> >> > &rs:Format=NULL
> >> >> >
> >> >> > --
> >> >> > Cheers,
> >> >> >
> >> >> > '(' Jeff A. Stucker
> >> >> > \
> >> >> >
> >> >> > Business Intelligence
> >> >> > www.criadvantage.com
> >> >> > ---
> >> >> > "John_g" <Johng@.discussions.microsoft.com> wrote in message
> >> >> > news:FD00DCEA-369A-4B2B-844E-C8BB1C3D509A@.microsoft.com...
> >> >> >> Thanks. That sounds like a little more than I currently know how
> >> >> >> to
> >> >> >> do.
> >> >> >>
> >> >> >> You are basically saying that I can query the data with a larger
> >> >> >> date
> >> >> >> range,
> >> >> >> or none at all, cache it, then when the user queries the
> >> >> >> information,
> >> >> >> they
> >> >> >> are querying the info from the cache?
> >> >> >>
> >> >> >> If so, it this easy, or am I going to have to do some aspx
> >> >> >> programming?
> >> >> >>
> >> >> >> Thanks for your reply.
> >> >> >>
> >> >> >>
> >> >> >> "Jeff A. Stucker" wrote:
> >> >> >>
> >> >> >> You could implement this with a report filter. You filter the
> >> >> >> data
> >> >> >> after
> >> >> >> it's returned, not in the query. If you want to have a cache
> >> >> >> preloaded,
> >> >> >> you
> >> >> >> can set up a process to call the report with a NULL rendering
> >> >> >> extension.
> >> >> >> That will execute the query without returning the results.
> >> >> >>
> >> >> >> --
> >> >> >> '(' Jeff A. Stucker
> >> >> >> \
> >> >> >>
> >> >> >> Business Intelligence
> >> >> >> www.criadvantage.com
> >> >> >> ---
> >> >> >> "John_g" <John_g@.discussions.microsoft.com> wrote in message
> >> >> >> news:71D9E727-49EA-447B-A55B-21A26592EA2F@.microsoft.com...
> >> >> >> >I figured it out and thought I would put it here for someone else
> >> >> >> >to
> >> >> >> >learn
> >> >> >> > from.
> >> >> >> >
> >> >> >> > A cache is only created when the report is first run, then it
> >> >> >> > can
> >> >> >> > expire
> >> >> >> > when you want it to. A snapshot runs on your schedule, and
> >> >> >> > stays
> >> >> >> > in
> >> >> >> > history.
> >> >> >> >
> >> >> >> > I would like a mix, the ability to load the report the first
> >> >> >> > time
> >> >> >> > from
> >> >> >> > the
> >> >> >> > snapshot, but give the users the ability to run it again with
> >> >> >> > date
> >> >> >> > ranges.
> >> >> >> >
> >> >> >> > Oh well :)
> >> >> >> >
> >> >> >> > "John_g" wrote:
> >> >> >> >
> >> >> >> >> I am new to reporting services and have created a few reports.
> >> >> >> >> I
> >> >> >> >> have
> >> >> >> >> noticed that I can cache or create a snapshot of the reports.
> >> >> >> >>
> >> >> >> >> I currently use a Crystal Command line tool to run crystal
> >> >> >> >> reports
> >> >> >> >> around
> >> >> >> >> 4:00 am, and I would like to do the same for some of my
> >> >> >> >> reports.
> >> >> >> >>
> >> >> >> >> What is the difference and which peroforms faster?
> >> >> >> >>
> >> >> >> >> Thanks for your help.
> >> >> >> >>
> >> >> >>
> >> >> >>
> >> >> >>
> >> >> >
> >> >> >
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>|||Where is your filter set up and what's it look like where the date is
bombing?
Have you verified that the null report works by calling the URL directly
from a browser? I'm not an expert on scheduling, so perhaps someone else
can help you with that.
--
Cheers,
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"John_g" <Johng@.discussions.microsoft.com> wrote in message
news:CA494272-DC24-49CD-9FB7-70852295EDD8@.microsoft.com...
> Thanks Jeff! I truly appreciate your help.
> One last question... I did as you said, but I cannot get my date to
> filter. The date field is datetime, but when I try '10/1/2004' or without
> the quotes, or even add time, it says that the data is not comparable.
> Also, I have been having a hard time scheduling the null report, but I
> have
> not given up yet.
> Thanks again
> "Jeff A. Stucker" wrote:
>> That's it! Perfect summary.
>> Since you're running off the cache, and filtering that, then at rendering
>> time it puts more load on the reporting engine, but zero load on the
>> back-end database. And that's perfectly fine if the back-end query was
>> where the thing was bogging down to begin with. So your cache will have
>> a
>> superset of all the various data your end-users will see on the same
>> report.
>> --
>> Cheers,
>> '(' Jeff A. Stucker
>> \
>> Business Intelligence
>> www.criadvantage.com
>> ---
>> "John_g" <Johng@.discussions.microsoft.com> wrote in message
>> news:0E1D42DA-702A-41F9-8277-0F5C42B26A5F@.microsoft.com...
>> > Thanks for your patience. I think I am finally catching on.
>> >
>> > I can run the report and leave my variables off of the query, just get
>> > the
>> > whole mess. Then I can use parameters and they can be entered by the
>> > user
>> > at
>> > run-time.
>> >
>> > So the cache will simply sit on the server, and when the user hits the
>> > query
>> > and passes parms, the report will be run off of the cache, and not
>> > directly
>> > hit the SQL Server.
>> >
>> > Am I close?
>> >
>> > "Jeff A. Stucker" wrote:
>> >
>> >> It runs for whatever the default dates are specified.
>> >>
>> >> --
>> >> Cheers,
>> >>
>> >> '(' Jeff A. Stucker
>> >> \
>> >>
>> >> Business Intelligence
>> >> www.criadvantage.com
>> >> ---
>> >> "John_g" <Johng@.discussions.microsoft.com> wrote in message
>> >> news:26547C04-1BEA-4438-A37D-FFFE6886979C@.microsoft.com...
>> >> > When I run this, what data ranges does it use? My variables are
>> >> > StartDate
>> >> > and EndDate.
>> >> >
>> >> > I am just wandering since I am not giving it dates, does it run for
>> >> > all
>> >> > dates?
>> >> > That could be time consuming.
>> >> >
>> >> > THanks.
>> >> >
>> >> >
>> >> > "Jeff A. Stucker" wrote:
>> >> >
>> >> >> To be more specfiic, launch this programmatically:
>> >> >>
>> >> >> http://RSSRV/ReportServer?/Path/Report&rs:Format=NULL
>> >> >>
>> >> >> --
>> >> >> Cheers,
>> >> >>
>> >> >> '(' Jeff A. Stucker
>> >> >> \
>> >> >>
>> >> >> Business Intelligence
>> >> >> www.criadvantage.com
>> >> >> ---
>> >> >> "Jeff A. Stucker" <jeff@.mobilize.net> wrote in message
>> >> >> news:enleZEI3EHA.1524@.TK2MSFTNGP09.phx.gbl...
>> >> >> > Probably not aspx programming. The report filter is just part of
>> >> >> > the
>> >> >> > report design; you can filter groups (check the properties of
>> >> >> > your
>> >> >> > List,
>> >> >> > Table, Matrix, etc.). To kick off the null report, you may be
>> >> >> > able
>> >> >> > to
>> >> >> > simply launch a URL using the Windows scheduler. I think the
>> >> >> > syntax
>> >> >> > is
>> >> >> > this:
>> >> >> >
>> >> >> > &rs:Format=NULL
>> >> >> >
>> >> >> > --
>> >> >> > Cheers,
>> >> >> >
>> >> >> > '(' Jeff A. Stucker
>> >> >> > \
>> >> >> >
>> >> >> > Business Intelligence
>> >> >> > www.criadvantage.com
>> >> >> > ---
>> >> >> > "John_g" <Johng@.discussions.microsoft.com> wrote in message
>> >> >> > news:FD00DCEA-369A-4B2B-844E-C8BB1C3D509A@.microsoft.com...
>> >> >> >> Thanks. That sounds like a little more than I currently know
>> >> >> >> how
>> >> >> >> to
>> >> >> >> do.
>> >> >> >>
>> >> >> >> You are basically saying that I can query the data with a larger
>> >> >> >> date
>> >> >> >> range,
>> >> >> >> or none at all, cache it, then when the user queries the
>> >> >> >> information,
>> >> >> >> they
>> >> >> >> are querying the info from the cache?
>> >> >> >>
>> >> >> >> If so, it this easy, or am I going to have to do some aspx
>> >> >> >> programming?
>> >> >> >>
>> >> >> >> Thanks for your reply.
>> >> >> >>
>> >> >> >>
>> >> >> >> "Jeff A. Stucker" wrote:
>> >> >> >>
>> >> >> >> You could implement this with a report filter. You filter the
>> >> >> >> data
>> >> >> >> after
>> >> >> >> it's returned, not in the query. If you want to have a cache
>> >> >> >> preloaded,
>> >> >> >> you
>> >> >> >> can set up a process to call the report with a NULL rendering
>> >> >> >> extension.
>> >> >> >> That will execute the query without returning the results.
>> >> >> >>
>> >> >> >> --
>> >> >> >> '(' Jeff A. Stucker
>> >> >> >> \
>> >> >> >>
>> >> >> >> Business Intelligence
>> >> >> >> www.criadvantage.com
>> >> >> >> ---
>> >> >> >> "John_g" <John_g@.discussions.microsoft.com> wrote in message
>> >> >> >> news:71D9E727-49EA-447B-A55B-21A26592EA2F@.microsoft.com...
>> >> >> >> >I figured it out and thought I would put it here for someone
>> >> >> >> >else
>> >> >> >> >to
>> >> >> >> >learn
>> >> >> >> > from.
>> >> >> >> >
>> >> >> >> > A cache is only created when the report is first run, then it
>> >> >> >> > can
>> >> >> >> > expire
>> >> >> >> > when you want it to. A snapshot runs on your schedule, and
>> >> >> >> > stays
>> >> >> >> > in
>> >> >> >> > history.
>> >> >> >> >
>> >> >> >> > I would like a mix, the ability to load the report the first
>> >> >> >> > time
>> >> >> >> > from
>> >> >> >> > the
>> >> >> >> > snapshot, but give the users the ability to run it again with
>> >> >> >> > date
>> >> >> >> > ranges.
>> >> >> >> >
>> >> >> >> > Oh well :)
>> >> >> >> >
>> >> >> >> > "John_g" wrote:
>> >> >> >> >
>> >> >> >> >> I am new to reporting services and have created a few
>> >> >> >> >> reports.
>> >> >> >> >> I
>> >> >> >> >> have
>> >> >> >> >> noticed that I can cache or create a snapshot of the
>> >> >> >> >> reports.
>> >> >> >> >>
>> >> >> >> >> I currently use a Crystal Command line tool to run crystal
>> >> >> >> >> reports
>> >> >> >> >> around
>> >> >> >> >> 4:00 am, and I would like to do the same for some of my
>> >> >> >> >> reports.
>> >> >> >> >>
>> >> >> >> >> What is the difference and which peroforms faster?
>> >> >> >> >>
>> >> >> >> >> Thanks for your help.
>> >> >> >> >>
>> >> >> >>
>> >> >> >>
>> >> >> >>
>> >> >> >
>> >> >> >
>> >> >>
>> >> >>
>> >> >>
>> >>
>> >>
>> >>
>>

Cache problem in Report Services

Hi All,
We developed 40 reports using Reporting services, all reports having
parameters, data source for all reports are views. I am facing problem
of may be cache. I am using url method to show reports, i wrote a ASP
page on link of report i am using report server url to show like -
http://servername/reportserver/fold...arOrderNo=2067,
but when i click on the link, it shows old data in report and when i
press refresh button given with RS it will refresh and shows the
updated data, every time to get updated data i have to press refresh
button. Can anyone suggest me how to show updated data on report
whenever it gets fired? Please my project submission data is too short,
suggest me asap
Thank You
Regards
Rajeshhi Raj,
I had the same problem (I'm not a developer) but managed to find the cause
and got my developer to fix it.
I don't have the solution to hand now, but if you don't get the solution by
the time I get to work and find the solution, I'll post it by tomorrow
morning.
Immy
p.s. You should try to post this message in the reporting services
newsgroup.
"Raj" <kapse_rajesh@.rediffmail.com> wrote in message
news:1141463255.332391.287870@.z34g2000cwc.googlegroups.com...
> Hi All,
> We developed 40 reports using Reporting services, all reports having
> parameters, data source for all reports are views. I am facing problem
> of may be cache. I am using url method to show reports, i wrote a ASP
> page on link of report i am using report server url to show like -
> http://servername/reportserver/fold...arOrderNo=2067,
> but when i click on the link, it shows old data in report and when i
> press refresh button given with RS it will refresh and shows the
> updated data, every time to get updated data i have to press refresh
> button. Can anyone suggest me how to show updated data on report
> whenever it gets fired? Please my project submission data is too short,
> suggest me asap
>
> Thank You
> Regards
> Rajesh
>|||sorry, forgot to say... it has something to do with the fact that you need
to append the command 'ClearSession=true' to the url at some point.
this should get you on your way if it can't wait until later.
"Immy" <therealasianbabe@.hotmail.com> wrote in message
news:OAF6lY4PGHA.5856@.TK2MSFTNGP10.phx.gbl...
> hi Raj,
> I had the same problem (I'm not a developer) but managed to find the cause
> and got my developer to fix it.
> I don't have the solution to hand now, but if you don't get the solution
> by the time I get to work and find the solution, I'll post it by tomorrow
> morning.
> Immy
> p.s. You should try to post this message in the reporting services
> newsgroup.
> "Raj" <kapse_rajesh@.rediffmail.com> wrote in message
> news:1141463255.332391.287870@.z34g2000cwc.googlegroups.com...
>

Cache Problem

I am using Reporting Services with SQL Server 2000. The report is called in IE and rendered in PDF and displays correctly. However, the problem I have occurs when, during the same IE session, first I call the report, then change the values in the database, and call the report again with the same parameters. The second time, the values in the report remain the same as the first one.

Somehow, the second time I call the report, it is not generating a new one but using the one in Cache. If I close IE, reopen it and call the report then the new values are displayed.

In the Report Manager Properties/Execution, I chose to execute the report with the most recent data and not to put a temporary copy in chache.

Is there a way to force the report to ALWAYS get fresh copies?

Thanks a lot

Bob

There are two levels of caching. One is "execution caching" and you can configure this via the execution options tab in Report Manager. The other is "output caching." Output caching is when we cache the actual rendered output. You are hitting the "output cache" in this case, which is not configurable. Try hitting using "ctrl-f5" to force a refresh. It should cause us to clear your current session and you should see the new data the next time you request the report.

|||

Thanks for the info. It is true, as u wrote, that I am hitting the "output Cache"... But the problem is that the report will not be generated by me, but by the users of the Intranet I am developping.

Is there a way I can simulate the "ctrl-F5", or force a refresh, in the program?

|||

Have you tried passing in the name/value pair

rs:ClearSession=true

with your report URL? That should force a new query every time the report is viewed.

|||CTRL-F5 (through IE) and rs:ClearSession=true should give you identical results.|||

Also, the different users will get the data as it existed when they first rendered the report. The way to think about it is that when their session is created and the report is rendered, the data that generated that report is bound to their session. Subsequent "exports" of the different formats should contain the same data since it is for the same session. We will not reuse the output cache across sessions since they will each have their own snapshot (because you have the report set to execute live).

|||

John Gallardo - MSFT wrote:

CTRL-F5 (through IE) and rs:ClearSession=true should give you identical results.

Well, the OP wondered if he could "force" a refresh. rs:ClearSession will work here; although they are identical CTRL-F5 does not seem like a valid solution.

Side note - I've seen some MVP postings in Google Group results that contradict IE's refresh will work for this. I had a similar problem I had to search for help on. Example:

"Either close IE and reopen it or try clicking on the Report Manager refresh
button (IE refresh does not work). Rolling out a new version of a report is
a special case.

Closing IE and reopening it always works.

--
Bruce Loehle-Conger
MVP SQL Server Reporting Services "

Original thread:
http://groups.google.com/group/microsoft.public.sqlserver.reportingsvcs/browse_thread/thread/acd8e041dd06dd86/8b17af12fee5b901?lnk=st&q=sql+reporting+services+session+caching&rnum=1&hl=en#8b17af12fee5b901

Just FYI. What, for my edification, is the difference in the thread's scenario and the OP?

|||Thx a lot, this was the solution!|||

Hello guys,

I have a problem with cashe.

When I get rport at the first time, it's OK

At the second - I get report without right part of header (it's truncated.)

Part of footer located at the same X position of footer isn't truncated.

All solutions mentioned above isn't useful.

What might it be?

Thanks