Passinga Parameter to Topcount()

Question

Passinga Parameter to Topcount()

Christopher-266697

SSC Enthusiast

Points: 123
More actions
February 20, 2006 at 10:12 am

#112954

for Report Services and AS 2005, in an Mdx query I'm trying to pass a parameter to the topcount function for the # of top counted records to get. I keep getting a Type Mismatch error. Here's the portion of the query... FROM ( SELECT ( TOPCOUNT([Donor Dim].[Account Number].[Account Number], " +Parameters!TopNum.Value +", [Measures].[Gift Amount]) ) ON COLUMNS
does anyone know how to resolve this? thanks much.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

stevefromOZ SSC-Forever Points: 43646 More actions · Answer 1

I haven't tested it but the datatype of the param may be fixed at varchar, so maybe casting it to an integer may fix the issue.

Steve.

Christopher-266697 SSC Enthusiast Points: 123 More actions · Answer 2

Thanks for the response

With report services, you can assign it to data type integer, or data type string, and either way, type mismatch error is the same. I tried strtovalue and strtomember functions as well, to no avail.

Vadim Govorovski SSC Enthusiast Points: 150 More actions · Answer 3

This is probably happening because your parameter TopNum is of type Integer. You need to cast its value to string in your query expression:

FROM ( SELECT ( TOPCOUNT([Donor Dim].[Account Number].[Account Number], " +Parameters!TopNum.Value.ToString() +", [Measures].[Gift Amount]) ) ON COLUMNS

Christopher-266697 SSC Enthusiast Points: 123 More actions · Answer 4

Thanks for the input, but there's no ToString() function available in mdx. It's counterpart, strtovalue() doesn't work. Also, passing the param without the quotes with the .value function on the end would pass it directly as an integer too, I would think. I don't know. Tried all these, so far, nothing works. Any ideas are appreciated.

Vadim Govorovski SSC Enthusiast Points: 150 More actions · Answer 5

ToString() function is part of .NET framework. It is not a part of MDX query in this context. It is executed as part of the expression that renders MDX query to be executed. The actual MDX query will not contain it after the expression is parsed by Reporting Services.

Have you tried the expression suggested in my previous post yet?

P.S. You can always resort to changing your TopNum parameter type to String. This way you will avoid type casting error, but will loose automatic validation that ensures that it contains a numeric value.