Month by Month Formula

[Former Member]

Post Author: hd1

CA Forum: Formula

I was wondering if someone could point me in the direction on how to write a formula that would generate a new month. Currently I have my report that has the total fees received for a specific employee.

Matter Client Description $xxxx

Now I would like to add a new column so it looks like this.

06/07 07/07 08/07

Matter Client Description $xxxx

Is this possible? I would need it to generate a new month every month.

Thanks!

Brian

[Former Member]

Post Author: hd1

CA Forum: Formula

I didn't think it would be that easy! You were right on it. Shows it month by month and total fees received. It list everything I need and it occurs every month. Thank you so much!

[Former Member]

Post Author: sleahcim

CA Forum: Formula

Hi hd1,

If it is creating a new crosstab for each item, then it sounds like you have created a group in your report design. Place the crosstab in the Page Footer, and remove the group. You will only have one, continous crosstab that way.

Cheers,-Michael

[Former Member]

Post Author: hd1

CA Forum: Formula

Ok, I figured out how to suppress certain columns or totals.

8/1/20070000.0000 Nonpareil Corporation General Representation $67.50Total $67.50

The next thing is it creates a whole new crosstab right below it, and so on. The information would be for the next matter number but it's in a whole new cross tab. One thing at a time though right? Any ideas?

[Former Member]

Post Author: hd1

CA Forum: Formula

Well we have two date columns one is for beginning date and the other is for the end date. But all dates that I would like to sort by are in the periodt.pebedt that way it goes by the first of the month. I tried using the cross-tab but I get my formatting all mixed up. I couldn't figure out how to display the items in a row. It would create multiple rows and "totals." I don't know if you are allowed to put multiple information in a single row.

This is what mine ended up looking like in the cross tab.

$67.50

Matter Client Description $67.50

Total $67.50

$67.50

Total $67.50

I would like it to look like this

June 07 August 07 September 07

Matter Client Description $67.50 50.00 0.00

Total

[Former Member]

Post Author: sleahcim

CA Forum: Formula

Hi hd1,

If I understand correctly, you have a date column in your database table called periodt.pebedt. This column is used in your report selection criteria, and the purpose of the selection is to only select records that correspond to the pebedt date of (2007, 06, 01). You also have a second date column, called something else, and that is the column you would like on your crosstab, right? As long as all of those other date records for whatever span of months has a pebedt date of June 6th, 2007, then they will show up on your crosstab. You are also free to use parameters to further restrict the selection of your records.

-Michael

[Former Member]

Post Author: hd1

CA Forum: Formula

I didn't think about that. I will give that a try. The thing is I had this in my selection record:

{periodt.pebedt} = Date (2007, 06, 01)

So the cross tab will be able to sort out the records I have that are located in another field? I was wondering about parameters as well but the previous guy hasn't got back on yet. What time zone are you in?

[Former Member]

Post Author: sleahcim

CA Forum: Formula

Hi hd1,

Another method, if you want to avoid writing a formula for each month, would be to use a crosstab. You can format your date column on the crosstab to be a month, month-year, or most any other format you desire.

-Michael

[Former Member]

Post Author: hd1

CA Forum: Formula

I guess that does make sense. I put the columns in there already. So this is just going to be a report that I would breifly edit monthly, that's no big deal. Here is what some of the data looks like right now.

00010.0005 Business Objects. General Corporate Matters 9880.

Now you mention parameters, I would like the report to run without entering any, is that possible?

[Former Member]

Post Author: yangster

CA Forum: Formula

really depends on how you are pulling in the dataon how you setup the formulathe columns are not going to magically appear on their own so you need to create them ahead of timei've create tons of reports that display monthly breakdowns for a full year based on begin and end date parametersdo you have any sample data of what you are dealing with?