Prev: Reporting Services Config Error
Next: Reporting services vs. Active Reports
From: Jack Bender on 15 Dec 2006 13:38
Instead of creating a sub-report for a report, I created an additional table
(FundHistoryTable) and an additional dataset (FundHistory) for that table
within the master report. My dataset is the following for FundHistory…

select
adv_fundhistoryid,
adv_currencyidname,
adv_amount,
amount =
CASE
WHEN adv_currencyidname = 'EUR' THEN '€ ' + CAST((adv_amount) as VARCHAR)
WHEN adv_currencyidname = 'USD' THEN '$ ' + CAST((adv_amount) as VARCHAR)
END,
adv_fundentityidname,
adv_commitmentdate

from FilteredAdv_FundHistory as CRMAF_FilteredAdv_FundHistory

where adv_companyid = @AcctID

I then created a report parameter called AcctID and that report parameter is
using a Default Value from the Company Dataset which is the accountid field
which resides in the SQL statement used for the Company Dataset

When I run the report from within Visual Studio 2005 under the preview tab,
I see the data in the FundHistory table and it is correct. However, when I
publish the report and go to view it in the
http://reportdbserver/reports/SRSFolder directory I get the following error…

An error has occurred during report processing. (rsProcessingAborted)
Cannot set the command text for data set 'Company'.
(rsErrorSettingCommandText)
Error during processing of the CommandText expression of dataset 'Company'.
(rsQueryCommandTextProcessingError)

I can throw this data into a subreport but I was extremely interested in
using the tables so I have less moving parts to upload, publish, etc.

Does anyone have any ideas as to what I am doing wrong?

From: Bruce L-C [MVP] on 15 Dec 2006 13:47
First off. There is should be no problem doing this. Having multiple
datasets is the way to go if you can tie them to a parameter. If you have a
master-detail relationship then you need subreport. This is kindof a pseudo
master-detail. It is for a single master so no problem doing this.

My guess is that this has to do with credentials/security. Is the
FilteredAdv_FunctionHistory a view? Does the account being used to access
the view/table have rights to it?

Also, go to the data source on the server and view the credentials for it
and make sure it looks OK.


--
Bruce Loehle-Conger
MVP SQL Server Reporting Services

"Jack Bender" <JackBender(a)discussions.microsoft.com> wrote in message
news:E3BB5D0A-0F43-414B-AAA1-3B88A571294A(a)microsoft.com...
> Instead of creating a sub-report for a report, I created an additional
> table
> (FundHistoryTable) and an additional dataset (FundHistory) for that table
> within the master report. My dataset is the following for FundHistory.
>
> select
> adv_fundhistoryid,
> adv_currencyidname,
> adv_amount,
> amount =
> CASE
> WHEN adv_currencyidname = 'EUR' THEN '? ' + CAST((adv_amount) as VARCHAR)
> WHEN adv_currencyidname = 'USD' THEN '$ ' + CAST((adv_amount) as VARCHAR)
> END,
> adv_fundentityidname,
> adv_commitmentdate
>
> from FilteredAdv_FundHistory as CRMAF_FilteredAdv_FundHistory
>
> where adv_companyid = @AcctID
>
> I then created a report parameter called AcctID and that report parameter
> is
> using a Default Value from the Company Dataset which is the accountid
> field
> which resides in the SQL statement used for the Company Dataset
>
> When I run the report from within Visual Studio 2005 under the preview
> tab,
> I see the data in the FundHistory table and it is correct. However, when
> I
> publish the report and go to view it in the
> http://reportdbserver/reports/SRSFolder directory I get the following
> error.
>
> An error has occurred during report processing. (rsProcessingAborted)
> Cannot set the command text for data set 'Company'.
> (rsErrorSettingCommandText)
> Error during processing of the CommandText expression of dataset 'Company'.
> (rsQueryCommandTextProcessingError)
>
> I can throw this data into a subreport but I was extremely interested in
> using the tables so I have less moving parts to upload, publish, etc.
>
> Does anyone have any ideas as to what I am doing wrong?
>


 | 
Pages: 1
Prev: Reporting Services Config Error
Next: Reporting services vs. Active Reports