Sql Activity log backup: custom

Showing posts with label custom. Show all posts

Thursday, March 29, 2012

adjust colours of bars in bar charts at run time

Hi,
does anyone know if it is possible to adjust the colours of the bars in
bar charts at run time using custom assemblies or functions? I have
clicked on all the options I can find and I can see no expression
capability for the colours of the bars, only a selection of color
schemes...is this just not an option in 2000?
Thanks
Peter
www.peternolan.comYou will need to have at least SP1 of Reporting Services 2000 installed (on
the server and on the report designer). Please check the SP1/SP2 Readme for
more details:
http://download.microsoft.com/download/5/1/3/513534ae-a0e7-44e6-9a04-ba3c549a5f5f/sp2Readme_EN.htm#_chart_enhancements
You can also search the archives of this newsgroup and you should find many
related threads about setting datapoint colors in charts.
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Peter Nolan" <peter@.peternolan.com> wrote in message
news:1127599073.728978.199980@.g44g2000cwa.googlegroups.com...
> Hi,
> does anyone know if it is possible to adjust the colours of the bars in
> bar charts at run time using custom assemblies or functions? I have
> clicked on all the options I can find and I can see no expression
> capability for the colours of the bars, only a selection of color
> schemes...is this just not an option in 2000?
> Thanks
> Peter
> www.peternolan.com
>|||Hi Robert,
I just installed RS...SP2.....yes, I understand I can change the
colour of the data points printed on the chart at run time.....but I
cannot see any way to change the colour of the bar itself....it only
appears that I can change the style of the report between things like
'excel', 'earthy', 'transparent' etc...and the colours of the bars are
rendered according to these definitions for all bars in the
series.....
No big deal as I will probably use another charting package on
2005...but I was wondering if I can do this in RS charts...
Thanks
Peter|||Peter,
I've done this in the following way:
In Chart Properties, Data, Values, Edit, Appearance, Series Style I use this
function:
= Code.GetColor(Fields!RightsHolder.Value)
And in the code for the report I have:
Public Function GetColor(ByVal RightsHolder As String) As String
if RightsHolder = "EMI" Then
Return "#CC3333" ' "Red"
end if
if RightsHolder = "EMI RW" Then
Return "Pink"
end if
if RightsHolder = "WMG" Then
Return "#3300CC" ' "Blue"
end if
if (RightsHolder <> "WMG") and (RightsHolder <> "EMI") and (RightsHolder
<> "EMI RW") Then
Return "Black"
end if
End Function
HTH,
Paul Ibison, SQL Server MVP|||Paul,
thanks for that...I will give it a go.
Peter|||You cannot define your own custom palettes directly. The closest you can get
is to use the approach discussed in the following blog posting (it also
provides a sample for download):
http://blogs.msdn.com/bwelcker/archive/2005/05/20/420349.aspx
The sample defines its own "color palette" by always overriding the colors
that come from the selected color palette.
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Peter Nolan" <peter@.peternolan.com> wrote in message
news:1127656949.717291.17150@.z14g2000cwz.googlegroups.com...
> Hi Robert,
> I just installed RS...SP2.....yes, I understand I can change the
> colour of the data points printed on the chart at run time.....but I
> cannot see any way to change the colour of the bar itself....it only
> appears that I can change the style of the report between things like
> 'excel', 'earthy', 'transparent' etc...and the colours of the bars are
> rendered according to these definitions for all bars in the
> series.....
> No big deal as I will probably use another charting package on
> 2005...but I was wondering if I can do this in RS charts...
> Thanks
> Peter
>|||Robert,
apologies for not referencing/crediting your great blog example - knew I got
my code template from somewhere but couldn't remember where :)
Cheers,
Paul Ibison, SQL Server MVP|||That's fine. I'm glad people find my samples, tips, answers, etc. useful and
it helps them solving their issues.
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:u3iZoK2wFHA.2728@.TK2MSFTNGP14.phx.gbl...
> Robert,
> apologies for not referencing/crediting your great blog example - knew I
> got my code template from somewhere but couldn't remember where :)
> Cheers,
> Paul Ibison, SQL Server MVP
>|||Paul,
you are brilliant!!! Thank You!!!!
When I read this I thought it related to the numbers that were printed
on the chart if they were printed...but when I tested I found it was
the bar and line colour that it was referring to...now I can pass data
from the row to a member of a custom assembly and get it to generate
the bars on the chart to be the colours I want!!
Thank you very much for this...this is a feature I really wanted on my
charts....
Best Regards
Peter
www.peternolan.com|||Hello,
I read the blog and for HTML and PDF it works great. However when i export a
report to excel, excel reinterprets the colors to what it can handle.
In my case i made a colorpalette of only oranges and browns. When i export
it to excel, the graph itself is exported ok. Only the colors i used in the
legend table are changed. eg. GoldenRod, Orange and DarkOrange are all
reverted to the same color.
Is there a way to have the table also exported as a picture or something to
get the right color across?
"Robert Bruckner [MSFT]" wrote:
> You cannot define your own custom palettes directly. The closest you can get
> is to use the approach discussed in the following blog posting (it also
> provides a sample for download):
> http://blogs.msdn.com/bwelcker/archive/2005/05/20/420349.aspx
> The sample defines its own "color palette" by always overriding the colors
> that come from the selected color palette.
>|||Hi,
I don't seem to have the 'Series Style" section showing on my 'Appearance'
tab.
Have installed SP2 for RS 2000 on the server, but surely this is a "client"
issue with
SQL Server Report Designer. I'm using Ver 8.00.743
Any ideas?
"Paul Ibison" wrote:
> Peter,
> I've done this in the following way:
> In Chart Properties, Data, Values, Edit, Appearance, Series Style I use this
> function:
> = Code.GetColor(Fields!RightsHolder.Value)
> And in the code for the report I have:
> Public Function GetColor(ByVal RightsHolder As String) As String
> if RightsHolder = "EMI" Then
> Return "#CC3333" ' "Red"
> end if
> if RightsHolder = "EMI RW" Then
> Return "Pink"
> end if
> if RightsHolder = "WMG" Then
> Return "#3300CC" ' "Blue"
> end if
> if (RightsHolder <> "WMG") and (RightsHolder <> "EMI") and (RightsHolder
> <> "EMI RW") Then
> Return "Black"
> end if
> End Function
> HTH,
> Paul Ibison, SQL Server MVP
>
>

Tuesday, March 27, 2012

ad-hoc query printing

Based on the tutorial in
http://msdn2.microsoft.com/en-us/library/ms170667.aspx
We have a prototype that works with our custom data extension in Reporting
Services 2005 such that we can generate a report based on a given query text.
Right now in order to view a report, we always publish the generated report
to a report server instance. Our question is whether we can do it without
using report server.
For example, does ReportViewer control (the local processing mode) help?
Our goal is to create a temporary report on the fly given a query such that
the user can print or export the report, which means that this temporary
report will be discarded as soon as the user is done with it. We would like
to know if this is possible.
Thanks in advance!Hi,
Welcome to use MSDN Managed Newsgroup!
From your descriptions, I understood you would like to whether it is
possible to create a temporary report for print only. If I have
misunderstood your concern, please feel free to point it out.
Unfortunately, Reporting Services do not have such funtionality to do so. I
wonder why do you need a Report on fly?
I have an interesting experience before that might give you some ideas. The
data source in my project was a remote linked server that need a lot of
time executing the complex query (cost about 5 minutes each time). When
perform the Email-Delivery Subscription to 10 or more different users, the
query will be executed 10 or more 5-minutes (around for 1 hours to complete
the email delivery subscription), which is obviously unacceptedable. Hence,
I will use DTS to transfer the data from linked server to local server
(cost 5 minutes) and then do the email-delivery subscription on local
tables, which is pretty cheap.
Hope this helps.
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi,
Oops, sorry that I think I missed something
Here is an interesting article that shows VS 2005's new ReportViewer
control and its built-in Report Designer enable smart-client and Web page
designers to lay out, format, embed, export, and print interactive reports
without running a SQL Server Report Server.
Build Client-Side Reports Easily
http://msforums.ph/forums/91285/ShowPost.aspx
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Thanks for your help. I found this walkthrough sample (similar to the one
you quoted below) from MSDN2 as well
http://msdn2.microsoft.com/en-us/library/ms251724.aspx
This is useful tutorial for us. However, I need to generate such report on
the fly ... that is, the dataset and datatable etc will be generated at
run-time. Is this even possible?
In addition, is there any tutorial on generating RDLC programmatically?
Thanks
Jenny
"Michael Cheng [MSFT]" wrote:
> Hi,
> Oops, sorry that I think I missed something
> Here is an interesting article that shows VS 2005's new ReportViewer
> control and its built-in Report Designer enable smart-client and Web page
> designers to lay out, format, embed, export, and print interactive reports
> without running a SQL Server Report Server.
> Build Client-Side Reports Easily
> http://msforums.ph/forums/91285/ShowPost.aspx
>
> Sincerely yours,
> Michael Cheng
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> =====================================================> This posting is provided "AS IS" with no warranties, and confers no rights.
>
>|||Actually, I just found out about the LocalReport class from
http://msdn2.microsoft.com/en-us/library/microsoft.reporting.winforms.localreport.aspx
Do you know of any example of how to use it? Say I already have a dataset
or a datatable or a collection of business objects (can't seem to find more
about what exactly a business object is).
Thanks in advance!
"yinjennytam@.newsgroup.nospam" wrote:
> Thanks for your help. I found this walkthrough sample (similar to the one
> you quoted below) from MSDN2 as well
> http://msdn2.microsoft.com/en-us/library/ms251724.aspx
> This is useful tutorial for us. However, I need to generate such report on
> the fly ... that is, the dataset and datatable etc will be generated at
> run-time. Is this even possible?
> In addition, is there any tutorial on generating RDLC programmatically?
> Thanks
> Jenny
>
>
> "Michael Cheng [MSFT]" wrote:
> > Hi,
> >
> > Oops, sorry that I think I missed something
> >
> > Here is an interesting article that shows VS 2005's new ReportViewer
> > control and its built-in Report Designer enable smart-client and Web page
> > designers to lay out, format, embed, export, and print interactive reports
> > without running a SQL Server Report Server.
> >
> > Build Client-Side Reports Easily
> > http://msforums.ph/forums/91285/ShowPost.aspx
> >
> >
> >
> > Sincerely yours,
> >
> > Michael Cheng
> > Microsoft Online Partner Support
> >
> > When responding to posts, please "Reply to Group" via your newsreader so
> > that others may learn and benefit from your issue.
> > =====================================================> > This posting is provided "AS IS" with no warranties, and confers no rights.
> >
> >
> >|||Hi,
To create a RDLC file, you may refer the article below
Creating Client Report Definition (.rdlc) Files
http://msdn2.microsoft.com/en-us/library/ms252067(en-US,VS.80).aspx
Converting RDL and RDLC Files
http://msdn2.microsoft.com/en-us/library/ms252109(en-US,VS.80).aspx
If you encounter any difficulties in the walkthrough, please let me know
the detail step you meet the error.
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Thanks Michael for the links. I did take a look at both and did try some
walkthrough tutorials. They are great and now I understand more about
ReportViewer controls in general.
However, my problem is still not resolved. I guess I can rephrase it a bit
to let you understand it more:
(1) I need to generate the RDLC file programmatically because I don't know
the report fields at design time
(2) The same applies to the report data source, and I have a way to run a
query to get a System.Data.DataTable object which I can use as the report
data source, but again this depends on the search query, which I don't know
until run-time
I have attempted to modify the walkthrough from
http://msdn2.microsoft.com/en-us/library/ms251784(en-US,VS.80).aspx
But instead of creating the rdlc file using the UI, I created the RDLC file
based on the tutorial from
http://msdn2.microsoft.com/en-us/library/ms170667(en-US,SQL.90).aspx
Again, I just renamed this file using the *.rdlc extension instead of *.rdl
and save it somewhere in my local system.
As I mentioned above, given a query, I can create a System.Data.DataTable on
the fly and modified the sample code such that I commented out the followings
from Form1.Designer.cs:
//this.reportViewer1.LocalReport.DataSources.Add(reportDataSource1);
//this.reportViewer1.LocalReport.ReportEmbeddedResource ="BusinessObject.Report1.rdlc";
and added the following lines to Form1_Load() just before the line
this.reportViewer1.RefreshReport();
this.reportViewer1.LocalReport.DataSources.Add(new
ReportDataSource("Sales", datatable));
this.reportViewer1.LocalReport.ReportPath =@."C:\Development\trials\BusinessObject\BusinessObject\mysales.rdlc";
When I ran the app, I got the following error:
"A data source instance has not been supplied for the data source 'DataSet1'"
First of all, am I in the wrong track? Can I actually programmatically
create an RDL file and simply renamed it as RDLC. What else do I need to do
programmatically (not through adding it to the project as indicated by the
walkthrough)?
Second of all, what am I missing? Is it possible to supply the DataTable
(report data source) programmatically?
Thanks a lot for reading my long post.
Jenny
"Michael Cheng [MSFT]" wrote:
> Hi,
> To create a RDLC file, you may refer the article below
> Creating Client Report Definition (.rdlc) Files
> http://msdn2.microsoft.com/en-us/library/ms252067(en-US,VS.80).aspx
> Converting RDL and RDLC Files
> http://msdn2.microsoft.com/en-us/library/ms252109(en-US,VS.80).aspx
> If you encounter any difficulties in the walkthrough, please let me know
> the detail step you meet the error.
>
> Sincerely yours,
> Michael Cheng
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> =====================================================> This posting is provided "AS IS" with no warranties, and confers no rights.
>|||Actually I've figured it out myself! :-)
When I added a new ReportDataSource object, I should have named it
'DataSet1' to associate that with my RDLC file.
Jenny
"yinjennytam@.newsgroup.nospam" wrote:
> Thanks Michael for the links. I did take a look at both and did try some
> walkthrough tutorials. They are great and now I understand more about
> ReportViewer controls in general.
> However, my problem is still not resolved. I guess I can rephrase it a bit
> to let you understand it more:
> (1) I need to generate the RDLC file programmatically because I don't know
> the report fields at design time
> (2) The same applies to the report data source, and I have a way to run a
> query to get a System.Data.DataTable object which I can use as the report
> data source, but again this depends on the search query, which I don't know
> until run-time
> I have attempted to modify the walkthrough from
> http://msdn2.microsoft.com/en-us/library/ms251784(en-US,VS.80).aspx
> But instead of creating the rdlc file using the UI, I created the RDLC file
> based on the tutorial from
> http://msdn2.microsoft.com/en-us/library/ms170667(en-US,SQL.90).aspx
> Again, I just renamed this file using the *.rdlc extension instead of *.rdl
> and save it somewhere in my local system.
> As I mentioned above, given a query, I can create a System.Data.DataTable on
> the fly and modified the sample code such that I commented out the followings
> from Form1.Designer.cs:
> //this.reportViewer1.LocalReport.DataSources.Add(reportDataSource1);
> //this.reportViewer1.LocalReport.ReportEmbeddedResource => "BusinessObject.Report1.rdlc";
> and added the following lines to Form1_Load() just before the line
> this.reportViewer1.RefreshReport();
> this.reportViewer1.LocalReport.DataSources.Add(new
> ReportDataSource("Sales", datatable));
> this.reportViewer1.LocalReport.ReportPath => @."C:\Development\trials\BusinessObject\BusinessObject\mysales.rdlc";
>
> When I ran the app, I got the following error:
> "A data source instance has not been supplied for the data source 'DataSet1'"
>
> First of all, am I in the wrong track? Can I actually programmatically
> create an RDL file and simply renamed it as RDLC. What else do I need to do
> programmatically (not through adding it to the project as indicated by the
> walkthrough)?
> Second of all, what am I missing? Is it possible to supply the DataTable
> (report data source) programmatically?
>
> Thanks a lot for reading my long post.
> Jenny
>
>
>
> "Michael Cheng [MSFT]" wrote:
> > Hi,
> >
> > To create a RDLC file, you may refer the article below
> >
> > Creating Client Report Definition (.rdlc) Files
> > http://msdn2.microsoft.com/en-us/library/ms252067(en-US,VS.80).aspx
> >
> > Converting RDL and RDLC Files
> > http://msdn2.microsoft.com/en-us/library/ms252109(en-US,VS.80).aspx
> >
> > If you encounter any difficulties in the walkthrough, please let me know
> > the detail step you meet the error.
> >
> >
> > Sincerely yours,
> >
> > Michael Cheng
> > Microsoft Online Partner Support
> >
> > When responding to posts, please "Reply to Group" via your newsreader so
> > that others may learn and benefit from your issue.
> > =====================================================> > This posting is provided "AS IS" with no warranties, and confers no rights.
> >
> >|||Hi Jenny,
Thanks for your prompt update and it's great to hear you have resovled it
now!! And thanks so much for sharing this information with us.
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================This posting is provided "AS IS" with no warranties, and confers no rights.

Sunday, March 25, 2012

Adding/Previewing custom table style

Hi,

I need to add and preview custom table styles. I was able to add custom style by editing the StyleTemplates.xml file under "C:\Program Files\Microsoft Visual Studio 8\Common7\IDE\PrivateAssemblies\Business Intelligence Wizards\Reports\Styles' as per one of the postings here.

Now, how should I add the ability for the user to preview this custom style. I can write a form similar to MS "Choose table style" proobably. Is there a way to launch a particular page of the report wizard programatically?

Thanks,

-Surendra

I have the same problem, Have you find the solution?

Adding/Previewing custom table style

Hi,

Thanks,

-Surendra

I have the same problem, Have you find the solution?

Sunday, March 11, 2012

Adding Reference to App_Code of current website

I know it's possible to add a reference to a report to a custom class, but I was wondering if it was possible to refer to the code in the App_Code directory of the current website?

We have a lot of built in functionality that I could take advantage of, if I can make a call to the classes in the current site. If I have to create a new class, there's a lot of things that would have to be duplicated to get it to work and we definately don't want to have to duplicate efforts.

TIA

Well, you can reference custom assemblies from within your reports (it could be private or signed ones)

herehttp://msdn2.microsoft.com/en-us/library/aa179513(SQL.80).aspx you gonna find all you need to do so.

Hope this helps

Adding Radio button in SQL Reporting services? Is there a work ar

Hey Peeps,
Is there anyway I could get a radio box added to my report either by adding
the radio button control to the tool box or adding a custom DLL with radio
button functionality. My objective is to simulate a "Jump to report" inbuilt
functionality using radio buttons. Like when the end user clicks on either of
the two radio buttons, he should see another report with parameters passed
into the second one. Any quick help is heartfully appreciatedI know some people have done something similar using windings font. I just
have a textbox with a phrase and make it blue and underlined.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Balaji" <Balaji@.discussions.microsoft.com> wrote in message
news:D65FA0B1-7845-43E6-A3C0-5B520ADF6BFF@.microsoft.com...
> Hey Peeps,
> Is there anyway I could get a radio box added to my report either by
> adding
> the radio button control to the tool box or adding a custom DLL with radio
> button functionality. My objective is to simulate a "Jump to report"
> inbuilt
> functionality using radio buttons. Like when the end user clicks on either
> of
> the two radio buttons, he should see another report with parameters passed
> into the second one. Any quick help is heartfully appreciated
>|||Hey Bruce,
Thanks mate, for the quick reply. But if I were to use a text box (shrinked
to a very small size) then does it function like radio button. Like in the
sense, does it toggle on and off on simultaneous clicks'
"Bruce L-C [MVP]" wrote:
> I know some people have done something similar using windings font. I just
> have a textbox with a phrase and make it blue and underlined.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Balaji" <Balaji@.discussions.microsoft.com> wrote in message
> news:D65FA0B1-7845-43E6-A3C0-5B520ADF6BFF@.microsoft.com...
> > Hey Peeps,
> >
> > Is there anyway I could get a radio box added to my report either by
> > adding
> > the radio button control to the tool box or adding a custom DLL with radio
> > button functionality. My objective is to simulate a "Jump to report"
> > inbuilt
> > functionality using radio buttons. Like when the end user clicks on either
> > of
> > the two radio buttons, he should see another report with parameters passed
> > into the second one. Any quick help is heartfully appreciated
> >
>
>|||No, it does not.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Balaji" <Balaji@.discussions.microsoft.com> wrote in message
news:352D0DFE-4CF1-4E31-B8D8-8D9E88A54753@.microsoft.com...
> Hey Bruce,
> Thanks mate, for the quick reply. But if I were to use a text box
(shrinked
> to a very small size) then does it function like radio button. Like in the
> sense, does it toggle on and off on simultaneous clicks'
> "Bruce L-C [MVP]" wrote:
> > I know some people have done something similar using windings font. I
just
> > have a textbox with a phrase and make it blue and underlined.
> >
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> > "Balaji" <Balaji@.discussions.microsoft.com> wrote in message
> > news:D65FA0B1-7845-43E6-A3C0-5B520ADF6BFF@.microsoft.com...
> > > Hey Peeps,
> > >
> > > Is there anyway I could get a radio box added to my report either by
> > > adding
> > > the radio button control to the tool box or adding a custom DLL with
radio
> > > button functionality. My objective is to simulate a "Jump to report"
> > > inbuilt
> > > functionality using radio buttons. Like when the end user clicks on
either
> > > of
> > > the two radio buttons, he should see another report with parameters
passed
> > > into the second one. Any quick help is heartfully appreciated
> > >
> >
> >
> >|||Hi,
This article could help you:
http://www.sqljunkies.com/Article/?type=2&cat=32
success pakam
"Balaji" wrote:
> Hey Peeps,
> Is there anyway I could get a radio box added to my report either by adding
> the radio button control to the tool box or adding a custom DLL with radio
> button functionality. My objective is to simulate a "Jump to report" inbuilt
> functionality using radio buttons. Like when the end user clicks on either of
> the two radio buttons, he should see another report with parameters passed
> into the second one. Any quick help is heartfully appreciated
>

Thursday, March 8, 2012

Adding new web.config custom section

Hello All,
I want to add custom cofiguration section to the report server web.config
and use this configuration from the RDL, can it be done? and if the answer is
yes, how it can be done.
Thank youOn Oct 7, 1:21 pm, Shlomi <Shl...@.discussions.microsoft.com> wrote:
> Hello All,
> I want to add custom cofiguration section to the report server web.config
> and use this configuration from the RDL, can it be done? and if the answer is
> yes, how it can be done.
> Thank you
You might be able to reference the Web config file from custom code in
the report (via: Layout view >> Report drop-down >> Report
Properties... >> Code tab). You might be able to reference it via a
call to Configurations.AppSettings(...). Of course this is a long
shot. Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant|||thank you,
i will be thankful if you can give me an example f custom configuration
section for the web.config
"EMartinez" wrote:
> On Oct 7, 1:21 pm, Shlomi <Shl...@.discussions.microsoft.com> wrote:
> > Hello All,
> >
> > I want to add custom cofiguration section to the report server web.config
> > and use this configuration from the RDL, can it be done? and if the answer is
> > yes, how it can be done.
> >
> > Thank you
>
> You might be able to reference the Web config file from custom code in
> the report (via: Layout view >> Report drop-down >> Report
> Properties... >> Code tab). You might be able to reference it via a
> call to Configurations.AppSettings(...). Of course this is a long
> shot. Hope this helps.
> Regards,
> Enrique Martinez
> Sr. Software Consultant
>

Saturday, February 25, 2012

Adding logging to a Data Extension

Hello,
I am attempting to troubleshoot why my custom Data Extension is
failing. To do so, I'd like to add some (log4net) logging to the code
to see what is being accessed and what is going wrong.
Do I need to add special permissions for the extra log4net assembly to
run? The documentation seems very sparse on how to debug these
extensions.
I'm able to attach the VS process and step through the code, but that
isn't really helping - Report Designer only shows a generic "The query
could not be loaded, Verify your connection string" error. My extension
(for testing purposes) isn't making use of either string.A followup question..
If I want to specify appSettings for my Data Processing Extension, what
should the name of the config file be?
I've tried assemblyname.config; but I'm going to guess that isn't
working because the Extension code is being run through another
assembly...

Adding line sequence numbers

hi all

I am stuck with something that seems easy but im obviously clueless as how to execute the idea.

I have a custom table that houses invoices on the details level. So for example i have:

InvcNo
00000001
00000001
00000001
00000002
00000002
00000003

and so forth

What I am wanting to do in another column is keep track of the sequence number for each distinct invoice like:

SeqNo
1
2
3
1
2
1

I am working in a stored proc and i cant get past adding the numbers up at each line as a whole and not reseting when the next invoice number is present. Any help would be so greatly appreciated.

ThanksWill this help?
http://www.sqlservercentral.com/columnists/mcoles/sequentialordering.asp|||Thanks...but I dont have 2005 (

Is it possible to pull the example that is given in 2005 section in a prior version of SQL?|||Can you uniquely identify each record for a specific InvcNo?|||Yes, I have an InvcKey column set up|||create table #t1 (InvcKey int, InvcNo int)
insert into #t1 select
1,1 union all select
2,1 union all select
3,1 union all select
4,2 union all select
5,2 union all select
6,3

select count(*) 'SeqNo', a.InvcKey, a.InvcNo from #t1 a, #t1 b
where a.InvcNo=b.InvcNo
and a.InvcKey>=b.InvcKey
group by a.InvcKey, a.InvcNo

drop table #t1|||Ok, Ive got it...I should have played around some before asking further

SELECT COUNT(*) AS [SeqNo], c.InvcNo, c.InvcKey
FROM table c, table d
WHERE d.InvcKey <= c.InvcKey AND d.InvcNo = cInvcNo
GROUP BY c.InvcNo, c.InvcKey
ORDER BY c.InvcKey

Thanks for the help!

tibor|||You should probably use a JOIN.
Using

FROM table1, table2, ... , tableN

Is not advised.|||Being a self join, would I really need JOIN?|||Being a self join, would I really need JOIN?It is semantics. The affect it the same but the style you used is considered old fashioned in many quarters. Whether or not it is a self join is irrelevent.

Rather excitingly - you will have created a Theta Join when you have finished :)|||Rather excitingly - you will have created a Theta Join when you have finished :)

neat! normally you'd have to pay $360k to get to that level (http://en.wikipedia.org/wiki/Operating_Thetan)!

Thursday, February 16, 2012

Adding custom property to custom component

What I want to accomplish is that at design time the designer can enter a value for some custom property on my custom task and that this value is accessed at executing time.

I thought this would involve adding a IDTSCustomProperty to the ComponentMetaData.CustomPropertyCollection and the right place to do this to mee seemed be in the ProvideComponentProperties() method:

IDTSCustomProperty90 property = ComponentMetaData.CustomPropertyCollection.New();
property.Name = "MyLittleProperty";

However, after compiling and redeploying the custom component and adding it to a package, the property does not show up in the editor.

Would anyone have suggestions on how what I want to do should be done right?

Thnx in advance,
Henk

Well, luckily it works after all. Closing down VS and restarting it did the trick. Probably kept reference to an old version of my custom component...|||You will need to restart VS everytime you recompile a component or task. You can set your project as the startup command for debugging the component, but it seems to take ages to load.

When testing the component execution side, set DTExec and a ready buildt package as the debug, as this is much faster.|||We've added a few tidbits that you might find interesting to the BOL topic on the subject of Custom Properties since the last CTP.

Creating Custom Properties

The call to the ProvideComponentProperties method is where component developers should add custom properties (IDTSCustomProperty90) to the component.

You can indicate that your custom property supports property expressions by setting the value of its ExpressionType property to CPET_NOTIFY from the DTSCustomPropertyExpressionType enumeration, as shown in the following example:

C# Copy Code IDTSCustomProperty90 myCustomProperty; ... myCustomProperty.ExpressionType = DTSCustomPropertyExpressionType.CPET_NOTIFY;

Visual Basic Copy Code Dim myCustomProperty As IDTSCustomProperty90 ... myCustomProperty.ExpressionType = DTSCustomPropertyExpressionType.CPET_NOTIFY

You can limit users to selecting a custom property value from an enumeration by using the TypeConverter property, as shown in the following example, which assumes that you have defined a public enumeration named MyValidValues:

C# Copy Code IDTSCustomProperty90 customProperty = outputColumn.CustomPropertyCollection.New(); customProperty.Name = "My Custom Property"; // This line associates the type with the custom property. customProperty.TypeConverter = typeof(MyValidValues).AssemblyQualifiedName; // Now you can use the enumeration values directly. customProperty.Value = MyValidValues.ValueOne;

Visual Basic Copy Code Dim customProperty As IDTSCustomProperty90 = outputColumn.CustomPropertyCollection.New customProperty.Name = "My Custom Property" ' This line associates the type with the custom property. customProperty.TypeConverter = GetType(MyValidValues).AssemblyQualifiedName ' Now you can use the enumeration values directly. customProperty.Value = MyValidValues.ValueOne

For more information, see "Generalized Type Conversion" and "Implementing a Type Converter" in the MSDN Library.

You can specify a custom editor dialog box for the value of your custom property by using the UITypeEditor property, as shown in the following example. First, you must create a custom type editor that inherits from System.Drawing.Design.UITypeEditor, if you are unable to locate an existing UI type editor class that fits your needs.

C# Copy Code public class MyCustomTypeEditor : UITypeEditor { ... }

Visual Basic Copy Code Public Class MyCustomTypeEditor Inherits UITypeEditor ... End Class

Then specify this class as the value of the UITypeEditor property of your custom property.

C# Copy Code IDTSCustomProperty90 customProperty = outputColumn.CustomPropertyCollection.New(); customProperty.Name = "My Custom Property"; // This line associates the editor with the custom property. customProperty.UITypeEditor = typeof(MyCustomTypeEditor).AssemblyQualifiedName;

Visual Basic Copy Code Dim customProperty As IDTSCustomProperty90 = outputColumn.CustomPropertyCollection.New customProperty.Name = "My Custom Property" ' This line associates the editor with the custom property. customProperty.UITypeEditor = GetType(MyCustomTypeEditor).AssemblyQualifiedName

For more information, see "Implementing a UI Type Editor" in the MSDN Library.

|||Great, thanks!|||

I have a question though...I followed those directions and I can get the enumerated list to show up in my transformation when added through SSIS.

My question now is, how do I access the property within the "ProcessInput" function?

|||

I would read the property value in PreExecute and store in a class level variable, but the code is the same regardless of location-

IDTSCustomProperty90 property = componentMetaData.CustomPropertyCollection[propertyName];
object o = property.Value;

|||Ahh, makes sense. Thank you.