Additional dates 1

[ganjass]

CR10

Hi All i have a quirky issue. I have a group that displays the year and month, dependant on paramters. The issue i have is the report needs to display an additional 4 months from the last date in the group, even though they are blank, and refelct the year as well, any ideas.

thanks in advance

 

[ganjass]

In addition, I'm getting an out of range if i try and dateadd on the maximum paramater field.

Cheers

 

[synapsevampire]

When you say that the dates are blank I'll assume that you mean that there is no data, which is a very different thing.

I have a FAQ which discusses this and provides SQL for creating a Period table:

faq767-4532

-k

 

[ganjass]

synapse vampire that looks ideal although, i dont have the luxury of being able to create a stored procedure or table.

the format i have to have is say i get a date range 01/10/03 to 01/02

I have to display it as so:
No Value
Jul-03
Aug-03
Sep-03
Oct-03
Nov-03
Dec-03
2003 Total Total
jan-04
feb-04
mar-04
apr-04
2004 Total Total

This is not proving to be as easy as i'd hoped.

Any ideas how i can achieve this?

 

[synapsevampire]

What you're asking for is to have Crystal fabricate data.

Your example of a date range made no sense:

01/10/03 to 01/02

And the original problem description "The issue i have is the report needs to display an additional 4 months from the last date in the group" probably means that you don't want an additional 4 months from the last date in the date parameter range, rather that data does not exist for the full period and you would like to create those dates even though they don't have data.

What you're not allowing for is that even within the range you may not have any data also, whcih means that you need to fabricate data for those periods too.

Everyone might be better served if you provided more technical information:

Cryustal version
Databases used
Example data
Expected output

Even though you don't have the ability to create a table, View or SP on the database, you might be able to still use some method to offload this to the database.

But this would be dependent on technical information, which we have none of.

If you're just trying to construct a cheat to create some display field for months beyond a current month, you can use a dateadd on a date range:

dateadd("m",1,maximum({?dates}))

-k

 

[ganjass]

Synsapse I mentioned it was CR10, ok i did't include the RDBMS, which is Progress9.1c.

I have used the dateadd scenario, where by 4 dateadd formulae in the footers pad out the additional dates. The problem i have is where the date range is up til january 04 so i have to sum the 03 totals, and then total the 04 totals. I have tried 1 group for months and one for years on the same date field but cant get it to work.

Thanks in advance

 

[synapsevampire]

Grouping by Year, and then by month within makes sense. You can then easily provide sums for any field within either group.

Stating that something doesn't work doesn't help much, please qualify with examples of the data used and what you'd like to see returned.

-k

 

[ganjass]

sorry about the vagueness. Well getting the totals to work out is fine as you say, its getting the dummy
dateadd("m",1,maximum(date)) within the totals eg

say for 2004 i only have 2 genuine months that i have to add, i will have the months before hand making up 03, they can be added up no problem, its the 04 months that cause the issue as the total comes after the genuine months and the padded out no data months.

I have the totals in the date by year groupfooter. How would i go about adding the dummy months so that they appear before the totals. But are not repeated throughout the group??

Thanks again

 

[synapsevampire]

Try right clicking the group footer and selecting insert section below.

Now right click that new section and select X 2 next to suppress and place:

not(onlastrecord)

Now this will only display on the last row (hence all other groups are displayed.

You don't need to worry about having these added to the totals as they don't exist, this is just a hack to allow for a one time report (I hope).

Now you can use whatever means you're using in the report footer to display whatever you need in the 2nd conditionally grouped footer.

-k