• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 454
  • Last Modified:

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

hi pls
http://www.experts-exchange.com/Databases/Crystal_Reports/Q_21772659.html
check it out for this below query.
----
SELECT    A.ityp_id,
          IIf(IsNull(B.CountOfinsp_id),0,B.CountOfinsp_id) as  CountOfinsp_id
FROM      InspectionType AS A
          LEFT outer  JOIN
(
          SELECT     InspectionType.ityp_id
                     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)
GROUP BY InspectionType.ityp_id
) AS B
      ON A.ityp_id = B.ityp_id
------
this query give the resutl

xxxx   10  
x2       5  
x3      11  
x4       0    
but
Actually I am having vb.net(2003) win form where the user selects the month in combobox.
and the date
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

1. how to write a query (based on the form selection)
2. i want to display above result in the bar garph
    showing legeng as Dates (2005/12   2006/01   2006/02).how should use it







0
bsarahim
Asked:
bsarahim
  • 10
  • 7
1 Solution
 
dylanyeeCommented:
>> 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

0
 
bsarahimAuthor Commented:
let put this way ignore 28,
just take it as Feb (month Selected)
so
Feb
jan
Dec
only
0
 
dylanyeeCommented:
Ok.. but which table is containing the date field?

dylan
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
bsarahimAuthor Commented:
sorry to tell u inspection table contains insp_DateTime(Date/Time)
0
 
dylanyeeCommented:
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
0
 
dylanyeeCommented:
Sorry, create 2 parameter {?FromMonth} & {?ToMonth}.

dylan
0
 
dylanyeeCommented:
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
0
 
bsarahimAuthor Commented:
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
0
 
dylanyeeCommented:
How about... onchange inspection.yearmonth and subdivided by inspection.itp_id? will it give you the desired result?

dylan
0
 
bsarahimAuthor Commented:
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
0
 
dylanyeeCommented:
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
0
 
bsarahimAuthor Commented:

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

y-axis is ityp_id like x1, x2, x3, x4...
0
 
dylanyeeCommented:
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
0
 
bsarahimAuthor Commented:
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)
0
 
dylanyeeCommented:
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
0
 
bsarahimAuthor Commented:
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
http://www.experts-exchange.com/Databases/Crystal_Reports/Q_21785422.html
0
 
dylanyeeCommented:
sorry my mistake, if you are using CR that come together with VS.NET 2003, then you are not able to rotate the label for X-axis label, you need to install CR9 or later in order to alter the x-axis.


dylan
0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

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