Help in creating charts. - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Development Tools

Reporting Services Development

Help in creating charts.

Author

Topic

collie
Constraint Violating Yak Guru

400 Posts

Posted - 2009-02-20 : 19:34:57

Hi,

Can someone please assist me with creating charts in reporting service 2005?

I have this sp:


ALTER  PROCEDURE[dbo].[IM_ChartReport]
	-- Add the parameters for the stored procedure here
@app_id int
, @subject_id int
,@issue_id int
,@date_from datetime,
@date_to datetime
 ,@agent int
AS
BEGIN
if @subject_id=0
set @subject_id=null
if @app_id=0
set @app_id=null
if @issue_id=0
set @issue_id=null

if @agent=0
set @agent=null
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

select  
 count(r.subject) as 'total'

,s.subject as 'Value'
 ,convert(nvarchar(10),[Date_Reported],103) as date_reported
,users.FirstName +' '+  LastName as DisplayName
from
dbo.IM_Msg_Issue_Reported r
left join dbo.IM_Bugs b on b.bug_id=r.issue
left join dbo.IM_Application a on a.App_id=r.application
left join dbo.IM_Subject s on s.Subject_id=r.subject
left outer join users on users.id=r.agent_id

where (r.date_reported BETWEEN  CONVERT(varchar(10), @date_from, 103) 
 AND  CONVERT(varchar(10), @date_to, 103)) 
 AND
 (R.SUBJECT=@subject_id or @subject_id is null)
and (r.application=@app_id or @app_id is null)
and (r.issue=@issue_id or @issue_id is null)
and (r.agent_id=@agent or @agent is null)

group by  
s.subject 
,r.date_reported
,users.FirstName
,LastName
 
union all
select  
 count (r.application) as   'total'
,a.application as 'Value'
 
,convert(nvarchar(10),[Date_Reported],103) as date_reported
,users.FirstName +' '+  LastName as DisplayName
from
dbo.IM_Msg_Issue_Reported r
left join dbo.IM_Bugs b on b.bug_id=r.issue
left join dbo.IM_Application a on a.App_id=r.application
left join dbo.IM_Subject s on s.Subject_id=r.subject
left outer join users on users.id=r.agent_id

where (r.date_reported BETWEEN  CONVERT(varchar(10), @date_from, 103) 
 AND  CONVERT(varchar(10), @date_to, 103)) 
 AND
 (R.SUBJECT=@subject_id or @subject_id is null)
and (r.application=@app_id or @app_id is null)
and (r.issue=@issue_id or @issue_id is null)
and (r.agent_id=@agent or @agent is null)
group by  
a.application
,r.date_reported
,users.FirstName
,LastName
 
union all
select  
 count(r.issue) as 'total'
,b.bug as 'Value'
,convert(nvarchar(10),r.[Date_Reported],103) as date_reported
,users.FirstName +' '+  LastName as DisplayName
from
dbo.IM_Msg_Issue_Reported r
left join dbo.IM_Bugs b on b.bug_id=r.issue
left join dbo.IM_Application a on a.App_id=r.application
left join dbo.IM_Subject s on s.Subject_id=r.subject
left outer join users on users.id=r.agent_id

where (r.date_reported BETWEEN  CONVERT(varchar(10), @date_from, 103) 
 AND  CONVERT(varchar(10), @date_to, 103)) 
 AND
 (R.SUBJECT=@subject_id or @subject_id is null)
and (r.application=@app_id or @app_id is null)
and (r.issue=@issue_id or @issue_id is null)
and (r.agent_id=@agent or @agent is null)

group by  
b.bug
,r.date_reported
,users.FirstName
,LastName
order by Date_Reported
end

The result of this if the values are null and date_from=’02/02/2009’ and date_to=’03/03/2009’
Total Value Date_reported Agent
1 subject1 02/02/2009 kb user
1 Subject5A 02/02/2009 Tom Agent

1 Subject5A 02/02/2009 kb user
1 product1 02/02/2009 kb user
1 Product3 02/02/2009 Tom Agent
1 Product5 02/02/2009 kb user
1 issue 5 02/02/2009 kb user
1 issue2 02/02/2009 kb user
1 issue3aaa 02/02/2009 Tom Agent
1 issue2 13/02/2009 Tom Agent
1 product2 13/02/2009 Tom Agent
1 subject2 13/02/2009 Tom Agent
1 subject1 18/02/2009 Support Agent
1 subject3 18/02/2009 Support Agent
1 product1 18/02/2009 Support Agent
1 Product3 18/02/2009 Support Agent
1 issue3aaa 18/02/2009 Support Agent
1 issue1 18/02/2009 Support Agent
1 issue1 19/02/2009 Sarah Black
1 issue1 19/02/2009 kb user
1 issue3aaa 19/02/2009 Sarah Black
1 ISSUE4 19/02/2009 kb user
1 Product4 19/02/2009 Sarah Black
1 Product4 19/02/2009 kb user
1 product2 19/02/2009 Tom Agent
1 product2 19/02/2009 Sarah Black
1 product2 19/02/2009 kb user
2 product2 19/02/2009 Support Agent
1 issue2 19/02/2009 Tom Agent
1 issue2 19/02/2009 Sarah Black
1 issue2 19/02/2009 kb user
2 issue2 19/02/2009 Support Agent
1 issue 5 19/02/2009 Sarah Black
1 Product5 19/02/2009 Sarah Black
1 product1 19/02/2009 Sarah Black
1 product1 19/02/2009 kb user
1 Subject5A 19/02/2009 Tom Agent
1 Subject5A 19/02/2009 Sarah Black
1 subject3 19/02/2009 Sarah Black
1 Subject4 19/02/2009 kb user
1 Subject4a 19/02/2009 Sarah Black
2 Subject4a 19/02/2009 kb user
1 subject2 19/02/2009 Sarah Black
2 subject2 19/02/2009 Support Agent

I need to display the results in a pie and line charts. I have looked at many examples and have wrecked my brains out trying to get a decent pie/line chart. I am doing something wrong. Maybe my sp is not written correctly or I am not dragging the correct values to the graph.
1. I have to show how many times the values were reported between dates selected by the user eg issue1was reported 2 times on the 19/02/2009 by Sarah Black and kb user and once on the 18/02/2009 by Support Agent.
2. In the line chart the user may want to compare how many times each DisplayName (i.e. agent) reported values between certain dates.
3. There are more options that the user might want to see such as how many times a certain value was reported between certain dates and the fluctuations.

Code for pie chart



<?xml version="1.0" encoding="utf-8"?>
<Report xmlns="http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition" xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
  <DataSources>
    <DataSource Name="dsReportIssue">
      <rd:DataSourceID>e19d53d5-6606-4244-982e-13ea0b0b7f1f</rd:DataSourceID>
      <DataSourceReference>dsReportIssue</DataSourceReference>
    </DataSource>
  </DataSources>
  <InteractiveHeight>11in</InteractiveHeight>
  <ReportParameters>
    <ReportParameter Name="date_from">
      <DataType>DateTime</DataType>
      <DefaultValue>
        <Values>
          <Value>02/02/2009</Value>
        </Values>
      </DefaultValue>
      <Prompt>date_from</Prompt>
      <Hidden>true</Hidden>
    </ReportParameter>
    <ReportParameter Name="date_to">
      <DataType>DateTime</DataType>
      <DefaultValue>
        <Values>
          <Value>03/03/2009</Value>
        </Values>
      </DefaultValue>
      <Prompt>date_to</Prompt>
      <Hidden>true</Hidden>
    </ReportParameter>
    <ReportParameter Name="app_id">
      <DataType>Integer</DataType>
      <DefaultValue>
        <Values>
          <Value>0</Value>
        </Values>
      </DefaultValue>
      <AllowBlank>true</AllowBlank>
      <Prompt>app_id</Prompt>
      <ValidValues>
        <DataSetReference>
          <DataSetName>dsApplication</DataSetName>
          <ValueField>App_id</ValueField>
          <LabelField>Application</LabelField>
        </DataSetReference>
      </ValidValues>
      <Hidden>true</Hidden>
    </ReportParameter>
    <ReportParameter Name="subject_id">
      <DataType>Integer</DataType>
      <DefaultValue>
        <Values>
          <Value>0</Value>
        </Values>
      </DefaultValue>
      <AllowBlank>true</AllowBlank>
      <Prompt>subject_id</Prompt>
      <ValidValues>
        <DataSetReference>
          <DataSetName>dsSubject</DataSetName>
          <ValueField>Subject_id</ValueField>
          <LabelField>Subject</LabelField>
        </DataSetReference>
      </ValidValues>
      <Hidden>true</Hidden>
    </ReportParameter>
    <ReportParameter Name="issue_id">
      <DataType>Integer</DataType>
      <DefaultValue>
        <Values>
          <Value>0</Value>
        </Values>
      </DefaultValue>
      <AllowBlank>true</AllowBlank>
      <Prompt>issue_id</Prompt>
      <ValidValues>
        <DataSetReference>
          <DataSetName>dsIssue</DataSetName>
          <ValueField>bug_id</ValueField>
          <LabelField>bug</LabelField>
        </DataSetReference>
      </ValidValues>
      <Hidden>true</Hidden>
    </ReportParameter>
    <ReportParameter Name="agent">
      <DataType>Integer</DataType>
      <DefaultValue>
        <Values>
          <Value>0</Value>
        </Values>
      </DefaultValue>
      <AllowBlank>true</AllowBlank>
      <Prompt>agent</Prompt>
      <ValidValues>
        <DataSetReference>
          <DataSetName>dsAgent</DataSetName>
          <ValueField>ID</ValueField>
          <LabelField>DisplayName</LabelField>
        </DataSetReference>
      </ValidValues>
      <Hidden>true</Hidden>
    </ReportParameter>
  </ReportParameters>
  <rd:DrawGrid>true</rd:DrawGrid>
  <InteractiveWidth>8.5in</InteractiveWidth>
  <rd:GridSpacing>0.25cm</rd:GridSpacing>
  <rd:SnapToGrid>true</rd:SnapToGrid>
  <RightMargin>2.5cm</RightMargin>
  <LeftMargin>2.5cm</LeftMargin>
  <BottomMargin>2.5cm</BottomMargin>
  <rd:ReportID>ad740dce-f4ab-4595-a0a0-d32cf3a3bcb0</rd:ReportID>
  <PageWidth>21cm</PageWidth>
  <DataSets>
    <DataSet Name="dsReportIssue">
      <Fields>
        <Field Name="total">
          <DataField>total</DataField>
          <rd:TypeName>System.Int32</rd:TypeName>
        </Field>
        <Field Name="Value">
          <DataField>Value</DataField>
          <rd:TypeName>System.String</rd:TypeName>
        </Field>
        <Field Name="date_reported">
          <DataField>date_reported</DataField>
          <rd:TypeName>System.String</rd:TypeName>
        </Field>
        <Field Name="DisplayName">
          <DataField>DisplayName</DataField>
          <rd:TypeName>System.String</rd:TypeName>
        </Field>
      </Fields>
      <Query>
        <DataSourceName>dsReportIssue</DataSourceName>
        <CommandType>StoredProcedure</CommandType>
        <CommandText>IM_ChartReport</CommandText>
        <QueryParameters>
          <QueryParameter Name="@date_from">
            <Value>=Parameters!date_from.Value</Value>
          </QueryParameter>
          <QueryParameter Name="@date_to">
            <Value>=Parameters!date_to.Value</Value>
          </QueryParameter>
          <QueryParameter Name="@subject_id">
            <Value>=Parameters!subject_id.Value</Value>
          </QueryParameter>
          <QueryParameter Name="@app_id">
            <Value>=Parameters!app_id.Value</Value>
          </QueryParameter>
          <QueryParameter Name="@issue_id">
            <Value>=Parameters!issue_id.Value</Value>
          </QueryParameter>
          <QueryParameter Name="@agent">
            <Value>=Parameters!agent.Value</Value>
          </QueryParameter>
        </QueryParameters>
        <rd:UseGenericDesigner>true</rd:UseGenericDesigner>
      </Query>
    </DataSet>
    <DataSet Name="dsApplication">
      <Fields>
        <Field Name="App_id">
          <DataField>App_id</DataField>
          <rd:TypeName>System.Int32</rd:TypeName>
        </Field>
        <Field Name="Application">
          <DataField>Application</DataField>
          <rd:TypeName>System.String</rd:TypeName>
        </Field>
      </Fields>
      <Query>
        <DataSourceName>dsReportIssue</DataSourceName>
        <CommandText>select App_id,Application from dbo.IM_Application where status=1 union select  0 as App_id,' All' as application order by application</CommandText>
        <rd:UseGenericDesigner>true</rd:UseGenericDesigner>
      </Query>
    </DataSet>
    <DataSet Name="dsSubject">
      <Fields>
        <Field Name="Subject_id">
          <DataField>Subject_id</DataField>
          <rd:TypeName>System.Int32</rd:TypeName>
        </Field>
        <Field Name="Subject">
          <DataField>Subject</DataField>
          <rd:TypeName>System.String</rd:TypeName>
        </Field>
      </Fields>
      <Query>
        <DataSourceName>dsReportIssue</DataSourceName>
        <CommandText>SELECT IM_Subject.[Subject_id], [Subject] FROM [IM_Subject] inner join  IM_Subj_App on  IM_Subj_App.Subject_id=IM_Subject.Subject_id
                where (App_id=@app_id or @app_id = 0)
                UNION
                SELECT 0 as subject_id, ' All ' AS Subject</CommandText>
        <QueryParameters>
          <QueryParameter Name="@app_id">
            <Value>=Parameters!app_id.Value</Value>
          </QueryParameter>
        </QueryParameters>
        <rd:UseGenericDesigner>true</rd:UseGenericDesigner>
      </Query>
    </DataSet>
    <DataSet Name="dsIssue">
      <Fields>
        <Field Name="bug_id">
          <DataField>bug_id</DataField>
          <rd:TypeName>System.Int32</rd:TypeName>
        </Field>
        <Field Name="bug">
          <DataField>bug</DataField>
          <rd:TypeName>System.String</rd:TypeName>
        </Field>
      </Fields>
      <Query>
        <DataSourceName>dsReportIssue</DataSourceName>
        <CommandText>SELECT im_bugs.bug_id,bug from 
dbo.IM_Bugs
inner join dbo.IM_Subj_App_Bug
on  IM_Subj_App_Bug.bug_id=im_bugs.bug_id
where (IM_Subj_App_Bug.app_id=@app_id or @app_id=0)
and (dbo.IM_Subj_App_Bug.subj_id=@subject_id or @subject_id=0)
                UNION
                SELECT 0 as bug_id, ' All ' AS bug</CommandText>
        <QueryParameters>
          <QueryParameter Name="@app_id">
            <Value>=Parameters!app_id.Value</Value>
          </QueryParameter>
          <QueryParameter Name="@subject_id">
            <Value>=Parameters!subject_id.Value</Value>
          </QueryParameter>
        </QueryParameters>
        <rd:UseGenericDesigner>true</rd:UseGenericDesigner>
      </Query>
    </DataSet>
    <DataSet Name="dsAgent">
      <Fields>
        <Field Name="ID">
          <DataField>ID</DataField>
          <rd:TypeName>System.Int32</rd:TypeName>
        </Field>
        <Field Name="DisplayName">
          <DataField>DisplayName</DataField>
          <rd:TypeName>System.String</rd:TypeName>
        </Field>
      </Fields>
      <Query>
        <DataSourceName>dsReportIssue</DataSourceName>
        <CommandType>StoredProcedure</CommandType>
        <CommandText>IM_Agents</CommandText>
      </Query>
    </DataSet>
  </DataSets>
  <Width>37.24868cm</Width>
  <Body>
    <ColumnSpacing>1cm</ColumnSpacing>
    <ReportItems>
      <Chart Name="chart3">
        <Legend>
          <Visible>true</Visible>
          <Style>
            <BorderStyle>
              <Default>Solid</Default>
            </BorderStyle>
          </Style>
          <Position>RightCenter</Position>
        </Legend>
        <CategoryAxis>
          <Axis>
            <Title />
            <MajorGridLines>
              <ShowGridLines>true</ShowGridLines>
              <Style>
                <BorderStyle>
                  <Default>Solid</Default>
                </BorderStyle>
              </Style>
            </MajorGridLines>
            <MinorGridLines>
              <Style>
                <BorderStyle>
                  <Default>Solid</Default>
                </BorderStyle>
              </Style>
            </MinorGridLines>
            <MajorTickMarks>Outside</MajorTickMarks>
            <Min>0</Min>
            <Visible>true</Visible>
          </Axis>
        </CategoryAxis>
        <ZIndex>1</ZIndex>
        <DataSetName>dsReportIssue</DataSetName>
        <PlotArea>
          <Style>
            <BackgroundColor>LightGrey</BackgroundColor>
            <BorderStyle>
              <Default>Solid</Default>
            </BorderStyle>
          </Style>
        </PlotArea>
        <ThreeDProperties>
          <Enabled>true</Enabled>
          <Rotation>30</Rotation>
          <Inclination>30</Inclination>
          <Shading>Simple</Shading>
          <WallThickness>50</WallThickness>
        </ThreeDProperties>
        <PointWidth>0</PointWidth>
        <Top>0.39375in</Top>
        <Subtype>Exploded</Subtype>
        <ValueAxis>
          <Axis>
            <Title />
            <MajorGridLines>
              <ShowGridLines>true</ShowGridLines>
              <Style>
                <BorderStyle>
                  <Default>Solid</Default>
                </BorderStyle>
              </Style>
            </MajorGridLines>
            <MinorGridLines>
              <Style>
                <BorderStyle>
                  <Default>Solid</Default>
                </BorderStyle>
              </Style>
            </MinorGridLines>
            <MajorTickMarks>Outside</MajorTickMarks>
            <Min>0</Min>
            <Margin>true</Margin>
            <Visible>true</Visible>
            <Scalar>true</Scalar>
          </Axis>
        </ValueAxis>
        <Type>Pie</Type>
        <Width>6.98906in</Width>
        <CategoryGroupings>
          <CategoryGrouping>
            <DynamicCategories>
              <Grouping Name="chart3_CategoryGroup2">
                <GroupExpressions>
                  <GroupExpression>=Fields!Value.Value</GroupExpression>
                </GroupExpressions>
              </Grouping>
              <Label>=Fields!Value.Value</Label>
            </DynamicCategories>
          </CategoryGrouping>
          <CategoryGrouping>
            <DynamicCategories>
              <Grouping Name="chart3_CategoryGroup1">
                <GroupExpressions>
                  <GroupExpression>=Fields!DisplayName.Value</GroupExpression>
                </GroupExpressions>
              </Grouping>
              <Label>=Fields!DisplayName.Value</Label>
            </DynamicCategories>
          </CategoryGrouping>
        </CategoryGroupings>
        <Palette>SemiTransparent</Palette>
        <ChartData>
          <ChartSeries>
            <DataPoints>
              <DataPoint>
                <DataValues>
                  <DataValue>
                    <Value>=Sum(Fields!total.Value)</Value>
                  </DataValue>
                </DataValues>
                <DataLabel>
                  <Value>=Fields!total.Value & "times " & vbcrlf & Fields!date_reported.Value</Value>
                  <Position>Top</Position>
                  <Visible>true</Visible>
                </DataLabel>
                <Marker>
                  <Size>6pt</Size>
                </Marker>
              </DataPoint>
            </DataPoints>
          </ChartSeries>
        </ChartData>
        <Style>
          <BackgroundColor>White</BackgroundColor>
        </Style>
        <Title />
        <Height>3in</Height>
      </Chart>
      <Textbox Name="textbox1">
        <rd:DefaultName>textbox1</rd:DefaultName>
        <Width>11in</Width>
        <Style>
          <Color>Navy</Color>
          <FontFamily>Tahoma</FontFamily>
          <FontSize>18pt</FontSize>
          <FontWeight>700</FontWeight>
          <PaddingLeft>2pt</PaddingLeft>
          <PaddingRight>2pt</PaddingRight>
          <PaddingTop>2pt</PaddingTop>
          <PaddingBottom>2pt</PaddingBottom>
        </Style>
        <CanGrow>true</CanGrow>
        <Height>0.8381cm</Height>
        <Value>Total times issues reported</Value>
      </Textbox>
    </ReportItems>
    <Height>34.11905cm</Height>
  </Body>
  <Language>en-GB</Language>
  <TopMargin>2.5cm</TopMargin>
  <PageHeight>29.7cm</PageHeight>
</Report>

Can someone please guide me on how to create a pie/line chart?

I am at a complete loss.

Thanks

Whisky-my beloved dog who died suddenly on the 29/06/06-I miss u so much.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-21 : 02:43:17

you need to group on Value field while ploting for number of time article was viewed b/w dates. then the count(*) value should be used as plot value.
and group on Agent field and take count(value) for how many times each DisplayName (i.e. agent) reported values between certain dates

collie
Constraint Violating Yak Guru

400 Posts

Posted - 2009-02-23 : 05:14:58

Thanks i will try it.

Whisky-my beloved dog who died suddenly on the 29/06/06-I miss u so much.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-24 : 12:49:14

welcome

collie
Constraint Violating Yak Guru

400 Posts

Posted - 2009-02-25 : 14:15:52

Hi,
There is a change in scenario now.
Agents have to report bugs. Before submitting the bug to the database they have to select an application. They then have to select a subject according to the application selected. Then they have to select the bug from the list based on the application and subject selected.
Bug is related on application and subject.
Subject on application.
Application on nothing.

In addition to the above 3 tables I have a table that stores the values once the agent clicked the submit button.

tbl_bugs_reported
id int identity
bug _id int
application_id int
subject_id int
agent_id int
date_reported datetime

_______________________
I now need to create pie, graph and line charts.
I have 4 dropdowns (application,subject,bug,agent)
2 date textboxes (date_reported_from, date_reported_to)

The 3 dropdowns (besides the agent) are dependent on each other (application->subject->bug)

The client wants on the charts to be able to see for example
1. how many times application x was reported between date_from and date_to
2. how many times issue x was reported for subject x and application x between date_from and date_to
3. how many times agent x reported issue x between date_from and date_to
and so forth

My problem is that I am not sure how I am able to know if the user wants me to show him how many times a product was reported, of if he wants to see on the chart how many times issue was reported etc.

If he selects product x->subject x=>issue x does he want to know how many times the issue x was reported or does he want to know how many times product x was reported or how many times subject x was reported.

I hope that i made myself understood :-)

Thanks for the help.

Whisky-my beloved dog who died suddenly on the 29/06/06-I miss u so much.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources