I want to change my graph to sort my months by financial month. ie sort order would be...
1 July
2 Aug
3 sept
4 oct
5 nov
6 dev
7 jan
....etc

Ideally I would like to have my chart show the month name, but sort by the financial sort number. It appears that charts do not have this functionality, nor can it handle more than two nested x-axis.
As a result I can only see that i can achieve this sort order by formatting a string as follows... 07-Jan, and using this in my axis.
I've tried different options to convert the month to a string and then concatenate with my sort number but have been unsuccessful.

so far I have the following in beginning of my proc
DEFINE FILE VOLUMES_CUBE
NMONTH/M=VOLUMES_CUBE.VOLUMES_CUBE.Month11;
FMONTH/M=IF NMONTH LE 6 THEN NMONTH + 6 ELSE NMONTH - 6;

How can I use this in a string and concatenate to my Month Name?


My master file is set up on a SSAS cube so I am aware that alot of the WF functionality is limited so not sure if this is stopping me.

Below is my simple graph where I wish to display Financial Year and Month in my x-axis.

GRAPH FILE VOLUMES_CUBE
-* Created by Advanced Graph Assistant
SUM Total_Due_Renewals
Total_Net_Renewals
COMPUTE jnc_NRR/F7.2%=( Total_Net_Renewals / Total_Due_Renewals ) ;
BY Financial_Year1
ACROSS Month_name11

The year and month are formatted as such in the master (snippet)

(...)
FIELDNAME=Date11, ALIAS=Date, USAGE=YYMD, ACTUAL=A10,
TITLE='Date',
WITHIN='*[Date].[Date]', DATEPATTERN='[DD]/[MM]/[YYYY]',
PROPERTY=CAPTION, $
FIELDNAME=Financial_Year1, ALIAS='Financial Year', USAGE=A7, ACTUAL=A7,
TITLE='Financial Year',
WITHIN='*[Date].[Financial Year]',
PROPERTY=CAPTION, $
FIELDNAME=Month11, ALIAS=Month, USAGE=M, ACTUAL=A7,
TITLE='Month',
WITHIN='*[Date].[Month]', DATEPATTERN='[MM]',
PROPERTY=CAPTION, $
FIELDNAME=Month_Name11, ALIAS='Month Name', USAGE=Mtr, ACTUAL=A9,
TITLE='Month Name',
WITHIN='*[Date].[Month Name]', DATEPATTERN='[Month]',
PROPERTY=CAPTION, $
(...)

This message has been edited. Last edited by: Kerry,

Consider this: BY MonthNumber NOPRINT BY MonthAlpha for starters. If that does not work for you, please provide a sample using the GGSALES file using a SUM of UNITS or DOLLARS by (month part of) DATE within a given year.

Do you want one Graph for each Year?
Basically add your Month sort and adjust the graph settings below.
When you add the three numbers together they should equal the number of BY fields in your graph.

  
ON GRAPH SET GRMULTIGRAPH 1
ON GRAPH SET GRLEGEND 0
ON GRAPH SET GRXAXIS 2

Here is a sample fex. I have three BY fields.

  
-*INTERNAL_PROPERTIES$fieldDisplayMode=label;OBJECTID=GLOBAL
-*INTERNAL_PROPERTIES$enablePreview=true;OBJECTID=GLOBAL
-*INTERNAL_PROPERTIES$prefixDisplayMode=;OBJECTID=GLOBAL
-*INTERNAL_PROPERTIES$GlobalRecordLimit=500;OBJECTID=GLOBAL
-*INTERNAL_PROPERTIES$SampleData=false;OBJECTID=GLOBAL

DEFINE FILE EMPLOYEE
YEAR/YY = DATECVT(HIRE_DATE, 'I6YMD', 'YY');
MONTH/M = DATECVT(HIRE_DATE, 'I6YMD', 'M');
MONTH_NAME/Mt = MONTH;
MONTH_SORT/I8 = DECODE MONTH ( 07 1  08 2 09 3 10 4 11 5 12 6 01 7 02 8 03 9 04 10 05 11 06 12);
END

GRAPH FILE EMPLOYEE
-* Created by Advanced Graph Assistant
SUM
   CURR_SAL
   SALARY
BY YEAR 
BY MONTH_SORT NOPRINT
BY MONTH_NAME 
ON GRAPH PCHOLD FORMAT PNG
ON GRAPH SET GRAPHDEFAULT OFF
ON GRAPH SET VZERO OFF
ON GRAPH SET HTMLENCODE ON
ON GRAPH SET HAXIS 770
ON GRAPH SET VAXIS 405
ON GRAPH SET UNITS PIXELS
ON GRAPH SET LOOKGRAPH VBAR
ON GRAPH SET GRMERGE ADVANCED
ON GRAPH SET GRMULTIGRAPH 1
ON GRAPH SET GRLEGEND 0
ON GRAPH SET GRXAXIS 2
ON GRAPH SET GRAPHSTYLE *
setTemplateFile("/images/tdg/template/IBISouthWestern.txt");
setReportParsingErrors(false);
setSelectionEnableMove(false);
setDepthRadius(5); 
setTransparentBorderColor(getChartBackground(),true); 
setTransparentBorderColor(getSeries(0),true);
setTransparentBorderColor(getSeries(1),true);
setTransparentBorderColor(getSeries(2),true);
setTransparentBorderColor(getSeries(3),true);
setTransparentBorderColor(getSeries(4),true);
setTransparentBorderColor(getSeries(5),true);
setTransparentBorderColor(getSeries(6),true);
setTransparentBorderColor(getSeries(7),true);
setTransparentBorderColor(getSeries(8),true);
setTransparentBorderColor(getSeries(9),true);
setTransparentBorderColor(getSeries(10),true);
setPlace(true);
ENDSTYLE
END

Good point... consider GRMERGE as well.

I couldn't find an elegant way to do this because of the BY/ACROSS limitations of GRAPH. The approach presented below takes advatage of the fact that Date-Time values are sorted based on the entire date value regardless of the display format. So a Date-Time value will sort by year and month even when the display format is month only (HMt). I take advantage of this fact by incrementing the year values based on the desired sort order for the months. Thus the report month is converted to a sort year/month in this manner:

Jan 1956/01
Feb 1957/02
Mar 1958/03
Apr 1959/04
May 1960/05
Jun 1961/06
Jul 1950/07
Aug 1951/08
Sep 1952/09
Oct 1953/10
Nov 1954/11
Dec 1955/12

When sorted by the sort year/month, the year values cause the records to be sorted with Jul (1950) first. But since only the month is displayed, the year values aren't really important (except for sorting).

DEFINE FILE GGSALES
 RPT_DATE/YYMD    = DATE ;
 RPT_YEAR/YY      = RPT_DATE ;
 MONTH_NBR/M      = RPT_DATE ;
 SORT_YEAR/I4     = IF (MONTH_NBR LT 07) THEN (MONTH_NBR + 5 + 1950) ELSE (MONTH_NBR - 7 + 1950);
 SORT_DATE/A8YYMD = EDIT(SORT_YEAR) | EDIT(MONTH_NBR) | '01';
 SORT_DATEX/YYMD  = SORT_DATE ;
 SORT_MONTH/HMt   = HDTTM(SORT_DATEX,8,'HMt');
END
-*
GRAPH FILE GGSALES
 SUM UNITS AS 'Units Sold'
 BY RPT_YEAR AS ''
 ACROSS SORT_MONTH AS ''
 ON GRAPH SET LOOKGRAPH VBAR
 ON GRAPH SET GRAPHEDIT SERVER
 ON GRAPH SET GRID OFF
 ON GRAPH SET 3D ON
 ON GRAPH SET GRMERGE ADVANCED
 ON GRAPH SET GRMULTIGRAPH 0
 ON GRAPH SET GRLEGEND 0
 ON GRAPH SET GRXAXIS 2
END

Thanks Mighty Max, I don't want one graph for each year. I'd like it combined as per my original graph. It is a shame I cannot insert a screen shot in this forum to show you what I mean.

I'm looking through Dan Satchell's solution as this combines the Years into one chart. It is not ideal but looks like a potential work around.

I've used the following and it has got it to sort correctly

DEFINE FILE VOLUMES_CUBE
MONTH_NBR/M = VOLUMES_CUBE.VOLUMES_CUBE.Month11 ;
SORT_YEAR/I4 = IF (MONTH_NBR LT 07) THEN (MONTH_NBR + 5 + 1950) ELSE (MONTH_NBR - 7 + 1950);
SORT_DATE/A8YYMD = EDIT(SORT_YEAR) | EDIT(MONTH_NBR) | '01';
SORT_DATEX/YYMD = SORT_DATE ;
SORT_MONTH/HMt = HDTTM(SORT_DATEX,8,'HMt');
END

GRAPH FILE VOLUMES_CUBE
-* Created by Advanced Graph Assistant
SUM VOLUMES_CUBE.VOLUMES_CUBE.Total_Due_Renewals
VOLUMES_CUBE.VOLUMES_CUBE.Total_Net_Renewals
COMPUTE jnc_NRR/F7.2%=( VOLUMES_CUBE.VOLUMES_CUBE.Total_Net_Renewals / VOLUMES_CUBE.VOLUMES_CUBE.Total_Due_Renewals ) ;
BY VOLUMES_CUBE.VOLUMES_CUBE.Financial_Year1
ACROSS SORT_MONTH AS 'Month'

I didn't need multiple x-axis to give the result I was looking for

ON GRAPH SET GRMERGE ADVANCED
ON GRAPH SET GRMULTIGRAPH 0
ON GRAPH SET GRLEGEND 1
ON GRAPH SET GRXAXIS 1

Going forward I will be implementing the Financial Month in the cube as I can atleast control the sort / display fields.
It also saves having to add this work around in for 100+ reports.

Thanks for the support on this.

Mike,

I use the free cloud service Dropbox where I have a public folder that contains images and screenshots that I embed in my FocalPoint posts...

I still have issues with this i'm afraid.

Eventhough the workaround is ok for now I wanted to add financial Month into my SSAS cube.
I've have now added a new dimension attribute in my cube that has a sort field of the Financial Month Number and a display field of Month Name.

The issue here is that webfocus simply reads in the new cube attribute as follows

FIELDNAME=Financial_Month1, ALIAS='Financial Month', USAGE=A3, ACTUAL=A3,
TITLE='Financial Month',
WITHIN='*[Date].[Financial Month]',
PROPERTY=CAPTION, $

So it has no visibility of my cube sort field. If I format this attribute in WF as Month, I am back to where I started.

I then thought that I could add both the Financial month number and Month Name seperately....However would this then allow me to use both in my graph whereby the month number is hidden but it is still used for sorting? I can't see how...

I tried to mimic nesting a month number in Dan S example be was unsuccessful.

BTW : does anyone have instructions on how to use the cloud service to embed screen shots?

This message has been edited. Last edited by: Mike_Lombardi,

Dropbox Tour

How I use it with FocalPoint:

Login to Dropbox web interface.
Click on my Public folder.
Click on Upload icon.
Choose file(s).
Once files are uploaded, right-click on image file and select "Copy public link".

In a FocalPoint post, click on the Image icon of the Message toolbar and paste (Ctrl-V) in the Image URL text-box.

The Dropbox image file public link will now be in the FocalPoint image URL. Once the message is posted, the image will appear in-stream with the text of the message. There are rare instances of corporate proxy servers that prevent viewing images in cloud services.

[ IMG ] http : // dl.dropbox.com/u/583666/Capture3.PNG [ /IMG ]

They block Dropbox where I work.

me too!

Does anyone have any idea of how I can get around the cube master limitation on the sort field?

You can also manually set the order of the columns.
The syntax is ACROSS COUNTRY AS 'Country' COLUMNS 'ENGLAND' AND 'ITALY' AND 'JAPAN'.
Below I am building the list of columns then specifying them in the graph. Hope this helps.

  
DEFINE FILE GGSALES
RPT_DATE/YYMD    = DATE ;
RPT_YEAR/YY      = RPT_DATE ;
MONTH/Mt         = RPT_DATE ;
MONTH_SORT/I8    = DECODE MONTH ( 07 1  08 2 09 3 10 4 11 5 12 6 01 7 02 8 03 9 04 10 05 11 06 12);
END


TABLE FILE GGSALES
SUM
    COMPUTE CNTR/I8 = CNTR + 1; NOPRINT
    COMPUTE MONTH_LIST/A50 = IF CNTR EQ 1 THEN '''' | EDIT(MONTH) | '''' ELSE ' AND ' | '''' | EDIT(MONTH) | '''' ; 
BY MONTH_SORT NOPRINT
ON TABLE SET ASNAMES ON 
ON TABLE SET HOLDLIST PRINTONLY
ON TABLE HOLD AS HMONTHS
END
-RUN


GRAPH FILE GGSALES
SUM UNITS AS 'Units Sold'
BY RPT_YEAR AS ''
ACROSS MONTH AS '' 
COLUMNS 
-INCLUDE HMONTHS
ON GRAPH SET LOOKGRAPH VBAR
ON GRAPH SET GRAPHEDIT SERVER
ON GRAPH SET GRID OFF
ON GRAPH SET 3D ON
ON GRAPH SET GRMERGE ADVANCED
ON GRAPH SET GRMULTIGRAPH 0
ON GRAPH SET GRLEGEND 0
ON GRAPH SET GRXAXIS 2
END

Try Tinypic.com No need to join or anything - just upload the graphic. It automatically creates the tag for the message board. The images do disappear after a while - 90 days I think, but that's normally eough time to get the answer you need.

This message has been edited. Last edited by: George Patton,

TinyPic is one that is sometimes blocked - questionable material posted quite often

Might Max. Thanks for that report solution. It is good to see that a custom sort is possible.

However I really need to implement the solution directly in my master file. As there are 100's of reports, adding a custom sort in each report is too much maintaince.

The issue I have is that my master is based on an SSAS cube, so the master is limited.

The two options I feel could help deal with this outside of the report is as follows

a) Modifying the master file to take an additional column that is formatted to the financial month order.
b) Create a “global” define that can be accessed via report creators


Are any of these options viable? If so how do I go about it.

Option A
I've tried creating an additional column in my SSAS master, but it just doesn't work. Adding columns or defines on an SSAS master doesn't seem possible.
I'm limited to formatting columns only.

Option B
Is there a way of defining my define once (globally) and using it across multiple reports?

The ideal solution is to add a define field for Financial Month Number to your master file. Then use that field in all of your reports.
But you say that the define is not working for your SSAS master. Open a case with IBI and find out why its not working.
Doing a DEFINE in a master is a little different than doing them in a fex.

...  
FIELD=BUY_SELL, ALIAS=BUY_SELL, FORMAT=A1,$  
FIELD=AMOUNT, ALIAS=AMOUNT, FORMAT=D16,$
DEFINE BUY_AMOUNT = IF BUY_SELL EQ 'B' THEN AMOUNT ELSE 0;$                                        
DEFINE ARGENTINA_PESO/D16 =    AMOUNT * 1.00;$
...

Another option would be to add the Financial Month Number column to your data source and then recreate the master. Can you add columns to a cube or does this mess it up?

I don't know of a way to include a define globally. You would have to add a -INCLUDE to all of your reports and in the include file make all of your DEFINE statements.

Thanks mighty max, this is what I thought.

I would like to have added it to my cube, however If I do, WF does not have the option to add a seperate sort field and display field in my x-axis. So I'm sort of stuck.
I hope v8 has more flexibility on this as most BI tools have the ability to control the sort/display field in reports and charts.

I will raise with IBI the errors I get when creating a define on an SSAS cube master. I've already raised a number of limitations on SSAS cube masters, so this can be added to the list.

in regards to the INCLUDE this is what I thought would be the option. It is not ideal, as the visibility of it is not obvious. But at least it may be an option if I cannot get my master to do what I want.

The likelihood is that I will have to update my cube to include the sort field, update my master and then battle my way though the chart editor to see if it can make use of it but still only show the Month Name.

Thanks for all the suggestions and work arounds.