SQL Server 2008 :: Dated Columns With Drill Report?
Jul 4, 2015
I have a matrix report with STORES in the row group and DATES in the column group. The table sums on SALES. The DATES column is formatted like =format(Fields!DATES.Value, "MMM yyyy"). The table also has 2 parameters @Start and @End. This all works great but I then added a child report so that the user can click on the SALES value for any sale by month and store. The child report uses the @Start and @End parameters from the original report but this is where I run into problems.
Rather than bringing me the sales details for a particular store and month it brings back everything from the time period selected with the original date parameters. So say I originally selected 2015-01-01 to 2015-06-30 with the parameters when I select on FEB 15 in my matrix report I get Febs data along with all the other months ie Jan-Jun 15. The DATES fields in both reports are in the same date format - in fact both reports use exactly the same dataset.
I realize it's something to do with the formatting of the DATE field not being recognized in the linked report.
View 5 Replies
ADVERTISEMENT
Jan 3, 2008
Hai,
Iam new to SSRS please help me, my question as follows:
in my table fields are departmentid, studentexpected, student name, date attendance,sortorder
while grouping(drill down) the departmentid column , iam displaying student name, date attendance its working fine.
problem: while grouping(drill down) the studentexpected resultant column i need to display student name, date attendance fields.
in my database studentexpected column displaying studentlist like 28, 56,250, while grouping(drilldown) the individual lists like 28,56,250, i need to dispaly student name and date attendance fields.
my problem is while grouping(drill down) the departmentid column iam getting result for studentlist 28 as iam getting 28 student names and 28 attendancedates, this functionality is correct.
but while grouping(drilldown) the studentexpected resultant column iam not getting (assume for studentlist 28) 28 studentnames and 28 attendancedates.iam geting only 2 studentnames, 2 attendance dates, but i need respective list columns
in database iam taking sordorder as 7.
iam facing difficulty at the time filtering the studentname and attendancedates columns in table properties
which data i have to give in filter column for displying 28 student names and 28 date attendances fields at the time of grouping studentexpected column?
take below references:
database prview:
departmentid studentexpected student name date attendance sortorder
101 28 jack 12-31-2007 7
dapartmetid drilldown report preview:
departmentid studentexpected student name date attendance
+101 28 jack 12-31-2007
-- ---
-- --
-- ---
-- --
(assume 28 student names and 28 date attendance)
My Problem:
like above preview i have to dispaly respective studentexpected column results,
departmentid studentexpected student name date attendance
101 + 28 jack 12-31-2007
-- ---
--- ----
(here while drilldown the studentexpected resultant column like 28,56,250 ,i need to dispaly respective studentnames and respective date attendances)
which data i have to set in filter column in front end , how to solve above problem? help me
Thank You
Jacks V
View 1 Replies
View Related
Nov 2, 2015
I have a report which takes around 5 seconds when run in BIDS but takes around 20 seconds when deployed on report server.Execution log says TimeDataRetrieval is around 3-4 seconds and rendering time is around 15-17 secs.From this report I am passing 8 parameters to a drill through report and there are 36 text boxes where I have defined these parameters for drill through action.All these parameters are populated in main dataset.When I deployed the same report without any drill through action and parameter, it takes 5 seconds.So I am suspecting that because of drill through parameters report is taking more rendering time on server.I am using 2008R2 and IE11.
Is it expected behavior that due to so many parameters for drill through action, report will take more rendering time?If yes, then why is it not taking same time in BIDS?
View 5 Replies
View Related
Oct 28, 2015
I have a report builder drill down report. I have row groups with totals. It looks like the attached. The problem is when the report is not expanded the Grand Totals column is not accurate... it is displaying the totals of one of the rows when expanded.The expression in the Total Show text box is
= Switch (
MID(Fields!protocol_id.Value,1,7)="THERAPY",
Sum(IIF(Fields!status.Value = "CO", CDbl(Fields!TX_CO.Value), Nothing)),
MID(Fields!protocol_id.Value,1,7) = "GENERAL" and MID(Fields!program_id.Value,1,6)
= "INTAKE", Sum(IIF(Fields!status.Value = "CO",
[code]...
Is there any way to not display the expression in the Total columns unless the report is expanded?
View 2 Replies
View Related
Sep 16, 2015
I am trying to develop a report from cube,and it has one drill through report.when i click on sales amount field on main report,then it open the drill through report with details.Here i passed the **(category,subcat,product)parameters(Cascaded)** to drill through report in action part of main report.In my main report i have two columns .
columns are **Level** and **Salesamount**.
Values are like
**[-]category** **100**
**[-]subcat** **50**
**product** **30**
when i click on 100, parameters are passed & it open the detail report correctly, but when i click on 50,the values for subcat parameter is not getting values in detail report and same as the product also.
Here i used the expressions in parameter values
**pCategory**----iif(inscope(category),!fields.category.value,split(join(Parameters!category.value,","),","))
**psubcat**----iif(inscope(subcat),!fields.subcat.value,split(join(Parameters!subcat.value,","),","))
**pproduct**----iif(inscope(product),!fields.product.value,split(join(Parameters!product.value,","),","))
View 3 Replies
View Related
Oct 2, 2015
I need to create a SSRS report using the Warehouse tfs_warehouse DB Report need to be drill down on WorkItemType Parents and child values. So, If we click on Product Back Log Item will get all the tasks inside it and so on....
------->Product BackLog items
----------> Bugs Or Tasks
-----------> Tasks
I have done research and found out how to do it in OLAP cube ..but still can't figure out the TSQL query to be used.Found one of the query on forum:
SELECT * FROM DimWorkItem DWI
INNER JOIN FactWorkItemLinkHistory FWILH ON DWI.System_Id = FWILH.TargetWorkItemID
WHERE FWILH.WorkItemLinkTypeSK IN (SELECT DWILT.WorkItemLinkTypeSK FROM DimWorkItemLinkType DWILT WHERE DWILT.LinkName = N'Child')
AND DWI.System_WorkItemType = N'Task'
AND DWI.System_Rev = (SELECT MAX(DWI1.System_Rev) FROM DimWorkItem DWI1 WHERE DWI1.System_Id = DWI.System_Id)
AND FWILH.SourceWorkItemID IN (SELECT DWI2.System_Id FROM DimWorkItem DWI2 WHERE DWI2.System_WorkItemType = N'Bug');
View 5 Replies
View Related
Feb 11, 2007
Hi,
I've created dsv that contain all fields from table database. in the smdl I've remove some fileds due to security. All fields in the smdl do not contain drill.
Issue: When I created calculated field in the report builder the field has a link. When I clicked the drill I saw all the record data including field that not in the smdl.
Questions:
1) Can I remove the link from the calculated fields?
2) Can I prevent from users drill to fields that not in the smdl?
Thanks,
Assaf
View 1 Replies
View Related
Mar 12, 2008
I deployed the same report model to 2 different SSRS servers. The report only has 1 field with count of records from 1 table. I can drill down on this column on 1 server, but not on the other server. This column is set to drillabe on the report model.
Is there any SSRS configuration that is stopping me drilling down on it?
View 1 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
Sep 10, 2015
I thought the built in expression Level() would show me be drill down level. I can't see to get that to work. I'd like to determine my drill down level so I can hide columns based on that.
View 2 Replies
View Related
May 8, 2007
Hi all
I am new in Sql Server reporting services ..So please help me to clarify my doubt
Please help me to know whether it is possible to generate a drill down report using Report sevices. If it possible please let me know how it can be generated
For Example
Measures
Quarter1 Quarter2 Quarter3#
Profit Profit Profit
====================================================================================
Dimension
Product1 200.50 1500.75 -200.40
Product2 5000.00 4000.00 6000.00
==================================================================================
In the above example Quarter 3 for product 1 shows los in such cases I want to drill down the transaction to get the detailed inormation and to find out the reason how the loss came..
Please help me to get such type of drill down is possible in measures to get the detailed report
With regards
Polachan
View 4 Replies
View Related
Jan 10, 2008
I have an interesting problem to report that I'm hoping someone will be able to assist in solving.
I have a report that contains a table inside of a list. When I view this report on my local machine via Visual Studio, everything appears normal, the columns are all the correct size. However, as soon as I publish this report to the report server and attempt to view it via Report Manager, one of the columns expands horizontally!
From all of the documentation I have read, I understand that Reporting Services does not provide the ability for columns to expand horizontally, only vertically. Can anyone help explain why this is happening or a possible solution to turn this automatic expanding off?
Many thanks,
canuck81
View 4 Replies
View Related
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
Nov 7, 2007
1)i have drill through report..
a is the parent report..
b is the child/subreport ..
after i click link at parent report -(a), it will go to the child/sub report -(b),
but when i want back to parent report,dont have button back...
for your infromation,it will hapened when i deploy to server and use report viewer control from data tab in visual studio..
2)when i click the button print at child report(b) , but it will print the parent report, not the child report..
any setting for this problem?
thanks for reply..
thanks in advance...
View 3 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
Sep 21, 2007
I'm building my report using the Report Designer in the SQL Server Business Intelligence Development Studio. At the moment I've got a single report which consists of 2 tables. The top table shows some summary data and the bottom table shows more detail.
What I'd like to do is design the report so that when the summary data (in the top table) appears, the user can click on a row in the table and this takes them to a new screen which shows more detail about that item, extracted from the bottom table.
Is this possible? I know it's possible to create a drill-through report if you have the high-level summary data in one report and the low-level detailed data in a separate report, but is it possible to create a drill-through report if all the data is in one single report (albeit in 2 separate tables)?
View 6 Replies
View Related
May 14, 2007
Hello All,
I am sure many people faced this problem before. I want to drill-through to a report in another project. I want to use "Jump to report" in the navigation tab.
I am able to use Jump to URL. But I cound not specify "method="post" target="_self" in the "Jump to URL" box.
Can some one help me on how to use:
1. "Jump to Report" for reports in another project.
2. Or. Help me specify method="post" target="_self"
Regards
Bobba
View 1 Replies
View Related
Mar 21, 2008
Hi,
I want to create the report with following requirements.
I want to have the following fields in the drill down
India
Sales report
Expense report
Analysis report
USA
Sales report
Expense report
Analysis Report
UK
....
If I am clicking any country, it should show the sub report. If I click the sub report, it should show the appropriate report. please help me. thanks in advance
View 3 Replies
View Related
Dec 13, 2007
Hello:
I am running into an issue with RS2k PDF export.
Case: Exporting Report to PDF/Printing/TIFF
Report: Contains 1 table with 19 Columns. 1 column is static, the other 18 are visible at the users descretion. Report when printed/exported to pdf spans 2 pages naturally, 16 on the first page, 3 on the second, and the column widths have been adjusted to provide a perfect page span .
User A elects to hide two of the columns, and show the rest. The report complies and the viewable version is perfect, the excel export is perfect.. the PDF export on the first page causes every fith column, starting with the last column that was hidden to be expanded to take up additional width. On the spanned page, it renders the first column on that page correctly, then there is a white space gap equal to the width of the hidden columns and then the rest of the cells show with the last column expanded to take up the same width that the original 2 columns were going to take up, plus its width.
We have tried several different settings to see if it helps this issue or makes it worse. So far cangrow/canshrink/keep together have made no impact. It is not possible to increase the page size due to limited page size selection availablility for the client. There are far too many combinations of what the user can elect to show or hide to put together different tables to show and hide on the same report to remove this effect.
Any help or suggestion on this issue would be appreciated
View 1 Replies
View Related
Feb 26, 2008
Does anyone have a link to a good tutorial on how to create a drill down report in SSRS 2000. I found one for SSRS 2005
http://www.c-sharpcorner.com/UploadFile/MohanKumar.R/DrillDownReport02212008083910AM/DrillDownReport.aspx
If I had the AdventureWorks Db I may be able to figure it out.
I know how to create a hyperlink but I need the toggle (+/-) for my latest endeavor.
My book "Hitchhikers Guide..." didn't help me out.
Thanks.
View 3 Replies
View Related
Jun 7, 2007
Hello,
I have a main report that drills-through to a secondary report. The main report has parameters which can be selected (Dairy, Frozen, Fruits, etc.). If I make a selection (let's say Fruits) the second report displays just Fruits information (as would be expected).
However, when I select 'All' from the parameter, on the Main report, and then click to drill-through, the passed parameter is the first selection below 'All.' I cannot seem to pass 'All' to the second report.
What am I doing wrong?
Thank you for the help.
-Gumbatman
p.s. The datasets are based on an Analysis Services cube.
View 1 Replies
View Related
Apr 10, 2007
Hi all,
I was wondering whether there was a way to implement a "Hide all/show all" option in the report. are there any examples that I could refer to ?
Thanks !
Bernard
View 8 Replies
View Related
Oct 24, 2007
Hi all,
We are developing some reports in Reporting Services 2005 (deployed in Sharepoint). We are having a bit of a problem with the drill down functionality:
When drilling, the report page "jumps", repositioning itself so that the item I drilled is at the top of the page. Not what I wanted, I want it to expand the item without repositioning it.
So, any ideas on what my problem is? I am in kind of a hurry to fix this problem (as always ...).
Regards,
Daniel
View 7 Replies
View Related
May 13, 2008
I am having an issue with a drill through report. I have a parent report with 12 parameters that drills through to a report with a single parameter. The parent reports runs fine and the drill through works fine.
The problem occurs when I hit the browser back button to return to the parent report. The parent report will start to refresh (I see activity in the status bar) but then I just get an endless 'Report is being generated' message. If the report refresh button is hit then the report will come up (I would expect this to happen). I can't expect the users to have to continually
hit the report refresh button when returning to the parent report.
The report and drill through works fine in Visual Studio and this probelm is only apparent when testing through IE7.
Anyone have any ideas about this? Out of curiosity is there a limit to how many parameters can be used in a report? I can't find any reference to this in any SSRS literature.
Thanks
Ray
View 1 Replies
View Related
Apr 11, 2008
I have a report with a matrix object within which I can "drill-down" through several row and column groups. The report runs, and I can successfully "drill-down" within the matrix object.
However, when I create a drill-through action that executes another report when users click on the summed number of the matrix object, the main report fails with an out of memory error. When I remove the drill-through action, the report runs successfully again. This report is important because the users need to use it to get the detailed records from the main report.
Why is this happening? Does adding the drill-through links really use up that much memory?
Is there anything I can do to correct this problem, besides play with server memory settings and/or upgrade to 64 bit? I have already passed as much of the aggregation as I can to the database; in fact, the query for the main report only pulls 1000 records!
I refuse to create an intermediary drill-through report, since the whole reason I created the first drill-through report was because the main report can't handle the detail in the first place.
Thanks.
View 4 Replies
View Related
Apr 16, 2007
I have created a drill down report, using text boxes. When the report is not drilled down, all root nodes are collapsed, the spacing is really weird between them. For
example.
+ Node A
+ Node B
<a bunch of white space>
+ NodeC.
I want all the nodes to be one after each other. Thanks for any help you can provide.
View 1 Replies
View Related
Jul 21, 2015
Recently I have come across a requirement where i need to design a table.
There are some columns in table like below with DECIMAL Datatype:
BldgLength
BldgHeight
BldgWeight
Based on my knowledge, i know that values before Floating-Point will not be more than 4 digits.
Now as per MSDN,
Precision => 1 - 9
Storage bytes => 5
so i can create column as:
BldgLengthDECIMAL(6,2) DEFAULT 0
OR
BldgLengthDECIMAL(9,2) DEFAULT 0
Now while reading some articles, i came to know that when we do some kind of operation like SUM Or Avg, on above column then result might be larger than current data type.
So some folks suggested me that i should keep some extra space/digits considering above MATH functions, to avoid an Arithmetic Over Flow error.
So my question is what should be value of DataType for above column ?
View 8 Replies
View Related
Aug 13, 2015
I have a database with 1million+ records in and i'm trying to collect the median values of column(2) for all distinct values in column (1)
Example DB:
Column 1 Column 2
978555 500
978555 502
978555 480
978555 490
978324 1111
978324 1102
978311 122
978311 120
978994 804
978320 359
and I need it to display on SELECT as
column 1 column 2
978555 495
978324 1106
978311 121
978994 804
978320 359
Is this possible on 2008 R2?
View 7 Replies
View Related
Aug 17, 2015
I have two tables: "Users" and "Messages".
The Messages table has a "UserFromID" and an "UserToID", indicating a message was sent from a user to a user.
The Users table has a UserID, FirstName, LastName and UserGroup.
Im trying to get all messages sent by or to a user in a specific group, with the First and Last names of the sending and receiving users.
Here is my tables and data:
CREATE TABLE tmp_users (userID INT, GroupID INT, UserName VARCHAR(10), FullName VARCHAR(100))
CREATE TABLE tmp_Messages (aKey INT IDENTITY, FromUserID INT, ToUserID INT, msg VARCHAR(100))
INSERT INTO tmp_users VALUES (1,1,'John','John Smith')
INSERT INTO tmp_users VALUES (2,1,'Jane','Jane Doh')
INSERT INTO tmp_users VALUES (3,1,'Bob','Bob Newheart')
[code]....
The query Im running so far is wrong, but here it is...
SELECT t.FromUserID, t.ToUserID, t.msg, u.UserName AS UserFrom,
u.GroupID AS FromGroup, u2.UserName AS UserTo, u2.GroupID AS ToGroup
FROM tmp_Messages t
LEFT JOIN (SELECT UserID, GroupID, UserName FROM tmp_users WHERE GroupID = 3) u
[Code] .....
im missing the details of one of the users.I know what the problem is, I just cant figure out how to get this working without using temp tables, which I cant do in the production version.
View 3 Replies
View Related
Sep 12, 2015
how to use like operator select statement to retrieve multiple column names in sql server DB...for ex: I have a table say employees where in I want to get all column names like emp_,acc_ etc using '%' And what is this below query used for?
SELECT column_name as 'Column Name', data_type as 'Data Type',
character_maximum_length as 'Max Length'
FROM information_schema.columns
WHERE table_name = 'tblUsers'
View 2 Replies
View Related
Oct 29, 2015
I am struggling with doing what should be a fairly simply transpose of columns to rows. I have found some examples but nothing I could get working.
I have data in the form of:
Brand, Model, Color1, Color2, Color3, Color4, Color5, Color6
Honda, Accord, Red, Grey, Black, White,,,
Toyota, Corolla, White, Black,,,,,
The output should look like:
Brand, Model, Color
Honda, Accord, Red
Honda, Accord, Grey
Honda, Accord, Black
Honda, Accord, White
Toyota, Corolla, White
Toyota, Corolla, Black
View 2 Replies
View Related
Nov 2, 2015
how to encrypt a either columns or tables in SQL server (2008 R2).
Do I need to encrypt a whole table of can I encrypt certain columns of a table ?
View 2 Replies
View Related
Mar 24, 2008
Hi all;
Could any body tell me as to how I would be able to pass report grouping in drill through report.
Please review the diagram below to better understand my problem
Report 1 (Summary) Columns1 Column 2
Row-1:Grouped by attribute X Calculation based of dates Calculation based of dates
Row-2: Grouped by attribute Y Calculation based of dates Calculation based of dates
The Drill thru option should be present in all of the columns; so that, when any of the numbers in the column are clicked it should drill through to another report which should show all the records pertaining to all the values in X1
Report 2 (Details) Report 3 ((Details)
X1 Y1
X2 Y2
X3 -
X4 -
X5 -
- Yn
-
-
-
Xn
What I did so far was to pass the report parmeter in report 1 as "Fields! attribute X ,Value" and same for Y
The problem with is that only the first value of the group that satisfies condition gets passed on to the detail reports. Kindly let me know were I am going wrong and what should I be doing.
Awaiting your opinions
Thanks and Regards
GM
View 4 Replies
View Related
