Creating a Formula that is linked with a Parameter

[Sahubba]

Hello,
I am trying to display how frequently orders are being used. I need to display how many times these orders have been used per month for the last 3 months. Ideally, I would like to linked the months to the parameters so the month will only display when the months are chosen within the date range. Example;

{Date Range}(4/21/2011 to 6/21/2011)

Month: April May June
Orders: 15 20 0

{Date Range}(1/21/2011 to 3/21/2011)
Month: Jan Feb Mar
Orders: 36 51 6

I hope this makes sense

 

[IanWaterman]

Use a cross tab and do not add anything to row, add your count to summary box. For the column select an appropriate date field and set group option to print for each month.

Ian

 

[Sahubba]

Thanks Ian for your respond.

Do you know of any other way besides a crosstab?

They do not want a crosstab.

 

[IanWaterman]

Why not use a cross tab?

If you do not use a standard cross tab it becomes a a very complex manual cross tab.

Ian

 

[Sahubba]

Hello,
Because I have 4 groups and 5 drilldown levels.
and how they customer want the report it will be impossible to use a cross tab. The only way that I could think that would work is creating a formula that links the parameters.

Are you telling me that can not be done?

 

[lbass]

Yes, it can be done. Please identify the fields you are grouping on, in order.

Also clarify whether the report would only be run for a three month period or whether the intention is to group by quarter and then within the quarter show monthly totals.

-LB

 

[Sahubba]

ok,

Group 1 = Location
Group 2 = Department
Group 3 = Provider
Group 4 = Enc

This customer wants the report to be run for the last 3 months.
But I was going to place a date range and they could choose the date the want.

The reason behind that is so more than one customer can use this report

 

[lbass]

What is "Enc"?

Let's say you create a parameter {?End} for the end date, assuming the user would want the last three months. Then in the record selection formula, you could use:

{table.date} in dateserial(year({?End}), month({?End})-2,1) to {?End}

Then create three formulas like this:

//{@TwoMonthsAgo}:
if {table.date} in dateserial(year({?End}), month({?End})-2,1) to
dateserial(year({?End}), month({?End})-1,1)-1 then
1 //or {table.qty}

//{@OneMonthAgo}:
if {table.date} in dateserial(year({?End}), month({?End})-1,1) to
dateserial(year({?End}), month({?End}),1)-1 then
1 //or {table.qty}

//{@CurrentMonth}:
if {table.date} in dateserial(year({?End}),month({?End}),1) to
{?End} then
1 //or {table.qty

Place these in the detail section and insert summaries on them at one or more group levels and/or at the report level. Then you can suppress the detail section if you wish.

-LB

 

[Sahubba]

Enc is short for encounter.

Would this still work if someone would choose
Jan feb and mar?

Or would this just work the last 3 months from todays date?

 

[lbass]

You would ask them to enter last day of the last month, or March 31, at the prompt for the date parameter.

-LB

 

[Sahubba]

Thank you LB.
I have one more question...sorry, I wanted to display the month in the page header, how can I link that text to the formulas?

Example;
April May June
Location: Mercy 8 9 10
Location: Demaue 11 8 2
Loaction: Choice 18 12 9

 

[lbass]

Create formulas for the column headers like this:

//{@TwoMonthsAgoLabel}:
totext(dateserial(year({?End}),month({?End})-2,1),"MMMM")

//{@OneMonthAgoLabel}:
totext(dateserial(year({?End}),month({?End})-1,1),"MMMM")

//{@EndMonthLabel}:
totext({?End},"MMMM")

-LB

 

[Sahubba]

Thank LB!
Everything looks great!