« October 2007 | Main | January 2008 »
November 14, 2007
Passing parameters to Reports in MOSS 2007 Integrated Mode
byDavid Macleary, Business Intelligence Developer, RDA Corporation
Steven Mann, Principal Architect - Business Intelligence Solutions, RDA Corporation
Background
We have a MOSS 2007 (SharePoint) site with Reporting Services installed using Integrated Mode. The reports are therefore stored within SharePoint. We wanted to be able to open reports within a new browser window. These reports, however, take parameters; so obviously we wanted to pass parameters to these reports, which again, live in SharePoint because we are running Reporting Services in Integrated Mode.
Initial Results
After an hour long call with Microsoft, the end result is that they do not support passing any parameters or instructive arguments via the URL string when a report is stored/hosted in a document library in SharePoint 2007 with SSRS in Integrated mode. It works fine in native mode (no SharePoint involved), but if you upgrade to the latest amp; greatest Integrated SharePoint 07 mode (sql 05 sp2), you’re stuck.
An example of what we are talking about - the red part of this URL is ignored: (amp;KeyDate=10/5/2007amp;EmpID=2347)
https://www.myMOSSServer.com/RS/_layouts/ReportServer/RSViewerPage.aspx?RelativeReportUrl=/RS/Recruiting/test.rdlamp;KeyDate=10/5/2007amp;EmpID=2347
In Integrated mode, you can only pass parameters using the “Jump To Report†method in the VS Report Designer.
“Jump To URL†will still open a report but it ignores all command line arguments such as all individual parameters and any window directives (hide toolbar, blank window, etc). The main rub with using “Jump To Report†is that it doesn’t open the drilldown report in a new window †it replaces the currently loaded window and that’s a major downer when a management user has a chart on screen and wants to keep the original report loaded and drill into all the different bars of each chart. Each click should ideally open up in a new tab/window, not blow away the original report. Then they have click IE’s back button after viewing the drilldown and it re-renders the original report. Very slow and clunky user experience.
"THE WORKAROUND"
We found a back-door way to get this to work. All of our readings thus far indicated you had to go through MOSS to fetch the report since it was stored in the MOSS library and not the report server database (and MOSS brokered all the communication and security for you to the RS Server). However, if we reference the ReportServer web app directly and append the full URL to the doc library report and pass the parameters - it accepts them! See below for an example:
This works:
https://www.myRSServer.com/ReportServer?https://www.myMOSSServer.com/mysite/doclibrary/test.rdlamp;KeyDate=1/4/07
where this does not:
https://www.myMOSSServer.com/mysite/_layouts/ReportServer/RSViewerPage.aspx?RelativeReportUrl=/mysite/doclibrary/test.rdlamp;KeyDate=1/4/07
It’s not perfect; but it actually does work and that’s the important note. Drawbacks with this method are that you lose your frame of reference within SharePoint (breadcrumb bar is gone, the ‘pretty blue theme’ of the site is gone, and worst of all you still have to manually code your MOSS URL into each drilldown link with VS Report Designer since there’s no global variable to use) but for a one-off drilldown type of report I think this is an acceptable tradeoff…
...for now.
Share:
Posted by Steve Mann at 5:36 PM | Comments (1)
November 13, 2007
Configuring the Configuration Database Connection String
by Ray Barley, Principal Architect - Business Intelligence Solutions, RDA Corporation
While storing the package configuration in a SQL Server database table works well, how do you dynamically configure the connection string to the database? Use the Environment Variable Package Configuration to store the connection string.
Start the Package Configuration Wizard (right click on Control Flow design surface).
Start the Package Configuration Wizard (right click on Control Flow design surface).
Select Environment variable as the Configuration type and pick an environment variable from the drop down list. Note: The drop down only shows environment variables that existed when you launched Visual Studio. If you create an environment variable after launching Visual Studio you need to exit and restart before you will see the new environment variable.
Click Next to navigate to the Select Properties to Export dialog. Select the ConnectionString of your configuration database. Note that an Environment Variable Package Configuration can be used to only set a single property.
Finally make sure the Environment Variable Package Configuration is first in the list of configurations; this is required so that your configuration connection string gets set first.
Share:
Posted by Steve Mann at 6:35 PM | Comments (0)
SSIS - Package Configurations - Best Practices - Part #3 (Configuring the DB Connection)
by Ray Barley, Principal Architect - Business Intelligence Solutions, RDA Corporation
Configuring the Configuration Database Connection String
While storing the package configuration in a SQL Server database table works well, how do you dynamically configure the connection string to the database? Use the Environment Variable Package Configuration to store the connection string.
Start the Package Configuration Wizard (right click on Control Flow design surface).
Start the Package Configuration Wizard (right click on Control Flow design surface).
Select Environment variable as the Configuration type and pick an environment variable from the drop down list. Note: The drop down only shows environment variables that existed when you launched Visual Studio. If you create an environment variable after launching Visual Studio you need to exit and restart before you will see the new environment variable.
Click Next to navigate to the Select Properties to Export dialog. Select the ConnectionString of your configuration database. Note that an Environment Variable Package Configuration can be used to only set a single property.
Finally make sure the Environment Variable Package Configuration is first in the list of configurations; this is required so that your configuration connection string gets set first.
Share:
Posted by Steve Mann at 6:35 PM | Comments (0)
November 12, 2007
SSIS: Expressions Trick
by Jim Pletscher, Business Intelligence Senior Consultant, RDA CorporationDon't know if this is obvious to others, but thought I'd share...
One often frustrating thing about using expressions in SSIS is that the property that you define with the expression looks just like it was hard-coded in the component. This is particularly deceiving when you've used an expression for a SQL statement as you can open and even edit the SQL, but the next time you open the package, your edits are gone(!) because the expression overrides it.
So, as a trick, I've taken to putting the comment "-- This is defined by an expression" as the first line in any of my SQL. Now, when I open the component, that comment is what shows in the SQL property and I have a reminder of where to look to edit the code.
Share:
Posted by Steve Mann at 5:14 PM | Comments (0)
SSIS Add-ons (Third Party)
Here's one company that's made several interesting SSIS add-ons, including a ZIP component, dataflow source and destination adapters so that data can be passed from within memory, SecureFTP, and others: http://www.cozyroc.com/
Share:
Posted by Steve Mann at 5:10 PM | Comments (0)
Authentication Issues accessing Report Builder via the Report Explorer Web Part in MOSS 2007
by Steve Mann, Principal Architect - Business Intelligence Solutions, RDA Corporation
Scenario: When accessing Reporting Services components through SharePoint in an Active Directory (AD) environment, there may be authentication issues especially when attempting to access Report Builder. When accessing the same components from Report Manager directly, the authentication issues are not present.
Solution: The Reports and ReportServer web sites need to digest the AD credentials. This is configured within Internet Information Services (IIS).
Open IIS and look under the Web Sites folder. Find the Reports and Report Server web sites.
The next steps should be completed for both web sites.
Right click the web site and select properties.
Under the Directory Security Tab, select Edit within the Authentication and Access Control.
Make sure the "Digest Authentication...." option is checked. You will receive a message in regards to Active Directory. Click OK and close the properties dialog.
Run an IISRESET command to make sure the changes are picked up by the web site.
Users should now be able to access Reporting Services components that they have access rights to via SharePoint.
Share:
Posted by Steve Mann at 2:46 PM | Comments (0)
