for example
with member [Measures].[Accumulated Sales] as 'Sum(YTD(),[Measures].[Store Sales])'
select
{[Measures].[Store Sales],[Measures].[Accumulated Sales]} on columns,
{Descendants([Time].[1997],[Time].[Month])} on rows
from Sales
I need more then 1 member how to do in
Thanks
Hi all,
I installed SQL 2005 SP2 + ReportServices Add-in for Sharepoint (WSS 3.0). All it's OK until I try to grant database access in the Sharepoint Central Admin site.
I setup the Reporting Services Integration (Manage integration settings). I use the default SQL instance, I put the USERNAME and the PASSWORD of my ADMIN account in the ENTER CREDENTIALS windows.. When I click the "OK" button, I receive always the error ...
"A new member could not be added to a local group because the member has the wrong account type"
I tried a lot of things... without success.
Is there someone who can help me....
PS: There is no error in the LOG
Thanks
Hi all,
I installed SQL 2005 SP2 + ReportServices Add-in for Sharepoint (WSS 3.0). All it's OK until I try to grant database access in the Sharepoint Central Admin site.
I setup the Reporting Services Integration (Manage integration settings). I use the default SQL instance, I put the USERNAME and the PASSWORD of my ADMIN account in the ENTER CREDENTIALS windows.. When I click the "OK" button, I receive always the error ...
"A new member could not be added to a local group because the member has the wrong account type"
I tried a lot of things... without success.
Is there someone who can help me....
PS: There is no error in the LOG
Thanks
I need to create a member that one of its Attributes (maybe my term is wrong) is a reference to another entity member named groupÂ
the codeÂ
createRequest.Members.MemberType = MemberType.Leaf;
createRequest.Members.Members = new System.Collections.ObjectModel.Collection<Member> { };
Member aNewMember = new Member();
aNewMember.MemberId = new MemberIdentifier() { Name = uag.groupName, MemberType = MemberType.Leaf };
[code]....
 "The attribute data type is not valid".Is it wrong to add the reference as attribute? How can I embed the reference in the new member?Â
I have a table where I need to delete rows if the member does not exists in the member table. Will the following syntax work ?
Delete T
FROM Event_Temp_Lead_Screen T
left join member M on ( M.MemberID = T.MemberID )
where
T.Gender is NULL
Hi. I am Rabbee from Bangladesh who has join recently with the team. I need a Solution. How can I find out Duplicate data in sql server 200 database by generating a query. Thank you.
fazle rabbee
ive created a photo sharing site, and im trying to show the statisics for certain users, so far i can COUNT all the photos the user has upload, and show them in descending order, but i need to be able to see the ranking of the user, like :
RANK USERID No. Of Photos
1 10 100
2 8 70
3 9 85
is there anyway of doing this? thanks Si!
Hi,
I need some help here. I am trying to do a DTS from As400 from SQL Server 2000. However, the file that I require have members. IF I just do a select statement, it will retrieve the earliest member.
Does anyone know how I can access the latest member?
Thanks a lot!
I have a dimension named Products with the following hierarchy:
Type
Sub Type
Product
Due to requirements I want that a particular product, whose name is, say, "XYZ" should not be displayed when the user drills down to the lowest level i.e. the Product level.
I tried playing with the Advanced Properties tab but could not make any progress and thus will be grateful for help.
Thanks.
Hello,
I have following problem:
A measure in a cube need to be divided by another measure as follows:
MEASURE1MEASURE2
Measure 1 (SUM of Money spent by each person)
Measure 2 (Amount of Money available for each country per person).
Example:
USA: 155
Germany:134
France:143)
Measure1 is a SUM and works fine, but Measure2 should only be a distinct value for each country. So if person comes from Germany, then the SUM of Spent Money should be divided by 134.
Any idea how this can be done.
I have been trying to transfer some data from a file located in a AS400 Server to SQL , but the file has more than one Member Name. I'm not sure how to specify a different member name on the SQL query . Please help.
The name of the file is:
Library = MTGLIBP2
File Name = CHSAVQPL
Member Name = INS
this is the query I have so far but I still need to reference the Member Name
SELECT *
FROM OPENQUERY(AS400PL,'SELECT * FROM mtglibp2.CHSAVQPL')
I have been trying to transfer some data from a file located in a AS400 Server to SQL , but the file has more than one Member Name. I'm not sure how to specify a different member name on the SQL query . Please help.
The name of the file is:
Library = MTGLIBP2
File Name = CHSAVQPL
Member Name = INS
this is the query I have so far but I still need to reference the Member Name
SELECT *
FROM OPENQUERY(AS400PL,'SELECT * FROM mtglibp2.CHSAVQPL')
I need to query data through SSIS from what I was told is an AS400 DB2 member table. I am assuming this is a sub-table of the main table. I was going to write a correlated sub-query in SQL to get this data, however our AS400 contracted programmer says that there is an easier way and she pointed me to the main table's member. I do not know how to go about accessing this.
Has anybody had experience with this? The AS400 programmer is familiar with SQL syntax, however she does not know how to have SQL grab the data from a member table.
If all else fails, I will just construct my correlated sub-query.
Thanks for the information.
Hello,
I have a calculated member that is a ratio calculation. It works fine but I would like that the value for the "All" member to be the sum of the children.
So I have the following MDX where the calculated member [Dim Group Rubric].[Group Rubric Description].[BO + ADM Cost] does not give me good results for the "All" member:
WITH
MEMBER [TOTAL YEAR CONTRACT] AS sum({[Total Year]},[Dim Contract].[Contract Number].[563131])
MEMBER [TOTAL YEAR BU] AS sum ([Total Year])
MEMBER [Dim Group Rubric].[Group Rubric Description].[TOTAL BU PVN] as
([Dim Group Rubric].[Group Rubric Description].[PVN], [TOTAL YEAR BU])
MEMBER [Dim Group Rubric].[Group Rubric Description].[TOTAL BU BO + ADM] as
([Dim Group Rubric].[Group Rubric Description].[BO + ADM], [TOTAL YEAR BU])
MEMBER [Dim Group Rubric].[Group Rubric Description].[BO + ADM Cost] as
([Dim Group Rubric].[Group Rubric Description].[PVN], [TOTAL YEAR CONTRACT])
/ ([Dim Group Rubric].[Group Rubric Description].[TOTAL BU PVN], [TOTAL YEAR BU])
* ([Dim Group Rubric].[Group Rubric Description].[TOTAL BU BO + ADM], [TOTAL YEAR BU])
SELECT
NON EMPTY {[TOTAL YEAR CONTRACT],[TOTAL YEAR BU]}
ON 0,
{[Dim Group Rubric].[Group Rubric Description].[BO + ADM Cost]}
*
UNION(
[Dim BV Organisation Finance].[Business Unit].[All]
,FILTER(
[Dim BV Organisation Finance].[Business Unit].children
,[Dim BV Organisation Finance].[Business Unit Code].currentmember.name
= '071MIND1'
)
,FILTER(
[Dim BV Organisation Finance].[Business Unit].children
,[Dim BV Organisation Finance].[Business Unit Code].currentmember.name
<> '071MIND1'
)
)
ON 1
FROM (SELECT ([Dim Year].[Year].&[2008]) ON 0
FROM (SELECT ({[Dim BV Organisation Finance].[Business Unit Code].[071MIND1]
,[Dim BV Organisation Finance].[Business Unit Code].[071DIDD1]}) ON 0
FROM [BV Contract Margin DW]))
When I launch this query I obtain:
TOTAL YEAR BU
BO + ADM Cost All - 45013 (I would like to obtain here - 90651)
BO + ADM Cost 071MIND1 - 35680
BO + ADM Cost 071DIDD1 - 54971
Please do you know what should be changed in this query to obtain the good results?
Thanks.
Guillaume
Hi:
Here is the question:
I add a user with "sp_adduser 'ABC', 'ABC', 'db_datawriter'" to the database role. However, the ABC login and password was rejected from select?
in ASP include file:
strConnect= "driver={SQL Server};server=ServerA;uid=ABC;pwd=ABC;database=Cu stDB"
--Error: [Microsoft][ODBC SQL Server Driver][SQL Server]
--SELECT permission denied on object 'tblCustomer', database 'CustomerDB', owner 'dbo'.
Do I have to give db_datawriter selection permission?
According to the books on line: this role has insert, delete and update rights
thanks
David
Hello everybody .
I have a group - SQL support.I want to give them rights to run any job from EM but I don't want them be a part of sa group
What rights should I give them ?
All existing jobs owned by members of sysadmin group.
Thank you
Hi
Analysis manager won’t let me create dimension unless fact table filed has value init. Other words, how do I create MT dimension(dimension without member)
thanks
Hi,
I have a dimension called customers having the following levels:
ALL
Level 1 CustType
Level 2 Cust Rank
Level 3 Cust Name
Suppose that the possible CustTypes are "Potential", "Active", "Lost".
Is it possible to hide displaying Level 1 and below if the Custtype (Level 1) = "Potential"
I have a
[Project].[Project Status]
there are 4 statuses
Open
Cancelled
Closed Complete
Closed Failed
I want to create a member that groups the closeds..Can you use case statements? case when 'open' then 'open' when 'Cancelled' then 'Cancelled' else 'Closed' end.
I am trying to CREATE a member in a file on the AS400 using SQL. The member name is a variable and will change everytime the Insert command is issued. I have tried using the Alias command, but I am either doing something wrong, or it is not meant to be used to create a member that had not existed prior to the Alias statement.
On the As400, the command to add a member is ADDPFM. Then an Override Data base file command (OVRDBF) is issued to point to the right member.
Any help would be greatly appreciated!
Thanks!
Hello Everyone,
I have report based on a cube and it is working. What it does is display a matrix based on six parameters selected. The matrix displays with the following results (there is some more, but this is the gist):
Job Level Headcount
Partners 102
Managers 85
Associates 98
By clicking on a number it properly navigates to the next report (also based on a cube) and properly passes all of the parameters. All of these paramters are dimensions.
But the rows (Partners, Managers, Associates) are not a parameter in this first report. So when I click a number and it navigates to the next report, it is not passing the JobLevel (Partners, Managers, Associates) I clicked on.
When I add the JobLevel to pass to the parameters in the second report, I can only add it as a Fields!JobLevel.Value, I can't pass it as a Dimension or a Set - which is what I assume the second report needs.
I tried StrToSet in the Navigation expression, but it doesn't recognize StrToSet in that expression.
How can I pass a Field value to populate a parameter in a second report that uses MDX for its parameters?
Thank you for the help.
-Gumbatman
Hi Gurus,
I'm trying to calculate the prior year sales count in a calculated member with the following expression. The cube processes without errors but, the result in PriorYearCount column when i browse the cube is null value for all rows.
-- Prior Yr Sales Count Calculated member
(
[Measures].[W SALES F Count],
ParallelPeriod([Date].[Fiscal Date Hierarchy].[Year]
, 1
, [Date].[Fiscal Date Hierarchy].CurrentMember)
)'
But the same expression works fine when I used it to write query on the cube to get previous year sales count. Following is the query for that. Coundnt understand why the same WITH MEMBER expression used in Calculated member is not working.
With Member [Prior Yr Sales count] As
'(
[Measures].[W SALES F Count]
, ParallelPeriod([Date].[Fiscal Date Hierarchy].[Year]
, 1
, [Date].[Fiscal Date Hierarchy].CurrentMember)
)'
Select
{[Date].[Fiscal Date Hierarchy].[Year].[2004]
, [Date].[Fiscal Date Hierarchy].[Year].[2004].Children} on columns
, {[Measures].[W SALES F Count], [Measures].[Prior Yr Sales count]} on rows
From Cube1
Please correct me if i'm doing anything wrong.
Thank you in advance.
I've got a cube in SSAS 2205 with some financial measures that I want to appear conditionally in an MDX query. Here's what I mean...
The cube measures I'm interested in are actual revenue, forecast revenue and budget revenue. The dimension I'm reporting across is the [Time].[month] member. The report I'm trying to create with this query has each month across the top. But the headers show actual revenue if the current reporting month is on or before the month in question. For future months, we show the forcast revenue if there is any. For any other future revenue, we show the budget measures. So a sample report for April would have columns like...
Code SnippetJanuary February March April May June July August September
Actual Actual Actual Actual Forecast Forecast Forecast Budget Budget
I need to conditionally bring back the right measure and have the measure name (e.g., column header) indicate if it's an actual, forecast or budget value.
Thanks.
Not sure where the problem is The @bucket-size is an integer value being entered by the user.
with
set [end] as strtomember("[REL TURN HRS].[TRN HRS].&[" + @bucket_size + "]")
member measures.bucket1 as
iif( isempty(sum([REL TURN HRS].[TRN HRS].&[0] :
[end],[Measures].[FACT CUT RELEASE Count])),0,sum([REL TURN HRS].[TRN HRS].&[0]:
[end],[Measures].[FACT CUT RELEASE Count]))
SELECT NON EMPTY { measures.bucket1} ON COLUMNS,
NON EMPTY {[CUSTOMER JOB].[Cust-Title-Issue-Job].[JOB_NUMBER].ALLMEMBERS } ON ROWS
FROM [DW INSIGHT]
WHERE ([CUSTOMER JOB].[JOB_NUMBER].&[A403EZ0])
In MSAS 2000, it was possible to drag/drop the member properties in a particular order. This came in handy when showing the properties in the screen tips...like information could be grouped together.
In MSAS 2005, it appears that the member properties are ordered by name and I cannot change the order in the Dimension Structure tab.
How do you change the member property order in 2005?
I have a feeling I'm overlooking something painfully simple here. Inside a Script Task, I'm trying to fill a DataTable with a recordset variable.
oledbAdapter.Fill(dataTable, Dts.Variables("rsSrcTblNames"))
...doesn't work, because Dts.Variables("rsSrcTblNames") is a System.Object, not an ADODB recordset. Sure, whatever. Most of the examples I see online use Me.Variables.rsSrcTblNames, as in:
oledbAdapter.Fill(dataTable, Me.Variables.rsSrcTblNames)
That also doesn't work, which makes less sense to me. The auto-correct highlights "Me.Variables", giving the message "'Variables' is not a member of ScriptTask_34dgf89sghg9a8h...". I guess this makes sense given that I'm in a Script Task, and apparently the syntax I'm trying to use is for Script Components (http://msdn2.microsoft.com/en-us/library/ms136031.aspx). But I'm at a loss how to proceed. Any suggestions would be appreciated.
(more detailed description of situation and code will be posted below)
Folks,
I'm trying to dump DB backups to a shared drive on a non domain memeber.
I'm using a UNC but it still tells me access denied. Is there a way of doing this or am I wasting my time.
Thanks
I have a calculated member that calculatest the prior year sales count and the expression looks like this:
--[Prior Yr Sales Count]
([Measures].[W SALES F Count],
ParallelPeriod([Date].[Fiscal Date Hierarchy].[Year]
, 1
, [Date].[Fiscal Date Hierarchy].CurrentMember))
I can see the values for [Prior Yr Sales Count] in the cube if i filter the cube on any year from Date hierarchy.
But, when i gave
[Measures].[Prior Yr Obl Count]
as KPI Value expression, I dont see any value for the KPI even after filtering on Date hierarchy. The calculated members should act just as any other Measures, so why am I not able to see the prior year sales count for the KPI value?
Correct me if I'm not interpreting the usage of calculated members in KPI value expressions.
thanks in advance.
Hi
I need to calculate an average over a dimesion other than time and "average of children" won't do that for me. I'm aware I can create a calculated member where I divide a sum by a count but the problem I have is I need use this calculation in a performance point scorecard and I want to drill down on the measure and you can't do this on calculated members.
Is there any other way of obtaining an this type of average?
Thanks in advance.
Hi,
I have a login 'testuser' on test server.
This login was 'db_datareader' for database called 'invent_test' on test server.
I deleted the whole database and restored the production backup as 'invent_test' database.
Now I am trying to modify login 'testuser' by adding it as 'db_datareader' for 'invent_test' again but it gives me an error that 'this user or role already exists for current database'.
I can not see 'db_datareader' or even 'invent_test' options selected in login properties for 'testuser'. Also I can not find 'testuser' in database roles
-->db_datareader member list.
Is this a bug with sql server 7.0? Or how should I give this user priviledges to read?
I dropped the login and recreated it. Server is also rebooted meanwhile so there is no possibility for cashe problem. Still same error is there.
Is there any thing that I am missing here?
I really appreciate your help. I am very frustrated now.
How to determine to a login is a member of SERVER ROLE or DB ROLE?
I want to query all users are member of SYSADMIN, how to do?
Hi,
I am really stuck here and need some help.
The scenario is that I have a dimension called Product... each product is given points and this is defined in the underlying product table.
The table on which my fact is based has a field called QtySold (which is the qty sold for the product). WhatI want is to somehow use Calculated Member (if that is the right solution) so that for any selected product, the user get's to see a calculated field called, say' Points earned, which will be QtySold multiplied by the points for that product.
Hoping that my question is clear, can someone please help me find a solution.
Billions of thanks in advance.
I've been having difficulties with the following mdx query:
WITH
MEMBER [Measures].[Group] AS
'Mid(MemberToStr([PhaseTask].CurrentMember),30,1)'
MEMBER [Measures].[LawFirmDesc] AS 'MemberToStr([Lawfirm].CurrentMember)'
SET [ClaimCloseDate_Set] AS '{[ClaimCloseDate].[All ClaimCloseDate].[2004] }'
MEMBER [ClaimCloseDate].[All ClaimCloseDate].[SelectedClaimCloseDates] AS
'Aggregate([ClaimCloseDate_Set])'
SET [Jurisdiction_Set] AS
'{
[Jurisdiction].[All Jurisdiction].[State]
}'
MEMBER [Jurisdiction].[All Jurisdiction].[SelectedJurisdiction] AS
'Aggregate([Jurisdiction_Set])'
MEMBER [Measures].[ClaimCount] AS
'Count(NonEmptyCrossJoin(Descendants(
[Case].CurrentMember,,LEAVES),
[ClaimCloseDate_Set],
[Jurisdiction_Set],
{[PhaseTask].[All PhaseTask]},
{[Lawfirm].CurrentMember}, 1))'
SELECT {
[Measures].[LawFirmDesc],
[Measures].[Group],
[Measures].[ClaimCount],
[Measures].[Legal Fees Approved],
[Measures].[Expenses Approved],
[Measures].[Hours Approved]} ON COLUMNS,
CrossJoin (
{
[PhaseTask].[All PhaseTask].[L100 Assessment].Children,
[PhaseTask].[All PhaseTask].[L200 Pre-Trial].Children,
[PhaseTask].[All PhaseTask].[L300 Discovery].Children,
[PhaseTask].[All PhaseTask].[L400 Trial].Children,
[PhaseTask].[All PhaseTask].[L500 Appeal].Children,
[PhaseTask].[All PhaseTask].[E100 Expenses].Children,
[PhaseTask].[All PhaseTask].[Indemnity],
[PhaseTask].[All PhaseTask].[Prior Legal Fees]
},
{[Lawfirm].[All Lawfirm]
}
) ON ROWS
FROM Datamart_Vis_Ins165_Claim
WHERE
(
[Case].[All Case].[C],
[FeeAgreement].[All FeeAgreement].[Hourly + Expenses],
[ClaimCloseDate].[All ClaimCloseDate].[2004],
[Jurisdiction].[All Jurisdiction].[State]
)
The problem is [Claim Count]. I want the claim count on each row to be the highest claim count from selected rows and placed on each row. The count is way under-reported. However, If I use a simple count statement or distinct count then only the last 2 rows have the count value I am looking for. It doens't seem to be affected by the where clause or the aggregated sets so it is over-reporting on the claim count. Anyone have any thoughts on how to solve this problem?
Basically, I'm cross joining law firms with tasks that show expenses and fees submitted by law firms for billing. I need to calulate averages by dividing the dollar amounts by a claim count. The twist is that the claim count I am looking for only occurs on the indemnity and prior legal fees rows because I know that every claim/case has an indemnity and prior-legal fee transaction in the fact table. The others types of transactions don't. So I have to assume the claim count is max claim count calculated on one of the rows and that number needs to be applied to the rest. Also, the count has to be adjusted by the slicers in the where clause (when I used a simple count statement it wasn't adjusting as I changed the values in the where clause) The slicers in the where clause change the date, or the jurisdiction as well as other dimensions. Hope this information has helped. Any suggestions would be greatly appreciated.
Hi,
I am really stuck here and need some help.
The scenario is that I have a dimension called Product... each product is given points and this is defined in the underlying product table.
The table on which my fact is based has a field called QtySold (which is the qty sold for the product). WhatI want is to somehow use Calculated Member (if that is the right solution) so that for any selected product, the user get's to see a calculated field called, say' Points earned, which will be QtySold multiplied by the points for that product.
Hoping that my question is clear, can someone please help me find a solution.
Billions of thanks in advance.