Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 452
  • Last Modified:

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







0
bsarahim
Asked:
bsarahim
  • 7
  • 5
1 Solution
 
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
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
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
 
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

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

  • 7
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now