Create a modified version of an existing rendering extension

Is there any documentation or sample code to point me in the right direction
on how to modify an existing rendering extension?
I need to add some browser checking to the existing JavaScript that is
generated for the toolbar. I was hoping to modify the existing HTML 4.0
rendering to include some more browser checking to replace some of the
specific IE JavaScript properties.
Any help would be greatly appreciated.
Thanks,This is not possible. All of our renderers have link demands and therefore
could not be loaded by your assemblies.
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
".Net Report Dev" <aanargyros@.hotmail.com> wrote in message
news:OwFagExeEHA.3412@.TK2MSFTNGP11.phx.gbl...
> Is there any documentation or sample code to point me in the right
direction
> on how to modify an existing rendering extension?
> I need to add some browser checking to the existing JavaScript that is
> generated for the toolbar. I was hoping to modify the existing HTML 4.0
> rendering to include some more browser checking to replace some of the
> specific IE JavaScript properties.
> Any help would be greatly appreciated.
> Thanks,
>|||Are you saying what I am trying cannot be done or that I cannot modify an
existing rendering extension. According to the books online I should be
able to modify an existing extension. If I cannot modify an existing
extension, is there any way for me to modify the toolbar JavaScript?
"Daniel Reib [MSFT]" <danreib@.online.microsoft.com> wrote in message
news:unQWaTxeEHA.2916@.TK2MSFTNGP12.phx.gbl...
> This is not possible. All of our renderers have link demands and
therefore
> could not be loaded by your assemblies.
> --
> -Daniel
> This posting is provided "AS IS" with no warranties, and confers no
rights.
>
> ".Net Report Dev" <aanargyros@.hotmail.com> wrote in message
> news:OwFagExeEHA.3412@.TK2MSFTNGP11.phx.gbl...
> > Is there any documentation or sample code to point me in the right
> direction
> > on how to modify an existing rendering extension?
> >
> > I need to add some browser checking to the existing JavaScript that is
> > generated for the toolbar. I was hoping to modify the existing HTML 4.0
> > rendering to include some more browser checking to replace some of the
> > specific IE JavaScript properties.
> >
> > Any help would be greatly appreciated.
> >
> > Thanks,
> >
> >
>|||You can not modify the existing renderers. Can you point me to where in the
docs it says this?
We currently have no mechanism for modifying the JavaScript in the toolbar
so I am not sure how you could accomplish what you are trying to do.
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
".Net Report Dev" <aanargyros@.hotmail.com> wrote in message
news:#XaMU8xeEHA.1656@.TK2MSFTNGP09.phx.gbl...
> Are you saying what I am trying cannot be done or that I cannot modify an
> existing rendering extension. According to the books online I should be
> able to modify an existing extension. If I cannot modify an existing
> extension, is there any way for me to modify the toolbar JavaScript?
>
> "Daniel Reib [MSFT]" <danreib@.online.microsoft.com> wrote in message
> news:unQWaTxeEHA.2916@.TK2MSFTNGP12.phx.gbl...
> > This is not possible. All of our renderers have link demands and
> therefore
> > could not be loaded by your assemblies.
> >
> > --
> > -Daniel
> > This posting is provided "AS IS" with no warranties, and confers no
> rights.
> >
> >
> > ".Net Report Dev" <aanargyros@.hotmail.com> wrote in message
> > news:OwFagExeEHA.3412@.TK2MSFTNGP11.phx.gbl...
> > > Is there any documentation or sample code to point me in the right
> > direction
> > > on how to modify an existing rendering extension?
> > >
> > > I need to add some browser checking to the existing JavaScript that is
> > > generated for the toolbar. I was hoping to modify the existing HTML
4.0
> > > rendering to include some more browser checking to replace some of the
> > > specific IE JavaScript properties.
> > >
> > > Any help would be greatly appreciated.
> > >
> > > Thanks,
> > >
> > >
> >
> >
>|||Could you point me at the section in books online that you're referring to?
You cannot extend our existing rendering extensions. You can extend report
server by writing another rendering extension.
No you cannot change/add script to the report viewer or the report manager.
-Lukasz
This posting is provided "AS IS" with no warranties, and confers no rights.
".Net Report Dev" <aanargyros@.hotmail.com> wrote in message
news:%23XaMU8xeEHA.1656@.TK2MSFTNGP09.phx.gbl...
> Are you saying what I am trying cannot be done or that I cannot modify an
> existing rendering extension. According to the books online I should be
> able to modify an existing extension. If I cannot modify an existing
> extension, is there any way for me to modify the toolbar JavaScript?
>
> "Daniel Reib [MSFT]" <danreib@.online.microsoft.com> wrote in message
> news:unQWaTxeEHA.2916@.TK2MSFTNGP12.phx.gbl...
>> This is not possible. All of our renderers have link demands and
> therefore
>> could not be loaded by your assemblies.
>> --
>> -Daniel
>> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>>
>> ".Net Report Dev" <aanargyros@.hotmail.com> wrote in message
>> news:OwFagExeEHA.3412@.TK2MSFTNGP11.phx.gbl...
>> > Is there any documentation or sample code to point me in the right
>> direction
>> > on how to modify an existing rendering extension?
>> >
>> > I need to add some browser checking to the existing JavaScript that is
>> > generated for the toolbar. I was hoping to modify the existing HTML
>> > 4.0
>> > rendering to include some more browser checking to replace some of the
>> > specific IE JavaScript properties.
>> >
>> > Any help would be greatly appreciated.
>> >
>> > Thanks,
>> >
>> >
>>
>|||Is there any documentation on creating your own rendering extension?
Maybe I can create a custom toolbar within my new rendering extension and
then hide the existing toolbar using the command provided by reporting
services.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rsprog/htm/rsp_prog_extend_security_87oi.asp
Here is a snippet.
Writing Custom Rendering Extensions
Before you decide to create a custom rendering extension, you should
evaluate simpler alternatives. You can:
a.. Create a modified version of an existing rendering extension.
b.. Customize rendered output by specifying device information settings
for existing extensions.
c.. Add custom formatting and presentation features by combining XSL
Transformations (XSLT) with the output of the XML rendering format.
"Daniel Reib [MSFT]" <danreib@.online.microsoft.com> wrote in message
news:%23L0zLVyeEHA.2764@.TK2MSFTNGP11.phx.gbl...
> You can not modify the existing renderers. Can you point me to where in
the
> docs it says this?
> We currently have no mechanism for modifying the JavaScript in the toolbar
> so I am not sure how you could accomplish what you are trying to do.
> --
> -Daniel
> This posting is provided "AS IS" with no warranties, and confers no
rights.
>
> ".Net Report Dev" <aanargyros@.hotmail.com> wrote in message
> news:#XaMU8xeEHA.1656@.TK2MSFTNGP09.phx.gbl...
> > Are you saying what I am trying cannot be done or that I cannot modify
an
> > existing rendering extension. According to the books online I should be
> > able to modify an existing extension. If I cannot modify an existing
> > extension, is there any way for me to modify the toolbar JavaScript?
> >
> >
> >
> > "Daniel Reib [MSFT]" <danreib@.online.microsoft.com> wrote in message
> > news:unQWaTxeEHA.2916@.TK2MSFTNGP12.phx.gbl...
> > > This is not possible. All of our renderers have link demands and
> > therefore
> > > could not be loaded by your assemblies.
> > >
> > > --
> > > -Daniel
> > > This posting is provided "AS IS" with no warranties, and confers no
> > rights.
> > >
> > >
> > > ".Net Report Dev" <aanargyros@.hotmail.com> wrote in message
> > > news:OwFagExeEHA.3412@.TK2MSFTNGP11.phx.gbl...
> > > > Is there any documentation or sample code to point me in the right
> > > direction
> > > > on how to modify an existing rendering extension?
> > > >
> > > > I need to add some browser checking to the existing JavaScript that
is
> > > > generated for the toolbar. I was hoping to modify the existing HTML
> 4.0
> > > > rendering to include some more browser checking to replace some of
the
> > > > specific IE JavaScript properties.
> > > >
> > > > Any help would be greatly appreciated.
> > > >
> > > > Thanks,
> > > >
> > > >
> > >
> > >
> >
> >
>|||I guess I did not read the documentation correctly. It sounded to me that
you can modify the existing extensions but instead it is saying create
another version of an existing extension with your changes. If that is what
the document means than how is that an alternative to writing a new
extension? Its not like MS will give up the source so that I can just
create my own version.
"Daniel Reib [MSFT]" <danreib@.online.microsoft.com> wrote in message
news:%23L0zLVyeEHA.2764@.TK2MSFTNGP11.phx.gbl...
> You can not modify the existing renderers. Can you point me to where in
the
> docs it says this?
> We currently have no mechanism for modifying the JavaScript in the toolbar
> so I am not sure how you could accomplish what you are trying to do.
> --
> -Daniel
> This posting is provided "AS IS" with no warranties, and confers no
rights.
>
> ".Net Report Dev" <aanargyros@.hotmail.com> wrote in message
> news:#XaMU8xeEHA.1656@.TK2MSFTNGP09.phx.gbl...
> > Are you saying what I am trying cannot be done or that I cannot modify
an
> > existing rendering extension. According to the books online I should be
> > able to modify an existing extension. If I cannot modify an existing
> > extension, is there any way for me to modify the toolbar JavaScript?
> >
> >
> >
> > "Daniel Reib [MSFT]" <danreib@.online.microsoft.com> wrote in message
> > news:unQWaTxeEHA.2916@.TK2MSFTNGP12.phx.gbl...
> > > This is not possible. All of our renderers have link demands and
> > therefore
> > > could not be loaded by your assemblies.
> > >
> > > --
> > > -Daniel
> > > This posting is provided "AS IS" with no warranties, and confers no
> > rights.
> > >
> > >
> > > ".Net Report Dev" <aanargyros@.hotmail.com> wrote in message
> > > news:OwFagExeEHA.3412@.TK2MSFTNGP11.phx.gbl...
> > > > Is there any documentation or sample code to point me in the right
> > > direction
> > > > on how to modify an existing rendering extension?
> > > >
> > > > I need to add some browser checking to the existing JavaScript that
is
> > > > generated for the toolbar. I was hoping to modify the existing HTML
> 4.0
> > > > rendering to include some more browser checking to replace some of
the
> > > > specific IE JavaScript properties.
> > > >
> > > > Any help would be greatly appreciated.
> > > >
> > > > Thanks,
> > > >
> > > >
> > >
> > >
> >
> >
>|||Yes, the documentation seems to be a little misleading. I will forward it
to our doc team to see if they can make it a little more clear.
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
".Net Report Dev" <aanargyros@.hotmail.com> wrote in message
news:u#6YtzyeEHA.4092@.TK2MSFTNGP10.phx.gbl...
> Is there any documentation on creating your own rendering extension?
> Maybe I can create a custom toolbar within my new rendering extension and
> then hide the existing toolbar using the command provided by reporting
> services.
>
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rsprog/htm/rsp_prog_extend_security_87oi.asp
> Here is a snippet.
> Writing Custom Rendering Extensions
> Before you decide to create a custom rendering extension, you should
> evaluate simpler alternatives. You can:
> a.. Create a modified version of an existing rendering extension.
> b.. Customize rendered output by specifying device information settings
> for existing extensions.
> c.. Add custom formatting and presentation features by combining XSL
> Transformations (XSLT) with the output of the XML rendering format.
>
> "Daniel Reib [MSFT]" <danreib@.online.microsoft.com> wrote in message
> news:%23L0zLVyeEHA.2764@.TK2MSFTNGP11.phx.gbl...
> > You can not modify the existing renderers. Can you point me to where in
> the
> > docs it says this?
> >
> > We currently have no mechanism for modifying the JavaScript in the
toolbar
> > so I am not sure how you could accomplish what you are trying to do.
> >
> > --
> > -Daniel
> > This posting is provided "AS IS" with no warranties, and confers no
> rights.
> >
> >
> > ".Net Report Dev" <aanargyros@.hotmail.com> wrote in message
> > news:#XaMU8xeEHA.1656@.TK2MSFTNGP09.phx.gbl...
> > > Are you saying what I am trying cannot be done or that I cannot modify
> an
> > > existing rendering extension. According to the books online I should
be
> > > able to modify an existing extension. If I cannot modify an existing
> > > extension, is there any way for me to modify the toolbar JavaScript?
> > >
> > >
> > >
> > > "Daniel Reib [MSFT]" <danreib@.online.microsoft.com> wrote in message
> > > news:unQWaTxeEHA.2916@.TK2MSFTNGP12.phx.gbl...
> > > > This is not possible. All of our renderers have link demands and
> > > therefore
> > > > could not be loaded by your assemblies.
> > > >
> > > > --
> > > > -Daniel
> > > > This posting is provided "AS IS" with no warranties, and confers no
> > > rights.
> > > >
> > > >
> > > > ".Net Report Dev" <aanargyros@.hotmail.com> wrote in message
> > > > news:OwFagExeEHA.3412@.TK2MSFTNGP11.phx.gbl...
> > > > > Is there any documentation or sample code to point me in the right
> > > > direction
> > > > > on how to modify an existing rendering extension?
> > > > >
> > > > > I need to add some browser checking to the existing JavaScript
that
> is
> > > > > generated for the toolbar. I was hoping to modify the existing
HTML
> > 4.0
> > > > > rendering to include some more browser checking to replace some of
> the
> > > > > specific IE JavaScript properties.
> > > > >
> > > > > Any help would be greatly appreciated.
> > > > >
> > > > > Thanks,
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>|||Is there any documentation on creating your own rendering extension?
Maybe I can create a custom toolbar within my new rendering extension and
then hide the existing toolbar using the command provided by reporting
services.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rsprog/htm/rsp_prog_extend_security_87oi.asp
Here is a snippet.
Writing Custom Rendering Extensions
Before you decide to create a custom rendering extension, you should
evaluate simpler alternatives. You can:
a.. Create a modified version of an existing rendering extension.
b.. Customize rendered output by specifying device information settings
for existing extensions.
c.. Add custom formatting and presentation features by combining XSL
Transformations (XSLT) with the output of the XML rendering format.
"Lukasz Pawlowski [MSFT]" <lukaszp@.online.microsoft.com> wrote in message
news:uf6ndVyeEHA.1036@.TK2MSFTNGP10.phx.gbl...
> Could you point me at the section in books online that you're referring
to?
> You cannot extend our existing rendering extensions. You can extend
report
> server by writing another rendering extension.
> No you cannot change/add script to the report viewer or the report
manager.
> -Lukasz
>
> --
> This posting is provided "AS IS" with no warranties, and confers no
rights.
>
> ".Net Report Dev" <aanargyros@.hotmail.com> wrote in message
> news:%23XaMU8xeEHA.1656@.TK2MSFTNGP09.phx.gbl...
> > Are you saying what I am trying cannot be done or that I cannot modify
an
> > existing rendering extension. According to the books online I should be
> > able to modify an existing extension. If I cannot modify an existing
> > extension, is there any way for me to modify the toolbar JavaScript?
> >
> >
> >
> > "Daniel Reib [MSFT]" <danreib@.online.microsoft.com> wrote in message
> > news:unQWaTxeEHA.2916@.TK2MSFTNGP12.phx.gbl...
> >> This is not possible. All of our renderers have link demands and
> > therefore
> >> could not be loaded by your assemblies.
> >>
> >> --
> >> -Daniel
> >> This posting is provided "AS IS" with no warranties, and confers no
> > rights.
> >>
> >>
> >> ".Net Report Dev" <aanargyros@.hotmail.com> wrote in message
> >> news:OwFagExeEHA.3412@.TK2MSFTNGP11.phx.gbl...
> >> > Is there any documentation or sample code to point me in the right
> >> direction
> >> > on how to modify an existing rendering extension?
> >> >
> >> > I need to add some browser checking to the existing JavaScript that
is
> >> > generated for the toolbar. I was hoping to modify the existing HTML
> >> > 4.0
> >> > rendering to include some more browser checking to replace some of
the
> >> > specific IE JavaScript properties.
> >> >
> >> > Any help would be greatly appreciated.
> >> >
> >> > Thanks,
> >> >
> >> >
> >>
> >>
> >
> >
>|||Is there any documentation on creating your own rendering extension that I
can use now?
"Daniel Reib [MSFT]" <danreib@.online.microsoft.com> wrote in message
news:eTRNDCzeEHA.1644@.tk2msftngp13.phx.gbl...
> Yes, the documentation seems to be a little misleading. I will forward it
> to our doc team to see if they can make it a little more clear.
> --
> -Daniel
> This posting is provided "AS IS" with no warranties, and confers no
rights.
>
> ".Net Report Dev" <aanargyros@.hotmail.com> wrote in message
> news:u#6YtzyeEHA.4092@.TK2MSFTNGP10.phx.gbl...
> > Is there any documentation on creating your own rendering extension?
> > Maybe I can create a custom toolbar within my new rendering extension
and
> > then hide the existing toolbar using the command provided by reporting
> > services.
> >
> >
>
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rsprog/htm/rsp_prog_extend_security_87oi.asp
> >
> > Here is a snippet.
> >
> > Writing Custom Rendering Extensions
> > Before you decide to create a custom rendering extension, you should
> > evaluate simpler alternatives. You can:
> >
> > a.. Create a modified version of an existing rendering extension.
> > b.. Customize rendered output by specifying device information
settings
> > for existing extensions.
> > c.. Add custom formatting and presentation features by combining XSL
> > Transformations (XSLT) with the output of the XML rendering format.
> >
> >
> > "Daniel Reib [MSFT]" <danreib@.online.microsoft.com> wrote in message
> > news:%23L0zLVyeEHA.2764@.TK2MSFTNGP11.phx.gbl...
> > > You can not modify the existing renderers. Can you point me to where
in
> > the
> > > docs it says this?
> > >
> > > We currently have no mechanism for modifying the JavaScript in the
> toolbar
> > > so I am not sure how you could accomplish what you are trying to do.
> > >
> > > --
> > > -Daniel
> > > This posting is provided "AS IS" with no warranties, and confers no
> > rights.
> > >
> > >
> > > ".Net Report Dev" <aanargyros@.hotmail.com> wrote in message
> > > news:#XaMU8xeEHA.1656@.TK2MSFTNGP09.phx.gbl...
> > > > Are you saying what I am trying cannot be done or that I cannot
modify
> > an
> > > > existing rendering extension. According to the books online I
should
> be
> > > > able to modify an existing extension. If I cannot modify an
existing
> > > > extension, is there any way for me to modify the toolbar JavaScript?
> > > >
> > > >
> > > >
> > > > "Daniel Reib [MSFT]" <danreib@.online.microsoft.com> wrote in message
> > > > news:unQWaTxeEHA.2916@.TK2MSFTNGP12.phx.gbl...
> > > > > This is not possible. All of our renderers have link demands and
> > > > therefore
> > > > > could not be loaded by your assemblies.
> > > > >
> > > > > --
> > > > > -Daniel
> > > > > This posting is provided "AS IS" with no warranties, and confers
no
> > > > rights.
> > > > >
> > > > >
> > > > > ".Net Report Dev" <aanargyros@.hotmail.com> wrote in message
> > > > > news:OwFagExeEHA.3412@.TK2MSFTNGP11.phx.gbl...
> > > > > > Is there any documentation or sample code to point me in the
right
> > > > > direction
> > > > > > on how to modify an existing rendering extension?
> > > > > >
> > > > > > I need to add some browser checking to the existing JavaScript
> that
> > is
> > > > > > generated for the toolbar. I was hoping to modify the existing
> HTML
> > > 4.0
> > > > > > rendering to include some more browser checking to replace some
of
> > the
> > > > > > specific IE JavaScript properties.
> > > > > >
> > > > > > Any help would be greatly appreciated.
> > > > > >
> > > > > > Thanks,
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>|||So, although the documentation alludes to modifying an existing rendering
extension, what it is actually referring to is creating a version of a
rendering extension that is similar to ones that we already have. That is,
writing a new HTML rendering extension. I must admit that it is misleading
and that writing one of our rendering extensions from scratch is not a
"simpler alternative."
Writing a rendering extension is no simple matter, in fact, it is one of the
most challenging development experiences I have seen at Microsoft. Please
see the following blog post for my take:
http://blogs.msdn.com/bryanke/archive/2004/03/16/90797.aspx
The Reporting Services team, in light of other key deliverables, elected not
to document rendering extensions in version 1 of Reporting Services because
it was considered a low usage task (only a few select partners ever
expressed an interest in developing one and only a handful have ever
successfully done it and that is after spending months with our developers
hands-on).
It is not an end-user feature and is not something for the average or even
advanced developer. If you are interested, we have included documentation
for rendering extensions in our SQL Server 2005 Books Online that was
released with SQL Server 2005 Beta 2 recently. We hope to someday create an
SDK that will explain rendering extensions in more detail, but that is
probably a ways off.
Bryan
--
Bryan Keller
Developer Documentation
SQL Server Reporting Services
A friendly reminder that this posting is provided "AS IS" with no
warranties, and confers no rights.
".Net Report Dev" <aanargyros@.hotmail.com> wrote in message
news:eB9luSzeEHA.1692@.tk2msftngp13.phx.gbl...
> Is there any documentation on creating your own rendering extension?
> Maybe I can create a custom toolbar within my new rendering extension and
> then hide the existing toolbar using the command provided by reporting
> services.
>
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rsprog/htm/rsp_prog_extend_security_87oi.asp
> Here is a snippet.
> Writing Custom Rendering Extensions
> Before you decide to create a custom rendering extension, you should
> evaluate simpler alternatives. You can:
> a.. Create a modified version of an existing rendering extension.
> b.. Customize rendered output by specifying device information settings
> for existing extensions.
> c.. Add custom formatting and presentation features by combining XSL
> Transformations (XSLT) with the output of the XML rendering format.
> "Lukasz Pawlowski [MSFT]" <lukaszp@.online.microsoft.com> wrote in message
> news:uf6ndVyeEHA.1036@.TK2MSFTNGP10.phx.gbl...
> > Could you point me at the section in books online that you're referring
> to?
> > You cannot extend our existing rendering extensions. You can extend
> report
> > server by writing another rendering extension.
> >
> > No you cannot change/add script to the report viewer or the report
> manager.
> >
> > -Lukasz
> >
> >
> > --
> > This posting is provided "AS IS" with no warranties, and confers no
> rights.
> >
> >
> > ".Net Report Dev" <aanargyros@.hotmail.com> wrote in message
> > news:%23XaMU8xeEHA.1656@.TK2MSFTNGP09.phx.gbl...
> > > Are you saying what I am trying cannot be done or that I cannot modify
> an
> > > existing rendering extension. According to the books online I should
be
> > > able to modify an existing extension. If I cannot modify an existing
> > > extension, is there any way for me to modify the toolbar JavaScript?
> > >
> > >
> > >
> > > "Daniel Reib [MSFT]" <danreib@.online.microsoft.com> wrote in message
> > > news:unQWaTxeEHA.2916@.TK2MSFTNGP12.phx.gbl...
> > >> This is not possible. All of our renderers have link demands and
> > > therefore
> > >> could not be loaded by your assemblies.
> > >>
> > >> --
> > >> -Daniel
> > >> This posting is provided "AS IS" with no warranties, and confers no
> > > rights.
> > >>
> > >>
> > >> ".Net Report Dev" <aanargyros@.hotmail.com> wrote in message
> > >> news:OwFagExeEHA.3412@.TK2MSFTNGP11.phx.gbl...
> > >> > Is there any documentation or sample code to point me in the right
> > >> direction
> > >> > on how to modify an existing rendering extension?
> > >> >
> > >> > I need to add some browser checking to the existing JavaScript that
> is
> > >> > generated for the toolbar. I was hoping to modify the existing
HTML
> > >> > 4.0
> > >> > rendering to include some more browser checking to replace some of
> the
> > >> > specific IE JavaScript properties.
> > >> >
> > >> > Any help would be greatly appreciated.
> > >> >
> > >> > Thanks,
> > >> >
> > >> >
> > >>
> > >>
> > >
> > >
> >
> >
>

create a Custom Delivery Protocol class and Auto-Refresh for SQL Grids displaying SQL Server Dat

How create a custom delivery Protocol class and show data in the Grid?

any advice and is good at having sample code?

I don't have it in front of me at the moment, but I almost positive that Shyam Pather's book show how to create a custom delivery protocol. (Even if it doesn't you'll want to get the book nonetheless; it's a great resource!)

HTH..

Joe

sql

create a Custom Delivery Protocol class and Auto-Refresh for SQL Grids displaying SQL Server Dat

How create a custom delivery Protocol class and show data in the Grid?

any advice and is good at having sample code?

I don't have it in front of me at the moment, but I almost positive that Shyam Pather's book show how to create a custom delivery protocol. (Even if it doesn't you'll want to get the book nonetheless; it's a great resource!)

HTH..

Joe

Crazy Row Numbering Poblem

I'm using ms sql 2000.
First here is a sample of my XML input:
- <scan ID="18.0" Section="System Restore">
- <scanattributes>
<scanattribute ID="18.0.0.0" ParentID="" Name="Description">Removed
ClientScanServiceSetup</scanattribute>
<scanattribute ID="18.0.0.0.0" ParentID="18.0.0.0" Name="Creation
Time">5/4/2006 11:42 AM</scanattribute>
<scanattribute ID="18.0.0.1" ParentID="" Name="Description">Installed
ClientScanServiceSetup</scanattribute>
<scanattribute ID="18.0.0.1.1" ParentID="18.0.0.1" Name="Creation
Time">5/4/2006 11:48 AM</scanattribute>
<scanattribute ID="18.0.0.2" ParentID="" Name="Description">Removed
ClientScanServiceSetup</scanattribute>
<scanattribute ID="18.0.0.2.2" ParentID="18.0.0.2" Name="Creation
Time">5/4/2006 12:05 PM</scanattribute>
<scanattribute ID="18.0.0.3" ParentID="" Name="Description">Installed
ClientScanServiceSetup</scanattribute>
<scanattribute ID="18.0.0.3.3" ParentID="18.0.0.3" Name="Creation
Time">5/4/2006 12:06 PM</scanattribute>
<scanattribute ID="18.0.0.4" ParentID="" Name="Description">Removed
ClientScanServiceSetup</scanattribute>
<scanattribute ID="18.0.0.4.4" ParentID="18.0.0.4" Name="Creation
Time">5/4/2006 12:15 PM</scanattribute>
<scanattribute ID="18.0.0.5" ParentID="" Name="Description">Installed
ClientScanServiceSetup</scanattribute>
<scanattribute ID="18.0.0.5.5" ParentID="18.0.0.5" Name="Creation
Time">5/4/2006 12:36 PM</scanattribute>
<scanattribute ID="18.0.0.6" ParentID="" Name="Description">Removed
ClientScanServiceSetup</scanattribute>
<scanattribute ID="18.0.0.6.6" ParentID="18.0.0.6" Name="Creation
Time">5/4/2006 12:57 PM</scanattribute>
<scanattribute ID="18.0.0.7" ParentID="" Name="Description">Installed
ClientScanServiceSetup</scanattribute>
<scanattribute ID="18.0.0.7.7" ParentID="18.0.0.7" Name="Creation
Time">5/4/2006 12:59 PM</scanattribute>
<scanattribute ID="18.0.0.8" ParentID="" Name="Description">Removed
ClientScanServiceSetup</scanattribute>
<scanattribute ID="18.0.0.8.8" ParentID="18.0.0.8" Name="Creation
Time">5/4/2006 1:04 PM</scanattribute>
<scanattribute ID="18.0.0.9" ParentID="" Name="Description">Installed
ClientScanServiceSetup</scanattribute>
<scanattribute ID="18.0.0.9.9" ParentID="18.0.0.9" Name="Creation
Time">5/4/2006 1:11 PM</scanattribute>
</scanattributes>
</scan>
Here is what I have so far:
declare @.iTree int
create table #temp (ID nvarchar(50), ParentID nvarchar(50), Name
nvarchar(50), scanattribute nvarchar(50))
create table #dup (attid nvarchar(50), name nvarchar (50), ID
nvarchar(50))
EXEC sp_xml_preparedocument @.iTree OUTPUT, @.doc
*/
INSERT INTO #temp
SELECT * FROM openxml(@.iTree,
'ComputerScan/scans/scan/scanattributes/scanattribute', 1)
WITH(
ID nvarchar(50) './@.ID',
ParentID nvarchar(50) './@.ParentID',
Name nvarchar(50) './@.Name',
scanattribute nvarchar(50) '.'
)
INSERT INTO #dup
SELECT ScanAttributeID, #temp.scanattribute, #temp.ID FROM
tblScanAttribute, #temp
WHERE #temp.ID like '18.%' AND tblScanAttribute.Name = #temp.Name AND
tblScanAttribute.ScanSectionID like '18'
INSERT INTO tblTest3(instance, attid, sectionid, name)
SELECT instance = (select count(*) from #dup where #dup.attid =
tblScanAttribute.ScanAttributeID
AND #dup.name<=#temp.scanattribute), tblScanAttribute.ScanAttributeID,
tblScanAttribute.ScanSectionID, #temp.scanattribute
FROM tblScanAttribute, #temp
WHERE #temp.ID like '18.%' AND tblScanAttribute.Name = #temp.Name AND
tblScanAttribute.ScanSectionID like '18'
The Results are as follows for tblTest3:
2 151 18 5/4/2006 1:11 PM
9 151 18 5/4/2006 12:57 PM
10 151 18 5/4/2006 12:59 PM
1 151 18 5/4/2006 1:04 PM
6 151 18 5/4/2006 12:06 PM
7 151 18 5/4/2006 12:15 PM
8 151 18 5/4/2006 12:36 PM
3 151 18 5/4/2006 11:42 AM
4 151 18 5/4/2006 11:48 AM
5 151 18 5/4/2006 12:05 PM
5 152 18 Installed ClientScanServiceSetup
10 152 18 Removed ClientScanServiceSetup
5 152 18 Installed ClientScanServiceSetup
10 152 18 Removed ClientScanServiceSetup
5 152 18 Installed ClientScanServiceSetup
10 152 18 Removed ClientScanServiceSetup
5 152 18 Installed ClientScanServiceSetup
10 152 18 Removed ClientScanServiceSetup
5 152 18 Installed ClientScanServiceSetup
10 152 18 Removed ClientScanServiceSetup
As you can see I am recording each repeating instace of the second
column, however when the fourth column has a repeating value the method
does not work, this is what I want it to look like:
2 151 18 5/4/2006 1:11 PM
9 151 18 5/4/2006 12:57 PM
10 151 18 5/4/2006 12:59 PM
1 151 18 5/4/2006 1:04 PM
6 151 18 5/4/2006 12:06 PM
7 151 18 5/4/2006 12:15 PM
8 151 18 5/4/2006 12:36 PM
3 151 18 5/4/2006 11:42 AM
4 151 18 5/4/2006 11:48 AM
5 151 18 5/4/2006 12:05 PM
1 152 18 Installed ClientScanServiceSetup
10 152 18 Removed ClientScanServiceSetup
9 152 18 Installed ClientScanServiceSetup
2 152 18 Removed ClientScanServiceSetup
8 152 18 Installed ClientScanServiceSetup
3 152 18 Removed ClientScanServiceSetup
5 152 18 Installed ClientScanServiceSetup
7 152 18 Removed ClientScanServiceSetup
6 152 18 Installed ClientScanServiceSetup
4 152 18 Removed ClientScanServiceSetup
IF anyone can help me out with this I would appreciate it greatly.I forgot to include the other tbl where I get the attid from. so here
it is.
151 18 Creation Time ND 0 5/9/2006 1:56:00 PM 5/9/2006 1:56:00 PM 0
152 18 Description ND 0 5/9/2006 1:56:00 PM 5/9/2006 1:56:00 PM 0|||It's not your main question, but I thought I'd point out that
the ordering of datetimes is probably not what you want:
1:04 PM
1:11 PM
11:42 AM
11:48 AM
...
If you have more than one year, it will get worse.
Steve Kass
Drew University
rhaazy wrote:

>I'm using ms sql 2000.
>First here is a sample of my XML input:
>- <scan ID="18.0" Section="System Restore">
>- <scanattributes>
> <scanattribute ID="18.0.0.0" ParentID="" Name="Description">Removed
>ClientScanServiceSetup</scanattribute>
> <scanattribute ID="18.0.0.0.0" ParentID="18.0.0.0" Name="Creation
>Time">5/4/2006 11:42 AM</scanattribute>
> <scanattribute ID="18.0.0.1" ParentID="" Name="Description">Installed
>ClientScanServiceSetup</scanattribute>
> <scanattribute ID="18.0.0.1.1" ParentID="18.0.0.1" Name="Creation
>Time">5/4/2006 11:48 AM</scanattribute>
> <scanattribute ID="18.0.0.2" ParentID="" Name="Description">Removed
>ClientScanServiceSetup</scanattribute>
> <scanattribute ID="18.0.0.2.2" ParentID="18.0.0.2" Name="Creation
>Time">5/4/2006 12:05 PM</scanattribute>
> <scanattribute ID="18.0.0.3" ParentID="" Name="Description">Installed
>ClientScanServiceSetup</scanattribute>
> <scanattribute ID="18.0.0.3.3" ParentID="18.0.0.3" Name="Creation
>Time">5/4/2006 12:06 PM</scanattribute>
> <scanattribute ID="18.0.0.4" ParentID="" Name="Description">Removed
>ClientScanServiceSetup</scanattribute>
> <scanattribute ID="18.0.0.4.4" ParentID="18.0.0.4" Name="Creation
>Time">5/4/2006 12:15 PM</scanattribute>
> <scanattribute ID="18.0.0.5" ParentID="" Name="Description">Installed
>ClientScanServiceSetup</scanattribute>
> <scanattribute ID="18.0.0.5.5" ParentID="18.0.0.5" Name="Creation
>Time">5/4/2006 12:36 PM</scanattribute>
> <scanattribute ID="18.0.0.6" ParentID="" Name="Description">Removed
>ClientScanServiceSetup</scanattribute>
> <scanattribute ID="18.0.0.6.6" ParentID="18.0.0.6" Name="Creation
>Time">5/4/2006 12:57 PM</scanattribute>
> <scanattribute ID="18.0.0.7" ParentID="" Name="Description">Installed
>ClientScanServiceSetup</scanattribute>
> <scanattribute ID="18.0.0.7.7" ParentID="18.0.0.7" Name="Creation
>Time">5/4/2006 12:59 PM</scanattribute>
> <scanattribute ID="18.0.0.8" ParentID="" Name="Description">Removed
>ClientScanServiceSetup</scanattribute>
> <scanattribute ID="18.0.0.8.8" ParentID="18.0.0.8" Name="Creation
>Time">5/4/2006 1:04 PM</scanattribute>
> <scanattribute ID="18.0.0.9" ParentID="" Name="Description">Installed
>ClientScanServiceSetup</scanattribute>
> <scanattribute ID="18.0.0.9.9" ParentID="18.0.0.9" Name="Creation
>Time">5/4/2006 1:11 PM</scanattribute>
> </scanattributes>
> </scan>
>Here is what I have so far:
>declare @.iTree int
>create table #temp (ID nvarchar(50), ParentID nvarchar(50), Name
>nvarchar(50), scanattribute nvarchar(50))
>create table #dup (attid nvarchar(50), name nvarchar (50), ID
>nvarchar(50))
>EXEC sp_xml_preparedocument @.iTree OUTPUT, @.doc
>*/
> INSERT INTO #temp
> SELECT * FROM openxml(@.iTree,
>'ComputerScan/scans/scan/scanattributes/scanattribute', 1)
> WITH(
> ID nvarchar(50) './@.ID',
> ParentID nvarchar(50) './@.ParentID',
> Name nvarchar(50) './@.Name',
> scanattribute nvarchar(50) '.'
> )
> INSERT INTO #dup
> SELECT ScanAttributeID, #temp.scanattribute, #temp.ID FROM
>tblScanAttribute, #temp
> WHERE #temp.ID like '18.%' AND tblScanAttribute.Name = #temp.Name AND
>tblScanAttribute.ScanSectionID like '18'
> INSERT INTO tblTest3(instance, attid, sectionid, name)
> SELECT instance = (select count(*) from #dup where #dup.attid =
>tblScanAttribute.ScanAttributeID
> AND #dup.name<=#temp.scanattribute), tblScanAttribute.ScanAttributeID,
>tblScanAttribute.ScanSectionID, #temp.scanattribute
> FROM tblScanAttribute, #temp
> WHERE #temp.ID like '18.%' AND tblScanAttribute.Name = #temp.Name AND
>tblScanAttribute.ScanSectionID like '18'
>The Results are as follows for tblTest3:
> 2 151 18 5/4/2006 1:11 PM
> 9 151 18 5/4/2006 12:57 PM
> 10 151 18 5/4/2006 12:59 PM
> 1 151 18 5/4/2006 1:04 PM
> 6 151 18 5/4/2006 12:06 PM
> 7 151 18 5/4/2006 12:15 PM
> 8 151 18 5/4/2006 12:36 PM
> 3 151 18 5/4/2006 11:42 AM
> 4 151 18 5/4/2006 11:48 AM
> 5 151 18 5/4/2006 12:05 PM
> 5 152 18 Installed ClientScanServiceSetup
> 10 152 18 Removed ClientScanServiceSetup
> 5 152 18 Installed ClientScanServiceSetup
> 10 152 18 Removed ClientScanServiceSetup
> 5 152 18 Installed ClientScanServiceSetup
> 10 152 18 Removed ClientScanServiceSetup
> 5 152 18 Installed ClientScanServiceSetup
> 10 152 18 Removed ClientScanServiceSetup
> 5 152 18 Installed ClientScanServiceSetup
> 10 152 18 Removed ClientScanServiceSetup
>As you can see I am recording each repeating instace of the second
>column, however when the fourth column has a repeating value the method
>does not work, this is what I want it to look like:
> 2 151 18 5/4/2006 1:11 PM
> 9 151 18 5/4/2006 12:57 PM
> 10 151 18 5/4/2006 12:59 PM
> 1 151 18 5/4/2006 1:04 PM
> 6 151 18 5/4/2006 12:06 PM
> 7 151 18 5/4/2006 12:15 PM
> 8 151 18 5/4/2006 12:36 PM
> 3 151 18 5/4/2006 11:42 AM
> 4 151 18 5/4/2006 11:48 AM
> 5 151 18 5/4/2006 12:05 PM
> 1 152 18 Installed ClientScanServiceSetup
> 10 152 18 Removed ClientScanServiceSetup
> 9 152 18 Installed ClientScanServiceSetup
> 2 152 18 Removed ClientScanServiceSetup
> 8 152 18 Installed ClientScanServiceSetup
> 3 152 18 Removed ClientScanServiceSetup
> 5 152 18 Installed ClientScanServiceSetup
> 7 152 18 Removed ClientScanServiceSetup
> 6 152 18 Installed ClientScanServiceSetup
> 4 152 18 Removed ClientScanServiceSetup
>IF anyone can help me out with this I would appreciate it greatly.
>
>|||rhaazy,
we need another column to break the ties. Try adding an identity column to
the temporary table #temp.
create table #temp (
[ID] nvarchar(50),
ParentID nvarchar(50),
[Name] nvarchar(50),
scanattribute nvarchar(50),
pk int not null identity unique -- new column
)
INSERT INTO #temp ([ID], ParentID, [Name], scanattribute)
SELECT * FROM openxml(...
...
INSERT INTO tblTest3(instance, attid, sectionid, name)
SELECT
instance = (
select count(*)
from #dup
where
#dup.attid = tblScanAttribute.ScanAttributeID
AND
(
#dup.[name] <= #temp.scanattribute
or
(#dup.[name] = #temp.scanattribute and #dup.puk < #temp)
)
),
tblScanAttribute.ScanAttributeID,
tblScanAttribute.ScanSectionID,
#temp.scanattribute
FROM
tblScanAttribute, #temp
WHERE
#temp.ID like '18.%'
AND tblScanAttribute.Name = #temp.Name
AND tblScanAttribute.ScanSectionID like '18'
AMB
"rhaazy" wrote:

> I forgot to include the other tbl where I get the attid from. so here
> it is.
> 151 18 Creation Time ND 0 5/9/2006 1:56:00 PM 5/9/2006 1:56:00 PM 0
> 152 18 Description ND 0 5/9/2006 1:56:00 PM 5/9/2006 1:56:00 PM 0
>|||NSERT INTO tblTest3(instance, attid, sectionid, name)
SELECT
instance = (
select count(*)
from #dup
where
#dup.attid = tblScanAttribute.ScanAttributeID
AND
(
#dup.[name] <= #temp.scanattribute
or
(#dup.[name] = #temp.scanattribute and #dup.puk <
#temp)
)
),
Does this imply I also need to add the pk column to the #dup table?|||You should be able to use your ID column to resolve duplicates.
Try changing
INSERT INTO tblTest3(instance, attid, sectionid, name)
SELECT instance = (select count(*) from #dup where #dup.attid =
tblScanAttribute.ScanAttributeID
AND #dup.name<=#temp.scanattribute),
tblScanAttribute.ScanAttributeID,
tblScanAttribute.ScanSectionID, #temp.scanattribute
FROM tblScanAttribute, #temp
WHERE #temp.ID like '18.%' AND tblScanAttribute.Name =
#temp.Name AND
tblScanAttribute.ScanSectionID like '18'
to
INSERT INTO tblTest3(instance, attid, sectionid, name)
SELECT instance = (select count(*) from #dup where #dup.attid =
tblScanAttribute.ScanAttributeID
AND ((#dup.name<#temp.scanattribute)
or (#dup.name=#temp.scanattribute) and
(#dup.ID<=#temp.ID))),
tblScanAttribute.ScanAttributeID,
tblScanAttribute.ScanSectionID,
#temp.scanattribute
FROM tblScanAttribute, #temp
WHERE #temp.ID like '18.%' AND tblScanAttribute.Name =
#temp.Name AND
tblScanAttribute.ScanSectionID like '18'|||rhaazy,
Sure, but here it will not have identity property. I will be populated from
#temp.
AMB
"rhaazy" wrote:

> NSERT INTO tblTest3(instance, attid, sectionid, name)
> SELECT
> instance = (
> select count(*)
> from #dup
> where
> #dup.attid = tblScanAttribute.ScanAttributeID
> AND
> (
> #dup.[name] <= #temp.scanattribute
> or
> (#dup.[name] = #temp.scanattribute and #dup.puk <
> #temp)
> )
> ),
> Does this imply I also need to add the pk column to the #dup table?
>|||I don't know who you are or where you come from but you are the
greatest person who ever lived... I have spent all w trying to
figure this out, I knew it needed an 'or' clause in there but I
couldn't quite get it right. I am an intern and I've only been
database programming for 2 ws and was pretty pleased I got as far as
I did. I just wanted to tell you how thankful I am for you helping me.
The fact you were able to make sense of all the crazy stuff I gave and
crank out exactly what I needed is truely amazing to me.
Thanks again.|||Good catch!!!
"markc600@.hotmail.com" wrote:

> You should be able to use your ID column to resolve duplicates.
> Try changing
> INSERT INTO tblTest3(instance, attid, sectionid, name)
> SELECT instance = (select count(*) from #dup where #dup.attid =
> tblScanAttribute.ScanAttributeID
> AND #dup.name<=#temp.scanattribute),
> tblScanAttribute.ScanAttributeID,
> tblScanAttribute.ScanSectionID, #temp.scanattribute
> FROM tblScanAttribute, #temp
> WHERE #temp.ID like '18.%' AND tblScanAttribute.Name =
> #temp.Name AND
> tblScanAttribute.ScanSectionID like '18'
>
> to
>
> INSERT INTO tblTest3(instance, attid, sectionid, name)
> SELECT instance = (select count(*) from #dup where #dup.attid =
> tblScanAttribute.ScanAttributeID
> AND ((#dup.name<#temp.scanattribute)
> or (#dup.name=#temp.scanattribute) and
> (#dup.ID<=#temp.ID))),
> tblScanAttribute.ScanAttributeID,
> tblScanAttribute.ScanSectionID,
> #temp.scanattribute
> FROM tblScanAttribute, #temp
> WHERE #temp.ID like '18.%' AND tblScanAttribute.Name =
> #temp.Name AND
> tblScanAttribute.ScanSectionID like '18'
>

Counting rows by a dynamic SQL statements

I need to count number of record return by a SQL statement. Any idea to do
this?
This is the sample SQL. You can try in Northwind database.
The subquery inside COUNT() expression actually is a dynamic sql which I may
change it anytime. My primary purpose is to get number of record after I
executed a dynamic SQL statement. The query below defintely cannot work
because COUNT() doesn;t take subquery.
At first, I try to use @.@.rowcount which return the number of record. Besides
using @.@.rowcount, any other T-SQL can be used?
DECLARE @.cnt int
SELECT @.cnt=COUNT(
SELECT Customers.ContactName
FROM Customers
INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID
INNER JOIN [Order Details] ON Orders.OrderID = [Order Details].OrderID
WHERE Customers.Country <> 'USA' AND Customers.Country <> 'Mexico'
GROUP BY Customers.CustomerID, Customers.ContactName, Customers.Address,
Customers.City, Customers.Country
HAVING (SUM([Order Details].UnitPrice*[Order Details].Quantity))>1000
)You can specify your SQL query as a derived table and use sp_executesql to
return the count variable as an output parameter. This will return the
count without the accompanying data:
USE Northwind
DECLARE @.cnt int
EXEC sp_executesql
N'
SELECT @.cnt = COUNT(*)
FROM (
SELECT Customers.ContactName
FROM Customers
INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID
INNER JOIN [Order Details] ON Orders.OrderID = [Order Details].OrderID
WHERE Customers.Country <> ''USA'' AND Customers.Country <> ''Mexico''
GROUP BY Customers.CustomerID, Customers.ContactName, Customers.Address,
Customers.City, Customers.Country
HAVING (SUM([Order Details].UnitPrice*[Order Details].Quantity))>1000
) AS t
',
N'@.cnt int OUT',
@.cnt OUT
SELECT @.cnt
Use a similar technique to get both the resultset and count:
USE Northwind
DECLARE @.cnt int
EXEC sp_executesql
N'
SELECT Customers.ContactName
FROM Customers
INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID
INNER JOIN [Order Details] ON Orders.OrderID = [Order Details].OrderID
WHERE Customers.Country <> ''USA'' AND Customers.Country <> ''Mexico''
GROUP BY Customers.CustomerID, Customers.ContactName, Customers.Address,
Customers.City, Customers.Country
HAVING (SUM([Order Details].UnitPrice*[Order Details].Quantity))>1000
SET @.cnt = @.@.ROWCOUNT
',
N'@.cnt int OUT',
@.cnt OUT
SELECT @.cnt
Hope this helps.
Dan Guzman
SQL Server MVP
"Joel Leong" <ch_leong@.hotmail.com> wrote in message
news:ehYf%23NOHFHA.3944@.TK2MSFTNGP10.phx.gbl...
>I need to count number of record return by a SQL statement. Any idea to do
>this?
> This is the sample SQL. You can try in Northwind database.
> The subquery inside COUNT() expression actually is a dynamic sql which I
> may change it anytime. My primary purpose is to get number of record after
> I executed a dynamic SQL statement. The query below defintely cannot work
> because COUNT() doesn;t take subquery.
> At first, I try to use @.@.rowcount which return the number of record.
> Besides using @.@.rowcount, any other T-SQL can be used?
>
> DECLARE @.cnt int
> SELECT @.cnt=COUNT(
> SELECT Customers.ContactName
> FROM Customers
> INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID
> INNER JOIN [Order Details] ON Orders.OrderID = [Order Details].OrderID
> WHERE Customers.Country <> 'USA' AND Customers.Country <> 'Mexico'
> GROUP BY Customers.CustomerID, Customers.ContactName, Customers.Address,
> Customers.City, Customers.Country
> HAVING (SUM([Order Details].UnitPrice*[Order Details].Quantity))>1000
> )
>

Counting issue in table

I have a table with the following data (sample)

Type Year Price
-- -- --
A 00 100.00
A 00 200.00
A 01 105.00
A 01 105.00-
B 00 100.00
B 00 200.00
B 01 105.00
B 02 00.00

I need to establish a Type Count. The business rule to do the count is -

For a particular type, take a single year and add up the price. If the price is greater than zero then count = 1 else count = 0. After completing the counts for all the years for a particular type, add up the counts.

Based on the business rule for the above sample data I should have the following count.

Count -
Type A = 1 (For Year 00, price = 300.00 so count = 1 and for year 01, price = .00 (105 + 105.00 - ) and so count = 0. add counts to get a total of 1)

Type B = 2 (For Year 00, price = 300.00 so count = 1 and for year 01, price = 105 and so count = 1 and for year 02 price = 00 so count = 0. add counts to get a total of 2)

How do I implement this count using a query. I am free to add any indicator columns that may be required to perform the counting.

Let me know.

Thanks

VivekUSE Northwind
GO

CREATE TABLE myTable99(Type char(1), [Year] char(2), Price money)
GO

INSERT INTO myTable99(Type, [Year], Price)
SELECT 'A', '00', 100.00 UNION ALL
SELECT 'A', '00', 200.00 UNION ALL
SELECT 'A', '01', 105.00 UNION ALL
SELECT 'A', '01', -105.00 UNION ALL
SELECT 'B', '00', 100.00 UNION ALL
SELECT 'B', '00', 200.00 UNION ALL
SELECT 'B', '01', 105.00 UNION ALL
SELECT 'B', '02', 00.00
GO

SELECT Type
, [Year]
, SUM(Price) AS SUM_Price
, CASE WHEN SUM(Price) > 0 THEN 1 ELSE 0 END AS COUNT_Price
FROM myTable99
GROUP BY Type, [Year]
GO

DROP TABLE myTable99
GO|||Thanks Brett. That helps.|||Can I play too? I added a snippet that I think answers the original questionCREATE TABLE myTable99(Type char(1), [Year] char(2), Price money)
GO

INSERT INTO myTable99(Type, [Year], Price)
SELECT 'A', '00', 100.00 UNION ALL
SELECT 'A', '00', 200.00 UNION ALL
SELECT 'A', '01', 105.00 UNION ALL
SELECT 'A', '01', -105.00 UNION ALL
SELECT 'B', '00', 100.00 UNION ALL
SELECT 'B', '00', 200.00 UNION ALL
SELECT 'B', '01', 105.00 UNION ALL
SELECT 'B', '02', 00.00
GO

SELECT Type
, [Year]
, SUM(Price) AS SUM_Price
, CASE WHEN SUM(Price) > 0 THEN 1 ELSE 0 END AS COUNT_Price
FROM myTable99
GROUP BY Type, [Year]

SELECT type, Sum(price_count)
FROM (SELECT type, [year]
, CASE WHEN 0 < Sum(price) THEN 1 ELSE 0 END AS price_count
FROM myTable99
GROUP BY type, [year]) AS a
GROUP BY type

GO

DROP TABLE myTable99
GO-PatP