Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SSRS - Calculate $ cost on monthly basis


SSRS - Calculate $ cost on monthly basis

Author
Message
engg.pankaj
engg.pankaj
Forum Newbie
Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)

Group: General Forum Members
Points: 8 Visits: 20
I am new to SSRS and stuck at a point and can not move further and so need your help

I have a question regarding calculating sum of sales for different months.

I have a report which shows weekly sales $ for east and west cost office. I have a report which shows weekly sales $ for east and west cost office. The datasource is a table which contains weekly sales data for the offices like:

Week $ Sales_Item1 $ Sales_Item2 $ Sales_Item3
Aug 20 1000 xyz abc
Aug 27 500
Sept 03 2000
etc
etc
etc


Report needs to show info like:

Week $ Sales_Item1 $ Sales_Item2 $ Sales_Item3
Aug 20 1000 xyz abc
Aug 27 500
Sept 03 2000
etc
etc
etc

Sum $ for Aug X Y
Sum $ for Sept A B




How do i calculate the sales for these individual items monthwise and show it on the report. What needs to be written in the "Expression" so as to get the sum of all 4 weeks data of the month?

Please reply.

Thanks
robert.gerald.taylor
robert.gerald.taylor
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1326 Visits: 1399
Rather than trying to do this in an expression, change your datasource and do it in T-SQL. Much easier.

Rob
davoscollective
davoscollective
SSChasing Mays
SSChasing Mays (601 reputation)SSChasing Mays (601 reputation)SSChasing Mays (601 reputation)SSChasing Mays (601 reputation)SSChasing Mays (601 reputation)SSChasing Mays (601 reputation)SSChasing Mays (601 reputation)SSChasing Mays (601 reputation)

Group: General Forum Members
Points: 601 Visits: 1000
If you are able to edit the SQL then do as robert says. If you aren't good with SQL or are unable to edit the SQL for some reason then here's an idea:

If the week field is a text datatype (not a true date) then you could create a calculated column as
=Left(Fields!Week.Value,4)   



This assumes that your weeks are as you've shown in your example, as either 3 or 4 letters followed by the day of the month.

You will also want to include the year in the group, that's important if your report spans multiple years.

If the week field is a proper date datatype then you can use this formula:

=MonthName(Month(Fields!Week.Value),False)



If you add a parent group to the details row, use that calculated column as the group.
You can then add totals for the groups. If you right click on the $Sales_Item1 details cell it will give you the option to "add total".

The results will look somewhat like this

Month  Week   $Sales_Item1 
Aug Aug 20 1000
Aug 27 500
Total 1500
Sept Sept 03 2000
Sep 10 100
Sep 17 0
Sep 24 0
Total 2100




If you really want it as per your example with the totals per month separate, then you can add a second tablix below and just use the grouping in it, i.e. change the details group to be grouped by month (see formulas above) and use something like
=sum(Field!@Sales_item1.value) 

for each cell expression.
engg.pankaj
engg.pankaj
Forum Newbie
Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)

Group: General Forum Members
Points: 8 Visits: 20
Thanks for the reply Robert.

So, do I alter the source table, add new column(s) which can hold the values for the sum of the month(s)?
robert.gerald.taylor
robert.gerald.taylor
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1326 Visits: 1399
engg.pankaj (1/2/2013)
Thanks for the reply Robert.

So, do I alter the source table, add new column(s) which can hold the values for the sum of the month(s)?

No, you wouldn't need to alter the table, but rather just write a query. Just sum up the values grouping by the month. You would use the SUM() aggregate function and the GROUP BY clause. Take a look at: http://www.sql-tutorial.net/SQL-GROUP-BY.asp
for an intro on how group by works.

Rob
engg.pankaj
engg.pankaj
Forum Newbie
Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)

Group: General Forum Members
Points: 8 Visits: 20
Hi Robert,

Thanks for the reply again.

I know how to write SUM() and group by queries in T-SQL however, I want to know how to write this query and show the result in that particular column.

If i right click on a column on the report, where do I write the query in it so that we can display the result of the query in that column.

I am new to SSRS and need guidance.

Thanks
robert.gerald.taylor
robert.gerald.taylor
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1326 Visits: 1399
engg.pankaj (1/3/2013)
Thanks for the reply again.

I know how to write SUM() and group by queries in T-SQL however, I want to know how to write this query and show the result in that particular column.

If i right click on a column on the report, where do I write the query in it so that we can display the result of the query in that column.

I am new to SSRS and need guidance.

You put your query in the Dataset area of the report. First, make sure you have a data source that points to your database. Then create a new dataset (right click on Datasets and choose Add Dataset if you're creating your report in Report Builder -- it's similar in VS as well). From here you can either go to the query designer, or just past in your query into the text window.

Now in your tablix, make sure it's connected to that dataset (in the tablix properties, Dataset name). And then you can select the fields from your dataset for the columns of your report table.

HTH,
Rob
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search