customized grouping

I have a tabular report with several groupings.
I want to customize one of the groups.
For example I have a field called country. I want to group it by
region, western and eastern. How would I do this?where does the regional information come from? you need to provide us with
more info about your fields and their data.
"OogleGoogle" <Yvonhong@.gmail.com> wrote in message
news:1183576344.729726.5950@.i38g2000prf.googlegroups.com...
>I have a tabular report with several groupings.
> I want to customize one of the groups.
> For example I have a field called country. I want to group it by
> region, western and eastern. How would I do this?
>

Custom time period - 26th of month to 25 of next month

How do I create a server time dimension which has custom defined month periods.

For example we use month that starts on 26 and ends on 25 next month.

So our January is actualy starting from 26.Dec.2006 until 25.Jan.2007,

and so on for Feb, Mar...

Any idea how I can do it?

Thank you,

Mitja

Hello! Create a new column in your time dimension called CustomMonth.

Update TimeDim

Set CustomMonth = 'Jan'

Where CalendarDate between '2006-12-26' and '2007-12-25'

--

Continue with the next month.

This is a very simple solution. It possible to make it generic but that will require more thinking.

HTH

Thomas Ivarsson

|||

Thank you Thomas!

I have tried the proposed solution,

but I get syntax error at "where" critieria.

I was looking for some pre-made solution in business intelligence wizards,

strangely enough to find there isn't one.

Any ideas?

|||

This is an example that works with the AdventureWorksDW sample database:

Alter Table DimTime

Add SpecialMonth Char(3)

Select * from DimTime

where FulldateAlternateKey Between '2002-12-26' and '2003-12-25' --365 Check the no of records that will be updated

-

Update DimTime

Set SpecialMonth = 'Jan'

where FulldateAlternateKey Between '2002-12-26' and '2003-12-25'

-

It is possible to build a full generic solution but it will take some time. You can use TSQL CASE for that.

This will help you to get started.

Regards

Thomas Ivarsson

custom subscription schedule

Is it possible to modify the subscription schedules such that you can avoid
sending reports on non-production days? Holidays, for example? I've got a
few simple subscriptions, each configured to run the reports on 'Every
Weekday'. But, what of the holidays? Is there anyway to account for
holidays, and avoid sending out empty reports?
-- LynnAny thoughts at all? I have a calendar table, I use it like this:
SET DATEFIRST 7
IF DATEPART(WEEKDAY, GETDATE()) BETWEEN 2 AND 6
AND NOT EXISTS (SELECT 1 FROM db.dbo.calendar WHERE dt = CONVERT(CHAR(8),
GETDATE(), 112) AND isholiday = 1)
BEGIN
EXEC ......
END
Thus I am able to avoid running certain items on non-production days. As
you know, Reporting Services subscriptions create corresponding SQL Agent
jobs. Can I not preface the tSQL command w/the same thing, like this? :
SET DATEFIRST 7
IF DATEPART(WEEKDAY, GETDATE()) BETWEEN 2 AND 6
AND NOT EXISTS (SELECT 1 FROM db.dbo.calendar WHERE dt = CONVERT(CHAR(8),
GETDATE(), 112) AND isholiday = 1)
BEGIN
exec ReportServer.dbo.AddEvent @.EventType='TimedSubscription',
@.EventData='ace5773f-2dec-4d0e-9d89-b73b42f1db00'
END
My only concern here is that I'm somewhat new to RS, I'm just worried that
modifying the scheduling via SQL Agent may conflict with the RS subscription.
Any ideas, please?
-- Lynn
"Lynn" wrote:
> Is it possible to modify the subscription schedules such that you can avoid
> sending reports on non-production days? Holidays, for example? I've got a
> few simple subscriptions, each configured to run the reports on 'Every
> Weekday'. But, what of the holidays? Is there anyway to account for
> holidays, and avoid sending out empty reports?
> -- Lynn

Custom Security Extensions for SQL RS 2005

Hi,

I'm having a lot of trouble trying to set up a custom security extension with Reporting Services 2005. Following the VB example from McGraw Hill Osborne (http://www.mhprofessional.com/product.php?cat=112&isbn=0072262397&cat=112), I've compiled the .dll for the extension and made the changes to the ReportManager and ReportServer .config files. After I reset IIS and return to the RS website, it displays "The report server has encountered a configuration error..." and I get a message in my System Event log:

The application-specific permission settings do not grant Local Activation permission for the COM Server application with CLSID
{BA126AD1-2166-11D1-B1D0-00805FC1270E}
to the user MYMACHINE\ASPNET SID (S-1-5-21-1708537768-839522115-1343024091-1005). This security permission can be modified using the Component Services administrative tool.

Wondering if anyone else has had similar trouble and how did they get around it?

Have you modified the RSSrvPolicy.config ?

Here is a guide I followed to successfully implement custom authentication in RS.

http://msdn2.microsoft.com/en-us/library/ms160724.aspx

Look at the section "To modify the RSSrvPolicy.config file"

Regards Andreas|||

Here are the changes I made to .config files before running Reporting Services and getting these errors:

Under the ReportManager directory:

rsmgrpolicy.config - MyComputer code group permission from Execution to FullTrust

rsWebApplication.config - modified <UI> entry, added redirection to login page + URL

Web.config - identify impersonate -> "false"

Under the ReportServer dierctory:

RSReportServer.config - Added <Extension> elements with Name ="Forms" inside both <Security> and <Authentication>

rssvrpolicy.config - added new <CodeGroup> block just under CodeGroup with "$CodeGen$" string

web.config - identify impersonate -> "false"

sql

Custom Resolver for merge replication

Hi there!
I'm trying to create a custom resolver for merge replication exactly like in the MS example.
It seems to work, but only ONE time. If I change, insert or delete a record in a table the second time, the subscriber monitor comes with the following errors:

Error messages:
Attempted to read or write protected memory. This is often an indication that other memory is corrupt. (Source: MSSQL_REPL, Error number: MSSQL_REPL-2147199411)
The Merge Agent encountered an error when executing code in the 'UpdateHandler' method implemented in the business logic handler 'D:\Program Files\Microsoft SQL Server\90\COM\MyResolver.dll'. Ensure that the overridden 'UpdateHandler' method has been properly implemented in the business logic handler. (Source: MSSQL_REPL, Error number: MSSQL_REPL-2147199411)
This last error is of course dependant on my action (update, delete, insert).
My code is -exactly- like the example (I just stripped out the log message).
Does anyone know why I am "trying to read or write protected memory" ?
The thing is that I'm trying to create an application that detects if a table changes. Is this the right way to do this anyway or are there better solutions?
Any help is appreciated! Thanks!

Hi there!

Any resolution to the problem described in the post? I'm getting the same problem with custom resolver.

Custom Resolver for merge replication

Hi there!

I'm trying to create a custom resolver for merge replication exactly like in the MS example.

It seems to work, but only ONE time. If I change, insert or delete a

record in a table the second time, the subscriber monitor comes with

the following errors:

Error messages:

Attempted to read or write protected memory. This is often an

indication that other memory is corrupt. (Source: MSSQL_REPL, Error

number: MSSQL_REPL-2147199411)

The Merge Agent encountered an error when executing code in the

'UpdateHandler' method implemented in the business logic handler

'D:\Program Files\Microsoft SQL Server\90\COM\MyResolver.dll'. Ensure

that the overridden 'UpdateHandler' method has been properly

implemented in the business logic handler. (Source: MSSQL_REPL,

Error number: MSSQL_REPL-2147199411)

This last error is of course dependant on my action (update, delete, insert).

My code is -exactly- like the example (I just stripped out the log message).

Does anyone know why I am "trying to read or write protected memory" ?

The thing is that I'm trying to create an application that detects if a

table changes. Is this the right way to do this anyway or are there

better solutions?

Any help is appreciated! Thanks!

Hi there!

Any resolution to the problem described in the post? I'm getting the same problem with custom resolver.

Custom Report Item example not rendering right?

I'm trying to work with the custom report item example (PolygonsCRI.dll) from the latest SQL Server 2005 downloads. I've followed all of the instructions on how to deploy the report. The report is now available on my Report Server, and I can load the Polygons.sln report and view it in the designer. However, whenever I preview the report in the designer or view the report on the server, the polygon report items just show up as big blank spots - there's no image there and no indication of an error. The chart on the report renders just fine, though.

Does anybody have any ideas on what could be causing this?

Thanks,

Glenn Burnsider

There is a typo in the PolygonsCRI.vb and .cs file. The class name should be "PolygonsCRI", not "PolygonsCCustomReportItem". Change the class name to "PolygonsCRI", redeploy, and the chart should be visible now.

Custom Report Item example not rendering right?

I'm trying to work with the custom report item example (PolygonsCRI.dll) from the latest SQL Server 2005 downloads. I've followed all of the instructions on how to deploy the report. The report is now available on my Report Server, and I can load the Polygons.sln report and view it in the designer. However, whenever I preview the report in the designer or view the report on the server, the polygon report items just show up as big blank spots - there's no image there and no indication of an error. The chart on the report renders just fine, though.

Does anybody have any ideas on what could be causing this?

Thanks,

Glenn Burnsider

There is a typo in the PolygonsCRI.vb and .cs file. The class name should be "PolygonsCRI", not "PolygonsCCustomReportItem". Change the class name to "PolygonsCRI", redeploy, and the chart should be visible now.

Custom Rendering Example.

Does anyone know of any code example available for writing a custom
renderer? Or are there any third party renderers available?
Thanks.
Satish.In case anyone is looking for the same i found one in MSDN mag
http://msdn.microsoft.com/msdnmag/issues/05/02/CustomRenderers/
Satish wrote:
> Does anyone know of any code example available for writing a custom
> renderer? Or are there any third party renderers available?
> Thanks.
> Satish.

Custom Property for Remove Duplicates transform Input Row

Im working through the MS example of "removeDuplicates". I cant seem to figure out how to add custom property for input column.

I added the helper method:
private static void AddIsKeyCustomPropertyToInput(IDTSInput90 input, object value)
{
IDTSCustomProperty90 isKey = input.CustomPropertyCollection.New();
isKey.Name = "IsKey";
isKey.Value = value;
}
I call it from:
public override void ProvideComponentProperties()
{
//...
AddIsKeyCustomPropertyToInput(input, false);
//...
}
public override void ReinitializeMetaData()
{
IDTSInput90 input = ComponentMetaData.InputCollection[0];
if (input.CustomPropertyCollection.Count == 0)
{
AddIsKeyCustomPropertyToInput(input, false);
}
// ...
}

However when I deployed it and added the component to SSIS package - I cant see the Custom Column "IsKey" in the input column properties window.
What am I missing - please help As stated by some fore-sightful guy in a blog -

the way to SSIS enlightment is treacherous

. it seems i was missing something very basic - i was trying to set custom property for the whole input instead of just input column.

So instead of 2 above mentioned calls (in ProvideComponentProperties and ReinitializeMetaData ) i needed to set it in following:

public override IDTSInputColumn90 SetUsageType(int inputID, IDTSVirtualInput90 virtualInput, int lineageID, DTSUsageType usageType)
{
if (virtualInput == null)
{
throw new ArgumentNullException("virtualInput");
}

IDTSVirtualInputColumn90 vCol = virtualInput.VirtualInputColumnCollection.GetVirtualInputColumnByLineageID(lineageID);
IDTSInputColumn90 col = null;

/// No support for BLOB image columns.
if (vCol.DataType == DataType.DT_IMAGE)
throw new Exception("Binary data types not supported.");

/// If the usageType is UT_IGNORED, then the column is being removed.
/// So remove it from the outputs also.
if (usageType == DTSUsageType.UT_IGNORED)
{
/// ...
}
else
{
/// Let the base class add the input column.
col = base.SetUsageType(inputID, virtualInput, lineageID, usageType);
// Store the lineageID of the input column in a custom property of the output column.
IDTSCustomProperty90 inputColIsKey = col.CustomPropertyCollection.New();
inputColIsKey.Name = "IsKey";
inputColIsKey.Value = false;

/// Add an output column to the distinct and duplicate outputs.
AddOutputColumn(ComponentMetaData.OutputCollection[0].ID, col);
AddOutputColumn(ComponentMetaData.OutputCollection[1].ID, col);
}
return col;
}

Hopefully - when i have created this transform to filter duplicates based on (Primery) Key column(s) i can post it in my blog (to be created)|||Now if only I could figure out how to pass this custom "IsKey" property to class "Row" Equals operator and to class "Buffer" sort method

Custom Parameter entry with Reporting Services 2005

Hi,
Does anyone know how to do this ? I need to create a multi-tier parameter
entry screen for a reporting service project.
For example, I have one dropdown box - the user selects an item and that
Item will deterimine what is in a different list box, then the user can
select multiple items from the listbox to run the report.
I have no problem creating a custom ASPX page but I dont know how to
inegrate it into the report server, I would like the main report menu to
still be able to access this report ( and send it to the custom param page)
Any ideas on how to solve this problem ? I would like to avoid creating my
own custom report menu page if possible.
Thanks,
--
Dave GI don't know what you want exactly but from what I understand here is my
solution.
From your first parameter, make a new DataSet for the values in your second
parameter. Make a DataSet parameter that contains the report parameter
value. Then, in your second parameter, take values from With request and
select your DataSet.
If your values for the parameters aren't in your database then I don't see
how to do it unless you make an Expression to set your values in the second
parameter. You then have to verify the first parameter if it is the good
one. If yes then show the value. If not return Nothing.
I hope this will help you!
"Dave G" wrote:
> Hi,
> Does anyone know how to do this ? I need to create a multi-tier parameter
> entry screen for a reporting service project.
> For example, I have one dropdown box - the user selects an item and that
> Item will deterimine what is in a different list box, then the user can
> select multiple items from the listbox to run the report.
> I have no problem creating a custom ASPX page but I dont know how to
> inegrate it into the report server, I would like the main report menu to
> still be able to access this report ( and send it to the custom param page)
> Any ideas on how to solve this problem ? I would like to avoid creating my
> own custom report menu page if possible.
> Thanks,
> --
> Dave G|||What you are describing is called cascading parameters. RS supports this.
Search books on line for the phrase cascading parameters.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Dave G" <DaveG@.discussions.microsoft.com> wrote in message
news:70C8C6F5-E011-4098-9A90-22E72BB0C562@.microsoft.com...
> Hi,
> Does anyone know how to do this ? I need to create a multi-tier parameter
> entry screen for a reporting service project.
> For example, I have one dropdown box - the user selects an item and that
> Item will deterimine what is in a different list box, then the user can
> select multiple items from the listbox to run the report.
> I have no problem creating a custom ASPX page but I dont know how to
> inegrate it into the report server, I would like the main report menu to
> still be able to access this report ( and send it to the custom param
> page)
> Any ideas on how to solve this problem ? I would like to avoid creating my
> own custom report menu page if possible.
> Thanks,
> --
> Dave G

Custom Order By

Hi,

I want to ask something. Is there any possibility to use order by using our own criteria. Example: I want to order my query by ProductName with specified criteria. See below

Products:

ProductID ProductName

1 Book

2 Pen

3 Ruler

The query for "select * from Products order by ProductName" will actually result

1 Book

2 Pen

3 Ruler

I want the result be like this:

2 Pen

1 Book

3 Ruler

May be my crazy idea is using this kind of query:

"select * from Products order by ProductName ( 'Pen' , 'Book' , 'Ruler' )"

Thanks in advance .....

If you have a limited number of 'sort by' items, you could use a CASE structure.

SELECT
Col1,
Col2,
Col3,
etc
FROM MyTable
ORDER BY CASE Col2
WHEN 'Pen' THEN 1
WHEN 'Book' THEN 2
WHEN 'Ruler' THEN 3
END

|||

it works, great !!! ...

thanks ....

Custom Function in SQL Statement?

In MS Access, I could write a function in a module, then just call that function as part of the SQL statement. For example, "SELECT RemoveDashes([SS_No]) AS SSN FROM Employee" with "RemoveDashes" being the name of the function.

I'm trying to do the same with an asp.net page and sql server. I have a custom function in the code behind that I call in the SQL statement, but I get the error, "not a recognized function name".

What do I need to do to make this work?
All help is greatly appreciated!

Lynnetteasp.net and sql server are two different things...you cannot call a vb/c# function in your sql statement...you can write the function in a UDF in your sql server and call it in your sql statement.|||Thanks for your response. I am brand new to SQL Server, but not so new to asp.net. Any ideas how to modify this vb.net function to a UDF?

Thanks again for all your help!
lds

 Function GetWorkStatus(ByVal sU As Object, ByVal sSG As Object) As Integer
 Dim dtEdate As String = Calendar1.SelectedDate.ToShortDateString()
 Dim mydateMonth As Integer = DatePart("m", dtEdate)
 Dim thisDay As String = CStr(DatePart("d", dtEdate))
 Dim sDayOfWeek As String = CStr(DatePart("w", dtEdate))
 Dim cnn As New SqlConnection(constants.SQLConStrFLSA)
 Dim cmd As New SqlCommand("usp_CheckWorkStatus", cnn)
 Try
 With cmd
 .CommandType = CommandType.StoredProcedure
 With .Parameters.Add("@.ForThisDate", SqlDbType.DateTime)
 .Value = dtEdate
 End With
 With .Parameters.Add("@.mydateMonth", SqlDbType.Int)
 .Value = mydateMonth
 End With
 With .Parameters.Add("@.thisDay", SqlDbType.VarChar, 2)
 .Value = thisDay
 End With
 With .Parameters.Add("@.theUnion", SqlDbType.NVarChar, 2)
 .Value = sU
 'Session("sUn") = sU
 End With
 With .Parameters.Add("@.theSG", SqlDbType.NVarChar, 50)
 .Value = sSG
 'Session("strSG") = sSG
 End With
 End With
 cnn.Open()
 Dim da As New SqlDataAdapter(cmd)
 Dim ds As New DataSet
 da.Fill(ds, "SGDetails")
 'no record in FLSAScheduleGroup
 If ds.Tables("SGDetails").Rows.Count = 0 Then
 Select Case sSG
 Case "08"
 If sDayOfWeek = 1 OrElse sDayOfWeek = 7 Then 'Sunday or Saturday
 Return 0
 Else
 Return 1
 End If
 Case "09"
 If sDayOfWeek = 1 OrElse sDayOfWeek = 2 Then 'Sunday or Monday
 Return 0
 Else
 Return 1
 End If
 Case "12"
 If sDayOfWeek = 4 OrElse sDayOfWeek = 5 Then 'Wed or Thurs
 Return 0
 Else
 Return 1
 End If
 Case "13"
 If sDayOfWeek = 5 OrElse sDayOfWeek = 6 Then 'Thurs or Friday
 Return 0
 Else
 Return 1
 End If
 Case "14"
 If sDayOfWeek = 6 OrElse sDayOfWeek = 7 Then 'Friday or Saturday
 Return 0
 Else
 Return 1
 End If
 Case "31"
 If sDayOfWeek = 1 Or 7 Then
 Return 0
 Else
 Return 1
 End If
 Case Else
 Return 1
 End Select
 ElseIf ds.Tables("SGDetails").Rows.Count = 1 Then 'if there is a row in FLSAScheduleGroup
 Return 0
 End If
 Catch ex As Exception
 lblError.Text = ex.ToString
 Finally
 With cmd.Connection
 If .State = ConnectionState.Open Then
 .Close()
 End If
 End With
 Session("sUn") = Nothing
 Session("strSG") = Nothing
 End Try
 End Function

|||what you have seems to be fine although it can be fine-tuned a little bit more..i dont see where you are calling a function in a sql stmt here..|||I wrote the function for the vb.net code behind, and called it from the <ItemTemplate Text=> part of the datagrid.

I have no idea how to make it work in SQL - don't know how to write a UDF.

Thanks again for all your help. Sorry the code is so spaced out - don't know what the "POST-ing" did to it.

Lynnette|||ok you got me confused when you said you are trying to call a function in codebehind from SQL.

what you have seems to be a regular vb function that queries a database to fill a dataset. so what are you trying to do now ? does this function not work ? do you get any errors and if so at which line ? and what is the xact error message ?|||Thanks so much for sticking with me here. I'm the only one at my office that does .NET, so I'm kind of an island here.

The vb.net Function "GetWorkStatus" in the post above works fine. I get the error, "not a recognized function name" when I try to call it from another function, that is using it in the SQL statement to identify values for the specified field. Here is the calling function:

 Private Sub GetTmpRecs()
 'uses the AppSettings table to generate WHERE clause from the filter values
 Dim dtWdate As String = Calendar1.SelectedDate.ToShortDateString()
 Dim cnn As New SqlConnection(constants.SQLConStrFLSA)
 Dim wValue As String = Session("svFilterValue")
 Dim dtEdate As DateTime = Now()
 Dim strUser As String = Session("sUser")
 Dim sSQL As String = "INSERT INTO tmpFLSAEmpInfo "
 sSQL = sSQL & " (Emp_Number, PT_ID, Name_full, Division, Department, Job_Dept_Code, Dept_Mgr, "
 sSQL = sSQL & " DeptInfo, Job_Supervisor, Location, Shift, sUnion, sSG, WrkDate, WrkStatus, HrsWorked, Username, AddDate) SELECT "
 sSQL = sSQL & "hcsoSharedTables.dbo.Employee2.Emp_Number, "
 sSQL = sSQL & "hcsoSharedTables.dbo.Employee2.[ID], "
 sSQL = sSQL & "hcsoSharedTables.dbo.Employee2.Name_Full, "
 sSQL = sSQL & "hcsoSharedTables.dbo.Employee2.Division, "
 sSQL = sSQL & "hcsoSharedTables.dbo.Employee2.Department,"
 sSQL = sSQL & "hcsoSharedTables.dbo.employee2.Job_Dept_Code, "
 sSQL = sSQL & "hcsoSharedTables.dbo.employee2.Dept_Mgr, "
 sSQL = sSQL & "hcsoSharedTables.dbo.employee2.Department + ' - ' + hcsosharedtables.dbo.employee2.Dept_Mgr + ' - ' + hcsosharedtables.dbo.employee2.job_dept_code, "
 sSQL = sSQL & "hcsoSharedTables.dbo.Employee2.Job_Supervisor, "
 sSQL = sSQL & "hcsoSharedTables.dbo.Employee2.Loc_Name, "
 sSQL = sSQL & "hcsoSharedTables.dbo.Employee2.Shift, "
 sSQL = sSQL & "hcsoSharedTables.dbo.Employee2.[Union], "
 sSQL = sSQL & "hcsoSharedTables.dbo.Employee2.Sched_Group, "
 sSQL = sSQL & "'" & dtWdate & "', "
 sSQL = sSQL & "IIF(GetWorkStatus(hcsoSharedTables.dbo.Employee2.[Union], hcsoSharedTables.dbo.Employee2.Sched_Group)=0, 0,1) AS WrkStatus, "
 sSQL = sSQL & "IIF(GetWorkStatus(hcsoSharedTables.dbo.Employee2.[Union], hcsoSharedTables.dbo.Employee2.Sched_Group)=0, 0,1) AS HrsWorked, "
 sSQL = sSQL & "'" & strUser & "', "
 sSQL = sSQL & "'" & dtEdate & "' "
 'sSQL = sSQL & "INTO tmpFLSAEmpInfo "
 sSQL = sSQL & "FROM hcsoSharedTables.dbo.Employee2 " & wValue & ""
 Dim ds As DataSet = New DataSet
 constants.ExecuteSQL(sSQL, constants.SQLConStrFLSA)
 End Sub

|||So I got it right..you were indeed trying to call a vb function into an sql query..I can suggest moving the entire code into a stored proc..move all the logit into it..you can get it all done in one trip.

Custom Formatting

I need to Format some items in a different way.
for example I have values that are .962 and want
the report to format this to 0.962 but
if say the value is .65 I want it to format it as 0.65
how would I do this?Patrick,
Try Format(Convert.ToDouble("<your field>"), "0.00")
--
Hope this helps.
---
Teo Lachev, MVP [SQL Server], MCSD, MCT
Author: "Microsoft Reporting Services in Action"
Publisher website: http://www.manning.com/lachev
Buy it from Amazon.com: http://shrinkster.com/eq
Home page and blog: http://www.prologika.com/
---
"Patrick K" <PatrickK@.discussions.microsoft.com> wrote in message
news:FD864917-BC4A-4DFB-912F-B893AD8B6AC5@.microsoft.com...
> I need to Format some items in a different way.
> for example I have values that are .962 and want
> the report to format this to 0.962 but
> if say the value is .65 I want it to format it as 0.65
> how would I do this?|||"Teo Lachev [MVP]" wrote:
> Patrick,
> Try Format(Convert.ToDouble("<your field>"), "0.00")
> --
> Hope this helps.
> ---
> Teo Lachev, MVP [SQL Server], MCSD, MCT
> Author: "Microsoft Reporting Services in Action"
> Publisher website: http://www.manning.com/lachev
> Buy it from Amazon.com: http://shrinkster.com/eq
> Home page and blog: http://www.prologika.com/
> ---
> "Patrick K" <PatrickK@.discussions.microsoft.com> wrote in message
> news:FD864917-BC4A-4DFB-912F-B893AD8B6AC5@.microsoft.com...
> > I need to Format some items in a different way.
> > for example I have values that are .962 and want
> > the report to format this to 0.962 but
> > if say the value is .65 I want it to format it as 0.65
> >
> > how would I do this?
>
> That is good but what if the values are mixed, some are .962 and others are .65|||Then use a custom function which will determine the length of the string and
figure out the format specifier.
--
Hope this helps.
---
Teo Lachev, MVP [SQL Server], MCSD, MCT
Author: "Microsoft Reporting Services in Action"
Publisher website: http://www.manning.com/lachev
Buy it from Amazon.com: http://shrinkster.com/eq
Home page and blog: http://www.prologika.com/
---
"Patrick K" <PatrickK@.discussions.microsoft.com> wrote in message
news:74808F24-4605-4DAD-8F55-00D80D677B8F@.microsoft.com...
>
> "Teo Lachev [MVP]" wrote:
> > Patrick,
> >
> > Try Format(Convert.ToDouble("<your field>"), "0.00")
> >
> > --
> > Hope this helps.
> >
> > ---
> > Teo Lachev, MVP [SQL Server], MCSD, MCT
> > Author: "Microsoft Reporting Services in Action"
> > Publisher website: http://www.manning.com/lachev
> > Buy it from Amazon.com: http://shrinkster.com/eq
> > Home page and blog: http://www.prologika.com/
> > ---
> >
> > "Patrick K" <PatrickK@.discussions.microsoft.com> wrote in message
> > news:FD864917-BC4A-4DFB-912F-B893AD8B6AC5@.microsoft.com...
> > > I need to Format some items in a different way.
> > > for example I have values that are .962 and want
> > > the report to format this to 0.962 but
> > > if say the value is .65 I want it to format it as 0.65
> > >
> > > how would I do this?
> >
> >
> > That is good but what if the values are mixed, some are .962 and others
are .65

Custom fields in SSAS 2005 KPI's

Hi there,

What is the best way to get a custom field in a SSAS 2005 Cube KPI?
For example, what if the user wanted two target fields, or a budget field, or a contact person field for a KPI? (mostly static data, hard coded by the user).

Thanks.

You can use the Annotations property of KPIs (and other AS 2005 objects) to store custom information. The annotations is a property bag of name-value pairs and can contain most anything you like (including complex XML). If you set Visibility=SchemaRowset on an annotation, then the annotation will be available in the schema rowset and can be used by client applications. The down side to this approach is that off-the-shelf clients will generally ignore annotations and you'll probably need a custom client application to look for and use the annotations.

Custom conflict resolver example code in C#

Hi!

I'm trying to create a custom conflict resolver for SQL Server 2000 in

C# but i can't seem to find any source code examples of a *.dll where i

can actually see what needs to be done.

Any help is welcome.

Thanks

In SQL 2000, you can only do this in VB, C# isn't supported. However in SQL 2005, you can definitely use C#.

|||Thanks,

so that's why i couldn't find anything in C# :/

Going to try and find a different approach then.

Custom conflict resolver example code in C#

Hi!

I'm trying to create a custom conflict resolver for SQL Server 2000 in

C# but i can't seem to find any source code examples of a *.dll where i

can actually see what needs to be done.

Any help is welcome.

Thanks

In SQL 2000, you can only do this in VB, C# isn't supported. However in SQL 2005, you can definitely use C#.

|||Thanks,

so that's why i couldn't find anything in C# :/

Going to try and find a different approach then.

Custom Code - Reference Report Objects

Hi,
Can anyone point me in the right direction on how to reference report objects in a custom sub or function. For example if I want to assign a value to a report parameter or a textbox
public Sub SetDepth()
Parameters!test.Value = 2
ReportItems!Textbox23.value = "test"
end sub
I've tried really simle stuff like below but i get the following error [BC30469] Reference to a non-shared member requires an object reference.
Cheers
KevinYou can pass in the Parameters or ReportItems collection as an argument to
your function. Note you can't change the values of parameters/textboxes
though - they are read-only.
Fang Wang (MSFT)
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Kevin Wilson" <KevinWilson@.discussions.microsoft.com> wrote in message
news:E136C0ED-9890-4315-8455-14687D66FB24@.microsoft.com...
> Hi,
> Can anyone point me in the right direction on how to reference report
objects in a custom sub or function. For example if I want to assign a value
to a report parameter or a textbox
> public Sub SetDepth()
> Parameters!test.Value = 2
> ReportItems!Textbox23.value = "test"
> end sub
> I've tried really simle stuff like below but i get the following error
[BC30469] Reference to a non-shared member requires an object reference.
> Cheers
> Kevin|||How do you do this?
"Fang Wang (MSFT)" wrote:
> You can pass in the Parameters or ReportItems collection as an argument to
> your function. Note you can't change the values of parameters/textboxes
> though - they are read-only.
> Fang Wang (MSFT)
> Microsoft SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "Kevin Wilson" <KevinWilson@.discussions.microsoft.com> wrote in message
> news:E136C0ED-9890-4315-8455-14687D66FB24@.microsoft.com...
> > Hi,
> > Can anyone point me in the right direction on how to reference report
> objects in a custom sub or function. For example if I want to assign a value
> to a report parameter or a textbox
> >
> > public Sub SetDepth()
> > Parameters!test.Value = 2
> > ReportItems!Textbox23.value = "test"
> > end sub
> >
> > I've tried really simle stuff like below but i get the following error
> [BC30469] Reference to a non-shared member requires an object reference.
> >
> > Cheers
> > Kevin
>
>|||For example if you want to calculate average sales. You have two fields
Number of Units and Sales in dollars.
You can write custom code as below:
Public Function GetAvg(ByVal NoOfUnits As Integer, ByVal TotalSales As
Decimal) As Decimal
Dim ReturnValue As Decimal
ReturnValue = 0
If NoOfUnits <> 0 Then
ReturnValue = TotalSales/NoOfUnits
End If
Return ReturnValue
End
In your table cell you can write expression as:
Code.GetAvg(Fields!Units.Value, Fields!Sales.Value)
When you preview the report, you will see the calculated average value in
that cell.
I hope this helps.
"Tanya" <Tanya@.discussions.microsoft.com> wrote in message
news:68055C09-D38E-4CDA-990C-CD4641594E33@.microsoft.com...
> How do you do this?
> "Fang Wang (MSFT)" wrote:
>> You can pass in the Parameters or ReportItems collection as an argument
>> to
>> your function. Note you can't change the values of parameters/textboxes
>> though - they are read-only.
>> Fang Wang (MSFT)
>> Microsoft SQL Server Reporting Services
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>> "Kevin Wilson" <KevinWilson@.discussions.microsoft.com> wrote in message
>> news:E136C0ED-9890-4315-8455-14687D66FB24@.microsoft.com...
>> > Hi,
>> > Can anyone point me in the right direction on how to reference report
>> objects in a custom sub or function. For example if I want to assign a
>> value
>> to a report parameter or a textbox
>> >
>> > public Sub SetDepth()
>> > Parameters!test.Value = 2
>> > ReportItems!Textbox23.value = "test"
>> > end sub
>> >
>> > I've tried really simle stuff like below but i get the following error
>> [BC30469] Reference to a non-shared member requires an object reference.
>> >
>> > Cheers
>> > Kevin
>>

Custom Assembly Reading from Registry

Can anyone point me to an example of a MS SRS 2005 custom assembly reading
from the registry? We have had this issue on/off for several months, and we
are at the breaking point. The assembly has to access our database and we
want to store the connection information in the registry for shared use with
other components.
The network admins assure us that all users have read access to the
registry. It appears that we have granted our assembly fulltrust via
entries in the rs srs policy file. For a while were were using MS SRS 2000
and a temp work-around was to disable Code Access Security (CAS). We have
since upgraded to MS SRS 2005 and that work-around does not appear to work
anymore as our admin cannot seem to turn-off CAS (not that that is the
solution we want). Our code appears to fail when we assert read permission
on our registry key.
We're no longer in a trial-and-error approach to solving this. We've spent
way too long on something that, in our opinion, should be fairly simple. A
working example of a custom assembly, including policy file entiries, that
reads string values from the registry would be very welcome.
Any help would be appreciated.
Thanks,
ChrisHi Chris,
Thank you for your posting!
My understanding of this issue is: You want use custom assembly to access
the Registry in Reporting Services 2005. If I misunderstood your concern,
please feel free to let me know.
Based on my scope, you could turn-off CAS temporarily and try to run your
assembly.
Remember, in .NET 2005, you could only turn off CAS by using the Caspol.exe
temporarily. If you press <Enter> then you will restore the setting and
turn on the CAS.
If you could access the registry when you turn-off the CAS, then please
let me know what change have you done on your policy config file and post
the changes here.
If you still can not access the registry when you turn-off the CAS, we need
to start the Regmon to monitor the registry. You could download this tool
from the following site:
http://www.sysinternals.com/Utilities/Regmon.html.
Please let me know the result and so that I can provide further assistance.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi Chris ,
How is everything going? Please feel free to let me know if you need any
assistance.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||I apologize for the long delay. Other matters have taken precedence, but I
hope to return to this next week. I will try disabling CAS again to see if
my policy entry is the culprit and I'll let you know the results.
Thanks,
Chris
"Wei Lu [MSFT]" <weilu@.online.microsoft.com> wrote in message
news:tcrT04ApGHA.4612@.TK2MSFTNGXA01.phx.gbl...
> Hi Chris ,
> How is everything going? Please feel free to let me know if you need any
> assistance.
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ==================================================> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ==================================================> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>|||Hi Chris,
Thanks for your followup. Since Wei is OOF due to some urgent business,
I'll continue to help you on this issue. And sure, please feel free to
manage your first priority work and just let me know when you get any
progress or need any further assistance. We'll hold this thread and closely
monitoring it.
Looking forward to your update!
Sincerely,
Steven Cheng
Microsoft MSDN Online Support Lead
========================================
This posting is provided "AS IS" with no warranties, and confers no rights.|||This is a multi-part message in MIME format.
--=_NextPart_000_000C_01C6AA84.480797E0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
I executed my report that has a custom assemby that reads from the =registery. As previously described, it failed with the following error:
Request for the permission of type ='System.Security.Permissions.RegistryPermission, mscorlib, =Version=3D2.0.0.0, Culture=3Dneutral, PublicKeyToken=3Db77a5c561934e089' =failed
As directed I disabled code access security (caspol -s off). Prior to =pressing <enter>, I re-executed the report and still received the same =error. I both refreshed the report, and also closed the browser and =re-logged in to execute the report. No change.
My rssrvpolicy.config entry for my custom assembly is below.
<CodeGroup class=3D"UnionCodeGroup"
version=3D"1"
PermissionSetName=3D"FullTrust"
Name=3D"cgBBSDataFormatter"
Description=3D"">
<IMembershipCondition class=3D"UrlMembershipCondition"
version=3D"1"
Url=3D"file://D:/Program Files/Microsoft SQL =Server/MSSQL.3/Reporting =Services/ReportServer/bin/BBSDataFormatter.dll"/>
</CodeGroup>
He is nested within the following codegroup entry:
<CodeGroup class=3D"FirstMatchCodeGroup" version=3D"1" PermissionSetName=3D"Execution"
Description=3D"This code group grants MyComputer code Execution =permission. ">
<IMembershipCondition class=3D"ZoneMembershipCondition"
version=3D"1"
Zone=3D"MyComputer" />
Any help would be greatly appreciated.
- Chris
"Steven Cheng[MSFT]" <stcheng@.online.microsoft.com> wrote in message =news:eedfRYYqGHA.4188@.TK2MSFTNGXA01.phx.gbl...
> Hi Chris,
> > Thanks for your followup. Since Wei is OOF due to some urgent =business, > I'll continue to help you on this issue. And sure, please feel free to =
> manage your first priority work and just let me know when you get any > progress or need any further assistance. We'll hold this thread and =closely > monitoring it.
> > Looking forward to your update!
> > Sincerely,
> > Steven Cheng
> > Microsoft MSDN Online Support Lead
> > ==3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D==3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
> > This posting is provided "AS IS" with no warranties, and confers no =rights.
>
--=_NextPart_000_000C_01C6AA84.480797E0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

I executed my report that has a custom =assemby that reads from the registery. As previously described, it failed with =the following error:
Request for the permission of =type 'System.Security.Permissions.RegistryPermission, mscorlib, =Version=3D2.0.0.0, Culture=3Dneutral, PublicKeyToken=3Db77a5c561934e089' failed
As directed I disabled code access =security (caspol -s off). Prior to pressing , I re-executed the report =and still received the same error. I both refreshed the report, and =also closed the browser and re-logged in to execute the report. No change.
My rssrvpolicy.config entry for my =custom assembly is below.
 =;
He is nested within the following =codegroup entry:

Any help would be greatly =appreciated.
- Chris
"Steven Cheng[MSFT]" wrote in message news:eedfRYYqGHA.4188@.TK2MSFTNGXA01.phx.gbl...> =Hi Chris,> > Thanks for your followup. Since Wei is OOF due to some urgent =business, > I'll continue to help you on this issue. And sure, please feel =free to > manage your first priority work and just let me know when you =get any > progress or need any further assistance. We'll hold this thread =and closely > monitoring it.> > Looking forward to your =update!> > Sincerely,> > Steven =Cheng> > Microsoft MSDN Online Support Lead> > =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D==3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D> => This posting is provided "AS IS" with no warranties, and confers no rights.>

---

--=_NextPart_000_000C_01C6AA84.480797E0--|||Hi Chris,
Thanks a lot for your prompt response and the further detailed information.
After reading the security policy file you posted, I found that you use the
"UnionCodeGroup" for your custom codegroup type, I think it would be
better to use the "FirstMatchCodeGroup" which will exclude any other
matched CodeGroup and you need to put it as before other groups as possible
so that it will be evaluated and matched first in the policy file. The
custom codegroup element's content is ok from my view.
In addition, I've performed some tests on my local environment and noticed
some other problems here:
In our custom assembly's code there are two things we need to take care:
1. Make sure that our assembly is marked as
"AllowPartiallyTrustedCallersAttribute", such as:
[assembly:AllowPartiallyTrustedCallers]
2. Before we calling the registry code(or access any restricted resource),
we need to construct the certain Permission instance and Assert it. e.g:
public class Class1
{
public static string Test()
{
RegistryPermission perm = new
RegistryPermission(RegistryPermissionAccess.Read,
@."HKEY_LOCAL_MACHINE\SOFTWARE");
perm.Assert();
RegistryKey regkey =Registry.LocalMachine.OpenSubKey("Software", false);
int i = regkey.SubKeyCount;
return "LocalMachine_Software_SubKeyCount: " + i;
}
}
Here is a web article which has demonstrate a complete sample(it is
performing file access in custom assembly) and it has mentioned many things
we need to take care when developing and deploying custom assemby.
http://www.c-sharpcorner.com/Code/2005/June/CustomAssemblyinRS.asp
BTW, here is my custom codegroup section in the policy file:
===============<CodeGroup
class="FirstMatchCodeGroup"
version="1"
PermissionSetName="FullTrust"
Name="MyCustomAssemblyCodeGroup"
Description="A special code group for my custom assembly.">
<IMembershipCondition
class="UrlMembershipCondition"
version="1"
Url="C:\Program Files\Microsoft SQL Server\MSSQL.3\Reporting
Services\ReportServer\bin\CustomAssembly.dll"/>
</CodeGroup>
===================
Further more, if you still meet problem on this and if you feel convenient,
you can provide me a copy or code of your simplified custom assembly(which
can reproduce the problem behavior) so that I can do some further test on
my side.
Hope the above info helps.
Sincerely,
Steven Cheng
Microsoft MSDN Online Support Lead
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.|||Hello Chris,
Have you got any further process on this issue, or does the information in
my last reply also helps on this issue?
Curerntly we're still closely monitoring this issue, if you still have any
questions or if there is anything we can help, please feel free to post
here.
Sincerely,
Steven Cheng
Microsoft MSDN Online Support Lead
This posting is provided "AS IS" with no warranties, and confers no rights.|||Steven,
Modifying the policy file as suggested did the trick. We had previously
implemented the other two suggestions when we first ran into this problem.
Thank you for your help.
Regards,
Chris
"Steven Cheng[MSFT]" <stcheng@.online.microsoft.com> wrote in message
news:6UHfrWNrGHA.4272@.TK2MSFTNGXA01.phx.gbl...
> Hello Chris,
> Have you got any further process on this issue, or does the information in
> my last reply also helps on this issue?
> Curerntly we're still closely monitoring this issue, if you still have any
> questions or if there is anything we can help, please feel free to post
> here.
> Sincerely,
> Steven Cheng
> Microsoft MSDN Online Support Lead
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>|||Thanks for your followup Chris,
Glad to hear the good news.
Have a good day!
Sincerely,
Steven Cheng
Microsoft MSDN Online Support Lead
This posting is provided "AS IS" with no warranties, and confers no rights.

Custom Aggregate

How can you create and use a custom aggregate function in Reporting
Services? For example, to calculate the Median in a group.Noel,
You always have the choice for <expression> and you can use nearly
anything you can dream up in SQL or CLR.
Dennis Graham
Noel wrote:
> How can you create and use a custom aggregate function in Reporting
> Services? For example, to calculate the Median in a group.