November 4, 2008 at 3:59 am
Hello,
I'm searching for a solution to make this:
Public Function dothis(datasetname as string) As Long
' now I wan't to access to the dataset with the name in "datasetname"
' how can I do that?
' I need this for a special/custom aggregation function, I can't use it in SQL because
' the dataset must be cachable ...
End Function
In a Textbox:
=Code.dothis("dsProducts")
November 5, 2008 at 9:13 am
Custom aggregates can be done with CLR in SQL Server 2005 or later, but it's not usually necessary. What kind of custom aggregate are you looking to create? In many cases, the query sourcing your dataset can be modified to include additional columns, such that a combination of standard aggregates on those columns might be sufficient to achieve the desired result. Details of the query for your dataset as well as DDL for the underlying tables would be most useful.
Steve
(aka smunson)
:):):)
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
November 5, 2008 at 1:37 pm
Hi Steve,
I must generate a median over the values in the a chart.
I use a very simple function in custom code für do this in a table:
Like this:
dim values As New System.Collections.ArrayList
Function AddValue(newValue as decimal) As decimal
values.Add(newValue)
AddValue = newValue
End Function
Function GetMedian() as decimal
Dim count As Integer = values.Count
If (count > 0)
values.Sort()
GetMedian = values(count/2)
End If
End Function
It works very fine in a table, but not in a chart.
November 5, 2008 at 1:45 pm
So where are the chart values coming from? Couldn't they be derived from a dataset that has the values directly in it? I see no reason why that couldn't have the median value generated in the T-SQL - just not as your typical aggregate might be generated.
Steve
(aka smunson)
:):):)
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
November 6, 2008 at 6:13 am
The Chart Values comes from a big SQL because the Filter comes from Parameters an must be filtert in the report controls to use schnapshots and other features from Report Manager.
So I can't get the Median with TSQL, I don't know the WHERE Condition in this moment.
Greetings
Frank
November 6, 2008 at 6:40 am
Umm... why are you using Reporting Services to do the filtering? Why not pass the parameters to a stored procedure instead? That way, you'll have a complete dataset at some point within the sp, and then can further process it to determine the median.
I've never heard of the RS "snapshot" concept, so please elaborate... (I'll look it up in BOL in the meantime) and what other features of RS depend on RS having filtering? That seems counterintuitive to me...
Steve
(aka smunson)
:):):)
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
November 6, 2008 at 6:55 am
this is very complicatet, there are SOX and other rules why I must store the "values" as is.
If I use a SP or any other SQL feature, the dataset includes only the filtered values, not all at this time.
November 6, 2008 at 9:24 am
Now that I know what a report snapshot is, there's no way to produce it without preconfigured parameters or at least default values for those parameters, and no way to change the parameters once the user is viewing that snapshot. Here's the relevant paragraph from Books Online:
Not all reports can be configured to run as a snapshot. You cannot create a snapshot for a report that prompts users for credentials or uses Windows integrated security to get data for the report. If you want to run a parameterized report as a snapshot, you must specify a default parameter to use when creating the snapshot. In contrast with reports that run on demand, it is not possible to specify a different parameter value for a report snapshot once the report is open. Choosing a different parameter value would result in a new report processing request, which is not allowed.
Thus I don't see the problem with calculating the median ahead of time. I know that SOX is Sarbanes-Oxley, and that has it's own set of headaches, but that has more to do with the confidentiality of certain information rather than the method of generating a numeric value, does it not?
Steve
(aka smunson)
:):):)
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
November 6, 2008 at 1:38 pm
Sorry, but I think I can't do this. Here is a sample:
1 A
2 A
3 A
4 B
5 B
6 B
7 C
8 C
9 C
The Median of all is: 5. If you filter the second column to c it will be 8. So I don't see any chance to do this in a tsql or any other place on the server side.
Any Idea to do this with tsql ?
Thanx
Frank
November 6, 2008 at 3:17 pm
From what you've previously posted, you indicated you were going to need to use the snapshot feature. As that will require any filtering already be done, what's the difficulty?
Even from the example, it would be easy enough to create a table variable to hold the median value for each possible value of the field to be filtered, using a group by methodology. Said table variable could then be the right side of a left outer join, and thus the median value for all as well as the median value for each value of the filter field could be available to all rows in the dataset.
If that's what will work for you, I'll have to work on some pseudo-code tomorrow, as it's too late for today.
Steve
(aka smunson)
:):):)
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
November 10, 2008 at 1:22 pm
Here's some code that you could adapt to your circumstances, although without more details, I can't determine the performance in your situation.
DECLARE @data TABLE(
RN int,
ALLRN int,
DATAVALUE int,
DATAGROUP char(1),
MINROW int,
MAXROW int,
HALFDIFFLOW int,
HALFDIFFHIGH int,
GROUPMEDIAN decimal(10,4),
MINROWALL int,
MAXROWALL int,
HDIFFLOW int,
HDIFFHIGH int,
OVERALLMEDIAN decimal(12,4)
)
INSERT INTO @data (DATAVALUE, DATAGROUP)
SELECT 1, 'A' UNION ALL
SELECT 2, 'A' UNION ALL
SELECT 3, 'A' UNION ALL
SELECT 4, 'B' UNION ALL
SELECT 5, 'B' UNION ALL
SELECT 6, 'B' UNION ALL
SELECT 7, 'C' UNION ALL
SELECT 8, 'C' UNION ALL
SELECT 9, 'C' UNION ALL
SELECT 10, 'D' UNION ALL
SELECT 11, 'D' UNION ALL
SELECT 12, 'D' UNION ALL
SELECT 13, 'D' UNION ALL
SELECT 14, 'E'
;WITH GROUP_MEDIAN_RAW AS (
SELECT ROW_NUMBER() OVER(PARTITION BY DATAGROUP ORDER BY DATAVALUE) AS RN,
ROW_NUMBER() OVER(ORDER BY DATAGROUP, DATAVALUE) AS ALLRN,
DATAVALUE, DATAGROUP
FROM @data
)
UPDATE D
SET RN = G.RN, ALLRN = G.ALLRN
FROM @data AS D INNER JOIN GROUP_MEDIAN_RAW AS G
ON D.DATAGROUP = G.DATAGROUP AND
D.DATAVALUE = G.DATAVALUE
;WITH GROUP_MEDIAN_SELECTORS AS (
SELECT DATAGROUP, MIN(RN) AS MINROW, MAX(RN) AS MAXROW, CAST(MAX(RN) - MIN(RN) AS decimal(10,4))/2 AS HALFDIFF
FROM @data AS D
GROUP BY DATAGROUP
)
UPDATE D
SET MINROW = G.MINROW,
MAXROW = G.MAXROW,
HALFDIFFLOW = CAST(G.HALFDIFF AS int),
HALFDIFFHIGH = CAST(G.HALFDIFF + 0.5 AS int)
FROM @data AS D INNER JOIN GROUP_MEDIAN_SELECTORS AS G
ON D.DATAGROUP = G.DATAGROUP
UPDATE D
SET GROUPMEDIAN = (
SELECT AVG(CAST(DATAVALUE AS decimal(10,4)))
FROM @data AS D2
WHERE D2.DATAGROUP = D.DATAGROUP AND
D2.RN IN (D.MINROW + D.HALFDIFFLOW, D.MINROW + D.HALFDIFFHIGH)
)
FROM @data AS D
DECLARE @MINA AS int, @MAXA AS int
SELECT @MINA = MIN(ALLRN)
FROM @data
SELECT @MAXA = MAX(ALLRN)
FROM @data
;WITH OVERALL_MEDIAN_SELECTORS AS (
SELECT DATAGROUP, DATAVALUE, @MINA AS MINROW, @MAXA AS MAXROW,
CAST(@MAXA - @MINA AS decimal(12,4))/2 AS HDIFF
FROM @data
)
UPDATE D
SET MINROWALL = G.MINROW,
MAXROWALL = G.MAXROW,
HDIFFLOW = CAST(G.HDIFF AS int),
HDIFFHIGH = CAST(G.HDIFF + 0.5 AS int)
FROM @data AS D INNER JOIN OVERALL_MEDIAN_SELECTORS AS G
ON D.DATAGROUP = G.DATAGROUP AND
D.DATAVALUE = G.DATAVALUE
UPDATE D
SET OVERALLMEDIAN = (
SELECT AVG(CAST(DATAVALUE AS decimal(12,4)))
FROM @data AS D2
WHERE D2.ALLRN IN (D.MINROWALL + D.HDIFFLOW, D.MINROWALL + D.HDIFFHIGH)
)
FROM @data AS D
SELECT *
FROM @data
This code has been tested on the sample data included, which expands somewhat on your sample data to ensure other data scenarios can be accommodated accurately. The median is computed for each group as well as overall, and included in every output record as appropriate. You'll have to adapt this to what you're doing as well as test for performance.
Steve
(aka smunson)
:):):)
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
November 14, 2008 at 5:35 am
I need to do the same and struggeling to find a way to get access to the DataSources collection
something like LocalReport.DataSources["myDataSource"].Value.
any idea? Thanks in advance.
November 14, 2008 at 6:17 am
bendaia,
You have to first tie a control on your report to a specific dataset, as otherwise, only aggregate functions for a given dataset are available in Reporting Services. Individual textboxes and similar controls cannot be tied to a dataset, and are thus limited. Table and matrix controls tie to a specific dataset, and then you access the Fields in that dataset as something like the following:
=Left(Fields!FirstName.Value,1)
Does that help?
Steve
(aka smunson)
:):):)
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
March 23, 2010 at 5:05 am
Has any one tried calculating median in SSRS 2005 where group by is used? The requirement is to calculate median for individual groups in a dataset?
- Regards
Dinesh
March 24, 2010 at 8:23 pm
Has any one tried calculating median in SSRS 2005 where group by is used? The requirement is to calculate median for individual groups in a dataset?
Here's a method using T-SQL:
-- setup
IF OBJECT_ID('tempdb..#test_table','U') IS NOT NULL
DROP TABLE #test_table;
--
CREATE TABLE #test_table(
[colA] [tinyint] NULL,
[colB] [char](1) NULL
) ON [PRIMARY];
-- test data
INSERT INTO #test_table(colA,colB)
SELECT 1,'A' UNION ALL
SELECT 2,'A' UNION ALL
SELECT 3,'A' UNION ALL
SELECT 4,'B' UNION ALL
SELECT 5,'B' UNION ALL
SELECT 6,'B' UNION ALL
SELECT 7,'C' UNION ALL
SELECT 8,'C' UNION ALL
SELECT 9,'C' UNION ALL
SELECT 10,'C'
-- return median values (grouped by colB)
SELECT X.colB, AVG(1.0*X.colA) AS median
--INTO #temp2
FROM
( SELECT colB, colA, ROW_NUMBER() OVER(PARTITION BY colB ORDER BY colA) AS rowno
FROM #test_table
) X
INNER JOIN
( SELECT colB, 1+count(*) AS N
FROM #test_table
GROUP BY colB
) Y
ON X.colB = Y.colB AND X.rowNo BETWEEN N/2 AND N/2+N%2
GROUP BY X.colB
Note that the number of rows where colB = 'C' is even so there is no true 'median', hence the average value is returned
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply