I want to have a column on the income statement quarter to date. I have tried to get CEO to agree to rolling QTD to no avail.
rolling qtd is easy base-2:base
QTD is harder
All I can come up with is having four columns that name the quarters
1:3 4:6 7:9 and 10:12 and then use conditional suppression.
but which to choose?
P>B works for 1:3 when the base is 4
but then it does not work for 7:9 and 10:12
ideally I need a conditional suppression that is between
is there a way to do this with the four columns, or a totally different way to do this?
in February QTD = 1 + 2.
in October QTD = 9 + 10.
I've found this...
that says you need four column formats and four reports. Seriously? there has to be a different way.
The effective dates section of that document is one way to do it. The other is to set up the 4 columns like you mentioned and then use the Print Control P <=B. In this scenario, the quarters will only print if they are less that or equal to the period you generate the report for. I believe you would always need to generate the report for periods 3, 6, 9, or 12 for this to work.
The effective dates scenario will require someone to edit the column format everytime a month changes. Not really an option.
The P<B option works if we are willing to display Q1 in Q2, 1 and 2 in 3 etc. Which we are not. You are also correct in it would mean only getting the report every three months. This for those two reasons is not an option.
We want one column that knows which quarter you are in and knows which month in the quarter you are in.
Frx could do this. I'm stunned that MR is struggling with what would appear to be a simple request.
thanks for any further advice / assistance (to Jake and any others.)
Hi, please try this.
For each month, use Print Control NP,P<=B. Total each quarter, using the X0 print control.
Expand for Q3 and Q4.
Sorry, 1 Correction. The Total Calc shold be E+I
thanks so much this is so close I can smell it.
Ideally I need to surpress Q1 once we get into Q2, 1 and 2 in 3 etc.
That is really the column I am struggling with. I need it to be a base is between as you used to be able to do in Frx.
I may be able to sell the CFO on showing all four quarters, but he is very keen on one sheet of paper, which add the other quarters will impede once I add in last year and net change columns.
any thoughts on surpressing the quarter once you move out of it?
thanks so much!
Sorry Ian. What about creating just a rolling 3 month report?
Oh how I tried to sell a rolling Quarter. Base -2. The higher ups simply refuse that notion.
In month 2 of quarter 2 they want to see month 1 + month 2 of quarter 2. in month 1 of quarter 1 they want just month 1. They want only one column labeled quarter. One for this year, one for last year, and one for difference.
I've made 12 column formats for now naming each month specifically. Once the report generators tire of that I will try your suggestion that will print Q1, Q2, Q3 and Q4 (if we are in Q4) and only print the total for the current month.
unless there are more amazing ideas on how to surpress on one column format that is.
Just confirming what you are experiencing. I am having the exact same issue and we are running MR 2012 RU5. I see no way to have 1 column layout generate a dynamic QTD column and will probably resort to 12 month specific column layouts as you suggest above. Have you made/voted for a product suggestion yet on Microsoft Connect?
The suggestion from Jerry is so very close to what my CFO wants. It however prints previous Q's as well as the current one.
The theory totally falls down when they want budget information on there, so I have had to make twelve separate column formats.
I have not had time to go to the MR suggestion site yet and add this one. I'll vote for yours if you post it.