August 6, 2010 at 6:28 am
I have upgrade my SQL 2000 database and analysis server and SQL 2005 reporting services server to a new instance of SQL 2008 running database engine, analysis services and reporting services.
The analysis services database migrated successfully and I have got the database into a state where it processes fine but I am having the following problems.
- My Reports show in reporting services but the drop down list parameters are all empty.
If I change the parameter in report builder to use the display name instead of the uniquename then the drop down list populates and works until I select a value that contains a space character and then it reports a problem with the dataset used by the report.
Query for the dataset used to populate my parameter.
with member [Measures].[GroupUniqueName] as '[Site].CurrentMember.UniqueName' member [Measures].[GroupDisplayName] as'[Site].CurrentMember.Name' select { [Measures].[GroupUniqueName], [Measures].[GroupDisplayName] } on columns, { [Site].[Group].members } on rows FROM [Invoice Reporting]
Query used by the report, filtering on the selected parameter value from the drop down list
="SELECT { { [Report Measures] } } ON COLUMNS , NON EMPTY { { { { [Reason Status].&[1001], [Reason Status].&[1002] } * DESCENDANTS(StrToMember(@sCustomerGroup), [Site].[Customer] ) } } } ON ROWS, { {[Analysis].[Period]} * {[Report Periods]}, {[Analysis].[YTD]} * {[Report Prior EOY Period]}, {[Analysis].[YTD]} * {[Report Current Period]} } ON PAGES FROM [Invoice Reporting]"
- The report data also does not display on the report.
I think this may be to do with a change in the way that dates are handled as when browse the cube in VS I need to specify a value for my date dimension before any data is returned.
Any help or assistance you can provide would be much appreciated! Thanks
August 6, 2010 at 6:28 am
The error that I receive when running the report having selected "Birmingham Airport" from the drop down list is:
An error occurred during client rendering.
An error has occurred during report processing.
Query execution failed for dataset 'DataSet'.
Parser: The syntax for 'Airport' is incorrect
So the parameter is being cut off after the space. The report at least runs when I select a value with no spaces but I still don't have any data coming through from the old dataset query or perhaps this is to do with the SQL Analysis services cube???
Please help!!
Thanks!!
August 6, 2010 at 6:29 am
It looks as though the calculated members in my invoice reporting cube could be the reason why nonoe of the report data is coming through to the report, though these do appear to show the data ok when I browse the cube. Here are a couple of the calculated members and the measures in the calculations.
CREATE MEMBER CURRENTCUBE.Measures.[Average Sales Price] AS 'iif([Measures].[Sales Volume] = 0, null, [Measures].[Gross Sales] / [Measures].[Sales Volume])', SOLVE_ORDER = 20, FORMAT_STRING = '#0.00';
CREATE MEMBER CURRENTCUBE.[Analysis].[YTD] AS SUM(YTD([Date].[Fiscal].CurrentMember), [Analysis].[Analysis].[Period]), SOLVE_ORDER = 10;
CREATE SET CURRENTCUBE.[Report Measures] AS '{[Measures].[Number of Installations], [Measures].[Trading Days], [Measures].[Sales Volume], [Measures].[Gross Sales], [Measures].[Customer Margin], [Measures].[Marketing Contribution], [Measures].[Net Sales], [Measures].[Sales Volume per Day], [Measures].[Customer Margin Pc], [Measures].[Annualised Volume per Unit], [Measures].[Average Sales Price]}';
CREATE SET CURRENTCUBE.[Report Periods] AS 'ParallelPeriod(Month, 13, StrToMember("[Date].[Fiscal].&[" + CStr([Date].[Fiscal].[Month].Members.Item(0).Properties("Current")) + "]")) : StrToMember("[Date].[Fiscal].&[" + CStr([Date].[Fiscal].[Month].Members.Item(0).Properties("Current")) + "]")';
CREATE SET CURRENTCUBE.[Report Current Period] AS '{StrToMember("[Date].[Fiscal].&[" + CStr([Date].[Fiscal].[Month].Members.Item(0).Properties("Current")) + "]")}';
CREATE SET CURRENTCUBE.[Report Prior EOY Period] AS 'Tail(Descendants(ParallelPeriod(Year, 1, StrToMember("[Date].[Fiscal].&[" + CStr([Date].[Fiscal].[Year].Members.Item(0).Properties("Current")) + "]")), [Date].[Fiscal].[Month]), 1)';
Is there anybody out there that can help shine some light, please???
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply