Passing OLAP-parameters between reports

I have to reports, ReportA and ReportB. Both uses datasets that are based on
OLAP cubes. Both reports has parameters and works fine.
My challenge:
When clicking in a field in ReportA, I want to navigate to ReportB, sending
the appropriate parameters from ReportA to ReportB. The first parameter I
send is the value of a fiield "Location", for instance "Seattle". However,
when I send this to reportB (which takes, amongst others, a location
parameter), the value "Seattle" is sent, and not the value which is
understood by ReportB's parameter, that is "[Dim Location].[Location].1".
To accomplsh my task I think I somehow need to print the value "[Dim
Location].[Location].1" in a hidden field in ReportA and send this fields
value to ReportB. But how do get both Seattle and [Dim
Location].[Location].1" as member values of the same OLAP dimension?
Hope I made my self clear enough here. It is a bit difficult to explain, and
don't hesitate to ask me if there is something you didn't understand.
Thanks in advance for your helpYou can return the OLAP dimension member's Unique name in your query, like
this:
with member [Measures].[MyName] as '[Gender].currentmember.name'
member [Measures].[MyLevelOrdinal] as '[Gender].currentmember.Level.Ordinal'
member [Measures].[MyUniqueName] as '[Gender].currentmember.UniqueName'
member [Measures].[MyMeasure4] as '[Measures].[Unit Sales]'
member [Measures].[MyMeasure5] as '[Measures].[Store Cost]'
member [Measures].[MyMeasure6] as '[Measures].[Store Sales]'
select {[Measures].[MyName], [Measures].[MyLevelOrdinal],
[Measures].[MyUniqueName], [Measures].[MyMeasure4], [Measures].[MyMeasure5],
[Measures].[MyMeasure6]} on columns,
{filter([Gender].members, [Measures].[Unit Sales] > 0) } on rows
from [Sales]
(try with Foodmart 2000)
In your case, this should return something like
MyName = Seattle
MyLevelOrdinal = Seattle's level ordinal
MyUniquName = [Dim Location].[Location].1
LIke: member [Measures].[MyUniqueName] as '[Dim
Location].currentmember.UniqueName'
Kaisa M. Lindahl
"Billy" <Billy@.discussions.microsoft.com> wrote in message
news:35966F0A-9112-487A-A8EC-BF6E3F2E39C3@.microsoft.com...
>I have to reports, ReportA and ReportB. Both uses datasets that are based
>on
> OLAP cubes. Both reports has parameters and works fine.
> My challenge:
> When clicking in a field in ReportA, I want to navigate to ReportB,
> sending
> the appropriate parameters from ReportA to ReportB. The first parameter I
> send is the value of a fiield "Location", for instance "Seattle". However,
> when I send this to reportB (which takes, amongst others, a location
> parameter), the value "Seattle" is sent, and not the value which is
> understood by ReportB's parameter, that is "[Dim Location].[Location].1".
>
> To accomplsh my task I think I somehow need to print the value "[Dim
> Location].[Location].1" in a hidden field in ReportA and send this fields
> value to ReportB. But how do get both Seattle and [Dim
> Location].[Location].1" as member values of the same OLAP dimension?
> Hope I made my self clear enough here. It is a bit difficult to explain,
> and
> don't hesitate to ask me if there is something you didn't understand.
> Thanks in advance for your help

Passing a value between datasets?

Hi
Does anyone know if it is possible to use a value from one dataset as a
parameter to the query in a second dataset?
Example: Datset1 returns 8 rows, my report produces a page for each
row, but each page requires additional data relevant to that page of
the report.
Is there a way I can take (for instance) the current value of the
ReportType field from dataset1 and use that in a select statement in
Dataset2?
TIA
StewartWhat you are describing is perfect for subreports. Subreports are regular
reports that you can design and test by themselves. You create the report
with a parameter. Make sure it all works and then drag and drop the report
onto the main one. Do a right mouse click on the report, properties,
parameter tab and then map the parameter to the field of you first dataset.
The last thing I do is I hide the subreport for list view via report manager
(go to the properties of the report and there is a check box to do this).
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"sdm" <macneisd-www@.dcs.gla.ac.uk> wrote in message
news:1108719942.123540.216560@.c13g2000cwb.googlegroups.com...
> Hi
> Does anyone know if it is possible to use a value from one dataset as a
> parameter to the query in a second dataset?
> Example: Datset1 returns 8 rows, my report produces a page for each
> row, but each page requires additional data relevant to that page of
> the report.
> Is there a way I can take (for instance) the current value of the
> ReportType field from dataset1 and use that in a select statement in
> Dataset2?
> TIA
> Stewart
>|||Bruce
That sounds just what I need! Apologies for not acknowledging it
sooner, I'm just looking into RS in 'spare' moments.
Thanks very much for your reply, I really appreciate it.
Stewart|||Bruce, worked great, thanks again.
Stewart
Bruce L-C [MVP] wrote:
> What you are describing is perfect for subreports. Subreports are
regular
> reports that you can design and test by themselves. You create the
report
> with a parameter. Make sure it all works and then drag and drop the
report
> onto the main one. Do a right mouse click on the report, properties,
> parameter tab and then map the parameter to the field of you first
dataset.
> The last thing I do is I hide the subreport for list view via report
manager
> (go to the properties of the report and there is a check box to do
this).
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "sdm" <macneisd-www@.dcs.gla.ac.uk> wrote in message
> news:1108719942.123540.216560@.c13g2000cwb.googlegroups.com...
> > Hi
> >
> > Does anyone know if it is possible to use a value from one dataset
as a
> > parameter to the query in a second dataset?
> >
> > Example: Datset1 returns 8 rows, my report produces a page for
each
> > row, but each page requires additional data relevant to that page
of
> > the report.
> >
> > Is there a way I can take (for instance) the current value of the
> > ReportType field from dataset1 and use that in a select statement
in
> > Dataset2?
> >
> > TIA
> >
> > Stewart
> >

pass hidden parameter from one dataset to another

I have 2 datasets in my report and I want to pass a field from the 1st dataset as a parameter to the 2nd dataset.

How can I do this?

I tried to set the report parameter from a query using the 1st dataset but the parameter is always equal to the first record in the recordset-it never changes per row.

I used a subreport and passed it a parameter based on each row to get this to work.

pass data between two datasets

Is there anyway i can pass data between two datasets
ex:
dataset 1 has
select empid, fname,lname ,salary from employee
dataset2 has
select userid ,total/(dataset1.salary) from anothertable
here i want to use a value from dataset 1 in dataset 2.
Thanks in advnceLook into using subreports.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Ramani" <Ramani@.discussions.microsoft.com> wrote in message
news:DECA721E-7B10-4D19-B57C-BA92D951453F@.microsoft.com...
> Is there anyway i can pass data between two datasets
> ex:
> dataset 1 has
> select empid, fname,lname ,salary from employee
> dataset2 has
> select userid ,total/(dataset1.salary) from anothertable
> here i want to use a value from dataset 1 in dataset 2.
> Thanks in advnce|||Sub reports ? but i dont want to go into the front end. i want to do it in
the backend itself.
you know pass data from one data set to another directly
Thanks
"Bruce L-C [MVP]" wrote:
> Look into using subreports.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Ramani" <Ramani@.discussions.microsoft.com> wrote in message
> news:DECA721E-7B10-4D19-B57C-BA92D951453F@.microsoft.com...
> > Is there anyway i can pass data between two datasets
> >
> > ex:
> > dataset 1 has
> > select empid, fname,lname ,salary from employee
> >
> > dataset2 has
> > select userid ,total/(dataset1.salary) from anothertable
> >
> > here i want to use a value from dataset 1 in dataset 2.
> >
> > Thanks in advnce
>
>|||You can embed a sub report into the table of the first report passing it the
value from the field of the first one (the subreport should have a
parameter).
There is no way from within RS to tie one dataset to another. Either use
subreports or put the logic in a stored procedure (but since a stored
procedure can only return a single resultset, or rather RS will only use a
single resultset from a stored procedure) I don't think a stored procedure
will work for you.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Ramani" <Ramani@.discussions.microsoft.com> wrote in message
news:B4ED0F7F-4CCE-4484-B21F-8DD2B84EF0A3@.microsoft.com...
> Sub reports ? but i dont want to go into the front end. i want to do it in
> the backend itself.
> you know pass data from one data set to another directly
> Thanks
> "Bruce L-C [MVP]" wrote:
>> Look into using subreports.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "Ramani" <Ramani@.discussions.microsoft.com> wrote in message
>> news:DECA721E-7B10-4D19-B57C-BA92D951453F@.microsoft.com...
>> > Is there anyway i can pass data between two datasets
>> >
>> > ex:
>> > dataset 1 has
>> > select empid, fname,lname ,salary from employee
>> >
>> > dataset2 has
>> > select userid ,total/(dataset1.salary) from anothertable
>> >
>> > here i want to use a value from dataset 1 in dataset 2.
>> >
>> > Thanks in advnce
>>|||Ramani,
You really need to get all the data you want for a data region into one
dataset. You need to redo the query, there must be a relationship
between ds1 and ds2, or if it's just a continuous list of data try the
Union command.
Sub-reports require much greater overhead it's better not to use them
in this scenario.
Maybe try something like this;
Select e.empid, e.fname, e.lname, e.salary, a.userid, a.total
from employee e
join anothertable a on e.empid = a.userid
It may generate lots of repeated data, but RS can deal with this much
more efficiently than using sub-reports.
Chris
Ramani wrote:
> Is there anyway i can pass data between two datasets
> ex:
> dataset 1 has
> select empid, fname,lname ,salary from employee
> dataset2 has
> select userid ,total/(dataset1.salary) from anothertable
> here i want to use a value from dataset 1 in dataset 2.
> Thanks in advnce|||Easiest way may be to create a view between the 2.
"Ramani" wrote:
> Is there anyway i can pass data between two datasets
> ex:
> dataset 1 has
> select empid, fname,lname ,salary from employee
> dataset2 has
> select userid ,total/(dataset1.salary) from anothertable
> here i want to use a value from dataset 1 in dataset 2.
> Thanks in advnce