Report problem

[pali]

hi all
I am very new to reportnet and i have a requirement like this
I have a table AAA with fields recruitter Name , join_date, Salary

Now i need to produce a report which will show total salary for previous 12 months and
the starting month and year is provided by prompt page. Report will look like this.
If prompt values are like this Jan 2006
then report will be like this

===============================================================================
recruitter Name     Fab-2004      March-2004 ..............................................................Jan-2005                       Total
===============================================================================
abc                           50000         45454545 ...................................................................................................654564
xyz                           878544.............
================================================================================
                                                                                                                                                                987454564
================================================================================

Please Help.

[BIsrik]

One idea that strikes my mind is to create 12 data items, each for every month and have an if-else clause in each data item. Have an overall filter which would fetch data for the previous 12 months.

Let me also try to simplify the soln also..

Srik

[bdybldr]

Pali, I don't understand the desired output.Ã,  Assuming a prompt selection of Jan 2006, is this the format you're looking for?Ã,  Please comfirm.

Recruiter NameÃ,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Jan 2005Ã,  Ã,  Ã,  Ã,  Ã,  Ã, Feb 2005Ã,  Ã,  Ã,  Ã,  Ã,  Mar 2005..........Dec 2005

JohnsonÃ,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  500,000Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  385,000Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  620,000Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  520,000
SmithÃ,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  610,000Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  590,000Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  735,000Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  482.000

[bdybldr]

pali, you need to establish relative date ranges in your report.  Try this...

Create 12 data items in your query.  The first one will be defined as follows:
IF
(months_between([Date], ?parmDate?) = -12)              *****NOTE: parmDate is the prompt value.
THEN
(salary)
ELSE
(0)

The next data item will be defined the same way except you need to replace the -12 with -11 and so on.

Make sure that the aggregate function for all 12 data items is set to "Total"

HTH...Please keep us posted on your progress.

[Boris-A]

Instead of 12 data items like you mention, I would have gone with a simple date field.  Your filter is where you want to use the add_months function.

You'll have to filter kinda like this (I don't have the exact expression but the logic should point you in the right direction).

months_between([Date], ?parmDate?) >= -12
AND
months_between([Date], ?parmDate?) <= 0

[pali]

Hi all
Sorry for late reply.As there is differnce in time zone.
But any way thanks for reply.Its working by this way also.
I have mean while tried with other way by creating view.that is also working.
report made with 12 data items runs a bit slow.while the report made with view is a bit fast.
So now i want to know why this so.
And when to for view and when to use more data items with complex calculation.
waiting for suggestions.
thanks

[bdybldr]

Pali,
It's generally advisable to defer processing of complex calculations and data transformation to the server.  In those cases, I would create a new table or view.  When you are performing simple calculations, it's acceptable to process them locally.

[bdybldr]

Boris,
The filter will work if Pali wanted only one total column for all 12 months in his report but the requirements dictate a column for each of the 12 previous months.  That's the reason for the 12 data items.

[Boris-A]

Boris,
The filter will work if Pali wanted only one total column for all 12 months in his report but the requirements dictate a column for each of the 12 previous months.Ã,  That's the reason for the 12 data items.

I have to disagree.Ã,  If he does a crosstab, he could then use the "date" field in the report directly.

[Boris-A]

Hi all
Sorry for late reply.As there is differnce in time zone.
But any way thanks for reply.Its working by this way also.
I have mean while tried with other way by creating view.that is also working.
report made with 12 data items runs a bit slow.while the report made with view is a bit fast.
So now i want to know why this so.
And when to for view and when to use more data items with complex calculation.
waiting for suggestions.
thanks

If you really want your 12 data items, I would go with two tabular models (parent-child).  The first (child) would bring you all your detailed data (filtered using my calculation above ideally).  And your second (parent) would break it down into multiple data items.  You would get much better performance that way because all your calculations would be based on a subset of the data (child tabular model).

Does this make any sense to you ?

The reason is that when you do a if, then, else statement in the query items, you bypass the indexes (unless I'm mistaken)..

Hope this is of some help

[bdybldr]

If you build a list report, you would still have to use an if then else construct on each of your data items to seperate them into months.  You can simply create a filter on the same query to limit your data set.

[Boris-A]

I understand that.  But if you have a child tabular model (whith the filter) that brings you back all your data (no if then elses) and have another (parent) tabular model with all the if then elses you want, your performance will be greatly improved.  I speak of experience.

Try it, you shall see the difference.

Cheers,