system
July 21, 2004, 8:18pm
1
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)
system
July 21, 2004, 9:55pm
2
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)
system
July 22, 2004, 11:45am
3
hi
did u try count the variable using function like for all or for each .
try creatin seperate variable using this
mayur (BOB member since 2004-07-22)
system
July 22, 2004, 1:31pm
4
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)
system
July 23, 2004, 5:21am
5
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 (BOB member since 2004-07-22)
system
July 23, 2004, 2:07pm
6
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)