Analysis :: Dynamic MDX Query For YearToDate Total

Jun 17, 2015

I have created cube. 1 fact table and few dimensions including dimDate

I need to create a calculated member for variance.

Variance = SUM([Measures].[Amt]) starting from financial year beginning(2015-04-01 to current date) -
SUM([Measures].[Amt]) for the same period last year(2014-04-01 to current date last year)

How can I create this calculated member?

View 5 Replies


ADVERTISEMENT

Analysis :: MDX Query For Total Profit

Sep 3, 2015

i am getting error for the mdx query fortotal profitis there any other way to write??????

View 2 Replies View Related

Analysis :: Dynamic Declaration Of Variable In MDX Query?

May 27, 2015

Is there a way to write such a query where we can declare the variable dynamically ? Currently I am using the query as shown below :

declare @pYear_Internal as NVarchar(100)
set @pYear_Internal = [D FISCALPERIOD].[FP CODE].[FP CODE]
WITH
MEMBER MEASURES.[REVENUE] AS [Measures].[TOTAL REVENUE]
SET LAST5YEARS AS STRTOMEMBER(@pYear_Internal).Lag(4) : STRTOMEMBER(@pYear_Internal)

[code]....

While executing the above query, getting the error - Query (1, 9) Parser: The syntax for '@pYear_Internal' is incorrect.  It looks like it doesn't recognize DECLARE keyword as it does with SQL queries.  I just want a query that runs directly against the server. 

View 3 Replies View Related

Query By Year Group By Total Students Sort By Total For Each County

Jul 20, 2005

I haven't a clue how to accomplish this.All the data is in one table. The data is stored by registration dateand includes county and number of students brokne out by grade.Any help appreciated!Rob

View 4 Replies View Related

Analysis :: DAX Row Level Multiply First Then Sum Up In Total

Jul 30, 2015

I've got measure a in one table and measure b in another table and calculation a*b.

            a  b   calc

            2  4   8

            5  2   10

SUM     7   6   18

How to get 18 as total instead of 7*6=42. I'd like to get row level calculation to sum up for the total level...

View 3 Replies View Related

Analysis :: Custom Grand Total In SSAS?

Jul 23, 2015

I have a requirement where I need to show the maximum value in grand totals but for the dimension members the same measure has to sum.

For ex: lets say I have a measure called Test and this is a base measure. The aggregation type set  to this is SUM.

For this same measure the grand totals should not show the sum instead it should show the maximum value of the dimension members which is being analyzed across.

View 4 Replies View Related

Analysis :: Total Calculation With Filter For Parallel Period

Nov 30, 2015

I have developed a cube in my work place for analyzing current year sales with previous year sales in Time Hierarchy (Year- Quarter- Month) using Parallel period. If we want to see data for particular Quarters i.e. Q1 and Q2 then total at the year level should also get change. Currently if we only choose 2 quarters in the filter then current year data gets change, however data using parallel period is not getting change accordingly and its shows Total of full year.

View 4 Replies View Related

Analysis :: Total Calculation Incorrect When Using IF In Calculated Member

Oct 27, 2015

I cannot understand why Total of calculated member is displayed incorrect. How should I change calculated member for it to work correctly?

Calculated Member:
     CREATE MEMBER CURRENTCUBE.[Measures].AverageScore
        AS IIF([Measures].[Distance]<2001,0,[Measures].[avgscore]/[Measures].[Date Count]),
     VISIBLE = 1;

It seems that Total is calculated without checking value in "AverageScore" for that month.

View 3 Replies View Related

Analysis :: Order Total For Orders Containing A Specific Product Group?

May 27, 2015

I have a request for being able to show ordertotals for those orders that contains a specific productgroup.

Case

Order 1   ProductGroup   Value
A 20
B 40
C 40
Total 100

Order 2   ProductGroup   Value
A 20
D 40
Total 60

So if i slice OrderTotal by ProductGroup the result will be

A 160
B 100
C 100
D 60
ALL            160

how to solve this.

View 9 Replies View Related

Analysis :: SSAS MDX Running Total Of Items In Current State?

May 22, 2015

Let's have a table to show what I mean ...

As you can see I have added only one contract (for the sake of simplicity). At any given date I want to calculate the running total for a contract (all contracts), but the aggregation must take the contract state into consideration.

You can see my expected result for queries 1 - 3. That means, with the actual state ('B') I want to aggregate all values regardless of their state in the past.

For query 2 the state is 'A' ... I hope you can follow.

Now my aim is to aggregate all contract states for any given date with the right value. The past states of each contract are not relevant. And of course future ones (with respect to the query date) shall also be irrelevant.

Maybe the solution is a combination of 'LastNonEmpty' and SUM/PERIODSTODATE ...

View 5 Replies View Related

Analysis :: How To Block Writing In Total Cells When Use Excel And Write Back

Nov 16, 2015

I have a cube with a partition configures in write back.

Users in Excel need to see the totals of the line. Unfornately they have the bad idea to write in this cell sometimes and not in the leaf cells.

As there is some MDX code behind in the weight expression field, we got some weird values : one is negative and others ARe 10 times the initial value in the total. So it's very dangerous.

How can we block the writing in this totals cells ?

View 2 Replies View Related

Average Value Total Dynamic Ignoring Zeros

Nov 25, 2013

I have this data returning from a query:

Procedure Intermediate New Old Total avg
Proc1 6 0 0 6 2.000000
Proc2 74 13 0 87 29.000000
Proc3 29 0 0 29 9.666666
Proc4 16 0 0 16 5.333333

And I want to dynamically calculate the average rather than divide the total by the number of columns every time, I want to divide only by the number of non zero values per row for intermediate, new and old. So row 1 average would just be itself = 6, row 2 would be 74+13+87/3 etc.

;with cte as
(
select
f.Procedure,
SUM(case when f.old_new = 'O' then f.Value else 0 end) "Intermediate",
SUM(case when f.old_new = 'Y' then f.Value else 0 end) "New",
SUM(case when f.old_new = 'N' then f.Value else 0 end) "Old"

[code]....

View 4 Replies View Related

Analysis :: Show Grand Total When Using SSAS Tabular Role Security Permission

Oct 30, 2015

If user want to see the grand total for a measure with include all members, even though the user has limited access for that member, so how we can do using DAX?For example, let’s say the total revenue for all the divisions in a cube is $15,000. You create a role called “Division A”, and set it up so members of that role can only see the revenue for Division A, which totals $3,000. If you use a front-end tool like Excel to access the cube and use the division hierarchy to see the total revenue, you will see the revenue of $3000 for Division A, but also want to see the Grand Total for the revenue as $15,000How we can achieve above scenerio in tabular model (DAX).

View 3 Replies View Related

Analysis :: Dynamic MDX - Select From 2 Cubes

Sep 2, 2015

I am trying to select from 2 cubes. in one condition - select .. From CubeX, and in sec Condition Select .. From CubeY.

I tried to put the name of the Cube as parameter:
SELECT ...
FROM @CurrentCube

in ssrs source and i got the message:
------------------------------
Failed to parse the query to detect if it is MDX or DMX. Error: 'Query (148, 6) Parser: The syntax for '@CurrentCube' is incorrect.' (MDXQueryGenerator)
------------------------------

Also tried to get the name of the Cube as Member (I have a property with the name of the cube),and than using the member to the select :

WITH MEMBER [CurrentCube] AS
...[dim name].CURRENTMEMBER.PROPERTIES("Cube")
SELECT ...
FROM [CurrentCube]

It didnt work too.. Is it Possible at all make the Cube-name dynamic?!

View 5 Replies View Related

Analysis :: Dynamic Calculated Measure In MDX

May 8, 2015

 I have a dimension called as DIM1 which has list of all measures and has an attribute called as ATTRMDX Formula. The formula will be like

([DIM1].[ATTR Measure Code].&[M1],[Measures].[ATTR MEASURE VALUE])+([DIM1].[ATTR Measure Code].&[M2],[Measures].[ATTR MEASURE VALUE]).

I want to pass this formula to a calculated measure as given below -

MEMBER [Measures].[FormMeasure] as ([Measure].[ATTRMDX Formula].currentmember.MEMBERVALUE)

but I get the string value itself as output, but when I put the formula as a string in the calculated measure I obtain the value.

View 2 Replies View Related

Dynamic Connection String For Analysis Services

Jun 29, 2006

I have a package where I need a dynamic connection string for an Analysis Services connection manager.

I have implemented this successfully for a Text data source, and a SQL data source, but the same approach does not seem to be working for an AS connection.

I set some expressions for the AS connection manager (ServerName, InitialCatalog, even the entire ConnectionString itself), but they don't take. I don't get any errors, but the task processes the cubes for the AS connection as it was established at run time. The design time connection string changes don't appear to get evaluated. This seems to be an issue only for AS connections.

Let me know if you have any ideas - thanks.



View 2 Replies View Related

Analysis :: SSIS Dynamic Database Creation

Jul 31, 2015

I have a requirement where in i need to create a Database dynamically in SSIS, Database name is given in table.

View 3 Replies View Related

Analysis :: Dynamic Default Values For Parameter

Nov 11, 2015

I am trying to write a query for the default values for month, and I am stuck in bringing out only the parameter values, the query I have , brings back the month on row and value on column, and I am trying to get the value out. and will ssrs accept it as a valid expression for a default value

WITH 
MEMBER
[MEASURES].[PARAMETERVALUE]
AS
[Account].[Month Short Name].CURRENTMEMBER.UNIQUENAME

[Code] .....

I tried using the exp

=Parameters!MONTH.Value(0)

so as to return the first parameter in the parameter dataset , but i have the error the expression that ref the parameter MONTH does not exists in the parameter collection.

View 2 Replies View Related

Analysis :: Dynamic Measure Value Based Upon Role / Department

Sep 28, 2015

I have an issue related to SSAS security. We have an SSAS multidimensional cube which needs 3 types of security:

- Access to the entire cube => OK, based upon a role
- Restricted access to one department (= dimension) => OK, based upon a role
- Access to the entire cube, but with dynamic security on 2 measures.

Let's say, we have 2 departments (food and non-food). Users within food are allowed to see sales and pieces from the food department, but not from the non-food department. 

It is not an option to restrict access to the non-food department because there are other measure which they have access to. I tried cell security, but this is very slow and generates multiple empty rows on my selections.

View 3 Replies View Related

Analysis :: Dynamic KPI Goal - Derive From Source Database

Jul 14, 2015

I have a requirement to make all of my KPI goals configurable by the end user - I would like to add the data to my datawarehouse so that it is updated in the cube whenever the cube is processed. I'm thinking I will need a table for each KPI that includes two columns (I will be setting goal by year):

Year
Value

In my cube I then create a measure group for each KPI (for some I will add a calculations to break down the yearly goal to lower levels of detail).I will add each measure to my Dimension Usage and relate to each of my time dimensions at the Year level.I currently have around a dozen KPIs - my above approach seems a bit messy i.e. requiring a table and measure group for each KPI (I guess I could at least put them all into one table in my relational db). 

View 2 Replies View Related

Analysis :: Tabular Dynamic Security With Multiple Values

Aug 6, 2015

It seems simple but I'm not able to make it works. I've got a fact table with sites (DimImplantation) with allowed users (DimDroit) :

1 user can have access to many sites. I've tried these codes but only one worked and only in case where the user had only one site :

a) =DimImplantation[DimImplantation_ID]= LOOKUPVALUE (DimDroit[DimImplantation_ID]; DimDroit[Utilisateur];USERNAME() ) ==> worked for 1user with 1 site

b) =DimImplantation[DimImplantation_ID]= LOOKUPVALUE (DimDroit[DimImplantation_ID]; DimDroit[Utilisateur];USERNAME(); DimDroit[DimImplantation_ID];DimImplantation[DimImplantation_ID] ) ==> doesn't work and says 'not able to to identifie the value of DimImplantation[DimImplantation_ID]
in the current context.

c) CALCULATE(VALUES(DimDroit[DimImplantation_ID], SUMMARIZE('DimDroit',DimDroit[Utilisateur]), DimDroit[Utilisateur]=USERNAME()) ==> version for  SQL2012. I've had 'End of input reached'.

View 3 Replies View Related

Analysis :: DAX - Dynamic Masking Of Information Based On Role Security

Oct 2, 2015

I have a business requirement to build a tabular data model, where I need to mask information of other Agents from a given Agent but I still need to show the overall sales of the given product. 

For eg: IF an Agent is in APAC region he should see APAC region sales and also should be able see the sales of the same product in other region without knowing region specific break down.

For Agent  "Tom" in APAC region, the numbers will look like this
APAC_Sales = 100,000
Other_Sales = 500,000

And if "John" is in NA region, then the number will look like this for him

NA_Sales     = 200,000
Other_Sales = 400,000

I wanted to create "Roles" based on the Region, so all the agents belong to "APAC" region will have same view as Tom and "NA" region agents will have John's view.

View 2 Replies View Related

Analysis :: SSAS Tabular - Dynamic Security Roles - Mixing Together DAX Filters

Oct 10, 2015

I have two different roles, each one with a dax filter. One is for filtering users that access by Excel, and other for filtering users that access by Reporting Services, respectively:

=CONTAINS(FILTER(V_FILTRO_C;V_FILTRO_C[IDPerfilGrupo]<>7);V_FILTRO_C[iCursoCod];[SK_CURSO];V_D05_DIM_UTIL[DSC_LOGIN_USER];RIGHT(USERNAME();LEN(USERNAME())-SEARCH("";USERNAME())))
=CONTAINS(FILTER(V_FILTRO_C;V_FILTRO_C[IDPerfilGrupo]<>7);V_FILTRO_C[iCursoCod];[SK_CURSO];V_FILTRO_C[IDUtilizador];VALUE(CUSTOMDATA()))

To create only one role that serves Excel and Reporting Services users, is it viable to use only the || (OR) operator?, is there any other regard i should take?

View 2 Replies View Related

Analysis :: Dynamic Values In XMLA To Incrementally Update Cube Processing

Nov 10, 2015

I am trying to incrementally update a Cube to get near real time data for the end users. Currently we have a Sql server agent Job that does a FullProcess on the Cube. The Cube consists of a single Measure group which is simply one named query containing inner joins of all the dimensions and fact tables in the underlying relational database. The end users have a lot to upload during the day and they would like us to refresh the cube (near real time) to ensure the adjustments are loaded so that they could reconcile their daily PnLs. We have a MeasureId added which is an auto increment column in the Measures table.

I am trying to schedule the below XMLA query in Sql server agent Job and schedule it to run every 15mins or even less (if possible). However it seems to be not working and keep throwing all sorts of errors.

DECLARE @LastMeasureId AS INT, @myXMLA nvarchar(max)
SELECT @LastMeasureId = "[Measures].[Maximum Measures Id]" FROM
OpenRowset(
'MSOLAP',
'DATA SOURCE=L68F728326574; Initial Catalog=GMDR;',
'SELECT NON EMPTY {[Measures].[Maximum Measures Id]} ON COLUMNS FROM [GMDR]');

[Code] ....

View 2 Replies View Related

Analysis :: Dynamic Set Needed For Last 12 Months Based On Current Month Selected By User

Sep 30, 2015

I need to create a set so that when a user selects a month in filter (say 201506) then it should give me a list of months from 201406 to 201506. Any appropriate MDX query.

View 7 Replies View Related

Query To Get Total

Feb 12, 2008

Hey all,
I have following query


select Date,

View 5 Replies View Related

Total A Query

May 2, 2007

real simple question. how would i total this query?


select source,dof,count(dof) from xentel_chk group by source,dof order by source,dof

comes out like:

NM | 20070216 | 20
NM | 20070223 | 17

....

View 19 Replies View Related

Get The Grand Total Of This Query

Dec 12, 2003

I need to get the Grand Total of the results of this query.
The query pulls the total customer quotes for each community in a management company and loads my DataGrid:

This query works fine for individual community totals"SELECT TOP 100 PERCENT vcName, COUNT(DISTINCT vcCustId) AS " & _
" Total FROM dbo.PropReportData WHERE cManagementCo = '" & Session("MgmtCo") & "' and " & _
" (vcEntryDate >= CONVERT(DATETIME, '" & StartDate & "', 102)) AND " & _
"(vcEntryDate <= CONVERT(DATETIME, '" & EndDate & " 11:59:59 PM" & "' , 102))GROUP BY vcName ORDER by vcName"

At first glance you would think that the following query would return the Grand Total for all communities in the management company:

Current Grand Total Query"SELECT COUNT(DISTINCT vcCustID) As gTotal FROM PropReportData WHERE
cManagementCo = '" & Session("MgmtCo") & "' AND vcEntryDate >= '" & Session
("StartDate") & "' AND vcEntryDate <= '" & Session("EndDate") & " 11:59:59 PM' "

But here's the problem. If there are multiple customer quotes created for different communites, then the customer(vcCustID) is only counted once in the Grand Total Query because I have to use DISTINCT, which of course only picks up one instance of the customer.

Thanks in advance.

View 2 Replies View Related

Help: Query To Get Total Last 7 Day Sales For Each Day

Aug 5, 2004

Let say I have this table in MS SQL server
table transaction(date,sales)

how to query to get result like this (date,sales,sum(sales last 7 day))
I'm thinking about using self join, but it means I must have to self join 7 times to get the total sales for the last 7 day. Is there any better way to do this? or maybe special function within MS SQL server.

note: i'm not looking for total sales per week group by each week, but total last 7 day sales for each day

thanks

View 2 Replies View Related

T-SQL (SS2K8) :: Query To Get Total At The End

Sep 15, 2014

select col1,count(*) from client1..table1 group by col1
union
select col1,count(*) from client2..table1 group by col1
union
select col1,count(*) from client3..table1 group by col1

The results yields

33915
3405
3412

I am trying to get the following result but can't figure out how to get the total in the end.

33915
3405
3412
Total 22

View 2 Replies View Related

Getting Total Count From Query

Sep 21, 2013

I have to write a query to get the count() of the customer who has max sales in the last 6 months.my query is

Select Inv_Cust,Count(Inv_Cust) as Salescount From Inv_Header Group By Inv_Cust,Inv_Date Having Inv_Date Between MIN(Inv_Date) And DATEADD(MM,6,min(Inv_Date))
which gives me a result like
inv_cust ' Salescount

[code]...

How can I modify my existing query to get this.

View 2 Replies View Related

Total Page Writes/total Amount Of Data Change In A Set Period Of Time

Jun 9, 2004

Does anyone know how I can determine the number of page writes that have been performed during a set period of time? I need to figure out the data churn in that time period.

TIA

View 5 Replies View Related

Aggregated Subquery - Sum Total Trips And Total Values As Separate Columns By Day

Feb 26, 2014

Very new to SQL and trying to get this query to run. I need to sum the total trips and total values as separate columns by day to insert them into another table.....

My code is as follows;

Insert Into [dbo].[CombinedTripTotalsDaily]
(
Year,
Month,
Week,
DayNo,
Day,
Trip_Date,

[Code] .....

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved