I have a SQL view that includes the following payroll data:
Employee#
PeriodEndDate
HireDate
Amount
What I'm trying to do is filter the data by choosing the desired period end dates and then totalling the Amount and dividing that amount to get an average daily rate. To do this I'm pulling the data from the SQL 2000 source view into Excel 2007 and creating a pivot chart.
In that pivot chart I'm able to select the desired period end dates and create a calculated field for my daily rate.
My problem is that I want to exclude employee's that have a Hire Date that is within 90 days of the latest selected period end date. For example, if the Period End dates selected are 3/15/2008 and 3/30/2008, then any employees with a Hire Date greater than 12/31/2007 (3/30 less 90 days) should NOT be included in the pivot chart.
Is there an easy way for me to do this, can I pass parameters to SQL via Excel?
1) Production data with column headers: Key, Facility, Line, Time, Output 2) Costs data with column headers: Key, Site, Cost Center, Time, Cost
The tables have a common key named obviously as Key. The data looks like this:
Key Facility Line Time Output Alpha
I would like to have two pivot tables which I can filter with ONE slicer based on the column Key. The first pivot table shows row labels Facility, Line and column labels Time. Value field is Output. The second pivot table shows row labels Site, Cost Center, and column lables Time. Value field is Cost.How can I do this with Power Pivot? I tried by linking both tables above to a table with unique Keys in PowerPivot and then creating a PivotTable where I would have used the Key from the Keys table.
Thanks a lot for answering my previous questions. Here's one more thing that I need. I have my data in one table called as Team_Aggregate. The fields there are TeamID, Date (1st of every month), Permanent Employeed, Contractor, Open Positions, Total Employees, Max Posns.
Now, when a user selects some months data that he wants to display I want it to appear something like this -
Columns as Month Names (Jan 08 Feb 08...June 08) Rows as Team Name and each cell = Team Total for that month and the corresponding team. Can I do something like this in sql? I want to display the data on the web eventually where I am using Gridview with datasource and databind methods.
There is a view on our server that has fields for project name and then 25 months, each row contains the descriptive name and the forecasted budget in each month. I need to create a chart showing the total budget for month and a running total for the year to date. In Excel this is easy with a Pivot Chart, how do I do this in SSRS 2005?
Is it possible to generate automatic refresh of excel 2013 table which displays some table of a power pivot model on file open?? I dont want to use pivottable (which supports this ...)
I have a pivot chart that displays 3 data series.Series 1 values range from 17 to 106.Series 2 values range from 1 to 18.Series 3 values range from 0 to 1.When I display all three series on the chart, the value axis labels run from1 to 120 with major unit lines at intervals of 20.When I remove Series 1, the chart automatically adjusts itself and thevalue axis labels run from 1 to 20 with lines at intervals of 2, thusnicely accommodating the value ranges of Series 1 and Series 2.All this is fine. But when I remove Series 2, leaving only Series 3,the labels run from 1 to 1.2 with interval spacing of .2. Since thesevalues are counts, it makes little sense to show decimal values.Can anyone provide advice on how to keep these axis labels fromshowing decimal values, if a user removes Series 1 and Series 2?Thanks.- Bob
It all works except that once we get to the current date, the CumulativeCompleted plateaus (as you'd expect), but the dates (x-axis) keep rolling. Now, I want the dates to roll on so I can project out a linear trendline but I want the graphing of the burndown to stop at today's date.
I am able to drill down a pivot graph in sharepoint's power pivot gallery using left mouse then magnefying glass (quick explore) but how on earth do i drill up again??? Using ie back brings the original report not the last drill i was at...
Creating a burn down chart using a running total of cumulative hours with the following formula:
CumulativeHoursLeft:=CALCULATE ( SUM('Projects'[Budget hours]) - SUM ( 'hours'[Hours] ), FILTER ( ALL ( 'hours'[Date] ), 'hours'[Date] <= MAX ('hours'[Date]) ) )
Works great except that in a Line Chart using [Date] as the Axis and CumulativeHoursLeft as the value, I get these spikes on days for which the employee reported no hours. I do know what exactly the measure is doing in this instance and I do not get this in a table, those dates simply do not appear. I have tried both Categories and Continuous for the Line Chart. I have also tried filtering where [Date] is not blank.how to get rid of the spikes?
I'm a relative newcomer to Power View. I've been playing with charts and have been struggling to combine both line and bar on the same chart. It would appear this functionality is not available. Considering this is basic functionality when it comes to charting, how to achieve this....
We have a excel 2007 file with a Pivot Table in it. We would like to use the same as data source (But all the source fields as it comes for Pivot). The key issue is that the data to the pivot is from a connection and points to DB - but there is no access to DB. So the Excel is static to us with a PIVOT in it.
Is there a way to get all the data that is avaialble as a source for pivot.
Ex. The select query has co1,co2....co 11 from xxxxx whereas the PIVot HAS ONLY cO1, CO2 AS ROW, CO3,CO4 AS COL, CO5 AS VALUE AND CO6,CO7 AS FILTER AND OTHERS MISSED OUT IN PIVOT.
THe SSIS as such provide Excel 2007 access to Access OLE DB driver with Excel 12 setting in advanced tab. But not sure whether we can give the range (or) a way around ot get all the data as available in excel regardless of what is used only in the pivot.
I would like to use a dynamic pivot table in my data source view. It seems that a named query can be only one sql statement. So, I cannot use my multi-statement procedure that creates a dynamic pivot table output.
What is the best course of action here? I could hard-code my pivot table query. I could maintain a redundant table in the pivot format. Do I have any good options?
I have an Excel 2013 file with lots of DAX connected to an Azure database. I'd like to reuse all that work by changing the data source for the PowerPivot model to a different database which is an exact copy (just empty) on the same server, but Excel won't let me. In PowerPivot I can change the database connection, the user ID and password as well as the connections name. When opening each table properties (inside PowerPivot model) the new connection is used and all old data is removed, but as soon as I refresh using Existing connections, both from PowerPivot or from the Excel Data tab, the old connection is used and old data is reloaded.
If I use Existing connections from inside PowerPivot, I can se that the new connection is highlighted and has the correct variable, but I think maybe that one is run first, then the old one is run afterwards (or something like that).
On the Excel Data tab, I can see that the old connection is the only one Excel itself seems to know about, but I cannot change anything there as it's read-only.
There must be a way to change this. Even with copy and paste it would take me days to recreate this Excel file from scratch and it would be a serious flaw and reduction of usability for PowerPivot.
Is it possible to have different chart (having their dataset from same shared datasets) like six on one report, then secondly can they all respond to same parameter filter.
I am trying to create a column chart that calculates the percentage of computers in our IT environment that are Actively communicating to our SCCM Server.
I have two datasets:
1. Total_Count_Of_AD_PC DataSet.
2. PC_With_Active_SCCM_Clients dataset.
Basically i wan to calculate the percentage for each Region (i.e. AP for Asia Pacific, EMEA, Americas).
Below is the Total_Count_Of_AD_PC Dataset screenshot.
Below is the PC_With_Active_SCCM_Clients dataset.
Below is the expression that i used that is causing the error.
The Y expression for the chart ‘Chart2’ has a scope parameter that is not valid for an aggregate function. The scope parameter must be set to a string constant that is equal to either the name of a containing group, the name of a containing data region, or the name of a dataset.
Is it possible to include a currency symbol in an amount-field in PowerPivot/Pivottable based on a Currency column in a table? Something as the same as with SSAS MD. And I don't want fixed values in my code.
When I export the report in excel format the chart is displayed as picture. I want it to be displayed as editable chart.Does Office Writer work in this situation and did anyone use Office Writer to accomplish same type of problem.Is there any other method or product we can use instead of the office writer.
I've question about how to handle structural datamodel changes in a datasource of PowerPivot. Suppose I'm developing a starmodel in SQL Server and sometimes a datatype changes or a name of a field changes in a table. It seems to me that PowerPivot handle this not gracefully as Analysis MD does (mostly). I received an error because of a wrong fieldname or even no error when a dattype changes in PowerPivot. Is this common or do I something wrong here. Does this mean that every time the datamodel changes the PowerPivot should be recreated? Or am I missing the clue here?
Hi all, I want to pass a parameter into my OLE DB source. For eg, I want to set the condition SELECT * FROM "tablename" where ID = parameter <----------------- I do not know how to do this? can it be done by using package variable?
I need to create a chart with the following features
1) Bar chart that has data for 3 years (3 series) 2) Line chart that has the same data as per the above points on the bar chart but this is a running total. (3 series) 3) These data points are for the 12 months 4) there should be a secondary axis for the cumulative one
I have an ASP page which uses an SQL data source control, as shown below: <asp:SqlDataSource ID="mySQL" runat="server" ConnectionString="<%$ ConnectionStrings:mySQLConnectionString %>" SelectCommand="SELECT device FROM device_table WHERE (device_id IN (@devicelist))"> <SelectParameters> <asp:Parameter DefaultValue="" Name="devicelist" Type="String" /> </SelectParameters> </asp:SqlDataSource>The problem is getting the "IN" clause of the select statement to work via the ASP page with code behind in C#. If I test the query in the page designer when configuring the SQL control by inserting 'device-1','device-2','device-3' as the value for the "devicelist" parameter, everything works as expected. However, when that same value is written programmatically into the DefaultValue of the parameter, the resulting query contains no results (as if the string contents are invalid). If I alter my program to supply a single entry (with no commas) it seems to work as well. I suspect the comma (and possibly the single-quote) characters in my string when multiple entries are involved are causing the issue.Is there a proper technique for getting the requisite quote-delimited, comma-separated list into the SQL control command parameter correctly? Should I be using some sort of escape sequence for the single-quote and comma characters? Should I be using another "type" for the parameter; one that accepts an array of entries? I have not been able to find any examples or documentation that provides an answer.
Hello everybody, I'm new using ASP 2.0, I was trying to find out how do i tell a SqlDataSource the parameters it will be using for the select query. I was looking at the Configure Data Source wizard and when I press the where botton to create the parameters, I do not know how two of the available Source selections work which are: (Query String, and Session). What I want to understand is how do I pass the parameter value during the form load like I used to do with ASP 1.1. I used to write: during the pageload event: me.dataAdapter.selectparameter("@param").value = [myVar] me.dataAdapter.fill(Me.ds)
I tried to do something like this for the SqlDataSource but didnt work. can anyone tell me how can I acomplish this?
Passing a date parameter in a stored procedure through OLEDB Source (Data flow task). We have a multipurpose stored proc in which one of the many expected parameter is a date. It keeps giving me the following error:Description: "Operand type clash: int is incompatible with date"- although there is no integer.
One way to fix it would be change the data type from date to datetime in proc but I can't do that since it is multi-purpose proc. I didn't find anything good on the net.
Hi, I have report parameter and its values are static lik (ABC, DEF, GHI ) etc. I want to select all as a default for this parameter. How I can do this?
i don't know weather is it possible or not..but Can any One tell's me How can i refresh the Chart Values.. acutally what's happening..
i have two Chart in a reprot .. One is Main Category and other one is SubCategory... acutally what i want.. in Main Category chart sum of Quantities of Subcategory values comes in bar or any other format.. and when i click on Main chart any bar it's refresh the other chart and return the result of subcategory under that main category in details...
i don't know is this possible .. acutally i m very new in reproting.. infact that's my first report.. so i want to do this.. if any article or any help anyone can provide me..
Each day I receive a file with a different name. For example, the name is filename_mmddyyyy.txt where filename_ stays constant and mmddyyyy is the date of the file. The file is always in the same format.
I want to build an SSIS where I pass it this file name. I can write a script to generate the correct file name. How do I build the SSIS so it can accept the input parameter and find the correct file to process?
I'm using following command to populate my OLE DB Source. I have two of those in each Data Flow in my package. One of the OLE DB Sources points to my source database the other to the destination. In order to limit the number of rows I use the WHERE clause below. The [EnergyMiserFSRLive] being the the name of the source database. The Connection manager points to the destination database.
I would like an elegant way to replace [EnergyMiserFSRLive] with a parameter which I can reuse in each of my many data flows rather than use this hard coded value [EnergyMiserFSRLive].In particular I'm after the syntax of the below query that uses the parameter for [EnergyMiserFSRLive].
SELECT [SitesId] ,[SiteName] ,[FilePrefix] ,[City] ,[StateProv] ,[Country] FROM [Sites] WHERE SitesId >= (SELECT MIN(SitesId) FROM [DBNameFSRLive].[dbo].[Sites]) ORDER BY [SitesId]
In SSIS in Sql task we have option to pass parameter or variable..But in Data Flow Task when we use Data Reader Source using ADO.NET connection..There is no option to pass parameter or variable Or no option to receive a parameter or variable .
I am having a query were it need to pass a parameter.in sql task ...And Data Reader Source have to receive this parameter from sql task .
Sql Task finds a value of parameter and pass to DataReader Source in DataFlow Task .. ...
Please can any one help me to solve this problem of Receiving parameter or variable in DataReader Source using DAO.Net connection in DataFlow Task..thank you dilsa
Hi, I am not getting Mining Accuracy Chart and Min ing Model Prediction Plz tel me how to do.And how to use the filter input data used to generate the lift chart and select predictable mining model columns to show in the lift chart
