Hello all,
I am facing an issue when I try to pass a parameter that comes from an SQL query to an MDX.
The situation is the following: I am having an MDX query, quering my cube and returning sales. One of the report criteria is the country that sales occur. This is working fine and returns the correct results.
Then I got a request from the sales department that they want each user to be able to query specific countries. So, I have a database on an SQL Server holding this info (which user - database). Some of the users may query more than a database. Then I introduced an SQL dataset that depending on the username it returns the country name that the user may access. The country names appear in a drop down list (multi selection) on the report.
The user selects the desired counties and the country name is passed to the MDX query. The problem is that I miss lots of sales, while when I select specific countries (e.g. Italy) I receive an error message : "An error occurred during local report processing. An error has occured during report processing. Query execution failed for dataset 'SiteSite'. Query (1, 403) members belong to different hierarchies in the STRTOSET function."
In addition I receive the following message in the Output box of BIDS: "[rsInvalidExpressionDataType] The Value expression used in textrun ‘textbox1.Paragraphs[0].TextRuns[1]’ returned a data type that is not valid.
Preview complete -- 0 errors, 1 warnings"
I have applied the same technique to other reports but both datasets are SQL based.
Has anybody experienced something different or knows any workarounds for the given problem?
Thanks in advance!