Matrix Drill Through Problem
Jul 6, 2007
So here goes the explnation. The problem is specifying an all parameter in a drill through on the value cell of a matrix for the columns in the matrix that are not expanded.
Here goes the example
Matrix for sales.
Region | Shop | Number of sales. And then financial period would be at the top. Everything is collapsed on load so it looks something like this.
Region1 | | 50
Region2 | | 75
Then you expand Region.
Region1 | Shop1 | 30
Region1 | Shop2 | 20
Region2| | 75
At this point if you click on the 30 for shop 1 is drills through into another report passing parameter Region1, shop1 and financial period. This is perfect. The problem comes in that if you haven't expanded region1 and you click on the 50 it drills into the other report but passes through Shop1 as the shop parameter. How can I get this to pass though an all option for the unexpanded columns in the matrix. This is ofcourse just an example and my matrix has quite a few more column and stuff.
Please guys, any help would be appreciated.
This is Reporting services 2005 linking to SSAS2005 cube.
Thanks in advance
View 3 Replies
ADVERTISEMENT
Mar 13, 2007
Hi All,
I have a table which has a country, division, category, product, Valuetype, value
data looks like this:
Country division, category, product, Valuetype, value
---------------------------------------------------------------------
UK Division1 Food Pizza Volume 10000
UK Division1 Food Pizza Revenue 2000
UK Division1 Food Pizza ROI 4.5
UK Division2 Food Pizza Volume 14000
UK Division2 Food Pizza Revenue 2400
UK Division2 Food Pizza ROI 4.1
UK Division2 Drinks Cola Volume 14000
UK Division2 Drinks Cola Revenue 2400
UK Division2 Drinks Cola ROI 4.1
I want to build a drill-through report to be grouped on country, division, category and product.
Can someone help me with some suggestions?
Cheers
Josh
View 1 Replies
View Related
Jan 8, 2007
is it possible to have drill down feature in matrix report?
A B C
x 1 0 1
y 2 2 2
z 4 0 7
What would be nice is if the user clicked on "7" and a report opened showing the detail for that value.
View 1 Replies
View Related
Oct 18, 2006
Hello Experts,
I have a Matrix in my report where on each each cell i have a drill down to another report. The Matrix also has SubTotal Column getting generated.
But the Problem is the drill down report gets carried on the SubTotal column also and the user gets vage results as proper information is not passed to another report.
Is there a way through which if its a subTotal cell then i can hide the drill down. some IIF() condition is there is
I appreciate if somebody can help me address this problem.
/Soni
View 1 Replies
View Related
Jan 16, 2007
I have defined the following to generate a matrix report
1. Row data: Country,region,state,Race,gender
(Red colored columns - Group1;
Blue colored columns - Group 2)
2. Column Data: Statistics as of (Date)
3. Value: household income
I'd like to be able to have a seperate drilldowns on group1 and group2. i.e drilling down on country, region, state shouldn't automatically expand/minimize race and gender. I played with the attributes of individual columns and edit groups of the same. But couldn't achive an independent drill down.
Income as of '05 Income as of '06
Country Region State Race Gender 100k 105k
Having independent drilldown on the group1 and 2 enables us to find out, for example, how many hispanics live in the region west coast. (in a typical tree like drill down, you need to expand country, region and state to get to Race)
How can create an independent drilldown on group1 and group2?
View 5 Replies
View Related
Apr 24, 2008
i'm trying to create a drill down matrix in report designer because the Chat System, i've a char in the upper region of the RDL and i want to put in the lower region a Matrix with drill down capabilities, but i don't know how to do it.
need help please
thx
Hugo
View 1 Replies
View Related
Oct 28, 2005
Can anyone help me with this?
This is my problem:
I have a cube that I created in AS2005, I have a hierarchy and drillthrough action defined. When I create a pivot table in Excel based on that cube and I try to drill down on the cell in the pivot table I can not drill down to the next level. I get an error message: Cannot show or hide detail for
this selection. My hierarchy is based on Dimension Facility. Facility State is root level, facility zip is next level. When I'm in AS2005 and browse the cube, I'm able to drill down and drill through and see the hierarchy just fine. (Also, I find interesting that when I create a cube in excel, based on a two relational tables, I'm able to drill down. ) I have the Analsysis Services Add-on installed on my machine, so I have the Cube Analysis Menu item. But I can't seem to even drill-through. So, for now all I want to know is why can't I drill-down (i.e. double clicking on a cell and having the data related "explode" in the spread sheet) like it shows in the article written by William Pearson, titled September 12, 2005
Introduction to MSSQL Server Analysis Services: Reporting Options for Analysis Services Cubes: MS Excel 2003 and More. Any help is greatly appreciated!!
Queen
View 3 Replies
View Related
Aug 6, 2015
I got the following code to add a column in a matrix with a variance:
IIF(IsNothing(Previous(Sum(Fields!Amount.Value))) or Fields!year.Value=First(Fields!year.Value,"Category") or Previous(Sum(Fields!Amount.Value))=0,nothing,
(
(Fields!Amount.Value)
/Previous(sum(Fields!Amount.Value))
)
)
This code works fine, except that the first row of the matrix shows an #error
This happens with each matrix where I use this expression. A warning emerges:
rsruntimeerrorinexpression the value expression for the textrun Textbox43.Paragraphs[0].TextRuns[0]' contains an error.
Attempted to divide by zero.
The strange thing is that the part
Fields!year.Value=First(Fields!year.Value,"Category")
should prevent an error and I expect it to show 'nothing'
An screenshot of the table. (each color is a different category. Each row stands for 2013, 2014, 2015)
As you can see, all other 2013 rows show a blank cell, except the first row.
View 3 Replies
View Related
Jan 10, 2008
I have a Matrix table that expands to the right when choosing an amount of months to be shown. Under this matrix I have to Charts. The two charts are situated together, that is no space between them, and to the left of the report.
Now, if I choose a lot of months, say three years the matrix diagram will be huge to the right. The problem I have is that the second diagram, the one on the right, moves to the right depending on how big the report gets, and this is not good at all. The two charts are supposed to be all the way to the left.
How? Why does the right chart move?
Thanks in advanced
Kind Regards
View 1 Replies
View Related
Nov 26, 2007
Hello.
I hope to explain myself well - I want to make a matrix with two rows.
Lats say my data is this:
I hava a list of months and in every month I have a number of pepole and there age.
How can I show this in a matrix?
It need to be in a matrix since I need the columns to expand acording to the month but I don't know how to create two diffrent rows in my matrix.
The data should look like this:
10/06 11/06 12/06 01/07 02/07 03/7 04/07 .....
num 5 1 2 5 4 5 7 .....
age 16.1 25 18.5 14.8 25.5 20.5 18.5 .....
Thanks for any help.
View 3 Replies
View Related
Aug 21, 2007
I have a report thats fully functional. I just want to add a filter so that my "Visits" field only displays the Visits per day that are less then 6. When i try to filter out the matrix or the group, it tells me the datatypes are different . Something about int32. Its in a matrix, but i have seen this happen in a table too, so i guessing thats not the problem. I just want to be able to display the information for Sales Reps with less then 6 Visits. Any help, will be greatlly appreciated.
View 1 Replies
View Related
Jan 2, 2007
Can we do this?
Adding more columns in a matrix report that don€™t
belong to the columns drilldown dimensions€¦
That is, for example, having the following report:
Product Family
Product
Country City Number of units sold
Then I
would add some ratios, that is, Units Sold/Months (sold per month) and other that
is the average for Product Family (Units Sold/Number of Product Family), for putting an example€¦ some
columns should be precalculated prior to the report so do not get into it, the
real problem I don€™t see how to solve is adding one or two columns for showing
these calculated column that doesn€™t depend on the column groups but they do
for the rows groups€¦
Any guidance
on that?
The only
way I am seeing by now is to set it as two different reports, and that is not
what my client wants€¦
Many
thanks,
Jose
View 4 Replies
View Related
Jun 8, 2007
Hello,
I have a matrix in my report that is based on an MDX query. I copy-paste the matrix and then see the two matrices in the report and all is fine.
I then insert a group into the second matrix. When I run the report the second matrix appears as I would expect, but the cells are blank in the first matrix. Sometime all the data cells are blank, but not the totals.
I tried a similair thing with a table. I added a table based on the same MDX dataset and the cells in the matrix were also then blanked out.
I made sure that the matrices were not sitting on top of each or even in the same space.
The goal here is to show the same dataset but with different groupings. I thought of doing drilldowns, drillthroughs, etc. but this is the way they want to see the data.
Any ideas what I am doing wrong?
Thank you for the help.
-Gumbatman
View 4 Replies
View Related
Jun 22, 2005
hi Guys
I want to allow users to have the ability to drilldown. But it doesn't seem possible outside AS ? am I wrong ? As I have built a cube with drilldown enabled and have tested and is working within AS. However after I've opened it up in excel I and tried to drill down it wasn't actually possible. I could only increase / decrease the report's hierachy and that was all.
Anyone know how I can allow my clients to view drill down values ?
any help is appreciated :)
Tom
View 10 Replies
View Related
Sep 8, 2005
Does anyone know if it's possible to use drill-through function, liked it's used in cubes in Analysis Manager, in a webreport in Reporting Services? With drill-through i mean the functionality to see individual rows from the database for a single cell in the web-interface.
Anders from Sweden
View 2 Replies
View Related
Apr 10, 2007
Hi !
I use a OLAP data Source (Analysis Services)
I have a problem, I have a bar chart, where I put an action on the data Properties to move to another report B if there is a clik on.
My report B need a parameter to execute it self.
So In my report A, I add an action which pass the good parameter to the report B.
In my report B, I add a parameter (via report menu, reportParameter item), but I don't how link the two (grid and parameter)...
because, in my report display, I display a grid, and a text area, in the text area I display the parameter which is pass by the drill through, and when I preview it, the value is ok, but not in my table (grid),
I try to add a dimension with ... but I have always an error, and if I put a dimension who is a parameter it is not ok...
I don't understand,
could you help me !?
Thank you
View 8 Replies
View Related
Apr 14, 2007
Hi All ,
I am new to Sql Server and Reporting services ..
I was trying to make a drill down report
I have created Grooup on detail row and set the Visibility with one of the group rows just above ..
But when preview the report I cannot see any data ..
How ever If I Delete the Groups I can see the data ..
Is there anything that I am missing Please help
View 3 Replies
View Related
May 28, 2007
I have a problem on creating a report using Microsoft SQL Server Business Intelligence Development Studio. I am new to this environment, anybody here can help me on how to enable the "DrillDown" on the table?
When I am using the wizard, the option to enable the drill down will appear, but when I am using "Report Server Project", I really don't know how to enable it, and theres was no table properties on how to enabled it.
Could you help me please
Thanks
View 4 Replies
View Related
Jun 5, 2007
Hi All,
I have one dotnet applciation in that i am viewing the report in report viewer. In the same report if the user clicks on one field another report will open. I got a problem with this second report. the image placed on the second report is not coming and if the user clicks on the print button which is on the report viewer the first report is only getting printed. How can I print the second report.
Thanks In Advance
View 1 Replies
View Related
Apr 4, 2007
Hello everybody,
I have a small challenge for you.
I'm developing an application using SQL Integration Services and SQL Reporting Services. I need to have the ability to drill down from a graph created with Reporting Services.
Let me explain: If I have a pie chart, I want to be able to click in one of the slides and see the information details that could be in another graph or in a table.
Somebody know how to do this?
Thanks for your help
View 3 Replies
View Related
Mar 7, 2008
Hi all,What is Drill Down Reports in Reporting Servies.How can i design Drill Down Reports.
Give me any example to desing Drill Down Reports.
Regards.
View 5 Replies
View Related
May 22, 2006
Does anyone know what code I'd use in an expression to conditionally format based on if the drill down is expanded or collapsed?
Thanks.
in an
in-an
inane
Inna
Ian
Edit...
Revert to "inan"conditionally
conditional
conditionals
conditionality
Edit...
Revert to "conditionaly"
View 3 Replies
View Related
Sep 25, 2007
I was wondering if when you click on the + in a drill down report, does the report requery the database, or is the report just re-rendered without having to query again?
depending on the answer, performance considerations would be different.
View 3 Replies
View Related
May 2, 2007
Hi,
I have created a drill down using a table. The drill down has 13 levels in it which causes a big vacant space between the top level and the mesures on the right hand side. I would like to hide the columns so that when i drill down the measure columns should dynamically move towards the right (as i keep on going to the next level) . I have tried using inscope for hiding that particular column by refering to the previous level group in it but it doesn't seem to be working.
Kindly suggest if anyone has done this before.
Regards,
Vikram
View 3 Replies
View Related
Feb 21, 2007
Any way to have the drill through report open in a new window on the web?
View 4 Replies
View Related
Sep 11, 2007
Hi all
is it possible make drill down reports in crystal
my requirement is like
i have n rows in my report
if am clicking the first column in first row it will list details of that row in between first and second row
Same for all the rows
Quick reply is appreciated
Regards
Rameez
View 2 Replies
View Related
Aug 13, 2007
Hello everyone
For starters i'm using Microsoft analysis services, integration services and reporting services.
All goes well when creating some general overview reports.
Once i get to making drill down reports with only 2000 rows of data is already starts to work very slow (sometimes takes hours). It seems a data cube is only fast for calculations like SUM, PARALLELPERIOD, ... but if it comes to amount of data it is extreemly weak! 2000 rows is not even alot, i've seen reporting tools beeing able to handle a million rows with drill down function.
I'm thinking that this microsoft tool is just not able to handle reports bigger than 100 rows in it combined with drill down. Please proof me wrong because we already paid for the microsoft tools and it would be a shame if we were not able to use them because of bad functionality...
The machine we work on is quite strong (dual core 2.4 ghz, 4 gig ram, ...)
And yes i know you can make reports clickable so they take the value with them to a next report as a parameters but thats not what we are looking for, we need this drill down to work properly with larger amounts of data.
View 1 Replies
View Related
Nov 3, 2006
Is there a way to get the Drill through to work with model viewer controls? Any workaround?
Also, where would I find more complex data mining models for review? SQLServerDataMining site, Microsoft data mining tutorial site, and Data Mining with SQL 2005 book, all checked. Anything else?
-Young K
View 10 Replies
View Related
Feb 1, 2007
I've got a report that drills through to itself, passing different parameters when drilling through. The report work flawlessly in development, flawlessly when you call the report server directly via url, but doesn't always drill through correctly when the report is called using the report viewer on web site. I've gone as far as to create a single aspx page web site that only has this report on it and it still doesn't work.
The drill through works about 1/2 of the time when viewing through the report viewer. But the other half of the time it takes you back to a previously visited parameter, instead of the parameter you clicked on.
So a series of clicks might work like this:
Parameter Clicked - Resultant page
A - Correct "A" page
B - Correct "B" page
C - Correct "C" page
D - Incorrectly taken to "B" page.
It seems to be worse if you throw in any click of the back button.
We thought at first it might be a parameter caching issue, but we turned off all caching and even created a time-stamp parameter to force no caching.
Any thoughts are appreciated.
Craig
View 1 Replies
View Related
Apr 4, 2007
Hello - I am using report builder against models.
Which attributes or properties determine what is shown when you do click/drill into the details? I'm using the adventure works database as my guide and expected the (default,Identifying or Aggregate ) attributes to control it, can you summarize how the drill details are determined? My test from adventure works sample:
Pull Territory Name and #customers into the report
Run it and drill into the details on one of the #customers
It returns Acct#, CustType, CustName, #SalesOrders, Sum of a bunch of sales order fields and # customer addresses.
The first three are the default attributes for a customer and the last sets look to be the default aggregates for the other relations to customer - Sales Order & Address.
Is it safe to say that it pulls in the default attributes of the immediate child and then aggregates of it's children? Does it go recursively?
Thanks in advance,
Toni
View 1 Replies
View Related
Jul 21, 2006
Hi,
can anybody tell me if it is possible in RS2005 to create a multi-value parameter in wich it is possibility to drill down? Say i want to use a multi-value parameter to show data for one or more specific years or months. Then the multivalue parameter would contain a list of years, with a + sign next to them, making it possible to drill down to the specific months.
Any help greatly appreciated.
Christopher
View 5 Replies
View Related
Sep 17, 2007
Hi,
I am new to reporting services. We are creating a report with drill-down capability. We have a Date dimension hierarchy defined as Year - Month - Day.
On the report, we expect reporting services send MDX query to SSAS to get the total sales just for the the year, say 2007, when we click the year 2007. Then it send query just to aggregate sales for that month when we click a month.
But when we build the reports, it looks like it always aggregate for all levels. So the performance is bad to first open the report.
How do I resolve this issue? Please help.
Thanks,
Jia
View 3 Replies
View Related