How to reference a shared schedule by Name ?

I'm generating a rss script for a report that will be depolyed to a new
server. If the report uses a shared schedule for its history snapshot then
in the script I need to pass a ScheduleReference to SetReportHistoryOptions.
What's the easiest way of interogating the new server to see if a Shared
Schedule with the same name exists and get a reference to it.
GetScheduleProperties needs a ScheduleID but this will be different between
the 2 servers. Since Shared Schedule names have to be unique why can't we
create a ScheduleReference based on the name of the Shared schedule? The
only other way I can think of is to call ListSchedules and then loop through
looking for one with the same name but this seems inefficient on a per
report basis.
--
Cheers,
JasYes, it is inefficient to have to loop through all the schedules,
unfortunately it is the only way to do it. By the way, how do you guarantee
that schedules with the same name will have the same reoccurrence pattern?
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
news:%230j1zQEFFHA.1836@.tk2msftngp13.phx.gbl...
> I'm generating a rss script for a report that will be depolyed to a new
> server. If the report uses a shared schedule for its history snapshot then
> in the script I need to pass a ScheduleReference to
> SetReportHistoryOptions. What's the easiest way of interogating the new
> server to see if a Shared Schedule with the same name exists and get a
> reference to it. GetScheduleProperties needs a ScheduleID but this will be
> different between the 2 servers. Since Shared Schedule names have to be
> unique why can't we create a ScheduleReference based on the name of the
> Shared schedule? The only other way I can think of is to call
> ListSchedules and then loop through looking for one with the same name but
> this seems inefficient on a per report basis.
> --
> Cheers,
> Jas
>|||By having standards and trying to stick to them :-) Shared schedules would
be set up by a DBA so as a project moves throught the environments, if a
need for a schedule is identified, the DBA should check to see if a suitable
shared schedule exists and get the developers to use that. If it doesn't
then create one and include it as part of the deployment package.
If I want to be ultra careful then when scripting a report on Server A that
reference a shared schedule I'd include the shared schedule definition in
the script so that if I do find one on Server B with the same name (which I
always should - in theory!) I can check the definitions are the same or
issue a warning/abort report creation. I could also optionally create the
schedule if it doesn't exist (but I'd rather not do that in the report
script)
At the simplest level i.e. just the report it's fairly easy to transport
across environments but as I keep adding all the various options and
properties (especially the scheduling stuff) to my scripting tool it's
getting more and more complex :-)
--
Cheers,
Jas
"Daniel Reib [MSFT]" <danreib@.online.microsoft.com> wrote in message
news:eR24d$TFFHA.2600@.TK2MSFTNGP09.phx.gbl...
> Yes, it is inefficient to have to loop through all the schedules,
> unfortunately it is the only way to do it. By the way, how do you
> guarantee that schedules with the same name will have the same
> reoccurrence pattern?
> --
> -Daniel
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>
> "Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
> news:%230j1zQEFFHA.1836@.tk2msftngp13.phx.gbl...
>> I'm generating a rss script for a report that will be depolyed to a new
>> server. If the report uses a shared schedule for its history snapshot
>> then in the script I need to pass a ScheduleReference to
>> SetReportHistoryOptions. What's the easiest way of interogating the new
>> server to see if a Shared Schedule with the same name exists and get a
>> reference to it. GetScheduleProperties needs a ScheduleID but this will
>> be different between the 2 servers. Since Shared Schedule names have to
>> be unique why can't we create a ScheduleReference based on the name of
>> the Shared schedule? The only other way I can think of is to call
>> ListSchedules and then loop through looking for one with the same name
>> but this seems inefficient on a per report basis.
>> --
>> Cheers,
>> Jas
>>
>

How to reference a shared datasource already on report server for data driven subscription

I see there's MS documentaion on creating a data driven subscription.
The example shows to create a data source for the data retrieval plan.
see
http://msdn2.microsoft.com/en-us/library/microsoft.wssux.reportingserviceswebservice.rsmanagementservice2005.reportingservice2005.createdatadrivensubscription.aspx
I would like to set the data source to a share data source on the
server. How should I implement that?
I have tried to use the following but failed:
...
delivery.Item = rs.GetDataSourceContents("/Data Sources/
testDataSource");
...I have worked this out: By creating a DataSourceReference object:
DataSourceReference reference = new
DataSourceReference();
reference.Reference = "/Data Sources/testDataSource";
delivery.Item = reference;
But then I got error like:
The current action cannot be completed because the user data source
credentials that are required to execute this report are not stored in
the report server database.
My credential information is stored with data source. If I manually
create the data-driven subscription from Report Manager, and use the
same shared data source, I wouldn't get this error. Anyone knows why?
Thanks

How to refer to Custom function in a query

I want to be able to refer to a custom function in a query
The Function is defined in the report proprties "Code" tab as follows:
public shared function MyCustomer as String
Return "ALFKI"
end function
I have tried different query formats, and I currently have:
="SELECT * FROM customers where CustomerID='" + Code.MyCustomer() +
"'"
I am using the Northwind database as an example
Though the code compiles, I am unable to refer to any of the fields in
"customers"
I know there are other ways to accomplish this. What I am interested in
is how to refer to user defined code in QueriesYou might be able to pull a field list first with simply
SELECT * FROM customers
and then change the SQL to include the code. You might want to try the
latter using View Code and see of that gets around the auto-population of
fields step.
--
Cheers,
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"cab0san" <cabosan@.gmail.com> wrote in message
news:1109783555.987331.113460@.g14g2000cwa.googlegroups.com...
>I want to be able to refer to a custom function in a query
> The Function is defined in the report proprties "Code" tab as follows:
> public shared function MyCustomer as String
> Return "ALFKI"
> end function
> I have tried different query formats, and I currently have:
> ="SELECT * FROM customers where CustomerID='" + Code.MyCustomer() +
> "'"
> I am using the Northwind database as an example
> Though the code compiles, I am unable to refer to any of the fields in
> "customers"
> I know there are other ways to accomplish this. What I am interested in
> is how to refer to user defined code in Queries
>|||Thanks, that works. A bit "klugy" but it works!
So, I set my query to
select * from customers, then execute the query, then click another
tab, causing the field list to populate, then switch back to the data
tab and change it to
="SELECT * FROM customers where CustomerID='" + Code.MyCustomer() +
"'"
Behind the scenes that is accomplishing the step of adding the dataset
definition to my RDL|||I'm glad it's working.
I found that editing directly in the data tab works fine until you have a
complex SQL statement that uses its own declared variables (which for SQL
Server look similar to parameters: @.ParmName). When you add parameters
manually to the dataset, sometimes the data tab editing blows away those
manually entered parameters. That's when I use Bruce's suggestion to edit
SQL using View Code.
--
Cheers,
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"cab0san" <cabosan@.gmail.com> wrote in message
news:1109862516.895060.241340@.l41g2000cwc.googlegroups.com...
> Thanks, that works. A bit "klugy" but it works!
> So, I set my query to
> select * from customers, then execute the query, then click another
> tab, causing the field list to populate, then switch back to the data
> tab and change it to
> ="SELECT * FROM customers where CustomerID='" + Code.MyCustomer() +
> "'"
> Behind the scenes that is accomplishing the step of adding the dataset
> definition to my RDL
>|||I have a similar issue however I am using a DB2 stored procedure so I dont
have the option to manipulate the sql. I was trying to put a translation on
the parameters list somehow and that doesnt seem to work although in the
Hitchhikers Guide it states that you can use and expression anywhere you have
the opportunity presented by <expression> or Fx ... which I see as an option
on the datasource parameters tab. I need to translate the client I receive
in as a parameter into a different value.
On the dataset parameters tab I have Parameter Name PM_Client_Code and the
value is =Code.TranslateClient(Parameters!PM_CLIENT_CODE.Value)
The function TranslateClient is this:
Function TranslateClient(ByVal clientnum As String) As String
TranslateClient = "ABC"
End Function
It doesnt work. Although the code works in vb .net just fine.
"cab0san" wrote:
> Thanks, that works. A bit "klugy" but it works!
> So, I set my query to
> select * from customers, then execute the query, then click another
> tab, causing the field list to populate, then switch back to the data
> tab and change it to
> ="SELECT * FROM customers where CustomerID='" + Code.MyCustomer() +
> "'"
> Behind the scenes that is accomplishing the step of adding the dataset
> definition to my RDL
>|||That looks good to me and should work. I suggest creating a report just with
your parameter and a textbox and assign this expression to the textbox (so
you can see what happens). Make sure that your parameter is exactly the name
you have here, the parameter is case sensitive.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"MJ Taft" <MJTaft@.discussions.microsoft.com> wrote in message
news:0EF45D79-4C8A-4B99-9D5A-30610FFB1F9D@.microsoft.com...
> I have a similar issue however I am using a DB2 stored procedure so I dont
> have the option to manipulate the sql. I was trying to put a translation
on
> the parameters list somehow and that doesnt seem to work although in the
> Hitchhikers Guide it states that you can use and expression anywhere you
have
> the opportunity presented by <expression> or Fx ... which I see as an
option
> on the datasource parameters tab. I need to translate the client I
receive
> in as a parameter into a different value.
> On the dataset parameters tab I have Parameter Name PM_Client_Code and the
> value is =Code.TranslateClient(Parameters!PM_CLIENT_CODE.Value)
> The function TranslateClient is this:
> Function TranslateClient(ByVal clientnum As String) As String
> TranslateClient = "ABC"
> End Function
> It doesnt work. Although the code works in vb .net just fine.
> "cab0san" wrote:
> > Thanks, that works. A bit "klugy" but it works!
> >
> > So, I set my query to
> >
> > select * from customers, then execute the query, then click another
> > tab, causing the field list to populate, then switch back to the data
> > tab and change it to
> > ="SELECT * FROM customers where CustomerID='" + Code.MyCustomer() +
> > "'"
> > Behind the scenes that is accomplishing the step of adding the dataset
> > definition to my RDL
> >
> >

How to query the date whose value is in text format

Hello,

I get a problem in developing the company report. The column of "BATCH" in Oracle view is shared by all departments that need the different entries, so we have to use "Text" data type for this column in order meet all departments needs.

One department uses it as a sample date column. The text entry value is in the format of mm/dd/yyyy.

If I do not do the data converting, when I set the query: Where SampleDate between '01/01/2005' and '01/31/2005', the outcome not only include the January data of 2005 but also include the January data from all privious years, because system evaluates the data in text format rather than date format.

What I have done is to use Oracle To_Date(SampleDate,'mm/dd/yyyy') function to convert the text format to Oracle date format in report dataset.

I have two problems:

1) How to take out the wrong format enties? for example, user enters 1102/2005 instead of 11/02/2005. I find that the wrong formating entries cause the failure of data retrival. Micro T-SQL has a function of IsDate() to flag out the entries that are not in date formating, but it does not work here, I guess the reason may be our data source is Oracle.

2) Even the data is conveted, it still does not work propertly.

For example, I write:

Where To_Date(Batch,'mm/dd/yyyy') between '01/01/2005' and '01/31/2005'

it does not work

I write: Where To_Date(Batch,'mm/dd/yyyy') between To_date('01/01/2005','mm/dd/yyyy') and To_date('01/31/2005','mm/dd,yyyy')

it still does not work.

How to resolve these issues.

Thanks,

Zixing

For problem #1, if you are using Oracle 10g you can use a regular expression in your query so you only get valid matching dates. If you aren't using 10g, then you could use the like operator with some wildcards.

For problem #2, try running your query in an Oracle client first just to make sure that you have the query syntax right. I am not familiar with Oracle tools for performing queries but I am thinking something like MSSQL Query Analyzer.