Interfacing SSRS Deep Integration to Sharepoint - 3rd time lucky?!

I am looking for the actual interface that will allow me to interface with
the SSRS Web Part from the Sharepoint Deep Integration suite. It appears that
it needs a doclib interface, I guessed it was the IListProvder but now find
it is not and not even IRowProvider. Can you / they tell me what it needs to
be to properly interface with it both from the
'Get Report Definitions From' connection
but also
'Get Report Parameters From' connection
For The latter I also need to know how to marry up the Parameters in say a
Filter Web Part with the SSRS Web Part Deep Integration.
Now have finally configured no spam alias and set up profile - will this
make a difference to the response time! Come on Microsoft you can do this,
not rocket science surely?
--
Steve G16 days and counting still no response - what happened to 48 hours response
promise?
--
Steve G
"ezeget.com" wrote:
> I am looking for the actual interface that will allow me to interface with
> the SSRS Web Part from the Sharepoint Deep Integration suite. It appears that
> it needs a doclib interface, I guessed it was the IListProvder but now find
> it is not and not even IRowProvider. Can you / they tell me what it needs to
> be to properly interface with it both from the
> 'Get Report Definitions From' connection
> but also
> 'Get Report Parameters From' connection
> For The latter I also need to know how to marry up the Parameters in say a
> Filter Web Part with the SSRS Web Part Deep Integration.
> Now have finally configured no spam alias and set up profile - will this
> make a difference to the response time! Come on Microsoft you can do this,
> not rocket science surely?
> --
> Steve G
>

Interface for Analysis Services 2005

Dear friends,

I have a problem…

1. How can I show the data from Analysis Services 2005? In ASP.Net 2.0 or Sharepoint 2007?
2. I have users and groups in tables from one database, how can control it in the interface in ASP.NET 2.0 or Sharepoint? (I’m cant use the asp.net 2.0 security controls) It’s easy to allow or deny users or groups to view some items as we can do in asp.net 2.0 controls?

Thanks!!

Pedro,

I do not know about ASP.NET, but you can build reports using Excel 2007 and use Sharepoint as report repository.

High level design:

- In Sharepoint you create connection library(s) that point to appropriate cubes/perspectives in SSAS 2005

- In Sharepoint you create report library(s)

- You create reports using connections from Sharepoint connection library and save them in Sharepoint report library

- You can use SharePoint Excel services for users to see reports with some limited interactivity. Users can use these reports just using browser, no need for Excel. And reports/charts look very similar to what you would see in Excel.

In Sharepoint you would use Integrated security to allow/deny access to reports. But of course you would have to setup proper role security in SSAS 2005 first.

Vidas Matelis

|||

Thanks Vidas for your help.

Note, that I'm using excel 2003, and I will allow users to use the report builder... and I'm very confused aboout the previligies for each user...

I have the user and groups in a OLTP database...

regards

|||

In SSAS 2005 you have security roles where you assing permissions to windows users group. So there you will define if specific windows user/group can access that cube.

Another level of security will be for reports. In sharepoint you can put reports to different report libraries or different report folders and assign if user/group can access that library or folder.

Keep in mind that if user have access to cube, he/she can just start Excel and connect to SSAS to query that cube. Report permissions should go hand in hand with cube/dimension permissions.

If you have excel 2003, I do not believe you will be able to use Excel Services in SharePoint 2007.

As you already know, Excel 2003 does not work very well with SSAS 2005. It is more in presentation, than calculation. You see all attributes as dimensions, and measures are not groupped by measure group and/or folder. So for bigger databases this is big problem.

Vidas Matelis

|||

Thanks for your post...

But how can I control the access to the cubes or reports, if I have the users and groups inside tables and not in the active directory?

Thanks

|||

Pedro,

Analysis Services can use just integrated security. So you cannot change SSAS security based on users saved in SQL Server table.

Vidas Matelis

|||

> Analysis Services can use just integrated security. So you cannot change SSAS security based on users saved in SQL Server table.

Unless you control middle tier, which seems to be a scenario here. It would involve some ASP.NET coding, but it is possible to use users from SQL table. For more information, please read about Roles property and for more dynamic scenarios - CustomData property and MDX function.

|||

Pedro, sorry I misguided you. I based my answer on BOL information, but Mosha pointed that there are ways to do it.

Mosha,

Any published papers on this? Any examples on how this could be done?

I googled it, but cannot find much more detail information.

Thank you,

Vidas Matelis

|||

yes... mosha, do you have some example? or links about the subject?

Thanks both!

|||

MOsha,

Supose I use integrated security, where I can have the groups and roles? The groups are in Active Directory as the users?

Regards!

|||In the middle tier (usually ASP.NET app), after you authenticated the user, you can look up in the SQL table or in AD or somewhere else what are the roles he should belong to. Then you create ADOMD.NET connection for that user passing "Roles=Role1,Role2,Role3" connection string parameter. If static role assignment doesn't work for you, you can pass user id through CustomData property, i.e. "CustomData=appuser1", and then inside security definitions you can use CustomData() MDX function which will resolve to the value passed in the property.|||

Mosha,

Could you please confirm if my understanding for Role property is right:

- For this to work middle tier should have full (or at least some) access to SSAS database.

- User does not need to have access to SSAS database, as it is queried by middle tier.

- Including role parameter in connection string will further limit middle tier access to SSAS based on roles specified.

- Will there be any penalty for performance using this? I know that years back when connection string to relational DB did not matched exactly, then you could not reuse cache betten connection.

Using CustomData() function, is my understanding right:

- I would disable access to direct measures to users.

- I would create calculated measures and use CustomData() function to limit what values are available.

- With this approach I cannot hide dimensions, hierarchies, cubes, just calculated measure values.

Did I understand right?

Thank you,

Vidas Matelis

|||

For Roles property your understanding is correct.

> Will there be any penalty for performance using this? I know that years back when connection string to relational DB did not matched exactly, then you could not reuse cache betten connection.

You won't be able to reuse connections with different Roles set on them. The FE caches cannot be reused as well (SE caches can be reused sometimes). For more information check out the SQL Server 2005 Analysis Services book, page 511 - it has good explanation of this subject.

> Using CustomData() function, is my understanding right:

- I would disable access to direct measures to users.

- I would create calculated measures and use CustomData() function to limit what values are available.

- With this approach I cannot hide dimensions, hierarchies, cubes, just calculated measure values.

You misunderstood how CustomData can be used for security. Just think about it as a replacement for the UserName() function in dynamic security when your authentication is not Windows Integrated but a custom one.

|||

Mosha,

Thank you! This was very helpful.

Vidas Matelis

Integration with Sharepoint Server 2007 - Second instance of servi

Ok, I have a SQL 2005 sp2 server running reporting service on one server, and
Sharepoint Portal 2007 on another server. As I have reports setup and want
to continue using reports manager, I installed a second instance of Report
services on my SQL server. I installed Portal server on the SQL server (as
front end server) and it appears as a member of the Sharepoint farm. I setup
a new database using the Reporting services Config wizard in integrated mode.
I am using a domain user for the windows and web service accounts.
On the sql server when browsing to the virtual directory for the reports
server "http://servername/reportserver" I get the following error:
"The report server has encountered a configuration error. See the report
server log files for more information. (rsServerConfigurationError) Get
Online Help "
from other network locations, I get a request for authentication that always
fails.
The log file contains the following:
w3wp!webserver!1!6/11/2007-10:24:13:: i INFO: Reporting Web Server started
w3wp!library!1!6/11/2007-10:24:13:: i INFO: Initializing ConnectionType to
'0' as specified in Configuration file.
w3wp!library!1!6/11/2007-10:24:13:: i INFO: Initializing IsSchedulingService
to 'True' as specified in Configuration file.
w3wp!library!1!6/11/2007-10:24:13:: i INFO: Initializing
IsNotificationService to 'True' as specified in Configuration file.
w3wp!library!1!6/11/2007-10:24:13:: i INFO: Initializing IsEventService to
'True' as specified in Configuration file.
w3wp!library!1!6/11/2007-10:24:13:: i INFO: Initializing PollingInterval to
'10' second(s) as specified in Configuration file.
w3wp!library!1!6/11/2007-10:24:13:: i INFO: Initializing
WindowsServiceUseFileShareStorage to 'False' as specified in Configuration
file.
w3wp!library!1!6/11/2007-10:24:13:: i INFO: Initializing MemoryLimit to '60'
percent as specified in Configuration file.
w3wp!library!1!6/11/2007-10:24:13:: i INFO: Initializing RecycleTime to
'720' minute(s) as specified in Configuration file.
w3wp!library!1!6/11/2007-10:24:13:: i INFO: Initializing MaximumMemoryLimit
to '80' percent as specified in Configuration file.
w3wp!library!1!6/11/2007-10:24:13:: i INFO: Initializing
MaxAppDomainUnloadTime to '30' minute(s) as specified in Configuration file.
w3wp!library!1!6/11/2007-10:24:13:: i INFO: Initializing MaxQueueThreads to
'0' thread(s) as specified in Configuration file.
w3wp!library!1!6/11/2007-10:24:13:: i INFO: Initializing IsWebServiceEnabled
to 'True' as specified in Configuration file.
w3wp!library!1!6/11/2007-10:24:13:: i INFO: Initializing
MaxActiveReqForOneUser to '20' requests(s) as specified in Configuration file.
w3wp!library!1!6/11/2007-10:24:13:: i INFO: Initializing MaxScheduleWait to
'5' second(s) as specified in Configuration file.
w3wp!library!1!6/11/2007-10:24:13:: i INFO: Initializing
DatabaseQueryTimeout to '120' second(s) as specified in Configuration file.
w3wp!library!1!6/11/2007-10:24:13:: i INFO: Initializing
ProcessRecycleOptions to '0' as specified in Configuration file.
w3wp!library!1!6/11/2007-10:24:13:: i INFO: Initializing
RunningRequestsScavengerCycle to '60' second(s) as specified in Configuration
file.
w3wp!library!1!6/11/2007-10:24:13:: i INFO: Initializing
RunningRequestsDbCycle to '60' second(s) as specified in Configuration file.
w3wp!library!1!6/11/2007-10:24:13:: i INFO: Initializing RunningRequestsAge
to '30' second(s) as specified in Configuration file.
w3wp!library!1!6/11/2007-10:24:13:: i INFO: Initializing CleanupCycleMinutes
to '10' minute(s) as specified in Configuration file.
w3wp!library!1!6/11/2007-10:24:13:: i INFO: Initializing
DailyCleanupMinuteOfDay to default value of '120' minutes since midnight
because it was not specified in Configuration file.
w3wp!library!1!6/11/2007-10:24:13:: i INFO: Initializing WatsonFlags to
'1064' as specified in Configuration file.
w3wp!library!1!6/11/2007-10:24:13:: i INFO: Initializing
WatsonDumpOnExceptions to
'Microsoft.ReportingServices.Diagnostics.Utilities.InternalCatalogException,Microsoft.ReportingServices.Modeling.InternalModelingException'
as specified in Configuration file.
w3wp!library!1!6/11/2007-10:24:13:: i INFO: Initializing
WatsonDumpExcludeIfContainsExceptions to
'System.Data.SqlClient.SqlException,System.Threading.ThreadAbortException'
as specified in Configuration file.
w3wp!library!1!6/11/2007-10:24:13:: i INFO: Initializing
SecureConnectionLevel to '0' as specified in Configuration file.
w3wp!library!1!6/11/2007-10:24:13:: i INFO: Initializing DisplayErrorLink to
'True' as specified in Configuration file.
w3wp!library!1!6/11/2007-10:24:13:: i INFO: Initializing
WebServiceUseFileShareStorage to 'False' as specified in Configuration file.
w3wp!resourceutilities!1!6/11/2007-10:24:13:: i INFO: Reporting Services
starting SKU: Standard
w3wp!resourceutilities!1!6/11/2007-10:24:13:: i INFO: Evaluation copy: 0
days left
w3wp!resourceutilities!1!6/11/2007-10:24:13:: i INFO: Running on 1 physical
processors, 2 logical processors
w3wp!runningjobs!1!6/11/2007-10:24:13:: i INFO: Database Cleanup (Web
Service) timer enabled: Next Event: 600 seconds. Cycle: 600 seconds
w3wp!runningjobs!1!6/11/2007-10:24:13:: i INFO: Running Requests Scavenger
timer enabled: Next Event: 60 seconds. Cycle: 60 seconds
w3wp!runningjobs!1!6/11/2007-10:24:13:: i INFO: Running Requests DB timer
enabled: Next Event: 60 seconds. Cycle: 60 seconds
w3wp!runningjobs!1!6/11/2007-10:24:13:: i INFO: Memory stats update timer
enabled: Next Event: 60 seconds. Cycle: 60 seconds
w3wp!library!1!06/11/2007-10:24:14:: e ERROR: Throwing
Microsoft.ReportingServices.Diagnostics.Utilities.ServerConfigurationErrorException:
The report server has encountered a configuration error. See the report
server log files for more information., SharePoint content service is null.
Report Server may not have joined the SharePoint farm, or Report Server
service account may not have been granted access to farm.;
Info:
Microsoft.ReportingServices.Diagnostics.Utilities.ServerConfigurationErrorException:
The report server has encountered a configuration error. See the report
server log files for more information.
w3wp!library!7!6/11/2007-10:25:13:: i INFO: Catalog SQL Server Edition = Standard
I have read a large number of posts, and went through installation steps
repeatedly. I setup SPN for the webservice account. I just can't see what I
am missing.
Please advise.
--
Fredrick A. Zilz
Director IT
InterHealth N.I.I made one step forward. I found I had a duplicate SPN which was preventing
a log in to the Reportserver web site. So I can now browse to the
http://machinename/reportserver site. I am, however still not able to set
the default settings on the Sharepoint Portal side. I may need to move this
to a sharepoint group.
Thanks in advance for any advice.
--
Fredrick A. Zilz
Director IT
InterHealth N.I.
"Fred Zilz" wrote:
> Ok, I have a SQL 2005 sp2 server running reporting service on one server, and
> Sharepoint Portal 2007 on another server. As I have reports setup and want
> to continue using reports manager, I installed a second instance of Report
> services on my SQL server. I installed Portal server on the SQL server (as
> front end server) and it appears as a member of the Sharepoint farm. I setup
> a new database using the Reporting services Config wizard in integrated mode.
> I am using a domain user for the windows and web service accounts.
> On the sql server when browsing to the virtual directory for the reports
> server "http://servername/reportserver" I get the following error:
> "The report server has encountered a configuration error. See the report
> server log files for more information. (rsServerConfigurationError) Get
> Online Help "
> from other network locations, I get a request for authentication that always
> fails.
> The log file contains the following:
> w3wp!webserver!1!6/11/2007-10:24:13:: i INFO: Reporting Web Server started
> w3wp!library!1!6/11/2007-10:24:13:: i INFO: Initializing ConnectionType to
> '0' as specified in Configuration file.
> w3wp!library!1!6/11/2007-10:24:13:: i INFO: Initializing IsSchedulingService
> to 'True' as specified in Configuration file.
> w3wp!library!1!6/11/2007-10:24:13:: i INFO: Initializing
> IsNotificationService to 'True' as specified in Configuration file.
> w3wp!library!1!6/11/2007-10:24:13:: i INFO: Initializing IsEventService to
> 'True' as specified in Configuration file.
> w3wp!library!1!6/11/2007-10:24:13:: i INFO: Initializing PollingInterval to
> '10' second(s) as specified in Configuration file.
> w3wp!library!1!6/11/2007-10:24:13:: i INFO: Initializing
> WindowsServiceUseFileShareStorage to 'False' as specified in Configuration
> file.
> w3wp!library!1!6/11/2007-10:24:13:: i INFO: Initializing MemoryLimit to '60'
> percent as specified in Configuration file.
> w3wp!library!1!6/11/2007-10:24:13:: i INFO: Initializing RecycleTime to
> '720' minute(s) as specified in Configuration file.
> w3wp!library!1!6/11/2007-10:24:13:: i INFO: Initializing MaximumMemoryLimit
> to '80' percent as specified in Configuration file.
> w3wp!library!1!6/11/2007-10:24:13:: i INFO: Initializing
> MaxAppDomainUnloadTime to '30' minute(s) as specified in Configuration file.
> w3wp!library!1!6/11/2007-10:24:13:: i INFO: Initializing MaxQueueThreads to
> '0' thread(s) as specified in Configuration file.
> w3wp!library!1!6/11/2007-10:24:13:: i INFO: Initializing IsWebServiceEnabled
> to 'True' as specified in Configuration file.
> w3wp!library!1!6/11/2007-10:24:13:: i INFO: Initializing
> MaxActiveReqForOneUser to '20' requests(s) as specified in Configuration file.
> w3wp!library!1!6/11/2007-10:24:13:: i INFO: Initializing MaxScheduleWait to
> '5' second(s) as specified in Configuration file.
> w3wp!library!1!6/11/2007-10:24:13:: i INFO: Initializing
> DatabaseQueryTimeout to '120' second(s) as specified in Configuration file.
> w3wp!library!1!6/11/2007-10:24:13:: i INFO: Initializing
> ProcessRecycleOptions to '0' as specified in Configuration file.
> w3wp!library!1!6/11/2007-10:24:13:: i INFO: Initializing
> RunningRequestsScavengerCycle to '60' second(s) as specified in Configuration
> file.
> w3wp!library!1!6/11/2007-10:24:13:: i INFO: Initializing
> RunningRequestsDbCycle to '60' second(s) as specified in Configuration file.
> w3wp!library!1!6/11/2007-10:24:13:: i INFO: Initializing RunningRequestsAge
> to '30' second(s) as specified in Configuration file.
> w3wp!library!1!6/11/2007-10:24:13:: i INFO: Initializing CleanupCycleMinutes
> to '10' minute(s) as specified in Configuration file.
> w3wp!library!1!6/11/2007-10:24:13:: i INFO: Initializing
> DailyCleanupMinuteOfDay to default value of '120' minutes since midnight
> because it was not specified in Configuration file.
> w3wp!library!1!6/11/2007-10:24:13:: i INFO: Initializing WatsonFlags to
> '1064' as specified in Configuration file.
> w3wp!library!1!6/11/2007-10:24:13:: i INFO: Initializing
> WatsonDumpOnExceptions to
> 'Microsoft.ReportingServices.Diagnostics.Utilities.InternalCatalogException,Microsoft.ReportingServices.Modeling.InternalModelingException'
> as specified in Configuration file.
> w3wp!library!1!6/11/2007-10:24:13:: i INFO: Initializing
> WatsonDumpExcludeIfContainsExceptions to
> 'System.Data.SqlClient.SqlException,System.Threading.ThreadAbortException'
> as specified in Configuration file.
> w3wp!library!1!6/11/2007-10:24:13:: i INFO: Initializing
> SecureConnectionLevel to '0' as specified in Configuration file.
> w3wp!library!1!6/11/2007-10:24:13:: i INFO: Initializing DisplayErrorLink to
> 'True' as specified in Configuration file.
> w3wp!library!1!6/11/2007-10:24:13:: i INFO: Initializing
> WebServiceUseFileShareStorage to 'False' as specified in Configuration file.
> w3wp!resourceutilities!1!6/11/2007-10:24:13:: i INFO: Reporting Services
> starting SKU: Standard
> w3wp!resourceutilities!1!6/11/2007-10:24:13:: i INFO: Evaluation copy: 0
> days left
> w3wp!resourceutilities!1!6/11/2007-10:24:13:: i INFO: Running on 1 physical
> processors, 2 logical processors
> w3wp!runningjobs!1!6/11/2007-10:24:13:: i INFO: Database Cleanup (Web
> Service) timer enabled: Next Event: 600 seconds. Cycle: 600 seconds
> w3wp!runningjobs!1!6/11/2007-10:24:13:: i INFO: Running Requests Scavenger
> timer enabled: Next Event: 60 seconds. Cycle: 60 seconds
> w3wp!runningjobs!1!6/11/2007-10:24:13:: i INFO: Running Requests DB timer
> enabled: Next Event: 60 seconds. Cycle: 60 seconds
> w3wp!runningjobs!1!6/11/2007-10:24:13:: i INFO: Memory stats update timer
> enabled: Next Event: 60 seconds. Cycle: 60 seconds
> w3wp!library!1!06/11/2007-10:24:14:: e ERROR: Throwing
> Microsoft.ReportingServices.Diagnostics.Utilities.ServerConfigurationErrorException:
> The report server has encountered a configuration error. See the report
> server log files for more information., SharePoint content service is null.
> Report Server may not have joined the SharePoint farm, or Report Server
> service account may not have been granted access to farm.;
> Info:
> Microsoft.ReportingServices.Diagnostics.Utilities.ServerConfigurationErrorException:
> The report server has encountered a configuration error. See the report
> server log files for more information.
> w3wp!library!7!6/11/2007-10:25:13:: i INFO: Catalog SQL Server Edition => Standard
>
> I have read a large number of posts, and went through installation steps
> repeatedly. I setup SPN for the webservice account. I just can't see what I
> am missing.
> Please advise.
>
> --
> Fredrick A. Zilz
> Director IT
> InterHealth N.I.

Integration of RS with Sharepoint

Good Day ,

May I know if there is any sites which explains the integration of RS with SPS?

Regards

Alu

Basically, I would like a user using the SPS to query and generate a report....with or without RS?

Regards

Alu

|||

You need to use a report viewer web part

http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/webrssp2.mspx

|||

thanks.

does this applies to sql 2005?

|||Yes. There may be minor differences but on the whole it still applies|||

without using report viewer/exploroer from RS web part...is there other means of accessing sql server database directly from SPS (without going thru RS)?

Regards

Alu

|||You can utilise URL access to the report server or query the web service directly|||

With SQL Server 2005 Service Pack 2, we will allow for you to publish reports directly to SharePoint 2007. I will blog on this soon at http://blogs.msdn.com/bwelcker.

|||

correct me if i am wrong ...report server is part of the RS package...and does it means that the service pack 2 can do without RS and generate reports directly from SPS?

Thanks.

Regards

Alu

|||

Hi can anyone explain why do we need an RS as a layer between db and sharepoint when we can directly code or using infopath or generate the required report?

Regards

Alu

|||

I find RS quite handy as middleware between SPS and SQL

we have SPS 2003, RS2005, SQL2005 and with lack of security in SPS 2003, we can customize security in RS per report/folder/group, etc...

Plus, having RS in the middle means you should be able to move the frontend SPS, or the backend SQL easily, as long as RS stays the same (we have SPS, RS, SQL on 3+ different servers)

Plus, designing reports in VS 2005 is much easier, like Crystal Reports

There are reasons why reporting software exists (or why people use Crystal Reports)

|||

May I know the means for communicating the to the database from Sharepoint to generate reports based on the input by the users in Sharepoint sites?

Regards

Alu

|||In this scenario, SharePoint does not talk to the data source directly. It talks to the Report Server (through web services), which, in turn, talks to the database to get the data. The Report Server then processes and renders the report and returns the output to SharePoint.|||

Hi I have just tried the Report Designer from VS2005 and depoly to RS..and it seems like whenever I have a parameter to be passed by the user interface..it will create a 'txtbox' on top of the web page..automatically.

Q1: What about controls like combo boxes and checkboxes ..is it possible to create that kind of a user input interface as well? ( where combo boxes data are retrieved from the database. ( and if its possible..all codings are done in xml ie VS2005 or asp.net 2.0)

Q2: Is the format of the user input interface a fixture? ie in position context. Can we reorganised the position to a desired one? (again some kind of coding is needed somewhere?)

Thanks.

|||

By the way I have put up a new thread regarding a issue on my RS and sql server2005..not sure if I have put up the right post on the right track...:) It happens that I have installed RS on a windows server 2003 and the db on my client machine. From WS2003, I could see my db and can connect sucessfully (have add the browser service and sql server serivces on the Exception tabs under firewall) However when i try to gerenate reports from my own machine, I hit the error...Cannot connect to the <databasename>....Seems like the configuration settings are wrong...been trying to look for the right Web.Config and not sure where it could be found :( ...since I find many of this files in different location:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=875380&SiteID=1

Regards

Alu

Integration of Analysis service to sharepoint

Is there any sites or demo on how to use sharepoint with the analysis services in sql server 2005. Does the reports generated requires RS as well?

Thanks,

Regards

Alu

There is an effort underway to make AdventureWorks dashboard demo available publicly but I don't know the details yet. Most likely it will coincide with the SharePoint 2007 release.

You should be able to plug-in any URL-addressable report in SPS. Of course, RS reports will work best :-) More details in the forthcoming CTP 2 of SQL Server SP2.