Data fields in the header

I'm getting this error when I try to preview my report: The value expression
for the textbox 'RepairOrderID' refers to a field. Fields cannot be used in
page headers or footers.
I searched through the news groups and a found suggestion to replace:
Fields!RepairOrderID.Value
with
ReportItems!RepairOrderID.Value
I tried this, it compiles now but when the report renders "#Error" displays
where the RepairOrderID should appear. I found another suggestion to use the
First function this does not work either...
Can someone please help....?
Thanks!!!
DanLooks like your textbox is referring to itself. You need to refer to a
textbox from report body.
More reliable solution is to create read-only parameter with default value
from query and then to use this parameter in the page header textbox.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"alien2_51" <dan.billow"at"n.o.s.p.a.m.monacocoach.commercialversion> wrote
in message news:OMQtyN0eEHA.2560@.TK2MSFTNGP09.phx.gbl...
> I'm getting this error when I try to preview my report: The value
> expression
> for the textbox 'RepairOrderID' refers to a field. Fields cannot be used
> in
> page headers or footers.
> I searched through the news groups and a found suggestion to replace:
> Fields!RepairOrderID.Value
> with
> ReportItems!RepairOrderID.Value
> I tried this, it compiles now but when the report renders "#Error"
> displays
> where the RepairOrderID should appear. I found another suggestion to use
> the
> First function this does not work either...
> Can someone please help....?
> Thanks!!!
> Dan
>|||I don't think my textbox is referring to its self... The name of the textbox
is textbox18, besides I'm explicitly qualifying the Value property with
Fields! or ReportItems!. How do I create a read-only parameter from
query...? I know this does not work.. SELECT @.PARAM = Value FROM Table
Thanks,
Dan
"Lev Semenets [MSFT]" <levs@.microsoft.com> wrote in message
news:%23N$kB50eEHA.3632@.TK2MSFTNGP11.phx.gbl...
> Looks like your textbox is referring to itself. You need to refer to a
> textbox from report body.
> More reliable solution is to create read-only parameter with default value
> from query and then to use this parameter in the page header textbox.
> --
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "alien2_51" <dan.billow"at"n.o.s.p.a.m.monacocoach.commercialversion>
wrote
> in message news:OMQtyN0eEHA.2560@.TK2MSFTNGP09.phx.gbl...
> > I'm getting this error when I try to preview my report: The value
> > expression
> > for the textbox 'RepairOrderID' refers to a field. Fields cannot be used
> > in
> > page headers or footers.
> >
> > I searched through the news groups and a found suggestion to replace:
> >
> > Fields!RepairOrderID.Value
> > with
> > ReportItems!RepairOrderID.Value
> >
> > I tried this, it compiles now but when the report renders "#Error"
> > displays
> > where the RepairOrderID should appear. I found another suggestion to use
> > the
> > First function this does not work either...
> >
> > Can someone please help....?
> >
> > Thanks!!!
> >
> > Dan
> >
> >
>|||http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSCREATE/htm/rcr_creating_interactive_v1_50fn.asp
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rshowto/htm/hrs_designer_v1_38du.asp
Create a parameter without Prompt , set its default value to From Query,
select your dataset and ReportOrderID as value field.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"alien2_51" <dan.billow"at"n.o.s.p.a.m.monacocoach.commercialversion> wrote
in message news:ev1O838eEHA.1100@.TK2MSFTNGP10.phx.gbl...
>I don't think my textbox is referring to its self... The name of the
>textbox
> is textbox18, besides I'm explicitly qualifying the Value property with
> Fields! or ReportItems!. How do I create a read-only parameter from
> query...? I know this does not work.. SELECT @.PARAM = Value FROM Table
> Thanks,
> Dan
> "Lev Semenets [MSFT]" <levs@.microsoft.com> wrote in message
> news:%23N$kB50eEHA.3632@.TK2MSFTNGP11.phx.gbl...
>> Looks like your textbox is referring to itself. You need to refer to a
>> textbox from report body.
>> More reliable solution is to create read-only parameter with default
>> value
>> from query and then to use this parameter in the page header textbox.
>> --
>> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>> "alien2_51" <dan.billow"at"n.o.s.p.a.m.monacocoach.commercialversion>
> wrote
>> in message news:OMQtyN0eEHA.2560@.TK2MSFTNGP09.phx.gbl...
>> > I'm getting this error when I try to preview my report: The value
>> > expression
>> > for the textbox 'RepairOrderID' refers to a field. Fields cannot be
>> > used
>> > in
>> > page headers or footers.
>> >
>> > I searched through the news groups and a found suggestion to replace:
>> >
>> > Fields!RepairOrderID.Value
>> > with
>> > ReportItems!RepairOrderID.Value
>> >
>> > I tried this, it compiles now but when the report renders "#Error"
>> > displays
>> > where the RepairOrderID should appear. I found another suggestion to
>> > use
>> > the
>> > First function this does not work either...
>> >
>> > Can someone please help....?
>> >
>> > Thanks!!!
>> >
>> > Dan
>> >
>> >
>>
>|||Excellent!!... That's what I needed... Thanks..:)
Dan
"Lev Semenets [MSFT]" <levs@.microsoft.com> wrote in message
news:udrYUvEfEHA.4092@.TK2MSFTNGP10.phx.gbl...
>
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSCREATE/htm/rcr_creating_interactive_v1_50fn.asp
>
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rshowto/htm/hrs_designer_v1_38du.asp
> Create a parameter without Prompt , set its default value to From Query,
> select your dataset and ReportOrderID as value field.
> --
> This posting is provided "AS IS" with no warranties, and confers no
rights.
>
> "alien2_51" <dan.billow"at"n.o.s.p.a.m.monacocoach.commercialversion>
wrote
> in message news:ev1O838eEHA.1100@.TK2MSFTNGP10.phx.gbl...
> >I don't think my textbox is referring to its self... The name of the
> >textbox
> > is textbox18, besides I'm explicitly qualifying the Value property with
> > Fields! or ReportItems!. How do I create a read-only parameter from
> > query...? I know this does not work.. SELECT @.PARAM = Value FROM Table
> >
> > Thanks,
> >
> > Dan
> >
> > "Lev Semenets [MSFT]" <levs@.microsoft.com> wrote in message
> > news:%23N$kB50eEHA.3632@.TK2MSFTNGP11.phx.gbl...
> >> Looks like your textbox is referring to itself. You need to refer to a
> >> textbox from report body.
> >> More reliable solution is to create read-only parameter with default
> >> value
> >> from query and then to use this parameter in the page header textbox.
> >>
> >> --
> >> This posting is provided "AS IS" with no warranties, and confers no
> > rights.
> >>
> >> "alien2_51" <dan.billow"at"n.o.s.p.a.m.monacocoach.commercialversion>
> > wrote
> >> in message news:OMQtyN0eEHA.2560@.TK2MSFTNGP09.phx.gbl...
> >> > I'm getting this error when I try to preview my report: The value
> >> > expression
> >> > for the textbox 'RepairOrderID' refers to a field. Fields cannot be
> >> > used
> >> > in
> >> > page headers or footers.
> >> >
> >> > I searched through the news groups and a found suggestion to replace:
> >> >
> >> > Fields!RepairOrderID.Value
> >> > with
> >> > ReportItems!RepairOrderID.Value
> >> >
> >> > I tried this, it compiles now but when the report renders "#Error"
> >> > displays
> >> > where the RepairOrderID should appear. I found another suggestion to
> >> > use
> >> > the
> >> > First function this does not work either...
> >> >
> >> > Can someone please help....?
> >> >
> >> > Thanks!!!
> >> >
> >> > Dan
> >> >
> >> >
> >>
> >>
> >
> >
>

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 Field not Truncating

I have a data field in my report that needs to truncate when it is too long. I do not have "Can Grow" checked, but this field will still run over and interrupt other data.
I cannot reposition this field so it won't interrupt anything else.
Thanks for any help.If the filed has too many characters without a space this may happen. To avoid this create a formula @.MyField having the code
mid(Field,1,20)So only 20 charaters will be printed

Data feeding a report is cached between different calls for a session?

Microsoft SQL Server 2000 - 8.00.818 + Reporting Services SP2
We have created a report (for invoices) that is usually called to render pdf
files (for quality) but can also be rendered into tif files (lower quality).
The front end program for users is written in Access 2003 with a SQL Server
back end for storing the data (client-server approach). Our problem is
described as follows:
1. We create a new invoice (insert new record for invoices table and some
new records for invoicesdetails table)
2. User wants to preview the pdf file for the invoice and click on a button
(MS Access) that internally connects to reporting services (via https)
downloads the pdf file, stores it locally (deleting the original local file
if exists) and opens using acrobat reader.
3. User needs to modify something in the invoice or details and updates the
record (s). Updates the records from tables that are used in the report.
4. Click again on the button to generate the pdf again and to see the new
revision of the invoice but they still receive the old data!!!
Simplifying the scenario, if the data feeding a report is changed between 2
subsecuent calls from the same user (session), reporting services generates
the report with oudated data. It seems that reporting services sees the
request coming from the same user asking for the same data with same
parameters and within the same session and instead of asking SQL server for
the data again (that might have changed), serves the report with the same
old data that it has stored somehow.
To reproduce this behaviour:
1. Create a simple report MyTestReport accepting a parameter InvoiceId
(integer) based on a simple table TInvoices (InvoiceId int, Amount money).
The report just shows the InvoiceId and the Amount of it on a single page.
It uses a shared datasource, type: Microsoft SQL Server, Windows
authentication. Upload this report to Reporting Services.
2. Execute the following SQL command 'INSERT INTO TInvoices (InvoiceId,
Amount) VALUES (0,0)' to ensure that an invoice with Id=0 exists with a
amount of 0.
3. Copy this URL and paste it into your web browser:
https://reports.mydomain.com/reportserver?/MyTestReport&InvoiceId=0&rs:Command=Render&rs:Format=HTML4.0
You will see a reporting services page with InvoiceId field on top filled in
with a 0 and the contents of the report showing 0 0 on it.
4. From the dropdown list of 'Select a format' select 'Acrobat PDF File' and
click on 'export', then click 'open'. You see a pdf file with 0 0 on it.
This is correct. Close acrobat reader. Do not close IE window.
5. Go back to your SQL Server and execute 'UPDATE TInvoices SET Amount=1000
WHERE InvoiceId = 0'
6. Up to this poing, the 0 0 on IE window is outdated, but we have not
refreshed yet. If we click on IE Refresh button to retrieve the same
original URL that we had on our clipboard a 0 0 still appears!!!!! The data
is not fetched again from SQL server, reporting services is caching it
somehow and returning it to me again. To be sure that it is not a problem
with IE cache, go Tools | Internet options | General tab and click the
buttons 'Delete cookies' and 'Delete files'. Click on Accept button. Now
refresh the page again (F5). The page is still showing 0 0!!!!!
7. If we select 'Adobe PDF File' from 'Select a format' dropdownlist and
click again on 'export', then 'open', a new PDF file is downloaded and open,
but there is a 0 0 on it yet!!!!
Of course, if you click the button 'View report' on top right side of the
page, the report is shown (requeried) with updated data. The report reads 0
1000 which is correct. However in our scenario, our users are not browsing
through reporting services site interface and do not have that 'View report'
button at hand. When they need a report (Invoices or other) with whatever
the filter and parameters, a MS Access Form is shown to them to select from
dropdown lists, dates, codes, etc. Then our front end in MS Access
constructs the apropriate URL considering the parameters the user has just
entered in the form and opens (or downloads) the report acordingly.
The only way to retrieve the updated data without clicking that 'View
report' button is having the user asking for another unwanted InvoiceId
(InvoiceId=1 for instance) before asking again for what he/she needs:
UPDATE TInvoices (InvoiceId, Amount) VALUES (0,0)
INSERT TInvoices (InvoiceId, Amount) VALUES (1,10)
Paste
https://reports.mydomain.com/reportserver?/MyTestReport&InvoiceId=0&rs:Command=Render&rs:Format=HTML4.0
in IE
0 0 is shown
UPDATE TInvoices (InvoiceId, Amount) VALUES (0,1000)
Paste
https://reports.mydomain.com/reportserver?/MyTestReport&InvoiceId=0&rs:Command=Render&rs:Format=HTML4.0
in IE or click refresh
0 0 is shown wich is incorrect
Paste
https://reports.mydomain.com/reportserver?/MyTestReport&InvoiceId=1&rs:Command=Render&rs:Format=HTML4.0
in IE
1 10 is shown (unwanted, but needed for reporting services flushing its
internal outdated data)
Paste
https://reports.mydomain.com/reportserver?/MyTestReport&InvoiceId=0&rs:Command=Render&rs:Format=HTML4.0
in IE
0 1000 is shown which is correct.
It seems that unless the user asks reporting services to generate the report
with other parameters, it returns the old dataset without asking again to
SQL server, in this case generating a report with outdated data.
Note: We have also checked that 'execution properties' for the report has
'Show this report with the most recent data' and 'Do not save cache copies
of this report' enabled.
Is this a known issue? Is there a fix/workaround for it? Any registry tweaks
for forcing reporting services to always ask the datasource instead of
caching?David,
This is correct behavior. Reporting Services maintains report session,
which is needed for certain things like switching between pages or drilling
down.
For your situation, I think there are two easy things you can do. First,
you can add "rs:ClearSession=true" to your URL. This will treat the request
as a new session, and not reuse the data from the previous report view. You
can find more information if you search BOL or this newsgroup for
ClearSession.
I think you can also resolve this problem by deallocating your web request
object in Access after each report view. I haven't done this myself, but I
think that would cause each request to start with a new session.
(FYI, the caching option allows you to store/share dataset results across
users. This is a big performance boost for data that doesn't change, e.g.
last month's balance sheet. Your problem has to do with session for the same
user.)
HTH,
Ted
"David Lightman Robles" wrote:
> It seems that unless the user asks reporting services to generate the report
> with other parameters, it returns the old dataset without asking again to
> SQL server, in this case generating a report with outdated data.
> Note: We have also checked that 'execution properties' for the report has
> 'Show this report with the most recent data' and 'Do not save cache copies
> of this report' enabled.
> Is this a known issue? Is there a fix/workaround for it? Any registry tweaks
> for forcing reporting services to always ask the datasource instead of
> caching?|||Thanks for the information. That solved all our problems.
"Ted K" <tedk@.nospam.nospam> escribió en el mensaje
news:F5ABBB95-AE6C-482B-B1B6-1A26859B0C0B@.microsoft.com...
> David,
> This is correct behavior. Reporting Services maintains report session,
> which is needed for certain things like switching between pages or
> drilling
> down.
> For your situation, I think there are two easy things you can do. First,
> you can add "rs:ClearSession=true" to your URL. This will treat the
> request
> as a new session, and not reuse the data from the previous report view.
> You
> can find more information if you search BOL or this newsgroup for
> ClearSession.
> I think you can also resolve this problem by deallocating your web request
> object in Access after each report view. I haven't done this myself, but
> I
> think that would cause each request to start with a new session.
> (FYI, the caching option allows you to store/share dataset results across
> users. This is a big performance boost for data that doesn't change, e.g.
> last month's balance sheet. Your problem has to do with session for the
> same
> user.)
> HTH,
> Ted
> "David Lightman Robles" wrote:
>> It seems that unless the user asks reporting services to generate the
>> report
>> with other parameters, it returns the old dataset without asking again to
>> SQL server, in this case generating a report with outdated data.
>> Note: We have also checked that 'execution properties' for the report has
>> 'Show this report with the most recent data' and 'Do not save cache
>> copies
>> of this report' enabled.
>> Is this a known issue? Is there a fix/workaround for it? Any registry
>> tweaks
>> for forcing reporting services to always ask the datasource instead of
>> caching?
>sql

Data Extraction Program within SQL Reporting Svcs

I have installed SQL Reporting Services and have been trying to implement the
Report Pack for SharePoint Portal Server that was recently released. Part of
the VERY limited instructions that comes with the Report Pack states that you
can import your existing SPS log files using the Data Extraction Program
(DEP).
I have tried this a number of times now and the SPS logs will import into
dbSPSReportingStaging with no errors. However, when the application attempts
to move those logs from dbSPSReportingStaging to dbSPSReporting I get an
error message "String or binary data would be truncated" and the statement
terminates without moving the data into dbSPSReporting.
Obviously (to me anyway) there is a field that is not of sufficient length
(or perhaps the wrong type) in dbSPSReporting. Given the fact that this DB is
created using a .sql script written and included in the Report Pack how can I
identify which field is causing the problem and fix it?
Any help is appreciated!
TIAMark Harrison pointed me to the link below which should fix the issue
http://blogs.msdn.com/dinod/archive/2005/08/19/453724.aspx
"Jay" wrote:
> I have installed SQL Reporting Services and have been trying to implement the
> Report Pack for SharePoint Portal Server that was recently released. Part of
> the VERY limited instructions that comes with the Report Pack states that you
> can import your existing SPS log files using the Data Extraction Program
> (DEP).
> I have tried this a number of times now and the SPS logs will import into
> dbSPSReportingStaging with no errors. However, when the application attempts
> to move those logs from dbSPSReportingStaging to dbSPSReporting I get an
> error message "String or binary data would be truncated" and the statement
> terminates without moving the data into dbSPSReporting.
> Obviously (to me anyway) there is a field that is not of sufficient length
> (or perhaps the wrong type) in dbSPSReporting. Given the fact that this DB is
> created using a .sql script written and included in the Report Pack how can I
> identify which field is causing the problem and fix it?
> Any help is appreciated!
> TIA

Data Extensions - Through Business Logic

I have a multi-tier application that does a lot of business processing of
data. I am looking for a way to use SQL Report Server to render reports
based upon this data. I have looked at several examples of extensions and
built one using a serialized XML file. What I am looking for though is to be
able to create an extension that uses data that has been processed from the
business logic in real time without creating a serialized XML file. Any help
would be appreciatedHave you check my DPE? It serializes the dataset and passes it as a
parameter to the report.
http://www.gotdotnet.com/Community/UserSamples/Details.aspx?SampleGuid=B8468707-56EF-4864-AC51-D83FC3273FE5
As a side node, the new controls in RS 2005 will support standalone mode
where you could bind your report to a dataset.
--
Hope this helps.
---
Teo Lachev, MVP [SQL Server], MCSD, MCT
Author: "Microsoft Reporting Services in Action"
Publisher website: http://www.manning.com/lachev
Buy it from Amazon.com: http://shrinkster.com/eq
Home page and blog: http://www.prologika.com/
---
"mkola" <mkola@.discussions.microsoft.com> wrote in message
news:C3E61BE8-D8E8-4330-8C0E-BD0221BC3BD4@.microsoft.com...
> I have a multi-tier application that does a lot of business processing of
> data. I am looking for a way to use SQL Report Server to render reports
> based upon this data. I have looked at several examples of extensions and
> built one using a serialized XML file. What I am looking for though is to
be
> able to create an extension that uses data that has been processed from
the
> business logic in real time without creating a serialized XML file. Any
help
> would be appreciated|||THanks for the Sample Teo. I will look into it today...
Mark
"Teo Lachev [MVP]" wrote:
> Have you check my DPE? It serializes the dataset and passes it as a
> parameter to the report.
> http://www.gotdotnet.com/Community/UserSamples/Details.aspx?SampleGuid=B8468707-56EF-4864-AC51-D83FC3273FE5
> As a side node, the new controls in RS 2005 will support standalone mode
> where you could bind your report to a dataset.
> --
> Hope this helps.
> ---
> Teo Lachev, MVP [SQL Server], MCSD, MCT
> Author: "Microsoft Reporting Services in Action"
> Publisher website: http://www.manning.com/lachev
> Buy it from Amazon.com: http://shrinkster.com/eq
> Home page and blog: http://www.prologika.com/
> ---
> "mkola" <mkola@.discussions.microsoft.com> wrote in message
> news:C3E61BE8-D8E8-4330-8C0E-BD0221BC3BD4@.microsoft.com...
> > I have a multi-tier application that does a lot of business processing of
> > data. I am looking for a way to use SQL Report Server to render reports
> > based upon this data. I have looked at several examples of extensions and
> > built one using a serialized XML file. What I am looking for though is to
> be
> > able to create an extension that uses data that has been processed from
> the
> > business logic in real time without creating a serialized XML file. Any
> help
> > would be appreciated
>
>

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 error

Im in the process of creating my first data extension for reporting
services.
Im getting an error while creating a new report via the add report
wizard. My entry shows up in the type dropdown just as expected but
the next screen has a text box that says querystring. When I click
next I get the following error:
"an error occurred while the query design method was being saved.
Object not set to an instance of an object."
How might I know where this occured? Any help is appreciated.And yes I am debugging this and it is failing in the
the createcommand() in the connection class.
I get no clue as to why. Perhaps its my db conn string. Or something
with those config files.|||And yes I am debugging this and it is failing in the
the createcommand() in the connection class.
I get no clue as to why. Perhaps its my db conn string. Or something
with those config files.

data export to CSV

I have a report that produces over 3 million rows, this report is
export to CSV and is scheduled to run weekly.
Sometimes the report is needed to run manually. Initially this report
timed out, now the report runs but takes a very long time to complete.
If the stored procedure is run directly it takes about 5 mins to
execute. Running in reporting services takes upward of an hour.
Does anyone have suggestions on how to speed up this report.
my initial thoughts would be to use SSRS to populate the parameters
and maybe hook into the reporting engine and execute the stored
procedure directly with the parameters defined
thanks in advance
IanHi, how run this report? http? web service? how?
--
----
Microsoft M.V.P en SQLServer
SQLTotal Consulting - Servicios en SQLServer
Email: maxi.da@.gmail.com.nospam
----
<emery.ian@.googlemail.com> escribió en el mensaje
news:e977052b-81b7-404f-88e1-d14968fbe0ab@.e25g2000prg.googlegroups.com...
>I have a report that produces over 3 million rows, this report is
> export to CSV and is scheduled to run weekly.
> Sometimes the report is needed to run manually. Initially this report
> timed out, now the report runs but takes a very long time to complete.
> If the stored procedure is run directly it takes about 5 mins to
> execute. Running in reporting services takes upward of an hour.
> Does anyone have suggestions on how to speed up this report.
> my initial thoughts would be to use SSRS to populate the parameters
> and maybe hook into the reporting engine and execute the stored
> procedure directly with the parameters defined
>
> thanks in advance
> Ian|||Do not waste your time trying to run the report different ways, it will not
affect the time.
Your time is coming in two places. One, does it really only take 5 minutes
to extract 5 million records? I'm not sure how it works when executing a
stored procedure from Query Analyzer that returns 5 million records, whether
it really retrieves all the records or not. Anyway, I think that is one
area, the number of records being physically returned takes time.
Second, RS 2000 and RS 2005 does all rendering in RAM. It does not page
anything out to disk or utilize the disk at all when rendering. I am pretty
sure this is changing substantially in RS 2008. Adding more RAM to the
server will help.
The other option is to use SSIS instead.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
<emery.ian@.googlemail.com> wrote in message
news:e977052b-81b7-404f-88e1-d14968fbe0ab@.e25g2000prg.googlegroups.com...
>I have a report that produces over 3 million rows, this report is
> export to CSV and is scheduled to run weekly.
> Sometimes the report is needed to run manually. Initially this report
> timed out, now the report runs but takes a very long time to complete.
> If the stored procedure is run directly it takes about 5 mins to
> execute. Running in reporting services takes upward of an hour.
> Does anyone have suggestions on how to speed up this report.
> my initial thoughts would be to use SSRS to populate the parameters
> and maybe hook into the reporting engine and execute the stored
> procedure directly with the parameters defined
>
> thanks in advance
> Ian|||An additional point, BCP is the tool to use (not SSIS and not RS) if
performance is your goal. Give a DBA your query used by the report and they
could have BCP written and scripted for you in no time. It's been over a
decade since I used BCP and I had forgotten about it.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:eES$LTZPIHA.4272@.TK2MSFTNGP06.phx.gbl...
> Do not waste your time trying to run the report different ways, it will
> not affect the time.
> Your time is coming in two places. One, does it really only take 5 minutes
> to extract 5 million records? I'm not sure how it works when executing a
> stored procedure from Query Analyzer that returns 5 million records,
> whether it really retrieves all the records or not. Anyway, I think that
> is one area, the number of records being physically returned takes time.
> Second, RS 2000 and RS 2005 does all rendering in RAM. It does not page
> anything out to disk or utilize the disk at all when rendering. I am
> pretty sure this is changing substantially in RS 2008. Adding more RAM to
> the server will help.
> The other option is to use SSIS instead.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> <emery.ian@.googlemail.com> wrote in message
> news:e977052b-81b7-404f-88e1-d14968fbe0ab@.e25g2000prg.googlegroups.com...
>>I have a report that produces over 3 million rows, this report is
>> export to CSV and is scheduled to run weekly.
>> Sometimes the report is needed to run manually. Initially this report
>> timed out, now the report runs but takes a very long time to complete.
>> If the stored procedure is run directly it takes about 5 mins to
>> execute. Running in reporting services takes upward of an hour.
>> Does anyone have suggestions on how to speed up this report.
>> my initial thoughts would be to use SSRS to populate the parameters
>> and maybe hook into the reporting engine and execute the stored
>> procedure directly with the parameters defined
>>
>> thanks in advance
>> Ian
>

Data eplorer DBxtra

Just found a very nice tool for data exploring, filtering and report creation.
Connects to almost any database.
Can create very fast and complete reports.
You even can schedule reports.Yeah, it's called reporting services...|||Even if you could get DBxtra to work, it is quite lame compared to Reporting Services. DBxtra seems more like a sub-set of the more commonly used features from Crystal Reports.

-PatP|||Pat & Brett, I haven't had a chance to play around with reporting services. Do both of you recommend it?|||There's no majik bullet for reporting, but Reporting Services is very, very good. If you have only Windoze hosted (or at least ODBC accessible) tools to report from, Reporting Services is probably as good as you'll find.

-PatP|||Pat & Brett, I haven't had a chance to play around with reporting services. Do both of you recommend it?

Getting server support (that's their gig) to get anything done, that a vp is not smashing them over the head, is not on their radar...I'm still waiting..

BUT...if it's like everything else...you can build it better and more customiozable...think about it...take dynamic to the nth level...

That's all the f'n wizards do...|||Thanks, I'll check it out and see what it's all about.

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 Subscriptions

Hi !!
I want to create a scheduled delivery, with email & file share used together
with a single execution on a report. This means that i will sent the report
attachment thru an email to the recipient, and the same time copy that report
using the file share in a folder. Is this possible to implement?
This is because, I can only execute the report only once due to an updation
being done to the database.
Thanks - PeteSubscriptions only support sending through 1 delivery extension. Have you
looked into setting up the report to run on an Execution snapshot? This
would allow you to control when the report will run and all renderings of
the report will come off the snapshot and not the data. You could then
create two subscriptions running off either a shared schedule of when the
report execution snapshot is updated.
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"Pete" <Pete@.discussions.microsoft.com> wrote in message
news:36D77E8B-1768-44AA-955E-D459516C7D41@.microsoft.com...
> Hi !!
> I want to create a scheduled delivery, with email & file share used
together
> with a single execution on a report. This means that i will sent the
report
> attachment thru an email to the recipient, and the same time copy that
report
> using the file share in a folder. Is this possible to implement?
> This is because, I can only execute the report only once due to an
updation
> being done to the database.
> Thanks - Pete

Data driven subscription: if no report data => do not deliver

I am wondering if there is a simple way to tell the report server to not
deliver a report if there is no data in the report.
I have several reports which have table layout to display a set of data IF
there happens to be data. One example is that I have a polling process
looking at certain tables in SQL Server every 10 minutes. Is there a quick
way to not send me an e-mail if there isn't data to look at?
Thanks!
DavidCurrently there is not, although this feature has been requested several
times. The only way to do it now is to construct the Data Driven
Subscriptions query to return no rows when the report will return no rows.
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"david boardman" <davidboardman@.discussions.microsoft.com> wrote in message
news:8D692421-D6E1-4185-B66B-3E2356F59CB6@.microsoft.com...
>I am wondering if there is a simple way to tell the report server to not
> deliver a report if there is no data in the report.
> I have several reports which have table layout to display a set of data IF
> there happens to be data. One example is that I have a polling process
> looking at certain tables in SQL Server every 10 minutes. Is there a
> quick
> way to not send me an e-mail if there isn't data to look at?
> Thanks!
> David|||Hi,
I have a similar data-driven subscription. The first query determines the
receivers for email, the second query prepares the email content for the
corresponding receiver.
So as Daniel wrote, first I had to eliminate no-data records then run the
query for the remaining set.
"david boardman" wrote:
> I am wondering if there is a simple way to tell the report server to not
> deliver a report if there is no data in the report.
> I have several reports which have table layout to display a set of data IF
> there happens to be data. One example is that I have a polling process
> looking at certain tables in SQL Server every 10 minutes. Is there a quick
> way to not send me an e-mail if there isn't data to look at?
> Thanks!
> David|||I guess I can post my interim solution for those looking to at least have
something working. I just am looking for a more elegant/built in solution.
For data-driven subscriptions, RS requires you to specify an SQL query which
returns data to the subscription (things like what e-mails to send to, and
what parameters to pass to the actual report, if any). I exec a stor proc in
order to return this information back to my subscription. The key lies in
the stor proc being called.
The stor proc which feeds the data driven subscription ends up calling the
stor proc which returns data for the report (which in reality is completely
compartmentalized from the subscription itself). The subscription proc
checks to see if at least 1 row of data is returned by the report proc. If
the report stor proc returns no data, then the subscription stor proc returns
a NULL SET back for the e-mail addresses to deliver the report to.
I don't know if I'm capitalizing on a bug or not, but there is no error
generated in the log files. I guess the report is actually generated (with
no data) and delivered into never-never land.
Hope I explained what I'm doing clearly, but just wanted people to know
there is an interim solution, although some what cludgy.
Cheers!
"eralper" wrote:
> Hi,
> I have a similar data-driven subscription. The first query determines the
> receivers for email, the second query prepares the email content for the
> corresponding receiver.
> So as Daniel wrote, first I had to eliminate no-data records then run the
> query for the remaining set.
>
> "david boardman" wrote:
> > I am wondering if there is a simple way to tell the report server to not
> > deliver a report if there is no data in the report.
> >
> > I have several reports which have table layout to display a set of data IF
> > there happens to be data. One example is that I have a polling process
> > looking at certain tables in SQL Server every 10 minutes. Is there a quick
> > way to not send me an e-mail if there isn't data to look at?
> >
> > Thanks!
> > David|||This is by design from the perspective of RS. If the query used to generate
the notifications returns no rows then the report will not be run for that
instance of the subscription until the next time it fires and the query
returns data.
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"david boardman" <davidboardman@.discussions.microsoft.com> wrote in message
news:7855BBDB-18BB-4191-86A1-778126B1D049@.microsoft.com...
>I guess I can post my interim solution for those looking to at least have
> something working. I just am looking for a more elegant/built in
> solution.
> For data-driven subscriptions, RS requires you to specify an SQL query
> which
> returns data to the subscription (things like what e-mails to send to, and
> what parameters to pass to the actual report, if any). I exec a stor proc
> in
> order to return this information back to my subscription. The key lies in
> the stor proc being called.
> The stor proc which feeds the data driven subscription ends up calling the
> stor proc which returns data for the report (which in reality is
> completely
> compartmentalized from the subscription itself). The subscription proc
> checks to see if at least 1 row of data is returned by the report proc.
> If
> the report stor proc returns no data, then the subscription stor proc
> returns
> a NULL SET back for the e-mail addresses to deliver the report to.
> I don't know if I'm capitalizing on a bug or not, but there is no error
> generated in the log files. I guess the report is actually generated
> (with
> no data) and delivered into never-never land.
> Hope I explained what I'm doing clearly, but just wanted people to know
> there is an interim solution, although some what cludgy.
> Cheers!
> "eralper" wrote:
>> Hi,
>> I have a similar data-driven subscription. The first query determines the
>> receivers for email, the second query prepares the email content for the
>> corresponding receiver.
>> So as Daniel wrote, first I had to eliminate no-data records then run the
>> query for the remaining set.
>>
>> "david boardman" wrote:
>> > I am wondering if there is a simple way to tell the report server to
>> > not
>> > deliver a report if there is no data in the report.
>> >
>> > I have several reports which have table layout to display a set of data
>> > IF
>> > there happens to be data. One example is that I have a polling process
>> > looking at certain tables in SQL Server every 10 minutes. Is there a
>> > quick
>> > way to not send me an e-mail if there isn't data to look at?
>> >
>> > Thanks!
>> > David

data driven subscription with special charater in parameter

Hi all,
I have a report which takes in a name as the parameter. When I run the
report from the server manually using the name Monzón it works fine. But when
that same report runs as part of a data driven subscription, the report comes
out blank. The data source is a sql table.
Any ideas?
Thanks,
MarcusOn Oct 19, 4:05 pm, Marcus K <Marc...@.discussions.microsoft.com>
wrote:
> Hi all,
> I have a report which takes in a name as the parameter. When I run the
> report from the server manually using the name Monz=F3n it works fine. Bu=t when
> that same report runs as part of a data driven subscription, the report c=omes
> out blank. The data source is a sql table.
> Any ideas?
> Thanks,
> Marcus
You might want to check the collation on the database that the table
resides in. I don't believe that the default collation accommodates
espa=F1ol/etc. This link should help.
http://msdn2.microsoft.com/en-us/library/ms190920.aspx
Regards,
Enrique Martinez
Sr. Software Consultant|||On Oct 19, 4:05 pm, Marcus K <Marc...@.discussions.microsoft.com>
wrote:
> Hi all,
> I have a report which takes in a name as the parameter. When I run the
> report from the server manually using the name Monz=F3n it works fine. Bu=t when
> that same report runs as part of a data driven subscription, the report c=omes
> out blank. The data source is a sql table.
> Any ideas?
> Thanks,
> Marcus
You might want to change the collation of the column in the table that
contains Monz=F3n.
This link should help.
http://msdn2.microsoft.com/en-us/library/ms190920.aspx
Regards,
Enrique Martinez
Sr. Software Consultant|||The Collation is SQL_Latin1_General_CP1P_CI_AS, it accepts the ó and returns
it in a query.
BTW. This is not the same server that is running the SRS subscription.
This seems directly related to data driven subscriptions.
Marcus
"EMartinez" wrote:
> On Oct 19, 4:05 pm, Marcus K <Marc...@.discussions.microsoft.com>
> wrote:
> > Hi all,
> >
> > I have a report which takes in a name as the parameter. When I run the
> > report from the server manually using the name Monzón it works fine. But when
> > that same report runs as part of a data driven subscription, the report comes
> > out blank. The data source is a sql table.
> >
> > Any ideas?
> >
> > Thanks,
> > Marcus
>
> You might want to check the collation on the database that the table
> resides in. I don't believe that the default collation accommodates
> español/etc. This link should help.
> http://msdn2.microsoft.com/en-us/library/ms190920.aspx
> Regards,
> Enrique Martinez
> Sr. Software Consultant
>

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 problem - (Not able to see subcription tab in my report server)

Hi,
Can someone please help me in getting, Data Driven Subscription option in my Report Server.(Not able to see Subscription tab)

My Server Details
1) Operating System – Windows Server 2003, Enterprise Edition
2) SQl Server 2000 Reporting Services – Standard Edition
3) SQL Server 2000 – Enterprise Edition
4) Have Enabled Application Server, IIS, ASP, and Front Page Extensions
5) Visual Studio .net 2003

Appreciate your quick response. Please reply back.

Thanks
Mahesh

Mahesh N Raju wrote:

Hi,
Can someone please help me in getting, Data Driven Subscription option in my Report Server.(Not able to see Subscription tab)

My Server Details
1) Operating System – Windows Server 2003, Enterprise Edition
2) SQl Server 2000 Reporting Services – Standard Edition
3) SQL Server 2000 – Enterprise Edition
4) Have Enabled Application Server, IIS, ASP, and Front Page Extensions
5) Visual Studio .net 2003

Appreciate your quick response. Please reply back.

Thanks
Mahesh

data driven subscription not available

Hi,
I am trying to create a data driven subscription. I have stored the
credentials and custom data source in the report. My account has site
administrator and content manager permissions, yet the "New Data Driven
Subscription" button is not available. I have multiple reports like this.
Any ideas?
MarcusAre you on Enterprise Edition of Reporting Services, it's an EE only
feature.
--
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Marcus K" <MarcusK@.discussions.microsoft.com> wrote in message
news:86A68FE4-6761-44D0-89A1-8EADE9855583@.microsoft.com...
> Hi,
> I am trying to create a data driven subscription. I have stored the
> credentials and custom data source in the report. My account has site
> administrator and content manager permissions, yet the "New Data Driven
> Subscription" button is not available. I have multiple reports like this.
> Any ideas?
> Marcus|||That explains it... Thanks!!
"Jasper Smith" wrote:
> Are you on Enterprise Edition of Reporting Services, it's an EE only
> feature.
> --
> HTH
> Jasper Smith (SQL Server MVP)
> http://www.sqldbatips.com
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
> "Marcus K" <MarcusK@.discussions.microsoft.com> wrote in message
> news:86A68FE4-6761-44D0-89A1-8EADE9855583@.microsoft.com...
> > Hi,
> >
> > I am trying to create a data driven subscription. I have stored the
> > credentials and custom data source in the report. My account has site
> > administrator and content manager permissions, yet the "New Data Driven
> > Subscription" button is not available. I have multiple reports like this.
> >
> > Any ideas?
> >
> > Marcus
>
>|||That might explain my problem...where does it state that it's an EE only
option? The developer edition supports it as well (obviously I suppose) but I
hadn't realised it wouldn't work in production!!
"Jasper Smith" wrote:
> Are you on Enterprise Edition of Reporting Services, it's an EE only
> feature.
> --
> HTH
> Jasper Smith (SQL Server MVP)
> http://www.sqldbatips.com
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
> "Marcus K" <MarcusK@.discussions.microsoft.com> wrote in message
> news:86A68FE4-6761-44D0-89A1-8EADE9855583@.microsoft.com...
> > Hi,
> >
> > I am trying to create a data driven subscription. I have stored the
> > credentials and custom data source in the report. My account has site
> > administrator and content manager permissions, yet the "New Data Driven
> > Subscription" button is not available. I have multiple reports like this.
> >
> > Any ideas?
> >
> > Marcus
>
>|||If you go to the SQL Server site then navigate to the reporting services page
and then find the feature compairison it is listed there.
For us the only thing we need EE for is the data driven subscriptions and I
can't get the boss to come off the hip with 10x the money for that one
feature.
I am going to try and write some code to run reports from the reporting
server using .net and sql statements.
"Paul Hasell" wrote:
> That might explain my problem...where does it state that it's an EE only
> option? The developer edition supports it as well (obviously I suppose) but I
> hadn't realised it wouldn't work in production!!
> "Jasper Smith" wrote:
> > Are you on Enterprise Edition of Reporting Services, it's an EE only
> > feature.
> >
> > --
> > HTH
> >
> > Jasper Smith (SQL Server MVP)
> > http://www.sqldbatips.com
> > I support PASS - the definitive, global
> > community for SQL Server professionals -
> > http://www.sqlpass.org
> >
> > "Marcus K" <MarcusK@.discussions.microsoft.com> wrote in message
> > news:86A68FE4-6761-44D0-89A1-8EADE9855583@.microsoft.com...
> > > Hi,
> > >
> > > I am trying to create a data driven subscription. I have stored the
> > > credentials and custom data source in the report. My account has site
> > > administrator and content manager permissions, yet the "New Data Driven
> > > Subscription" button is not available. I have multiple reports like this.
> > >
> > > Any ideas?
> > >
> > > Marcus
> >
> >
> >

Data Driven Subscription Help

Hello all,

I am trying to schedule an MDX report to run once a week and it only works if I pass one parameter per field.It would fail every time I try to pass multiple parameters.Do you know if there is way to pass multiple MDX parameters?

The parameter field is set as multi-value field.

This worked –

'[Dim Travel Product].[Dim Travel Product].&[67]'

This failed –

'[Dim Travel Product].[Dim Travel Product].&[67], [Dim Travel Product].[Dim Travel Product].&[70], [Dim Travel Product].[Dim Travel Product].&[69]'

This failed -

'[Dim Travel Product].[Dim Travel Product].&[67]&[69]&[70]'

Could you post the MDX statement into which the parameters are being passed?

Thanks,
Bryan

|||

The parameter is passed to @.TravelProduct:

SET [Dim Travel Product Set] AS StrToSet(@.TravelProduct)

MEMBER [Dim Travel Product].[Dim Travel Product].[Travel Product Subset] AS 'Aggregate([Dim Travel Product Set])'

Thanks in advance!

|||It also works if i were to run this manually and select multiple parameters. The problem is I don't know the proper syntax of what's being passed when there are multiple selections.

|||

Enclose the list of members inside { } when passing multiple members.

|||

None of the below syntax works:

'{[Dim Travel Product].[Dim Travel Product].&[4]&[5]&[7]}'

'{[Dim Travel Product].[Dim Travel Product].&[4], [Dim Travel Product].[Dim Travel Product].&[5], [Dim Travel Product].[Dim Travel Product].&[7]}'

This is the method that I am using to pass the parameter:

So this is still working:

SELECT CONVERT(char(10),DateAdd(dd, -1 - (DatePart(dw, getdate()) - 2), getdate()),101) as Sunday,
'[Dim Travel Product].[Dim Travel Product].&[4]' as TravProd,
'[Dim Car Vendor].[Car Vendor Desc].[All]' as Vendor

This does not work:

SELECT CONVERT(char(10),DateAdd(dd, -1 - (DatePart(dw, getdate()) - 2), getdate()),101) as Sunday,
'{[Dim Travel Product].[Dim Travel Product].&[4], [Dim Travel Product].[Dim Travel Product].&[5], [Dim Travel Product].[Dim Travel Product].&[7]}' as TravProd,
'[Dim Car Vendor].[Car Vendor Desc].[All]' as Vendor

This does not work:

SELECT CONVERT(char(10),DateAdd(dd, -1 - (DatePart(dw, getdate()) - 2), getdate()),101) as Sunday,
'{[Dim Travel Product].[Dim Travel Product].&[4]&[5]&[7]}' as TravProd,
'[Dim Car Vendor].[Car Vendor Desc].[All]' as Vendor

|||

Take a look at this. It works against Adventure Works:

Code Snippet

select

[Date].[Date].[July 1, 2003] on 0,

strtoset("{[Product].[Category].[Bikes],[Product].[Category].[Clothing]}") on 1

from [Adventure Works]

B.|||

What I am trying to do is pass the @.TravelProduct from a Data Driven Subscription in Reporting Services and schedule it to run once a week. The select statement that I built contains all my filters, but when I add more than one filter for a particular field, it errors.

Thanks!

|||

What my code illustrates is how the MDX must be constructed to support multiple values from a parameter. You need to use STRTOSET, you need to wrap that comma delimited list of set members in curly braces, and that set string needs to be wrapped in double-quotes. If you've got all that in place, you can use a parameter in SSRS supply the set of members. The trick is getting your MDX in order to be able to work with the value from the parameter.

B.

|||

Thanks! I'll try that later today and report back.

|||

I tried what Bryan did above but it is still not working. ;(

sql