When I run the designer report for current period it has or lists 2 periods as the current period.

If I use the direct query tool it does the same. What causes this issue ?

I have found previous requests about this issue and there was no proper response from Sage on this issue.

Views: 64

Attachments:

Reply to This

Replies to This Discussion

Hi Cliff,

Please provide the original report name and version as I know there area few Designers out there.

Not to worry I resolved the issue.

Hi

Can you please share the solution. I still have a challenge on this

Hi Brian,
I havent been able to replicate the issue, but do know these tables and the container. The problem may be with the join to the GLAFS table which in fact is not needed for the purpose of this container. So eliminate it and replace it with CSFSC. Here is the replacement code assuming this is for the "....Settings" container:

Select Distinct
RTRIM("CSFSC"."AUDTORG") As Company,
RTRIM("CSCOM"."CONAME") AS CompanyName,
(SELECT MAX("CSFSC"."FSCYEAR") FROM "CSFSC") AS CurrentYear,
CASE
WHEN MONTH(GETDATE()) = SUBSTRING(CAST("CSFSC"."ENDDATE1" AS VARCHAR),5,2) THEN '01'
WHEN MONTH(GETDATE()) = SUBSTRING(CAST("CSFSC"."ENDDATE2" AS VARCHAR),5,2) THEN '02'
WHEN MONTH(GETDATE()) = SUBSTRING(CAST("CSFSC"."ENDDATE3" AS VARCHAR),5,2) THEN '03'
WHEN MONTH(GETDATE()) = SUBSTRING(CAST("CSFSC"."ENDDATE4" AS VARCHAR),5,2) THEN '04'
WHEN MONTH(GETDATE()) = SUBSTRING(CAST("CSFSC"."ENDDATE5" AS VARCHAR),5,2) THEN '05'
WHEN MONTH(GETDATE()) = SUBSTRING(CAST("CSFSC"."ENDDATE6" AS VARCHAR),5,2) THEN '06'
WHEN MONTH(GETDATE()) = SUBSTRING(CAST("CSFSC"."ENDDATE7" AS VARCHAR),5,2) THEN '07'
WHEN MONTH(GETDATE()) = SUBSTRING(CAST("CSFSC"."ENDDATE8" AS VARCHAR),5,2) THEN '08'
WHEN MONTH(GETDATE()) = SUBSTRING(CAST("CSFSC"."ENDDATE9" AS VARCHAR),5,2) THEN '09'
WHEN MONTH(GETDATE()) = SUBSTRING(CAST("CSFSC"."ENDDATE10" AS VARCHAR),5,2) THEN '10'
WHEN MONTH(GETDATE()) = SUBSTRING(CAST("CSFSC"."ENDDATE11" AS VARCHAR),5,2) THEN '11'
WHEN MONTH(GETDATE()) = SUBSTRING(CAST("CSFSC"."ENDDATE12" AS VARCHAR),5,2) THEN '12'
WHEN MONTH(GETDATE()) = SUBSTRING(CAST("CSFSC"."ENDDATE13" AS VARCHAR),5,2) THEN '13'


ELSE ''
END AS CurrentPeriod


FROM
"CSFSC"
LEFT JOIN "CSCOM"
ON "CSFSC"."AUDTORG" = "CSCOM"."AUDTORG"

If this does not resolve it, then I would need to look at your CSCOM and CSFSC tables. Dump them into Excel and email me.

Regards

RSS

The Sage Intelligence Blog

Like Sage Intelligence?

Follow @SageSupport

© 2019   Created by Sage Alchemex.   Powered by

Badges  |  Report an Issue  |  Terms of Service