data fields in header or footer

Hi all,
is there any workaorund for using fields in report header or footer?
Now I user parameters as workaround and I´m not really happy with that...
Thanks,
ToniYou mean dataset fields in the header?
Use reportitem for refering fields in the header.
like this.. =First(ReportItems("EmpName").Value)
Amarnath
"Toni Pohl" wrote:
> Hi all,
> is there any workaorund for using fields in report header or footer?
> Now I user parameters as workaround and Im not really happy with that...
> Thanks,
> Toni
>|||Hi Armanath,
well, this works! (and is a good workaround ;-)
Thanks!!!
Toni
"Amarnath" <Amarnath@.discussions.microsoft.com> schrieb im Newsbeitrag
news:BDAEC70F-7992-4277-89D9-6BCA627BC926@.microsoft.com...
> You mean dataset fields in the header?
> Use reportitem for refering fields in the header.
> like this.. =First(ReportItems("EmpName").Value)
> Amarnath
> "Toni Pohl" wrote:
>> is there any workaorund for using fields in report header or footer?
>> Now I user parameters as workaround and Im not really happy with that...
>> Thanks,sql

Data extension question?

Hi,
when you use sql server data extension and set the query to a stored
proceduere, Reporting services automatically creates report parameters based
on the stored proc parameters. I'm creating a custom data processing
extension and i would like to create parameters based on a config file. How
do i do this?
thanks
shankarIn your custom data processing extension, you should implement
IDbCommandAnalysis. In GetParameters, you can access your config file.
--
This post is provided 'AS IS' with no warranties, and confers no rights. All
rights reserved. Some assembly required. Batteries not included. Your
mileage may vary. Objects in mirror may be closer than they appear. No user
serviceable parts inside. Opening cover voids warranty. Keep out of reach of
children under 3.
"shankar" <sramasubramanian@.ozcap.com> wrote in message
news:uk7P%23hXdEHA.3132@.TK2MSFTNGP11.phx.gbl...
> Hi,
> when you use sql server data extension and set the query to a stored
> proceduere, Reporting services automatically creates report parameters
based
> on the stored proc parameters. I'm creating a custom data processing
> extension and i would like to create parameters based on a config file.
How
> do i do this?
> thanks
> shankar
>|||Thanks, Chris. I have one more question. I implemented IDbCommandAnalysis
interface and i got all the parameters from my config file. In the config
file , i also stored designer default values for all the parameter. I would
like the Reportdesigner to use the designer value when it executes the query
command. You could view the designer value as dummy value. For Example, if
you are using sql server stored proc, you would pass all the required
parameter as a part of command text.
When i implemented, IDbCommandAnalysis interface, i passed this
parameter,designer value pair. I think reporting services uses this
interface only to get the list of parameters and it simply ignores default
value.Is there any way to do this.
thanks
shankar
"Chris Hays [MSFT]" <chays@.online.microsoft.com> wrote in message
news:ueGeyMadEHA.2384@.TK2MSFTNGP09.phx.gbl...
> In your custom data processing extension, you should implement
> IDbCommandAnalysis. In GetParameters, you can access your config file.
> --
> This post is provided 'AS IS' with no warranties, and confers no rights.
All
> rights reserved. Some assembly required. Batteries not included. Your
> mileage may vary. Objects in mirror may be closer than they appear. No
user
> serviceable parts inside. Opening cover voids warranty. Keep out of reach
of
> children under 3.
> "shankar" <sramasubramanian@.ozcap.com> wrote in message
> news:uk7P%23hXdEHA.3132@.TK2MSFTNGP11.phx.gbl...
> > Hi,
> > when you use sql server data extension and set the query to a stored
> > proceduere, Reporting services automatically creates report parameters
> based
> > on the stored proc parameters. I'm creating a custom data processing
> > extension and i would like to create parameters based on a config file.
> How
> > do i do this?
> >
> > thanks
> >
> > shankar
> >
> >
>|||That is correct. The design tool uses the interface only to obtain the
parameter names, not to retrieve default values.
Using it for default values as well is a good idea. I'll forward it on to
the designer team. Thanks.
--
This post is provided 'AS IS' with no warranties, and confers no rights. All
rights reserved. Some assembly required. Batteries not included. Your
mileage may vary. Objects in mirror may be closer than they appear. No user
serviceable parts inside. Opening cover voids warranty. Keep out of reach of
children under 3.
"shankar" <sramasubramanian@.ozcap.com> wrote in message
news:OjXL2YLeEHA.3864@.TK2MSFTNGP10.phx.gbl...
> Thanks, Chris. I have one more question. I implemented IDbCommandAnalysis
> interface and i got all the parameters from my config file. In the config
> file , i also stored designer default values for all the parameter. I
would
> like the Reportdesigner to use the designer value when it executes the
query
> command. You could view the designer value as dummy value. For Example, if
> you are using sql server stored proc, you would pass all the required
> parameter as a part of command text.
> When i implemented, IDbCommandAnalysis interface, i passed this
> parameter,designer value pair. I think reporting services uses this
> interface only to get the list of parameters and it simply ignores default
> value.Is there any way to do this.
> thanks
> shankar
> "Chris Hays [MSFT]" <chays@.online.microsoft.com> wrote in message
> news:ueGeyMadEHA.2384@.TK2MSFTNGP09.phx.gbl...
> > In your custom data processing extension, you should implement
> > IDbCommandAnalysis. In GetParameters, you can access your config file.
> >
> > --
> > This post is provided 'AS IS' with no warranties, and confers no rights.
> All
> > rights reserved. Some assembly required. Batteries not included. Your
> > mileage may vary. Objects in mirror may be closer than they appear. No
> user
> > serviceable parts inside. Opening cover voids warranty. Keep out of
reach
> of
> > children under 3.
> > "shankar" <sramasubramanian@.ozcap.com> wrote in message
> > news:uk7P%23hXdEHA.3132@.TK2MSFTNGP11.phx.gbl...
> > > Hi,
> > > when you use sql server data extension and set the query to a
stored
> > > proceduere, Reporting services automatically creates report parameters
> > based
> > > on the stored proc parameters. I'm creating a custom data processing
> > > extension and i would like to create parameters based on a config
file.
> > How
> > > do i do this?
> > >
> > > thanks
> > >
> > > shankar
> > >
> > >
> >
> >
>

Data Extension Parameters

Hi,
I've implemented a data extension to use a custom business class to return a
dataset. This all works fine but I cannot seem to get parameters working. Has
anyone got any code snippets of creating parameters and the parameters being
available at run-time & design time.
All the examples I've seen don't use parameters so any help would be much
appreciated as I've got to produce this ASAP.
RegardsHi,
OK - I've worked out how to get the parameters in designer mode via the
GetParameters method. However, how do I get the parameters that I add via the
Report Dialog in VS.NET?
I've tried passing the parameter collection object but this comes back with
no items although there is one displayed in the preview.
Regards
"MikeD" wrote:
> Hi,
> I've implemented a data extension to use a custom business class to return a
> dataset. This all works fine but I cannot seem to get parameters working. Has
> anyone got any code snippets of creating parameters and the parameters being
> available at run-time & design time.
> All the examples I've seen don't use parameters so any help would be much
> appreciated as I've got to produce this ASAP.
> Regardssql

Data extension and ad hoc reporting

I've created a data processing extension that retrieves data from a
particular table based on the parameters given by the end-user. Of course
the tables do not have the same columns, so creating the report in the report
designer has been problematic. So far I've found one possible solution,
generating the rdl dynamically, similar to what is done in the "Ad hoc"
sample. I have been unable to successfully tie the dynamically generated rdl
to my data extension. I'm hoping that someone can point me in the right
direction.Sorry I forgot to include the following information.
RS 2000 Sp2
VB.NET
"Tamichan" wrote:
> I've created a data processing extension that retrieves data from a
> particular table based on the parameters given by the end-user. Of course
> the tables do not have the same columns, so creating the report in the report
> designer has been problematic. So far I've found one possible solution,
> generating the rdl dynamically, similar to what is done in the "Ad hoc"
> sample. I have been unable to successfully tie the dynamically generated rdl
> to my data extension. I'm hoping that someone can point me in the right
> direction.

Data Extension - Parameters ?

Hi,
I've just created a custom data extension for Reporting Services, which
works fine. Now, I want to support parameters. I correctly implemented
IDbParameter and IDbParameterCollection but they don't seem to be
called at anytime...
Any sample about implementing parameter support ?
Thanks in advance.
Best regards,
JulienNote that in order to support using parameters in your data extension (in
the report designer), you must also implement the IDbCommandAnalysis
interface, which parses the command text and returns the list of parameters
contained in the query.
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Julien Cheyssial" <julich@.gmail.com> wrote in message
news:1117728338.575235.15190@.g49g2000cwa.googlegroups.com...
> Hi,
> I've just created a custom data extension for Reporting Services, which
> works fine. Now, I want to support parameters. I correctly implemented
> IDbParameter and IDbParameterCollection but they don't seem to be
> called at anytime...
> Any sample about implementing parameter support ?
> Thanks in advance.
> Best regards,
> Julien
>|||Thanks, now I implemented IDbCommandAnalysis, parameters are now
correctly passed to my data extension. I thought IDbCommandAnalysis
wasn't mandatory, since it is called by the Reporting Services designer
to know which params are to be asked to the user.
Thanks,
Julien

Data Driven Subscriptions-Expressions in Parameters

I'm trying to create a report that, when generated, puts in a start
date of last Sunday to last Saturday. To be more specific, take this
week which begins on Sunday, Feb 10 and ends Saturday, Feb 16. When
this report gets generated anytime this week, the start date should be
Sunday Feb 3 and the end date should be Saturday Feb 9. When the
report is run next week, the start date/end date for that report
should be Feb 10/Feb 16.
I figured out the date expression for the start and end dates and set
them as the default for this report in Visual Studio. However, when I
upload them to my Report Server and set the data driven subscription
(DDS) to use the default for those date parameters, it won't let me.
So my question is, in the DDS itself, can I use an expression for the
date paramenters? If the answer is no, then I'm obviously not getting
the syntax correctly.
Any help would be greatly appreicated.On Feb 12, 4:49=A0pm, lsantos13 <lsanto...@.gmail.com> wrote:
> I'm trying to create a report that, when generated, puts in a start
> date of last Sunday to last Saturday. =A0To be more specific, take this
> week which begins on Sunday, Feb 10 and ends Saturday, Feb 16. =A0When
> this report gets generated anytime this week, the start date should be
> Sunday Feb 3 and the end date should be Saturday Feb 9. =A0When the
> report is run next week, the start date/end date for that report
> should be Feb 10/Feb 16.
> I figured out the date expression for the start and end dates and set
> them as the default for this report in Visual Studio. =A0However, when I
> upload them to my Report Server and set the data driven subscription
> (DDS) to use the default for those date parameters, it won't let me.
> So my question is, in the DDS itself, can I use an expression for the
> date paramenters? =A0If the answer is no, then I'm obviously not getting
> the syntax correctly.
> Any help would be greatly appreicated.
Correction on the last sentence - I meant to say "If the answer is
YES,..."|||Did you ever get a fix on this, as I am having the same problem. In my report
definition in VS I have a multi-select YEAR parameter that is dynamically
generating values using the query (a google query :).
***********
SELECT (YEAR(GETDATE()) -5) + 5*5*(a-1)+5*(b-1) + c AS [YEAR] FROM
(SELECT 1 a UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION
ALL SELECT 5) x
CROSS JOIN
(SELECT 1 b UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION
ALL SELECT 5) y
CROSS JOIN
(SELECT 1 c UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION
ALL SELECT 5) z
WHERE 5*5*(a-1)+5*(b-1) + c <= (YEAR(GETDATE())+1-(YEAR(GETDATE()) -5))
ORDER BY 1
*************
and default values for current year and last year using Year(Now) and
Year(Now)-1. This is causing an error in data-driven subscription
rsReportParameterValueNotSet. The odd thing is that this only occurs if other
parameters are set to "Query Results Field". If all parameters are set to
"Static Selection" and "Use Default" is checked everything works fine.

Data driven subscriptions scripting

Hi,

I have a reporting environement where the same report must be sent to a long ever changing list of recipients with different parameters values for each recipient.

My idea is to use data driven subscriptions and put back the administrative responsibility of managing this where it belongs, the business.

To achieve this goal, a one stop shopping user friendly management interface should be provided.

The list of features is as follow;

- pick list for existing reports
- auto-discovery of parameters and their values
- List of existing subscriptions with add/edit /delete buttons
- management of tables content from this interface
- creation of related data driven subscriptions from this interface

Right now, I think I can tackle some of these features piece by piece and build a library of scripts till the day all pieces can fall together.

The immediate question I have is
Can I script the creation of a data driven subscription and how?
I saw one can script a regular subscription but not a data driven one.

Can I use WS to do this? Any example, guidance or thought?

Thanks,

Philippe

SSRS exposes its features to the outside world as a set of Web Service APIs. Custom applications can call to these APIs. In fact, all the requirements you mentioned are available in the Report Manager which behind the scenes calls down to the RS Web service. The CreateDataDrivenSubscription API allows you to create a data-driven expression. See the documention for an example.

Data Driven Subscription with no results

I have a data driven subscription that is set up to email and uses a sql statement to generate the delivery settings and report parameters for each recipient (so each person does not reveive one email per row in the result set). I'm told that this is a good work around for creating a subscription that will NOT fire an email if there are no results returned, however I can not seem to get this to work...it continues to send an email with a blank report if there is no data for the previous day. Any thoughts/help would be appreciated. thanks in advance!If you want a fixed recipient list you want to do the following:

Assume your report data set comes from table DataTable, Pseudo SQL:
select Top 1 * from DataTable where InsertDate >= DATEADD(day, -1, GETDATE())

This will return exactly one row (one delivery) anytime there is data to report on. If you have multiple data sets in your report you'll need to play with the Joins to get this behavior.

Then in your subscription in the TO field, provide the static list of recipients.

If you need a dynamic list of recipients, I'd write a stored proc that returns the list of recipients if there was data in the DataTable, or no rows if there wasn't.

-Lukaszsql

Data Driven Subscription Query Parameters

Does anyone know if its possible to use report parameters as
parameters to a data driven subscription query. This would be useful
for changing the names of the files generated by the subscription
according to the parameters passed to the report.
For example, a report that takes a customer name as a parameter could
pass the customer name through to the data driven subscription query.
This would allow the customer name to be included in the file name of
the file that the data driven subscription generates.
Cheers,
David.In the parameters page, you are able to do a select statement & assign
parameters based on that.
So you could do a select * From names
Assuming your names table has a name field, then you could use the name &
field to assign it to a "file name".
"David Evans" wrote:
> Does anyone know if its possible to use report parameters as
> parameters to a data driven subscription query. This would be useful
> for changing the names of the files generated by the subscription
> according to the parameters passed to the report.
> For example, a report that takes a customer name as a parameter could
> pass the customer name through to the data driven subscription query.
> This would allow the customer name to be included in the file name of
> the file that the data driven subscription generates.
> Cheers,
> David.
>|||Yes, I know you can obtain the file name from a database query for a
data driven subscription. What I dont know is whether you can change
the file name based on the value of a report parameter.
Say I had a report with a parameter of customer name. If someone
created a data driven subscription and specified a customer of
customer1 for the customer name. I would like to be able to generate a
file name like "customer1_09-11-2004.csv".
I guess you would need to pass the customer name as the where clause
to the "select * from names" query mentioned below.
Is this possible.
Cheers,
David.
"mresanchez" <mresanchez@.donot-spam.com> wrote in message news:<1D9F5CAA-3EFD-4747-9455-F51DB8016FEF@.microsoft.com>...
> In the parameters page, you are able to do a select statement & assign
> parameters based on that.
> So you could do a select * From names
> Assuming your names table has a name field, then you could use the name &
> field to assign it to a "file name".
> "David Evans" wrote:
> > Does anyone know if its possible to use report parameters as
> > parameters to a data driven subscription query. This would be useful
> > for changing the names of the files generated by the subscription
> > according to the parameters passed to the report.
> >
> > For example, a report that takes a customer name as a parameter could
> > pass the customer name through to the data driven subscription query.
> > This would allow the customer name to be included in the file name of
> > the file that the data driven subscription generates.
> >
> > Cheers,
> >
> > David.
> >

Data Driven Subscription - Will the Report run for each parameter ?

Hi,

I have a report which has 200 parameters to be passed for which I am using Data driven subsription. This report takes 10 minutes to run so does this mean that this report would run 200 times or will it generate the report once and then burst the report based on parameters.

Any help is appreciated.

Adarsh

If you have one subscription then it will run once.|||

Thanks!! Can you specify how it would work in this case ?

AM

|||

Let say you have a report (Report1) with 3 parameters (Name, ID, Region) and one data driven subscription on top of it with the follwoing parameter values:

Name=abcde

ID=000

Region=xyz

The subscription will run the report with that param values.

I hope that helps.

Data Driven

Hi,
I understand I can create a stored procedure to return all the parameters
for data driven. The last statement of the Stored Procedure is Select *
DataDrivenTableName. However, after I put Exec SPName, and when i go to the
next page to fill out all the To, CC, Subject..., there is nothing in each
drop down box and I can't continue to finish the setup.
Any idea what's wrong.
Thanks
EdAFAIK RS uses first resultset to get data, not last.
Stjepan
"Ed" <Ed@.discussions.microsoft.com> wrote in message
news:2F0D6FE4-2FA8-49EB-A965-D0A57D47362C@.microsoft.com...
> Hi,
> I understand I can create a stored procedure to return all the parameters
> for data driven. The last statement of the Stored Procedure is Select *
> DataDrivenTableName. However, after I put Exec SPName, and when i go to
> the
> next page to fill out all the To, CC, Subject..., there is nothing in each
> drop down box and I can't continue to finish the setup.
> Any idea what's wrong.
> Thanks
> Ed

Data about Stored Procudre

How i get data about stored procedures

Data like stored procudres name

Name of parameters in store procudre .

And type of parameters in stored procedure.

Check this

select a.Name,b.Name as ParameterName,c.name as Datatype,b.Length From
(select *from sysobjects where xtype='p') a
inner join
Syscolumns b on a.id=b.id

inner join

Systypes c ON c.xtype=b.xtype

Madhu

|||

I found way to take detail

select * from INFORMATION_SCHEMA.PARAMATERS

CVS - Columns not exporting when Visibility>Hidden has a parameter in it

Hi there,
I am using SQL 2005, with Reporting Services 2005 and Visual Studio
2005 Team Suite.
I have a report which uses 52 parameters to determine which columns are
shown.
Each of the columns have a value in the visibilty>hidden field of
something like '=IIF(Parameters!showname.Value,False,True)' where
showname is a boolean parameter.
It displays perfectly in html & pdf - if the parameter is false, the
column is hidden, if the parameter is true, the column is hidden.
When i try to export to csv, it doesn't show the column at all. Even
if the expression is changed to '=IIF(1=1,False,True)' - the only way I
can get the column to show in the csv output is by setting the hidden
value to 'False' which means that its shown regardless of the
parameter.
Any ideas?I got a reply from a MS contact on msdn. Here is his reply for anyone
else with the same problem...
John,
You can't conditionally hide and show data in data renderers (CSV,
XML). This is by design. If you have an expression in the Hidden field
and 'Auto' in DataOutput tab for text boxes in the column, your data
will not be rendered into CSV or XML.
You can set DataElementOutput to Output for textboxes in the cells and
in the header, and the coulmn will always be in the output file.
Thanks!
----
- DenisL (SQL RS Team)
johnburns007@.gmail.com wrote:
> Hi there,
> I am using SQL 2005, with Reporting Services 2005 and Visual Studio
> 2005 Team Suite.
> I have a report which uses 52 parameters to determine which columns are
> shown.
> Each of the columns have a value in the visibilty>hidden field of
> something like '=IIF(Parameters!showname.Value,False,True)' where
> showname is a boolean parameter.
> It displays perfectly in html & pdf - if the parameter is false, the
> column is hidden, if the parameter is true, the column is hidden.
> When i try to export to csv, it doesn't show the column at all. Even
> if the expression is changed to '=IIF(1=1,False,True)' - the only way I
> can get the column to show in the csv output is by setting the hidden
> value to 'False' which means that its shown regardless of the
> parameter.
> Any ideas?