Issue getting Accounting period number on report header

system · February 25, 2009, 4:02pm

Hi,

One of my report has two prompts called fiscal year and accounting period. But accounting period prompt is BETWEEN two values

Ex: Accounting period between 1 and 10( 1 is static but ending value can be selected from the LOV’s ranging from 1 to 12)

Assume that when user runs for fiscal year = 2008 and ending accounting period = 8 it works fine. I would like to place accounting period value on report header however when i try to pull accounting period object on report header its displaying from 1 to 8 but i want to see only number 8.

Any suggestions are really appreciated.

Thanks in advance.

santhoshini (BOB member since 2007-11-08)

system · February 25, 2009, 4:36pm

Instead of having your accounting period BEGIN and END in one filter, separate them into two different filters, where BEGIN is greater than or equal to a constant, and END is less than or equal to a prompt.

MichaelWelter (BOB member since 2002-08-08)

system · February 25, 2009, 5:03pm

Thanks for reply Michael.

I tried adding two filters. I .e accounting_period greater than or equal to 1 and accounting_period less than or equal to prompt( nothing but LOV’s from 1 to 12). Reports runs ok.( ex : accounting_period = 9 and fiscal year = 2009)

Then pulled accounting period object onto report it displaying all accounting period from 1 to 9. But i just want to see 9 only.

Am i missing something? Can anyone please tell me how to get only period based on prompt value?

Thanks

santhoshini (BOB member since 2007-11-08)

system · February 25, 2009, 6:10pm

Why are you not using the userresponse function to capture the accounting period value and display it in the header

 = USERRESPONSE("PROMPT TEXT")

.

haider (BOB member since 2005-07-18)

system · February 25, 2009, 7:00pm

Thank you Haider,

Created variable called Period and implemented below function.

= userresponse([Query 1];“Enter Accounting Period;”)

Parsed ok…Pulled on to report but its showing NULL.

Any more thoughts?

Thanks

santhoshini (BOB member since 2007-11-08)

system · February 25, 2009, 7:54pm

Hi
Did you try ReportFilter([accounting period]). I think this should work if not let me know will post any work around.

Thanks

friend (BOB member since 2006-07-03)

system · February 25, 2009, 7:58pm

The text of your prompt has to be entered into the function exactly as it appears in the query filter. If your prompt doesn’t have the “;” in it, then take it out of the function in the variable.

MichaelWelter (BOB member since 2002-08-08)

system · February 25, 2009, 9:03pm

Friend,

My prompt has accounting period between 1 and End value( 10). When i use Reportfilter function and try to pull the variable into report it will list from 1 to 10

Ex: 1:2;3:4:5…10

Michael,

I corrected the formula and pulled the variable into report and it worked fine

Correction:
= userresponse([Query 1];“Enter Accounting Period:”)

I have a strange requirement. Based on the accounting period i need to display current calender year (YYYY)

ex: If we run for fiscal year = 2009 and accounting period = 1, then i would like to see as follows.

Fiscal Year 2009
Accounting Period = 1
calender Year = 2008

Accounting Period, calendar Year
1; 2008
2; 2008
3;2008
…
…
…
8;2009
9;2009
10;2009
11;2009
12;2009

Is there any way around to get calender year based on accounting period?

Thanks

santhoshini (BOB member since 2007-11-08)

system · February 25, 2009, 10:51pm

The best way to get that is to use a calendar table in the database, and build the needed objects in the universe. Otherwise, it’s difficult and ugly to create that in the report.

MichaelWelter (BOB member since 2002-08-08)

system · March 3, 2009, 6:54pm

Michael,

Created variable to pull into header to show accounting period number.
= userresponse([Query 1];“Enter Accounting Period;”)

It works fine.

Then created another variable which says:

Year = If([Accounting Period] <= 6) Then [Fiscal Year] -1 Else [Fiscal Year]. parsed ok.

But when pulled on report beside accounting period it giving two years

ex: when query run for accounting period 8
Result:
8, 2009
8, 2008

I think the reason might be:

As i am having two data providers where Query1 gives present year data and query 2 gives previous year data.

now when i created userresponse variable it suppose to pull the accounting period from query1 but somehow it showing query 2 results too.

Does any idea how to pull only query1 accounting period only?

santhoshini (BOB member since 2007-11-08)

system · March 4, 2009, 5:55am

May be below formula can give you the desired results:

Year = If([Query 1].[Accounting Period] <= 6) Then [Fiscal Year] -1 Else [Fiscal Year].

Prashant Purohit (BOB member since 2009-02-18)

system · March 4, 2009, 6:02am

Year = If([Query 1].[Accounting Period] <= 6;Max([Fiscal Year] -1);[Fiscal Year].

I think you need something like Max() in your code. Try this code, if this is not working you can check this for your help.

aniketp (BOB member since 2007-10-05)

system · March 5, 2009, 3:33pm

Thanks for input!!!

Issue is resolved.

The best way to show fiscal year and accounting period on report header its always advisable to get it from prompt condition but not from database field. That was mistake i did and realized later.

First created a variable V_Year

=ToNumber( UserResponse([Query 1];“Enter a Year:”))

Then created another variable:

= If ToNumber( UserResponse([Query 1];“Enter Accounting Period:” )) <= 6 Then [V_Year] -1 Else [V_Year]

Thanks

santhoshini (BOB member since 2007-11-08)