I posted last year about getting and Expand All/Collapse All link working in reports, and it does work perfectly for on-demand reporting. I was able to get these working using the boolean report parameter and then the Jump to Report option going back to itself changing that parameter.
The issue that I have just discovered is that when a report with these Expand All/Collapse All links are in a report snapshot, clicking those links will cause it to re-render the report from the datasource at the time the link is clicked. This is an issue for one of the datasources we are using because the data is always changing, so when it goes back to re-render the counts and data returned are completely different then they were when the snapshot was first created. As time goes on the data will no longer even be in the datasource since it can only maintain 4 months of data.
Is there another way to get all detail groups to expand/collapse at once that does not require the report to be completely re-rendered from the datasource?
I'm relatively new to this, so bear with me here. (SQL Server 2005 Express, Datatypes are all varchar, int or money, nothing crazy...)
I currently have a table (not designed by me...) which looks like this:
ProjectID Months Expenses
3214 JAN 45.67
3214 MAR 56.78
1234 JAN 78.99
4567 MAY 43.56
And so on.... And I need this:
Project ID Jan Feb Mar Apr May Jun etc....
3214 45.67 56.78
1234 78.99
4567 43.56
I had attempted to do it using this code (really sloppy... I know. Beginner's attempt...)
DECLARE @Months varchar(4)
DECLARE @Counter int
DECLARE @Rowcount int
EXEC @RowCount=dbo.ReturnRowCount
SET @Counter = 0
WHILE @Counter <= @RowCount
BEGIN
SET @Counter=@Counter+1
SET @Months=(SELECT Months FROM TestData WHERE RowNum=@Counter)
SELECT @Months
WHILE @Months='JAN'
BEGIN
INSERT INTO SusansOutputTable (ProjectID, Jan)
SELECT ProjectID, Expenses FROM TestData WHERE @Counter=TestData.RowNum
SET @Months=''
END
WHILE @Months='FEB'
BEGIN
INSERT INTO SusansOutputTable (ProjectID, Feb)
SELECT ProjectID, Expenses FROM TestData WHERE @Counter=TestData.RowNum
SET @Months=''
END
WHILE @Months='MAR'
BEGIN
INSERT INTO SusansOutputTable (ProjectID, Mar)
SELECT ProjectID, Expenses FROM TestData WHERE @Counter=TestData.RowNum
SET @Months=''
END
WHILE @Months='APR'
BEGIN
INSERT INTO SusansOutputTable (ProjectID, Apr)
SELECT ProjectID, Expenses FROM TestData WHERE @Counter=TestData.RowNum
SET @Months=''
END
WHILE @Months='MAY'
BEGIN
INSERT INTO SusansOutputTable (ProjectID, May)
SELECT ProjectID, Expenses FROM TestData WHERE @Counter=TestData.RowNum
SET @Months=''
END
WHILE @Months='JUN'
BEGIN
INSERT INTO SusansOutputTable (ProjectID, Jun)
SELECT ProjectID, Expenses FROM TestData WHERE @Counter=TestData.RowNum
SET @Months=''
END
WHILE @Months='JUL'
BEGIN
INSERT INTO SusansOutputTable (ProjectID, Jul)
SELECT ProjectID, Expenses FROM TestData WHERE @Counter=TestData.RowNum
SET @Months=''
END
WHILE @Months='AUG'
BEGIN
INSERT INTO SusansOutputTable (ProjectID, Aug)
SELECT ProjectID, Expenses FROM TestData WHERE @Counter=TestData.RowNum
SET @Months=''
END
WHILE @Months='SEP'
BEGIN
INSERT INTO SusansOutputTable (ProjectID, Sep)
SELECT ProjectID, Expenses FROM TestData WHERE @Counter=TestData.RowNum
SET @Months=''
END
WHILE @Months='OCT'
BEGIN
INSERT INTO SusansOutputTable (ProjectID, Oct)
SELECT ProjectID, Expenses FROM TestData WHERE @Counter=TestData.RowNum
SET @Months=''
END
WHILE @Months='NOV'
BEGIN
INSERT INTO SusansOutputTable (ProjectID, Nov)
SELECT ProjectID, Expenses FROM TestData WHERE @Counter=TestData.RowNum
SET @Months=''
END
WHILE @Months='DEC'
BEGIN
INSERT INTO SusansOutputTable (ProjectID, Dec)
SELECT ProjectID, Expenses FROM TestData WHERE @Counter=TestData.RowNum
SET @Months=''
END
SET @Months=''
END
SELECT RowNum, ProjectID, Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, Dec FROM SusansOutputTable
I also tried using IF statement instead of the imbedded WHILE statement and had the same result. What it's returning is 98,765 results of the same thing, with the exception of the last 25 rows, which return what I expected (which still needs to be collapsed...) I know there HAS to be an easier way to do this, I'm afraid it might be a bit beyond me though, any help?
Ps. ReturnRowCount function just returns the row count of the base table, assuming a rowid column with an IDENTITY int variable, which I can safely assume in this case. Also, the counter seems to work fine, but something is wrong in my internal portion of the while loop I think...
I am having a difficult time trying to generate a result set that collapses multiple rows into one. However, I am also getting some of the columns that I am trying to SUM, to double their value.
I have created a report where I have Z-Index all set correctly, but I cannot see the desired effect of toggling as can be seen in the AdventureWorks sample of SalesOrder report that gets shipped with SSRS. What am I missing?
I have a report that is grouping phone calls by "Category" (Outgoing, Incoming, Voicemail, etc). When the report is rendered, each category (along with totals) is shown and the details (each actual call) are hidden. Using toggling, I allow the user to burst open any category to view all of the call details.
I join the query for this report so that even if a category has 0 calls for the day, it is still shown. When expand a category with no calls I get a blank record (as I should, because there are no call details).
The question I have is ... Can I have the toggle be conditional? Can I have the ability to toggle thr group only when there are call details? Can I have the + to the left of the category name not be shown?
Thanks in advance for any help! Merry Christmas!!!
On one of my reports, the +/- for expand/collapse of groups is reversed -- i.e. when the group is expanded it displays "+" and when the group is collapsed it displays "-". The display of the document map is correct.
I have an SSIS package that moves data from SQL Server to an legacy Access database. In SQL Server, there is a date/time column that I need to split into a separate date column and time column in the access database. Initially I just created a derrived column for the time and set the expression equal to the source date/time column from SQL Server. Unfortunately, that just makes the date column and time column the same having the full date/time.
What expression can I use during a derrived column transformation to assign just the date to a derrived column and just the time to another derrived column?
I want to compare two columns in the same table called start date and end date for one clientId.if clientId is having continuous refenceid and sartdate and enddate of reference that I don't need any caseopendate but if clientID has new reference id and it's start date is not continuous to its previous reference id then I need to set that start date as caseopendate.
INSERT INTO [GPO].dbo.tblMetric (KPI_ID, METRIC_ID, GOAL, REPORTING_MONTH, ACTUALS) SELECT
1 AS KPI_OWNER_ID , 23 AS METRIC_ID , .75 AS GOAL , CAST(Z.REPORTING_MONTH as DATE) AS REPORTING_MONTH , SUM(CAST(FTP_COUNT AS DECIMAL))/SUM(CAST(FULL_COUNT AS DECIMAL)) AS ACTUALS
[Code] ....
The insert column I am trying to get into is a date type. The original state of the field is YYYYMM varchar. How to get this into the table.
I have a table here. I want find a way of getting the latest date, when the code is the same. If the Declined date is null. Then I still want the latest date. E.g. ID 3.
If the declined date is filled in. Then I want to get the row, when the Datein column value is greater then the declined date only.
I tried grouping it by max date, but i got an error message when trying this out. Against the code
WHERE MAX(Datein) > Declined
An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference. What do I need to do to get both my outputs working?
So my data column [EODPosting].[MatchDate] is defined as a DATE column. I am trying to SELECT from my table where [EODPosting].[MatchDate] is today's date.
Is this not working because GETDATE() is like a timestamp format? How can I get this to work to return those rows where [EODPosting].[MatchDate] is equal to today's date?
I have a db-query that returns quite a few rows which I must show in a table. At initial load the report only shows the row group heading for the detailed data, and when clicked the detailed data for a group expands.
My problem is that reporting services assumes that when collapsed it should summarize everything in each column and display it.
Here is an illustration of the problem.
First the detailed view of my data (Notice the 5th and 6th rows which are simply the values in the 1st through 4th rows divided into groups - and the "Total" row shows the actual total for Row 1 Group Header: [-] "Row 1 Group Header" "Detail data 1" "10" "Detail data 2" "10" "Detail data 3" "4" "Detail data 4" "6" "Row 1 group 1" "20" "Row 1 group 2" "10" "Total" "30" The detailed view is just as I want it - no problems there.
However, when the detailed view is collapsed, reporting services calculates the sum for the column - which is 90 - not 30 as is the actual total: [+] "Row 1 Group Header" "90"
Is there any way I can redefine the formula/insert filters used for calculating the "collapsed-total" for each column when the row is collapsed? Or maybe simply prevent it from showing a "collapsed-total" altogether? Or can you think of another way to structure the data so the "collapsed-totals" gives me the actual value (30)?
I thought about splitting the values into a column for each data-group, so that I'd have a column for the "Detail data n" values, a 2nd column for the "Row 1 group n" values and a 3rd column for the "Total" values - this would yield correct "collapsed-total" values. But is really a plan B - I really do not want to do that unless it is the only way out of my dilemma.
as you can see, the records have a 30minutes time interval. i need to create a query to know if there are missing records in the table. so basically the result should be this:
In my report i have more than 10 sub reports each should be displayed when we click on some label but my requirement is to expand all the sub reports at a time or collapse the sub reports.URL.....problem here is it is a genius solution working beautifully in my local machine but in the client server if i click on the Expand/Collapse radio button it is asking for the parameters again.
I have a problem with collapsing of groups. It seems that the reports saves the expand/collapse state of groups between renderings ... bug or feature?
In my report I have a dataset which contains some numerical data over time.
The report is designed to show data from a selected month as well as January to selected month. A parameter is used to select the month.
The presentation looks like the following: Company total Department Employee Employee ... Department Emplyee ... ... The employees have their initial state set to hidden, with their respective department as toggler.
This works perfectly ... within Visual Studio. When I deploy this report to my web server strange things happens.
Fx 1) If I expand one department to see the data for the employees for may and then decide to look at data from another month. 2) I change the month parameter and click 'View report' to render the report again. 3) The report renders as it should. All departments are collapsed. 4) If I then expand some other department than before, both the newly selected department and the old one are expanded ... a behavior that is not that practical
It seems that the report saves the expand/collapse state for the groups between renderings. So when I click expand for the second time it expands the one I just clicked and the department which was expanded before rendering.
Is there a way to expand/collapse all items in the documentmap ?
I know there is an option DocumentMapCollapsed but this is to collapse/expand the documentmap panel. I would like to control the collapse state of the items into the document map ...
We have a bunch of Audit tables that contain almost exact copies of the operations tables. The audit tables also include:
AuditID - the audit action (insert, modify - old, modify - new, deleted) AuditDate - date and time of action AuditUser - User who did it...
At the end of the day I need to know for any given record what it looked like at the beginning of the day and what it looks like at the end of the day. There could have been numerous changes to the record throughout the day, those records I am not interested in. Only the first record and the last record of a give day.
I am going to be doing a lot of MIN(AuditDate) and MAX(AuditDATE) and .. WHERE AuditDate BETWEEN '10/1/2007 00:00:00' AND '10/1/2007 11:59:59' ...
Question: Whats better for performance:
1. Separating out the date and time and doing a clusterd index on the date.
2. Keeping date and time in the same column and just use a normal index.
I have a report with some groups which can all be expanded/collapsed by clicking on a textbox with an action attached to it. The example I used can be found here: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=600583&SiteId=1
But I want to hide the row containing this textbox (or the textbox itself) on the actual printed report. Is there any report item that can tell me if the report is in preview phase, or can it be solved in any other way?
I am trying to create a whole number DAX calculated column that is derived from a date column. Basically it gets the date from the source data column and outputs it as an integer in the YYYYMMDD format.So 01/OCT/2015 would become --> 20151001...I've been fidgeting with DAX but my problem is that I keep missing the leading zeroes for months and days. So 01/March/2015 becomes 201531 which is not what I want (I need 20150301 in this case).
has anyone out there found a way to get the best of both recursive hierarchy and drill down in the same report, ie without needing to know how many levels there are in your hierarchy, still being able to report them like a tree view with collapse and expand capability at each level?
How do I programmatically check a row group's Visibility or Expand/Collapse flag in a matrix table? For example, I have a matrix table contains the following groups:
Row groups: Facility --> Category Type --> Category Column groups --> year, quarter, month
I want to be able to programmatically update the table content if Category rows are not visible (Category Type row group is collapsed).
Basically, the sample raw data is a result from my last cte process which consolidate all the records. I want to find the closest date in PO ended using Receipt_date column but my problem, for this ESN R9000000000019761824, i wanted to get the latest or the last transaction date.
please see below DDL and sample data.
--this is the result from my last cte process Create table #sample (ESN nvarchar(35),ESN2 nvarchar(35), Receipt_Date datetime,PO_ENDED datetime)
insert into #sample(ESN,ESN2,Receipt_Date,PO_ENDED)values('990002036924452','990002036924452','2015-01-07 17:39:44.660','2014-01-09 04:13:29.000')
I have two columns in a table:StartDate DateTime and StartTime DateTime.The StartDate column holds a value such as 07/16/2004The StartTime column holds a value such as 3:00:00 PMI want to be able to add them in a stored procedure.When I use StartDate + StartTime I get a date two days earlier than expected.For example, instead of 7/16/2004 3:00:00 PM StartDate + StartTime returns7/14/2004 3:00:00 PM.Can anyone point out wht I'm doing wrong with this one?Thanks,lq
What is the syntax for adding a column where you are adding a year to a date in a date format? For example adding a column displaying a year after the participation date in date format?
