April 28, 2009 at 5:12 am
i am using the below mentioned query for creating SSRS reports. but i am getting report in the order of Fiscal Month No (1,10,11,12,5,6,7,8), I would like to get the report in ascending order of Fiscal Month Number ie,(1,5,6,7,8,10,11,12).
Interesting thing is that if i copy the MDX query into Database SERver i am getting results in ascending order. but SSRS couldn't maintain the Ascending order.
SELECT NON EMPTY { [Measures].[SLA Value] } ON COLUMNS, NON EMPTY { ([Credit Note Date].[Fiscal Month No].[Fiscal Month No].ALLMEMBERS * [Credit Note Date].[Fis Month Name].[Fis Month Name].ALLMEMBERS * [Fact Allowances].[Area Of Origin].[Area Of Origin].ALLMEMBERS * [Credit Note Date].[Fiscal Year].[Fiscal Year].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME,MEMBER_VALUE ON ROWS FROM ( SELECT ( { [Dim Publication].[Pub Name].&[PJ] } ) ON COLUMNS FROM [Sentinel DW]) WHERE ( [Dim Publication].[Pub Name].&[PJ] ) CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS
Any one please hepl me.
April 28, 2009 at 7:10 am
Do you have any groups in the Report? What is the data type of the column?
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
April 28, 2009 at 8:51 am
i haven't got any groups in the reports. Fiscal Month no (integer),Fiscal Month Name(Varchar)
April 28, 2009 at 9:39 am
I did a little testing and I can't find anyway without an sort in the report itself that the report will show the data in a different order than the query returns it.
I don't do MDX/Analysis Services, but in the regular DBEngine if the plan changes then you could get a different sort order based on index(es) used, unless you specify an explicit order by.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
April 28, 2009 at 9:43 am
Jack Corbett (4/28/2009)
I did a little testing and I can't find anyway without an sort in the report itself that the report will show the data in a different order than the query returns it.I don't do MDX/Analysis Services, but in the regular DBEngine if the plan changes then you could get a different sort order based on index(es) used, unless you specify an explicit order by.
Can Anyone please help me?
April 28, 2009 at 10:00 am
Have you tried putting an explicit sort on your query?
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
April 28, 2009 at 10:06 am
i tried the expliciy sort from the SSRS design surface , but that's not working . Is it possible to put a sort criterea onto the MDX query ?. If so can you please let me how i can achieve it?
April 29, 2009 at 6:58 am
i am checking the SSRS furthur I have 2 row groups (Fis Month No,Fis Month Name) and 1 column group(Fioscal Year) just now , but still i can't manintaon the Sort Order
September 3, 2009 at 5:14 pm
Hi i need to sort the report which has the similar query ...........interactive sort is working for one level.........i m trying to sort using MDX query can any one help me out
September 3, 2009 at 6:08 pm
Open your cube in BIDS and check the properties for that dimension. You want to make sure that the OrderBy property is set to Key rather than to Name.
You can also explicitly set the Order in the MDX using the Order(<set>, <numeric>, <sort>). You may need to specify the numeric as a measure, so you may need a calculated member to do that. The sort is optional, but there are four options: Asc, Desc, BAsc, and BDesc. The first two preserve the hierarchy, if any, and the last two break the hierarchy.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
September 3, 2009 at 7:11 pm
Hi thanks for ur reply can u be little bit clear i m new to SQL .................and explain me in detail if possible
September 3, 2009 at 7:17 pm
This is my query
SELECT NON EMPTY { [Measures].[Column Name 1],
[Measures].[Column Name 2 ],
[Measures].[Column Name 3], [Measures].[Column Name 4],
[Measures].[Column Name 5 ], [Measures].[Column Name 6], [Measures].[Column Name 7], [Measures].[Column Name 8 ], [Measures].[Column Name 9], [Measures].[Column Name 10], [Measures].[Column Name 11],
[Measures].[Column Name 12], [Measures].[Column Name 13],
[Measures].[Column Name 14], [Measures].[Column Name 15] } ON
COLUMNS, NON EMPTY { ([Product].[By Business Area].[Business
Area].ALLMEMBERS * [Product].[By Segment Category
Brand].[Brand].ALLMEMBERS ) }{ ([Product].[By Name ].[Name].ALLMEMBERS * [Product].[By Name Category
Brand].[Brand].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION,
MEMBER_UNIQUE_NAME ON ROWS FROM ( SELECT (
STRTOSET(@SellingCompanyBySellingCompany, CONSTRAINED) ) ON COLUMNS
FROM ( SELECT ( STRTOSET(@ByYearMonth, CONSTRAINED) ) ON COLUMNS FROM
[SALES])) WHERE ( IIF( STRTOSET(@ByYearMonth, CONSTRAINED).Count = 1,
STRTOSET(@ByYearMonth, CONSTRAINED), [By Year Month].currentmember ),
IIF( STRTOSET(@SellingCompanyBySellingCompany, CONSTRAINED).Count = 1,
STRTOSET(@SellingCompanyBySellingCompany, CONSTRAINED), [Selling
Company].[By Selling Company].currentmember ) ) CELL PROPERTIES VALUE,
BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME,
FONT_SIZE, FONT_FLAGS
September 3, 2009 at 7:53 pm
rabbu456 (9/3/2009)
Hi thanks for ur reply can u be little bit clear i m new to SQL .................and explain me in detail if possible
Look up Order() in BOL. (Make sure that you're looking in the Analysis Services section.) It does a much better job of explaining than I could. Once you've done that, repost your code with the order() expression if you're still having problems.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
September 4, 2009 at 3:19 am
If the results are sorted properly when you run the MDX query in SSMS the issue must be in the report. It sounds like the values are being sorted as a string so try using converting to an integer in the sort expression, like this:
=CInt(Fields!MonthNo.Value)
September 5, 2009 at 11:42 am
Hi i m i need to sort the report which looks like this ...............when some one clicks on top of any column the entire column need to be sorted even the drill down values too ...........i have tried using interactive sort but its working for only one level..............i need to sort all the column................
Business Area PPPP Month Estract X
Segment Plan Sales
Category
Brand
--------------------------------------------------------------------------------------------------------------------------------------------
+RRRR 34567 678 111
+Decorative 43727 457 2453 6574
+ xyz 3587 8764 1134 36572
+pqrs 6154 7676 8699 58585
in interactive sorting what i have done is i have selected groups in that i have selected RRRR and in sort by i have selected column name ...........and in apply this sort to group i have selected tablix................................soo its working for only one level.............i need it urgently thanks in advance
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply