Wednesday, March 21, 2012
Reports with "Multi Parameters" Accessing Analysis Services Cube (MDX calls)?
with MDX syntax. I am successful so far in having one parameter set. I need
to know if it is possible to have more than one to set and get the desired
results accordingly. Here is what I have so far:
Data Source:
="Select NON EMPTY{ {[Geo1].[Div].members}*{[Dept1].[Department].members}*
{ [Employee].[All Employee].children}} On ROWS,
NON EMPTY {[Measures].[Percent Complete]} ON COLUMNS
FROM [My Cube1]
WHERE ([" + Parameters!pCurr.Value + "])"
pCurr data set :
SELECT { [Measures].[Percent Complete]} ON Columns,NON EMPTY [Curriculum].
[Curriculum].Members ON ROWS FROM [My Cube1]
So, thus Far I can control the result set by Choosing the Curriculum only.
I need to be able to have another parameter set, besides Curriculum, for
instance Department, where I would choose department, then curriculum,
then the result set would be the outcome needed? Is it feasible and how?
Thanks so much in advance.
--
Message posted via http://www.sqlmonster.com="SELECT NON EMPTY { {[Geo1].[Div].members } * {[Dept1].[Department].[" +
Parameters!pDept.Value + "] } * { [Employee].[All Employee].children}} On
ROWS,
NON EMPTY {[Measures].[Percent Complete]} ON COLUMNS
FROM [My Cube1]
WHERE ([" + Parameters!pCurr.Value + "])"
btw, i usually don't use the where clause, i just put parameter into the
select dimensions.
"Hobie via SQLMonster.com" wrote:
> I have an OLAP Cube that is being accessed by Reporting services reports
> with MDX syntax. I am successful so far in having one parameter set. I need
> to know if it is possible to have more than one to set and get the desired
> results accordingly. Here is what I have so far:
> Data Source:
> ="Select NON EMPTY{ {[Geo1].[Div].members}*{[Dept1].[Department].members}*
> { [Employee].[All Employee].children}} On ROWS,
> NON EMPTY {[Measures].[Percent Complete]} ON COLUMNS
> FROM [My Cube1]
> WHERE ([" + Parameters!pCurr.Value + "])"
> pCurr data set :
> SELECT { [Measures].[Percent Complete]} ON Columns,NON EMPTY [Curriculum].
> [Curriculum].Members ON ROWS FROM [My Cube1]
> So, thus Far I can control the result set by Choosing the Curriculum only.
> I need to be able to have another parameter set, besides Curriculum, for
> instance Department, where I would choose department, then curriculum,
> then the result set would be the outcome needed? Is it feasible and how?
> Thanks so much in advance.
> --
> Message posted via http://www.sqlmonster.com
>|||Great. it worked with few glitches that I am working on correcting.
However, I do not want to include my parameters in the select. see I would
like the below format:
Select Select Select
Division: Services Department:Acounting Curriculum: Excel (as params)
_________________________________________________________________
Employee Percent Complete
John 40%
Amy 20%
Kerry 90%
That is why I was adding the where clause. If I can say in the WHERE:
WHERE ([" + Parameters!pDiv.Value + "] AND [" + Parameters!pDept.Value + "]
" AND [" + Parameters!pCurr.Value + "] ),
But I know there is no AND in this fasion in MDX. Any suggestions!
Again thanks in advance for the help!!!
--
Message posted via http://www.sqlmonster.com|||WEll - I solved my own problem, but thanks to mike for the tip; No more
WHERE statement in MDX for params specifications here is what mike
suggested and how I implemented it. :) .
--
DataSource1:
="SELECT NON EMPTY { {[Geo1].[Div].[" + Parameters!pDiv.Value + "]} * {
[Dept1].[Department].[" + Parameters!pDept.Value + "] }*{[Curriculum].
[Curriculum].[" + Parameters!pCurr.Value + "] }*{ [Employee].[All Employee]
.children} } On ROWS, NON EMPTY {[Measures].[Curr Percent Complete]} ON
COLUMNS FROM [CertificationsDB]"
--
DataSet pCurr:
SELECT { [Measures].[Curr percent Complete]} ON Columns,NON EMPTY
[Curriculum].[Curriculum].Members ON ROWS FROM [CertificationsDB]
DataSet pDept:
SELECT { [Measures].[Curr percent Complete]} ON Columns,NON EMPTY [Dept1].
[Department].Members ON ROWS FROM [CertificationsDB]
DataSet pDiv:
SELECT { [Measures].[Curr percent Complete]} ON Columns,NON EMPTY [Geo1].
[Div].Members ON ROWS FROM [CertificationsDB]
Enjoy y'all
--
Message posted via http://www.sqlmonster.comsql
Tuesday, March 20, 2012
Reports run under my credentials, not those stored on report
I've created a report in SSRS 2005 that sources from a SSAS 2000 cube (on another server). In BIDS (running on the SSRS 2005 server) I'm using a shared data source with "generic" credentials to access the data. The generic credentials I'm using is an account that has datareader access. In BIDS the report runs, builds and deploys just fine. No problem so far!
My problem has to do with running the report from the Report Server. The only way I can get the report to run from the Report Server web site is to modify the data source and pass my credentials in the report (thereby overriding the credentials stored in the report). Why would this be?
SSRS is running on a Windows Server 2003 machine and I've installed the SQL 2005 SP1. I've also modified the Report Server web site on the server to include the Everyone group, with appropriate permissions.
I'm at a total loss here. I know I'm an admin on the server that is running SSRS, but why is it that only my credentials can be used to run the report? I haven't tried using the credentials of another admin, but I'm assuming their's would work as well. I guess I don't understand why the generic credentials won't work.
I thought I might be experiencing the "double-hop" issue, but I don't think my credentials would work with double-hop. Or would they?
Anyway, any help is appreciated.
I guess you're storing Windows credentials. Verify that 'use as windows authentication credetials' is checked for the stored credentials on the server database credentials page.
-Lukasz
|||
Well, I found my problem...
In the cube, I added the Everyone group as a user and now everthing works fine. A little confusing since the report ran without a problem in BIDS. However, this would explain why I had to pass my NT credentials to get the report to run.
|||I've been dealing with the same kind of issues. My conclusion isthat (using NTLM and not Kerberos) RS and AS have to be on the same
machine.
I don't think it's going to work for us to use Everyone for security.
An alternative might be to add the security for the machine account
that RS runs under (ASPNET?) to one box to an AS role on the other box,
but I don't see how to do that, since the machine account is not in a
domain that's recognized on the AS box.
Jim|||Ok, I got it going, thanks to the instructions below from MS. We have a dummy userid
we use for this.
1. Publish your report & data source from VS
2. Use Report Manager to browse to the report, click Properties
3. Click Data Sources, Choose “Credentials Stored securely in the report server….”
4. Enter UID/PWD
5. Select “Use as Windows Credentials when connecting to the data source” checkbox
6. *Click Apply*
7. View report again, click
Properties, go back to Data Source and make sure the changes you made
have indeed been applied.|||
Yeah, the Everyone thing is a temporary work-a-round. I agree with you that this is a Kerberos issue. Unfortunately, RS and AS can't run on the same machine in our environment.
I'm still researching and I'll keep y'all posted.
|||
AS only allows windows credentials to access data. So you're limited to one of three options:
1) use stored credentials on the report server - all users access the data using the same credentials
2) use integrated security AND EITHER enable Kerberos OR put RS and AS on the same computer - more difficult configuration
3) prompt the user for their credentials - more onerous user experience
The balance is up to you to strike based on your business needs. Generally granting Everyone permission to your corporate data is a bad idea :-).
-Lukasz
|||Thanks. I'll research #2.
|||
Hi Lukasz,
Our probleam seems like this.
We have an system, with only one user to access AS05. We have an security level with users, roles and groups. When our user loggin into our system (ASP.NET 2), we concatenate in connection string same roles. With this roles, the user can see only him data.
In our system, this work fine, but how can I do this in SSRS?
Thanks,
Cristiano Leite.
Reports run under my credentials, not those stored on report
I've created a report in SSRS 2005 that sources from a SSAS 2000 cube (on another server). In BIDS (running on the SSRS 2005 server) I'm using a shared data source with "generic" credentials to access the data. The generic credentials I'm using is an account that has datareader access. In BIDS the report runs, builds and deploys just fine. No problem so far!
My problem has to do with running the report from the Report Server. The only way I can get the report to run from the Report Server web site is to modify the data source and pass my credentials in the report (thereby overriding the credentials stored in the report). Why would this be?
SSRS is running on a Windows Server 2003 machine and I've installed the SQL 2005 SP1. I've also modified the Report Server web site on the server to include the Everyone group, with appropriate permissions.
I'm at a total loss here. I know I'm an admin on the server that is running SSRS, but why is it that only my credentials can be used to run the report? I haven't tried using the credentials of another admin, but I'm assuming their's would work as well. I guess I don't understand why the generic credentials won't work.
I thought I might be experiencing the "double-hop" issue, but I don't think my credentials would work with double-hop. Or would they?
Anyway, any help is appreciated.
I guess you're storing Windows credentials. Verify that 'use as windows authentication credetials' is checked for the stored credentials on the server database credentials page.
-Lukasz
|||Well, I found my problem...
In the cube, I added the Everyone group as a user and now everthing works fine. A little confusing since the report ran without a problem in BIDS. However, this would explain why I had to pass my NT credentials to get the report to run.
|||I've been dealing with the same kind of issues. My conclusion is that (using NTLM and not Kerberos) RS and AS have to be on the same machine.I don't think it's going to work for us to use Everyone for security.
An alternative might be to add the security for the machine account that RS runs under (ASPNET?) to one box to an AS role on the other box, but I don't see how to do that, since the machine account is not in a domain that's recognized on the AS box.
Jim
|||Ok, I got it going, thanks to the instructions below from MS. We have a dummy userid
we use for this.
1. Publish your report & data source from VS
2. Use Report Manager to browse to the report, click Properties
3. Click Data Sources, Choose “Credentials Stored securely in the report server….”
4. Enter UID/PWD
5. Select “Use as Windows Credentials when connecting to the data source” checkbox
6. *Click Apply*
7. View report again, click Properties, go back to Data Source and make sure the changes you made have indeed been applied.
|||
Yeah, the Everyone thing is a temporary work-a-round. I agree with you that this is a Kerberos issue. Unfortunately, RS and AS can't run on the same machine in our environment.
I'm still researching and I'll keep y'all posted.
|||AS only allows windows credentials to access data. So you're limited to one of three options:
1) use stored credentials on the report server - all users access the data using the same credentials
2) use integrated security AND EITHER enable Kerberos OR put RS and AS on the same computer - more difficult configuration
3) prompt the user for their credentials - more onerous user experience
The balance is up to you to strike based on your business needs. Generally granting Everyone permission to your corporate data is a bad idea :-).
-Lukasz
|||Thanks. I'll research #2.
|||Hi Lukasz,
Our probleam seems like this.
We have an system, with only one user to access AS05. We have an security level with users, roles and groups. When our user loggin into our system (ASP.NET 2), we concatenate in connection string same roles. With this roles, the user can see only him data.
In our system, this work fine, but how can I do this in SSRS?
Thanks,
Cristiano Leite.
Monday, February 20, 2012
Reporting Services without Analysis Services - how to connect to cube?
Right now I'm trying to get a report based on OLAP data to work on a server
which is only running Reporting Services. No Analysis Services have ever
touched this server. So when I try to run my report, I get the following
error:
An error has occurred during report processing. (rsProcessingAborted)
Cannot create a connection to data source 'PB_AxpCmpWR_Project'.
(rsErrorOpeningConnection)
The 'MSOLAP' provider is not registered on the local machine.
No error information available: REGDB_E_CLASSNOTREG(0x80040154).
I guess that it's because some dll's are missing, the msolap.dll and
msolap80.dll. I've figured I need to get these and register them in some
way, but I'm not sure how.
Can I copy these from a different server, and register them manually? Or
will they be installed and registered if I run the PTSFull.exe from the
Analysis Services installation? What is the best way of doing this?
All help appreciated!
Kaisa M. LindahlKaisa M. Lindahl wrote:
> Can I copy these from a different server, and register them manually?
> Or will they be installed and registered if I run the PTSFull.exe
> from the Analysis Services installation? What is the best way of
> doing this?
Installing of ptsfull should solve your problems
frank|||"Frank Matthiesen" <fm@.xax.de> wrote in message
news:3lepd6F12a6brU1@.individual.net...
> Kaisa M. Lindahl wrote:
>> Can I copy these from a different server, and register them manually?
>> Or will they be installed and registered if I run the PTSFull.exe
>> from the Analysis Services installation? What is the best way of
>> doing this?
> Installing of ptsfull should solve your problems
> frank
Thanks. I installed PTSFull.exe and the report is working. I can't find any
good MS articles or notion of this in the Books online, though. It should be
noted somewhere, so it doesn't come as a surprise to someone who installs RS
alone. Or maybe I just didn't look in the right places. Anyone got a link to
an article or something about this?
Kaisa