Hi everyone, I am working on a report to display the monthly General & Administrative Expenses. I was able to create the report, but it runs into an error whenever I try to run the 13th period. I tried to research why this is happening and found out that it’s because of the formulas I used to display the column headers. However, I can’t find an alternative or a solution for it. Could anyone have any idea about this? I have attached the XML file for the report.
Best answer by lorach
Hi everyone, I figured out a workaround for this report, so I had to add an extra column in the column set, I used, =IIf(True,'P13',Left(Format('{0:MMM yyyy}',DateAdd(CDate(Left(@StartPeriod,2)+'/1/'+Right(@StartPeriod,4)),'m',1)),3)) for the column heading, and =IIf(CInt(Left(@StartPeriod, 2)) = 12, True, False) for Visible formula, in the data source I maintained both offset (year, period) as 1 and Amount Type as Turnover, what this does, is that it enables the report display the 13th period only when I specify the 12th period on the report parameter and the 13th period won’t be display if any period less than the 12th is specified on the report parameter plus I added more two columns to show the Total (Ending Balance without the 13th period values) and another with 13th period included.
The screenshot 1 below shows when I run the report with 12th period as you can use the 2 columns are added the “P13 and “Total (incl P13)”
Below is when I run it with a period less the 12th period
I have the excel formats of the Column set details and header section that I used.
+13Hi
I think there is a problem with Visible Formula in your report and your report is missing a column if there is 13th period.
Please refer to the ARM report in the attached file. This report works fine with 13th period.
Best Regards,
NNT
Hi
The required format
+13Hi
If you want to display all 12 previous periods when selecting any period, please refer to the attached report.
Best Regards,
NNT
Hi
+13Assuming the report will always go from period 01 on the left and then add columns to the right throughout the year, I have done something similar in the past.
In the Column Headers, I’ve used “= Format( '{0: MMMM d, yyyy}',Report.GetPeriodEndDate('02'+right(@StartPeriod,4)))” (replacing the period number for each column)
Your column for period 1 would always be visible. Visibility formulas for periods 2 - 13 would be “=CInt(left(@StartPeriod,2))>=2” (for period 2, and replace 2 with 3 for the next column, etc.)
Your data source would have the period number hard coded in for each column:
For my YTD columns at the end, I hardcode in period 01 as the start period.
Hi @meganfriesen37, thanks for the suggestion. However, it still doesn’t meet the requirement I mentioned earlier. Your suggestion lists the future period after what was specified in the parameter; for example, if you run 05-2025, it will also show the columns for 06-2025, 07-2025…….. so yes, it doesn’t list the previous periods, but it lists the future ones.
+13With putting in the visibility rules it should hide the “future” periods.
Hi
Hi
Hi everyone, I figured out a workaround for this report, so I had to add an extra column in the column set, I used, =IIf(True,'P13',Left(Format('{0:MMM yyyy}',DateAdd(CDate(Left(@StartPeriod,2)+'/1/'+Right(@StartPeriod,4)),'m',1)),3)) for the column heading, and =IIf(CInt(Left(@StartPeriod, 2)) = 12, True, False) for Visible formula, in the data source I maintained both offset (year, period) as 1 and Amount Type as Turnover, what this does, is that it enables the report display the 13th period only when I specify the 12th period on the report parameter and the 13th period won’t be display if any period less than the 12th is specified on the report parameter plus I added more two columns to show the Total (Ending Balance without the 13th period values) and another with 13th period included.
The screenshot 1 below shows when I run the report with 12th period as you can use the 2 columns are added the “P13 and “Total (incl P13)”
Below is when I run it with a period less the 12th period
I have the excel formats of the Column set details and header section that I used.
Thank you for sharing your solution with the community
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.
