Datasource error

Hi, i've a problem with calling a report created with reporting service; i get this error :System.Web.Services.Protocols.SoapException : Impossible find datasource...... ( i translate from italian ) I'm sure ip i wrote is right , and , moreover, any ip datasource i put ,i get the same error. Thank you in advance for your help.

Hi,

From your description, it seems that you are using WebService as your datasource of your report, right?

And the error "System.Web.Services.Protocols.SoapException" indicates that the WebService failed to return the result which your report expected.

So I suggest that you should try to check your WebService to see if it can work correctly, we suppose that it returns a dataset typed datasource, then you can just write a simple project, remove the reporting service part, try to see if you can retrieve the return properly.

Thanks.

|||Hi Nai-Dong,
I use the webservice as result, distribuiting report I've developed with SQL Server Development intelligence studio.
I'm doing this for the first time , I'm studying how to use SQLServer reporting service.
The webservice "seems" to me that works properly because I see exposed all the methods, and moreover ,
calling the other methods that don't involve datasource , the report is correctly called.
I'm using XP .
Thank you.|||

Hi,

The webservice "seems" to me that works properly because I see exposed all the methods, and moreover , calling the other methods that don't involve datasource , the report is correctly called.

Well, if so. I think you should try to check if the database is available from the machine which host the web service. Another problem is the account. You should try to check if the account which runs the webservice exist in the web service identity of the report server. Also, try to check if the account which runs the webservice (ASPNET or IUSR_MachineName) has to permission to access the reporting service database. If not, add the account into the users of that database.

Besides, I suggest you to refer the following link, it may be helpful to you.

http://msdn2.microsoft.com/en-us/library/aa179578(SQL.80).aspx

Thanks.

DataSource and more than one recordset

I've a report that use a stored procedure as datasource.
This s.p. return 2 recordset. How to access the second recordset in
reporting services? (like Recordset.NextRecordset in VB)
--
OSVALDO COLITTIRS does not support more than one recordset being returned.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Osvaldo Colitti" <OsvaldoColitti@.discussions.microsoft.com> wrote in
message news:14D1D808-F9CE-40ED-8493-CD8B77055E69@.microsoft.com...
> I've a report that use a stored procedure as datasource.
> This s.p. return 2 recordset. How to access the second recordset in
> reporting services? (like Recordset.NextRecordset in VB)
> --
> OSVALDO COLITTI
>|||Bruce is correct in that the native Data Processing Extensions do not
support stored procedures that return more than one recordset - what happens
is that the first recordset is all that your Reporting Services DataSet will
see.
However, you can certainly create a custom Data Processing Extension, and
could then gain access to the other recordsets returned by a stored
procedure in a report. Its not optimal and the best way forward is usually
to re-design the report logic if this is at all possible. Typically what you
need to do in your Data Processing Extension is archive off the recordsets
into a tables that you can then pull them down from seperately for other
Report DataSets in the Report.
Peter Blackburn
Windows Server Systems - SQL Server MVP
Hitchhiker's Guide to SQL Server Reporting Services
http://www.sqlreportingservices.net
"Osvaldo Colitti" <OsvaldoColitti@.discussions.microsoft.com> wrote in
message news:14D1D808-F9CE-40ED-8493-CD8B77055E69@.microsoft.com...
> I've a report that use a stored procedure as datasource.
> This s.p. return 2 recordset. How to access the second recordset in
> reporting services? (like Recordset.NextRecordset in VB)
> --
> OSVALDO COLITTI
>

Datasource and Catalog

Hi all,

I accidentally deleted the datasource including the root data source and catalog from reporting services running in SQL server 2005. As a result, my reports didn't work. What should I do? Thanks!

lk_spec

Create a new datasource and link the report to the new datasources. What do you mean by catalog ?

HTH, jens Suessmeyer.

http://www.sqlserver2005.de|||

Thanks for you reply!

The data source was created dynamiclly through web services and SOAP. Before I deleted the data source, my reports worked fine. After that, however, it didn't work. I wonder if it's related to the root data source or something. Any help is appreciated!

|||The datasource is saved in the RDL as:

<rd:DataSourceID>e80dd27e-83d3-475c-b4c7-81261ee98f08</rd:DataSourceID>

with an ID, I don′t know if you can recover that. I you have the chance to reploy everything, I would go this way.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||

Jen,

Where did I find the datasource id. I checked the rdl file, but there wasn't such information. I'm really new at this stuff.

|||Did you have a view on the RDL in VS or the RDL in the Report Manager, you can download the RDL in Report Manager by Opening the report > Properties > Edit Report, you will get the RDL file downloaded which contained (in my case) this GUID.

HTH, Jens Suessmeyer.

http.//www.sqlserver2005.de|||

Jen,

Thanks for your reply!

I was able to create a new data source. The problem now is, however, an error message saying that the item "/" cannot be found. The application failed when it tried to FindCatalogItems:

rsws.FindItems(folderName, BooleanOperatorEnum.Or, conditions)

Any help would be appreciated!

lk_spec

|||

If you take regular backup of database than restore the database.

Dataset Window

Could someone please tell me how to bring up the dataset window in
Reporting Services so I can drag and drop the fields from the dataset?
I've searched high and low in the report designer and cannot find that
option anywhere. I am working with Reporting Services 2000.
Thank you.In Visual Studio, on the menu bar, select the View menu. When you have
a report RDL open, you should be able to see the "Datasets" option
under the View menu, usually towards the bottom of the menu.
Otherwise, try the "Other Windows >" option for more submenu options.
Regards,
Thiago Silva
Nergock@.gmail.com wrote:
> Could someone please tell me how to bring up the dataset window in
> Reporting Services so I can drag and drop the fields from the dataset?
> I've searched high and low in the report designer and cannot find that
> option anywhere. I am working with Reporting Services 2000.
> Thank you.

Dataset Refresh...

Hi Folks,
I have a dataset in Reporting Services which points to an stored procedure.
This SP returns all data. Then the data is filtered in the dataset based on
the report parameters is choosen.
1. Will the dataset be refreshed every time i hit the View Report button or
will it get refreshed only the first time the view report is choosen and
would have the data cached till the session ends.
2. Is it better to do the filtering of data in the SP by passing parameters
from the report or is it better to use filters on a dataset.
Thank you
RamdasThe dataset gets refreshed each time you click on View Report with one
important note. In development the designer caches the data and only
refreshes if the parameter changes. If deployed to the server it refreshes
every time.
You are much better off to filter with the query or the SP. I pretty much
never use report filters. The very best thing to do for performance is have
the database return only those records you want.
In RS 2005 all rendering operations are done in RAM. If you have a large
resultset you will be using up a lot of your server's resources.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Ram" <Ram@.discussions.microsoft.com> wrote in message
news:FF7426E4-2FC0-4069-8282-D819CE00F6AF@.microsoft.com...
> Hi Folks,
> I have a dataset in Reporting Services which points to an stored
> procedure.
> This SP returns all data. Then the data is filtered in the dataset based
> on
> the report parameters is choosen.
> 1. Will the dataset be refreshed every time i hit the View Report button
> or
> will it get refreshed only the first time the view report is choosen and
> would have the data cached till the session ends.
> 2. Is it better to do the filtering of data in the SP by passing
> parameters
> from the report or is it better to use filters on a dataset.
> Thank you
> Ramdas
>|||Hi Bruce,
Thank you for the insight.
One more question, is it possible to refresh the report without clicking the
View Report button.
Thank you
"Bruce L-C [MVP]" wrote:
> The dataset gets refreshed each time you click on View Report with one
> important note. In development the designer caches the data and only
> refreshes if the parameter changes. If deployed to the server it refreshes
> every time.
> You are much better off to filter with the query or the SP. I pretty much
> never use report filters. The very best thing to do for performance is have
> the database return only those records you want.
> In RS 2005 all rendering operations are done in RAM. If you have a large
> resultset you will be using up a lot of your server's resources.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Ram" <Ram@.discussions.microsoft.com> wrote in message
> news:FF7426E4-2FC0-4069-8282-D819CE00F6AF@.microsoft.com...
> > Hi Folks,
> > I have a dataset in Reporting Services which points to an stored
> > procedure.
> > This SP returns all data. Then the data is filtered in the dataset based
> > on
> > the report parameters is choosen.
> >
> > 1. Will the dataset be refreshed every time i hit the View Report button
> > or
> > will it get refreshed only the first time the view report is choosen and
> > would have the data cached till the session ends.
> >
> > 2. Is it better to do the filtering of data in the SP by passing
> > parameters
> > from the report or is it better to use filters on a dataset.
> >
> > Thank you
> >
> > Ramdas
> >
>
>|||If you have a report that you want to refresh automatically then you can set
that in the dataset tab. Click on the ...
For instance, I have a report that refreshes every 5 minutes automatically.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Ram" <Ram@.discussions.microsoft.com> wrote in message
news:D205F7FF-A85D-4ACD-95EF-D68050FA3EFE@.microsoft.com...
> Hi Bruce,
> Thank you for the insight.
> One more question, is it possible to refresh the report without clicking
> the
> View Report button.
> Thank you
> "Bruce L-C [MVP]" wrote:
>> The dataset gets refreshed each time you click on View Report with one
>> important note. In development the designer caches the data and only
>> refreshes if the parameter changes. If deployed to the server it
>> refreshes
>> every time.
>> You are much better off to filter with the query or the SP. I pretty much
>> never use report filters. The very best thing to do for performance is
>> have
>> the database return only those records you want.
>> In RS 2005 all rendering operations are done in RAM. If you have a large
>> resultset you will be using up a lot of your server's resources.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "Ram" <Ram@.discussions.microsoft.com> wrote in message
>> news:FF7426E4-2FC0-4069-8282-D819CE00F6AF@.microsoft.com...
>> > Hi Folks,
>> > I have a dataset in Reporting Services which points to an stored
>> > procedure.
>> > This SP returns all data. Then the data is filtered in the dataset
>> > based
>> > on
>> > the report parameters is choosen.
>> >
>> > 1. Will the dataset be refreshed every time i hit the View Report
>> > button
>> > or
>> > will it get refreshed only the first time the view report is choosen
>> > and
>> > would have the data cached till the session ends.
>> >
>> > 2. Is it better to do the filtering of data in the SP by passing
>> > parameters
>> > from the report or is it better to use filters on a dataset.
>> >
>> > Thank you
>> >
>> > Ramdas
>> >
>>

Dataset Query IN Clause issue

Hi everyone I'm using Reporting Services and in my Dataset's Query I'm
want to use a variable in my IN clause that that holds the different
values (strings) but I can't seem to get it to work. I've posted the
SQL statement as it currently is and what I tried to do to get it to
work.
Thanks for any help in advance.
Mike
Current Statemenet:
SELECT NumOfPhotos.NumberOfPhotos
FROM NumOfPhotos
INNER JOIN vewStatsInnerSummary ON
(vewStatsInnerSummary.Batch=NumOfPhotos.Batch AND
vewStatsInnerSummary.PhotoSet=NumOfPhotos.PhotoSet)
WHERE vewStatsInnerSummary.Batch=@.Batch
AND vewStatsInnerSummary.PhotoSet=@.PhotoSet
AND vewStatsInnerSummary.Photoset<>'99'
AND DATEDIFF(day,@.ReportBeginDate ,statsdate) >=0
AND DATEDIFF(day,@.ReportEndDate,statsdate) <=0
GROUP BY NumberOfPhotos
What I tried:
SELECT NumOfPhotos.NumberOfPhotos
FROM NumOfPhotos
INNER JOIN vewStatsInnerSummary ON
(vewStatsInnerSummary.Batch=NumOfPhotos.Batch AND
vewStatsInnerSummary.PhotoSet=NumOfPhotos.PhotoSet)
WHERE vewStatsInnerSummary.Batch=@.Batch
AND vewStatsInnerSummary.PhotoSet IN (@.PhotoSet)
AND vewStatsInnerSummary.Photoset<>'99'
AND DATEDIFF(day,@.ReportBeginDate ,statsdate) >=0
AND DATEDIFF(day,@.ReportEndDate,statsdate) <=0
GROUP BY NumberOfPhotosMike,
The string variable is treated as a single data element, not a list or array
of elements. So, if:
SET @.PhotoSet = '1,23,56,78'
Your query would only return a row with the value '1,23,56,78' in
vewStatsInnerSummary.Batch.
You can slice up the string into a temporary table and then use that table
in your query, or use dynamic SQL, etc.
Vyas has a write up on your issue.
http://vyaskn.tripod.com/passing_arrays_to_stored_procedures.htm
RLF
"Mike" <m_gorgone@.hotmail.com> wrote in message
news:1194024918.596959.227710@.y42g2000hsy.googlegroups.com...
> Hi everyone I'm using Reporting Services and in my Dataset's Query I'm
> want to use a variable in my IN clause that that holds the different
> values (strings) but I can't seem to get it to work. I've posted the
> SQL statement as it currently is and what I tried to do to get it to
> work.
> Thanks for any help in advance.
> Mike
> Current Statemenet:
> SELECT NumOfPhotos.NumberOfPhotos
> FROM NumOfPhotos
> INNER JOIN vewStatsInnerSummary ON
> (vewStatsInnerSummary.Batch=NumOfPhotos.Batch AND
> vewStatsInnerSummary.PhotoSet=NumOfPhotos.PhotoSet)
> WHERE vewStatsInnerSummary.Batch=@.Batch
> AND vewStatsInnerSummary.PhotoSet=@.PhotoSet
> AND vewStatsInnerSummary.Photoset<>'99'
> AND DATEDIFF(day,@.ReportBeginDate ,statsdate) >=0
> AND DATEDIFF(day,@.ReportEndDate,statsdate) <=0
> GROUP BY NumberOfPhotos
>
> What I tried:
> SELECT NumOfPhotos.NumberOfPhotos
> FROM NumOfPhotos
> INNER JOIN vewStatsInnerSummary ON
> (vewStatsInnerSummary.Batch=NumOfPhotos.Batch AND
> vewStatsInnerSummary.PhotoSet=NumOfPhotos.PhotoSet)
> WHERE vewStatsInnerSummary.Batch=@.Batch
> AND vewStatsInnerSummary.PhotoSet IN (@.PhotoSet)
> AND vewStatsInnerSummary.Photoset<>'99'
> AND DATEDIFF(day,@.ReportBeginDate ,statsdate) >=0
> AND DATEDIFF(day,@.ReportEndDate,statsdate) <=0
> GROUP BY NumberOfPhotos
>|||The type of (Reporting Services) parameter you are using is for a
multivalued parameter. It would show up as a drop down with several values,
each value having a checkbox next to it. Then, whatever values are checked
will be in your "IN" clause. The top checkbox is used for "Select All".
--
Alain Quesnel
alainsansspam@.logiquel.com
www.logiquel.com
"Mike" <m_gorgone@.hotmail.com> wrote in message
news:1194024918.596959.227710@.y42g2000hsy.googlegroups.com...
> Hi everyone I'm using Reporting Services and in my Dataset's Query I'm
> want to use a variable in my IN clause that that holds the different
> values (strings) but I can't seem to get it to work. I've posted the
> SQL statement as it currently is and what I tried to do to get it to
> work.
> Thanks for any help in advance.
> Mike
> Current Statemenet:
> SELECT NumOfPhotos.NumberOfPhotos
> FROM NumOfPhotos
> INNER JOIN vewStatsInnerSummary ON
> (vewStatsInnerSummary.Batch=NumOfPhotos.Batch AND
> vewStatsInnerSummary.PhotoSet=NumOfPhotos.PhotoSet)
> WHERE vewStatsInnerSummary.Batch=@.Batch
> AND vewStatsInnerSummary.PhotoSet=@.PhotoSet
> AND vewStatsInnerSummary.Photoset<>'99'
> AND DATEDIFF(day,@.ReportBeginDate ,statsdate) >=0
> AND DATEDIFF(day,@.ReportEndDate,statsdate) <=0
> GROUP BY NumberOfPhotos
>
> What I tried:
> SELECT NumOfPhotos.NumberOfPhotos
> FROM NumOfPhotos
> INNER JOIN vewStatsInnerSummary ON
> (vewStatsInnerSummary.Batch=NumOfPhotos.Batch AND
> vewStatsInnerSummary.PhotoSet=NumOfPhotos.PhotoSet)
> WHERE vewStatsInnerSummary.Batch=@.Batch
> AND vewStatsInnerSummary.PhotoSet IN (@.PhotoSet)
> AND vewStatsInnerSummary.Photoset<>'99'
> AND DATEDIFF(day,@.ReportBeginDate ,statsdate) >=0
> AND DATEDIFF(day,@.ReportEndDate,statsdate) <=0
> GROUP BY NumberOfPhotos
>

Dataset Priority

I'm new to reporting services and have a problem that I need help with.
Is there a way to set Dataset priority?
My problem is this. I have two datasets on my report that produce data
to display.
Dataset1(DS1) is a Stored procedure that returns a recordset.
DS2 is also a procedure that returns data.
DS1 creates a detailed set of records for a specific entity. While
doing this it runs 10-20 views and Procs (based on User Settings). In
order for DS2 to be correct, DS1 needs to be completely done before
the call to DS2 is made.
When running the report with SQL Profiler, I see that DS1 before DS2,
but DS2 is completing sooner.
Creating a LINKED sub-report for DS2 is not a viable alternative.
Any help would be greatly appreciated.
RUMAGEtry this:
create a parameter for the second SP, then setup the value of this parameter
from the SP1 result.
RS will evaluate the first SP, then execute to the second SP because there
is a parameter dependency.
"jrumage" <jeff@.grinandgiggle.com> wrote in message
news:1137429946.779314.249040@.g14g2000cwa.googlegroups.com...
> I'm new to reporting services and have a problem that I need help with.
> Is there a way to set Dataset priority?
> My problem is this. I have two datasets on my report that produce data
> to display.
> Dataset1(DS1) is a Stored procedure that returns a recordset.
> DS2 is also a procedure that returns data.
> DS1 creates a detailed set of records for a specific entity. While
> doing this it runs 10-20 views and Procs (based on User Settings). In
> order for DS2 to be correct, DS1 needs to be completely done before
> the call to DS2 is made.
> When running the report with SQL Profiler, I see that DS1 before DS2,
> but DS2 is completing sooner.
> Creating a LINKED sub-report for DS2 is not a viable alternative.
> Any help would be greatly appreciated.
> RUMAGE
>|||Thats a good idea, however it doesn't work the way you might thinkI
Doing that causes the first proc(DS1) to run twice.
First DS1 runs to get the result for the Parameter,
Then DS2 Runs,
Then DS1 runs to get the results for the report
Any Other ideas?|||For RS 2000 (and I believe RS 2005) the datasets get filled in the order
they are in the rdl file. Make a copy of your report (because messing with
the rdl file directly is dangerous) and move the definitions around.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"jrumage" <jeff@.grinandgiggle.com> wrote in message
news:1137517745.147303.157840@.g43g2000cwa.googlegroups.com...
> Thats a good idea, however it doesn't work the way you might thinkI
> Doing that causes the first proc(DS1) to run twice.
> First DS1 runs to get the result for the Parameter,
> Then DS2 Runs,
> Then DS1 runs to get the results for the report
> Any Other ideas?
>

Dataset Confusion in Reporting Services 2005

I'm creating a report that gets its data from a dataset returned from a
webservice call. I've supplied the URL for the webservice and in my Dataset
dialog (in Visual Studio), I've indicated the Comnand Type as 'Text' and my
Query String as follows:
<Query>
<SoapAction>Federation/GetAll</SoapAction>
<ElementPath />
</Query>
Where: 'Federation' is my namespace and 'GetAll' is the method which gets
consumed from the webservice.
When I click the Run button on the report Design screen, the webservice is
called correctly but what I get back is one row for each field in my dataset
instead of one row for each record in the dataset. IOW, my dataset has 20
fields in it and what I get returned is a table with 20 records in it, one
for each field, instead of the actual data.
Can someone tell me what I'm doing wrong?
Amos."Amos Soma" wrote:
> I'm creating a report that gets its data from a dataset returned from a
> webservice call. I've supplied the URL for the webservice and in my Dataset
> dialog (in Visual Studio), I've indicated the Comnand Type as 'Text' and my
> Query String as follows:
> <Query>
> <SoapAction>Federation/GetAll</SoapAction>
> <ElementPath />
> </Query>
> Where: 'Federation' is my namespace and 'GetAll' is the method which gets
> consumed from the webservice.
> When I click the Run button on the report Design screen, the webservice is
> called correctly but what I get back is one row for each field in my dataset
> instead of one row for each record in the dataset. IOW, my dataset has 20
> fields in it and what I get returned is a table with 20 records in it, one
> for each field, instead of the actual data.
> Can someone tell me what I'm doing wrong?
> Amos.
I am having the same problem. Any answers yet? I am messing around with the
Element Path attribute, but since the BOL is pretty thin, and I'm a newbie to
XML & Web Services the going is slow.|||Same problem!....please help!
Tito Perez wrote:
> "Amos Soma" wrote:
> > I'm creating a report that gets its data from a dataset returned from a
> > webservice call. I've supplied the URL for the webservice and in my Dataset
> > dialog (in Visual Studio), I've indicated the Comnand Type as 'Text' and my
> > Query String as follows:
> >
> > <Query>
> > <SoapAction>Federation/GetAll</SoapAction>
> > <ElementPath />
> > </Query>
> >
> > Where: 'Federation' is my namespace and 'GetAll' is the method which gets
> > consumed from the webservice.
> >
> > When I click the Run button on the report Design screen, the webservice is
> > called correctly but what I get back is one row for each field in my dataset
> > instead of one row for each record in the dataset. IOW, my dataset has 20
> > fields in it and what I get returned is a table with 20 records in it, one
> > for each field, instead of the actual data.
> >
> > Can someone tell me what I'm doing wrong?
> >
> > Amos.
> I am having the same problem. Any answers yet? I am messing around with the
> Element Path attribute, but since the BOL is pretty thin, and I'm a newbie to
> XML & Web Services the going is slow.|||Can someone please help with this?|||Try this... I was having the same problem but found another example. Here is
what I did and it worked for me - Notice the changes to the "ElementPath".
<Query>
<SoapAction>Federation/GetAll</SoapAction>
<ElementPath
IgnoreNamespaces="True">GetAllResponse/GetAllResult/diffgram/NewDataSet/Table</ElementPath>
</Query>
Note: If you only want certain columns returned, add your column name(s)
seperated by commas in brackets "{} " after "Table" (eg. <ElementPath
IgnoreNamespaces="True">GetAllResponse/GetAllResult/diffgram/NewDataSet/Table{column1_name,column2_name}</ElementPath>)
Hope this helps!
"Kays" wrote:
> Same problem!....please help!
> Tito Perez wrote:
> > "Amos Soma" wrote:
> > > I'm creating a report that gets its data from a dataset returned from a
> > > webservice call. I've supplied the URL for the webservice and in my Dataset
> > > dialog (in Visual Studio), I've indicated the Comnand Type as 'Text' and my
> > > Query String as follows:
> > >
> > > <Query>
> > > <SoapAction>Federation/GetAll</SoapAction>
> > > <ElementPath />
> > > </Query>
> > >
> > > Where: 'Federation' is my namespace and 'GetAll' is the method which gets
> > > consumed from the webservice.
> > >
> > > When I click the Run button on the report Design screen, the webservice is
> > > called correctly but what I get back is one row for each field in my dataset
> > > instead of one row for each record in the dataset. IOW, my dataset has 20
> > > fields in it and what I get returned is a table with 20 records in it, one
> > > for each field, instead of the actual data.
> > >
> > > Can someone tell me what I'm doing wrong?
> > >
> > > Amos.
> >
> > I am having the same problem. Any answers yet? I am messing around with the
> > Element Path attribute, but since the BOL is pretty thin, and I'm a newbie to
> > XML & Web Services the going is slow.
>

Dataset as source

Can we have an ADO.NET dataset as a datasource to a MS SQL Reporting service
report ? If so, how do we configure the report to do so ?
Regards,
Chak.here you are!
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql2k/html/RSDSetEx3.asp
"Chakra" <r_chakravarthy@.hotmail.com> schrieb im Newsbeitrag
news:u5I51OloFHA.3552@.TK2MSFTNGP10.phx.gbl...
> Can we have an ADO.NET dataset as a datasource to a MS SQL Reporting
> service
> report ? If so, how do we configure the report to do so ?
> Regards,
> Chak.
>

Datagrid and sql query isssue. Revenue reporting system

Hi-

I am trying to develop a page that pulls all customers from a database and display the revenue for each customer for each day I query in a months time.

I am able to pull out the revenue for one day in the month and display it in the column with the appropriate revenue next to the customer name.

My problem is two things.

1. It only displays customers that actually have revenue. So how would I get my datagrid to display all customers regardless if they have revenue in the database.

2. I need to be able to display more than 1 day in columns format. For example

Customer | Day1 | Day 2 |
----------
Acme Inc | $2200. |$1300.

Here is my sql code that pulls out the customers that have revenue and displays one day between a specific set of dates.

SQL = "SELECT pb_customers.customer_name AS 'customer', sum(pb_report_shippers.total_ext_price + pb_report_shippers.setup_cost) as 'total' FROM pb_report_shippers INNER JOIN pb_jobs ON pb_report_shippers.job_id = pb_jobs.job_id INNER JOIN pb_customers ON pb_jobs.customer_id = pb_customers.customer_id WHERE pb_report_shippers.shipper_date_time between cast('9/01/03' as datetime) and cast('9/02/03' as datetime) AND job_completed = '1' GROUP by pb_customers.customer_name"

Any help would be appreciated

ThanksTo get all customers, you'll want a left outer join for your related tables. Then try something like this

SELECT pb_customers.customer_name AS 'customer',
 sum(case (when day(pb_report_shippers.shipper_date_time) = 1
 then pb_report_shippers.total_ext_price + pb_report_shippers.setup_cost
 else 0
 end) as day1,
 sum(case (when day(pb_report_shippers.shipper_date_time) = 2
 then pb_report_shippers.total_ext_price + pb_report_shippers.setup_cost
 else 0
 end) as day2,
... and so on
FROM pb_report_shippers
 Left outer JOIN pb_jobs ON pb_report_shippers.job_id = pb_jobs.job_id
 left outer JOIN pb_customers ON pb_jobs.customer_id = pb_customers.customer_id WHERE pb_report_shippers.shipper_date_time between cast('9/01/03' as datetime) and cast('9/02/03' as datetime) AND job_completed = '1' GROUP by pb_customers.customer_name

HTH|||Thanks for the reply.

I am confused about this line.

sum(case (when day(pb_report_shippers.shipper_date_time) = 1

then pb_report_shippers.total_ext_price + pb_report_shippers.setup_cost

else 0

end) as day1,

What does the "1" represent. Am I supposed to put the datevalue I am looking for right there? When I do i get an error with the "when" keyword.|||The DAY() function returns the DD portion of MM/DD/YYYY. So, for 10/23/2003 it would contain 23. I don't think this is exactly what you are looking for, but the methodology should work. Instead I would use the DATEDIFF() function.

SELECT
 pb_customers.customer_name AS 'customer',
 SUM(
 CASE
 WHEN DATEDIFF(d,pb_report_shippers.shipper_date_time,CAST('9/01/03' AS datetime)) = 1
 THEN pb_report_shippers.total_ext_price + pb_report_shippers.setup_cost
 ELSE 0
 END
 ) AS day1,
 SUM(
 CASE
 WHEN DATEDIFF(d,pb_report_shippers.shipper_date_time,CAST('9/01/03' AS datetime)) = 2
 THEN pb_report_shippers.total_ext_price + pb_report_shippers.setup_cost
 ELSE 0
 END
 ) AS day2,
 SUM(
 CASE
 WHEN DATEDIFF(d,pb_report_shippers.shipper_date_time,cast('9/01/03' AS datetime)) = 3
 THEN pb_report_shippers.total_ext_price + pb_report_shippers.setup_cost
 ELSE 0
 END
 ) AS day3,
... and so on
FROM
 pb_report_shippers
LEFT OUTER JOIN
 pb_jobs ON pb_report_shippers.job_id = pb_jobs.job_id
LEFT OUTER JOIN
 pb_customers ON pb_jobs.customer_id = pb_customers.customer_id
WHERE
 pb_report_shippers.shipper_date_time between cast('9/01/03' as datetime) and cast('9/02/03' as datetime) AND
 job_completed = '1'
GROUP BY
 pb_customers.customer_name

Terri|||thanks for the help. I am getting closer to what I need.

One thing that is still occuring however is it only displays the customers that have revenue. It wont display all customers regardless is no revenue is found in the database. Is this what the left outer join is suppoesed to handle. What does it doe exactly compated to a normal Inner join.|||The left outer join should accomplish this. experiment a bit and comment out the

LEFT OUTER JOIN
 pb_customers ON pb_jobs.customer_id = pb_customers.customer_id

and any related columns of the query to see if you get the same results. This should help you track down why not all customers are showing up.|||Then I would restruture slightly. I would make the primary table you are pulling from your customers table, and I would LEFT OUTER join your shippers table. Like this (untested but hopefully it's close):

SELECT
 pb_customers.customer_name AS 'customer',
 SUM(
 CASE
 WHEN DATEDIFF(d,pb_report_shippers.shipper_date_time,CAST('9/01/03' AS datetime)) = 1
 THEN pb_report_shippers.total_ext_price + pb_report_shippers.setup_cost
 ELSE 0
 END
 ) AS day1,
 SUM(
 CASE
 WHEN DATEDIFF(d,pb_report_shippers.shipper_date_time,CAST('9/01/03' AS datetime)) = 2
 THEN pb_report_shippers.total_ext_price + pb_report_shippers.setup_cost
 ELSE 0
 END
 ) AS day2,
 SUM(
 CASE
 WHEN DATEDIFF(d,pb_report_shippers.shipper_date_time,cast('9/01/03' AS datetime)) = 3
 THEN pb_report_shippers.total_ext_price + pb_report_shippers.setup_cost
 ELSE 0
 END
 ) AS day3,
... and so on
FROM
 pb_customers
LEFT OUTER JOIN
 pb_jobs ON pb_jobs.customer_id = pb_customers.customer_id AND pb_report_shippers.shipper_date_time BETWEEN CAST('9/01/03' as datetime) AND CAST('9/02/03' as datetime) AND
 job_completed = '1'
LEFT OUTER JOIN
 pb_report_shippers ON pb_report_shippers.job_id = pb_jobs.job_id
GROUP BY
 pb_customers.customer_name

Data-Driven Subscriptions: Detecting Success

I am using Data-Driven Subcriptions to send a report to different
recipients using different parameters.
I would like to know if Reporting Services can:
-- update a database on successful distribution of report.
Simplest example: I have bit in table of recipients that determines
whether their report should be run and sent. After report is sent
(successfully), I want sql to set bit to 0.
Is this possible?
DanielThere is nothing built into Report Service that would allow you to do this.
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
<dangordo@.gmail.com> wrote in message
news:1105368020.135674.280110@.c13g2000cwb.googlegroups.com...
>I am using Data-Driven Subcriptions to send a report to different
> recipients using different parameters.
> I would like to know if Reporting Services can:
> -- update a database on successful distribution of report.
> Simplest example: I have bit in table of recipients that determines
> whether their report should be run and sent. After report is sent
> (successfully), I want sql to set bit to 0.
> Is this possible?
> Daniel
>|||Similar question from different angle: Sometimes distribution ecounters
errors. Is reportserverservice_*.log the only place success and errors
are recorded? Any advice/code on parsing that file?
I am prepared to create mini application to accomplish what i need.
Should I be investigating the rs utility? an application in C#?
Thanks.
Daniel Reib [MSFT] wrote:
> There is nothing built into Report Service that would allow you to do
this.
> --
> -Daniel
> This posting is provided "AS IS" with no warranties, and confers no
rights.
>
> <dangordo@.gmail.com> wrote in message
> news:1105368020.135674.280110@.c13g2000cwb.googlegroups.com...
> >I am using Data-Driven Subcriptions to send a report to different
> > recipients using different parameters.
> >
> > I would like to know if Reporting Services can:
> >
> > -- update a database on successful distribution of report.
> >
> > Simplest example: I have bit in table of recipients that determines
> > whether their report should be run and sent. After report is sent
> > (successfully), I want sql to set bit to 0.
> >
> > Is this possible?
> >
> > Daniel
> >|||Yes, the log file is the only place that we record specific information
about a Data driven subscription failure. You could parse for the
information, but we do not guarantee that the format will be the same from
release to release (a QFE or SP could change it as well).
I'm not sure of how useful it would be anyway. Are you seeing some of the
subscriptions fail, while others succeed? Generally they will either all
succeed or all fail.
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
<dangordo@.gmail.com> wrote in message
news:1105456778.305024.60080@.f14g2000cwb.googlegroups.com...
> Similar question from different angle: Sometimes distribution ecounters
> errors. Is reportserverservice_*.log the only place success and errors
> are recorded? Any advice/code on parsing that file?
> I am prepared to create mini application to accomplish what i need.
> Should I be investigating the rs utility? an application in C#?
> Thanks.
> Daniel Reib [MSFT] wrote:
>> There is nothing built into Report Service that would allow you to do
> this.
>> --
>> -Daniel
>> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>>
>> <dangordo@.gmail.com> wrote in message
>> news:1105368020.135674.280110@.c13g2000cwb.googlegroups.com...
>> >I am using Data-Driven Subcriptions to send a report to different
>> > recipients using different parameters.
>> >
>> > I would like to know if Reporting Services can:
>> >
>> > -- update a database on successful distribution of report.
>> >
>> > Simplest example: I have bit in table of recipients that determines
>> > whether their report should be run and sent. After report is sent
>> > (successfully), I want sql to set bit to 0.
>> >
>> > Is this possible?
>> >
>> > Daniel
>> >
>

Datadriven Subscription - Web Archive Improper formatting

Hi All,

I have a data driven subscription with Web Archive rendering format.

(SQL Reporting Service 2000). When a user gets this email, the format is not alligned properly, even, lines are missing in the report.

What could be the problem, is this a known issue? if yes, any patches available?

Please help me.

Thanks,

Nagesh

There are no replies to this query since one week...

Is this a silly query or tough one...

data-driven subscription

Does Reporting Services 2000 have data-driven subscriptions or will I need
to upgrade to RS 2005. I would like different employees to pass different
parameters by default. I receive the following message in RS 2000 and I'm
storing a sql account password so I don't think security is the issue.
The error says the following "Subscriptions cannot be created because the
credentials used to run the report are not stored, the report is using
user-defined parameter values"data-driven subscriptions do work in RS 2000. I believe the problem with
your subscription is that one of your parameters is the user login. You are
probably defaulting the current user into the field. You may need to remove
the default value so that you can supply it from the data driven query for
your subscription.
"Colin" wrote:
> Does Reporting Services 2000 have data-driven subscriptions or will I need
> to upgrade to RS 2005. I would like different employees to pass different
> parameters by default. I receive the following message in RS 2000 and I'm
> storing a sql account password so I don't think security is the issue.
>
> The error says the following "Subscriptions cannot be created because the
> credentials used to run the report are not stored, the report is using
> user-defined parameter values"
>
>

DATABASEPROPERYEX is not a recognised function name

I am trying to write a report in reporting services 2000 which access a sql server 7 database. It all workes fine with the connection and creating a dataset but I cannot seem to get the results displayed in the preview section. I get the following error:

"An error occured during report processing query execution failed for dataset XXXX DATABASEPROPERYEX is not a recognised function name "

Any help greatly appreciated

Your are running RS 2000 RTM - you have two options:

* install RS 2000 SP1 or SP2 on both the report server and the report designer machines. The service packs contain the fix.

* Alternatively, in report designer go to the "Data Options" tab of the "Dataset"
dialog. On the Data Options tab you will see that all settings contain "Auto" (and report server would therefore try to auto-detect the collation settings from the database server). Replace the Auto-settings with the following settings (e.g. if your SQL 7.0
database collation is "SQL_Latin1_General_CP1_CI_AS"):
Collation = Latin1_General
Case sensitivity = false
Kanatype sensitivity = false
Width sensitivity = false
Accent sensitivity = true

-- Robert

Database versipn (C.0.8.40) does not match

When I click the "Upgrade" button while trying to configure reporting
services I get a couple of errors here are the two errors:
"Verify Database Version" First Yellow "!" Says :
The database version (C.0.8.40) does not match your reporting services
installation. You must upgrade your reporting services database.
"Create Upgrade Script for Database Version" Second Yellow "!" say:
Couldn't generate the upgrade script. There is no upgrade script available
for this version.
Has anyone seen these errors before. How to I get around these. Right now
my "Database Setup" in has a red "X" on the "Reporting Services Configuration
Manager" window.I have the same exact problem. Any fixes yet?
--
---
Yes, I searched first :)
"Greg Larsen" wrote:
> When I click the "Upgrade" button while trying to configure reporting
> services I get a couple of errors here are the two errors:
> "Verify Database Version" First Yellow "!" Says :
> The database version (C.0.8.40) does not match your reporting services
> installation. You must upgrade your reporting services database.
> "Create Upgrade Script for Database Version" Second Yellow "!" say:
> Couldn't generate the upgrade script. There is no upgrade script available
> for this version.
> Has anyone seen these errors before. How to I get around these. Right now
> my "Database Setup" in has a red "X" on the "Reporting Services Configuration
> Manager" window.
>|||Do you have a resolution to this issue? I ahve the exact same problem.
"Greg Larsen" wrote:
> When I click the "Upgrade" button while trying to configure reporting
> services I get a couple of errors here are the two errors:
> "Verify Database Version" First Yellow "!" Says :
> The database version (C.0.8.40) does not match your reporting services
> installation. You must upgrade your reporting services database.
> "Create Upgrade Script for Database Version" Second Yellow "!" say:
> Couldn't generate the upgrade script. There is no upgrade script available
> for this version.
> Has anyone seen these errors before. How to I get around these. Right now
> my "Database Setup" in has a red "X" on the "Reporting Services Configuration
> Manager" window.
>|||I had the same problem.
It was caused by that I had installed reporting services after I had run
SP1 on SQL Server 2005.
Solved it by runing SQL Server 2005 SP1 again.
Hope this helps!
"Rich" wrote:
> Do you have a resolution to this issue? I ahve the exact same problem.
> "Greg Larsen" wrote:
> > When I click the "Upgrade" button while trying to configure reporting
> > services I get a couple of errors here are the two errors:
> >
> > "Verify Database Version" First Yellow "!" Says :
> > The database version (C.0.8.40) does not match your reporting services
> > installation. You must upgrade your reporting services database.
> >
> > "Create Upgrade Script for Database Version" Second Yellow "!" say:
> > Couldn't generate the upgrade script. There is no upgrade script available
> > for this version.
> >
> > Has anyone seen these errors before. How to I get around these. Right now
> > my "Database Setup" in has a red "X" on the "Reporting Services Configuration
> > Manager" window.
> >

Database version - can't install SP1

I have a SQL Server 2005 install with the 64-bit Database Server, but
I've had to install the 32-bit version of Reporting Services, since IIS
is running in 32-bit mode. Everything's green in the Configuration
Tool, but when I go to http://server/reportserver, I get the following
error:
The version of the report server database is either in a format that is
not valid, or it cannot be read. The found version is 'C.0.8.43'. The
expected version is 'C.0.8.40'. To continue, update the version of the
report server database and verify access rights.
(rsInvalidReportServerDatabase)
I think the problem is that I have SP1 installed on the 64-bit install,
but it isn't installed on the 32-bit install. However, when I run the
x86 SP1, it doesn't find that I have Reporting Services installed, so
it doesn't update it. I've run the 32-bit and the 64-bit SP1 installer
several times (with reboots!), but to no avail.
Any suggestions? I think I need the SP1 installer to find my
installation of the 32-bit Reporting Services.
Thanks,
CliffeA little more info: If I try to run the 32-bit setup from the original
install CD, it doesn't recognize that anything's installed - it walks
through the wizard from scratch.
If I run ARPWrapper.exe from \Program Files (x86)\Microsoft SQL
Server\90\Setup Bootstrap, it comes up with an error "SQL Server Setup
cannot proceed because the component you have selected has already been
uninstalled. Refer to books online..."
Any suggestions?
Thanks,
Cliffe
cliffeh wrote:
> I have a SQL Server 2005 install with the 64-bit Database Server, but
> I've had to install the 32-bit version of Reporting Services, since IIS
> is running in 32-bit mode. Everything's green in the Configuration
> Tool, but when I go to http://server/reportserver, I get the following
> error:
> The version of the report server database is either in a format that is
> not valid, or it cannot be read. The found version is 'C.0.8.43'. The
> expected version is 'C.0.8.40'. To continue, update the version of the
> report server database and verify access rights.
> (rsInvalidReportServerDatabase)
> I think the problem is that I have SP1 installed on the 64-bit install,
> but it isn't installed on the 32-bit install. However, when I run the
> x86 SP1, it doesn't find that I have Reporting Services installed, so
> it doesn't update it. I've run the 32-bit and the 64-bit SP1 installer
> several times (with reboots!), but to no avail.
> Any suggestions? I think I need the SP1 installer to find my
> installation of the 32-bit Reporting Services.
> Thanks,
> Cliffe

database updates not reflected in report

Iam using visual web developer express with sql server 2005 with advanced services. Iam designing my database reports with sql server reporting services(SQL server business interlligence development studio) and they are running correctly. Problem is, when i add records to my database and then go back and run my reports, the new records effects are not reflected in my new report runs.May be i should say my reports do not update. they only retrieve the data that was already in the database table during the time of report design. I dont know a way of forcing updates to be reflected in the reports.

Help Appreciated.

The updates aren't instant. Try closing the report and reopening after you make an update.