Here is an abbreviated example of the table I am working with:
Month ............ SiteID ........ Patients ....FundingSource.... Year
January ............ 1 ........... 13 .......... ABCD.......... 2019
February............ 1 ........... 223 ......... ABCD.......... 2019
February ........... 2 ........... 52 .......... ABCD.......... 2019
February ........... 3 ........... 21........... EFGH.......... 2018
March .............. 1 ........... 44 .......... EFGH.......... 2018
April .............. 1 ........... 12 .......... ABCD.......... 2019
May................. 1 ........... 34 .......... ABCD.......... 2018
May................. 1 ........... 51 .......... ABCD.......... 2019
June ............... 1 ............62 .......... EFGH.......... 2017
I am trying to output a report that shows the monthly, quarterly, and Year to Date totals of number of patients seen for the given parameters. The Parameters are FundingSource, SiteID, Month , Year. Because the report is supposed to look like a form it has to print the result out one time/on one page.
Edit: The quarterly total should be for the fiscal quarter, which is from July to June. The Year column is the calendar year.
The Patients field itself is a string field representing the number of patients seen in the month. So creating a monthly total is easy because it is just a matter of adding the field.
For the Quarterly total I made a Cdbl Formula field to convert the table.Patients to a number (it is an nvarchar field in our database -- can't to a sum in the running total field), then made a running total field for each quarter for that field (by making that cdbl field evaluate for Month IN [January, February, March, April], etc, and reset on change of SiteID). Then I made another formula field which would output the corresponding running total field for the quarter for whichever month
ex.
IF month IN [January, February, March, April] then
{#PatientsSeenQ1}
The problem I'm having is that given these parameter fields, The Quarterly result ends up being the same as the Monthly result, because Month = ?Month and it can't zoom out to capture the other months in the quarter. I think this is the same problem with getting the YTD total as well.
I've been banging my head against this problem for a while now, so any help would be greatly appreciated!! This is an important problem for my job so I will definitely respond promptly.
EDIT:
I'm asked to format the output like this (assuming calendar year quarters for example purposes):
Reporting Period: May 2019
Funding Source: ABCD
Site: 1
........................... Monthly ... Quarterly ..... YTD
Number of patients seen:..... 51 ........ 63........... 299
I don't have to report for each site, year, and month, only for the entered site, year, funding source and month (the parameters). The output is supposed to be a printable version of a report that users will view on a website.
EDIT:
With the below from user heringer I was able to find the solutions to my problems. For the YTD I had outside help, so here is that answer:
I made one formula field @FiscalYear to determine the year:
IF {@RowMonthCDate} IN [7,8,9,10,11,12] and {@ParMonthCDate} IN [1,2,3,4,5,6] THEN toText((ToNumber({?Year})-1),"#")
ELSE IF {@RowMonthCDate} IN [1,2,3,4,5,6] and {@ParMonthCDate} IN [7,8,9,10,11,12] THEN toText((ToNumber({?Year})+1),"#")
ELSE toText((ToNumber({?Year})),"#")
Then for the YTD Running total field I put this for the Evaluate formula:
{reporting_year} = {@FiscalYear} AND
(
if {@ParMonthCDate} = 7 then {@RowMonthCDate} = 7
else if {@ParMonthCDate} = 8 then {@RowMonthCDate} in [7,8]
else if {@ParMonthCDate} = 9 then {@RowMonthCDate} in [7,8,9]
else if {@ParMonthCDate} = 10 then {@RowMonthCDate} in [7,8,9,10]
else if {@ParMonthCDate} = 11 then {@RowMonthCDate} in [7,8,9,10,11]
else if {@ParMonthCDate} = 12 then {@RowMonthCDate} in [7,8,9,10,11,12]
else if {@ParMonthCDate} = 1 then {@RowMonthCDate} in [7,8,9,10,11,12,1]
else if {@ParMonthCDate} = 2 then {@RowMonthCDate} in [7,8,9,10,11,12,1,2]
else if {@ParMonthCDate} = 3 then {@RowMonthCDate} in [7,8,9,10,11,12,1,2,3]
else if {@ParMonthCDate} = 4 then {@RowMonthCDate} in [7,8,9,10,11,12,1,2,3,4]
else if {@ParMonthCDate} = 5 then {@RowMonthCDate} in [7,8,9,10,11,12,1,2,3,4,5]
else if {@ParMonthCDate} = 6 then {@RowMonthCDate} in [7,8,9,10,11,12,1,2,3,4,5,6]
)
AND
{report.fundingsource} = {?FundingSource} AND
{report.Site_id} = {?Site_ID}
Maybe not elegant, but it gives me the total for the field up to the Parameter Month for that fiscal year. Thank you again to heringer for working with me for about a week on this!
You can create a formula to build a Date using the year and the month (and consider day 1).
Then create 3 groups by this formula.
Use group expert to each group to make then yearly, quaterly, monthly.
Then put simple summary fields on the groups footers.
I am not sure if it fits in your final design. If not, please show it.
EDIT:
After the new comments, here is a new purposed solution. I will keep the old one above for futher reference.
Based com comments, i am assuming that the report will show data for one year, one month, one funding source and one siteid.
I understand you started at the right directions. I will try to help with the details.
{Table.Year} = {?Year} AND
{Table.Month} = {?Month} AND
{Table.FundingSource} = {?FundingSource} AND
{Table.SiteId} = {?SiteId}
numbervar rowNumberOfMonth := 0; //TODO i'm letting it up to you, since you said you did it
numbervar parNumberOfMonth := 0; //TODO
numbervar rowQuarter := (rowNumberOfMonth - 1) \ 4; //notice the integer divide, the symbol is not wrong
numbervar parQuarter := (parNumberOfMonth - 1) \ 4; //notice the integer divide, the symbol is not wrong
{Table.Year} = {?Year} AND
rowQuarter = parQuarter AND
{Table.FundingSource} = {?FundingSource} AND
{Table.SiteId} = {?SiteId}
{Table.Year} = {?Year} AND
{Table.FundingSource} = {?FundingSource} AND
{Table.SiteId} = {?SiteId}