Friday, March 30, 2012
Passing parameter in MDX query
I am facing problem during passing the parameters in MDX query. I need tp
pass two parameter in my dataset, but not sure whether I am writing correct
query or not. as I am getting error during parsing the query.
here the query
SELECT {
[Measures].[SAT ARR], [Measures].[SAT Warranty ARR]
} on columns,
NON EMPTY NonEmptyCrossJoin(
[Component Parts].[Part].Members,
NonEmptyCrossJoin([Batch].[Batch].Members, [Building Code].[Building
Code].Members)
)
on rows FROM [CUBE NAME]
WHERE (" + Parameters!ManufactureDate.Value + ", " +
Parameters!Suppliername.Value + ")
--
The two parameter I have declared in parameter box with multiselect value
option.
while executiing the query I am gettiign belwo message.
"The tuple function expects a tuplet set of expression for the argument.A
string or numeric was used"
Please help me to write the correct syntax for this mdx.
Appreciate your help
Regards
SomuMosha Pasumansky has written an article called "Writing multiselect friendly
MDX calculations":
http://www.sqljunkies.com/WebLog/mosha/archive/2005/11/18/multiselect_friendly_mdx.aspx
Maybe you'll find your answer there. :)
Have you tried your query without parameters in an MDX parser? If not, try
it and you might figure out how the query should be before trying with the
parameters.
Kaisa M. Lindahl
"Somu" <Somu@.discussions.microsoft.com> wrote in message
news:65941D3D-8CBB-494C-9E95-3F4FD983E31F@.microsoft.com...
> Hi,
> I am facing problem during passing the parameters in MDX query. I need tp
> pass two parameter in my dataset, but not sure whether I am writing
> correct
> query or not. as I am getting error during parsing the query.
> here the query
> SELECT {
> [Measures].[SAT ARR], [Measures].[SAT Warranty ARR]
> } on columns,
> NON EMPTY NonEmptyCrossJoin(
> [Component Parts].[Part].Members,
> NonEmptyCrossJoin([Batch].[Batch].Members, [Building Code].[Building
> Code].Members)
> )
> on rows FROM [CUBE NAME]
> WHERE (" + Parameters!ManufactureDate.Value + ", " +
> Parameters!Suppliername.Value + ")
> --
> The two parameter I have declared in parameter box with multiselect value
> option.
> while executiing the query I am gettiign belwo message.
> "The tuple function expects a tuplet set of expression for the argument.A
> string or numeric was used"
> Please help me to write the correct syntax for this mdx.
> Appreciate your help
> Regards
> Somu
Passing parameter in LIKE statement with '%'
How do i handle this code:
CREATE PROCEDURE sp_Test
@.pchrTest1
AS
SELECT
fldTest1,
fldTest2
FROM
tblTest1
WHERE fldTest1 LIKE '%' + @.pchrTest1
This codes seems it does not work.
Thanks in advance
You can't use variable directly when executing SQL commands., instead will you need to construct a string representation of your command and execute it using the EXEC statement.Your code above should work when done like this:
CREATE PROCEDURE sp_Test
@.pchrTest1
AS
EXEC('SELECT fldTest1, fldTest2 FROM tblTest1WHERE fldTest1 LIKE '''%' + @.pchrTest1)
Regards,
-chris|||You haven't specified a datatype for the parameter.
Try: @.pchrTest1 varchar(256)
It does work like this (without dynamic SQL).
|||
Just a warning...if any of this data is sensitive, this will open you up to "SQL injection" attacks:
http://www.nextgenss.com/papers/advanced_sql_injection.pdf
sqlPassing parameter in LIKE statement with '%'
How do i handle this code:
CREATE PROCEDURE sp_Test
@.pchrTest1
AS
SELECT
fldTest1,
fldTest2
FROM
tblTest1
WHERE fldTest1 LIKE '%' + @.pchrTest1
This codes seems it does not work.
Thanks in advance
You can't use variable directly when executing SQL commands., instead will you need to construct a string representation of your command and execute it using the EXEC statement.Your code above should work when done like this:
CREATE PROCEDURE sp_Test
@.pchrTest1
AS
EXEC('SELECT fldTest1, fldTest2 FROM tblTest1WHERE fldTest1 LIKE '''%' + @.pchrTest1)
Regards,
-chris|||You haven't specified a datatype for the parameter.
Try: @.pchrTest1 varchar(256)
It does work like this (without dynamic SQL).|||
Just a warning...if any of this data is sensitive, this will open you up to "SQL injection" attacks:
http://www.nextgenss.com/papers/advanced_sql_injection.pdf
Passing parameter in LIKE statement with '%'
How do i handle this code:
CREATE PROCEDURE sp_Test
@.pchrTest1
AS
SELECT
fldTest1,
fldTest2
FROM
tblTest1
WHERE fldTest1 LIKE '%' + @.pchrTest1
This codes seems it does not work.
Thanks in advance
You can't use variable directly when executing SQL commands., instead will you need to construct a string representation of your command and execute it using the EXEC statement.Your code above should work when done like this:
CREATE PROCEDURE sp_Test
@.pchrTest1
AS
EXEC('SELECT fldTest1, fldTest2 FROM tblTest1WHERE fldTest1 LIKE '''%' + @.pchrTest1)
Regards,
-chris|||You haven't specified a datatype for the parameter.
Try: @.pchrTest1 varchar(256)
It does work like this (without dynamic SQL).|||
Just a warning...if any of this data is sensitive, this will open you up to "SQL injection" attacks:
http://www.nextgenss.com/papers/advanced_sql_injection.pdf
passing parameter from vb 6 to crystal report 8
.Reset
.ReportFileName = App.Path & "\reports\vaccine_flow_ledger.rpt"
.Connect = "Provider=MSDASQL.1;Persist Security Info=False;Data Source=APACCESS"
.DiscardSavedData = True
.ParameterFields(0) = "SDT;" & #1/7/2005#
.ParameterFields(1) = "EDT;" & #1/7/2005#
.Action = 1
End With
Please help me to resolve this problem.Try this
.ParameterFields(0) = "SDT;"# & 1/7/2005 & #"
.ParameterFields(1) = "EDT;"# & #1/7/2005& #"|||I tried that one,but i didn't work.
but
.parameterfields(0) = "sdt;" & "date(2005,1,1);"
works finesql
passing parameter from stored proc to crystal
Ive written a stored proc in my sybase DB which has 2 parameters, one for start date & one for end date. When I execute the proc on the DB I am prompted to enter values for my parameters.
Using an ODBC connection I am able to find my stored proc through crystal, however when I choose my proc, crystal is not prompting me to enter values for the parameters.
In the past Ive used crystal (versions 8 & 9) & when I select the stored proc, it always prompted me to enter values for the parameters.
What am I missing here? Any help would be greatly appreciated.check the sql statement in CR, also, are you using a Command ?,
if not, that may be the way you want to go. Lastly, you could just
add the parameter in CR and have CR prompt you for the date range.|||According to the crystal help, once you choose your stored proc from the list & add it to your report, crystal should prompt you to enter parameter values.
How do I get crystal to prompt me when I add the stored proc to the report?
passing parameter FROM REPORT TO WINDOWS APP
Hi all. Is it possible to pass parameter FROM Report to my windows application(C#) ? Report is made in Business Intelligence Project. I just want to retrieve the total row number to my windows appication. Is it possible? If yes, Can you provide codes for this? Thanks. Your help would be highly appreciated.
-Ron-
Yes you can pass parameter values from your report to your windows application.
Have a default value for your parameter which is the count of rows of the table and then use Values property of ReportParametersInfo class ro retrieve the value.
Shyam
|||Excluding creative approaches such as exporting the report to HTML and parsing the HTML payload, in general the user has to initiate an action explicitly in the report. Then, assuming you use the Windows Report Viewer, you can sink the event. See the hypelink example in this article.|||Shyam - I have no idea of what you are saying. Can you provide me sample codes for this? thanks.
Teo Lachev - yes i am using windows report viewer. can't find the hyperlink example in link you gave.
Please Help me....
-Ron-
|||In the link I gave you, there is another link that will bring you to a DevX article. From there you can download a code sample that shows how to sink a hyperlink event.