bar graph and query

hi,
pls visit for more info, based on the same query
http://www.experts-exchange.com/Databases/Crystal_Reports/Q_21781980.html

SELECT A.ityp_id, B.YearMonth, IIf(IsNull(B.CountOfinsp_id),0,B.CountOfinsp_id) AS CountOfinsp_id
FROM InspectionType AS A LEFT JOIN [SELECT     InspectionType.ityp_id,
                     Format(Inspection.insp_DateTime, "yyyy/mm") AS YearMonth,
                     COUNT(Inspection.insp_id)  AS CountOfinsp_id
          FROM       (((((Sector
                     INNER JOIN Building
                            ON Sector.sect_id = Building.sect_id)
                     INNER JOIN InspectionUnit
                            ON Building.buil_id = InspectionUnit.buil_id)
                     INNER JOIN Inspection
                            ON InspectionUnit.unit_id = Inspection.unit_id)
                     INNER JOIN InspectionSchedule
                            ON Inspection.sinp_id = InspectionSchedule.sinp_id)
                     INNER JOIN InspectionType
                            ON InspectionUnit.ityp_id = InspectionType.ityp_id
                            AND InspectionSchedule.ityp_id = InspectionType.ityp_id)
          WHERE      Format(Inspection.insp_DateTime, "yyyy/mm") BETWEEN '2005/12' And '2006/02'
          GROUP BY InspectionType.ityp_id, Format(Inspection.insp_DateTime, "yyyy/mm")
]. AS B ON A.ityp_id = B.ityp_id;

----
the above query works fine
iam having a form vb.net

and iam having a textbox [ending date]  value is 19-Mar-01
and a Drop Down box choosing [weekly or fortnightly,Monthly]value is Weekly
and a drop down box using value  is 3

pls help me to change above query in the crystal report to generate a bar graph, that can work weekly, fortnighly & Monthly

if let say i choose weekly
the value should appear(ityp_id) items in bar graph
the dates  should get in legend below chart

27/02/2006 - 05/03/2006                    
06/03/2006 -12/03/2006
13/03/2006-19/03/2006

If let say i choose Fortnightly

06/02/2006 - 19/02/2006
20/02/2006 - 05/03/2006
06/03/2006 -09/03/2006

if let say i choose monthly
12/2005
01/2006
02/2006

all must be  dynamic parameters
1. monthly,fortnightly, or weekly (dropdown)
2. No of months/week(3,4,5) (dropdown)
3. Ending date can change (textbox)

waiting for your reply







bsarahimAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

dylanyeeCommented:
bsarahim, please specify what do you want it to be at X-axis and Y-axis...

dylan
0
bsarahimAuthor Commented:
if i am not wrong
x-axis is number like 0 to 40
y-axis is ityp_id like x1, x2, x3, x4...

the dates  should get in legend below chart

27/02/2006 - 05/03/2006                    
06/03/2006 -12/03/2006
13/03/2006-19/03/2006
0
bsarahimAuthor Commented:
x-axis is  sum of countofinsp_id
0
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

bsarahimAuthor Commented:
(y axis)
      |
 40 |
      |
 30 |
      |
 20  |
      |
 10 |
      |
    0 --------------------------------------------(x axis)
       (ityp_id & year month
0
dylanyeeCommented:
>> Fortnightly
>> 06/02/2006 - 19/02/2006
>> 20/02/2006 - 05/03/2006
>> 06/03/2006 -09/03/2006

bsarahim, what is Fortnightly....? from your example above, 06/03/2006 -09/03/2006 is only 4 days...

I think you already done your chart for monthly data right? For weekly and fortnightly, you need to have a same partern for all the dates.
Like monthly report, the chart will plot the bar for 2/2006, 3/2006, 4/2006.
But if you want a weekly report (27/02/2006 - 05/03/2006, 06/03/2006 -12/03/2006, 13/03/2006-19/03/2006), you need to provide a same partern to the dates, otherwise the graph will plot to bar for everydays. For weekly's case, I think you could use week number. But I don't have any idea for Fortnightly... since I don't know what is it :p

This is the example of query if you want to plot the chart base on week number, I use Datepart("ww", Inspection.insp_DateTime)  to obtain the week number:

SELECT A.ityp_id, B.YearMonth, IIf(IsNull(B.CountOfinsp_id),0,B.CountOfinsp_id) AS CountOfinsp_id
FROM InspectionType AS A LEFT JOIN [SELECT     InspectionType.ityp_id,
                     Datepart("ww", Inspection.insp_DateTime) AS YearMonth,
                     COUNT(Inspection.insp_id)  AS CountOfinsp_id
          FROM       (((((Sector
                     INNER JOIN Building
                            ON Sector.sect_id = Building.sect_id)
                     INNER JOIN InspectionUnit
                            ON Building.buil_id = InspectionUnit.buil_id)
                     INNER JOIN Inspection
                            ON InspectionUnit.unit_id = Inspection.unit_id)
                     INNER JOIN InspectionSchedule
                            ON Inspection.sinp_id = InspectionSchedule.sinp_id)
                     INNER JOIN InspectionType
                            ON InspectionUnit.ityp_id = InspectionType.ityp_id
                            AND InspectionSchedule.ityp_id = InspectionType.ityp_id)
          WHERE      Inspection.insp_DateTime BETWEEN BETWEEN 'YourStartDate' And 'YourEndDate'
          GROUP BY InspectionType.ityp_id, Format(Inspection.insp_DateTime, "yyyy/mm")
]. AS B ON A.ityp_id = B.ityp_id;

dylan
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
dylanyeeCommented:
sorry, the above query should group by:
GROUP BY InspectionType.ityp_id, Datepart("ww", Inspection.insp_DateTime)

dylan
0
dylanyeeCommented:
For Fortnightly (bi-weekly), you could try
Datepart("ww", Inspection.insp_DateTime) % 2, this will group 2 week's data together.

dylan
0
bsarahimAuthor Commented:
hi dylan,

Datepart("ww", Inspection.insp_DateTime)%2 will get error

syntax errorin query expression Datepart("ww", Inspection.insp_DateTime)%2

i have changed to Datepart("ww", Inspection.insp_DateTime)/2 but gives wrong result

help me dylan
thanks
0
dylanyeeCommented:
How about
(Datepart("ww", Inspection.insp_DateTime) + 1)/2   ?
If you want a Bi-weekly result. the (week number + 1)/2 will let you group by 2 weeks. eg)

week 1 + 1/2:   1
week 2 + 1/2:   1
week 3 + 1/2:   2
week 4 + 1/2:   2
week 5 + 1/2:   3
week 6 + 1/2:   3

dylan
0
dylanyeeCommented:
sorry bsarahim, Please use \ instead of /.
Datepart("ww", Inspection.insp_DateTime)\2
or
(Datepart("ww", Inspection.insp_DateTime) + 1)\2

dylan
0
bsarahimAuthor Commented:
hi everything works fine results absolutely correct,
but
show legend

come like this  

[] 4   []  5

but i dont want like this

my legend should look like this for week(selection)
[] 27/02/2006 - 05/03/2006                    
[] 06/03/2006 -12/03/2006
[] 13/03/2006-19/03/2006

wht should i have to do, do i need to change my query or graph.
pls help  dylanyee
thanks

0
dylanyeeCommented:
You could create a formula in crystal report to convert the bi-week number to date, then use that formula to plot your graph.
There is no direct function you could use to convert bi-week/week number to date, you have to make one your own. The logic is like:

1) find out the "week day number" for 1st day of Year
2) subtract the "week day number" from 1st day of Year, this will get you the "1st week's begining date".
3) Now you can make use of the "1st week's begining date" as a starter. Use dateadd to add your bi-week's number to "1st week's begining date". eg) dateadd("d", ({table.biweekno} - 1) * 2, "1st week's begining date")

Give it a try, if you need further assistant, let me know.

dylan
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Crystal Reports

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.