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
>
>

Adding Weekend data to Monday

We have a data extract prcess that runs daily. In SQL Script, there is a
variable that equals to 1 (meaning 1 day worth of data). We run this monday
through friday at 9:00 PM. Now the client wants, if there is any processing
on the weekend (Saturday/Sunday) it should be added to the monday's data.
This is an automated process and we need to keep it like that. The question
is, how can I modify the script so that it picks up the daily data Tuesday
through friday and 3 days of data (Saturday, Sunday and Monday) on mondays ?
Thanks for any help.Do you want a sum or the rows on their own, only presenting those on the
weekdays ?
You have to specify your expected results a bit.
--
HTH, Jens Suessmeyer.
--
http://www.sqlserver2005.de
--
"DXC" <DXC@.discussions.microsoft.com> schrieb im Newsbeitrag
news:B12A81D8-BFC1-44B4-8842-AC895B2E30F0@.microsoft.com...
> We have a data extract prcess that runs daily. In SQL Script, there is a
> variable that equals to 1 (meaning 1 day worth of data). We run this
> monday
> through friday at 9:00 PM. Now the client wants, if there is any
> processing
> on the weekend (Saturday/Sunday) it should be added to the monday's data.
> This is an automated process and we need to keep it like that. The
> question
> is, how can I modify the script so that it picks up the daily data Tuesday
> through friday and 3 days of data (Saturday, Sunday and Monday) on mondays
> ?
> Thanks for any help.|||Data is selected according to what is in 'datetime' columns and not as sum.
"Jens Sü�meyer" wrote:
> Do you want a sum or the rows on their own, only presenting those on the
> weekdays ?
> You have to specify your expected results a bit.
> --
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
> "DXC" <DXC@.discussions.microsoft.com> schrieb im Newsbeitrag
> news:B12A81D8-BFC1-44B4-8842-AC895B2E30F0@.microsoft.com...
> > We have a data extract prcess that runs daily. In SQL Script, there is a
> > variable that equals to 1 (meaning 1 day worth of data). We run this
> > monday
> > through friday at 9:00 PM. Now the client wants, if there is any
> > processing
> > on the weekend (Saturday/Sunday) it should be added to the monday's data.
> >
> > This is an automated process and we need to keep it like that. The
> > question
> > is, how can I modify the script so that it picks up the daily data Tuesday
> > through friday and 3 days of data (Saturday, Sunday and Monday) on mondays
> > ?
> >
> > Thanks for any help.
>
>|||Sure that you got your results in mind you want to have, we don´t.
Do you want to get something like
Tu-1,we-1,th-1.fr-1,m-4
...if there is data for every day.
Or do you want to build a sum of some value for thse days ?
The best things would be to provide us with some DDL and sample data.
--
HTH, Jens Suessmeyer.
--
http://www.sqlserver2005.de
--
"DXC" <DXC@.discussions.microsoft.com> schrieb im Newsbeitrag
news:28A41391-7E40-4550-88D0-EC1A32AB35CF@.microsoft.com...
> Data is selected according to what is in 'datetime' columns and not as
> sum.
>
> "Jens Süßmeyer" wrote:
>> Do you want a sum or the rows on their own, only presenting those on the
>> weekdays ?
>> You have to specify your expected results a bit.
>> --
>> HTH, Jens Suessmeyer.
>> --
>> http://www.sqlserver2005.de
>> --
>> "DXC" <DXC@.discussions.microsoft.com> schrieb im Newsbeitrag
>> news:B12A81D8-BFC1-44B4-8842-AC895B2E30F0@.microsoft.com...
>> > We have a data extract prcess that runs daily. In SQL Script, there is
>> > a
>> > variable that equals to 1 (meaning 1 day worth of data). We run this
>> > monday
>> > through friday at 9:00 PM. Now the client wants, if there is any
>> > processing
>> > on the weekend (Saturday/Sunday) it should be added to the monday's
>> > data.
>> >
>> > This is an automated process and we need to keep it like that. The
>> > question
>> > is, how can I modify the script so that it picks up the daily data
>> > Tuesday
>> > through friday and 3 days of data (Saturday, Sunday and Monday) on
>> > mondays
>> > ?
>> >
>> > Thanks for any help.
>>

Adding Weekend data to Monday

We have a data extract prcess that runs daily. In SQL Script, there is a
variable that equals to 1 (meaning 1 day worth of data). We run this monday
through friday at 9:00 PM. Now the client wants, if there is any processing
on the weekend (Saturday/Sunday) it should be added to the monday's data.
This is an automated process and we need to keep it like that. The question
is, how can I modify the script so that it picks up the daily data Tuesday
through friday and 3 days of data (Saturday, Sunday and Monday) on mondays ?
Thanks for any help.
Do you want a sum or the rows on their own, only presenting those on the
weekdays ?
You have to specify your expected results a bit.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
"DXC" <DXC@.discussions.microsoft.com> schrieb im Newsbeitrag
news:B12A81D8-BFC1-44B4-8842-AC895B2E30F0@.microsoft.com...
> We have a data extract prcess that runs daily. In SQL Script, there is a
> variable that equals to 1 (meaning 1 day worth of data). We run this
> monday
> through friday at 9:00 PM. Now the client wants, if there is any
> processing
> on the weekend (Saturday/Sunday) it should be added to the monday's data.
> This is an automated process and we need to keep it like that. The
> question
> is, how can I modify the script so that it picks up the daily data Tuesday
> through friday and 3 days of data (Saturday, Sunday and Monday) on mondays
> ?
> Thanks for any help.
|||Data is selected according to what is in 'datetime' columns and not as sum.
"Jens Sü?meyer" wrote:

> Do you want a sum or the rows on their own, only presenting those on the
> weekdays ?
> You have to specify your expected results a bit.
> --
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
> "DXC" <DXC@.discussions.microsoft.com> schrieb im Newsbeitrag
> news:B12A81D8-BFC1-44B4-8842-AC895B2E30F0@.microsoft.com...
>
>
|||Sure that you got your results in mind you want to have, we dont.
Do you want to get something like
Tu-1,we-1,th-1.fr-1,m-4
...if there is data for every day.
Or do you want to build a sum of some value for thse days ?
The best things would be to provide us with some DDL and sample data.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
"DXC" <DXC@.discussions.microsoft.com> schrieb im Newsbeitrag
news:28A41391-7E40-4550-88D0-EC1A32AB35CF@.microsoft.com...[vbcol=seagreen]
> Data is selected according to what is in 'datetime' columns and not as
> sum.
>
> "Jens Smeyer" wrote:

Adding Weekend data to Monday

We have a data extract prcess that runs daily. In SQL Script, there is a
variable that equals to 1 (meaning 1 day worth of data). We run this monday
through friday at 9:00 PM. Now the client wants, if there is any processing
on the weekend (Saturday/Sunday) it should be added to the monday's data.
This is an automated process and we need to keep it like that. The question
is, how can I modify the script so that it picks up the daily data Tuesday
through friday and 3 days of data (Saturday, Sunday and Monday) on mondays ?
Thanks for any help.Do you want a sum or the rows on their own, only presenting those on the
weekdays ?
You have to specify your expected results a bit.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"DXC" <DXC@.discussions.microsoft.com> schrieb im Newsbeitrag
news:B12A81D8-BFC1-44B4-8842-AC895B2E30F0@.microsoft.com...
> We have a data extract prcess that runs daily. In SQL Script, there is a
> variable that equals to 1 (meaning 1 day worth of data). We run this
> monday
> through friday at 9:00 PM. Now the client wants, if there is any
> processing
> on the weekend (Saturday/Sunday) it should be added to the monday's data.
> This is an automated process and we need to keep it like that. The
> question
> is, how can I modify the script so that it picks up the daily data Tuesday
> through friday and 3 days of data (Saturday, Sunday and Monday) on mondays
> ?
> Thanks for any help.|||Data is selected according to what is in 'datetime' columns and not as sum.
"Jens Sü?meyer" wrote:

> Do you want a sum or the rows on their own, only presenting those on the
> weekdays ?
> You have to specify your expected results a bit.
> --
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
> "DXC" <DXC@.discussions.microsoft.com> schrieb im Newsbeitrag
> news:B12A81D8-BFC1-44B4-8842-AC895B2E30F0@.microsoft.com...
>
>|||Sure that you got your results in mind you want to have, we dont.
Do you want to get something like
Tu-1,we-1,th-1.fr-1,m-4
...if there is data for every day.
Or do you want to build a sum of some value for thse days ?
The best things would be to provide us with some DDL and sample data.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"DXC" <DXC@.discussions.microsoft.com> schrieb im Newsbeitrag
news:28A41391-7E40-4550-88D0-EC1A32AB35CF@.microsoft.com...[vbcol=seagreen]
> Data is selected according to what is in 'datetime' columns and not as
> sum.
>
> "Jens Smeyer" wrote:
>

Adding User Define fields together

This is driving me crazy! The SQL Statement refenced is shown at the end of this email.

When I try and run the statement, an error is raised saying that Undrawn_GT5MIL_LE365Days is invalid (likewise for Undrawn_LE5MIL_LE365Days). From what I can gather, it is saying that I cannot include a User Defined variable in another argument. This is unlike Access. Any suggestions?

SQL View......

SELECT TOP 100 PERCENT QRY_FacNew_Term.Category, QRY_FacNew_Term.Fac_No, QRY_FacNew_Term.Client_Number, QRY_FacNew_Term.Client_Name,

Undrawn_GT5MIL_LE365Days = CASE WHEN Undrawn_CDN >= 5000000 AND Term <= 365 THEN Undrawn_CDN ELSE 0 END,

Undrawn_GT5MIL_GT365Days = CASE WHEN (Undrawn_CDN >= 5000000 AND Term > 365) OR

(Cr_Limit_CDN IN (0, 1)) THEN Undrawn_CDN ELSE 0 END, [Undrawn_GT5MIL_LE365Days]+[Undrawn_GT5MIL_GT365Days] AS Total

FROM dbo.QRY_FacNew_Term

WHERE (Exclude <> 'Y')

ORDER BY Category, Client_NameUse brackets if name includes space:

[drawn_GT5MIL_LE365Days ] = CASE WHEN Undrawn_CDN >= 5000000 AND Term <= 365 THEN Undrawn_CDN ELSE 0 END,

Adding Query Results To A Tbale

hello everybody,

I have a small problem and was hipeing someone could help. Want to run a query, where I sellect results from two tables. Once this is done I want to takes those results and update a table that is already created with those results. How would I do this? Thw eonly way I know how to do this is by writing this:

select CompanyName, ContactName, Address, City, Region, ProductName, UnitPrice INTO TABLE2
From Suppliers, Products

But the problem with this is that I can only input the query results into a new table that I would name above. I can't insert it into a table that has already been created.

This is the erroe that I receive:
There is already an object named 'TABLE2' in the database.

Please Help!

thanksINSERT INTO TABLE2 (
CompanyName
, ContactName
, Address
, City
, Region
, ProductName
, UnitPrice)
SELECT
CompanyName
, ContactName
, Address
, City
, Region
, ProductName
, UnitPrice
From Suppliers, Products

I'd also say you're missing a JOIN|||So say that information changes on the 2 tables that I am querying, when I run this insert code with the query, it will reinsert all the records into table2, which will basically be updating it. Am i getting this right? Thats what I am trying to do. I could also turn that into a job so that it could run automatically right?

thanks|||Inserting will add records to the table unless you have constraints that prevent duplicate records/keys. Updating modifies the original field(s) of the record you are working with. Are you new to database design ?|||Can you elaborate what you are trying to do ?|||Originally posted by rnealejr
Inserting will add records to the table unless you have constraints that prevent duplicate records/keys. Updating modifies the original field(s) of the record you are working with. Are you new to database design ?

Actually yes, I am new to this. Thats probably why I sound redundante. So I need as much help as possibel

thanks|||Originally posted by rnealejr
Can you elaborate what you are trying to do ?

Okay, here it goes. I created a table called "table1". This table has all the columns named already. I want to hold records from a a query that I run on two other tables and then place the results in table2. The colums that I query are named the same as the columns I created in table2. I just want all those records to show up in table2.|||Aren't you planning on using the code I posted?|||Originally posted by Brett Kaiser
Aren't you planning on using the code I posted?
ACTUALLY i USED IT AND IT WORKED WELL. i WAS JUST WONDERING IF THERE WERE OTHER WAYS OF DOING IT.
THANKS FOR YOUR HELP. i AM PRETTY NEW AT THIS.

Adding New Measure to OLAP Cube

To add a record count measure to the olap cube. Create the cube as
usual then run the following VB code with command line parameters
Step1: Build your cube as usual
Step2: Convert the blow vb code to exe prog
Step3: Run the exe with the <Ananlysis server name> <cube name>
parameters (e.g) OLAPcount.exe <Analysis Server> <Cube Name>
Public Sub main()
Dim dsoServer As New DSO.Server
Dim dsoDB As DSO.MDStore
Dim dsoCube As DSO.MDStore
Dim dsoMea As DSO.Measure
Dim dsoAssFactCube As DSO.Cube
Dim dsoPortAnalyzerCube As DSO.Cube
'for storing initial command line arguments as entered by user
Dim strArgs() As String
'for storing the parsed command line arguments
Dim ParsedArgs As String
'for storing the final array of command line arguments
Dim finalArgs() As String
'Splitting the command line arguments based on a space
strArgs = Split(Command$, " ")
'Parsing the command line arguments to generate the parsed string
For i = 0 To UBound(strArgs)
If Len(Trim(strArgs(i))) > 0 Then
ParsedArgs = ParsedArgs & Trim(strArgs(i)) & " "
End If
Next
'Splitting the parsed string into final array of arguments
finalArgs = Split(ParsedArgs, " ")
'Check for correct number of arguments
If UBound(finalArgs) < 2 Then
MsgBox ("Wrong Syntax..." & "or wrong number of
arguments....Correcet Syntax : OLAPcount.exe <Analysis Server> <Cube
Name> (e.g)OLAPcount.exe livdwqprj03 AIGTMSReport1")
Else
'connect to the server (Analysis Server name)
dsoServer.Connect (finalArgs(0))
'Examine whether all necessary components are present (Cube
name)
If dsoServer.MDStores.Find(finalArgs(1)) = False Then
GoTo err_no_database
End If
'Connect with the data base (CUBE) (Cube name)
Set dsoDB = dsoServer.MDStores(finalArgs(1))
If dsoDB.DataSources.Count = 0 Then
GoTo err_no_datasource
ElseIf dsoDB.Dimensions.Count = 0 Then
GoTo err_no_dimensions
ElseIf dsoDB.MDStores.Find("MSP_ASSN_FACT") = False Then
GoTo err_no_fact_cube
ElseIf dsoDB.MDStores.Find("MSP_PORTFOLIO_ANALYZER") = False
Then
GoTo err_no_analyzer
End If
'Set the cube table to use
Set dsoAssFactCube = dsoDB.MDStores("MSP_ASSN_FACT")
Set dsoPortAnalyzerCube =
dsoDB.MDStores("MSP_PORTFOLIO_ANALYZER")
'Specify the name of the new measure
Set dsoMea = dsoAssFactCube.Measures.AddNew("Total
Assignments")
'Specify the source column based on which the operation need to
be performed
'dsoMea.SourceColumn =
"""MSP_CUBE_ASSN_FACT"".""ENT_ASSIGNMENT_CODE6"""
dsoMea.SourceColumn = """MSP_CUBE_ASSN_FACT"".""PROJ_UID"""
'The datatype for the column
dsoMea.SourceColumnType = ADODB.DataTypeEnum.adDecimal
'The method for the column aggSum or aggCount aggregates the
column by summation or counts.
dsoMea.AggregateFunction = aggCount
'update the cube
dsoAssFactCube.Update
dsoAssFactCube.Process
'dsoAnalyzerCube represents a virtual Cube. the measure of a
virtual Cubes has
'the characteristics of the measure of the material cubes
Set dsoMea = dsoPortAnalyzerCube.Measures.AddNew("Total
Assignments")
'The column is indicated in "more normal" form, since the
measure belongs to the virtual Cube!
'dsoMea.SourceColumn = "MSP_ASSN_FACT.FIXED COST"
dsoMea.SourceColumn = "MSP_ASSN_FACT.Total Assignments"
dsoPortAnalyzerCube.Update
dsoPortAnalyzerCube.Process
dsoDB.Process
leave_now:
UserOLAPUpdate = 0
' Exit Function
err_no_database:
l_errnum = 1
s_errdesc = "Datenbank konnte nicht gefunden werden!"
UserOLAPUpdate = vbObjectError + 1
' Exit Function
err_no_datasource:
l_errnum = 1
s_errdesc = "Datenquelle konnte nicht gefunden werden!"
UserOLAPUpdate = vbObjectError + 2
' Exit Function
err_no_dimensions:
l_errnum = 1
s_errdesc = "Dimensionen konnten nicht gefunden werden!"
UserOLAPUpdate = vbObjectError + 3
' Exit Function
err_no_fact_cube:
l_errnum = 1
s_errdesc = "Cube MSP_ASSN_FACT konnte nicht gefunden werden!"
UserOLAPUpdate = vbObjectError + 4
' Exit Function
err_no_analyzer:
l_errnum = 1
s_errdesc = "Cube MSP_PORTFOLIO_ANALYZER konnte nicht gefunden
werden! "
UserOLAPUpdate = vbObjectError + 5
' Exit Function
error_handler:
l_errnum = Err.Number
s_errdesc = Err.Description
UserOLAPUpdate = 1 ' although it could be any non-zero value
' to indicate an error
End If
End SubPerhaps you should head for the ng
http://www.microsoft.com/communitie...sqlserver.olap
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"wilsonjust@.gmail.com" wrote:

> To add a record count measure to the olap cube. Create the cube as
> usual then run the following VB code with command line parameters
> Step1: Build your cube as usual
> Step2: Convert the blow vb code to exe prog
> Step3: Run the exe with the <Ananlysis server name> <cube name>
> parameters (e.g) OLAPcount.exe <Analysis Server> <Cube Name>
> Public Sub main()
> Dim dsoServer As New DSO.Server
> Dim dsoDB As DSO.MDStore
> Dim dsoCube As DSO.MDStore
> Dim dsoMea As DSO.Measure
> Dim dsoAssFactCube As DSO.Cube
> Dim dsoPortAnalyzerCube As DSO.Cube
> 'for storing initial command line arguments as entered by user
> Dim strArgs() As String
> 'for storing the parsed command line arguments
> Dim ParsedArgs As String
> 'for storing the final array of command line arguments
> Dim finalArgs() As String
> 'Splitting the command line arguments based on a space
> strArgs = Split(Command$, " ")
> 'Parsing the command line arguments to generate the parsed string
> For i = 0 To UBound(strArgs)
> If Len(Trim(strArgs(i))) > 0 Then
> ParsedArgs = ParsedArgs & Trim(strArgs(i)) & " "
> End If
> Next
> 'Splitting the parsed string into final array of arguments
> finalArgs = Split(ParsedArgs, " ")
> 'Check for correct number of arguments
> If UBound(finalArgs) < 2 Then
> MsgBox ("Wrong Syntax..." & "or wrong number of
> arguments....Correcet Syntax : OLAPcount.exe <Analysis Server> <Cube
> Name> (e.g)OLAPcount.exe livdwqprj03 AIGTMSReport1")
> Else
> 'connect to the server (Analysis Server name)
> dsoServer.Connect (finalArgs(0))
> 'Examine whether all necessary components are present (Cube
> name)
> If dsoServer.MDStores.Find(finalArgs(1)) = False Then
> GoTo err_no_database
> End If
> 'Connect with the data base (CUBE) (Cube name)
> Set dsoDB = dsoServer.MDStores(finalArgs(1))
> If dsoDB.DataSources.Count = 0 Then
> GoTo err_no_datasource
> ElseIf dsoDB.Dimensions.Count = 0 Then
> GoTo err_no_dimensions
> ElseIf dsoDB.MDStores.Find("MSP_ASSN_FACT") = False Then
> GoTo err_no_fact_cube
> ElseIf dsoDB.MDStores.Find("MSP_PORTFOLIO_ANALYZER") = False
> Then
> GoTo err_no_analyzer
> End If
> 'Set the cube table to use
> Set dsoAssFactCube = dsoDB.MDStores("MSP_ASSN_FACT")
> Set dsoPortAnalyzerCube =
> dsoDB.MDStores("MSP_PORTFOLIO_ANALYZER")
> 'Specify the name of the new measure
> Set dsoMea = dsoAssFactCube.Measures.AddNew("Total
> Assignments")
> 'Specify the source column based on which the operation need to
> be performed
> 'dsoMea.SourceColumn =
> """MSP_CUBE_ASSN_FACT"".""ENT_ASSIGNMENT_CODE6"""
> dsoMea.SourceColumn = """MSP_CUBE_ASSN_FACT"".""PROJ_UID"""
> 'The datatype for the column
> dsoMea.SourceColumnType = ADODB.DataTypeEnum.adDecimal
> 'The method for the column aggSum or aggCount aggregates the
> column by summation or counts.
> dsoMea.AggregateFunction = aggCount
> 'update the cube
> dsoAssFactCube.Update
> dsoAssFactCube.Process
> 'dsoAnalyzerCube represents a virtual Cube. the measure of a
> virtual Cubes has
> 'the characteristics of the measure of the material cubes
> Set dsoMea = dsoPortAnalyzerCube.Measures.AddNew("Total
> Assignments")
> 'The column is indicated in "more normal" form, since the
> measure belongs to the virtual Cube!
> 'dsoMea.SourceColumn = "MSP_ASSN_FACT.FIXED COST"
> dsoMea.SourceColumn = "MSP_ASSN_FACT.Total Assignments"
> dsoPortAnalyzerCube.Update
> dsoPortAnalyzerCube.Process
> dsoDB.Process
> leave_now:
> UserOLAPUpdate = 0
> ' Exit Function
> err_no_database:
> l_errnum = 1
> s_errdesc = "Datenbank konnte nicht gefunden werden!"
> UserOLAPUpdate = vbObjectError + 1
> ' Exit Function
> err_no_datasource:
> l_errnum = 1
> s_errdesc = "Datenquelle konnte nicht gefunden werden!"
> UserOLAPUpdate = vbObjectError + 2
> ' Exit Function
> err_no_dimensions:
> l_errnum = 1
> s_errdesc = "Dimensionen konnten nicht gefunden werden!"
> UserOLAPUpdate = vbObjectError + 3
> ' Exit Function
> err_no_fact_cube:
> l_errnum = 1
> s_errdesc = "Cube MSP_ASSN_FACT konnte nicht gefunden werden!"
> UserOLAPUpdate = vbObjectError + 4
> ' Exit Function
> err_no_analyzer:
> l_errnum = 1
> s_errdesc = "Cube MSP_PORTFOLIO_ANALYZER konnte nicht gefunden
> werden! "
> UserOLAPUpdate = vbObjectError + 5
> ' Exit Function
> error_handler:
> l_errnum = Err.Number
> s_errdesc = Err.Description
> UserOLAPUpdate = 1 ' although it could be any non-zero value
> ' to indicate an error
> End If
> End Sub
>

Adding dates giving blank or incorrect date

My report has a finishdate column which is grouped into week intervals, Im trying to run the following formula to get the Saturday(end of the week) based on the finishdate e.g if the finish date is 4/7/2006 which is a Tuesday, I want to find out what the end off week date is - which would be 8/7/2006 (Saturday) and place this into another column.

NumberVar whatday;

whatday = dayofweek({finishdate});
If whatday = 2 then //monday
{finishdate} + 5;
Else whatday = 3 then
{finishdate} + 4;
Else whatday = 4 then
{finishdate} + 3;
Else whatday = 5 then
{finishdate} + 2;
Else whatday = 6 then
{finishdate} + 1;

This is not returning any errors but also it is giving me just a blank output. Also when i run the lines
{finishdate} + 4;
on its own, the dates are adding up correctly

OR

dayofweek({finishdate}) onit owns it returns the correct day off week for the finishdate

when i put variables
NumberVar whatday;

whatday = dayofweek({finishdate});
If whatday = 2 then //monday
{finishdate} + 5;

i get a day returned off 1/1/-4713 or an incorrect dayofweek
I have no idea why this is returned.

I am using Crystal Report 7 and the dateadd() is not available, I dont know why when run with conditions etc such as if and variable declarations, it doesnt return the right date, but when run on its own it works.

Can someone help,

thanksThe common mistake people do with Crystal, is when they forget there are two different syntaxes: Crystal and VB. In your case, I think the problem is in assignment operator. Try whatday := dayofweek({finishdate}); instead of whatday = dayofweek({finishdate}); The latter would return the boolean value as a result of comparision, not assignment.

Adding Comments to a SQL2005 Query!

Hi everyone,

Has anyone else had problems trying to get comments to save in a view?

Every time you run or save the view, the comments disapear. Also all of the formatting / laying out of the query are lost also.

Anyone found a way to save them or keep your formatting?

Regards,

Steve

It depends on the program that you use to create the view. If you try to use the query design wizard (that automatically parses your SQL), then this will certainly be the case.

Try instead to create a new Query file (in SQL Management Studio). You'll notice that you get a different editor, and your code won't be modified as its parsed. In this mode, you'll also be able to add comments, and format your text as you wish, unheeded.

Adding comments to a Report

I would like to allow users to add a comment to a report. What is the best way to do this? When the report is run I would like users to be able to see all comments and which user reported them and the date and time the comment was entered

Hi,

Reporting Services doesn't include such functionality. I think that the best way to do it is to implement it in the context where the report is viewed. For example, if users view there report starting from a Sharepoint application, you can add comment functionality in Sharepoint. Eventually, you can create an ASP.NET page that displays reports (Report Viewer Control) and gives the possibility to display comments.

Greetz,

Geert

Geert Verhoeven
Consultant @. Ausy Belgium

My Personal Blog

Adding column to sp_helpuser

I am working on creating a configuration database. This will connect to many databases on many different servers. What I would like to do is run sp_MSForEachDB then store the name of the current server and database the package is connected to. Then add both of these into a column before running sp_helpuser. I don't know how confusing this is, but I think I may have the code worked out pretty well. If you have any questions, please post them and I will try to answer as best as I can. What I would like the ending result to look like is...

<server1> <database1> <userID1> <datareader>
<server1> <database1> <userID1> <datawriter>
<server1> <database1> <userID2> <datawriter>
<server1> <database2> <userID1> <datawriter>

I am at a loss, and I can't use the sys.<table> tables because it has to be backward compatible. Thanks for any help in advance.
sp_helplogins is the answer.
-Kyle

Adding column to sp_helpuser

I am working on creating a configuration database. This will connect to many databases on many different servers. What I would like to do is run sp_MSForEachDB then store the name of the current server and database the package is connected to. Then add both of these into a column before running sp_helpuser. I don't know how confusing this is, but I think I may have the code worked out pretty well. If you have any questions, please post them and I will try to answer as best as I can. What I would like the ending result to look like is...

<server1> <database1> <userID1> <datareader>
<server1> <database1> <userID1> <datawriter>
<server1> <database1> <userID2> <datawriter>
<server1> <database2> <userID1> <datawriter>

I am at a loss, and I can't use the sys.<table> tables because it has to be backward compatible. Thanks for any help in advance.
sp_helplogins is the answer.
-Kyle

adding an article to a publication

When adding an article I have checked the checkbox to show unpublished
articles
and then selected the required the tables. After this, I have run the
snapshot agent for this publication.
The 2 tables I added contained no data yet the snapshot started the coping
of rows of other tables in the publication . Is this right as I thought this
snapshot would just process information for the unpublished tables?
thanks for any help
Sammy
Hi Sammy,
This is expected behavior if you have a merge or snapshot publication. If
you have a transactional publication, a snapshot of all articles will always
be generated if the immediate_sync publication property is set to true.
Typically, the immediate_sync publication property is set to true if you
allowed anonymous subscriptions while creating the publication through the
CreatePublication wizard. If none of what I said previously applies to your
scenario, it would be great if you can post more details about your
publication so either me or other folks on this newsgroup are in a better
position to figure this out.
HTH
-Raymond
"Sammy" <Sammy@.discussions.microsoft.com> wrote in message
news:0A215E11-F110-458D-A9FB-E29B4747E3E9@.microsoft.com...
> When adding an article I have checked the checkbox to show unpublished
> articles
> and then selected the required the tables. After this, I have run the
> snapshot agent for this publication.
> The 2 tables I added contained no data yet the snapshot started the coping
> of rows of other tables in the publication . Is this right as I thought
> this
> snapshot would just process information for the unpublished tables?
>
> thanks for any help
> Sammy
>

Adding a table to transactional repliaction

I am adding a table to an existing publication that has one pull subscription
using the steps listed below. When I run the snapshot agent, it does not
recognize the newly added article ("no subscriptions needed initialization").
I WANT TO RUN THE SNAPSHOT FOR JUST ONE TABLE. Do I need to drop and readd
the subscriber or subscription? If so, please let me know the steps.
ALSO, the steps below did work on a QA server but failed in production.
-- Thanks for your help.
exec sp_addarticle @.publication = N'my_PUBLICATION', @.article = N'my_TABLE',
@.source_owner = N'dbo', @.source_object = N'my_TABLE', @.destination_table =
N'my_TABLE', @.type = N'logbased', @.creation_script = null, @.description =
null, @.pre_creation_cmd = N'drop', @.schema_option = 0x00000000000000F3,
@.status = 16, @.vertical_partition = N'false', @.ins_cmd = N'CALL
sp_MSins_my_TABLE', @.del_cmd = N'CALL sp_MSdel_my_TABLE', @.upd_cmd = N'MCALL
sp_MSupd_my_TABLE', @.filter = null, @.sync_object = null, @.auto_identity_range
= N'false', @.force_invalidate_snapshot = 1
GO
select * from distribution..msArticles where article = 'my_TABLE'
go
EXEC sp_refreshsubscriptions
N'my_PUBLICATION'
go
exec sp_reinitsubscription
@.publication = 'my_PUBLICATION',
@.article = 'my_TABLE',
@.for_schema_change = 1,
@.subscriber = 'all'
GO
exec sp_addsubscription
@.publication = 'my_PUBLICATION',
@.article = 'my_TABLE',
@.subscriber = N'SUB_SERVER',
@.destination_db = N'pub_sub_database',
@.sync_type = N'automatic',
@.update_mode = N'read only'
I think you need to run sp_refreshpublications to get it to pick up the
changes.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"sbawa" <sbawa@.discussions.microsoft.com> wrote in message
news:F24C7DED-985E-4011-B4E7-F2C5E972F8EC@.microsoft.com...
>I am adding a table to an existing publication that has one pull
>subscription
> using the steps listed below. When I run the snapshot agent, it does not
> recognize the newly added article ("no subscriptions needed
> initialization").
>
> I WANT TO RUN THE SNAPSHOT FOR JUST ONE TABLE. Do I need to drop and
> readd
> the subscriber or subscription? If so, please let me know the steps.
> ALSO, the steps below did work on a QA server but failed in production.
> -- Thanks for your help.
>
> exec sp_addarticle @.publication = N'my_PUBLICATION', @.article =
> N'my_TABLE',
> @.source_owner = N'dbo', @.source_object = N'my_TABLE', @.destination_table =
> N'my_TABLE', @.type = N'logbased', @.creation_script = null, @.description =
> null, @.pre_creation_cmd = N'drop', @.schema_option = 0x00000000000000F3,
> @.status = 16, @.vertical_partition = N'false', @.ins_cmd = N'CALL
> sp_MSins_my_TABLE', @.del_cmd = N'CALL sp_MSdel_my_TABLE', @.upd_cmd =
> N'MCALL
> sp_MSupd_my_TABLE', @.filter = null, @.sync_object = null,
> @.auto_identity_range
> = N'false', @.force_invalidate_snapshot = 1
> GO
> select * from distribution..msArticles where article = 'my_TABLE'
> go
> EXEC sp_refreshsubscriptions
> N'my_PUBLICATION'
> go
> exec sp_reinitsubscription
> @.publication = 'my_PUBLICATION',
> @.article = 'my_TABLE',
> @.for_schema_change = 1,
> @.subscriber = 'all'
> GO
> exec sp_addsubscription
> @.publication = 'my_PUBLICATION',
> @.article = 'my_TABLE',
> @.subscriber = N'SUB_SERVER',
> @.destination_db = N'pub_sub_database',
> @.sync_type = N'automatic',
> @.update_mode = N'read only'
>
|||I tried using object search but unable to find this procedure in any of the
databases. Please help.
Your book on transactional replication is great. But you can probably see
that I haven't gotten too far into it yet.
"Hilary Cotter" wrote:

> I think you need to run sp_refreshpublications to get it to pick up the
> changes.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "sbawa" <sbawa@.discussions.microsoft.com> wrote in message
> news:F24C7DED-985E-4011-B4E7-F2C5E972F8EC@.microsoft.com...
>
>