Calculated fields

I'm an SRS 2005 newbie. I'm trying to create a Cash Requirements
Forecast that plots values in six weekly buckets (columns) based on
the Required Date from line items in the PO. I'm stuck on one last
task in the report - getting the values bucketed - and am trying to
use calculated fields to do so.
REQDATE is the field from the PO line item that I'm applying this
expression to. startdate is a parameter that I have setup. I'm
trying to limit the output in a field on the report to a one-week
period beginning on the startdate that the user selects. Here is my
expression for that calculated field -
=Fields!REQDATE.Value>=Parameters!startdate.Value And Fields!
REQDATE.Value < (Parameters!startdate.Value+8)
First, is this the correct way to do this? If not, how should I
calculate this value and add it to my Matrix?
Second, should I be using a Matrix in my report or a Table? It seems
less than intuitive as to how to add column fields in a Matrix so
perhaps I should be using a Table instead.
Third, please keep any responses in plain English as I'm not a
programmer and I did not stay at Holiday Inn Express last night.
Thanks and best regards,
Frank Hamelly
MCP-GP, MCT
East Coast Dynamics
www.eastcoast-dynamics.comQuestions:
1. What's a PO?
2. Is this what you want your report to look like:
Week of 12/3-12/10 | Week of
12/11-12/18 |
Bikes
50 58
Cars
1 3
Boats
0 99
I'm guessing you want to combine all items that fit in that week.
Some answers:
Your expression will not work unfortunately. Second, you should use
the SQL statement in the data table and your parameter to limit the
records for the dates you select. Sorry I have to talk geek now so
book a room in the Holiday Inn (I still have no idea what that joke
referenced). For example:
select *
from table1
where datetime >= @.startdate
When the user puts a start date, the query will only bring back data
after that so you are limiting your data from the get go. If you
understand me so far and you can answer my questions, I can try to
help you out a little more.|||On Dec 5, 9:53 am, SQL Guy <ayma...@.gmail.com> wrote:
> (I still have no idea what that joke referenced).
There is a long-running series of television commercials here in the
USA about a chain of hotels called "Holiday Inn Express". In the
commercial, a person is doing a very technical job (doctor,
electrician, etc) using all sorts of jargon, and at the end of the
commercial the person says "Oh, I'm not a <profession>, I just stayed
at a Holiday Inn Express last night", implying that simply sleeping at
the hotel will make you more rested and more intelligent the next
day.
As far as the query, SQL Guy is correct, it will be more efficient to
limit the data to 7 days in the Data tab (the query itself) rather
than filtering the data in the report. Imagine that 5 years from now
your query returned 20,000 rows, so every time you view the query, it
has to pull all the data from the database, then filter it down at
Report Render time to the 10 that occurred this week. Inefficient.
It is much better to put a WHERE clause in the query to let the server
limit the rows.
Next the "weekly buckets": go to your DataSet, right-click and Add a
new Field, then give it a calculated expression like:
= DateAdd( "d", 1 - Weekday(Fields!datetime.Value), Fields!
datetime.Value )
for every row in your DataSet, this returns a DateTime equal to the
Sunday in that week based off of the field datetime. You can then
group on this calculated field just like you would any othe from your
original dataset.
The Matrix object is good when you have an undetermined number of
elements that you want to group by. For example, you put LineItem in
the Rows group, the calculted Week field in the Column group, and
=Format( Sum( Fields!amount.Value ), "$#,##0.00") in the Details
group. You dont care how many line items you have to report on, or
how many weeks you are reporting, the object draws it.
But, a Table is good for... well... tabular reports. You have one
report for a week period, each Detail row is a line item, and you have
a column that is explicitly one value of your billing amount per line
item. Sum()s then go in headers and footers, with some grouping
capability as well. When you Group here (by right-clicking and Adding
a Group), it just repeats the Grouped sections, and every section has
the exact same explicit layout.
-- Scott|||Thank yoiu Scott and SQL Guy. Scott, what does the "d" in the
expression represent?
Thx, Frank|||Also, assuming I need 6 distinct weekly buckets in my report, I need
to define 6 calculated fields with explicit filtering for each field,
correct? If so, how/where do I insert the calculated fields into the
matrix so that the result is a 6-week header with one column for each
week?
Thanks so much. I really appreciate the help. I've been trying to
figure this out on my own for too long now!
Frank|||On Dec 5, 11:54 am, "Frank Hamelly, MCP-GP" <fhame...@.cfl.rr.com>
wrote:
> Also, assuming I need 6 distinct weekly buckets in my report, I need
> to define 6 calculated fields with explicit filtering for each field,
> correct? If so, how/where do I insert the calculated fields into the
> matrix so that the result is a 6-week header with one column for each
> week?
> Thanks so much. I really appreciate the help. I've been trying to
> figure this out on my own for too long now!
> Frank
The "d" represents Day. The DateAdd function is very funky. Unlike
normal VB, the DateAdd and Format commands use case sensitive
identifiers: MM for month, dd for day, yyyy for year, HH for 24 hour
format, hh for 12 hour format, mm for minutes, ss for second.
The way a Matrix works is that it takes the Field that you supplied,
breaks it into Groups containing distinct values of the contents of
the Field, then Aggregates on the Group in the Details section. The
two display groups are Rows and Columns.
Think of it this way, you have a list of items with similar
characteristics:
Shape, Color, Price
Circle, Red, $10
Circle, Green, $25
Square, Blue, $15
Square, Red, $5
If you drag the Shape field into a Row of a Matrix, and the Color into
a Column, and the =Sum( Fields!Price.Value ) in the Details section,
when you go to Preview mode you will see two rows (Circle and
Sequare), three columns (Red, Green, Blue), and Summed values ($10 |
$25 | Nothing || $5 | Nothing | $15) in the Details table. You didn't
have to define anything, it just evaluated the row contents and
created the groups.
For your dataset:
-- If using a Matrix, all you need is a Field that returns 6 distinct
values for your 6 weeks, and it will automatically build 6 columns.
Any function in the Details section is going to display the aggregate
for only the
-- If using a Table, you will need 6 distinct calculations, and it
gets a little trickier.
1. Add a "WeekDate" Field that rounds the ReqDate back to the first
day of the week using the method previously outlined.
2. Drag the "WeekDate" into the Column section of the Matrix
3. Drag the "LineItemName" into the Row section of the Matrix
4. Drag the "AmountNeedingAggregation" into the Details section of
the Matrix
Right-click on the Row/Column to and Edit Group to change Sorting,
Filtering, etc.
-- Scott|||Thanks for the Holiday Inn info. I've stayed in so many but I don't
watch TV at all so that's why I was missing that valuable nugget of
information. I now feel like I've spent the night there!
"d" stands for day. That function is quite nice for many things. Lets
say you want to default a parameter to last week you can simply type
for the default parameter =dateadd("d",-7,today()) - today is a
function that gets today's date in RS. So what I understand from
Orne's suggestion is that he will take the date in the field and
subtract it from... actually I'm not really following that so much
maybe because it's almost closing time. I think it can be done in
tabular form but I will wait for Orne's explanation so I can better
understand what he's trying to get at.|||Thank you Scott and SQL Guy for your help. I'm going to give your
further suggestions a try tomorrow.
Thanks again,
Frank