bar char

[hatemgamil]

hi all
i have a question about bar chart
lets say i have a report like that

case_number Mild Serious Casualities
10/2010 3 3 5
11/2010 4 5 4
12/2010 5 4 4
total 12 12 13
i am showing the cases where year(date)=2010
and i am using suppress formula in details section in order to suppress all records where year(date)=2009 but i want to make a bar chart that shows the sum of mild,serious,casualities in 2010 and 2009 in the same chart
i have made a formula like that year(date) and in chart expert @data tab i have added this formula in the change of
and @show values i have added (sum Mild,Sum Serious,Sum Casualities),,the chart is working and it shows the value of change of year but not the way i want the chart makes them looks like group shows 3 bars for 2010 and shows 3 other bars for 2009 ,i want to make it shows 2 bars for mild (1 for 2010 and 1 for 2009) then 2 bars for serious (1 for 2010 and other for 2009) and so on

all suggestion are welcomed

thnx

 

[lbass]

Are Mild, Serious, and Casualties separate fields? Or are they instances of one field and you created the three summaries by using conditional formulas?

-LB

 

[hatemgamil]

hi lB,,sorry for my late to reply as it was the weekend
they are separated fields ,and i have created the summary using running total field

thnx

 

[lbass]

Why are you using running totals? How are they set up? You should only use running totals if inserted summaries don't return the correct result.

The problem is that your fields are separate, rather than instances of one field. If you have the option of using a command, you should create a command as the source for your report, where you use a union all to merge the fields into one, as in:

select 'Mild' as type, table.mild as injuryextent, table.date, table.caseno
from table
union all
select 'Serious' as type, table.serious, table.date, table.caseno
from table
union all
select 'Casualties' as type, table.casualties, table.date,table.caseno
from table

The punctuation/syntax depends upon your datasource, so you might have to adapt this. What type of database are you using?

Then set up the chart to use {command.type} as the first "on change of" field, and {command.date} (on change of year) as the second "on change of" field. Use sum of {command.injuryextent} as your summary field.

If you don't want to start from scratch, you could add the command in a subreport, instead of changing your entire report.

-LB

 

[hatemgamil]

i am working on sql server 2008 ,and i have created a stored proc to get what i want and in vs i have datatable which holds the values returned from the proc then i have created a schema from this data table,,then i have added a new report and i attached the schema to it,,can u please tell me where can i write the sql statemnet u have sent to me in .rpt file

thnx

 

[lbass]

If you are able to use a command, you would see "Add command" directly above the list of tables for your datasource in database->database expert.

But since you have created a stored procedure, couldn't you do the equivalent in the stored procedure? (I'm not really familiar with using stored procedures though.)

-LB