I have implemented a few reports against Analysis Services cubes in
reporting services. I have more detailed data contained in the cubes
in the underlying SQL datawarehouse. I have build a couple of reports
in RS against the datawarehouse tables as well. Now I want to link
these together some scenarios are in the cube I am storing data for
each product at a monthly level so if a user select a particular
product and month combination I want to trigger the reports against
the datawarehouse and pass the values like product# and month chosen
in the report against the cube to the report against the datawarehouse
to enable users to see all the data for the month at a more detailed
level.

Please can anybody share thoughts how this can be done in reporting
services. Currently we use Cognos reportnet we can do this we are
trying to migrate from Cognos reportnet to Reporting Services from a
standardisation and cost perspective.


Thanks
Karen

This MSDN paper has an example of Reporting Services "Drill-Through",
where an OLAP-based report can be used to launch another Reporting
Services Report, with the appropriate parameters. If the parameter
values for your existing relational reports don't match the names of
corresponding cube dimension members, string manipulation can be used:

http://msdn.microsoft.com/library/de.../en-us/dnsql2k
/html/olapasandrs.asp
...
Using the Reporting Services "Drill-through" Action

In the first example, we'll create a report action that is triggered
when a user selects a bar on a chart. The report for this example is
included in the file FoodmartDrillThrough.rdl. If you preview this
report, you'll find that the report contains a chart that displays the
sales for the selected product and time period for each city. If you
select any of the bars that represent the sales of a city, a report
action of type "drillthrough" is executed. This process opens the
FoodmartDrilledTo report that displays how each promotion contributed to
the overall sales of the selected product. The report parameters from
the initial report are passed to the second report and are displayed at
the top of the report for reference.

To see how this capability was added to the report, select the chart for
editing and right-click on the [Measures].[Unit Sales] data field.
Select the "Action" tab. This should indicate that the "Jump to Report"
property is set to the FoodmartDrilledTo report, and by viewing the
parameters option, you should notice that the "pTime" and "pProduct"
parameters of the second report are set to the value of the parameters
in the initial report. Finally, the last parameter listed, "pCity", is
set to the current value of the City field in the dataset.

This example serves to illustrate the concept of a "drill-through"
report for Reporting Services, but does not make use of the MDX
DRILLTHROUGH statement in Analysis Services. An MDX query is still used;
it's just not a DRILLTHROUGH query. It's also possible that the
drill-through action on the report could have triggered a web page
displaying weather forecasts for the selected city. The point is that
Reporting Services has drill-through capabilities that are independent
of the drill-through capabilities in Analysis Services.
...

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***

Are you trying to link from AS cubes to RS reports through actions?
If you are looking at integrating AS 2000 and RS 2000, you may want to check
the following MSDN article about how to use RS URL access in AS actions:
http://msdn.microsoft.com/SQL/sqlwar...olapasa_topic8

The good news is that setting up these actions and passing parameters will
be much easier in AS 2005 / RS 2005.

--
Robert M. Bruckner
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.



"Karen Middleton" <karenmiddleol (AT) yahoo (DOT) com> wrote

Rob & Deepak

Thanks very much for the update. This was one of our pressing needs as
business
objects is asking the business to switch from reporting services to
Business Objects.

I must say the support in reporting services for Analysis Services at
this point
please note we are still on SQL 2000 is very limited. We are already
using Reporting
Services and constantly we face issues from people like Business
Objects who are
coming to our IT Managers and recommending to switch from Reporting
Services
to Crystal. Their justification is in Microsoft the reporting services
and
Analysis Services and the Excel teams refuse to talk to each other and
the product
be it Excel reporting or reporting services reporting lack integration
with
SQL & analysis services what Crystal can achieve.

We do not want to move from reporting services the competition
constantly goes to
the business with the story of lack of integration.

Thanks