Question Status

Unanswered
Ian Richardson asked a question on 19 Jul 2013 1:38 PM

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.

Reply
Ian Richardson responded on 19 Jul 2013 2:15 PM

I've found this...

www.t3infosystems.com/.../management-reporter-migrate-frx.pdf

that says you need four column formats and four reports.  Seriously?  there has to be a different way.

Reply
Jake Friedl responded on 19 Jul 2013 4:40 PM

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.

Reply
Ian Richardson responded on 22 Jul 2013 6:20 AM

Hi Jake

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.)

Reply
Jerry Bifano responded on 22 Jul 2013 10:33 AM

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.

A B C D E F G H I J
Header 1 2 3 Q1 4 5 6 Q2 Total

Column Type

DESC FD FD FD Calc FD FD FD Calc Calc
Book Actual Actual Actual Actual Actual Actual

Fiscal Year

Base Base Base Base Base Base
Period 1 2 3 4 5 6
Periods Covered Periodic Periodic Periodic Periodic Periodic Periodic
Formula B+C+D F+G+H E+F
Print Control NP,P<=B NP,P<=B NP,P<=B X0 NP,P<=B NP,P<=B NP,P<=B X0

Regards,

 

 

 

 

 

Reply
Jerry Bifano responded on 22 Jul 2013 10:34 AM

Sorry, 1 Correction. The Total Calc shold be E+I

Reply
Ian Richardson responded on 22 Jul 2013 11:18 AM

Hi Jerry

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!

Reply
Ian Richardson responded on 22 Jul 2013 11:18 AM

Hi Jerry

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!

Reply
Jerry Bifano responded on 22 Jul 2013 11:23 AM

Sorry Ian. What about creating just a rolling 3 month report?

Reply
Ian Richardson responded on 22 Jul 2013 11:30 AM

hi Jerry

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.

Reply
Ian Richardson responded on 22 Jul 2013 11:30 AM

unless there are more amazing ideas on how to surpress on one column format that is.

Reply
Michael Weaver responded on 25 Jul 2013 11:33 AM

Hi Ian,

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?

Thanks,

Mike

Reply
Ian Richardson responded on 25 Jul 2013 12:09 PM

Hi Mike

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.

Ian

Reply