bar graph and how to write a query in ms access, vb.net,crystalreports

>> let say he selected 3 months and the date 28 Feb, i should get from the above query like this format
>> Col1       2005/12   2006/01   2006/02
>> xxxx             9               2      12
>> x2               10               2      10
>> x3                13              2      12
>> x4                 0              0      14

bsarahim, if user selected 3 months and date 28 Feb, could you further explain how is the date 28 Feb reflected in the cross-tab? from the sample above I could only see 3 months data... but not for 28 Feb.

dylan

let put this way ignore 28,
just take it as Feb (month Selected)
so
Feb
jan
Dec
only

Ok.. but which table is containing the date field?

dylan

sorry to tell u inspection table contains insp_DateTime(Date/Time)

bsarahim, could you try out the below sql query? Please put the query into your CR's Command object, and create 2 string parameter {?FromDate} and {?ToDate}. the formate of the parameter should be in "yyyy/mm" format:

SELECT    A.ityp_id, B.YearMonth,
          IIf(IsNull(B.CountOfinsp_id),0,B.CountOfinsp_id) as  CountOfinsp_id
FROM      InspectionType AS A
          LEFT outer  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 {?FromMonth} TO {?ToMonth}
          GROUP BY InspectionType.ityp_id, Format(Inspection.insp_DateTime, "yyyy/mm")
) AS B
      ON A.ityp_id = B.ityp_id

If the query runs ok, it should give you the result of

ityp_id     YearMonth         CountOfinsp_id
xxxx        2005/12            5  
xxxx        2006/01            3
xxxx        2006/02            2
x2           2005/12            2  
x2           2006/01            2  
x2           2006/02            1  
.....

You can then use the data to create your cross-tab and bar char.
For cross-tab, put the ityp_id in rows, YearMonth in columns, and CountOfinsp_id in Summarized Fields.
Once you created your cross-tab, you can then right click your cross-tab and insert chart, Go to Data tab and choose subdivided by ityp_id.

dylan

Sorry, create 2 parameter {?FromMonth} & {?ToMonth}.

dylan

I think you should also add a '' to fromdate and todate parameter since they are string:

WHERE      Format(Inspection.insp_DateTime, "yyyy/mm") BETWEEN '{?FromMonth}' TO '{?ToMonth}'

dylan

thanks for your reply
onchange inspection.ityp_id
subdivided by inspection.yearmonth
show sumofinspection.countofinsp_ud
works fine

but i want to show the below the inspection.itp_id ,
i want label show vertically all the items
so that the Gap will be there for each ityp_id

How about... onchange inspection.yearmonth and subdivided by inspection.itp_id? will it give you the desired result?

dylan

hi dylan,
the result is wrong, does not tally.

could u pls answer my prv question.
i just want a verical label(ityp_id) items, so that i can view

thanks

bsarahim, I am not quite sure what do you want in your bar chart, could you tell me what do you want it to be at X-axis and Y axis?

You can try on change of YearMonth sum of inspection.countofinsp_ud. Leave the subdivided by blank.

dylan

x-axis is number like 0 to 40 (sum of countofinsp_id)

y-axis is ityp_id like x1, x2, x3, x4...

you should create a bar chart with
on change of ityp_id and
sum of countofinsp_id.

This will give you a Y-axis with total count of CountOfinsp_id regardless of YearMonth, and X-axis with ityp_id.

dylan

thanks
sorry i mistaken
x i thought y axis

x should be with ityp_id (onchange)
 inspectiontype.year month(subdivided by)
 and y is total count of insp_id (show)

this works fine and tally the result

but in the x axis(itpy_id) i need a gap between the labels because i have sooo many itypy_id items, and i need to scroooll to long....
i.e., i want to display  in vertical.

(y axis)
      |
 40 |
      |
 30 |
      |
 20  |
      |
 10 |
      |
    0 --------------------------------------------(x axis)
           x       x         x      x .....
           x       x         x      3 ...
           1      2          3     3

            (ityp_id & year month)

bsarahim, which CR version are you using? the one that come together with VS.net 2003?

- if you want to change your X-axis label to display vertically, in your chart, choose the x-axis's label carefully and right click->chart options->selected item, from there you could change the display style of your x-axis label, you could rotate it to display vertically.
- You could also make your x-axis to display only odd number of labels instead of display all labels:
right click your chart -> chart options-> grid->Group Axis->Labels.
Check the checkbox for "Use manual number of groups between label", and enter 2 in the textbox next to it.

I am using CR9, don't know CR.net 2003 have the same option or not. pls let me know.

dylan

sorry dylanyee,
i dont see any option
in my vs.net

how to check which version iam using CR

pls help me to answer this query
https://www.experts-exchange.com/questions/21785422/bar-graph-and-query.html