How Can I Have Multiple Chart Options For A Report?
Apr 13, 2007
Hi, all experts here,
Thank you very much for your kind attention.
I have a question about how to have multiple chart options for a report? Like when I view a report, I want to see the report in different charts formats. It is possible in SQL Server 2005 Reporting Services (designer) to change from different chart options? Hope my question is clear for your advices.
Thank you very much in advance for your help and advices. And I am looking forward to hearing from you.
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.
Okay, here's an algorithm question for you TSQL gurus out there...
Due to circumstances beyond our control, our group has been tasked with a massive project and a very short timeline. And of course, timely completion is needed because our STOCK OPTIONS grants depend on this! And of course board meetings are always scheduled sooner than you expect.
Here's one of the killer questions we're trying to solve...
Given a table of employee ID's, associated supervisor ID's, and the amount of stock options given, how would you write a stored procedure to return, for any branch of the organizational tree, the sum of all the stock options in a particular branch?
What we need is something like : "sp_StockOptionsPerDepartment @SUPERVISORID=30" with a result : "1750".
Basically we're building an organizational chart of our company from this table, on the fly, and also counting up for certain branches of the org chart, the total stock options.
If a manager has two managers under him, and each sub-manager has three employees, then we want to know the total stock options that all 3+3+2+1 = 9 people possess. Basically it's the total pool of stock options for a department, or work group, or division, etc.
Got this to work for a small set of employees, but when we begin to scale up to entire departments, the query times out because it takes tooooo long...
Any ideas? ANY ideas at ALL would be helpful...
Comments (1) This is basically a tree-traversal algorithm, but conversion into SQL is not always so straightforward. Starting from an arbitrary root node, we must visit every child node underneath, walking all the way down to the leaves.
(2) We tried a brute force algorithm which is fast for smaller sets, but impossibly long for sets where we're dealing with hundreds of employees. Any cheats? Caching results as we go? Any ideas out there?
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'm writing an Insert form which will write records to a few tables. What I want to know is how do I write multiple answers to one question in different rows in the table but keeping the ID?
For example.
The form has the following fields:
HotelIDHotelFacilities (CheckBoxList)
Now each hotel (in this case) will only have one ID but more than one HotelFacility .
How do I get my table to read...
HotelID
HotelFacility
1
Bar
1
Restaurant
1
Cafe
1
Wi-Fi Access
I presume INSERT INTO tblHotelFacilities(HotelID, HotelFacility) VALUES(@HotelID, @HotelFacility) won't write more than one selected facility?Thanks,Brett
I need to create a series of reports, which will have multiple parameters. I will have 2 parameters, which I need to make available for both to be used, none to be used or one or the other.
I can achive this in the 'data' area of VS2005, the parameters are listed in the 'layout' area, however I can not get the report to run with out selecting both the parameters in the 'preview' area.
I think it may be to do with the Allow Null check box but I can not get it to work! with data type string.
He is my where clause, which works: -
WHERE (NewMainintro = @Intro) AND (NewStatus = @Status)
OR (@Intro = '') AND (@Status = '') OR (NewMainintro = @Intro) AND (@Status = '') OR (NewStatus = @Status) AND (@Intro = '')
The variable @Item can contain multiple values separated by a comma. e.g. ItemA, ItemB
But the STORED PROCEDURE query sees it as one string instead of 2 separate ones.
How do I solve this? Thanks.
Code SnippetWHILE(@StartDate < @EndDate) BEGIN IF LEFT(DATENAME(dw,@StartDate),3) NOT IN ('SAT','SUN') INSERT INTO @temp(myDate) VALUES(@StartDate); SET @StartDate = DATEADD(d,1,@StartDate); END
Code SnippetSELECT LEFT(DATENAME(dw, te.myDate), 3) + ' ' + CONVERT(char(11), te.myDate, 106) AS 'NewsDate' FROM @temp AS te LEFT OUTER JOIN NewsItemTable AS t ON t.NewsDate = te.myDate AND t.Item IN(@Item) WHERE t.NewsDate IS NULL
I need to set multiple values for some SQL statements, for exampleSET NUMERIC_ROUNDABORT OFFGOSETANSI_PADDING,ANSI_WARNINGS,CONCAT_NULL_YIELDS_NULL ,ARITHABORT,QUOTED_IDENTIFIER,ANSI_NULLS ONGOin a .sql file, but would like to reset them to their previous settingsagain for other SQL statements. I didn't find anything in books online. Ithought just calling "set" was enough, but apparently not ....Thanks.
I inherited a system and am moving our SQL2000 input data into a new 2005 database for web searching by clients. The conversion went pretty well, but I'm looking for suggestions on how best to index and query the data.
Details: We have almost 500,000 records in a pretty wide history table, and will be adding more records daily. The original table design has numerous sets of dates(first/last of different types), some small fields, and one large text field that is used for full-text catalog. The input dates are all stored as datetimes set to midnight by an insert trigger.
...
FirstPub as datetime
LastPub as datetime
FirstSale as datetime
LastSale as datetime
Name as varchar(50)
Addr as varchar(50)
DetailText as text....
I need to create a new asp.net page that can accept any specific date, or a range of dates (first/last publish date or range, first/last sale date or range) along with any of the optional 10 text fields. All input is optional, and any combination of fields can be entered. Result set has to include the primary key, key dates, and the large text field.
I've read many posts and articles, but find many different opinions about what works best. I found a similar solution that used a series of case statements with text operators indicating if the date was input or not. Other guys say no - just pass the dates as null without any additional control fields.
I found solutions saying it's best to pass dates as strings, and others that say to use datetime everywhere and just format the presentation. Some say convert dates to formatted dates and then compare them. Others say that causes table scans and should be avoided.
Questions:
1) What is the best way to get a compare dates that are entered through an asp.net page and stored procedures?
2) How to query either a specific date, as well as a range between the first and last date specified? Can it be done efficiently without needing additional screen fields for the range specification? If tried checking nulls, but am having trouble selecting multiple sets of date ranges and/or specific dates with one proc. There is always the old method of storing the selected keys in temp table, and then returning the distinct rows. Most of my experience has been in SQL 7.0 and some 2000, so I'm wondering if there are new ways to tackle the problem.
3) Better to create an index for each date type that might be selected, or one index that has all the main dates in one place?
4) Should we split the large text field to another table in our search database? It would seem to make sense to split them as we add records from the input system. Two tables linked with the same primary key seems like a good improvement from original table design. How would that affect the performance of returning all selected rows and including the large text field?
Friends, I would like to put employee name and type of leave on the y-axis and on the x-axis the date, so that users can select the date start and end and employee/employees and see the chart or leaves. But how to do that? My fields are: Name, LeaveDescription and Date. The data with me is for each employee, for each date, leave codes are there.
I am developing a report using SQL Server Reporting Services 2005. Now I need to restrict the Export to Excel functionality for some users, but allow it to others, on a given report (Not all reports). Please let me know if it can be done, and if Yes, then How?
we have a requirement that, when we click on the multiple series bars in a SSRS chart, it should drill through that chart and the detail report should filter based on these x axis and y axis of the bars selected.
As far as my knowledge, we can drill through the chart by selecting single series bar at a time in SSRS. Is there any way to acheive this multiple drill through of a chart.
Is it possible to incorporate data from 2 datasets into a single chart? For example I have 1 set of data from 1 db which allows me to display datapoint "A" by week. I have a second dataset from another db which allows me to display datapoint "B" by week. I'd like to plot each of these separate datapoints on a single chart with the x axis being week and the y axis being a stacked bar chart incorporating both A and B values as individual data series. Further, assuming this is possible, is it then possible to have 1 series displayed as an area while the 2nd series is a column or line?
If we decide to make Report Manager the UI facing both internet and intranet users, but prefer that our intranet users not face the performance hit created by SSL, and prefer that SSL be turned on for internet users, what options do we have for configuting our RS environment if all users ultimately use the same reports and we wish to keep number of licenses at a minimum?
Which one should be my Data fields? Which one should be my Category Fields? I don't think I need "Series Field". But the "ValueOFTheDate" was keeping showing as "Series Field".
The value of 'y' axis shows the value of "#of record for the date" which is wrong.
Is there any way to create a chart using data fields from more that one query?
I tried to create two different datasets, but the chart has to be bound to only one dataset. So when I drop the data field from the second dataset onto the chart I get a SQL error.
I've also tried UNION ALL. Each of these queries is correct by itself, but UNION ALL combines GLBUDAMOUNT and GLTRXAMOUNT into one field. I need them to be two different fields so that I can do GLBUDAMOUNT VS GLTRXAMOUNT in the chart.
I was wondering if anyone has ever written a chart with multiple datasets.
I need to be able to show sales dollars inflow by order date on one line and on the other needs to be sales dollars delivered by delivery date. So the all sections Values, Category groups, and Series Groups in the chart will be from 2 different datasets.
I have tried but it will not allow aggreates in the series groups.
I encounter this error when user try to subscribe using email delivery to one of the report. I have configured the Reporting Services to use Sharepoint integrated mode. This user is a regular user that has read access to the report item.
Below is the message detail:
A subscription delivery error has occurred. (rsDeliveryError) A subscription delivery error has occurred. (rsDeliveryError) One of the extension parameters is not valid for the following reason: The account you are using does not have administrator privileges. A subscription cannot be created for [emailaddress]. (rsInvalidExtensionParameter)
Is it true that user needs to have administrator privilege in order to subscribe for report? I don't think so, right?
I am just designing a simple chart in Report Builder - but when trying to drag any field into the value field of the chart (it doesn't change colour and won't accept any field).
If I drag a field into the category or series the curser changes and on approaching the chart properties they seem to change colour.
Hi, Is there a way to set the visibility of a chart as can be done for a textbox or table or matrix? My requirement is that the chart should be visible only if certain data conditions are met.
Hi, I've just started using Pie Charts and I'm stuck on a value problem (I think!). My category will be "Connection Status", and the values can be: success:human; success:machine; Connected; Null.
What I want to do is merge the "success:human" and "connected" values into one slice of pie. Is this possible?
Does anyone know how to create a bar chart in which the x-axis is dynamic (meaning I can have 0+ bars) and have the width of the bar being a fixed size?
I'm using Reporting Services 2008 R2, version 10.50.2550.0, and Visual Studio 2008, version 9.0.30729.4462 QFE. Not sure if the SQL version matters, since I haven't gotten the report out of the development environment.I've read lots of messages, including URL.... that show you can have multiple data entries per row in a Range Bar Chart, simply by turning DrawSideBySide to False.My data set basically contains four pieces of data, LineNumber, Equipment, StartTime and EndTime. I have data like:
(I actually have a date with my datetime entries, but that should give you the idea.) My chart is set up with Values of EndTime and StartTime. If I set my chart up with only Equipment in the Category Groups entry, my chart will show CLM1 from 08:00-08:30, and SS1 from 08:05 to 08:35. No sign of the other entries per equipment, although I added a tablix to retrieve the same data and it all shows up.Changing DrawSideBySide between True, False and Auto has no effect on the data displayed on my chart. If I add StartTime in the Category Groups, I do show all the data, but one row for each piece of data, which isn't what the customer wants.From what I can tell from various sites, I'm set up right, but it just isn't working.
I am using SQL Server 2008 Reporting Services Charts. I am using Chart Type as Column (3-D Cluster Cylinder). I am using X axis of graph as Time/Date field. I have two questions while displaying report in desired format:
1. Currently I have grouped Category field on Date field fetched from database. It groups values per day. Now whenever there is no data for a particular date, it skips that date in chart. How can I show each and every date on report graph in user given date range?
2. If there is no data for a legend of a chart, how can I display a Bar in Chart with value 0. This means, I have to show Bar with Zero value.
Currently if I Show data labels of Chart, it just shows data for 0 value but does not show Bar with 0 value which could be residing as flat on X axis for a particular legend.
Please help me resolving this issues for Chart graphs.
Hi to everyone.I have this problem in my chart control in report.I am retrieving datas from my database.My x-axis is consist of dates of the year,y-axis is the hit counts.My problem here is when i only have hits in only one month(ex december) and almost over 20 hits are retrieved,the bars of my graph become so overcrowded and overlayed which make the value unclear to see.And also another thing is my legend repeats the color when there are too much data.is there anyway to fix this or this is just a ReportingService bug??Hope not..hehe.. Thanx
I have a case. I have 3 numeric fields and 1 category field to be displayed on bar chart reports. the problem is 2 of the 3 numeric fields have significant different scale value. so I need to add additonal scale at the right side of bar chart to represent 1 numeric fields + the 2 other numeric fields at the left side scale (Y). Please advice. Thanks.
I am trying to display a line chart with cumulative totals over period of 12 months in a fiscal year. I know this can be achieveable in report designer using "Running value function".
Any idea how to achieve the same in Report builder ?
