Topic
  • 3 replies
  • Latest Post - ‏2013-07-12T21:11:24Z by OpherB
Goud2013
Goud2013
13 Posts

Pinned topic Prompt Report to Run for Current Year and Current Month

‏2013-07-11T17:04:37Z |

Hello,

My requirement is that, when the report runs it has to run for current year and current month, also year and month prompts are cascaded. i am having Biennium year values as 2013-2015,2011-2013, 2009-2011,2007-2009; and fiscal months values are 01,02,03,04.........21,22,23,24; so by default the report has to run for 2013-2015 (i.e. 2013-2014 first biennium year and 2014-2015 is second biennium year and Fiscal month should run for July).July is my first fiscal month..i.e 01
I have only two prompts namely Biennium year and Fiscal month as single select value prompts in the report page(no prompt page).
Please help me out as i tried many of things but no luck.......

Thanks,
 

  • OpherB
    OpherB
    664 Posts

    Re: Prompt Report to Run for Current Year and Current Month

    ‏2013-07-12T17:25:42Z  

    First off, do these biennium periods overlap? I see you have listed 2013-2015,2011-2013 - but each of these is a THREE year range, and 2013 appears to be in both. Is that correct?

    Next, if it's a three year range, and the fiscal periods run 01-24...what happens for the last (or first?) twelve periods?

    Finally, what does your database store as the transaction "date"? Does it say 2013-2015/23/15 as a value, or 2013/08/15 - the underlying calendar date?

    No matter how you answer the first two questions, the issue will be how values are stored in the database. If you have a normal calendar date, how do you expect to translate the biennium values to calendar dates?

    It would be as if cars as "Sedans", "Convertibles" and "Compacts" but the prompt will be for "Chevrolet" or "Cadillac" - how do you translate one value into the comparable values of the other set...

    Good luck,
    Opher

     

  • Goud2013
    Goud2013
    13 Posts

    Re: Prompt Report to Run for Current Year and Current Month

    ‏2013-07-12T20:03:52Z  
    • OpherB
    • ‏2013-07-12T17:25:42Z

    First off, do these biennium periods overlap? I see you have listed 2013-2015,2011-2013 - but each of these is a THREE year range, and 2013 appears to be in both. Is that correct?

    Next, if it's a three year range, and the fiscal periods run 01-24...what happens for the last (or first?) twelve periods?

    Finally, what does your database store as the transaction "date"? Does it say 2013-2015/23/15 as a value, or 2013/08/15 - the underlying calendar date?

    No matter how you answer the first two questions, the issue will be how values are stored in the database. If you have a normal calendar date, how do you expect to translate the biennium values to calendar dates?

    It would be as if cars as "Sedans", "Convertibles" and "Compacts" but the prompt will be for "Chevrolet" or "Cadillac" - how do you translate one value into the comparable values of the other set...

    Good luck,
    Opher

     

    thanks for reply...

    Actually Biennium never overlap,  ex: For biennium 2009-2011 we have 24 months which is 01 as July  to 06 as December  of 2009 year, 07  as January  to 12 for 2010 year and then 2nd biennium starts from 13 as july of 2010  upto 18 as December 2010. then  next 19 as January  upto june 24th   of  2011.

    My fiscal month starts from july -01 to June - 12  then 2nd July -13 to june -24  of whole one biennium year (2009-2011).iam not at all using canlender year. I hope you understand issue now.How can i setup  the report for current month and current year. DB2 Datasource. 

     

    Thanks!!!!!

  • OpherB
    OpherB
    664 Posts

    Re: Prompt Report to Run for Current Year and Current Month

    ‏2013-07-12T21:11:24Z  
    • Goud2013
    • ‏2013-07-12T20:03:52Z

    thanks for reply...

    Actually Biennium never overlap,  ex: For biennium 2009-2011 we have 24 months which is 01 as July  to 06 as December  of 2009 year, 07  as January  to 12 for 2010 year and then 2nd biennium starts from 13 as july of 2010  upto 18 as December 2010. then  next 19 as January  upto june 24th   of  2011.

    My fiscal month starts from july -01 to June - 12  then 2nd July -13 to june -24  of whole one biennium year (2009-2011).iam not at all using canlender year. I hope you understand issue now.How can i setup  the report for current month and current year. DB2 Datasource. 

     

    Thanks!!!!!

    Well, having a Fiscal Year that runs July-June only complicates things.

    Basically, you do not want to attempt to make this adjustment in Report Studio.

    Your datawarehouse should have a Time/Calendar table and there should be columns in that table to facilitate the conversions. You may even need two different tables to support all of the lookups. Either way, doing it once in a table so that Report Studio just looks up the values will be much faster in the long run than trying to make this happen entirely within a report...and separately in every report!

    I would recommend you look at the discussions about Time at www.kimballgroup.com for some reference materials.

    Good luck,
    Opher