This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

Yes, I'm afraid I'm back.. I just can't get this to work out.. I've attached a screen print this time of my report along with a PDF of what the report looks like thus far.

In the GROUP FOOTER 3, under the YTD column (row LY for last year), I am trying to get a sum of the Months that are <= current month but in the previous fiscal year which run from 10/1 to 9/30..

So at this moment I'd only want to see a sum that would run from 10/1 through current for the previous year.

Then I would want this to continue automatically going forward without the user needing to put in any dates. I am just not advanced enough to pull this one off. I've tried everything I can think of.. Including a formula someone shared with me on an earlier post.

zmyh-rep-cust-edp-fiscal-ytd.pdf

screen.gif

In the GROUP FOOTER 3, under the YTD column (row LY for last year), I am trying to get a sum of the Months that are <= current month but in the previous fiscal year which run from 10/1 to 9/30..

So at this moment I'd only want to see a sum that would run from 10/1 through current for the previous year.

Then I would want this to continue automatically going forward without the user needing to put in any dates. I am just not advanced enough to pull this one off. I've tried everything I can think of.. Including a formula someone shared with me on an earlier post.

zmyh-rep-cust-edp-fiscal-ytd.pdf

screen.gif

Experts Exchange Solution brought to you by

Enjoy your complimentary solution view.

Get every solution instantly with Premium.
Start your 7-day free trial.

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

-2 years through March -1 year only.. Then once we reach April I would need the formulat to pick up the sum of Oct -2 years through April -1 year so I'm always getting Last Years sum based on current month. I think the formula below can get me there somehow as MLMCC pointed out to me in another post but I'm missing something. This is only grabbing month if = to 1 which is January. I need it to pick up Oct, Nov, Dec, Jan, Feb, Mar, and then next month add April and so on...

IF Month({oelinhst_sql.billed

{oelinhst_sql.billed_dt} IN DateSerial (year(currentdate)-2,10,01

TO DateSerial (year(currentdate)-2,Month

(({oelinhst_sql.qty_to_shi

Else

if Month({oelinhst_sql.billed

{oelinhst_sql.billed_dt} IN DateSerial (year(currentdate)-1,01,01

TO DateSerial (year(currentdate)-1,Month

(({oelinhst_sql.qty_to_shi

you will have to modify the date ranges, but here is what I do for my company's fiscal reporting.

local numbervar x;

local numbervar y:= month(currentdate);

if y = 1 and y in dateserial(year(currentdat

then x:=(({oelinhst_sql.qty_to_

if y = 2 and y in dateserial(year(currentdat

then x:=(({oelinhst_sql.qty_to_

if y = 3 and y in dateserial(year(currentdat

then x:=(({oelinhst_sql.qty_to_

if y = 4 and y in dateserial(year(currentdat

then x:=(({oelinhst_sql.qty_to_

if y = 5 and y in dateserial(year(currentdat

then x:=(({oelinhst_sql.qty_to_

if y = 6 and y in dateserial(year(currentdat

then x:=(({oelinhst_sql.qty_to_

if y = 7 and y in dateserial(year(currentdat

then x:=(({oelinhst_sql.qty_to_

if y = 8 and y in dateserial(year(currentdat

then x:=(({oelinhst_sql.qty_to_

if y = 9 and y in dateserial(year(currentdat

then x:=(({oelinhst_sql.qty_to_

if y = 10 and y in dateserial(year(currentdat

then x:=(({oelinhst_sql.qty_to_

if y = 11 and y in dateserial(year(currentdat

then x:=(({oelinhst_sql.qty_to_

if y = 12 and y in dateserial(year(currentdat

then x:=(({oelinhst_sql.qty_to_

x

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
dont forget for each month you have to figure out what year you want to look at with your date range

local numbervar x;

local numbervar y:= month(currentdate);

if y = 1 and {oelinhst_sql.billed_dt} in dateserial(year(currentdat

then x:=(({oelinhst_sql.qty_to_

if y = 2 and {oelinhst_sql.billed_dt} in dateserial(year(currentdat

then x:=(({oelinhst_sql.qty_to_

if y = 3 and {oelinhst_sql.billed_dt} in dateserial(year(currentdat

then x:=(({oelinhst_sql.qty_to_

if y = 4 and {oelinhst_sql.billed_dt} in dateserial(year(currentdat

then x:=(({oelinhst_sql.qty_to_

if y = 5 and {oelinhst_sql.billed_dt} in dateserial(year(currentdat

then x:=(({oelinhst_sql.qty_to_

if y = 6 and {oelinhst_sql.billed_dt} in dateserial(year(currentdat

then x:=(({oelinhst_sql.qty_to_

if y = 7 and {oelinhst_sql.billed_dt} in dateserial(year(currentdat

then x:=(({oelinhst_sql.qty_to_

if y = 8 and {oelinhst_sql.billed_dt} in dateserial(year(currentdat

then x:=(({oelinhst_sql.qty_to_

if y = 9 and {oelinhst_sql.billed_dt} in dateserial(year(currentdat

then x:=(({oelinhst_sql.qty_to_

if y = 10 and {oelinhst_sql.billed_dt} in dateserial(year(currentdat

then x:=(({oelinhst_sql.qty_to_

if y = 11 and {oelinhst_sql.billed_dt} in dateserial(year(currentdat

then x:=(({oelinhst_sql.qty_to_

if y = 12 and {oelinhst_sql.billed_dt} in dateserial(year(currentdat

then x:=(({oelinhst_sql.qty_to_

x

Now, for instance on one of our items, I've got sales in Oct 2008 and Dec 2008 or .2 for each of those 2 months. However, using the above, what is being returned is .2, I am looking for is to return .4

I THINK I updated the date ranges accordingly but given my level of experience, I wouldn't doubt I've messed it up.. I sure appreciate your help.. Fiscal is going to drive me NUTS!!!

But hang on, I'm on to something here.. I'll post in a sec. if it works.. Did I mention you're the best! I now, I'm a KA!! lol

Thanks,

Nathalie

date in date(year(currentdate)-1,1

but this will always give you the current year to date..

local numbervar y:= month(currentdate);

if y = 1 and {oelinhst_sql.billed_dt} in dateserial(year(currentdat

then ({@LY 10 OCT INVOICED})+({@LY 11 NOV INVOICED})+({@LY 12 DEC INVOICED})+({@LY 01 JAN INVOICED})else

if y = 2 and {oelinhst_sql.billed_dt} in dateserial(year(currentdat

then ({@LY 10 OCT INVOICED})+({@LY 11 NOV INVOICED})+({@LY 12 DEC INVOICED})+({@LY 01 JAN INVOICED})+({@LY 02 FEB INVOICED})else

if y = 3 and {oelinhst_sql.billed_dt} in dateserial(year(currentdat

then ({@LY 10 OCT INVOICED})+({@LY 11 NOV INVOICED})+({@LY 12 DEC INVOICED})+({@LY 01 JAN INVOICED})+({@LY 02 FEB INVOICED})+({@LY 03 MAR INVOICED})else

if y = 4 and {oelinhst_sql.billed_dt} in dateserial(year(currentdat

then ({@LY 10 OCT INVOICED})+({@LY 11 NOV INVOICED})+({@LY 12 DEC INVOICED})+({@LY 01 JAN INVOICED})+({@LY 02 FEB INVOICED})+({@LY 03 MAR INVOICED})+({@LY 04 APR INVOICED})else

if y = 5 and {oelinhst_sql.billed_dt} in dateserial(year(currentdat

then ({@LY 10 OCT INVOICED})+({@LY 11 NOV INVOICED})+({@LY 12 DEC INVOICED})+({@LY 01 JAN INVOICED})+({@LY 02 FEB INVOICED})+({@LY 03 MAR INVOICED})+({@LY 04 APR INVOICED})+({@LY 05 MAY INVOICED})else

if y = 6 and {oelinhst_sql.billed_dt} in dateserial(year(currentdat

then ({@LY 10 OCT INVOICED})+({@LY 11 NOV INVOICED})+({@LY 12 DEC INVOICED})+({@LY 01 JAN INVOICED})+({@LY 02 FEB INVOICED})+({@LY 03 MAR INVOICED})+({@LY 04 APR INVOICED})+({@LY 05 MAY INVOICED})+({@LY 06 JUN INVOICED})else

if y = 7 and {oelinhst_sql.billed_dt} in dateserial(year(currentdat

then ({@LY 10 OCT INVOICED})+({@LY 11 NOV INVOICED})+({@LY 12 DEC INVOICED})+({@LY 01 JAN INVOICED})+({@LY 02 FEB INVOICED})+({@LY 03 MAR INVOICED})+({@LY 04 APR INVOICED})+({@LY 05 MAY INVOICED})+({@LY 06 JUN INVOICED})+({@LY 07 JUL INVOICED})else

if y = 8 and {oelinhst_sql.billed_dt} in dateserial(year(currentdat

then ({@LY 10 OCT INVOICED})+({@LY 11 NOV INVOICED})+({@LY 12 DEC INVOICED})+({@LY 01 JAN INVOICED})+({@LY 02 FEB INVOICED})+({@LY 03 MAR INVOICED})+({@LY 04 APR INVOICED})+({@LY 05 MAY INVOICED})+({@LY 06 JUN INVOICED})+({@LY 07 JUL INVOICED})+({@LY 08 AUG INVOICED})else

if y = 9 and {oelinhst_sql.billed_dt} in dateserial(year(currentdat

then ({@LY 10 OCT INVOICED})+({@LY 11 NOV INVOICED})+({@LY 12 DEC INVOICED})+({@LY 01 JAN INVOICED})+({@LY 02 FEB INVOICED})+({@LY 03 MAR INVOICED})+({@LY 04 APR INVOICED})+({@LY 05 MAY INVOICED})+({@LY 06 JUN INVOICED})+({@LY 07 JUL INVOICED})+({@LY 08 AUG INVOICED})+({@LY 09 SEP INVOICED})else

if y = 10 and {oelinhst_sql.billed_dt} in dateserial(year(currentdat

then ({@LY 10 OCT INVOICED})else

if y = 11 and {oelinhst_sql.billed_dt} in dateserial(year(currentdat

then ({@LY 10 OCT INVOICED})+({@LY 11 NOV INVOICED})else

if y = 12 and {oelinhst_sql.billed_dt} in dateserial(year(currentdat

then ({@LY 10 OCT INVOICED})+({@LY 11 NOV INVOICED})+({@LY 12 DEC INVOICED})

Crystal Reports

From novice to tech pro — start learning today.

Experts Exchange Solution brought to you by

Enjoy your complimentary solution view.

Get every solution instantly with Premium.
Start your 7-day free trial.