Customized Delivery Extension

I am developing a customized delivery extension and I am trying to
write the stream renderedoutputfile[] to a file on the server. When I
write it to a file, the file is blank with a bunch of spaces in it. Do
I need to decode the Stream? Any help would be great.
Here is the code I am using.
This is inside the Delivery Method..
deviceInfo = String.Format(@."<DeviceInfo><OutputFormat>{0}</OutputFormat></DeviceInfo>","PDF");
// Render report
files = notification.Report.Render("PDF",deviceInfo);
BinaryReader br = new BinaryReader(m_files[0].Data);
byte[] byteData = new byte[(int)m_files[0].Data.Length];
int j = br.Read(byteData,0, (int)m_files[0].Data.Length);
FileStream fst = new FileStream(@."c:\temp\test.pdf", FileMode.Append,
FileAccess.Write);
BinaryWriter writer = new BinaryWriter(fst);
writer.Write(byteData,0,byteData.Length);
writer.Flush();
writer.Close();
What I am doing wrong? Code is in c#.Is it as simple as:
files = notification.Report.Render("PDF",deviceInfo);
=> note you're writing to local variable files
BinaryReader br = new BinaryReader(m_files[0].Data);
=> note you're reading from member variable m_files
-Lukasz
This posting is provided "AS IS" with no warranties, and confers no rights.
"jason" <obryan7@.hotmail.com> wrote in message
news:d16403e7.0407180926.620e1a79@.posting.google.com...
> I am developing a customized delivery extension and I am trying to
> write the stream renderedoutputfile[] to a file on the server. When I
> write it to a file, the file is blank with a bunch of spaces in it. Do
> I need to decode the Stream? Any help would be great.
> Here is the code I am using.
> This is inside the Delivery Method..
> deviceInfo =String.Format(@."<DeviceInfo><OutputFormat>{0}</OutputFormat></DeviceInfo>","
PDF");
> // Render report
> files = notification.Report.Render("PDF",deviceInfo);
> BinaryReader br = new BinaryReader(m_files[0].Data);
> byte[] byteData = new byte[(int)m_files[0].Data.Length];
> int j = br.Read(byteData,0, (int)m_files[0].Data.Length);
> FileStream fst = new FileStream(@."c:\temp\test.pdf", FileMode.Append,
> FileAccess.Write);
> BinaryWriter writer = new BinaryWriter(fst);
> writer.Write(byteData,0,byteData.Length);
> writer.Flush();
> writer.Close();
> What I am doing wrong? Code is in c#.

Customers & Phones

Hello,
I have a customers table linked to a phones table.
Is there a way to write an update query which will take the first & second
phone number of every customer and put it into respective fields (strPhone1
&
strPhone2) on the customer table, and dump any other phones into a memo fiel
d?
Thanks,
LomasWhy? There is so much wrong with this is is not funny. First normal form
violations in each of your columns, for starters, but beyond that:
Data put into memo fields (text columns, I assume) is pretty much useless in
your database from then on. All of the things you can do with it is
basically lost. Best case, you use a query to make a view of the data like
you are asking for and abstract it out from there. I know it takes more
work to do it right at first but it is worth it in the long run. Not to
mention that you can ask here for any query help to make the abstractions
work.
A moderately reasonable solution would be to have two columns <purpose>Phone
and <otherPurpose>Phone in your customer table if they will always have the
same purpose and then the phone table to keep other phone types.
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"optidev" <optidev@.community.nospam> wrote in message
news:34051B1C-0F85-4F32-8A20-798B5F2FCD34@.microsoft.com...
> Hello,
> I have a customers table linked to a phones table.
> Is there a way to write an update query which will take the first & second
> phone number of every customer and put it into respective fields
> (strPhone1 &
> strPhone2) on the customer table, and dump any other phones into a memo
> field?
> Thanks,
> Lomas
>|||Ok, let me explain a little bit better.
I am writing this sql code as part of a modification to a database. After
this update query comes the drop tblPhones statement. This sql statement, i
n
its final form, will handle the range of phone types.
It seems to me that allowing a user an unlimited number of phone numbers
will have little practical value, and will be more difficult to query.
Modifying at this stage, while there are still few users, shouldn't cause
too much of an impact.
"Louis Davidson" wrote:

> Why? There is so much wrong with this is is not funny. First normal form
> violations in each of your columns, for starters, but beyond that:
> Data put into memo fields (text columns, I assume) is pretty much useless
in
> your database from then on. All of the things you can do with it is
> basically lost. Best case, you use a query to make a view of the data lik
e
> you are asking for and abstract it out from there. I know it takes more
> work to do it right at first but it is worth it in the long run. Not to
> mention that you can ask here for any query help to make the abstractions
> work.
> A moderately reasonable solution would be to have two columns <purpose>Pho
ne
> and <otherPurpose>Phone in your customer table if they will always have th
e
> same purpose and then the phone table to keep other phone types.
>
> --
> ----
--
> Louis Davidson - http://spaces.msn.com/members/drsql/
> SQL Server MVP
> "Arguments are to be avoided: they are always vulgar and often convincing.
"
> (Oscar Wilde)
> "optidev" <optidev@.community.nospam> wrote in message
> news:34051B1C-0F85-4F32-8A20-798B5F2FCD34@.microsoft.com...
>
>|||On a conceptual level, SSN, LastName and Ethnicity are attributes of the
Customer, but PhoneNumber and Address are not. One or more PhoneNumber(s)
are related to a specific Customer at a given moment in time, but could
belong to an entirely different Customer tomorrow. Consider, relating them
using a CustomerPhone table with StartDate and EndDate columns to keep an
audit whenever their profile changes.
"optidev" <optidev@.community.nospam> wrote in message
news:6B7323C5-3FE7-41AB-8EFD-0F13C978E1F3@.microsoft.com...
> Ok, let me explain a little bit better.
> I am writing this sql code as part of a modification to a database. After
> this update query comes the drop tblPhones statement. This sql statement,
> in
> its final form, will handle the range of phone types.
> It seems to me that allowing a user an unlimited number of phone numbers
> will have little practical value, and will be more difficult to query.
> Modifying at this stage, while there are still few users, shouldn't cause
> too much of an impact.
>
> "Louis Davidson" wrote:
>|||This is exactly what he wants to avoid, though it is still not a good idea
:)
To answer your original question, you will have to define what data makes a
phone number first or second. If you go ahead with this change, I would at
least suggest you have a column for home phone, one for mobile phone, etc.
rather than phone1, phone2. This is really bad design. On the other hand,
limiting the user to only one phone number of a specific type is a valid
design that doesn't leave the end user guessing what they are storing where.
Post your table structures and someone will help write the query..
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"JT" <someone@.microsoft.com> wrote in message
news:u89fgLkqFHA.272@.TK2MSFTNGP15.phx.gbl...
> On a conceptual level, SSN, LastName and Ethnicity are attributes of the
> Customer, but PhoneNumber and Address are not. One or more PhoneNumber(s)
> are related to a specific Customer at a given moment in time, but could
> belong to an entirely different Customer tomorrow. Consider, relating them
> using a CustomerPhone table with StartDate and EndDate columns to keep an
> audit whenever their profile changes.
> "optidev" <optidev@.community.nospam> wrote in message
> news:6B7323C5-3FE7-41AB-8EFD-0F13C978E1F3@.microsoft.com...
>|||Hi ,
Write a update trigger for your main table and put a code like this , i cant
exactly remember right now
if updated(firstPhone) || updated(secondPhone)
......
"optidev" <optidev@.community.nospam> wrote in message
news:34051B1C-0F85-4F32-8A20-798B5F2FCD34@.microsoft.com...
> Hello,
> I have a customers table linked to a phones table.
> Is there a way to write an update query which will take the first & second
> phone number of every customer and put it into respective fields
> (strPhone1 &
> strPhone2) on the customer table, and dump any other phones into a memo
> field?
> Thanks,
> Lomas
>|||I *was* planning to have have different columns for the different phone
types. I just didn't want to make the question too complicated. :)
Do you still think it is better to completely normilize the tables? In that
case I would need to keep track of the current primary address for use in
display and reporting. I don't see much practicality in knowing a customer'
s
previous addresses. Either way, I would also split the phones table if I wa
s
going to keep it this way.
Here are the tables with the added columns for phone types:
tblFamilies: --
pkFamilyID
strLastName
strAddress1
strAddress2
strCity
strZip
strHomePhone1
strHomePhone2
strHomeFax
strFamilyNotes
tblCustomers: --
pkCustomerID
fkFamilyID
strTitle
strFirstName
strMiddleInitial
strPostTitle
strWorkPhone1
strWorkPhone2
strWorkFax
strMobilePhone
strPager
strVoiceMail
strCustomerNotes
tblPhones: --
pkPhoneID
fkPhoneTypeID
fkFamilyID
fkCustomerID (one of these is null - either family or customer)
strPhoneNumber
strExtension (I also want to combine the extension with the phone number)
tblPhoneTypes: --
pkPhoneTypeID
numOrder
strDescription
The primary key currently defines what makes the phone number first or secon
d.
This code, for example, retrives all home phone numbers:
SELECT tblFamilies.pkFamilyID, tblPhoneTypes.strDescription, CASE WHEN
tblPhones.strExtension IS NULL
THEN tblPhones.strPhoneNumber ELSE
tblPhones.strPhoneNumber + ' x' + tblPhones.strExtension END AS PhoneNumber
FROM tblPhones INNER JOIN
tblFamilies ON tblPhones.fkFamilyID =
tblFamilies.pkFamilyID LEFT OUTER JOIN
tblPhoneTypes ON tblPhones.fkPhoneTypeID =
tblPhoneTypes.pkPhoneTypeID
WHERE (tblPhoneTypes.strDescription = N'Home')
UNION
SELECT tblFamilies.pkFamilyID, tblPhoneTypes.strDescription, CASE WHEN
tblPhones.strExtension IS NULL
THEN tblPhones.strPhoneNumber ELSE
tblPhones.strPhoneNumber + ' x' + tblPhones.strExtension END AS PhoneNumber
FROM tblPhones INNER JOIN
tblCustomers ON tblPhones.fkCustomerID =
tblCustomers.pkCustomerID INNER JOIN
tblFamilies ON tblCustomers.fkFamilyID =
tblFamilies.pkFamilyID LEFT OUTER JOIN
tblPhoneTypes ON tblPhones.fkPhoneTypeID =
tblPhoneTypes.pkPhoneTypeID
WHERE (tblPhoneTypes.strDescription = N'Home')
I can add this kind of complexity to the statement after I know the coding
method.
Thanks,
Lomas
"Louis Davidson" wrote:

> This is exactly what he wants to avoid, though it is still not a good idea
> :)
> To answer your original question, you will have to define what data makes
a
> phone number first or second. If you go ahead with this change, I would a
t
> least suggest you have a column for home phone, one for mobile phone, etc.
> rather than phone1, phone2. This is really bad design. On the other hand
,
> limiting the user to only one phone number of a specific type is a valid
> design that doesn't leave the end user guessing what they are storing wher
e.
> Post your table structures and someone will help write the query..
> --
> ----
--
> Louis Davidson - http://spaces.msn.com/members/drsql/
> SQL Server MVP
> "Arguments are to be avoided: they are always vulgar and often convincing.
"
> (Oscar Wilde)
> "JT" <someone@.microsoft.com> wrote in message
> news:u89fgLkqFHA.272@.TK2MSFTNGP15.phx.gbl...
>
>|||Interesting... Now I am thinking of normalizing the addresses and
denormalizing the phones... I still don't really see a point in keeping a
history of phone numbers.
What would be the best way to set up the query for retriveing the current
primary address in such a case?
As for the normalized phones table, I ran into a problem when a customer
asked me to export a list of them. He needed a column for each phone type
(Home1, Home2, HomeFax, etc.). How would I set up the query to put the firs
t
home number (it found) into the Home1 column, the second into Home2, etc.
What it there are (let's say) 15 of them? Also, how would I set up the quer
y
for reporting purposes. (I don't want every one to print when I print the
report.)
Thanks,
Lomas
"JT" wrote:

> Ask the users if they need a history of changes to a user's profile. If yo
u
> are shipping product to a customer's address, then knowing the address whe
re
> you actually shipped a product on a specific date would not only be useful
> but critical. Also, the marketing department may be interested in datamini
ng
> the changes in their customer's demographics. If the customers who generat
e
> the most sales are migrating to another geographical area, then that would
> be significant.
> "JT" <someone@.microsoft.com> wrote in message
> news:u89fgLkqFHA.272@.TK2MSFTNGP15.phx.gbl...
>
>|||What is is the customer's most recent address?
select top 1
Address
from
CustomerAddress
where
CustomerID = @.CustomerID
order by
StartDate desc
What was the customer's address on the date that the product was shipped?
select top 1
Address
from
CustomerAddress
where
CustomerID = @.CustomerID and
StartDate <= @.ShipDate
order by
StartDate desc
If you have a normalized database and want to return a crosstab query
(Home1,Home2,Cell1,Cell2,etc) for reporting purposes, you can use inline
sub-queries like below. However, this will perform better and is more
flexible when implemented on the client side using Crystal, Excel or
whatever your reporting tool happens to be.
select
CustomerName,
(select bottom top 1 Phone from CustomerPhone where CustomerID =
@.CustomerID and PhoneType='H' order by StartDate desc) as Home1,
(select bottom 1 Phone from (select bottom top 2 Phone from
CustomerPhone where CustomerID = @.CustomerID and PhoneType='H' order by
StartDate desc) as x) as Home2,
(select bottom 1 Phone from (select bottom top 3 Phone from
CustomerPhone where CustomerID = @.CustomerID and PhoneType='H' order by
StartDate desc) as x) as Home3,
(select bottom top 1 Phone from CustomerPhone where CustomerID =
@.CustomerID and PhoneType='C' order by StartDate desc) as Cell1,
(select bottom 1 Phone from (select bottom top 2 Phone from
CustomerPhone where CustomerID = @.CustomerID and PhoneType='C' order by
StartDate desc) as x) as Cell2,
(select bottom 1 Phone from (select bottom top 3 Phone from
CustomerPhone where CustomerID = @.CustomerID and PhoneType='C' order by
StartDate desc) as x) as Cell3
from
Customers
where
CustomerID = @.CustomerID
"optidev" <optidev@.community.nospam> wrote in message
news:A0E6085D-D6EE-42FC-BCC9-524FFFDEAF5A@.microsoft.com...
> Interesting... Now I am thinking of normalizing the addresses and
> denormalizing the phones... I still don't really see a point in keeping a
> history of phone numbers.
> What would be the best way to set up the query for retriveing the current
> primary address in such a case?
> As for the normalized phones table, I ran into a problem when a customer
> asked me to export a list of them. He needed a column for each phone type
> (Home1, Home2, HomeFax, etc.). How would I set up the query to put the
> first
> home number (it found) into the Home1 column, the second into Home2, etc.
> What it there are (let's say) 15 of them? Also, how would I set up the
> query
> for reporting purposes. (I don't want every one to print when I print the
> report.)
> Thanks,
> Lomas
>
> "JT" wrote:
>|||select bottom doesn't work
"optidev" wrote:
> Interesting... Now I am thinking of normalizing the addresses and
> denormalizing the phones... I still don't really see a point in keeping a
> history of phone numbers.
> What would be the best way to set up the query for retriveing the current
> primary address in such a case?
> As for the normalized phones table, I ran into a problem when a customer
> asked me to export a list of them. He needed a column for each phone type
> (Home1, Home2, HomeFax, etc.). How would I set up the query to put the fi
rst
> home number (it found) into the Home1 column, the second into Home2, etc.
> What it there are (let's say) 15 of them? Also, how would I set up the qu
ery
> for reporting purposes. (I don't want every one to print when I print the
> report.)
> Thanks,
> Lomas
>
> "JT" wrote:
>

custome code - global?

Is the custom code you write in the Properties section of one report
accessible to other reports within the same project? if so, how is it
accessed?
Thanks,
PMNo it is not. Consider code behind reports. If you want to do this you have
to use custom assemblies. I.e. create the code outside of RS and then
reference it from RS.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"PacMan" <PacMan@.discussions.microsoft.com> wrote in message
news:32547C22-21CA-4EB6-9482-6DDB4A21A630@.microsoft.com...
> Is the custom code you write in the Properties section of one report
> accessible to other reports within the same project? if so, how is it
> accessed?
> Thanks,
> PM

Custom Security Extension issue...

I have tried to write a custom security extension. When I run the report
manager I get ...
Object Reference is not set to an instance of an object.
It's breaking in GlobalApp.Application_AuthenticationRequest
I figure its because it can't find my extension but I have the correct Url
in the securityextensioncodegroup. Could it be the type attibute in the
extension tag?
Type="RSSecurity.AuthorizationExtension,AuthorizationExtension"You may be getting the exception in the GetWebRequest override in the
derived ReportingService class because the HttpContext.Current.Request is
null which causes the call to the AuthCookie property to fail. I'm having a
similar problem that I'm working through right now. Maybe this will give you
some place to start looking for the problem.
Just a thought...
Garry
"Tad" <Tad@.discussions.microsoft.com> wrote in message
news:CF04B8C0-641A-4D50-9A90-B49625266D90@.microsoft.com...
> I have tried to write a custom security extension. When I run the report
> manager I get ...
> Object Reference is not set to an instance of an object.
> It's breaking in GlobalApp.Application_AuthenticationRequest
> I figure its because it can't find my extension but I have the correct Url
> in the securityextensioncodegroup. Could it be the type attibute in the
> extension tag?
> Type="RSSecurity.AuthorizationExtension,AuthorizationExtension"|||Gary,
Have you found a solution to this issue of the
HttpContext.Current.Request being null or found more information on
what might cause this to be null?
- Jessica
Garry Lenz wrote:
> You may be getting the exception in the GetWebRequest override in the
> derived ReportingService class because the
HttpContext.Current.Request is
> null which causes the call to the AuthCookie property to fail. I'm
having a
> similar problem that I'm working through right now. Maybe this will
give you
> some place to start looking for the problem.
> Just a thought...
> Garry
>
> "Tad" <Tad@.discussions.microsoft.com> wrote in message
> news:CF04B8C0-641A-4D50-9A90-B49625266D90@.microsoft.com...
> > I have tried to write a custom security extension. When I run the
report
> > manager I get ...
> >
> > Object Reference is not set to an instance of an object.
> >
> > It's breaking in GlobalApp.Application_AuthenticationRequest
> >
> > I figure its because it can't find my extension but I have the
correct Url
> > in the securityextensioncodegroup. Could it be the type attibute
in the
> > extension tag?
> >
> > Type="RSSecurity.AuthorizationExtension,AuthorizationExtension"|||This looks familiar. It could be one of a myriad of things. In our case,
the custom security extension called a web service, and there was an invalid
type definition in the web reference. We had to delete all our binaries and
web references from the development environment, reattach the web references
and recompile. Then it worked.
I hope that points you in the right direction. Bottom line issue: don't
lose your cookies.
--
Cheers,
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
<Jessica.Landisman@.ndchealth.com> wrote in message
news:1105364285.978877.33130@.c13g2000cwb.googlegroups.com...
> Gary,
> Have you found a solution to this issue of the
> HttpContext.Current.Request being null or found more information on
> what might cause this to be null?
> - Jessica
> Garry Lenz wrote:
>> You may be getting the exception in the GetWebRequest override in the
>> derived ReportingService class because the
> HttpContext.Current.Request is
>> null which causes the call to the AuthCookie property to fail. I'm
> having a
>> similar problem that I'm working through right now. Maybe this will
> give you
>> some place to start looking for the problem.
>> Just a thought...
>> Garry
>>
>> "Tad" <Tad@.discussions.microsoft.com> wrote in message
>> news:CF04B8C0-641A-4D50-9A90-B49625266D90@.microsoft.com...
>> > I have tried to write a custom security extension. When I run the
> report
>> > manager I get ...
>> >
>> > Object Reference is not set to an instance of an object.
>> >
>> > It's breaking in GlobalApp.Application_AuthenticationRequest
>> >
>> > I figure its because it can't find my extension but I have the
> correct Url
>> > in the securityextensioncodegroup. Could it be the type attibute
> in the
>> > extension tag?
>> >
>> > Type="RSSecurity.AuthorizationExtension,AuthorizationExtension"
>sql

Custom Report Item

Hello.
I need to write a CRI which will update a attribute in the RDL file.
Can anyone point me in the right direction.
This is my view I
will have a textbox and a button on the CRI.
While building a report through BI , the report author adds CRI.
enter a value in the text box and click ok.
Then the text in the textbox needs to be added to the RDL file.
Thanks!!
RadhikaHello,
Does any one have ideas.
Thanks!!
"Radhika" wrote:
> Hello.
> I need to write a CRI which will update a attribute in the RDL file.
> Can anyone point me in the right direction.
> This is my view I
> will have a textbox and a button on the CRI.
> While building a report through BI , the report author adds CRI.
> enter a value in the text box and click ok.
> Then the text in the textbox needs to be added to the RDL file.
>
> Thanks!!
> Radhika

Custom Rendering Extension in SQL 2000 Reporting Services

I am trying to write a custom rendering extension for the SQL Server 2000
version of Reporting Services. The documentation I found on MSDN indicates
that it is possible to write one using Visual Studio .Net 2003 (which is what
I'm using). The samples they have are for VS.Net 2005. I'm trying to
implement it for VS.Net 2003, and I can't seem to find the references for:
Microsoft.ReportingServices.ReportRendering.IRenderingExtension
in SQL 2000 Reporting Services. What is the equivalent in SQL Server 2000
RS and Visual Studio .Net 2003?
Thanks,
Mike SandwickMicrosoft.ReportingServices.ReportRendering.IRenderingExtension is identical
in RS 2000 and RS 2005. In RS 2000 you need to add a reference to
Microsoft.ReportingServices.Processing.dll, in the final RS 2005 release it
would be Microsoft.ReportingServices.ProcessingCore.dll.
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Mike Sandwick" <MikeSandwick@.discussions.microsoft.com> wrote in message
news:F9A76586-30EE-450D-993E-7FEE3ADA1BF3@.microsoft.com...
>I am trying to write a custom rendering extension for the SQL Server 2000
> version of Reporting Services. The documentation I found on MSDN
> indicates
> that it is possible to write one using Visual Studio .Net 2003 (which is
> what
> I'm using). The samples they have are for VS.Net 2005. I'm trying to
> implement it for VS.Net 2003, and I can't seem to find the references for:
> Microsoft.ReportingServices.ReportRendering.IRenderingExtension
> in SQL 2000 Reporting Services. What is the equivalent in SQL Server 2000
> RS and Visual Studio .Net 2003?
> Thanks,
> Mike Sandwick|||Thanks, Robert. This is exactly the information I was looking for! I added
the reference to Microsoft.ReportingServices.Processing.dll, and I can now
compile the sample.
Thanks,
Mike Sandwick
"Robert Bruckner [MSFT]" wrote:
> Microsoft.ReportingServices.ReportRendering.IRenderingExtension is identical
> in RS 2000 and RS 2005. In RS 2000 you need to add a reference to
> Microsoft.ReportingServices.Processing.dll, in the final RS 2005 release it
> would be Microsoft.ReportingServices.ProcessingCore.dll.
>
> -- Robert
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Mike Sandwick" <MikeSandwick@.discussions.microsoft.com> wrote in message
> news:F9A76586-30EE-450D-993E-7FEE3ADA1BF3@.microsoft.com...
> >I am trying to write a custom rendering extension for the SQL Server 2000
> > version of Reporting Services. The documentation I found on MSDN
> > indicates
> > that it is possible to write one using Visual Studio .Net 2003 (which is
> > what
> > I'm using). The samples they have are for VS.Net 2005. I'm trying to
> > implement it for VS.Net 2003, and I can't seem to find the references for:
> >
> > Microsoft.ReportingServices.ReportRendering.IRenderingExtension
> >
> > in SQL 2000 Reporting Services. What is the equivalent in SQL Server 2000
> > RS and Visual Studio .Net 2003?
> >
> > Thanks,
> > Mike Sandwick
>
>

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 forms

I have created custom form without borders , i have written functions to minimize, maximize, exit and even for moving the window, now i need to write function to resize the window , is there any way resize the window...locking this post as this isn't replication or sql server related, and I can't move it since I don't even know what programming language you're using. Please repost your question in the correct forum.

Custom Delivery for Printer

Hi,
For Reporting Services for SQL Server 2000, we need to write a custom
delivery dll for printer right?
Just wondering has microsoft come out with a sample of it already? Maybe a
sample code or a working DLL already?
Best Regards,
ShelA printer delivery sample shipped with RS 2000. If you run setup you can
install the samples. There is help for the sample in the help
documentation.
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"Shelby" <shelby@.singnet.com.sg> wrote in message
news:OiSAqH%23AGHA.516@.TK2MSFTNGP15.phx.gbl...
> Hi,
> For Reporting Services for SQL Server 2000, we need to write a custom
> delivery dll for printer right?
> Just wondering has microsoft come out with a sample of it already? Maybe a
> sample code or a working DLL already?
> Best Regards,
> Shel
>

Custom Delivery Extension

Hello Everyone,
I'm in the process of creating a custom delivery extension that will
write to a file share and then email users that the file is available.
I have used the custom printing extension sample as the basis for
creating my FileShareEmail extension.
I think my question is unlike most posted here in that, I have gotten
all the config files to work. So far I have set up a few parameters
(email address to and from). I then send an email with smtp.
I successfully receive the email and I created the notification.Status
= "Status: Email Sent.". This is what i see in the log file:
ReportingServicesService!notification!3ffc!11/02/2005-09:42:06::
Notification 7493a45c-2f0b-431c-bb53-592a99a5977c completed. Success:
False, Status: Email Sent., DeliveryExtension: File Share Email,
Report: 01 - Standard Listing Report, Attempt 1
There are no exceptions and the email was sent. The Report Manager
still has Done: 1 processed of 1 total; 1 errors. in the status column.
I cannot figure out why there was an error generated. Is there a
property that i forgot to set?
Another question I have is: I can see my custom extension in data
driven subscriptions but not in the regular subscriptions. Is this by
design or am I missing a config.
Resources question: I've looked quite a few places for some more
examples (other than the printer example), does anyone know some good
blogs or sites that have some different samples implemented?
The fourth and final question: Since I am trying to replicate some of
the existing functionality of RS, is there a way to send the email
through RS instead of coding my own error checking and such into my
extension?
Thanks for your help!
Regards,
DanI figured out one of the 4 questions. My Deliver() method was
returning the wrong boolean value (i.e. true when failed and false when
succeeded).

Custom Data Mining Functions

I would like to write a custom mining function, which takes a string, queries the database, and returns an answer based upon those queries. So the basic function is then:

[MiningFunction("Performs Foo")]
public string Foo(string param)
{
// process parameters
// query database

// calculate answer from query results

// return query results
}

And is executed from the client using:

SELECT Foo("X Y Z") FROM FooModel

This arrangement is so that resource-intensive calculations are performed server-side.

My question is: what is the preferrable method for executing the database query from within the custom mining function?

Custom mining functions are not actually designed for this kind of operations. They are intended for predictive features that are related to the mining model and typically this kind of operations do not need external access (such as a database query). I assume that your function's calculation part will use some information from the mining model and apply it to the database query results.

I think you should use a stored procedure. Inside the stored procedure, you should use the server side object model (add a reference to Microsoft.AnalysisServices.AdomdServer). With the server side object model, you can perform the following operations:

- use AdomdCommand to execute calls such as CALL SystemOpenQuery(DataSource, Query), which is the recommended way of querying a relational database from analysis services

- also use AdomdCommand to execute calls such as SELECT .... FROM YourModel PREDICTION JOIN OPENQUERY(DataSource, Query)

This would allow you to get the information from the data base together with scoring for each, scoring computed as a prediction from your model ). Your code could use the results and perform aggregations or more complex computations on the result.

If, in the code of your stored procedure, you need to get extra information from your model, you can traverse the content of the mining model using the object model.

The article at http://www.sqlserverdatamining.com/DMCommunity/TipsNTricks/4264.aspx contains such a stored procedure, which requires both data and model content information, so I think it may be a good example. The data is coming directly from the model, with a drillthrough query. You can replace that query with a CALL SystemOpenQuery or prediction against an OPENQUERY statement.

Hope this helps

|||Yes, this is helpful. I'm looking at the material you referenced to see if it completely answers my question. Unfortunately, I don't seem able to progress pass the logon screen at sqlserverdatamining at the moment, so I can't get the .cs example...|||Do you have an account on sqlserverdatamining.com? Do you have problems logging in with your account? Or creating a new account

Custom Data Extension Trace

Hi,
could anybody help to figure out what is the correct (recommended) way to
write trace messages for custom data processing extension? I have written
such an extension and I would like to enable trace for it to be able to see
how it performs through the time.
I tried to write trace messages using RSTrace, but I got permission
exception and I guess it means that reporting services prevents to use this
class in different assembly than ReportingServices.Diagnostics.dll.
Then I tried to add trace listener (TextWriterTraceListener) to
<system.diagnostics> section of ReportingServicesService.exe.config file.
Doing so I got an IOException that the target file is used by different
process and report server refuse to start at all.
Is it possible to somhow use System.Diagnostics.Trace in custom data
extension?
Thanks,
MarekI'm not sure, but I'm just guessing you'll need to add some lines to the
rssrvpolicy.config file located here:
C:\Program Files\Microsoft SQL Server\MSSQL\Reporting Services\ReportServer
Have you done this?
--
Cheers,
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"Marek Zoth" <MarekZoth@.discussions.microsoft.com> wrote in message
news:EE4A6E80-6FDB-4900-8835-5F8117285E6F@.microsoft.com...
> Hi,
> could anybody help to figure out what is the correct (recommended) way to
> write trace messages for custom data processing extension? I have written
> such an extension and I would like to enable trace for it to be able to
> see
> how it performs through the time.
> I tried to write trace messages using RSTrace, but I got permission
> exception and I guess it means that reporting services prevents to use
> this
> class in different assembly than ReportingServices.Diagnostics.dll.
> Then I tried to add trace listener (TextWriterTraceListener) to
> <system.diagnostics> section of ReportingServicesService.exe.config file.
> Doing so I got an IOException that the target file is used by different
> process and report server refuse to start at all.
> Is it possible to somhow use System.Diagnostics.Trace in custom data
> extension?
> Thanks,
> Marek|||Thanks Jeff,
I tried, my extension runs in FullTrust mode and I tried to increase
permissions also for ReportingServices.Diagnostics.dll but no success. The
exception I got is:
--> System.Security.SecurityException: Request for the permission of type
System.Security.Permissions.StrongNameIdentityPermission, mscorlib,
Version=1.0.5000.0, Culture=neutral, PublicKeyToken=b77a5c561934e089 failed.
at DataExtension.TACommand.ExecuteReader(CommandBehavior behavior)
at Microsoft.ReportingServices.ReportProcessing.a0.v()
The state of the failed permission was:
<IPermission
class="System.Security.Permissions.StrongNameIdentityPermission, mscorlib,
Version=1.0.5000.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"
version="1"
PublicKeyBlob="0024000004800000940000000602000000240000525341310004000001000100272736AD6E5F9586BAC2D531EABC3ACC666C2F8EC879FA94F8F7B0327D2FF2ED523448F83C3D5C5DD2DFC7BC99C5286B2C125117BF5CBE242B9D41750732B2BDFFE649C6EFB8E5526D526FDD130095ECDB7BF210809C6CDAD8824FAA9AC0310AC3CBA2AA0523567B2DFA7FE250B30FACBD62D4EC99B94AC47C7D3B28F1F6E4C8"/>
I am not sure what it exactly means but looking to MSDN it seeem to me RS
prevents calling RSTrace. Or am I wrong?
Any idea how to write traces in custom data extensions is appreciated.
Thanks,
M.
"Jeff A. Stucker" wrote:
> I'm not sure, but I'm just guessing you'll need to add some lines to the
> rssrvpolicy.config file located here:
> C:\Program Files\Microsoft SQL Server\MSSQL\Reporting Services\ReportServer
> Have you done this?
> --
> Cheers,
> '(' Jeff A. Stucker
> \
> Business Intelligence
> www.criadvantage.com
> ---
> "Marek Zoth" <MarekZoth@.discussions.microsoft.com> wrote in message
> news:EE4A6E80-6FDB-4900-8835-5F8117285E6F@.microsoft.com...
> > Hi,
> >
> > could anybody help to figure out what is the correct (recommended) way to
> > write trace messages for custom data processing extension? I have written
> > such an extension and I would like to enable trace for it to be able to
> > see
> > how it performs through the time.
> >
> > I tried to write trace messages using RSTrace, but I got permission
> > exception and I guess it means that reporting services prevents to use
> > this
> > class in different assembly than ReportingServices.Diagnostics.dll.
> >
> > Then I tried to add trace listener (TextWriterTraceListener) to
> > <system.diagnostics> section of ReportingServicesService.exe.config file.
> > Doing so I got an IOException that the target file is used by different
> > process and report server refuse to start at all.
> >
> > Is it possible to somhow use System.Diagnostics.Trace in custom data
> > extension?
> >
> > Thanks,
> >
> > Marek
>
>

Custom Code...For the Sake of Argument

Is it possible to write custom code that achieves the same thing as an aggregate function?

ex: Calculating the sum of a group? (with out using the sum( field!one.value) function)

if I had a group that grouped on name.

Name Number
header Fields!FirstName.Value ************
details Fields!Number.Value
footer

Can i write a custom code function (or functions) that will get the sume of the numbers in that group.

Thanks in advance.

Hi all

I am also interested in writing code that can access the datasets created for the reports. I wanted to write a search function that searched a dataset from a parameter input to custom code. I am trying to count records of a group, and also trying to count filltered records within that group to calculate a percentage and display them on a group banner. The easy formatting examples everybody provides for custom code is no help... I was trying to use ADO code with the already created datasets, but got nowhere... That infernal "error in custom code" I think is hard-coded into the SSRS...

Dick

I just stumbled on to "Microsoft Reporting Services inAction: Extending Microsoft SQL Server 2000 Reporting Services with Custom Code". Some light weekend reading.

|||

Yes you can perform certain types of custom aggregations via custom code. One example is shown in the following blog posting: http://blogs.msdn.com/bwelcker/archive/2005/05/10/416306.aspx

-- Robert

|||Currently SSRS doesn't support custom aggregates although SQL Server 2005 provides this capability in the form of CLR custom aggregate functions. One approach is to prepare your dataset in such a way that your aggregates can be produced by the standard SSRS aggregate functions, e.g. SUM. For example, if some report lines need to add up while others to subtract from the group aggregate, you can have an additional sign column which will have values of 1 or -1 accordingly.

Custom Code problem!

Hi,
I want to use Custom Code to control the Visibility of a Group.
I write a test code, it always Return True:
Function CanSeen() as Boolean
Return True
End Function
Then I set the initial visibility of a group to Expression =Code.CanSeen()
But the the group can't seen!!
What is the problem?Are you calling your custom code from the row properties? If so, which
property? The "hidden" one? If so, notice that RS is asking you "do you want
this row to be hidden?" and you're saying "True". Perhaps that's the point.
I hope this helps
--
Please mark the correct/helpful answers!
"ad" wrote:
> Hi,
> I want to use Custom Code to control the Visibility of a Group.
> I write a test code, it always Return True:
> Function CanSeen() as Boolean
> Return True
> End Function
> Then I set the initial visibility of a group to Expression =Code.CanSeen()
> But the the group can't seen!!
> What is the problem?
>
>
>

Custom Charts...

Does reporting services currently allow me to write my own particular chart
that will appear inline int he report as the stock reports do?
reports and reports, but I would like to have some specific views that arent
in the chart selection.
Thanks in advance
Weston WeemsThere are several options:
* RS 2000:
You could draw your chart in a custom assembly and retrieve the value via
the Image.Value expression. Note: the custom assembly would be responsible
to retrieve all the data needed to draw the chart.
If you search the archives of this newsgroup you should find some related
postings from me and other people about how to do this.
* RS 2005:
In addition to what you can do on RS 2000, you could take advantage of the
new CustomReportItem feature and write your own custom processing control.
In this case, you could leverage the capabilities of the RS processing
engine to retrieve, group, sort, filter the data for you and the custom
processing control can read the already processed data through an object
model and then draw the chart image and return it as Image "rendering item".
More information and samples about this will be available when SQL Server
2005 is going to be released.
Robert M. Bruckner
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Weston Weems" <wweems@.NOSPAMno-span-gmail.com> wrote in message
news:%23lzKjnWXFHA.2128@.TK2MSFTNGP14.phx.gbl...
> Does reporting services currently allow me to write my own particular
> chart that will appear inline int he report as the stock reports do?
> reports and reports, but I would like to have some specific views that
> arent in the chart selection.
> Thanks in advance
> Weston Weems
>

Custom Charting

Hey can I write my own charting component for Reporting Services? Where
could I find information on that?
Thanks,
CJHello CJ,
Generally the answer you'll get to that question is "no" you cannot create
your own charting component for Reporting Services.
What you can't do easily is create a new custom component that you can host
in a Report and in the Report Designer.
However if you are prepared to make some compromises you most certainly can
create a custom component that takes your data and generates an image stream
that is displayed in a Reporting Services Image control.
This is usually best done by creating a custom data processing extension,
and ensuring that one field is an image stream of the data, but if you think
carfully about how to get all the data your component requires parsed into a
DataSet field then you can do this entirely in a function within a custom
assembly. What you do there is pass in the DataSet field containing your
parsed data, and have the Function work with this data and return an image
stream.
ok - so that is indeed only going to return a static image of your
component, but depending on what you are trying to do, you may be able to
programmatically set the Jump to URL Action property of the image control
containing the image of your component to jump to a URL in which you might
have a more fully functional and interactive version of the component.
I think future versions of Reporting Services would be missing a trick if
they don't provide a straightforward mechanism for hosting custom components
within reports, although there are doubtless architechural issues that the
developers will have to work out.
Peter Blackburn
Windows Server Systems - SQL Server MVP
Author: Hitchhiker's Guide to SQL Server Reporting Services
http://www.sqlreportingservices.net
"CJ Taylor" <nono@.nono.com> wrote in message
news:%23rmBRmqyEHA.1452@.TK2MSFTNGP11.phx.gbl...
> Hey can I write my own charting component for Reporting Services? Where
> could I find information on that?
> Thanks,
> CJ
>

Custom Assemblies RS 2000

Hi,

I'm creating a report with 3 parameters. The first parameter will hold the user ID. In order to get this I need to write a function to find the NT login name of the user and then search in a SQL Server Table to find what the User ID is for that user.

This user ID will be required in around 15 or so reports so I suspect a custom assembly will suit the purpose rather than adding custom code to each report.

Can anybody tell me if this sort of thing achievable?

Thanks in advance,

Steve

Sure. You can take a look at the documentation on custom assemblies here http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rsprog/htm/rsp_prog_rdl_6d0i.asp

Custom Aggregation Functions like SUM, AVERAGE etc.

Hi,

I want to write a custom aggregation function called PRODUCT (as it exists in Excel) to be used with [Measures].[Monthly Return] column. I could not see such function in AggregateFunction attribute of the mesaure in the Cube explorer.

What are the different ways I can write my own Aggregation functions? OR Achieve similar functionality with any alternative approach?

Appreciate your response.. let me know if further information is required.

-Ashish

You can't write your own Aggregation functions but you can certainly achieve the same result using MDX Script assignments to control how measures roll up. Regarding your specific problem, there is a function in the Analysis Services Stored Procedure project which does exactly what you're looking for:

http://www.codeplex.com/Wiki/View.aspx?ProjectName=ASStoredProcedures&title=Multiplication

...although my understanding is that using a sproc in your MDX Script could have an adverse effect on caching, so you might want to test out using the sproc and the technique using logs which is described by Darren Gosbell here:

http://geekswithblogs.net/darrengosbell/archive/2006/07/18/85539.aspx

HTH,

Chris

Custom aggregate function

hi how do i write a custom aggregate function or select query.I have Table name MemberDetail where which has two column Memberid and WokingDay.In this table same member could have multiple working days. i want select query that will return single row for each member and his/her quama separated working days. without using cursor. how do i do that.

eg

Table MemberDetail

MemberID WorkingDay

M1234 12

M1234 10

M1555 02

M1555 15

See

http://www.aspfaq.com/show.asp?id=2529

|||

cool its working,but i also wanted to get count as

MEMBERID WORKINDAYS COUNT

M1234 12,10 2

M1555 02,15 2

|||select MEMBERID, yourstrcatfunction(MEMBERID), count(*)
from yourtable
group by MEMBERID