database collation: guys

Showing posts with label guys. Show all posts

Tuesday, March 27, 2012

Changing the Server Name

Hi Guys,

I am using a MS SQL Server 2005 express edition for storing the meta-files of a b2b application.

The SQL 2005 is installed localy. Now I must change my server (machine) name cause a internal application need this action.

What is the best practice to do this?

I've got 2 databases and a lot of tabeles within the installed sql server.

I am happy for all tutorials or instructions to handle this proposal nearyl perfectyl.

Many Regards

Check out http://msdn2.microsoft.com/en-us/library/ms143799.aspx

Mike

Changing the owner of a user-defined data type

Hi Guys
Wonder if you could help me.
Basically I produce an accounts package that uses a SQL 2000 DB as the
RDBMS. I always instruct users to login as 'sa' and the relevant
password when doing an update to my program, as sometimes I need to do
database changes for new stuff.
Found that one of my users has not only logged in with their login
name (in this case Edward), but have also made this login a 'db owner'
so that when I created 2 new user-defined data types they belong to
Edward rather than dbo.
This must have happened a long time ago, but now that they want to
move Edward round the roles and/or delete him from a copy of the
database that they have, they can't because he's the owner of these
user-defined types.
This brings me to the reason for my post, how can I change the owner
from Edward to dbo for these data types? I found an article on
technet of how to do this, but when it suggests changing my
user-defined type to standard format it doesn't seem to work.
Any ideas?
Rgds
RobbieI have never changed the owner of a UDT, but if it can be done, then
sp_changeobjectowner would the be sp to do it.
--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Robbie" <google@.astraaccounts.co.uk> wrote in message
news:f588d13a.0310062353.3dba1c6e@.posting.google.com...
> Hi Guys
> Wonder if you could help me.
> Basically I produce an accounts package that uses a SQL 2000 DB as the
> RDBMS. I always instruct users to login as 'sa' and the relevant
> password when doing an update to my program, as sometimes I need to do
> database changes for new stuff.
> Found that one of my users has not only logged in with their login
> name (in this case Edward), but have also made this login a 'db owner'
> so that when I created 2 new user-defined data types they belong to
> Edward rather than dbo.
> This must have happened a long time ago, but now that they want to
> move Edward round the roles and/or delete him from a copy of the
> database that they have, they can't because he's the owner of these
> user-defined types.
> This brings me to the reason for my post, how can I change the owner
> from Edward to dbo for these data types? I found an article on
> technet of how to do this, but when it suggests changing my
> user-defined type to standard format it doesn't seem to work.
> Any ideas?
> Rgds
> Robbie

Tuesday, March 20, 2012

changing SQL server service account

Guys,
I have got WINDOWS 2000 Advanced Server and MS SQL SERVER 7.0 running on my live server. Now when we are planning for replication, we have found that SQL server will require to run under a domain account. At the moment there are so many ASP pages running on our server accesses different databases created using SQL server 7.0. Most of them are DSN connections to the database. Now if i create a domain account and restart the server and MS SQL services with the domain account, how is it going to effect the current web pages running on it?
Any help will be greatly appreciated.
ThanksA connection to a database has no bearing on what account starts the sql server service. However the account used does affect how the server interacts with other sql servers, or other machine resources, you found that out when you tried to implement replication using LocalSystem account. You can search msdn for "sql server service account" to get a better understanding of the purpose of accounts for sql server.|||Thanks very much Greg,
your reply helped me to have better understanding.
I would find out more from MSDN as you have suggested.
Cheers.

Sunday, March 11, 2012

Changing Server

Hi guys
We are closing down our old test server. This server used to run RS. Is
there any documentation on how to move the RS installation to another server
so that Reports, Schedules etc. is intact and ready for use on the new
server?
Thanks.
Regards
JonasJonas,
You may find my post useful
http://prologika.com/blog/archive/2004/08/20/161.aspx
Let me know if you find discrepancies.
--
Hope this helps.
----
Teo Lachev, MCSD, MCT
Author: "Microsoft Reporting Services in Action"
Publisher website: http://www.manning.com/lachev
Buy it from Amazon.com: http://shrinkster.com/eq
Home page and blog: http://www.prologika.com/
----
"Jonas Larsen" <Jonas.Larsen@.Alcan.com> wrote in message
news:O9WLxgkjEHA.1136@.tk2msftngp13.phx.gbl...
> Hi guys
> We are closing down our old test server. This server used to run RS. Is
> there any documentation on how to move the RS installation to another
server
> so that Reports, Schedules etc. is intact and ready for use on the new
> server?
> Thanks.
> Regards
> Jonas
>|||Hi Teo
My current version of various sw does not support web farms therefore I had
to:
- Remove the key in the new server: rskeymgmt -r[InstallationID]
(InstallationId is in the RSReportServer.Config file).
- Swap the installation id in the actual RSReportServer.Config file with
the old one.
- Run rskeymgmt -a -f -p
Regards
Jonas
"Teo Lachev" <teo.lachev@.nospam.prologika.com> wrote in message
news:uYUkuAojEHA.3972@.tk2msftngp13.phx.gbl...
> Jonas,
> You may find my post useful
> http://prologika.com/blog/archive/2004/08/20/161.aspx
> Let me know if you find discrepancies.
> --
> Hope this helps.
> ----
> Teo Lachev, MCSD, MCT
> Author: "Microsoft Reporting Services in Action"
> Publisher website: http://www.manning.com/lachev
> Buy it from Amazon.com: http://shrinkster.com/eq
> Home page and blog: http://www.prologika.com/
> ----
> "Jonas Larsen" <Jonas.Larsen@.Alcan.com> wrote in message
> news:O9WLxgkjEHA.1136@.tk2msftngp13.phx.gbl...
> > Hi guys
> >
> > We are closing down our old test server. This server used to run RS. Is
> > there any documentation on how to move the RS installation to another
> server
> > so that Reports, Schedules etc. is intact and ready for use on the new
> > server?
> >
> > Thanks.
> >
> > Regards
> > Jonas
> >
> >
>

Wednesday, March 7, 2012

Changing parameters causes refresh...

Hey Guys,

I have two parameters on a report, which are independent of one another, yet when I change one, the current report refreshes, and I'm left with a blank screen. What is causing the refresh behavior on this report? It appears as though it is treating them as cascading parameters, but I don’t use one to filter the other. Since the report has default values, when first navigating to the report, it displays just fine. I would like the user to be able to change the first parameter without the report refreshing to a blank screen. Once it refreshes, they can click on the “View Report” button and see the report, but I would like for the current report to stay there, which is the case on my other reports, until they click the button. Any ideas?

Thanks - Dan

Send the code of your parameters datasets. they shouldnt refresh if they are not referenced|||

Any news on this, I have Same problem.

SQL Server 2000 SP3

Data is retrieved from OLAP cube, some parameters are also retrieved from olap cube and I have also some non-queried parameters and all parameters have non- queried defaults with expressions and custom codes.

When I change a selection the parameters are refreshed and defaulted.

Any Idea?

My Parameters Data Sets:

SELECT
{RS_ns_PX_Time_Hier} ON COLUMNS,
{[Time].Members} ON ROWS
FROM
RSParams

SELECT
{RS_ns_PX_Currency_Hier} ON COLUMNS,
{[Currency].Members} ON ROWS
FROM
RSParams

My Main Data Set :

"WITH SET [Period1] AS '{"+ Parameters!px_Time_Hier1.Value +"}' MEMBER [Measures].[SalesP1] AS 'SUM([Period1] ,[Measures].Sales)' MEMBER [Measures].[SalesP2] AS 'SUM({" + Parameters!CompareBy.Value+ "},[Measures].Sales)' MEMBER [Measures].[SalesP3] AS 'SUM({ParallelPeriod(Year)} ,[Measures].Sales)' MEMBER [Measures].[SalesP4] AS 'SUM({PeriodsToDate("+Parameters!CompareBy.Label+",ParallelPeriod(Year))} ,[Measures].Sales)' MEMBER [Measures].[CIPP1] AS 'SUM([Period1] ,[Measures].CIP)' MEMBER [Measures].[CIPP2] AS 'SUM({" + Parameters!CompareBy.Value+ "},[Measures].CIP)' MEMBER [Measures].[Open Orders] AS 'SUM({[Time].[All Time]},[Measures].[Open Order])' MEMBER [Sales Category].[SalesCat] AS 'Aggregate({"+ Parameters!SalesCat.Value +"})' SELECT NONEMPTYCROSSJOIN({" + Parameters!px_Subsidiary_Hier.Value +"},{[Country].[Purc Country].Members} ,{Descendants([Item].[All Item],[Item].[Commodity],LEAVES)} ) ON ROWS, {[Measures].[SalesP1], [Measures].[CIPP1],[Measures].[SalesP2], [Measures].[CIPP2],[Measures].[SalesP3],[Measures].[SalesP4],[Measures].[Open Orders]} ON COLUMNS , [Period1] ON PAGES FROM Cost WHERE ([Sales Category].[SalesCat], " + Parameters!px_Currency_Hier1.Value + ")"

|||

It sounds like the dataset queries used as valid value / default value of the second parameter use complex expressions (e.g. with string concatenation) and could potentially call custom assemblies etc. which could indirectly reference the value of the first report parameter - this is why the parameters are treated as cascading parameters.

You said the two report parameters are independent - in that's really the case, you could just switch the order of the two parameters to solve the issue.

-- Robert

|||Actually, there were no complex expressions involved. The datasets only took one parameter, which was a hard-coded number. I tried switching the the order of the parameters, and it didn't help. What ended up helping was to create a report parameter specifically for the dataset to pass as its parameter, instead of using the hard-coded parameter directly in the dataset. Not sure why that helped, but it did.|||It is related with default values, expression type defaults cause this
eventhough expression does not refer to another parameter. I have changed my
default values to queried defaults and the problem has gone.

Not sure if this is documented, I have checked BOL and could not find
anything maybe somewhere else.

Regards,|||

I'm facing the same problem. My reportViewer web-control is 'refreshed', but left blanc (white) when I leave a parameter input-box (a datetime box).

There is no referenses between my parameters and non of them has 'hardcoded' default-values (except for some that defaults to null). However, some datasets uses the parameters as input to stored procedures.

When the exact same report is viewed from a reportViewer in a winform the report is left intact after the parameters are changed and the report only updates if the 'view report' or 'refresh' button is pressed. So, here I'm not left with a blanc (white) report.

Can I solve this problem in some way?

Regards Andreas

Changing parameters causes refresh...

Hey Guys,

Thanks - Dan

Send the code of your parameters datasets. they shouldnt refresh if they are not referenced|||

Any news on this, I have Same problem.

SQL Server 2000 SP3

When I change a selection the parameters are refreshed and defaulted.

Any Idea?

My Parameters Data Sets:

SELECT
{RS_ns_PX_Time_Hier} ON COLUMNS,
{[Time].Members} ON ROWS
FROM
RSParams

SELECT
{RS_ns_PX_Currency_Hier} ON COLUMNS,
{[Currency].Members} ON ROWS
FROM
RSParams

My Main Data Set :

|||

You said the two report parameters are independent - in that's really the case, you could just switch the order of the two parameters to solve the issue.

-- Robert

Not sure if this is documented, I have checked BOL and could not find
anything maybe somewhere else.

Regards,|||

I'm facing the same problem. My reportViewer web-control is 'refreshed', but left blanc (white) when I leave a parameter input-box (a datetime box).

Can I solve this problem in some way?

Regards Andreas

Changing parameters causes refresh...

Hey Guys,

Thanks - Dan

Send the code of your parameters datasets. they shouldnt refresh if they are not referenced|||

Any news on this, I have Same problem.

SQL Server 2000 SP3

When I change a selection the parameters are refreshed and defaulted.

Any Idea?

My Parameters Data Sets:

SELECT
{RS_ns_PX_Time_Hier} ON COLUMNS,
{[Time].Members} ON ROWS
FROM
RSParams

SELECT
{RS_ns_PX_Currency_Hier} ON COLUMNS,
{[Currency].Members} ON ROWS
FROM
RSParams

My Main Data Set :

|||

You said the two report parameters are independent - in that's really the case, you could just switch the order of the two parameters to solve the issue.

-- Robert

Not sure if this is documented, I have checked BOL and could not find
anything maybe somewhere else.

Regards,|||

I'm facing the same problem. My reportViewer web-control is 'refreshed', but left blanc (white) when I leave a parameter input-box (a datetime box).

Can I solve this problem in some way?

Regards Andreas

Saturday, February 25, 2012

Changing my database path in web.config

Hey guys,

I uploaded my website from VWD Express to ipower. My database did not work, which I have learned upon some research should have not been a shock to me, but most definetely was. When I contacted tech support they told my that my web.config files were pointing to my local paths. That I should change them and I should be fine. Elsewhere, in the knowledge base I found the strConnect that I am under the impression I need to use.

strConnect = "Provider=SQLOLEDB; Server=SQL-A1; UID=account_username; PWD=account_password; Database=user_db_name"
oSQLServer.Connect strServer,strLogin,strPwd

The problem is, I'm a complete newbie, learning as I go and I can't find anything on where or how I insert this into my web.config to correct my paths. IPower will not help with coding. I know my current connection string is in web.config in the appsettings, that's about as far as my knowledge goes.

So, could anyone show me how I go about putting this into my web.config? Examples are very helpful :]

On a side note, I've been reading that this is a common problem among newbies like myself. So, is changing these files actually going to work, or am I wasting my time? Also, before I get knee-deep into it, is the database-publishing kit put out by MS the way to go here?

Thanks for any and all replies, you guys are always awesome.

your HOST company should have either emailed you or given you information about your database access and login information. you basically change the Server, Username, and password values in your connection string in web.config to match this.

heres a good article about connection strings in web.config .

http://weblogs.asp.net/owscott/archive/2005/08/26/Using-connection-strings-from-web.config-in-ASP.NET-v2.0.aspx

|||

I know my account id, password, and I think that I can replace. I looked at the article you gave me but, what I'm still confused about is this (what I do have and what the examples in the article look like):

<add name="ConnectionString" connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\ASPNETDB.MDF;Integrated Security=True;User Instance=True"

providerName="System.Data.SqlClient" />

doesn't look anything like this: (what IPower shows me I need to connect my sql database)

strConnect = "Provider=SQLOLEDB; Server=SQL-A1; UID=account_username; PWD=account_password; Database=user_db_name"

oSQLServer.Connect strServer,strLogin,strPwd

Is this strConnect not what I really need? I have no idea what I'm supposed to be doing Confused

|||

the strConnect is the code you would use if it declare your connection in your code and NOT in the web.config file. So everytime you wanted to access the database you would put line of code. By placing the connection in the web.config if you change sqlservers you only need to change it in one location and not every time you make a call to the server. Here is what my web.config file looks like as far as a connection string (using your info above - not sure about the server name).

<addname="MydbConnection"connectionString="Data Source=SQL-A1;Initial Catalog=user_db_name;User ID=account_username;Password=account_password;"providerName="System.Data.SqlClient"/>

</connectionStrings>

then add this code to your project to access the conenction.

Dim dbConnAs SqlConnection =New SqlConnection(ConnectionStrings("MydbConnection").ConnectionString)

then it would be like

dbconn.open()

|||

Thank you very much for trying to help.

strConnect is out the window.

I added the connection string in the web.config

grbourque:
then add this code to your project to access the conenction.
Dim dbConnAs SqlConnection =New SqlConnection(ConnectionStrings("MydbConnection").ConnectionString)

Where? I tried to put that in web.config as well and it didn't want to go there. I tried to put it in the code behind and that didn't work.

I'm sorry I sound like an idiot, I sure feel like one. I have more or less followed some type of tutorial to do everything I've done so far. I'm so frustrated right now that I can't get this to work I can't stand it.

I know your already being very specific and I should be able to pick it up from what you've already told me, but can you tell me where to put the other lines.

|||

Okay a couple things. When you placed the connection string in the web.config file did you keep the same name and only change the user info? If so then the name of that connection string will be MydbConnection. You can actually name it anything you want and have more than one connection if you like, just call up the connection you want by name. Second, I could be completely out to lunch but I do not like the server name they gave you. When I ping that name it comes back as not being able to find it. When I ping the hosted sql server that I am using it comes back with the IP address of that server. So that might be problem #1 and needs to be resolved prior to trying the rest.

Okay as far as the code goes. you would place that where you want to access the database.

private sub GetData()

Dim dbconn as sqlconnection = new sqlconnection(connectionstrings("MydbConnection").ConnectionString)
Dim dbReaderAs SqlDataReader
Dim dbQueryAs String ="SELECT * FROM Table"
Dim dbCommandAs New SqlCommand(dbQuery, dbConn)

dbConn.Open()
dbReader = dbCommand.ExecuteReader()

DO something here with reader

dbconn.close()

End Sub

|||

hi want to just use access db for smaple comments how to use server.mappath in web.config or in tire application classes thanks in advance

Sunday, February 19, 2012

Changing from dropdowns to datepickers

Hi guys,

just a simple question here: i have some input parameters on my report that are datetimes (i.e. the user gets the date picker to select the date), if i want to use that parameter in a MDX statement what will it look like? IOW would today's date look like the string "13/02/07", or would i be expecting a string like this: "2007/02/13 00:00"?

I am looking to convert some dropdowns that contain dates extracted from a cube heirarchy with the datepickers so i need to know what i have to change in the MDX to accomodate this.

I also filter the dates that appear in the current dropdowns, is there a way to do this with the datepickers (maybe by pointing them to a dataset of dates extracted from the cube)?

Thanks!

sluggy

This report sample may help.

<?xml version="1.0" encoding="utf-8"?>

<DataSourceReference>AdventureWorksAS</DataSourceReference>

<rd:DataSourceID>aad8c909-02c6-4fb6-841c-0557ea327ec4</rd:DataSourceID>

</DataSource>

</DataSources>

<DataType>String</DataType>

<Value>[Product].[Product Categories].[Category].&[1]</Value>

</Values>

</DefaultValue>

<Prompt>Product Categories</Prompt>

<DataSetName>ProductProductCategories</DataSetName>

<ValueField>ParameterValue</ValueField>

<LabelField>ParameterCaptionIndented</LabelField>

</DataSetReference>

</ValidValues>

</ReportParameter>

<DataType>DateTime</DataType>

</Values>

</DefaultValue>

</ReportParameter>

</ReportParameters>

<rd:DrawGrid>true</rd:DrawGrid>

<rd:SnapToGrid>true</rd:SnapToGrid>

<Body>

<rd:DefaultName>textbox7</rd:DefaultName>

<Style>

</Style>

</Textbox>

</ReportItems>

</TableCell>

<rd:DefaultName>textbox8</rd:DefaultName>

<Style>

</Style>

</Textbox>

</ReportItems>

</TableCell>

<rd:DefaultName>textbox9</rd:DefaultName>

<Style>

</Style>

</Textbox>

</ReportItems>

</TableCell>

<rd:DefaultName>textbox6</rd:DefaultName>

<Style>

</Style>

</Textbox>

</ReportItems>

</TableCell>

<rd:DefaultName>textbox12</rd:DefaultName>

<Style>

</Style>

</Textbox>

</ReportItems>

</TableCell>

</TableCells>

</TableRow>

</TableRows>

</Footer>

<rd:DefaultName>textbox16</rd:DefaultName>

<Style>

</Style>

<Value>Category Total:</Value>

</Textbox>

</ReportItems>

</TableCell>

<rd:DefaultName>textbox17</rd:DefaultName>

<Style>

</Style>

</Textbox>

</ReportItems>

</TableCell>

<rd:DefaultName>textbox18</rd:DefaultName>

<Style>

</Style>

</Textbox>

</ReportItems>

</TableCell>

<Style>

<TextAlign>Right</TextAlign>

</Style>

<Value>=Sum(Fields!InternetSalesAmount.Value)</Value>

</Textbox>

</ReportItems>

</TableCell>

<Style>

<TextAlign>Right</TextAlign>

</Style>

<Value>=Sum(Fields!ResellerSalesAmount.Value)</Value>

</Textbox>

</ReportItems>

</TableCell>

</TableCells>

</TableRow>

</TableRows>

</Footer>

<rd:DefaultName>Category</rd:DefaultName>

<Style>

</Style>

<Value>=Fields!Category.Value</Value>

</Textbox>

</ReportItems>

</TableCell>

<rd:DefaultName>textbox11</rd:DefaultName>

<Style>

</Style>

</Textbox>

</ReportItems>

</TableCell>

<rd:DefaultName>textbox13</rd:DefaultName>

<Style>

</Style>

</Textbox>

</ReportItems>

</TableCell>

<rd:DefaultName>textbox14</rd:DefaultName>

<Style>

</Style>

</Textbox>

</ReportItems>

</TableCell>

<rd:DefaultName>textbox15</rd:DefaultName>

<Style>

</Style>

</Textbox>

</ReportItems>

</TableCell>

</TableCells>

</TableRow>

</TableRows>

</Header>

<GroupExpression>=Fields!Category.Value</GroupExpression>

</GroupExpressions>

</Grouping>

</TableGroup>

<rd:DefaultName>textbox26</rd:DefaultName>

<Style>

</Style>

</Textbox>

</ReportItems>

</TableCell>

<rd:DefaultName>textbox27</rd:DefaultName>

<Style>

</Style>

<Value>Subcategory Total:</Value>

</Textbox>

</ReportItems>

</TableCell>

<rd:DefaultName>textbox28</rd:DefaultName>

<Style>

</Style>

</Textbox>

</ReportItems>

</TableCell>

<rd:DefaultName>InternetSalesAmount_1</rd:DefaultName>

<Style>

<TextAlign>Right</TextAlign>

</Style>

<Value>=Aggregate(Fields!InternetSalesAmount.Value)</Value>

</Textbox>

</ReportItems>

</TableCell>

<rd:DefaultName>ResellerSalesAmount_1</rd:DefaultName>

<Style>

<TextAlign>Right</TextAlign>

</Style>

<Value>=Sum(Fields!ResellerSalesAmount.Value)</Value>

</Textbox>

</ReportItems>

</TableCell>

</TableCells>

</TableRow>

</TableRows>

</Footer>

<rd:DefaultName>textbox21</rd:DefaultName>

<Style>

</Style>

</Textbox>

</ReportItems>

</TableCell>

<rd:DefaultName>Subcategory</rd:DefaultName>

<Style>

</Style>

<Value>=Fields!Subcategory.Value</Value>

</Textbox>

</ReportItems>

</TableCell>

<rd:DefaultName>textbox23</rd:DefaultName>

<Style>

</Style>

</Textbox>

</ReportItems>

</TableCell>

<rd:DefaultName>textbox24</rd:DefaultName>

<Style>

</Style>

</Textbox>

</ReportItems>

</TableCell>

<rd:DefaultName>textbox25</rd:DefaultName>

<Style>

</Style>

</Textbox>

</ReportItems>

</TableCell>

</TableCells>

</TableRow>

</TableRows>

</Header>

<GroupExpression>=Fields!Subcategory.Value</GroupExpression>

</GroupExpressions>

</Grouping>

</TableGroup>

</TableGroups>

<rd:DefaultName>textbox5</rd:DefaultName>

<Style>

</Style>

</Textbox>

</ReportItems>

</TableCell>

<rd:DefaultName>textbox22</rd:DefaultName>

<Style>

</Style>

</Textbox>

</ReportItems>

</TableCell>

<rd:DefaultName>Product</rd:DefaultName>

<Style>

</Style>

<Value>=Fields!Product.Value</Value>

</Textbox>

</ReportItems>

</TableCell>

<rd:DefaultName>InternetSalesAmount</rd:DefaultName>

<Style>

<TextAlign>Right</TextAlign>

</Style>

<Value>=Fields!InternetSalesAmount.FormattedValue</Value>

</Textbox>

</ReportItems>

</TableCell>

<rd:DefaultName>ResellerSalesAmount</rd:DefaultName>

<Style>

<TextAlign>Right</TextAlign>

</Style>

<Value>=Fields!ResellerSalesAmount.Value</Value>

</Textbox>

</ReportItems>

</TableCell>

</TableCells>

</TableRow>

</TableRows>

</Details>

<rd:DefaultName>textbox1</rd:DefaultName>

<Style>

</Style>

<Value>Category</Value>

</Textbox>

</ReportItems>

</TableCell>

<rd:DefaultName>textbox2</rd:DefaultName>

<Style>

</Style>

<Value>Subcategory</Value>

</Textbox>

</ReportItems>

</TableCell>

<rd:DefaultName>textbox3</rd:DefaultName>

<Style>

</Style>

<Value>Product</Value>

</Textbox>

</ReportItems>

</TableCell>

<rd:DefaultName>textbox4</rd:DefaultName>

<Style>

<TextAlign>Right</TextAlign>

</Style>

<Value>Internet Sales Amount</Value>

</Textbox>

</ReportItems>

</TableCell>

<rd:DefaultName>textbox10</rd:DefaultName>

<Style>

<TextAlign>Right</TextAlign>

</Style>

<Value>Reseller Sales Amount</Value>

</Textbox>

</ReportItems>

</TableCell>

</TableCells>

</TableRow>

</TableRows>

</Header>

</TableColumn>

</TableColumn>

</TableColumn>

</TableColumn>

</TableColumn>

</TableColumns>

</Table>

</ReportItems>

</Body>

<rd:ReportID>03e1fd95-077c-4176-a8c7-4d09893efe4e</rd:ReportID>

<Query>

<rd:SuppressAutoUpdate>true</rd:SuppressAutoUpdate>

<CommandText>WITH MEMBER [Measures].[Total Sales] AS '[Measures].[Internet Sales Amount] + [Measures].[Reseller Sales Amount]' SELECT NON EMPTY { [Measures].[Internet Sales Amount], [Measures].[Reseller Sales Amount], [Measures].[Total Sales] } ON COLUMNS, NON EMPTY {[Product].[Product Categories].[Subcategory].ALLMEMBERS, ([Product].[Product Categories].[Product].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM ( SELECT (STRTOMEMBER(@.DateDate, CONSTRAINED) ) ON COLUMNS FROM ( SELECT ( STRTOSET(@.ProductProductCategories) ) ON COLUMNS FROM [Adventure Works])) WHERE ( STRTOMEMBER(@.DateDate)) CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS</CommandText>

<Value>=Parameters!ProductProductCategories.Value</Value>

</QueryParameter>

<Value>="[Date].[Date].[" & CDate(Parameters!DateDate.Value).ToString("MMMM d, yyyy") & "]"</Value>

</QueryParameter>

</QueryParameters>

<DataSourceName>AdventureWorksAS</DataSourceName>

<rd:MdxQuery><QueryDefinition xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="http://schemas.microsoft.com/AnalysisServices/QueryDefinition"><CommandType>MDX</CommandType><Type>Query</Type><QuerySpecification xsi:type="MDXQuerySpecification"><Select><Items><Item><ID xsi:type="Level"><DimensionName>Product</DimensionName><HierarchyName>Product Categories</HierarchyName><HierarchyUniqueName>[Product].[Product Categories]</HierarchyUniqueName><LevelName>Category</LevelName><UniqueName>[Product].[Product Categories].[Category]</UniqueName></ID><ItemCaption>Category</ItemCaption><UniqueName>true</UniqueName></Item><Item><ID xsi:type="Level"><DimensionName>Product</DimensionName><HierarchyName>Product Categories</HierarchyName><HierarchyUniqueName>[Product].[Product Categories]</HierarchyUniqueName><LevelName>Subcategory</LevelName><UniqueName>[Product].[Product Categories].[Subcategory]</UniqueName></ID><ItemCaption>Subcategory</ItemCaption><UniqueName>true</UniqueName></Item><Item><ID xsi:type="Level"><DimensionName>Product</DimensionName><HierarchyName>Product Categories</HierarchyName><HierarchyUniqueName>[Product].[Product Categories]</HierarchyUniqueName><LevelName>Product</LevelName><UniqueName>[Product].[Product Categories].[Product]</UniqueName></ID><ItemCaption>Product</ItemCaption><UniqueName>true</UniqueName></Item><Item><ID xsi:type="Measure"><MeasureName>Internet Sales Amount</MeasureName><UniqueName>[Measures].[Internet Sales Amount]</UniqueName></ID><ItemCaption>Internet Sales Amount</ItemCaption><BackColor>true</BackColor><ForeColor>true</ForeColor><FontFamily>true</FontFamily><FontSize>true</FontSize><FontWeight>true</FontWeight><FontStyle>true</FontStyle><FontDecoration>true</FontDecoration><FormattedValue>true</FormattedValue><FormatString>true</FormatString></Item><Item><ID xsi:type="Measure"><MeasureName>Reseller Sales Amount</MeasureName><UniqueName>[Measures].[Reseller Sales Amount]</UniqueName></ID><ItemCaption>Reseller Sales Amount</ItemCaption><BackColor>true</BackColor><ForeColor>true</ForeColor><FontFamily>true</FontFamily><FontSize>true</FontSize><FontWeight>true</FontWeight><FontStyle>true</FontStyle><FontDecoration>true</FontDecoration><FormattedValue>true</FormattedValue><FormatString>true</FormatString></Item><Item><ID xsi:type="Measure"><MeasureName>Total Sales</MeasureName><UniqueName>[Measures].[Total Sales]</UniqueName></ID><ItemCaption>Total Sales</ItemCaption><BackColor>true</BackColor><ForeColor>true</ForeColor><FontFamily>true</FontFamily><FontSize>true</FontSize><FontWeight>true</FontWeight><FontStyle>true</FontStyle><FontDecoration>true</FontDecoration><FormattedValue>true</FormattedValue><FormatString>true</FormatString></Item></Items></Select><From>Adventure Works</From><Filter><FilterItems /></Filter><Calculations /><Aggregates /><QueryProperties /></QuerySpecification><Query><Statement>WITH MEMBER [Measures].[Total Sales] AS '[Measures].[Internet Sales Amount] + [Measures].[Reseller Sales Amount]' SELECT NON EMPTY { [Measures].[Internet Sales Amount], [Measures].[Reseller Sales Amount], [Measures].[Total Sales] } ON COLUMNS, NON EMPTY {[Product].[Product Categories].[Subcategory].ALLMEMBERS, ([Product].[Product Categories].[Product].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM ( SELECT (STRTOMEMBER(@.DateDate, CONSTRAINED) ) ON COLUMNS FROM ( SELECT ( STRTOSET(@.ProductProductCategories) ) ON COLUMNS FROM [Adventure Works])) WHERE ( STRTOMEMBER(@.DateDate)) CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS</Statement><ParameterDefinitions><ParameterDefinition><Name>ProductProductCategories</Name><DefaultValues><DefaultValue>[Product].[Product Categories].[Category].&[1]</DefaultValue></DefaultValues><Caption>Product Categories</Caption><HierarchyUniqueName>[Product].[Product Categories]</HierarchyUniqueName><ParameterValuesQuery><Statement>WITH MEMBER [Measures].[ParameterCaption] AS '[Product].[Product Categories].CURRENTMEMBER.MEMBER_CAPTION' MEMBER [Measures].[ParameterValue] AS '[Product].[Product Categories].CURRENTMEMBER.UNIQUENAME' MEMBER [Measures].[ParameterLevel] AS '[Product].[Product Categories].CURRENTMEMBER.LEVEL.ORDINAL' SELECT {[Measures].[ParameterCaption], [Measures].[ParameterValue], [Measures].[ParameterLevel]} ON COLUMNS , [Product].[Product Categories].ALLMEMBERS ON ROWS FROM [Adventure Works]</Statement><ParameterizedStatement><ReferencedParameters /></ParameterizedStatement></ParameterValuesQuery><MultipleValues>true</MultipleValues></ParameterDefinition><ParameterDefinition><Name>DateDate</Name><DefaultValues><DefaultValue>[Date].[Date].[All Periods]</DefaultValue></DefaultValues><Caption>Date.Date</Caption><HierarchyUniqueName>[Date].[Date]</HierarchyUniqueName><ParameterValuesQuery><Statement>WITH MEMBER [Measures].[ParameterCaption] AS '[Date].[Date].CURRENTMEMBER.MEMBER_CAPTION' MEMBER [Measures].[ParameterValue] AS '[Date].[Date].CURRENTMEMBER.UNIQUENAME' MEMBER [Measures].[ParameterLevel] AS '[Date].[Date].CURRENTMEMBER.LEVEL.ORDINAL' SELECT {[Measures].[ParameterCaption], [Measures].[ParameterValue], [Measures].[ParameterLevel]} ON COLUMNS , [Date].[Date].ALLMEMBERS ON ROWS FROM [Adventure Works]</Statement><ParameterizedStatement><ReferencedParameters /></ParameterizedStatement></ParameterValuesQuery><MultipleValues>true</MultipleValues></ParameterDefinition></ParameterDefinitions></Query></QueryDefinition></rd:MdxQuery>

</Query>

<rd:TypeName>System.String</rd:TypeName>

</Field>

<rd:TypeName>System.String</rd:TypeName>

</Field>

<rd:TypeName>System.String</rd:TypeName>

</Field>

<rd:TypeName>System.Int32</rd:TypeName>

</Field>

<rd:TypeName>System.Int32</rd:TypeName>

</Field>

<rd:TypeName>System.Int32</rd:TypeName>

</Field>

</Fields>

</DataSet>

<Query>

<rd:SuppressAutoUpdate>true</rd:SuppressAutoUpdate>

<CommandText>WITH MEMBER [Measures].[ParameterCaption] AS '[Product].[Product Categories].CURRENTMEMBER.MEMBER_CAPTION'

MEMBER [Measures].[ParameterValue] AS '[Product].[Product Categories].CURRENTMEMBER.UNIQUENAME'

MEMBER [Measures].[ParameterLevel] AS '[Product].[Product Categories].CURRENTMEMBER.LEVEL.ORDINAL'

SELECT {[Measures].[ParameterCaption], [Measures].[ParameterValue], [Measures].[ParameterLevel]} ON COLUMNS ,

DrilldownLevel([Product].[Product Categories].[Category].ALLMEMBERS) ON ROWS

FROM [Adventure Works]</CommandText>

<DataSourceName>AdventureWorksAS</DataSourceName>

<rd:AutoGenerated>true</rd:AutoGenerated>

MEMBER [Measures].[ParameterValue] AS '[Product].[Product Categories].CURRENTMEMBER.UNIQUENAME'

MEMBER [Measures].[ParameterLevel] AS '[Product].[Product Categories].CURRENTMEMBER.LEVEL.ORDINAL'

SELECT {[Measures].[ParameterCaption], [Measures].[ParameterValue], [Measures].[ParameterLevel]} ON COLUMNS ,

DrilldownLevel([Product].[Product Categories].[Category].ALLMEMBERS) ON ROWS

FROM [Adventure Works]</Statement><ParameterDefinitions /></Query></QueryDefinition></rd:MdxQuery>

<rd:Hidden>true</rd:Hidden>

</Query>

<rd:TypeName>System.String</rd:TypeName>

</Field>

<rd:TypeName>System.String</rd:TypeName>

</Field>

<rd:TypeName>System.Int32</rd:TypeName>

</Field>

<rd:TypeName>System.Int32</rd:TypeName>

</Field>

<rd:TypeName>System.Int32</rd:TypeName>

</Field>

<Value>=Space(3*Fields!ParameterLevel.Value) + Fields!ParameterCaption.Value</Value>

</Field>

</Fields>

</DataSet>

</DataSets>

</Report>

|||

Thanks Teo, I'll pull that apart and have a look at it.

Cheers

sluggy

|||

Note that whatever you do with the date parameter, you need to comply with the AS member format, e.g. [Date][Day].[Day].&[20070213]. For the sample report, I think I pulled a little trick where I changed the Value property of the dimension key in the AW Date dimension to load the DateTime value. Also you can try:

1. Using the MDX query designer for your main query, define a date parameter.

2. Edit the report parameter it created. Make it DateTime type. Don't query the database. Uncheck multi-value. Default it to null.

3. Delete the extra dataset it created which would have driven the date parameter.

4. Edit the query parameter and write an expression which converts the DateTime from the parameter into an MDX member name... such as:
="[Date].[Date].&[" & Year(Parameters!DateDate.Value) & Right("0" & Month(Parameters!DateDate.Value),2) & Right("0" & Day(Parameters!DateDate.Value),2) & "]"

Changing Formats

Hey guys,
I have a file that has date formatted like so: 2006-11-16 20:12:00
I would like the dateformat to be like mm/dd/yyy hh:mm

The file is being pulled into a varchar field.... as 2006-11-16 20:12:00
when I do a conversion i can only get it to mon 11,2006

any suggestionsStop loading it into a varchar field. Load it into a datetime column so you can display it however you want.

Friday, February 10, 2012

Changing Data Source of Existing Cube

Hi Guys,

We had existing cubes in our Analysis Server, we were required to move them on another Reporting Server which would be using Data Replicated every night to that server. Problem is now source data is divided into 2 Reporting Database Servers. Table Names/View Names are the same in all the Databases. I just want to change the data source pointing to existing Database to the new Reporting Server. Can you tell me how this can be achieved?

Regards,

Kaushal

I have the same question while i also can't find better ways

in fact I have to create cube for another time to change datasource

|||

Looks like RS-related question.

Moving to reporting services forum.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||By Reporting Server, do you mean SSRS? It looks like you just need to change the connection string inside the SSAS database.

Tuesday, March 27, 2012

Changing the Server Name

Changing the owner of a user-defined data type

Tuesday, March 20, 2012

changing SQL server service account

Sunday, March 11, 2012

Changing Server

Wednesday, March 7, 2012

Changing parameters causes refresh...

Changing parameters causes refresh...

Changing parameters causes refresh...

Saturday, February 25, 2012

Changing my database path in web.config

Sunday, February 19, 2012

Changing from dropdowns to datepickers

Changing Formats

Friday, February 10, 2012

Changing Data Source of Existing Cube

database collation

Blog Archive

About Me