Hi,
i created a cube that has 2 measures. I created the measures by selecting the columns from my fact table, but the function that applied in the measures was the sum function. I need to apply the count function in my measure. How can i do that?
I am trying to count a set as a calculated measure, when this set is called directly in the row , it returns fast, but when i try to count the set as calculated measure(so i can slice with another dimension) the query keeps running forever.
The queries are below
select {} on 0, nonempty ( {([Transaction].[RPC Count].&[1],[Transaction].[Account ID].[Account ID])} , {([Account].[PAYMENTSTATUS].&[0],[Account].[Account ID].[Account ID])} ) on 1
I'm using sql server analysis services 2000, i have a problem with grand total when i select more than one member of any demension. the measure of this cube is built on a distinct count agregation function. i have only one measure in th cube
Say that I have employee data for 2010-2015. I have a pivot table that displays employee data for all the different months in 2015 I want each month to show all people who have been employed since the start of time until and including that month. So
for January 2015 it should show all people employed from January 2010 to January 2015. for February 2015 it should show all people employed from January 2010 to February 2015. for March 2015 it should show all people employed from January 2010 to March 2015.
How can I write a measure for this? I have a date table set up.
I want to count the rows in the Incident Table by using filters to limit the rows to be counted if they meet the below conditions. I know I need a logical test for each row of the incident table based on the apparatus table’s rows. But, I want to test for each row in the incident table, counting, but not returning a true or false in the overall measure.Something like look at each incident row, test for true or false and then count IF the statement is true. Then go to the next incident row and do the same. The aggregation would be the final count of “true” results.I tried this for MET objective:
set buyerset as exists(dimcustomer.leval02.allmembers,custoertypeisRetailers,"Sales") set saleset(buyerset) set custdimensionfilter as {custdimensionmemb1,custdimensionmemb2,custdimensionmemb3,custdimensionmemb4} set finalset as exists(salest,custdimensionfilter,"Sales") Set ProdIP as dimproduct.dimproduct.prod1 set Othersset as (cyears,ProdIP) (exists(([FINALSET],Othersset,dimension2.dimension2.item3),[DimCustomerBuyer].[ParentPostalCode].currentmember, "factsales")).count
I have a Problem with cumulative totals of DistinctCount of customers while working in the MDX-Query-Designer of Analysis Services. The measure [Measures].[DC Customer] is a distinct count of CustomerNr of Customers who have purchased products. I use
With Member [Measures].[DC Custumer kum] As 'Sum (Periostodate([Time].[Year]),[Measures].[CD Customer kum]' Select {[Measures].[DC Custumer],[DC Custumer kum]} on colums, [Productgroup].[Maingroup].members on rows From Sales Where [Time].[All_Time].[2005].[March]
but it returns the sum of [Measures].[DC Customer] and added the January, February and March value. I need to get the value of customers who have purchased products from january to march.
I'm working on a measure which I'd like to use in PowerView, my data model contains 2 things:
1) Employees' scores 2) Stores in which employees work in
I would like to be able to put the employee's score in comparison to the store average (which is the average of all the employee's scores in the store), and then provide every employee with an individual report on their scores against their store's score (by tiling the view in PowerView by Employee). To do this, my (first attempt) at a measure for the store average looks like the following:
Which gives me the score of the employee vs. all other employees across all stores. What I'd like to then achieve is to add an additional filter that filters only the scores for the store that the employee belongs to - which is where I'm stuck.
works perfectly, but doesn't give me the desired output, which is 1 measure that changes the store depending on which employee (and thus store) is being selected.
In my cube there are two measures which are used in different calculations.Now I'm need to show in report if there any months in data when both or even another one of the measures is not updated (value = 0 or NULL).
how should I create the calculated measure for that?
I have tried in mgmt studio to plan this but I'm in a loop of errors.
I am trying to script a case when to achieve the following.
I have a table of measures with certain threshold. The threshold direction can either be > or < so I want to create a field that shows if the measure hits that threshold or not to be later picked up in SSRS. So a nested case when?
CASE WHEN M.[Threshold Direction] = '>' THEN CASE WHEN A.[Value] > M.[Threshold] THEN 'GREEN' CASE WHEN A.[Value] < M.[Threshold] THEN 'RED' ELSE '' END END END AS 'Condition'Is this at all possible?
I am wondering how to create a matrix that contains 1 dimension for Top Label (Column), let's say "Year-Month" and then 2 Measure to be in the row format rather than columnar format. Example as below : Year-Month on the column, and the measure is on the row :
2007-04 2007-05 2007-06
Amount Sales 1000 2000 3000
Unit Sales 10 20 30
Total 1010 2020 3030
Please share with me if you have this solution in Reporting services as it works in excel, hyperion brio, bo, cognos but somehow cannot see that function in Reporting Services.
We are trying to do some utilization calculations that need to factor in a given number of holiday hours per month.
I have a date dimension table (dimdate). Has a row for every day of every year (2006-2015)
I have a work entry fact table (timedetail). Has a row for every work entry. Each row has a worked date, and this column has a relationship to dimdate.
Our holidays fluctuate, and we offer floating holidays that our staff get to pick. So we cannot hard code which individual dates in dimdate as holidays. So what we have done is added a column to our dimdate table called HolidayHoursPerMonth.
This column will list the number of holiday hours available in the given month that the individual date happens to fall within, thus there are a lot of duplicates. Below is a brief example of dimdate. In the example below, there are 0 holiday hours for the month of June, and their are 8 holiday hours for the month of July.
I have a pivot table create based of the fact table. I then have various date slicers from the dimension table (i.e. year, month). If I simply drag this column into the pivot table and summarize by MAX it works when you are sliced on a single month, but breaks if anything but a single month is sliced on.
I am trying to create a measure that calculates the amount of holiday hours based on the what's sliced, but only using a single value for each month. For example July should just be 8, not 8 x #of days in the month.
Listed below is how many hours per month. So if you were to slice on an entire year, the measure should equal 64. If you sliced on Jan, Feb and March, the measure should equal 12. If you were to slice nothing, thus including all 15 years in our dimdate table, the measure should equal 640 (10 years x 64 hours per year).
In SSAS, I want to add a calculated measure and set that measure as the Parent of existing measure. For example I have the measures as A and B from the fact table. Now i want to add a calculated measure as C and set this measure as Parent for measures A and B. How to do that in SSAS.
What I want to do is to query this table to return me a list of product type and category, and the total number of products in each category, and the count of number of instock, and locked.
i.e. Type, Category, TotalCount, NumberInStock, NumberNoStock, NumberLocked, NumberUnlocked.
I need to perform a summation on the results of a COUNT but I'm not sure what's the best way to do it.
Example: TableA --------------- CaseID (int) FollowupCorrespondence (int) (not guaranteed to be sequential) CustomerName (varchar)
/**Get the total number of followups for each customer case, and display the customer's name.*/ select T.caseid, sum(T.cnt), T.CustomerName, T.FollowupCorrespondence from (select CaseID, count(FollowupCorrespondence) as cnt, CustomerName, FollowupCorrespondence from tableA group by caseID, CustomerName, FollowupCorrespondence; ) as T group by caseid, customername, followupcorrespondence
I get the following output, but what I need is to get '4' in each row of the SumCases column. CASEID SUMCases CUSTOMERNAME FOLLOWUPCORRESPONDENCE ---------------------------------------------------------------------------------------------------------------- 1.................1.............John Doe................1 1.................1.............John Doe................2 1.................1.............John Doe................3 1.................1.............John Doe................4
I have created the following query... and need to get the total records display for my report. I have tried adding in the count(*) function to my select list, but I get errors. Any help is appreciated.
select t.value,sum(t.countvalue) as totalcount from (
select
sm.value,count(sm.value) as countvalue
from subjectbase s join stringmap sm on s.organizationid = sm.organizationid
inner join audit a on s.subjectid=a.subjectid
inner join incidentbase i on i.subjectid=s.subjectid
where a.auditid= @audit and (i.modifiedon between @startdate and @enddate) and sm.attributename = 'contractservicelevelcode' and sm.ObjectTypeCode = 112
group by sm.value
) t
group by t.value
)
value totalcount ------------------ NHLBI Employee329 NIH Employee329 Public329 VIP329
instead of different values i m getting same... there is something wrong in joins..can anyone help me?
I am creating a report that will print the customer's Invoices grouped by SalesPerson. I created a variable in the dataset so that it sets to the status of the invoce to Paid, Not Paid and Partial. All is working fine so far. I have a groupfooter for each salesperson where I want to print the total no. of invoices for that SalesPerson that are PAID, NONPAID and Partaial. So on the expressions of that fields, I have the following code. It allows me to print the report, but it prints #Error for the value of thoses field I also want to print the the same fields in the report total area. Any ideas ??
I am trying to divide 2 funcitons by each other and I am coming back with 0 as my result in one of them while I am getting the correct answer in another. The two columns I am trying to pull with functions are:
SELECT
sum(D.PrincipalBal)/sum(L.PrincipalBal)*100 as DelqRatioByBal , count(D.LoanID)/count(L.LoanID)*100 as DelqRatioByCnt
FROM Loan L
INNER JOIN DelqINFO D on D.LoanID = L.LoanID
I get the correct result back for 'DelqRatioByBal' but I get 0 back for 'DelqRatioByCnt'
I need a function to count business days (exclude Sat, Sun) that I can call within a view. I would rather not build a "calendar table" this will be used ongoing for years into the future.
Does anyone have anything like this they could share? If there is another source you could direct me to I would appreciate that as well.
I have data like this in a two column temporary table -ID Age23586 323586 323586 223586 223586 123586 123586 123586 123586 1I need to create a temporary table that look like this:ID Age1 Age2 Age3 Age423586 5 2 2 0However, what I get is this:23586 5 NULL NULL NULL23586 NULL 2 NULL NULL23586 NULL NULL 2 NULLHere is the query that I am using...select managed_object_id, (select count(Age) where Age = 1) As Age1,(select count(Age) where Age = 2) as Age2,(select count(Age) where Age = 3) as Age3,(select count(Age) where Age = 4) as Age4into #enhancementCount from #enhancementsgroup by managed_object_id, AgeWhere's my mistake?Thanks-Danielle
I have a database that contains a column for UnitName , BeginDate andEndDate.I want to pass two parameters (@BeginDate and @EndDate) and retrieve atable of valuesthat include UnitName along with Counts for each UnitName.SELECT UnitName, COUNT(BeginDate) AS Start(SELECT COUNT(EndDate) AS Finish WHERE EndDate BETWEEN @BeginDate AND@EndDate)FROM TableWHERE BeginDate BETWEEN @BeginDate AND @EndDateGROUP BY UnitNameORDER BY UnitNameThis works. But when I try to add another count by using a subselect Iget an error dealing with GROUP BY not including the column in mysubselect.How is the best way to Count two columns using Group By.
I have a really basic question. The following SQL query works for me:
Select EnterUserID, Enterdate from tblCsEventReminders where EnterDate >= Convert(datetime, '2015-04-01')
I am essentially trying to write a query to count the number of user logins after a certain date, so I need to count 'EnterUserID' but I am having problems getting the count() function to work.
This is so complicated (for me) because I usually only work with single table and simple queries (SELECT, INSERT, UPDATE), but now I am in a situation where I am stuck.
What I am trying to archive is that: when a project manager logged-into his/her account, a grid-view will show a quick overview for all of his/her projects (id, created date, name and how many files are in pending) like below picture:
3 tables will be involved are:
Sample data for manager_id = 11
I tried this query but it not worked, it seems to display all columns right but the COUNT pending files column (assume the manager_id = 11)
SELECT COUNT(file_id) as 'Pending files', projects.project_id, projects.project_name, projects.status, projects.start_date FROM ((project_manager INNER JOIN files ON project_manager.mag_id = files.manager_id AND project_manager.mag_id = 11 AND file_status = 'Pending') INNER JOIN projects ON projects.project_id = project_manager.project_id) GROUP BY projects.project_id, projects.project_name, projects.status, projects.start_date ORDER BY projects.status, projects.start_date DESC