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 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
[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.
how to average the best X of Y records in a table for each member. Here's what I have for columns:
Member_ID
Year
Month
MonthlySpend (summary value for month)
Basically, I'm trying to find out the average monthly spend for each member's best 4 of the last 6 months (the best months will vary by member of course). I thought this would be simple (and maybe it is) but I am having difficulty figuring this out. I'm using MS SQL 2000.
Working on a "social networking site" and would like to have something that records a member's interactions within the site.So for instance if a member uploads a photo, I would like to record it and then display it as "[Member] added photo" with a link to the photo. Also, if a friend is accepted as a friend "[Member1] is now friends with [Member2]" and also if a member posts a comment on the forums "[Member] posted message in [ForumName]" with link to post.This is very similar to Facebook's News Feeds and MySpace Friend Updates.The only way I can think of having this is having an Activity table with a field for each different Id I want to record and an associated list of tracked Actions:Activity:idmemberIdactionTypePhotoIdMember2IdForumIdThen there would be the ActionType tableactionIdNameThe biggest problem I see with this solution is that I would need to add a new field in the Activity table for each different type of activity I would like to track. This could get pretty big as I begin to track more and more activity items.Is there some way I can generalize this. - Andy
View 2 Replies View RelatedI have a table that contains a column for a calcuated member (x) of type decimal number. When I tried to display the total of this calculated member in the table footer (=sum(x)), I am getting "#Error" instead of the sum of all displayed calculated values.
Column X
--------------
0
0.67
0.10
0.23
#Error (footer cell, expression -> =Sum(x))
=First(x), =Last(x) and =Max(x) worked fine, not sure why Sum failed. Please help...
Thanks.
I am helping a friend with a gamming website. There are pages which displays data from other clans.
Members need to register and login to view full clan details. User who have not logged in can only view partial data about a clan.
I need to keep track of the kind of hits each page received. That is I want to tell say "Clan X" that these particular members viewed your page these many times and on these dates and these members who have not registered have viewed your page these many times and on these dates.
I am using ASP with MS SQL.
I would like some help on designing the table layout that is efficient for keeping track of the visitors for each page.
Any help will be appreciated.
I have a master table and i need to import the rows into the parent and child table.
Master table name is Flatfile_Inventory
Parent Table name is INVENTORY
Child Tables name are INVENTORY_AMOUNT,INVENTORY_DETAILS,INVENTORY_VEHICLE,
Error details will be goes to LOG_INVENTORY_ERROR
I have 4 duplicate rows in the Flatfile_Inventory which i have already inserted in the Parent and child table.
Again when i run the query using stored procedure,its tells that all the 4 rows are duplicate and will move to the Log_Inventory_Error.
I need is if i have the duplicate rows in the flatfile_Inventory when i start inserting into the parent and child table the already inserted row have the unique ID i must identify it and delete that row in the both parent and chlid table.And latest row must get inserted into the Parent and child table from Flatfile_Inventory.
-- ===============================================================================================
-- STORED PROCEDURE FOR FLATFILE_INVENTORY
-- ===============================================================================================
USE [IconicMarketing]
---=======================================SALES_CURSUR===========================================
--USE IconicMarketing
--GO
DECLARE
@FileType varchar(50) ,
@ACDealerID varchar(50) ,
[code].....
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. 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 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.