XL Reporter Period

[Former Member]

I have a parameter in XL Reporter using the Fiscal Period.

However, the period code is different from what I see in SAP B1 Administration> System Initialiazation> General Setting --> Posting Period.

For example, when I run XL Reporter, the parameter show me a list:

Code Period Name

200601 2007-01

200602 2007-02

However, in SAP Posting Period, I see

Code Period Name

200701 2007-01

200702 2007-02

The information in SAP Posting Period is what I want. What have I missed?

[Former Member · Accepted solution]

Hi

Just run the following query through SQL Qeury Analizer and it will replace the XL reporter view that shows the fiscal info.

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ixvPeriods]') and OBJECTPROPERTY(id, N'IsView') = 1)

drop view [dbo].[ixvPeriods]

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_NULLS ON

GO

CREATE view dbo.ixvPeriods

AS

select

(DatePart(yyyy,b.FinancYear)+1)*100 + SubNum as PeriodId,

(DatePart(yyyy,b.FinancYear)+1)*100 + SubNum as Code,

a.Name ' 'ltrim(datename(month,a.T_RefDate)) as Name,

convert(nvarchar(8),a.F_RefDate,112) AS PeriodStart,

convert(nvarchar(8),a.T_RefDate,112) AS PeriodEnd,

a.F_RefDate AS PeriodStart2,

a.T_RefDate AS PeriodEnd2,

DatePart(yyyy,b.FinancYear)+1 as Year,

a.SubNum as PeriodInYear,

a.SubNum-1 as PeriodMinus1,

b.SubType

from OFPR a

JOIN OACP b on b.PeriodCat = a.Category

GO

SET QUOTED_IDENTIFIER OFF

GO

SET ANSI_NULLS ON

GO