July 28, 2011 at 4:57 am
1. If you re using SSRS to produce report, grouping can be done in SSRD itself
2. You was almost there 😀
select
sil.[Document No_] AS 'Invoice Number'
,sih.[Posting Date] AS 'Invoice Date'
,sih.[Sell-to Customer No_] AS 'Bill-To Customer'
,sih.[Ship-to Name] AS 'Name'
,sih.[Payment Terms Code] AS 'Payment Terms'
,sih.[Order No_] AS 'Order No.'
,sih.[Salesperson Code] AS 'Person'
,SUM((sil.[Quantity])*(sil.[Unit Price])) AS Amount
,SUM([Retail Price]-[Vendor Quoted Cost]) AS Profit
,(
(
(
SUM((sil.[Quantity] * sil.[Unit Price])-(sil.[Quantity] * sil.[Unit Cost (LCY)]))
)
/SUM((NULLIF(sil.[Quantity],0.0) * (NullIF(sil.[Unit Price],0.0))))* 100
)
) AS 'Profit%'
From [dbo].[GK Live$Sales Invoice Line] AS sil
Inner Join [dbo].[GK Live$Sales Invoice Header] AS sih ON sih.No_ = sil.[Document No_]
GROUP BY
sil.[Document No_]
,sih.[Posting Date]
,sih.[Sell-to Customer No_]
,sih.[Ship-to Name]
,sih.[Payment Terms Code]
,sih.[Order No_]
,sih.[Salesperson Code]
Having sih.[Posting Date] = '07/15/2011'
Order by sil.[Document No_]
You needed to "sum" your profit calculation.
Also, you are using different data to calculate Profit amount and Profir percentage. So, until your users know exactly what it's about, they will not sea corellation between two of them.
July 28, 2011 at 5:00 am
Eugene Elutin (7/28/2011)
1. If you re using SSRS to produce report, grouping can be done in SSRD itself
FYI: I agree with the above statement. In fact, in SSRS, you can do summary calcs without depending on the T-SQL source. You can create formulas to do everything you need, then just use the source query to draw out the details.
I don't know how well the new SSRS does drilldowns. I know in Crystal reports, you can hide the details and show summary lines which can be clicked on to get more drilldown details. See if SSRS can do that for you.
That being said, I'm still going to look at the code and see if I find anything different from Eugene.
July 28, 2011 at 5:13 am
I'm seeing two things. 1) In your result set, you refer to customer 9181, who doesn't exist in the test data. I'm assuming you meant 9151 and this is just a typo.
2) Eugene's solution left out a SUM in the Profit% column, but he's essentially correct. The reason you can't leave all those columns out of your GROUP BY is because this column doesn't use the SUM() function. If you SUM(), you don't have to group all those columns that are only used in the calculations.
So, use Eugene's query, but instead of:
,(
(
(
SUM((sil.[Quantity] * sil.[Unit Price])-(sil.[Quantity] * sil.[Unit Cost (LCY)]))
)
/SUM((NULLIF(sil.[Quantity],0.0) * (NullIF(sil.[Unit Price],0.0))))* 100
)
) AS 'Profit%'
You need:
,(
(
(
SUM((sil.[Quantity] * sil.[Unit Price])-SUM(sil.[Quantity] * sil.[Unit Cost (LCY)]))
)
/SUM((NULLIF(sil.[Quantity],0.0) * (NullIF(sil.[Unit Price],0.0))))* 100
)
) AS 'Profit%'
That should fix your problem.
July 28, 2011 at 5:13 am
Brandie Tarvin (7/28/2011)
...I don't know how well the new SSRS does drilldowns. I know in Crystal reports, you can hide the details and show summary lines which can be clicked on to get more drilldown details. See if SSRS can do that for you.
...
Yep, Crystal is quite good in it. But, SSRS can do it as just as well (almost :-)).
Personally, I would group data for report in query only if the report is basic and not requires drill-down and other formulas for something else.
Business users usually like drilldown functionality (even when they don't need it). But for managers... Than higher the manager is placed in company hierarchy, then less details he wants from report, they could be easely satisfied with a single line and column called Profit (if it's positive and high enough to qualify them for bonuses) 😀
Viewing 4 posts - 16 through 18 (of 18 total)
You must be logged in to reply to this topic. Login to reply