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 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 for SQL Server Reporting Services

Is there any other then "Data Extension process" way to manipulate a Dataset ?
Is it a good idea to edit dataset from SQL server using "Data Extension
process" ?
Thanks, JoshWhat is it you are trying to do?
I would stay away from a data extension unless you absolutely have no other
way of solving the problem. It is non-trivial plus it will be unneccesary
when version 2 comes out (probably late summer). Version 2 will have both a
web form and winform control that you can pass a dataset to.
I have found that usually people can get what they need done by using a
stored procedure.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Josh T" <Josh T@.discussions.microsoft.com> wrote in message
news:446B51BE-F0A8-443A-8BD1-DDE651998272@.microsoft.com...
> Is there any other then "Data Extension process" way to manipulate a
> Dataset ?
> Is it a good idea to edit dataset from SQL server using "Data Extension
> process" ?
> Thanks, Josh|||Thanks Bruce!
We are trying to solve a conceptual problem: is possible to add a business
layer to a dataset, although it was received from SQL server, before it'll be
processed by Report Server e.g. how we can manipulate dataset before it gets
into report.
Best regards, Ilya
"Bruce L-C [MVP]" wrote:
> What is it you are trying to do?
> I would stay away from a data extension unless you absolutely have no other
> way of solving the problem. It is non-trivial plus it will be unneccesary
> when version 2 comes out (probably late summer). Version 2 will have both a
> web form and winform control that you can pass a dataset to.
> I have found that usually people can get what they need done by using a
> stored procedure.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Josh T" <Josh T@.discussions.microsoft.com> wrote in message
> news:446B51BE-F0A8-443A-8BD1-DDE651998272@.microsoft.com...
> > Is there any other then "Data Extension process" way to manipulate a
> > Dataset ?
> > Is it a good idea to edit dataset from SQL server using "Data Extension
> > process" ?
> >
> > Thanks, Josh
>
>|||I've been struggling with this same question. The data extension IS daunting
(maybe not so bad for .Net experts). In my web-app, I call a SQL sproc
(passing lots of parms from form data). With the resulting ADO.Net dataset, I
want to display a report in .PDF format. I've been struggling with all the
mechanics of capturing a model representation of the data as an XML file,
creating an XSD file from that, using the XSD file in the Report Designer.
Preview does not work for me unless I strip a bit of the XML file into the
Designer (I actually have to type it in; PASTE after COPY only puts in one
element - weird?). Then I deploy the RDL. When I run the app it works until I
get to the .RENDER method when it fails saying "item not found" regarding my
reportPath parameter.
In short, I agree this is fraught with problems. How can I accomplish the
display of a PDF report with a stored procedure?
--
John
"Josh T" wrote:
> Thanks Bruce!
> We are trying to solve a conceptual problem: is possible to add a business
> layer to a dataset, although it was received from SQL server, before it'll be
> processed by Report Server e.g. how we can manipulate dataset before it gets
> into report.
> Best regards, Ilya
> "Bruce L-C [MVP]" wrote:
> > What is it you are trying to do?
> >
> > I would stay away from a data extension unless you absolutely have no other
> > way of solving the problem. It is non-trivial plus it will be unneccesary
> > when version 2 comes out (probably late summer). Version 2 will have both a
> > web form and winform control that you can pass a dataset to.
> >
> > I have found that usually people can get what they need done by using a
> > stored procedure.
> >
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> > "Josh T" <Josh T@.discussions.microsoft.com> wrote in message
> > news:446B51BE-F0A8-443A-8BD1-DDE651998272@.microsoft.com...
> > > Is there any other then "Data Extension process" way to manipulate a
> > > Dataset ?
> > > Is it a good idea to edit dataset from SQL server using "Data Extension
> > > process" ?
> > >
> > > Thanks, Josh
> >
> >
> >|||Could someone notice my post dated 5-12-2005 and help me out with an answer
or comment? Thanks.
--
John
"Bruce L-C [MVP]" wrote:
> What is it you are trying to do?
> I would stay away from a data extension unless you absolutely have no other
> way of solving the problem. It is non-trivial plus it will be unneccesary
> when version 2 comes out (probably late summer). Version 2 will have both a
> web form and winform control that you can pass a dataset to.
> I have found that usually people can get what they need done by using a
> stored procedure.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Josh T" <Josh T@.discussions.microsoft.com> wrote in message
> news:446B51BE-F0A8-443A-8BD1-DDE651998272@.microsoft.com...
> > Is there any other then "Data Extension process" way to manipulate a
> > Dataset ?
> > Is it a good idea to edit dataset from SQL server using "Data Extension
> > process" ?
> >
> > Thanks, Josh
>
>|||You need to rethink this. You are making it wayyy more complicated than it
needs to be. From your web app you use either URL integration or Web
services. URL integration is easier. You call the report. The report uses
the stored procedure as its data source. When you call the report you
specify that it render it as PDF. The report has report parameters that
your web apps specifies when it calls the report.
Note, first get the report working prior to integrating with your app. I
usually first hard code the parameters to the stored procedure then I take
out the hard coded values and make the query parameters. When you create a
query parameter RS automatically (usually) creates the report parameter for
you.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"jjamjatra" <johna@.cbmiweb.donotspam.com> wrote in message
news:F30020E9-02FE-435C-94A1-CAC2199A93AC@.microsoft.com...
> I've been struggling with this same question. The data extension IS
> daunting
> (maybe not so bad for .Net experts). In my web-app, I call a SQL sproc
> (passing lots of parms from form data). With the resulting ADO.Net
> dataset, I
> want to display a report in .PDF format. I've been struggling with all the
> mechanics of capturing a model representation of the data as an XML file,
> creating an XSD file from that, using the XSD file in the Report Designer.
> Preview does not work for me unless I strip a bit of the XML file into the
> Designer (I actually have to type it in; PASTE after COPY only puts in one
> element - weird?). Then I deploy the RDL. When I run the app it works
> until I
> get to the .RENDER method when it fails saying "item not found" regarding
> my
> reportPath parameter.
> In short, I agree this is fraught with problems. How can I accomplish the
> display of a PDF report with a stored procedure?
> --
> John
>
> "Josh T" wrote:
>> Thanks Bruce!
>> We are trying to solve a conceptual problem: is possible to add a
>> business
>> layer to a dataset, although it was received from SQL server, before
>> it'll be
>> processed by Report Server e.g. how we can manipulate dataset before it
>> gets
>> into report.
>> Best regards, Ilya
>> "Bruce L-C [MVP]" wrote:
>> > What is it you are trying to do?
>> >
>> > I would stay away from a data extension unless you absolutely have no
>> > other
>> > way of solving the problem. It is non-trivial plus it will be
>> > unneccesary
>> > when version 2 comes out (probably late summer). Version 2 will have
>> > both a
>> > web form and winform control that you can pass a dataset to.
>> >
>> > I have found that usually people can get what they need done by using a
>> > stored procedure.
>> >
>> >
>> > --
>> > Bruce Loehle-Conger
>> > MVP SQL Server Reporting Services
>> >
>> > "Josh T" <Josh T@.discussions.microsoft.com> wrote in message
>> > news:446B51BE-F0A8-443A-8BD1-DDE651998272@.microsoft.com...
>> > > Is there any other then "Data Extension process" way to manipulate a
>> > > Dataset ?
>> > > Is it a good idea to edit dataset from SQL server using "Data
>> > > Extension
>> > > process" ?
>> > >
>> > > Thanks, Josh
>> >
>> >
>> >|||--
John
"Bruce L-C [MVP]" wrote:
> You need to rethink this. You are making it wayyy more complicated than it
> needs to be. From your web app you use either URL integration or Web
> services. URL integration is easier. You call the report. The report uses
> the stored procedure as its data source. When you call the report you
> specify that it render it as PDF. The report has report parameters that
> your web apps specifies when it calls the report.
> Note, first get the report working prior to integrating with your app. I
> usually first hard code the parameters to the stored procedure then I take
> out the hard coded values and make the query parameters. When you create a
> query parameter RS automatically (usually) creates the report parameter for
> you.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "jjamjatra" <johna@.cbmiweb.donotspam.com> wrote in message
> news:F30020E9-02FE-435C-94A1-CAC2199A93AC@.microsoft.com...
> > I've been struggling with this same question. The data extension IS
> > daunting
> > (maybe not so bad for .Net experts). In my web-app, I call a SQL sproc
> > (passing lots of parms from form data). With the resulting ADO.Net
> > dataset, I
> > want to display a report in .PDF format. I've been struggling with all the
> > mechanics of capturing a model representation of the data as an XML file,
> > creating an XSD file from that, using the XSD file in the Report Designer.
> > Preview does not work for me unless I strip a bit of the XML file into the
> > Designer (I actually have to type it in; PASTE after COPY only puts in one
> > element - weird?). Then I deploy the RDL. When I run the app it works
> > until I
> > get to the .RENDER method when it fails saying "item not found" regarding
> > my
> > reportPath parameter.
> >
> > In short, I agree this is fraught with problems. How can I accomplish the
> > display of a PDF report with a stored procedure?
> > --
> > John
> >
> >
> > "Josh T" wrote:
> >
> >> Thanks Bruce!
> >> We are trying to solve a conceptual problem: is possible to add a
> >> business
> >> layer to a dataset, although it was received from SQL server, before
> >> it'll be
> >> processed by Report Server e.g. how we can manipulate dataset before it
> >> gets
> >> into report.
> >>
> >> Best regards, Ilya
> >>
> >> "Bruce L-C [MVP]" wrote:
> >>
> >> > What is it you are trying to do?
> >> >
> >> > I would stay away from a data extension unless you absolutely have no
> >> > other
> >> > way of solving the problem. It is non-trivial plus it will be
> >> > unneccesary
> >> > when version 2 comes out (probably late summer). Version 2 will have
> >> > both a
> >> > web form and winform control that you can pass a dataset to.
> >> >
> >> > I have found that usually people can get what they need done by using a
> >> > stored procedure.
> >> >
> >> >
> >> > --
> >> > Bruce Loehle-Conger
> >> > MVP SQL Server Reporting Services
> >> >
> >> > "Josh T" <Josh T@.discussions.microsoft.com> wrote in message
> >> > news:446B51BE-F0A8-443A-8BD1-DDE651998272@.microsoft.com...
> >> > > Is there any other then "Data Extension process" way to manipulate a
> >> > > Dataset ?
> >> > > Is it a good idea to edit dataset from SQL server using "Data
> >> > > Extension
> >> > > process" ?
> >> > >
> >> > > Thanks, Josh
> >> >
> >> >
> >> >
>
>

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 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 unavailable, please help

I'm running sql2000 Standard with SP4 applied. I also have Reporting Services 2000 with service packs running on the same machine. I'm trying to get a data-driven subscription, but the button to access is unavailable.

I've assigned the account I'm working under the Publisher role and have modified this role to have all permissions (including managing subscriptions). I have stored credentials with my data source as well.

I can create a standard subscription with no problems, but just don't get the button to do the data-driven option.

Any assistance is appreciated.

You are running RS 2000 Standard Edition. Data driven subscriptions are only available on Enterprise Edition and Developer Edition.

The same applies to data driven subscriptions on RS 2005: http://www.microsoft.com/sql/technologies/reporting/rsfeatures.mspx

-- Robert

|||Is it possible to run the enterprise edition of Reporting Services on the Standard edition of SQL 2000?|||

Actually, you can run a Reporting Services Enterprise Edition with its metadata store on a SQL Server Standard Edition.

-- Robert

|||We have the same issue and we are using the enterprise version. The user who is trying to set-up the data-driven subscription is also an admin on the report server settings...|||

Does anybody know the license implications if you install reporting services 2000 enterprise on a sql server 2000 standard install?

|||Does anybody know the license implications if you install reporting services 2000 enterprise on a sql server 2000 standard install?

Data Driven Subscriptions unavailable, please help

I'm running sql2000 Standard with SP4 applied. I also have Reporting Services 2000 with service packs running on the same machine. I'm trying to get a data-driven subscription, but the button to access is unavailable.

I've assigned the account I'm working under the Publisher role and have modified this role to have all permissions (including managing subscriptions). I have stored credentials with my data source as well.

I can create a standard subscription with no problems, but just don't get the button to do the data-driven option.

Any assistance is appreciated.

You are running RS 2000 Standard Edition. Data driven subscriptions are only available on Enterprise Edition and Developer Edition.

The same applies to data driven subscriptions on RS 2005: http://www.microsoft.com/sql/technologies/reporting/rsfeatures.mspx

-- Robert

|||Is it possible to run the enterprise edition of Reporting Services on the Standard edition of SQL 2000?|||

Actually, you can run a Reporting Services Enterprise Edition with its metadata store on a SQL Server Standard Edition.

-- Robert

|||We have the same issue and we are using the enterprise version. The user who is trying to set-up the data-driven subscription is also an admin on the report server settings...|||

Does anybody know the license implications if you install reporting services 2000 enterprise on a sql server 2000 standard install?

|||Does anybody know the license implications if you install reporting services 2000 enterprise on a sql server 2000 standard install?sql

Data Driven Subscriptions unavailable, please help

I'm running sql2000 Standard with SP4 applied. I also have Reporting Services 2000 with service packs running on the same machine. I'm trying to get a data-driven subscription, but the button to access is unavailable.

I've assigned the account I'm working under the Publisher role and have modified this role to have all permissions (including managing subscriptions). I have stored credentials with my data source as well.

I can create a standard subscription with no problems, but just don't get the button to do the data-driven option.

Any assistance is appreciated.

You are running RS 2000 Standard Edition. Data driven subscriptions are only available on Enterprise Edition and Developer Edition.

The same applies to data driven subscriptions on RS 2005: http://www.microsoft.com/sql/technologies/reporting/rsfeatures.mspx

-- Robert

|||Is it possible to run the enterprise edition of Reporting Services on the Standard edition of SQL 2000?|||

Actually, you can run a Reporting Services Enterprise Edition with its metadata store on a SQL Server Standard Edition.

-- Robert

|||We have the same issue and we are using the enterprise version. The user who is trying to set-up the data-driven subscription is also an admin on the report server settings...|||

Does anybody know the license implications if you install reporting services 2000 enterprise on a sql server 2000 standard install?

|||Does anybody know the license implications if you install reporting services 2000 enterprise on a sql server 2000 standard install?

Data Driven Subscriptions in 2005 - which versions?

Hi,
Does anyone know which version(s) of SQL Server 2005/reporting services will
support data driven subscriptions?
Only Enterprise (or Developer) with SQL Server 2000 which is very limiting!
Cheers,
--
BenOn Thu, 7 Apr 2005 09:11:05 -0700, "Ben S"
<BenS@.discussions.microsoft.com> wrote:
>Hi,
>Does anyone know which version(s) of SQL Server 2005/reporting services will
>support data driven subscriptions?
>Only Enterprise (or Developer) with SQL Server 2000 which is very limiting!
>Cheers,
Ben,
Only Enterprise according to
http://www.microsoft.com/sql/2005/productinfo/sql2005features.asp.
Questions about SQL Server 2005 Reporting Services are probably best
asked on microsoft.private.sqlserver2005.reportingsvcs, which is a
public beta newsgroup, despite the name.
Andrew Watt
MVP - InfoPath|||Thanks Andrew (I was afraid of that!)
"Andrew Watt [MVP - InfoPath]" wrote:
> On Thu, 7 Apr 2005 09:11:05 -0700, "Ben S"
> <BenS@.discussions.microsoft.com> wrote:
> >Hi,
> >
> >Does anyone know which version(s) of SQL Server 2005/reporting services will
> >support data driven subscriptions?
> >Only Enterprise (or Developer) with SQL Server 2000 which is very limiting!
> >
> >Cheers,
> Ben,
> Only Enterprise according to
> http://www.microsoft.com/sql/2005/productinfo/sql2005features.asp.
> Questions about SQL Server 2005 Reporting Services are probably best
> asked on microsoft.private.sqlserver2005.reportingsvcs, which is a
> public beta newsgroup, despite the name.
> Andrew Watt
> MVP - InfoPath
>

Data Driven Subscriptions

Hello,
I have an installation of Reporting Services with SQL
Server 7.0 SP3. One of my reports is a data driven
subscription and it worked wonderfully.
Then, I applied Reporting Services Service Pack 1 and now
the data driven subscription does not work.
Any ideas?
Thanks,
DaveSorry, SQL Server 2000 SP3
>--Original Message--
>Hello,
>I have an installation of Reporting Services with SQL
>Server 7.0 SP3. One of my reports is a data driven
>subscription and it worked wonderfully.
>Then, I applied Reporting Services Service Pack 1 and now
>the data driven subscription does not work.
>Any ideas?
>Thanks,
>Dave
>.
>

Data Driven Subscription or Notification Services or both

I have read all the latest data driven subscription info and seen posts
referring people to use Notification Services in some instances. Here is my
question:
I want to automatically send an email with an attached SQL Reporting
SErvices Report to a client if they submit a new order request. The client
submitts an order request that generates a new record in an SQL database that
has a Reporting Services Report that can pull up that order/invoice. I want
to automatically send that user an email with the attached report or the
report right in the email.
Can I do this with a data driven ubscription or with Notification Services?
Can I set up Notification services to send out the SQL Reporting Serives
Report based on the new record?
If I need to be in another group please elt me know.
I can do this in either SQL 2000 or 2005 depending on your answers.
thanks in advance for your assistance.
--
Jeanne Conde, MCPHi Jeanne,
My suggestion is to use trigger that calls RS to produce and send
report to the user when new record is inserted.
I am not sure wether it is really related to data driven subscription
at all. it would be more like single report for each new record event.
It will work for any SQL versions.
Hope this helped.
cheers,
ian , MCSE NT4.0sql

Data Display in Reports based on windows Authentication ID

Hi Experts,

I have a reporting scenario, where the reports are fetched from Analysis Services.

The reports should display data only spcecific to that user.

All users except those in admin roles should be validated using the Windows Authentication ID and data specific to them has to be displayed.

Any pointers/suggestions on how to implement this in Reporting services/ Analysis Services 2005 would be highly appreciated

Thanks,

Hi,

Easier said than done. I guess that the easier path would be to use data filtering based on parameters values limited by userid.

It depends, if you access cubes directly as well, i.e. from Excel, then that would be different. You would have to create custom MDX dimension access formulas based on roles and map these roles to Windows groups.

Assuming you are only going after AS data from RS, then you would still need to create an access policy.

i.e. you can segregate access to data slices based on membership to Windows groups.

We have found that using cumulative membership to groups was the most flexible and did not require too many groups.

You have to find a way to load LDAP (or Windows AD groups and membership) data, then each time an user access the report, you use the userid info to check what is the acceptable list of values for your parameters.

This way, a given user will be able to see and select only parameters values acceptable for her job function.

That is quite a lot of code, functions and procedures to write, no quick fix.

First thing first, you need the AD or LDAP data, there are quite a few ways to get it. I remember that SQL Server magazine ran an article a few month ago about getting it with a SSIS package.

You can also settle for another easier way, like calling Exporter Pro from a Windows scheduled task. Exporter Pro is part of the Hyena software.

Your question is not the type of question where a quick and precise answer is available. I can only give some high level clues. The rest would take time and require insider knowledge of your situation and requirements.

Hope it helps somehow

Philippe

Data Cubes using MS Analysis Services

I need to use cubes in VS 2005 web project. I have no clue how to create cubes or use them in my web page. I am familiar with Reporting Services but not cubes.

thanks in advance!

In SQL Server 2005 it depends on where your data is comming from, if you have a Data Warehouse running you can create the Cubes in Analysis Service but SQL Server 2005 also comes with UDM(unified dimension modeling) stripping the Algebra and moving to calculus in one step, some thing you need tools like Hyperion to do in SQL Server 2000. Try the link below for some SQL Server 2005 Analysis resources including UDM. Hope this helps.

http://www.mosha.com/msolap/yukon.htm

|||Thanks Caddre. I am sure I will find lots of info there. I also found this link: http://www.microsoft.com/technet/prodtechnol/sql/default.mspx.|||

Hi,

I have created a cube using MS Analysis Services 2005. I have a fact table that contains 6 different measures (actualUSrevenue, actualEURrevenue,Qty) and (scheduledUSrevenue,scheduledEURrevenue,scheduledQty). How do I break these up into two separate groups of measures.

I will be taking these separate measure groups to build a stacked column chart with both actual and scheduled in the same column but each in a separate series.

thanks,

marilyn

marilyn.beaudreau@.philips.com

|||

There is very good sample code from Microsoft in the file below and the second link is a tutorial to get you started. Hope this helps.

http://www.microsoft.com/downloads/details.aspx?FamilyID=790d631b-bff9-4f4a-b648-e9209e6ac8ad&DisplayLang=en

http://www.databasejournal.com/article.php/1459531

Data Conversion supported on Standard Edition?

I created an Integration Services Package that runs fine from my local computer using BIDS. However when I imported into our SQL Server and try to run it from there I get the following error:

DTS_E_PRODUCTLEVELTOLOW. The product level is insufficient for component "Data Conversion"

We are running SQL Server 2005 Standard Edition 64-bit.

We have integration services installed on the server. Is data conversion something that is not supported on Standard Edition?

Also have a similar message for "Send Mail Task."

Is there anywhere that outlines what features are supported on each version?

What version of SQL Server are you running on your developer's workstation?|||

I'm running BIDS on my workstation which is connecting to our SQL Server. The same SQL Server where the integration package will not run from when imported into. So I'm actually not running a full blown SQL Server database on my workstation, just using BIDS on it along with Studio.

The Management Studio on my workstation is 9.00.3042.

SQL Server Integration Services is 9.00.3042. (Found by going to About > Help in Visual Studio)

Our SQL Server version is 9.00.3050

|||So both your workstation and the server are running SQL Server Standard edition? Not developer/enterprise edition?|||

Server is definitely Standard Edition. When I installed the workstation components on my workstation I honestly don't remember if I installed them from the Standard edition or Developer edition. Most likely I installed the workstation components using the Developer edition. Is there a way to check?

I guess that would explain why it works on my workstation but not on the server?

|||

Erikk Ross wrote:

Server is definitely Standard Edition. When I installed the workstation components on my workstation I honestly don't remember if I installed them from the Standard edition or Developer edition. Most likely I installed the workstation components using the Developer edition. Is there a way to check?

Run this query on the server and again on your local version:

SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition')|||

Yeah, like I said I don't have the actual Database engine installed on my workstation. But our server version is SP2: 9.00.3050.

So I guess Data Conversion is not supported in Standard Edition? Or send mail? Is there a matrix somewhere that shows what features in Integration Services are supported in each version of SQL Server? It would seem to me that data conversion is something that is pretty common...I'm a little suprised it would require the Enterprise edition.

|||Alright, well, I do think it's because you're developing a package in a Developer environment, which is a higher level than the Standard edition that your server runs. If your server was an Enterprise version, you wouldn't have a problem (of course).

Uninstall and reinstall the SSIS components from the Standard Edition CDs and you should be fine. The Data Conversion component should work in the Standard Edition.|||

Phil Brammer wrote:

Alright, well, I do think it's because you're developing a package in a Developer environment, which is a higher level than the Standard edition that your server runs. If your server was an Enterprise version, you wouldn't have a problem (of course).

Uninstall and reinstall the SSIS components from the Standard Edition CDs and you should be fine. The Data Conversion component should work in the Standard Edition.

That was indeed the solution. I just did a quick test and creating the package from a Standard edition version did allow the data conversion to work correctly. Thank you!!

|||

Well, after uninstalling SQL Server workstation components on my development machine and reinstalling the Standard version it did not fix my problem. Any package created on my local development machine still does not work when imported into SQL Server. Get the same Product level to low error.

At least the good news is that I can create packages on the SQL Server itself and they seem to work fine. My best guess is that uninstalling the developer edition and reinstalling the standard edition just wasn't enough. I would suspect that if I was to completely wipe my machine clean then install Standard Edition it would probably be ok. But that is more hassle than it's worth.

|||

Erikk Ross wrote:

Well, after uninstalling SQL Server workstation components on my development machine and reinstalling the Standard version it did not fix my problem. Any package created on my local development machine still does not work when imported into SQL Server. Get the same Product level to low error.

At least the good news is that I can create packages on the SQL Server itself and they seem to work fine. My best guess is that uninstalling the developer edition and reinstalling the standard edition just wasn't enough. I would suspect that if I was to completely wipe my machine clean then install Standard Edition it would probably be ok. But that is more hassle than it's worth.

Any NEW packages created still don't work?|||

Correct, I created a brand new package. Well I first tried to rebuild my old package but that didn't work either, so I just created a new one. Same problems, runs fine on my local workstation, but when imported into SQL Server gives the same error.

I am currently installing standard edition workstation components on a new workstation with Visual Studio and will try to create a package from there and import it and see if that works.

|||Run this on the server, please:
SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition')|||

Phil Brammer wrote:

Run this on the server, please:
SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition')

9.00.3050.00 SP2 Standard Edition (64-bit)

Well now I am completely lost. I just installed the standard edition workstation components on a brand new machine. A machine that has never had SQL Server installed on it. I created a new package in Visual Studio, imported it into our SQL Server, ran it, and still get the same ProductLevelToLow errors.

Error: SSIS Error Code DTS_E_PRODUCTLEVELTOLOW. The product level is insufficient for component "Data Conversion".

|||

After reading this I realized my problem: http://blogs.msdn.com/michen/archive/2006/08/11/package-exec-location.aspx

I was running the package from my workstation using Management Studio, unaware that the package was actually running on my local workstation and not on the server. Since it was running on my workstation it required Integration Services to be installed which it wasn't, so that is why I got those error messages. As soon as I ran it directly from the server it worked fine.

*sigh* I wish this was more obvious. I thought that by running it in Management Studio it was just automatically running it on the server.

dashboard in/with reporting services

A consultant asked if If I knew how to 'intergrate" dashboard into
reporting services.
I don't know what it is. can someone please explain.
Thanks
KenDashboard is a user interface that organizes and presents information which
is easy to read, basically it contains graphs, tables and drill down reports
in a single page. Yes it is possible to do in SSRS. Infact I have created
dashboard using SSRS and integrated in SPS using web parts..
Amarnath
"SQLKen" wrote:
> A consultant asked if If I knew how to 'intergrate" dashboard into
> reporting services.
> I don't know what it is. can someone please explain.
> Thanks
> Ken
>|||Hi Amarnath,
I am currently killing myself to finish a BI project. I could find a
dashboard creating tool in the Microsoft environment. I have tried scorecard
creator (BSM), and the SQL BI- report services. Nothing works like Cognos or
Business Objects tools (xcelcius). I will appreciate if you could share some
info on how you build the dashboard.
As a last resource I am trying to display five images against records in the
SSRS matrix. Since I am new to the Report Services, it could make it work
either.
Your information will be highly appreciated,
KG
"Amarnath" wrote:
> Dashboard is a user interface that organizes and presents information which
> is easy to read, basically it contains graphs, tables and drill down reports
> in a single page. Yes it is possible to do in SSRS. Infact I have created
> dashboard using SSRS and integrated in SPS using web parts..
> Amarnath
> "SQLKen" wrote:
> > A consultant asked if If I knew how to 'intergrate" dashboard into
> > reporting services.
> > I don't know what it is. can someone please explain.
> >
> > Thanks
> >
> > Ken
> >
> >

Dashboard Guage type graph?

Hi,
I am on RS sp1, I have developed several reports bases on Analysis Services
cubes. I use in the reports several pie, bar, etc charts. I would like to
know if there is a Dashboard type Guage chart ( sort of like a speedometer
in the dashboard of one's car) in the RS graphicas library, maybe not built
in but can be added to the RS existing Graphics library, where I can use it
seemlesly as if it wa part of the product. If an addition exists, how do I
get it and what is the procedure to add it in.
Thank you in advance for your help.
--
Message posted via http://www.sqlmonster.comwould't that be nice. The extensions RS are very expensive. Every executive
now wants some sort of analog guage to display the overall aggregates of
their metrics at different levels of the enterprise.
--
Message posted via http://www.sqlmonster.com|||Here is an idea:
You could try creating a background image that gives the appearance of
the dashboard, then use a pie-chart with 2 values to act as the guage.
If you place it just right, you may be able to simulate the "Guage"
appearance you want.
Good luck!
~Lance Hunt
http://weblogs.asp.net/lhunt/

Dash boards with Reporting Services

Hi All,

I have been working on Reporting Services for quite some time but not able to find a way to create dash boards.

I hereby request you to tell me a way to do that.

An early reply from you help solve my problem.

Thanks

SPC

On which platform ? Plain HTML / ASP.NET / Sharepoint ?

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||Moved to Reporting Services.

Daniel Reib, Please Help with installation error

On one particular server, when installing Reporting Services I keep getting
"Failure Compiling WMI registration file"
What is the problem and how can I resolve it.
Thank you.
--Hmm, not really sure what would cause this. Can you run setup, when it
fails, but before you cancel, run mofcomp on reportingservices.mof. Perhaps
this will give a more detailed error message.
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"Richard Cranium" <mgreco40@.hotmail.com> wrote in message
news:eeDqOZBnEHA.324@.TK2MSFTNGP11.phx.gbl...
> On one particular server, when installing Reporting Services I keep
getting
> "Failure Compiling WMI registration file"
> What is the problem and how can I resolve it.
> Thank you.
> --
>
>

Damaged PDF files

Hi,
I have created a web application with ASP.Net 2.0. I also have created a
report using Reporting Services 2005. After showing the report on the
screen, I give the suer the possibility to export to report. One of the
possibilities is PDF. Now we have some troubles with this export.
On some PC's after saving or opening the PDF we got the message :
"There was an error opening this document. The file is damaged and could
not be repaired"
It only appears on some PC's, on other PC's exporting the same documents
works fine. It also didn't have any effect on the version of Acrobat. The
problems comes with Acro v 5, 7 and 8. Even after upgrading, deinstalling,
rebooting, installing a newer version the problem still exists on those PC's.
So I hope that someone have a solution for this as we don't know if it's a
problem with reporting services or with acrobat.
Txs in advance.On Apr 30, 4:36 am, Cipidos <Cipi...@.discussions.microsoft.com> wrote:
> Hi,
> I have created a web application with ASP.Net 2.0. I also have created a
> report using Reporting Services 2005. After showing the report on the
> screen, I give the suer the possibility to export to report. One of the
> possibilities is PDF. Now we have some troubles with this export.
> On some PC's after saving or opening the PDF we got the message :
> "There was an error opening this document. The file is damaged and could
> not be repaired"
> It only appears on some PC's, on other PC's exporting the same documents
> works fine. It also didn't have any effect on the version of Acrobat. The
> problems comes with Acro v 5, 7 and 8. Even after upgrading, deinstalling,
> rebooting, installing a newer version the problem still exists on those PC's.
> So I hope that someone have a solution for this as we don't know if it's a
> problem with reporting services or with acrobat.
> Txs in advance.
I've never seen this occur before; however, from what you are saying,
it seems that it is a non-Reporting Services issue. Traditionally, if
it was SSRS, then all of the machines would be behaving the same way.
It may not be related to Acrobat either; however, I'm not sure what
could be causing the problem (missing Adobe update, missing MS Office
update, etc). Sorry that I could not be of further assistance.
Regards,
Enrique Martinez
Sr. Software Consultant