November 14, 2017 at 8:47 am
Hi
I have a report that I am trying to do that is filtered by what period the user enters. What I would like to show on the tablix is the total for the year to date even though it has been filtered by period to show current billing, is there a way I can do this by a sum command or something similar
Thanks
Chris
November 14, 2017 at 9:02 am
Rather than filtering the data at SP level, or Tablix, filter the Row Group. Add the Totals row to your tablix first, then in your Row Groups Pane put the filter on the Details_Group. The Total will still have the full total for the data that was returned to the report, where as the tablix will only display those you filtered to.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
November 14, 2017 at 9:08 am
Hi Thom
Thanks for the reply, I would have done that but one of the items in the data is a total pulled from the server and it multiples this total when filtered like this so I thought the only way around it was to do this calculation on the tablix itself. Sorry im still new to this and self trained so I dont always know the best answer to it.
Is there a way I can do the above without totalling a vital piece of information that must show as per how it should be pulled in?
Thanks
Chris
November 14, 2017 at 9:14 am
It sounds like your describing that you have totals already in your data as well as the non-aggragated data itself. Are you having to display those sub-totals as well? I'd suggest that, if so, you do your grouping and sub-totaling in SSRS, not in your dataset. Then you would have to issue of double counted data.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
November 14, 2017 at 10:04 am
The total for the year to date is data. When I do grouping to show totals the grouping doesnt show the year target total which is why i thought it needed to be done at tablix level
November 14, 2017 at 10:12 am
cmw 66135 - Tuesday, November 14, 2017 10:04 AMThe total for the year to date is data. When I do grouping to show totals the grouping doesnt show the year target total which is why i thought it needed to be done at tablix level
I don't really understand what you're saying here.
Perhaps you could provide some sample data (including data you don't want to include) and your expected results in SSRS.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
November 15, 2017 at 2:48 am
Hi Thom
This is below what I am trying to achieve with my report. This is a spreadsheet that we currently update manually but I am trying to automate a report and make it, idiot proof for the people who will us it.
This is what I have done so far, I feel im on the right track but as I say I am still very new to this and self taught so I dont always know if I have explained things correctly
November 15, 2017 at 3:06 am
That dataset doesn't look anything like what you originally posted, hw does it relate? Also, those screenshots are tiny (I very much doubt you have a screen that is only 271 pixels tall!).
Have a look at the link in my signature on how to post sample data. If you can provide some consumable sample data, along with what you want your SSRS report to look like, I'll be more than happy to try to guide you through.
Cheers.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
November 15, 2017 at 3:21 am
Looking at the detail you have posted it is clear that this is a law firm, and I should think that the data you are looking for is already calculated within the PMS system as this would be a normal reporting requirement, so not entirely sure why you need a manual form of entry onto a spreadsheet; also the data you have posted above is not obfuscated and can lead to the easy identification of the individuals and the law firm you represent, please take this as a piece of advice on being careful of what you post in future.
You may wish to edit your post!!
...
November 15, 2017 at 3:25 am
Point taken Happygeek. Thanks for the advice.
November 15, 2017 at 10:16 am
You have removed all reference to data and that seems to have stopped any advice. Depending on your PMS system you should find that there are objects; maybe a separate database, maybe a table, or even columns that have a suffix something like '_YTD' and '_PTD' these are where you will find the data you are seeking without having to do manual or otherwise calculations.
Alternatively in the FE table you may find that the data already exists - I don't know what PMS you have - as this would normally be available through the Time Recording application. If it is not then you should find it in an archive table associated with time recording.
I Hope this helps, the report you are seeking is pretty standard and achievable.
...
November 16, 2017 at 10:32 pm
In your data set definition >
Something like this will help get you going : This just to illustrate the concept. I am using an older version of the adventure works database here .
/* assume that prior month is selected as the period */
SELECT s.customerID, sum(s.totalDue) as totalSalesAmount,
ca.totalSalesAmount as totalSalesAmountYTD
FROM Sales.SalesOrderHeader as s
CROSS APPLY
(
SELECT sum(totalDue) as totalSalesAmount
FROM Sales.SalesOrderHeader
WHERE
customerID = @customer and
year(orderDate) = 2007 /* can do like year(getdate()) */
) as ca
WHERE
customerID = @customer and
month(orderDate) = 10 and
year(orderDate) = 2007
GROUP BY s.customerID, ca.totalSalesAmount
;
----------------------------------------------------
November 17, 2017 at 8:25 am
Thanks MMartin. Sadly that went right over my head. Sorry im very green at SQL reporting 🙁
November 17, 2017 at 8:30 am
I have had a thought though is there a way i can create a dynamic calculated field that in my head would look something like =sumif(and("for each fee earner change",Where year id = perameter,Where financial period doesn't equal perameter) as an expression? also for the financial period not to include numbers greater than if someone selects and earlier period number?
Thanks
Chris
November 18, 2017 at 12:56 am
cmw 66135 - Friday, November 17, 2017 8:25 AMThanks MMartin. Sadly that went right over my head. Sorry im very green at SQL reporting 🙁
No problem. I created some test data to better illustrate my point
create table #table ( customerID int,
salesAmt decimal(10,2),
orderDate date
)
insert into #table(customerID, salesAmt, orderDate)
values
(15652,20,'2017-01-01'),
(15652,20,'2017-02-01'),
(15652,20,'2017-03-01'),
(15652,20,'2017-04-01'),
(15652,20,'2017-05-01'),
(15652,20,'2017-06-01'),
(15652,20,'2017-07-01'),
(15652,20,'2017-08-01'),
(15652,20,'2017-09-01'),
(15652,50,'2017-10-01'),
(15675,20,'2017-01-01'),
(15675,20,'2017-02-01'),
(15675,20,'2017-03-01'),
(15675,20,'2017-04-01'),
(15675,20,'2017-05-01'),
(15675,20,'2017-06-01'),
(15675,20,'2017-07-01'),
(15675,20,'2017-08-01'),
(15675,20,'2017-09-01'),
(15675,70,'2017-10-01'),
(27059,20,'2017-01-01'),
(27059,20,'2017-02-01'),
(27059,20,'2017-03-01'),
(27059,20,'2017-04-01'),
(27059,20,'2017-05-01'),
(27059,20,'2017-06-01'),
(27059,20,'2017-07-01'),
(27059,20,'2017-08-01'),
(27059,20,'2017-09-01'),
(27059,80,'2017-10-01')
SELECT t.customerID, sum(t.salesAmt) as totalSalesAmountLastMonth,
ca.totalSalesAmount as totalSalesAmountYTD
FROM #table as t
CROSS APPLY
(
SELECT customerID,
sum(salesAmt) as totalSalesAmount
FROM #table
WHERE
year(orderDate) = year(getdate())
GROUP BY customerID
) as ca
WHERE
year(orderDate) = year(getdate()) and
month(orderDate) = month(getdate())-1 and
ca.customerID = t.customerID
GROUP
BY t.customerID, ca.totalSalesAmount
There is no need here to create anything within SSRS as the YTD field is here and ready to be pulled.
----------------------------------------------------
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply