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

Pass two parameters in query

Hello,
I am new to SQL Server 2000 but proficient in Access, and the switch is
giving me a challenge. I am trying to do a simple parameter query, which I
believe is now called a View in SQLServer. In Access, I would have entered
in Criteria of RecordEntry this statement: Between [BeginningDate] and
[EndingDate]. Even when I view the query in Access' "SQL View", it is not
correct in correct code for SQL Server.
The field I want to have the user populate is UsageLog.RecordEntry, a
datetime field, so they can view only those records between the dates they
enter in the pop up window when the query is ran.
Here is the code:
SELECT dbo.UsageLog.*, dbo.UsageLog.RecordEntry AS Expr1
FROM dbo.ACFTSchedule INNER JOIN
dbo.UsageLog ON dbo.ACFTSchedule.ACFTSched =
dbo.UsageLog.FKACFTSched INNER JOIN
dbo.AirspaceArea ON dbo.UsageLog.FKAirspace =
dbo.AirspaceArea.AirspaceArea INNER JOIN
dbo.BaseSQD ON dbo.UsageLog.FKBaseSQD =
dbo.BaseSQD.BaseSQD INNER JOIN
dbo.ControlAgency ON dbo.UsageLog.FKControlType =
dbo.ControlAgency.ControlAgency INNER JOIN
dbo.MissionType ON dbo.UsageLog.FKMissionType =
dbo.MissionType.MissionType INNER JOIN
dbo.PlaneType ON dbo.UsageLog.FKPlaneType =
dbo.PlaneType.PlaneType
WHERE UsageLog.dbo.RecordType @.BeginningDate AND @.EndingDate
GO
The current error I am getting is saying that "Error in WHERE clause near
'UsageLog'. Unable to parse query text." Any assistance will be greatly
appreciated!
Thank you,
MaryMary, change the WHERE clause:
WHERE dbo.UsageLog.RecordType >= @.BeginningDate
AND dbo.UsageLog.RecordType <= @.EndingDate
OR
WHERE dbo.UsageLog.RecordType BETWEEN @.BeginningDate AND @.EndingDate
Cordially,
Richard_SQL
"Mary" wrote:

> Hello,
> I am new to SQL Server 2000 but proficient in Access, and the switch is
> giving me a challenge. I am trying to do a simple parameter query, which
I
> believe is now called a View in SQLServer. In Access, I would have entere
d
> in Criteria of RecordEntry this statement: Between [BeginningDate] and
> [EndingDate]. Even when I view the query in Access' "SQL View", it is not
> correct in correct code for SQL Server.
> The field I want to have the user populate is UsageLog.RecordEntry, a
> datetime field, so they can view only those records between the dates they
> enter in the pop up window when the query is ran.
> Here is the code:
> SELECT dbo.UsageLog.*, dbo.UsageLog.RecordEntry AS Expr1
> FROM dbo.ACFTSchedule INNER JOIN
> dbo.UsageLog ON dbo.ACFTSchedule.ACFTSched =
> dbo.UsageLog.FKACFTSched INNER JOIN
> dbo.AirspaceArea ON dbo.UsageLog.FKAirspace =
> dbo.AirspaceArea.AirspaceArea INNER JOIN
> dbo.BaseSQD ON dbo.UsageLog.FKBaseSQD =
> dbo.BaseSQD.BaseSQD INNER JOIN
> dbo.ControlAgency ON dbo.UsageLog.FKControlType =
> dbo.ControlAgency.ControlAgency INNER JOIN
> dbo.MissionType ON dbo.UsageLog.FKMissionType =
> dbo.MissionType.MissionType INNER JOIN
> dbo.PlaneType ON dbo.UsageLog.FKPlaneType =
> dbo.PlaneType.PlaneType
> WHERE UsageLog.dbo.RecordType @.BeginningDate AND @.EndingDate
> GO
> The current error I am getting is saying that "Error in WHERE clause near
> 'UsageLog'. Unable to parse query text." Any assistance will be greatly
> appreciated!
> Thank you,
> Mary|||Richard,
Thank you very much for your quick response. Should I be in the Views
window? I am getting an error saying that this variable @.BeginningDate need
s
to be declared.
In Access I would declare it with a Dim statement, but I am in
SQLServer.
Or should this be in a stored procedure? I want to be able to call this
query from a command button when we make the web front.
Thank you again,
Mary
"Richard" wrote:
> Mary, change the WHERE clause:
> WHERE dbo.UsageLog.RecordType >= @.BeginningDate
> AND dbo.UsageLog.RecordType <= @.EndingDate
> OR
> WHERE dbo.UsageLog.RecordType BETWEEN @.BeginningDate AND @.EndingDate
>
> Cordially,
> Richard_SQL
>
> "Mary" wrote:
>|||Richard,
The code you gave me for the WHERE statement worked great in a stored proc.
Thank you. But how do I run it to prompt me for the 2 inputs, so I can make
sure it works correctly?
The new stored procedure (below) and the syntax has been cleared by
SQLServer and it is visible in Query Analyzer and ran with the sp_help syste
m
stored proc.
CREATE PROCEDURE dbo.DateParameter
@.BDate datetime, @.EDate datetime
AS
SELECT dbo.UsageLog.*, dbo.UsageLog.RecordEntry AS Expr1
FROM dbo.ACFTSchedule INNER JOIN dbo.UsageLog ON
dbo.ACFTSchedule.ACFTSched = dbo.UsageLog.FKACFTSched INNER JOIN
dbo.AirspaceArea ON dbo.UsageLog.FKAirspace = dbo.AirspaceArea.AirspaceArea
INNER JOIN dbo.BaseSQD ON dbo.UsageLog.FKBaseSQD = dbo.BaseSQD.BaseSQD INNER
JOIN dbo.ControlAgency ON dbo.UsageLog.FKControlType =
dbo.ControlAgency.ControlAgency INNER JOIN dbo.MissionType ON
dbo.UsageLog.FKMissionType = dbo.MissionType.MissionType INNER JOIN
dbo.PlaneType ON dbo.UsageLog.FKPlaneType = dbo.PlaneType.PlaneType
WHERE dbo.UsageLog.RecordEntry >= @.BDate AND dbo.UsageLog.RecordEntry <=
@.EDate
GO
Thank you,
Mary :)
"Mary" wrote:
> Richard,
> Thank you very much for your quick response. Should I be in the Views
> window? I am getting an error saying that this variable @.BeginningDate ne
eds
> to be declared.
> In Access I would declare it with a Dim statement, but I am in
> SQLServer.
> Or should this be in a stored procedure? I want to be able to call this
> query from a command button when we make the web front.
> Thank you again,
> Mary
> "Richard" wrote:
>|||Mary,
You're welcome... Run this query:
DECLARE @.StartDate DATETIME,
@.EndDate DATETIME
SET @.StartDate = '2006-01-01' --YYYY-MM-DD
SET @.EndDate = '2006-03-22'
EXEC dbo.DateParameter @.BDate = @.StartDate, @.EDate = @.EndDate
go
Richard
"Mary" wrote:
> Richard,
> The code you gave me for the WHERE statement worked great in a stored proc
.
> Thank you. But how do I run it to prompt me for the 2 inputs, so I can ma
ke
> sure it works correctly?
> The new stored procedure (below) and the syntax has been cleared by
> SQLServer and it is visible in Query Analyzer and ran with the sp_help sys
tem
> stored proc.
> CREATE PROCEDURE dbo.DateParameter
> @.BDate datetime, @.EDate datetime
> AS
> SELECT dbo.UsageLog.*, dbo.UsageLog.RecordEntry AS Expr1
> FROM dbo.ACFTSchedule INNER JOIN dbo.UsageLog ON
> dbo.ACFTSchedule.ACFTSched = dbo.UsageLog.FKACFTSched INNER JOIN
> dbo.AirspaceArea ON dbo.UsageLog.FKAirspace = dbo.AirspaceArea.AirspaceAre
a
> INNER JOIN dbo.BaseSQD ON dbo.UsageLog.FKBaseSQD = dbo.BaseSQD.BaseSQD INN
ER
> JOIN dbo.ControlAgency ON dbo.UsageLog.FKControlType =
> dbo.ControlAgency.ControlAgency INNER JOIN dbo.MissionType ON
> dbo.UsageLog.FKMissionType = dbo.MissionType.MissionType INNER JOIN
> dbo.PlaneType ON dbo.UsageLog.FKPlaneType = dbo.PlaneType.PlaneType
> WHERE dbo.UsageLog.RecordEntry >= @.BDate AND dbo.UsageLog.RecordEntry <=
> @.EDate
> GO
> Thank you,
> Mary :)
>
> "Mary" wrote:
>