Problems With Filters/Drillthrough/calculated Measures
May 20, 2008
Wondering if anyone has any work arounds.
We have a cube that has financial data in it. There are report actions on cells. I want them to be able to drill through the the supporting data stored in the fact table and all the measures I am showing are calculated measures.
Users connect to this cube from Excel 2007. If they use a "Filter" in Excel and do not select "All" or "One" of the members of the filter hierarchy, then the action dissapears. Microsoft explained this is because that cell then has a calculated cordinate and they can't determine the action to perform.
Has anyone experienced a need to do this? Can you provide me alternatives?
Thanks
[Edit]
The functionality I am looking for is not available in the BIDS either (Well it is to a point--if you select the first 2, 3, 4...members in the filter, the action will still appear. It only reads the first one however. If you select the members starting with any but the first, the action disappears).
Chris
View 5 Replies
ADVERTISEMENT
Jan 21, 2008
Hi people
My users are having troubles with link to default drillthrough report when reports are exported to excel (they REALLY don't like this behavior ), so I decided set all of them disabled in report model, this work fine, but calculated field in reports has this drillthrough link.
Let me show you the situation. Entity Product has an UnitaryCost field, I set the EnableDrillthrough Property in False so when I export a report with this field, no link is shown.
But if I create in the report a calculated field Round(UnitaryCost) this field has a Drillthrough Link
Is this the standard and expected behavior? or its simply a BUG?
Have I done something wrong in my model? and in this case, How I can correct this?
regards.
Julio Diaz.
View 1 Replies
View Related
Nov 4, 2014
I'm trying to show measures from 2 measures groups in a drillthrough. Obviously, it's not possible with a standard drillthrough action, but I still hope that I can somehow achieve this with the ASSP GetCustomDrillthroughMDX function.
Speaking in AdventureWorks2008R2 terms: Imagine I have a pivot table with Product Categories in filter (say, filtered on Gloves) and "Internet Sales Amount" as measure. From context menu in Excel I can call the drillthrough action which shows me the individual sales records. I would like to show in drillthrough additionally "End of Day Rate" measure from "Exchange Rates" measure group.
One option would be to join FactInternetSales with FactCurrencyRate and make EndOfDayRate a physical measure in the "Internet Sales" measure group. This is a pretty huge overhead for my scenario and I'd like to avoid this.
Another one would be to call something completely external for a drillthrough (for example, a SRRS Report).
View 3 Replies
View Related
Jun 5, 2015
This is so trivial but I cannot figure out where the calculated fields/measures area is in the PP window. Is there something to toggle it on and off? I used PP in Excel 2013 before and the area I'm talking about was at the bottom of the PP window. Now I'm with a new company who has Excel 2010 so I downloaded the PP add-in for Excel 2010. It is version 10.50.4000.0. This add-in does not have a calculated fields/measures are at the bottom of the PP window. Is it just a matter of 2010 vs. 2013 or is there a way to turn the frame area I'm talking about on?
View 5 Replies
View Related
Oct 7, 2015
I am trying to develop a cube with translated calculated measures in it. I have also translated the all underlying measures/dimensions being used in action for a header.
I can see my translated measures and dimension in excel but when I go to Underlying data, I see default member's names not the translated one. Please note that I have translated all underlying members.
View 4 Replies
View Related
Aug 28, 2015
I am new one in MDX. Our PeriodsToDate function does not return any value. We have set type property of our Date Dimension as time.Actually CURRENT MEMBER does not return a valid value. So our PeriodsToDate function fail.
With MEMBER [Measures].[YTDÂ Actual]
AS
Aggregate
(
               PeriodsToDate
               (
               [DimDate].[CalendarHierarchyDateLevel].[Calendar Year]
               ,[DimDate].[CalendarHierarchyDateLevel].CURRENTMEMBER
               )
[code]....
View 2 Replies
View Related
Jul 31, 2015
For some of my calculations it is mandatory to get the first and the last date, filtered by the slicer type "timeline".
This works well as long as the date is not used on rows or columns ...
As soon as I'm using theDate within the rows this very simplistic dax measure doesn't work ...
I'm wondering how to determine the StartDate (from the timeline) and the EndDate (from the timeline) when "thedate" is actively contributing to the filtercontext.
In my opinion it is not another example of the "event in progress" situation, due to the fact that start and end dates are actively selected by the user and can not be derived from within the date model.
[URL]
View 2 Replies
View Related
Jul 9, 2015
I try to calculate the turnover of an employee based on all sales on this customer and the hours spends working for this customer.To simplify, I have 2 tables.General Ledger table.I have customer no and sales.And activity table,I have customer no, hours and users.To be able to calculate the average of a customer, I made an average value based on the sales/divided by the hours on activity table.Customer 1 sales of 100$.Value of the customer is 10$/H -> John 40$ / Mike 60$.How can I use the information 10$/h to use it with the hours from a employee.
View 3 Replies
View Related
Jul 13, 2015
I am looking to add a column to one of my tables that displays a running rank of how many times a customer has ordered in a given period.Â
I currently have such a column however this column ranks against ALL of the orders that a customer has placed and ignores filters, whereas I need one that ranks based on the filters that are active at any given time.Â
The current formula is:
CustOrderCountPersistant=RANKX(FILTER('Q1 Data Set',[k1_customer_id]=EARLIER([k1_customer_id])),[order_id],[order_id],1,DENSE)
For example, if I am looking at a full years worth of data and a customer has placed 10 orders in that period this formula will add a 1 in the column for first order, a 2 for the second and so forth all the way to 10, the last order.Â
However it will give me exactly the same results if I filter the data to just one month of that year where they may have order only 2 orders.Â
In this scenario I want to have another column with a table that is filter sensitive and would show 1 for the first order and 2 for the second order.Â
Now, I do understand that the issue here is probably the FILTER() I have on as, if I understand correctly, that means all other filters are ignored. My attempts at reworking the formula to remove this have been unsuccessful (such as using a CALCUALTE and trying to use filter properties within that forumula).
To explain the context - I want to create a measure that counts how many customers have placed x amount orders in y number of days e.g. how many customers have placed 2 orders in 30 days.
View 3 Replies
View Related
Apr 10, 2007
Hello,
I have a master report that uses the "Jump to report" property to drill through to a detail report when the user clicks a report column value. This all works great and the users love it. However, when I click the brower's "Back" button to return to the master report, the master report is rerun and I am returned to page 1. If I drilled through on page 25 of the master report then I have lost my original position and I must navigate back to page 25. The users don't love this "feature"!
Is there a way I can return from the drill through detail report to the master report without using the browser's back button and losing my original position in the master report?
Thanks,
BCB
View 1 Replies
View Related
Jul 5, 2015
I have an old model that unfortunately had to be re-establish.Â
In order to save time, I thought that I can export all my measures and paste it as measures in my new model.Â
I used the following technique to export the measures from the old file: [URL] ....
How to use the output and create the identical measures in my new model, without the need to manually write each one of them?
View 7 Replies
View Related
Apr 20, 2014
I have 4 tables involved here. The priority table is TABLE1:
NAMEID TRANDATE TRANAMT RMPROPID TOTBAL
000001235 04/14/2014 335 A0A00 605
000001234 04/14/2014 243 A0A01 243
000001236 04/14/2014 425 A0A02 500
TRANAMT being the amount paid & TOTBAL being the balance due per the NAMEID & RMPROPID specified.The other table includes a breakdown of the total balance, in a manner of speaking, by charge code (thru a SUM(OPENAMT) query of DISTINCT CHGCODE
TABLE2
NAMEID TRANDATE TRANAMT RMPROPID CHGCODE OPENAMT
000001234 04/01/2014 400 A0A01 ARC 0
000001234 04/05/2014 -142 A0A01 ARC 228
000001234 04/10/2014 15 A0A01 ALT 15
[code]...
Also with a remaining balance (per CHGCODE) column. Any alternative solution that would effectively split the TABLE1.TRANAMT up into the respective TABLE2.CHGCODE balances? Either way, I can't figure out how to word the queries.
View 0 Replies
View Related
Mar 27, 2007
I am a confused newbie, grateful for some help. I need to select 10 rows from total profit of married people. How would I do that?
DRILLTHROUGH MAXROWS 10 SELECT
{[Measures].[Total Profit]} on 0, [customer].[Name]ON 1 ON columns
FROM from [Electronics] WHERE [Demo].[civilstatus-salary].[civilstatus].&[married]}
RETURN [Measures].[Total profit](as rows)
View 2 Replies
View Related
Jan 5, 2007
I have created two reports. One links to the other with a drillthrough. The linking works fine, but no values show up in the fields besides the group titles and column titles. Why is this happening?
The user is given a list of Counties with values. The Counties(in a matrix) are clickable for a drillthrough of Ages and Genders in the Counties. Looks something like this:
Years
Counties Total Crashes Fatalities
Cook 85 98
Manchester 2850 258
Sagamon 987 526
So when a user clicks a county, such as "Cook", this is displayed
Years
Age Gender Total Crashes Fatalities
0-8 Female 25 8
Male 55 15
9-15 Female 850 258
Male 185 35
16-22 Female 87 526
Male 10 35
But I am getting this in :
Years
Age Gender Total Crashes Fatalities
0-8 Female
Male
9-15 Female
Male
16-22 Female
Male
Whats going on? Can anyone make any suggestions? Just to let you know its based off of an Analysis Services Cube and not relational tables.
View 2 Replies
View Related
Jan 11, 2007
Trying to drillthrough one report to another. But im not getting the correct values. I dont know why its not working. I created the parameters and linked them in the properties of the clickable field. What am i missing? or is it more complex in a cube to drillthrough then relational tables?
View 3 Replies
View Related
Nov 29, 2007
Hello again,
We have a drillthrough report that our users will see as part of an ASP.net page. I would like to nicely show the parameters that were sent through from the main report, maybe in a text box of some kind. However, when I set a text box value to =Fields!FieldName.value, I get the tuple: [Dimension].[Hierarchy].&[Member]. I just want the Member.
I know I can parse out the value, but is there any easier way that I'm missing. We aren't using the parameter bar that comes with RS, we want the report itself, above the matrix with the data, to show the parameters.
Any ideas, resources, references, links or good clean jokes would be appreciated.
Thanks,
Kathryn
View 2 Replies
View Related
Jun 27, 2007
I've published a matrix type report through report builder to my report server, based on the model I created. When I click the subtotals to drillthrough, I receive the following error:
"The permissions granted to user 'Domainuserid' are insufficient for performing this operation. (rsAccessDenied)"
If I explicitly specify another report to tie to the drillthrough property on the entity via the model property page in SSMS, the drillthrough stuff works fine. But if I just rely on Reporting services to auto generate ( see "Autogenerated" here ) the clickthrough report I get the error above. Can anyone help?
View 1 Replies
View Related
Jan 8, 2007
Hi,
I've looked at all of the published materials related to this subject that I could find but cannot find a good answer.
I'm trying to find out if there is a way to directly influence what details are shown when a figure is clicked on in a report.
I know it shows related data but it does not seem to show it all, infact it seems to pick pretty arbitrary things.
I've tried messing around with the attribute properties (in particular the EnableDrillthrough one) but to no avail.
Is there anything I am missing?
Thanks in advance.
View 4 Replies
View Related
May 2, 2007
I'm trying to use the drill-through feature to link to a report in the same solution, on the same server, but in a different project. Local help files say:
"The list of report names includes all reports in the current report server project. If the drillthrough report is on the report server but is not in the project, type the name of the report. The report name can contain a relative or absolute path to the report."
Can someone give an example of a relative or absolute path? Nothing I've tried has worked.
Thanks,
JL
View 5 Replies
View Related
May 21, 2007
Dear all,
Please help to create DRILLTHROUGH in a report server project. I am a beginner . I want the DRILLTHROUGH featrure in Report server like same as that of analysis server project
Please help
regards
Polachan
View 3 Replies
View Related
Jun 27, 2007
I have enabled drillthrough on a cube (AS 2005) and selected the columns required. This worked. When I use the drillthrough option in Excel, drillthrough queries return less rows than expected. For example, for a cells combinations (pivot table) I saw 21198 rows, but after access to detail, query return just 21106 rows. Less rows than expected.
Any ideas what causes this?
Thanks!
:)
View 1 Replies
View Related
Mar 31, 2004
Hi:
The drillthrough in my cube is working fine except for the cases where the dimension member is null. For example I have the dimension
PRODUCT (dim)
- PROD_TYPE_CD (name = PROD_TYPE_CD || '-' || PROD_TYPE_NM)
- PROD_CD
-PROD_NM
So for if the data is like the following where the PROD_TYPE_CD is null :
- (name = -FOOD)
- 123
- bread
The drillthough is not displaying any data no matter I select the PROD_TYPE_CD, PROD_CD or PROD_NM in the dimension drop down altough if query the DB directly I found data to display in the drillthrough
Any ideas why this is happening and how to solve it.
Thanks
J
View 1 Replies
View Related
Oct 25, 2007
Good morning all!
I am working on a Actuals vs Budget report which needs drillthrough capability. Right now the reports are not deployed, I am working on them in Visual Studio 2005.
My main report has a text box with the Navigation tab set to Jump to report: DrillThrough1025. I have set all the parameters to pass through to the drill through report.
When I preview my main report and click on the drillthrough textbox, the DrillThrough1025 Report opens, but it opens with the parameter bar at the top, waiting for the parameters to be filled in.
Am I making a basic newbie error somewhere?
Any ideas, references, or resources would be greatly appreciated.
Thanks,
Kathryn
View 1 Replies
View Related
Oct 25, 2007
Hello!
I'm working with the SQL Server 2005 Developer Edition. When i created a mining structure for association rules and go into the rule by drillthrough there's something i don't understand.
In the SQL Server the data set is:
ID: MM: MF:
47 463 200
And drillthrough shows me:
ID: MM: MF:
47 476 227
Why is the result of drillthrough another than in the table of the SQL Server???
View 1 Replies
View Related
Apr 11, 2007
Hello,
I have a master report that uses the "Jump to report" property to drill through to a detail report when the user clicks a report column value. This all works great and the users love it. However, when I click the brower's "Back" button to return to the master report, the master report is rerun and I am returned to page 1. If I drilled through on page 25 of the master report then I have lost my original position and I must navigate back to page 25. The users sure don't love this "feature"!
I am using Report Manager and not the .NET report viewer control. Also, I don't see any navigational control on the drillthrough report that allows me to "back up" to the master - that is why I have used the browser's "Back" button.
Is there a way I can return from the drill through detail report to the master report without using the browser's back button and losing my original position in the master report?
Thanks,
BCB
View 4 Replies
View Related
Jan 31, 2005
Hello.
i have dimensions: month, country, customer, item.
measures: amount, price.
i would like to get such result :
for each customer from USA: sum (amount of item in June * price for the same
item in January).
is it possible?
thank you for your help, Mike.
View 2 Replies
View Related
Nov 29, 2007
what security measures should I take to restrict access on sql server 2005 from LAN and WAN. I am using local Ip address to connect Sql server. Security measure should be so strong that unauthorized user can not access my database.
Any help will be appreciated.
Thanks
View 2 Replies
View Related
Jun 18, 2007
Hi,
I would like to create a report on a cube with Report Server (Query Builder) with one dimension in columns and the "measures" dimension members in rows:
Jan Feb Mar ...
Measure1 10 12 14
Measure2 20 22 24
..
How to create with either Tabular or Matrix report type?
Thanks,
Marcus
View 3 Replies
View Related
Mar 15, 2008
Hi all
I have created two reports.
And check the jump to option button and gave the second report name.and passed the parameter in first report.
Problem is when i click on preview button on the main report,First time i am able to see the main report and when i click on the linked text box i am able to view the second report.
But when i click on the preview button second time i am directly getting the second report preview.
How can i view first report every time i click on preview button.From there i need to go to second report by clicking on the specified text box.
How can i Achieve it.
Any body pls help me.
View 11 Replies
View Related
Feb 6, 2007
Hi,
I€™ve build a report in reporting services 2005, based on a MOLAP cube (Analysis Services 2005). In the cube I€™ve enabled drillthrough.
I know that we can define drillthrough in the report by clicking on one of the members of the dimension hierarchy, but that€™s not what I want. I want to be able to drillthrough on a cell. Is there any way how to do that in Reporting Services 2005?
Thanks,Abdel
View 3 Replies
View Related
May 29, 2008
I made a number of dimension and calculated member mods and notice that all now no base or calculated measures display values, they just show blanks in the browser and query window. To try to isolate, I've
(1) removed every dimension and calculated member
(2) verified there are no defaultmembers for dimensions
(3) verified there are no dimension attributes set to IsAggregatable = False
(4) verified there are no deploy or script errors
(5) fully processed every dm and fact
(6) verified there are dim and claim records loaded with values in all appropriate columns
When I process the cube, it shows that rows are read, etc... All that looks normal. Just when I browse or query, all measures display as blanks or nulls.
What could cause this behavior? Any ideas on how to futher isolate? I can go to a backup but very concerned that this can happen with no error messages or warnings, so I want to find the cause.
View 4 Replies
View Related
Oct 5, 2005
Wonder if you could help me ?
I have set up a cube running under MSSQL Server 2000 Analysis services and have created reports in Excel 2002 using pivot tables and linking into the cube as an external data source.
The pivottable works fine and I can slice / dice as usual, the only thing that doesnt work is the drill through into AS. I receive and error message saying "Cannot show detail data for that selection...".
Now I know that the drill through works, as when I create a report using the Analsyis Services Excel Add Inthe drill through works fine, its just how do I get it working using the pivot tables ?
fyi - the reason I dont want to use the Analsyis Services Excel Add In for all the reports is because I have to deploy this to XX number of users, who wont have admin rights on their machines etc etc....
Is there any VB that I could use to perform this drill through and return the results.....or easier ??
Thanks very much
Mark
View 3 Replies
View Related
Nov 2, 2007
Hi Friends,
We are using Drillthrough links in page header/footer which is not supported in RS2005.
Is there any fix available bcas the same problem was raised one year back.
thanks
Novin
View 12 Replies
View Related
