Need help with my poorly done report.

I have a report that I somehow got it to work. But I need some advices to make it better.

Data:

matl, valid from, valid to, category, …

a, 2/1/2004, 7/10/2004, A
b, 5/23/2004, 9/10/2004, B
. … … .

the report is: for every month, report the counts of the valid matl


Category, 2004 01, 2004 02 , 2004 03 …
A, 123, 124, 154
B, 22, 50, 100 …

I created one variable for each month using some formula like this:

2004 01 = If FormatDate( ,“yyyy mm”) <= “2004 01” And FormatDate(-1 , “yyyy mm”) >= “2004 01” Then 1 Else 0

Somehow I got the counts I wanted. But since there are different variables involved, I can’t do a trend graph on that.

I really don’t like the way I did this. Format is a problem and also I have to add variable every month.

Can anyone help me out?

Thanks.

Joanna


joanna (BOB member since 2004-01-29)

What about creating a count distinct object in the universe which can then be used to group by the month and category. You would also need to use a between clause to handle the valid from and valid to fields.


Bobjoy (BOB member since 2002-08-20)

hi
did u try count the variable using function like for all or for each .
try creatin seperate variable using this


mayur :india: (BOB member since 2004-07-22)

Thanks for helping.

This is a freehand SQL report. No universe is involved.

I hope I can use a single variable for the count but I don’t know how.

I don’t know how to use or in this case. Please help.

Joanna


joanna (BOB member since 2004-01-29)

Joanna,

count foreach (FormatDate( ,“yyyy mm”))-counts distinct values
count allforeach(FormatDate( ,“yyyy mm”))-counts all values with duplicates

Create a seperate variable and try to drag the variable and put it against category.
Hope this works.


mayur :india: (BOB member since 2004-07-22)

Mayur,

Thank you for taking your time to help me. When I tried your way, it gave me syntax error. I was not able to create a variable that way. Where does come in play in this case?
Thanks again.

Joanna


joanna (BOB member since 2004-01-29)