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 RepliesHello,
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
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.
I am just starting out using CUBEMEMBER/CUBEVALUE formulas in excel linked into a sql olap db - using this method for some custom reports where pivot tables are not suitable.
The time dimension values include Months, Quarters and Years and the CUBEMEMBER formulas like
=CUBEMEMBER("OLAPCUBE","[Time].[Time].[Year].&[2015].&[1].&[1]") work fine - 1st quarter 1st month etc.
Is there a straightforward notation to aggregate months or do I need to use a plus sign to add a number of CUBEMEMBER formulas together.In other words - Is there an easier way of for say jan to july 2015 totals than
=CUBEMEMBER("OLAPCUBE","[Time].[Time].[Year].&[2015].&[1]") + (CUBEMEMBER("OLAPCUBE","[Time].[Time].[Year].&[2015].&[2]")) + (CUBEMEMBER("OLAPCUBE","[Time].[Time].[Year].&[2015].&[3].&[7]"))
I haven't tested this but have assumed it works but a bit long and clumsy.
I have problems creating a cube with AMO.
I can add the cube to the database object and fill it with dimensions and a measuregroup (see code below).
If I call cube.Update() it says something like "Error in meta data manager. Cube has no measuregroups." (getting the message in german language)
The error in Microsoft.AnalysisServices.OperationException.Results.Messages is -1055653629
I can't find any documentation about this (or any other) error code in Microsoft documentation.
Here's my Code:
Cube newCube = database.Cubes.Add("MyCube","MyCube");
newCube.Language = 1031;
newCube.Collation = "Latin1_General_CI_AS";
CubeDimension dim = newCube.Dimensions.Add("dim1","dim1","dim1");
CubeAttribute attrib = dim.Attributes.Find("dim1Attr1");
[code]....
When I make a call to GetSchemaDataset with a restriction of a cube name with a space in the name of the cube the call fails. Following is a sample of the code: adoRestriction = new AdomdRestriction("CATALOG_NAME", "Contoso Telecom_Contoso"); adoRestrictions.Add(adoRestriction); dataSet = conn.GetSchemaDataSet("MDSCHEMA_CUBES", adoRestrictions); I am running SQL Server 2005 Analysis Services SP2. Is there some way to qualify the cube name in the restriction or is this just a bug? Thanks.
View 1 Replies View RelatedI 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)
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.
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
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?
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.
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
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
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!
:)
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
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
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???
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
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.
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).
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
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
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
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
I'm working on an SSAS 2012 OLAP cube with around 100 dimensions and a single measures group. When I try to run a simple DRILLTHROUGH query such as:
DRILLTHROUGH
MAXROWS 1000 SELECT
FROM [Cube] WHERE ([Measures].[Fact_Count])
I get an error as below.The specified query is too complex to be evaluated as a single statement. If I remove the majority of the dimensions from the cube I can run this query successfully. Looking at the limits set for analysis services I should be well within these.
We have started to use reporting services in integrated mode with windows sharepoint. Since this installation the drillthrough reports have stopped working. We get the following error when a hyperlink is cllicked:-
The item 'http://vtrends/reports/IslandRegionCountryState' cannot be found. (rsItemNotFound)
In the textbox properties we have set the 'Jump to Report' hyperlink option to use a condition. Based on the link clicked, one of two reports will be displayed. If i remove this condition and manually select the report to run then there are no problems. This however is not the functionality that is needed.
Any help will be appreciated.
I'm having trouble dynamically specifying the name of the report I want to drill through to.
I have the follwing expression in the Jump To Report field on the Advanced Textbox Properties Navigation tab:
=IIF( Fields!Third_Element.Value = "NONE", "Details Report.rdl", "Second Level Service Sub Report.rdl")
I get an error saying I'm missing the true part of the expression... Am I specifying the report name incorrectly? Both reports have the same parameters even though each report uses different parameters:
C:devReportsSLA Request TypeService Desk SLA Performance - First Level Service Sub Report.rdl The expression for the textbox €˜Second_Element€™ contains an error: [BC30455] Argument not specified for parameter 'TruePart' of 'Public Function IIf(Expression As Boolean, TruePart As Object, FalsePart As Object) As Object'.
Also, what does the "Omit" parameter do in the paramters set up? I tried dynamically turning parameters on/off based on the report I needed to call and it didn't react as I expected.
Does anyone have any ideas?
Thanks
Carol
Good morning, all
I have a really cool Budget report which has two matrices on it. One is used/visible if the user selects four levels of grouping, and one is used if the user selects only three levels of grouping. The four choices for grouping are four fields, Par, FERC, Point, and Resource.
Since grouping is optional, I need a way to pass the parameter for a field that is not grouped on from the main report to the drillthrough report. For example if the user chooses to group on all fields but Point, I need to pass the report parameter that was used by the main report for Point to the drillthrough report as a report parameter.
So, I tried testing the three grouping levels values and if none were equal to "Point" then pass in the parameter by putting the following expression in the Parameter Value box for the Point parameter on the Navigation tab of the datacell of the main report that is the drillthrough link:
=iif(
Parameters!GroupLevel1.Value <> "Point"
and
Parameters!GroupLevel2.Value<>"Point"
and
Parameters!GroupLevel3.Value<>"Point"
,
Join(Parameters!PNTPoint.Value,",")
,
Fields!Point.UniqueName
)
But this is not working; the drillthrough report shows a blank dropdown for the Point parameter and I have to manually set it. Does anyone see an error in the syntax of the iif statement?
Any references, links, resources or good clean jokes would be greatly appreciated.
Thanks,
Kathryn
Can any body help me to know about cube in dataminig?
How is the cube represented in database?
How to querry the cube?
unable to browse the cube - unspecified error - ms sql server
--- even the sps are up to date..
what is the missing link...
thank u
I have an archieve cube as .cab file and i have restored my cube into analysis server , but i am not able to browse the datain the cube. My doubt is- Does the cube contains data with it to browse or not? or we have to explicitly enter data at the sql query analyser.
Thanks in advance.
Hi ALL,
Just started researching Analysis Services product, and am playing w/ foodmart db (located on my C: drive) and creating cubes and dimensions, etc.
My problem is that I cannot browse any dimensions once they are created. For exampe, for a dimension called product I am getting an error message, "Unable to browse the dimension 'product'. Unspecified error"
Can anyone offer help?
thanks so much,
Alan
1.Create a table. The table must contain four columns of your choice and at least ten rows. Create a meaningful example of your own. The last column in the table must be a quantity. Provide output showing your CREATE TABLE and INSERT statements. Also include output showing their successful execution.
2.Create a ROLL-UP query using the table you created in problem #1 Provide output showing your SELECT statements and the resulting output rows. Next use the TRANSCT SQL help function of SQL Server and write definitions of the following SQL statements:
a.IS NULL-
b.GROUPING-
c.AS-
3.Create a CUBE query using the table you created in problem #1 Provide output showing your SELECT statements and the resulting output rows.
4.Create the following CUBE queries using the table you created in problem #1. Provide output showing your SELECT statements and the resulting output rows.
a.A CUBE query with Grouping used to distinguish Null values.
b.A CUBE query showing a multidimensional cube.
c.A CUBE query created using a view. For this problem you must not only create your view but query it and display the results.
5.Create one example of a query using COMPUTE and one example of a query using COMPUTE BY. These queries should use the table you created in problem #1 Provide output showing your SELECT statements and the resulting output rows.