I am using a matrix to display current and historical financial data. To do this, I am using 5 different datasets. Each dataset contains 1 row of data for a specific year for a specific account.
Now that problem I am having is displaying this data in a chart. When I create a new chart, it asks which dataset I would like to use. I do not want to use any specific dataset, I want to get chart's data from the matrix. Is this possible?
I have this main chart setup. It pulls data from a query based on the selected parameters.
I want to create multiple charts for every catagory in the result set.
On the main chart there is a catagory called "Missing".
I want a chart for this category to display when the main chart is loaded. So below the main chart it would show a chart for each of the categories on the main chart.
Basically this report returns information on a certain part number displaying its problem types. Then I want it to create other charts for each problem type displaying the problem sub-types.
Hello, I need to know what means are available to change the Minimum and Maximum Y-Axis scale values rather than leave them simply as they are in the properties. Right now whatever min/max values I enter into the scale for the Y-Axis are unchangeable. I need to be able to change the min/max scale values based on the value of the data at run time.
Hi,I am studying data mining features of SSAS and for a workshop I'vecreated 2 views derived from vTargetMail view of AdventureWorksDW.Train data consists every record except those in Pacific, and testview consists only records from Pacific area.1. I've created a mining structure based on Decision Tree and selectedBikeBuyer as predictable column.2. According to input column suggestions, I've selected Age,Eng.Education, NumberCarsOwned, YearlyIncome, CommuteDistance,NumberChildsatHome and TotalChildren as input columns,3. I've modified no other setting, and deployed project.I can get training results in decision tree browser and dependencynetwork (and both seem to give rather logical results) however, when Itry to browse lift chart or classification matrix I get an emptyclass.matr. and a lift chart of a single 45 degree line.Am I missing a step, or must I do some fine-tuning on (what)parameters?Thanks...
Hi, I am studying data mining features of SSAS and for a workshop I've created 2 views derived from vTargetMail view of AdventureWorksDW. Train data consists every record except those in Pacific, and test view consists only records from Pacific area.
1. I've created a mining structure based on Decision Tree and selected BikeBuyer as predictable column. 2. According to input column suggestions, I've selected Age, Eng.Education, NumberCarsOwned, YearlyIncome, CommuteDistance, NumberChildsatHome and TotalChildren as input columns, 3. I've modified no other setting, and deployed project.
I can get training results in decision tree browser and dependency network (and both seem to give rather logical results) however, when I try to browse lift chart or classification matrix I get an empty class.matr. and a lift chart of a single 45 degree line.
Am I missing a step, or must I do some fine-tuning on (what) parameters?
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.
Hi all, Is it possible with Reporting Services to create a scatter chart where the colour of the report area are dependant upon X or Y axis value? For example: can I specify that if the point values are greater than 75 then the color of those parts shall be red? and other are green as an example.
Furthermore can I get X and Y axis value of chart? also i like to know that i want add a line in scatter report based on a value which is not a Major or Minor gridline.can i do it? pls advice.Looking for your help desparately.
Hi everyone, I have created a matrix report which takes 3 parameters being store, year and quarter which then displays budget information in a matrix. This all works fine but I would also like to have a bar chart below the matrix to visually show the results.
However when I add the bar chart and add the relevent dataset details etc the chart does not appear when I preview the report. I wondered if the chart wasn't showing because the parameter values are not being passed to the chart.
Has anybody any idea of adding charts to reports in this way or knows of any instructions to do so.
The values on X-axis are scaled from -100 to +100. I want to colour the band from -100 to 0 with red colour and that from 0 to +100 in green colour, and i want to provide legend for these bands/sections. How can i do this?
I am producing a report in Report Builder 2 containing a chart and table. The user is able to select single or multiple choices on a parameter called Specialty. My present chart is only suitable when one Specialty is selected. Therefore I only want this to be visible when the user has selected one Specialty. I am planning to create a second chart which is suitable for than more than one selection. So if the user selects more than one Specialty the existing chart will not be visible and instead will be replaced by the second.I think this should be done using Chart Properties>Visibility but cannot develop the logic of the expression.
I have a matrix that looks similar to the table above with two row groups and one column group. Does anyone know a way to manually do column and row based averaging without using the avg() function -as the data is not numerical? Thanks
I am on the verge of being able to do exactly what I want, but just can't seem to find the right combination of things to do it. I'm sure all of you wonderful folks will be able to point it out to me immediately, but I've been looking at it too long or something....
I have a record of individual sales with the state, and quarter of the sale.
sale_id state quarter 001 NY 2005Q1 003 WI 2006Q2 etc.
I create a report with a matrix to show count(sale_id) with Quarter as the column group and State as the row group. This works fine.
Now what I want to do is to get percentages based on quarterly sales. In other words, what percent of sales for 2005Q1 in NY vs. all sales in 2005Q1. So I create a second dataset (called total) with an SQL query like so:
SELECT count(sale_id) FROM data_table WHERE quarter = @QueryQuarter
Now, back in the matrix I want to use the column that we're in (2005Q1, 2005Q2, etc.) as the value that is passed to this query.
This is a simple concept, but I can't seem to figure out the correct call to pass the column group to the query as the parameter.
Thank you for any pointers you might be able to give. As I said, I'm right on the verge and just can't quite get it.
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?
Do some one has idea how we can create pagebreak in matrix based on RowNumber.Say I have 40 rows in matrix(I don't have group on which I can create a group by setting PAGE BREAK AT THE END)
=ROWNUMBER(NOTHING) MOD 10 = 0 ,TRUE,FALSE
I don't have any distinct value on which I can create Group like
= IIF(Parameters!PageBreak.Value,Fields!CategoryID.Value,1) and setting Page Break At The End
I have created a matrix report having "Probability" in Row and "Impact" in Column and "Risk ID" in Data Part .
Now the problem which I am facing is I need to fill matrix with different color value based on combination of Probability and Impact.
I am able to write the expression in "Fill" area of "Risk_ID" text box but the problem is only those text box gets colored which are having Risk-Id value in it like shown below.I want all text box to be colored even if no Risk_Id is there in any text box.
I am trying writing the correct logic so that all text box gets colored even if there is no Risk _Id in it.
I have a matrix report with 2 column SaleAmount and ProfitAmounts by Month like
Sale Profit Dealer 5/1/2007 6/1/2007 7/1/2007 5/1/2007 6/1/2007 7/1/2007 A 100 200 300 20 25 15 B 200 250 50 30 45 19
how can i do following 3 things
1)Add Total column for Sale and Average column for Profit 2)Sort report by lastMonth of Sale (here 7/1/2007) High to low 3)if last month of sale(here 7/1/2007) is less than second last month here (6/1/2007) whole row should be red
I am VERY new to SQL and I do not know if I am word this question correctly, so forgive me.
I would like to create a new table that does calculations on some of the same fields. •IPA number (from the IPA_Num field) •Total number of Rx's (sum of rx field ) •Total Paid( sum of amt_paid field) •Total generic paid (If the field drugs = 1 then sum the amt_paid field) •Percent generic (If the field drugs = 1 then sum and divide by the sum of the field rx) •Percent formulary (if the formulary field = A then sum and divide by the rx field) •Cost difference ( Average amount paid per brand Rx - average amount paid per generic Rx )
I have a spreadsheet that we download from one of our customers that contains Sales Order Release information. The spreadsheets contains 1-n releases per part #.
I have a SQL 2000 view that groups this information into a smaller table (after importing into SQL).
I need to take this view and create 1-n release records based on our production run qty and put it in a new table. I have a field in my view that contains the desired # of records to create.
Since I never used TSQL to loop through table/view, how do I do this. Following is what the output should look like:
From SQL view --------------------------------------- Part #: 11124A1 Qty Due:175 Run Qty: 50 # Release to Generate: 4
I have 2 tables that I need to merge let me explain. I have a range of product ID's that have a product grouping of * meaning all product groups. So I have a table with products and one with around 100 groups
ProdID ProdGrp -------- --------- 11 * 12 *
ProdGrp ProdGrpDesc --------- --------------- A Prod Group A B Prod Group B C Prod Group C
I need a table which looks like the below but I have no joining mechanism
ProdID ProdGrp -------- --------- 11 A 11 B 11 C 12 A 12 B 12 C
I would like to reopen this discussion. I need to create this tableusing SQL that will return this dataset.BRAID RATEDEFID MIN(BRADATE) MAX(BRADATE RATECODE PRODNAME614 14 12/1/2002 12/4/2004 Rack BeachTower Terrace618 14 12/8/2002 12/11/2004 Rack BeachTower Terrace622 14 12/15/2002 12/19/2004 Rack BeachTower TerraceI have provided the test data below and the SP I have been working with.The SP works fine as long as there are 4 seqential dates. I getunpredictable results when the count <> 4. I am also concerned on whathappens at the end of the month. For example, 1/31/2004, 2/1/2004.Does this affect the SP? I appreciate your continued support.MichaelCREATE TABLE [dbo].[BASERATEAVAIL_Tmp] ([BRAID] [decimal](18, 0) NOT NULL ,[RATEDEFID] [decimal](18, 0) NOT NULL ,[BRADATE] [datetime] NOT NULL ,[RATECODE] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NOTNULL,[PRODNAME] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NOTNULL) ON [PRIMARY]GOINSERT INTO dbo.BASERATEAVAIL_Tmp(BRAID, RATEDEFID, BRADATE, RATECODE, PRODNAME)VALUES (614, 14, '12/1/2002', 'Rack', 'Beach Tower Terrace');INSERTINTO dbo.BASERATEAVAIL_Tmp(BRAID,RATEDEFID, BRADATE, RATECODE, PRODNAME)VALUES (615, 14, '12/2/2002', 'Rack', 'Beach Tower Terrace');INSERTINTO dbo.BASERATEAVAIL_Tmp(BRAID,RATEDEFID, BRADATE, RATECODE, PRODNAME)VALUES (616, 14, '12/3/2002', 'Rack', 'Beach Tower Terrace');INSERTINTO dbo.BASERATEAVAIL_Tmp(BRAID,RATEDEFID, BRADATE, RATECODE, PRODNAME)VALUES (617, 14, '12/4/2002', 'Rack', 'Beach Tower Terrace');INSERTINTO dbo.BASERATEAVAIL_Tmp(BRAID,RATEDEFID, BRADATE, RATECODE, PRODNAME)VALUES (618, 14, '12/8/2002', 'Rack', 'Beach Tower Terrace');INSERTINTO dbo.BASERATEAVAIL_Tmp(BRAID,RATEDEFID, BRADATE, RATECODE, PRODNAME)VALUES (619, 14, '12/9/2002 ', ' Rack ', ' Beach Tower Terrace ');INSERTINTO dbo.BASERATEAVAIL_Tmp(BRAID,RATEDEFID, BRADATE, RATECODE, PRODNAME)VALUES (620, 14, '12/10/2002', ' Rack ', ' Beach Tower Terrace ');INSERTINTO dbo.BASERATEAVAIL_Tmp(BRAID,RATEDEFID, BRADATE, RATECODE, PRODNAME)VALUES (621, 14, '12/11/2002', ' Rack ', ' Beach Tower Terrace ');INSERTINTO dbo.BASERATEAVAIL_Tmp(BRAID,RATEDEFID, BRADATE, RATECODE, PRODNAME)VALUES (622, 14, '12/15/2002', ' Rack ', ' Beach Tower Terrace ');INSERTINTO dbo.BASERATEAVAIL_Tmp(BRAID,RATEDEFID, BRADATE, RATECODE, PRODNAME)VALUES (623, 14, '12/16/2002', ' Rack ', ' Beach Tower Terrace ');INSERTINTO dbo.BASERATEAVAIL_Tmp(BRAID,RATEDEFID, BRADATE, RATECODE, PRODNAME)VALUES (624, 14, '12/17/2002', ' Rack ', ' Beach Tower Terrace ');INSERTINTO dbo.BASERATEAVAIL_Tmp(BRAID,RATEDEFID, BRADATE, RATECODE, PRODNAME)VALUES (625, 14, '12/18/2002', ' Rack ', ' Beach Tower Terrace ');INSERTINTO dbo.BASERATEAVAIL_Tmp(BRAID,RATEDEFID, BRADATE, RATECODE, PRODNAME)VALUES (626, 14, '12/19/2002 ', ' Rack ', ' Beach Tower Terrace ');CREATE PROCEDURE usp @n INTASSET ANSI_WARNINGS OFFSELECT MIN( braid) AS "braid",ratedefid, ratecode, prodname,MIN( CASE n WHEN 1 THEN bradate END ) AS "min_date",MAX( CASE n WHEN 0 THEN bradate END ) AS "max_date"FROM ( SELECT t1.braid, t1.ratedefid, t1.ratecode, t1.prodname,t1.bradate,COUNT( * ) % @n AS "n",( COUNT( * ) - 1 ) / @n AS "r"FROM BASERATEAVAIL_Tmp t1INNER JOIN BASERATEAVAIL_Tmp t2ON t2.bradate <= t1.bradateGROUP BY t1.braid, t1.ratedefid, t1.ratecode, t1.prodname,t1.bradate) DGROUP BY r, ratedefid, ratecode, prodnameHAVING MAX( CASE n WHEN 0 THEN bradate END ) IS NOT NULL ;Usage:EXEC usp @n = 4--Anith*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
Hi,We have XML files (and its XSD) that are dump of our tree-like datastructure in memory. These XML files may potentially have unlimitedlevels of nesting because some elements can contain themselves(recursively defined).We want to build some reports using some third party reporting tool(Crystal Report, for example).The first question is - is there any way for Crystal Reports toprocess such complicated (some elements have recursive definitions)XML file directly? If not, anyone knows any other reporting tool thatcan do that job?The second question is - if we have to develop a small applicationthat converts the XML to dataset, anyone who has done similar thingbefore can give us some recommendations as to which approach is theeasiest to take (Java, .NET, etc.)? I'd appreciate it if someone canpoint me to some resources to get me started.Another approach is to convert the hierarchical XML into relationaltable set and store the data into relational database. In that case,the reporting tool can simply read from the database. I'd appreciateit if someone can let me know if there is any softeware/program thatcan do the conversion. (I heard that some database server can do theconversion from XML to relational tables, but didn't find anythingconcrete.)Any comment/recommendation is appreciated!Thank you for your help in advance!!
in my report model project, i'm able to successfully create and deploy a report model based on a data source view that is in turn based on a table object in my data store.
but if i try to create a report model that's based on a a data source view that is in turn based on a view in my data store, well then, the report model that gets created comes up blank - i.e. shows no objects.
What gives? Is it not possible to create a report based on a view? that can't be right!
I would like to know if for example i have some tables in my DataBase and i need from each table to get for instance, the Name by the ID, how can i make only one procedure which use parameters and variables so i can use this SP to get the Name By ID for all the Tables?
My application uses 2 databases from 2 different server. When i create a report model in business intellegence development studio I can only create 1 datasource for each database, and 1 datasource view for each datasource. Then when I create a new report, I can only use one datasource.
Is it possible that my report can be built on more than one databases?
CREATE TYPE dbo.MyTableType AS TABLE ( Name varchar(10) NOT NULL, ValueDate date NOT NULL, TenorSize smallint NOT NULL, TenorUnit char(1) NOT NULL, Rate float NOT NULL PRIMARY KEY (Name, ValueDate, TenorSize, TenorUnit) );
and I would like to create a table of this type. From this answer [URL] .... the suggestion was to try
CREATE TABLE dbo.MyNewTable AS dbo.MyTableType
which produced the following error message in my SQL Server Express 2012:
> Incorrect syntax near the keyword 'OF'.
Is this not supported by SQL Server Express? If so, could I create it some other way, for example using `DECLARE`?
I wish to build a table based on values from another table.I need to populate a table between two dates from another table. Usingthe START_DT and END_DT, create records between those dates.I need a new column that is the days between the date and the MID_DTThe data I wish to end with would look something like this:PERIOD DATE DAY_NO200602 2005-07-06 -89200602 2005-07-07 -88200602 2005-07-08 -87<...>200602 2005-10-02 -2200602 2005-10-03 -1200602 2005-10-04 0200602 2005-10-05 1<...>200602 2005-12-18 75CREATE TABLE "dbo"."tblDates"("PERIOD" CHAR(6) NOT NULL,"START_DT" DATETIME NULL,"MID_DT" DATETIME NULL,"END_DT" DATETIME NOT NULL)INSERT INTO tblDates VALUES('200505',2005-04-12,2005-07-05,2005-09-12)INSERT INTO tblDates VALUES('200602',2005-07-06,2005-10-03,2005-12-18)INSERT INTO tblDates VALUES('200603',2005-10-04,2006-01-17,2006-03-27)INSERT INTO tblDates VALUES('200604',2006-01-18,2006-04-10,2006-06-19)INSERT INTO tblDates VALUES('200605',2006-04-11,2006-07-04,2006-09-11)INSERT INTO tblDates VALUES('200702',2006-07-05,2006-10-02,2006-12-18)
We have a product that we were experiencing high recompile rates do totemp tables, we have re-written them to use table variables to reducerecompile rate. The problem I am having is that we have somecustomers still on SQL 7 where the table variables were not valid. Iwould like to have one installation script that would create thestored procedure with table variables on SQL Server 2000 and withouton SQL Server 7, I have been unsuccessful do to the limitation thatCREATE PROCEDURE statement cannot be in a script with any otherstatements.Was wondering if anyone else has run into this and found a way aroundit.
I would like to transfer selected data from an ODBC-based table to a OLEDB-based table. However, there isn't a data flow source on the Data Flow Design screen to accomodate such an action. Please help!
