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
>

calculate weekendings for a date range

All,
I have stored procedure that accepts 2 parameters (StartDate and EndDate). I need a way to find out which weekending dates fall between the two parameters .

For example :

StartDate: 02-jan-2006
EndDate: 19-jan-2006

Therefore the weekending dates would be 8-Jan 2006 and 15-jan-2006

Can anyone help

Cheers

Hi,

look here:

http://www.aspfaq.com/show.asp?id=2519

HTH, Jens Suessmeyer.

|||

You can compute the last "end of week" based on the day-of-week DatePart() value. From this, you can also compute the next "end of week". This will all be consistent with the @.@.DateFirst parameter setting the first day of the week -- hence moving the actual day associated with the "end of week".

To find the last "end of week", subtract days equal to 1 less than the DatePart() value. To find the next "end of week", do as above, but add 7 days modulo 7 -- which won't change the date if the end of week is originally supplied.

Declare @.date1 datetime,
@.date2 datetime

Set @.date1 = '1/2/2006'
Set @.date2 = '1/19/2006'

Select next_end = DateAdd( dd, ( -1 * ( DatePart( dw, @.date1 ) - 1 ) + 7 ) % 7, @.date1 ),
last_end = DateAdd( dd, ( -1 * ( DatePart( dw, @.date2 ) - 1 ) ) , @.date2 )

The above will return the results you are expecting. You could place these into functions for better readability.

|||

Relying on the setting of @.@.DATEFIRST can be a problem, the best thing would be that @.@.DATEFIRST could be set to any day, bit still get the same result...

It's practical with a day-table containing each date, much like a numbers-table.

create table dbo.days (d datetime not null)
go
set nocount on
declare @.d datetime
set @.d = '20060101'
while @.d < '20070101'
begin
insert dbo.days select @.d
set @.d = dateadd(day, 1, @.d)
end
set nocount off
go

To get any given weekday from a date, regardless of @.@.DATEFIRST setting, we can use the following formula:
(( @.@.datefirst + datepart(weekday, @.mydate) -2 ) %7 + 1 )

Monday is 1, Tuesday 2 etc until Sunday that is 7.

So, if we want to know which dates that are Sundays between January 2nd 2006 and January 19th 2006, we can ask our date-table. (and not bother with what @.@.DATEFIRST may be set to)

declare @.startDate datetime, @.endDate datetime
select @.startDate = '20060102', @.endDate = '20060119'

select d from days
where d between @.startDate and @.endDate
and (( @.@.datefirst + datepart(weekday, d) -2 ) %7 + 1) = 7

The last '7' is the weekday (Sunday) we're looking for.

You can play with this example and see that nothing happens when @.@.datefirst changes.

set datefirst 3
select @.@.datefirst as 'datefirstSetTo'

declare @.startDate datetime, @.endDate datetime
select @.startDate = '20060102', @.endDate = '20060119'

select d as 'mySundays'
from days
where d between @.startDate and @.endDate
and (( @.@.datefirst + datepart(weekday, d) -2 ) %7 + 1) = 7

datefirstSetTo
--
3

(1 row(s) affected)

mySundays
2006-01-08 00:00:00.000
2006-01-15 00:00:00.000

(2 row(s) affected)

..hope it's of some help.

=;o)
/Kenneth

|||

Best is to use a calendar table that contains all the dates and query it to get the details. This approach gives maximum flexibility, maintainability and performance in lot of cases also. It is quite common to do this in data warehouses to simplify these type of queries. For example, you can build a Calendar table like:

create table Calendar (

dayid int not null primary key,

dt smalldatetime not null,

....

IsSaturday bit not null,

IsSunday bit not null

... other columns that can contain holidays or special days etc

)

Now your query becomes:

select count(*)

from Calendar

where dt between '20060102' and '20060119'

and 1 in ( IsSaturday, IsSunday )

You can answer more complex questions also using the calendar table which will be harder to do by writing UDFs or special procedural logic.

Caching reports

I have many reports that run off the same query statement. The Statement
uses parameters that are supplied when the report is executed. There are
allso additional parameters that are used in filters and displayed on the
report (such as title information). I have set up the reports to be cached
and modified the parameters in the report that are not used in the Query with
<UsedInQuery>False. If I run the report with all the same parameters the
Cache seems to work well. If I change one of the parameters used as a filter
the cache is not used. Is there any way around this?
What would be even better is if I could set up cacheing on the shared data
source instead of the report and cache the data for all reports that uses the
same query.
ThanksIt sounds like the report cache includes filters in its caching of data.
It's important to remember that the cache is not just a cache of query data,
but a cache of data the way it will be used in the report, ready for
rendering to any of several different formats.
Now, you could work on the SQL side of things, to see if you can streamline
your datasource (the database itself) to work more effeciently with multiple
reports.
--
Cheers,
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"Ken McCullough" <Ken McCullough@.discussions.microsoft.com> wrote in message
news:44B0FAB8-5260-4F06-830E-47429006D5FB@.microsoft.com...
>I have many reports that run off the same query statement. The Statement
> uses parameters that are supplied when the report is executed. There are
> allso additional parameters that are used in filters and displayed on the
> report (such as title information). I have set up the reports to be
> cached
> and modified the parameters in the report that are not used in the Query
> with
> <UsedInQuery>False. If I run the report with all the same parameters the
> Cache seems to work well. If I change one of the parameters used as a
> filter
> the cache is not used. Is there any way around this?
> What would be even better is if I could set up cacheing on the shared data
> source instead of the report and cache the data for all reports that uses
> the
> same query.
> Thanks
>|||Ken,
Double check that the <UsedInQuery>False</UsedInQuery> that you added are
still in the RDL.
Though I have never determined the exact sequence to duplicate, I have had
times where I believe the Report Designer removed <UsedInQuery> settings and
I had to add them again.
We have done a fair amount of testing with
<UsedInQuery>False</UsedInQuery> and its cache effects, and at least for us
it is definately working as advertised.
Bob
"Ken McCullough" wrote:
> I have many reports that run off the same query statement. The Statement
> uses parameters that are supplied when the report is executed. There are
> allso additional parameters that are used in filters and displayed on the
> report (such as title information). I have set up the reports to be cached
> and modified the parameters in the report that are not used in the Query with
> <UsedInQuery>False. If I run the report with all the same parameters the
> Cache seems to work well. If I change one of the parameters used as a filter
> the cache is not used. Is there any way around this?
> What would be even better is if I could set up cacheing on the shared data
> source instead of the report and cache the data for all reports that uses the
> same query.
> Thanks
>|||I stand corrected then. The documentation indicates that UsedInQuery
affects report snapshots, which is similar to caching.
--
Cheers,
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"bobhug" <bobhug@.discussions.microsoft.com> wrote in message
news:289783D1-D1DC-4E63-B967-B49DC416FCC8@.microsoft.com...
> Ken,
> Double check that the <UsedInQuery>False</UsedInQuery> that you added are
> still in the RDL.
> Though I have never determined the exact sequence to duplicate, I have
> had
> times where I believe the Report Designer removed <UsedInQuery> settings
> and
> I had to add them again.
> We have done a fair amount of testing with
> <UsedInQuery>False</UsedInQuery> and its cache effects, and at least for
> us
> it is definately working as advertised.
> Bob
> "Ken McCullough" wrote:
>> I have many reports that run off the same query statement. The Statement
>> uses parameters that are supplied when the report is executed. There are
>> allso additional parameters that are used in filters and displayed on the
>> report (such as title information). I have set up the reports to be
>> cached
>> and modified the parameters in the report that are not used in the Query
>> with
>> <UsedInQuery>False. If I run the report with all the same parameters the
>> Cache seems to work well. If I change one of the parameters used as a
>> filter
>> the cache is not used. Is there any way around this?
>> What would be even better is if I could set up cacheing on the shared
>> data
>> source instead of the report and cache the data for all reports that uses
>> the
>> same query.
>> Thanks
>>

Caching or reusing parameters populated with SqlCommandBuilder.DeriveParameters

Hello,

I have a real heartache with runtime parameter interogation on my DB.
Sure I get the latest and greatest and sure I don't have to type in all those lovely parameter types..but...the hit I take on performance for making no less then 3 DB hits for each SqlAdapter is unreasonable!

So ...I like the idea of maybe calling it once for all my stored procs on application startup...and then maybe saving this in CacheObject.

My problem is that I can't see where you can even serialize a SqlParametersCollection or even for that matter assign it to a Command object. Can you cache a command object ?

LOL

I think I may just have to write some generic routine for creating and populating my command objects based on a key (type) and then use that to fetch my command.Update,
command.Insert and command.

I would like to use the new AsynchBlock to do the fetching of the stored proc parameters and then just pull them from the Cache object...put a file watch so that if the DB's change my params it re-pulls them again.

*nice*....

Then I get the best of both worlds...caching...and no parameter writing...

Ericsp_sproc_columns [[@.procedure_name =] 'name']
[,[@.procedure_owner =] 'owner']
[,[@.procedure_qualifier =] 'qualifier']
[,[@.column_name =] 'column_name']
[,[@.ODBCVer =] 'ODBCVer']

sp_stored_procedures [[@.sp_name =] 'name']
[,[@.sp_owner =] 'owner']
[,[@.sp_qualifier =] 'qualifier']

does that help?|||well yeah that's what I was going to do once I have the params in some cachable state...
I was just wondering if you could fetch the SqlParametersCollection from the Command object...alal IDbCommandParameters

and then cache those dudes...:)

I can manually interogate my hash for the parameters based on type key..."UpdSales", "InsSales","DelSales"...etc..|||those are the system procedures you need to get ALL the procedures from your database, then get their parameters.

Check out the details from books online.|||Duh...

I know that!

The SqlCommandBuilder.DerieveParameters(SqlCommand)

And those parameters have to stored somewhere in command object right?

Ahhh the Parameters which implments the IDbParameterCollection...

So can you fetch or store that Parameters collection...

That is what I"m asking...

I think I will just write the code "AsyncService" to fetch them at Application on start and then store them....I just don't like all those chatty calls ...that's all

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.

Caching a report with parameters

I have two reports one with no parameters and one with two parameters that
uses a data source with credentials stored.
When I try and setup caching for these reports I cannot for the one with
parameters. I get an error:"Credentials used to run this report are not
stored" even though they are.
Is this by design or do I need to do soimething else.
ThanksIt is by design. See my post
http://groups.google.com/group/microsoft.public.sqlserver.reportingsvcs/browse_frm/thread/1efc856d389b549b/80177e1ebec33cd0?lnk=st&q=%22+snapshot+question%2C+support+for+a+parameter...%22&rnum=1&hl=en#80177e1ebec33cd0
--
HTH,
---
Teo Lachev, MVP, MCSD, MCT
"Microsoft Reporting Services in Action"
"Applied Microsoft Analysis Services 2005"
Home page and blog: http://www.prologika.com/
---
"Niall" <Niall@.discussions.microsoft.com> wrote in message
news:3C8A43E6-5BC9-4380-A9DD-8F78C8F39DA8@.microsoft.com...
>I have two reports one with no parameters and one with two parameters that
> uses a data source with credentials stored.
> When I try and setup caching for these reports I cannot for the one with
> parameters. I get an error:"Credentials used to run this report are not
> stored" even though they are.
> Is this by design or do I need to do soimething else.
> Thanks
>

cacheRemove

I have a stored proc with several input parameters and it runs very efficient
(14 seconds) on my dev server (PII-450 single processor, 512 mb ram), even
when drastically changing the parameters. I run the same stored proc on my
production server (dual Pentium 1 ghz, 1 or 2 gb ram) and it will take up to
15 minutes. The CPU usage on the production server is very low. When we ran a
trace on production we found that it was removing the cache and then of
course the cache would be missing and it would recompile - sometimes up to 10
or 15 times through the execution of the store proc. It doesn't do the
recompiling on the dev server. We feel certain that there must be a
difference in the configuration of either SQL server between the servers or
Windows. We aren't sure where to start looking.
It might be that the connection in which you are running the sp has
different enviorment settings than the dev connection. Run a profile trace
on both with the existing connection and connection Login to see if they are
the same. The sp can be poorly written and force recompilation as well. Do
you have temp tables in it? Have a look here:
http://support.microsoft.com/default.aspx?kbid=243586
Andrew J. Kelly SQL MVP
"James" <James@.discussions.microsoft.com> wrote in message
news:76AECD82-262E-428C-B0D9-E03A9FC49BCD@.microsoft.com...
>I have a stored proc with several input parameters and it runs very
>efficient
> (14 seconds) on my dev server (PII-450 single processor, 512 mb ram), even
> when drastically changing the parameters. I run the same stored proc on my
> production server (dual Pentium 1 ghz, 1 or 2 gb ram) and it will take up
> to
> 15 minutes. The CPU usage on the production server is very low. When we
> ran a
> trace on production we found that it was removing the cache and then of
> course the cache would be missing and it would recompile - sometimes up to
> 10
> or 15 times through the execution of the store proc. It doesn't do the
> recompiling on the dev server. We feel certain that there must be a
> difference in the configuration of either SQL server between the servers
> or
> Windows. We aren't sure where to start looking.

cacheRemove

I have a stored proc with several input parameters and it runs very efficient
(14 seconds) on my dev server (PII-450 single processor, 512 mb ram), even
when drastically changing the parameters. I run the same stored proc on my
production server (dual Pentium 1 ghz, 1 or 2 gb ram) and it will take up to
15 minutes. The CPU usage on the production server is very low. When we ran a
trace on production we found that it was removing the cache and then of
course the cache would be missing and it would recompile - sometimes up to 10
or 15 times through the execution of the store proc. It doesn't do the
recompiling on the dev server. We feel certain that there must be a
difference in the configuration of either SQL server between the servers or
Windows. We aren't sure where to start looking.It might be that the connection in which you are running the sp has
different enviorment settings than the dev connection. Run a profile trace
on both with the existing connection and connection Login to see if they are
the same. The sp can be poorly written and force recompilation as well. Do
you have temp tables in it? Have a look here:
http://support.microsoft.com/default.aspx?kbid=243586
--
Andrew J. Kelly SQL MVP
"James" <James@.discussions.microsoft.com> wrote in message
news:76AECD82-262E-428C-B0D9-E03A9FC49BCD@.microsoft.com...
>I have a stored proc with several input parameters and it runs very
>efficient
> (14 seconds) on my dev server (PII-450 single processor, 512 mb ram), even
> when drastically changing the parameters. I run the same stored proc on my
> production server (dual Pentium 1 ghz, 1 or 2 gb ram) and it will take up
> to
> 15 minutes. The CPU usage on the production server is very low. When we
> ran a
> trace on production we found that it was removing the cache and then of
> course the cache would be missing and it would recompile - sometimes up to
> 10
> or 15 times through the execution of the store proc. It doesn't do the
> recompiling on the dev server. We feel certain that there must be a
> difference in the configuration of either SQL server between the servers
> or
> Windows. We aren't sure where to start looking.

cacheRemove

I have a stored proc with several input parameters and it runs very efficien
t
(14 seconds) on my dev server (PII-450 single processor, 512 mb ram), even
when drastically changing the parameters. I run the same stored proc on my
production server (dual Pentium 1 ghz, 1 or 2 gb ram) and it will take up to
15 minutes. The CPU usage on the production server is very low. When we ran
a
trace on production we found that it was removing the cache and then of
course the cache would be missing and it would recompile - sometimes up to 1
0
or 15 times through the execution of the store proc. It doesn't do the
recompiling on the dev server. We feel certain that there must be a
difference in the configuration of either SQL server between the servers or
Windows. We aren't sure where to start looking.It might be that the connection in which you are running the sp has
different enviorment settings than the dev connection. Run a profile trace
on both with the existing connection and connection Login to see if they are
the same. The sp can be poorly written and force recompilation as well. Do
you have temp tables in it? Have a look here:
http://support.microsoft.com/default.aspx?kbid=243586
Andrew J. Kelly SQL MVP
"James" <James@.discussions.microsoft.com> wrote in message
news:76AECD82-262E-428C-B0D9-E03A9FC49BCD@.microsoft.com...
>I have a stored proc with several input parameters and it runs very
>efficient
> (14 seconds) on my dev server (PII-450 single processor, 512 mb ram), even
> when drastically changing the parameters. I run the same stored proc on my
> production server (dual Pentium 1 ghz, 1 or 2 gb ram) and it will take up
> to
> 15 minutes. The CPU usage on the production server is very low. When we
> ran a
> trace on production we found that it was removing the cache and then of
> course the cache would be missing and it would recompile - sometimes up to
> 10
> or 15 times through the execution of the store proc. It doesn't do the
> recompiling on the dev server. We feel certain that there must be a
> difference in the configuration of either SQL server between the servers
> or
> Windows. We aren't sure where to start looking.

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?
> >> >
> >> >
> >>
> >>
> >
> >
>

Cache problem in Report Services

Hi All,
We are using RS for our application to show report. We are calling URL
for all report from ASP with some parameters, like on click of a button
with some filters we are using report link as
http://localhost/reportserver?/rptParameter/rdlTestParameter&rs:Command=Render&rc:Parameters=false&CID=2067
When we click on button, report gets fired, but it shows old data and
when we press refresh button of RS it shows updated data. We changed
the data and fired the above url again, it doesnt show updated data and
when we press refresh button it will shows updated data. May be this is
due to cache but in Report Manager i have checked the Do not cache
reports.., but problem still persist, can anybody please tell me how to
solve this problem.
Thanks
Regards
RajeshHi,
when calling your report, you can add the following parameter:
http://localhost/reportserver?/rptParameter/rdlTestParameter&rs:Command=Render&rc:Parameters=false&rs:ClearSession=true&CID=2067
Laurent
"Raj" <kapse_rajesh@.rediffmail.com> a écrit dans le message de news:
1141463990.435176.137140@.i40g2000cwc.googlegroups.com...
> Hi All,
> We are using RS for our application to show report. We are calling URL
> for all report from ASP with some parameters, like on click of a button
> with some filters we are using report link as
> http://localhost/reportserver?/rptParameter/rdlTestParameter&rs:Command=Render&rc:Parameters=false&CID=2067
> When we click on button, report gets fired, but it shows old data and
> when we press refresh button of RS it shows updated data. We changed
> the data and fired the above url again, it doesnt show updated data and
> when we press refresh button it will shows updated data. May be this is
> due to cache but in Report Manager i have checked the Do not cache
> reports.., but problem still persist, can anybody please tell me how to
> solve this problem.
>
> Thanks
> Regards
> Rajesh
>|||Thanks Laurent, this solved my problem.
Thanks
Rajesh

C#/SQL noob question

Using C# and ASP.NET, I need to connect to an SQL Server running Reporting Services, grab a report, supply a couple of parameters and an output format, and render a stream that I can write to a browser. Can anybody point me to an example or tutorial explaining how to do that?

thanks!

You probably want to start here:

http://msdn.microsoft.com/sql/bi/reporting/default.aspx?pull=/library/en-us/dnsql90/html/integratrsapp.asp

c# reusing parameters

I have a select statement which requires numerous parameters.
here is a snippet.

SqlCommand cmd = new SqlCommand("SELECT this from MyTable WHERE Answer1 = @.Att 1AND Answer2 = @.Att2 AND Answer3 = @.Att3", connection)

my parameters are added as follows.

SqlParameter Att1 = new SqlParameter("@.Att1", SqlDbType.VarChar, 50);
Att1.Value = Attributes1;
cmd.Parameters.Add(Att1);

and so on...

What I would like to do is be able to remove a parameter and re-run the SELECT statement if the number of entries retrieved is less than 5 (or any number)

I tried just having a new Sql command like this.

SqlCommand cmd2 = new SqlCommand("SELECT this from MyTable WHERE Answer1

= @.Att 1AND Answer2 = @.Att2, connection)

and then did this..

cmd2.Parameters.Add(Att1);

but it didn't work.

Is there a way to do this so I don't have to keep copying the whole parameter command?

Thank you in advance for your help. And please be gentle, I'm very new to this.YOu will have to remove the parameter from the first collections before using it in the second command.

cmd.Parameters.Remove(param);

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

sometimes it's too simple.

thanks.

Bypass PDF open/save prompt

I have figured out how to call a report and pass parameters via a URL,
but I get the dreaded Open/Save prompt. Is there any way to bypass
this and just open the PDF file? Also, is it even possible to simply
print the report with the user's default printer when a button is
clicked?
I am using SSRS 2005 and SQL 2005 June CTP with Visual Studio 2005 Beta
2. My application is ASP.NET with VB
Thanks in advance.RS SP2 has print support from the report viewer. I think that the open/save
prompt is a security feature of the web browser. If you want to get the PDF
without the prompt, you can use the Web Service' Render method
--
Floyd
<chrishalldba@.yahoo.com> wrote in message
news:1123779652.686768.275080@.g49g2000cwa.googlegroups.com...
>I have figured out how to call a report and pass parameters via a URL,
> but I get the dreaded Open/Save prompt. Is there any way to bypass
> this and just open the PDF file? Also, is it even possible to simply
> print the report with the user's default printer when a button is
> clicked?
> I am using SSRS 2005 and SQL 2005 June CTP with Visual Studio 2005 Beta
> 2. My application is ASP.NET with VB
> Thanks in advance.
>|||Thanks for the fast reply! Do you know if I could use this render
method with SSRS 2005? I'm using the June CTP if that makes a
difference. Do you have any code examples of this?
Chris|||From what I've seen, SP2's print is a button on the report viewer web page.
When that button is clicked, an activex control is downloaded that does the
printing. I don't know what happens behind the scenes.
--
Floyd
"Chris" <chrishalldba@.yahoo.com> wrote in message
news:1123780980.603308.323720@.o13g2000cwo.googlegroups.com...
> Thanks for the fast reply! Do you know if I could use this render
> method with SSRS 2005? I'm using the June CTP if that makes a
> difference. Do you have any code examples of this?
> Chris
>|||You can compile and install the Server Side printing example that comes with
SQL 2005. A user can then subscribe to a report and have it printed to the
named printer.. THe printer must be installed on the server...
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
<chrishalldba@.yahoo.com> wrote in message
news:1123779652.686768.275080@.g49g2000cwa.googlegroups.com...
>I have figured out how to call a report and pass parameters via a URL,
> but I get the dreaded Open/Save prompt. Is there any way to bypass
> this and just open the PDF file? Also, is it even possible to simply
> print the report with the user's default printer when a button is
> clicked?
> I am using SSRS 2005 and SQL 2005 June CTP with Visual Studio 2005 Beta
> 2. My application is ASP.NET with VB
> Thanks in advance.
>

Bulkload xml data

Is any one knowing how I bulkload data with characters like to SQL? I have no problem with common English characters. Is it anny parameters in the schema file or what?

Thanks for help Joel

Ps: I youse a schema file like this one...

<?xml version="1.0" ?>
<Schema xmlns="urn:schemas-microsoft-com:xml-data"
xmlns:dt="urn:schemas-microsoft-com:xml:datatypes"
xmlns:sql="urn:schemas-microsoft-com:xml-sql" >

<ElementType name="namn" dt:type="string" />
<ElementType name="titel" dt:type="string" />
<ElementType name="datum" dt:type="string" />

<ElementType name="ROOT" sql:is-constant="1">
<element type="test" />
</ElementType>

<ElementType name="test" sql:relation="test">
<element type="namn" sql:field="namn" />
<element type="titel" sql:field="titel" />
<element type="datum" sql:field="datum" />
</ElementType>

</Schema>use unicode string type.