Iterate Thru A Dataset To Generate Multiple Charts
Jan 17, 2008
Hi, has anyone been able to implement something like this?
I have two data sets
1) List of all stores
2) Table containing the following fields: store, sales as of end of quarter, quarter end date
I am creating a graph that would track the trend of sales quarter per quarter for each store and would need to have an individual graph for each.
Is there a way to dynamically do this rather than creating an individual recordset for each store?
Thanks
View 1 Replies
ADVERTISEMENT
Jun 15, 2007
Hello,
I have the following tables :
Area
areano
areaname
Locality
locno
areano
RequestType
reqid
reqdtls
WebSummit
SummitId
RequestorName
DateOfRequest
reqid
Areano
I want to display the total no. of Addition, Removals and Relocations during the past 3 months in a paticular Area.
I have written the query for this.
select q.[areaname] as Area,
classCount as TotalCount, ReqType
from ( select a.areano,
a.classCount, ReqType
from ( select areano,
count(*) as classCount, reqdtls as ReqType
from WebSummit
join RequestType
on WebSummit.reqid = RequestType.reqid
where date_created >= dateadd(mm, -3, getdate())
group by areano, reqdtls
) a
join WebSummit b
on a.areano = b.areano
group by a.areano,
a.classCount, ReqType
) p
join Area q
on p.areano = q.areano
The query works fine and has been tested. Now I want to display this in chart format.
The display should be in a chart format, one for each area showing a bar for each RequestType.
I have placed "TotalCount" in the Data Fields section and ReqType in Category fields. How do I use the Filter to set an expression so that it displays the data in a seperate chart for each Area.
I tried using =Fields!Area.Value and set it to Area1 to display only Area1 values, however the preview shows nothing.
Plz help me out.
Here's some sample data
Area
AreaNo AreaName
1 Area1
2 Area2
3 Area 3
4 Area4
5 Area5
6 Area6
7 Area7
Locality
LocNo AreaNo
1 1
2 1
3 1
1 2
2 2
1 3
2 3
3 3
4 3
1 4
2 4
1 5
2 5
3 5
RequestType
reqid reqdtls
1 Addition
2 Removal
3 Relocate
WebSummit
SummitId RequestorName DateOfRequest reqid AreaNo
1 John 12/6/2007 1 1
2 Jack 13/6/2007 1 1
3 Bill 12/6/2007 2 1
4 Ben 12/6/2007 3 1
5 Dale 14/6/2007 1 2
6 Evjen 15/6/2007 1 2
7 Fuller 16/6/2007 2 2
8 Jimmy 16/6/2007 3 2
9 Kart 16/6/2007 3 2
10 Fuller 16/6/2007 3 2
Regards,
Vidya
View 1 Replies
View Related
May 20, 2015
I'm looking to build a report that is basically a series of line charts that is plotting a value over time. I can accomplish this one chart/dataset at a time, but I believe with 'List's this can be achieved in a more automated fashion.If I have some data similar to this:
Area_ID, Location_Name, DataValue, ReadingDate
1,Site1,100,1990-10-10
1,Site1,110,1990-11-11
1,Site2,105,1994-01-07
1,Site2,105,1994-02-07
2,Site3,113,1994-06-02
2,Site3,120,1994-07-30
2,Site4,120,1994-06-30
2,Site4,120,1994-07-30
I want the charts grouped by the Area_ID, and then each Location_Name is a Series in that chart. So in this example, I would end up with 2 charts (Areas 1 & 2), each have 2 series (Area 1: Site1, Site2 and Area 2: Site3, Site4)Is it possible to build this with a list? or do I need to structure the data differently?
View 6 Replies
View Related
Mar 18, 2008
Can you loop through the records of one dataset and create a chart for each record?
View 2 Replies
View Related
Aug 24, 2007
Hi,
I am upgrading all my access reports to reporting services. One of my access reports, contains a chart which is set a a multiple chart (so I can see a chart of data for each day in the data set). Is it possible to do the same using the SQLserver 2005 chart component?....I cannot see an obvious button to press which makes a single chart a multiple one.
If this is not possible in vanilla reporting services, can anyone suggest a thrid party add-in that will rovide the functionality I want? (cheaply lol!)
Cheers,
Kevin.l
View 18 Replies
View Related
Aug 13, 2007
Hi,
With Report Builder, I created a basic chart using the chart Wizard..
I am wondering If I can create some 5 charts on a Single Page(Webpage), like a Mini Dashboard.
Can someone please guide me in this regard ?
Thanks in Advance for your help.
Regards,
Sundar
View 6 Replies
View Related
Jun 4, 2015
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.
View 1 Replies
View Related
Apr 30, 2008
am trying to generate the same batch id for the records that I am trying to insert..
for example,
I have 3 records
1001
1002
1004
and I want to insert with the same batch id starts with 1
batchid id
1 1001
1 1002
1 1003
and next tiem i insert with new records
1005
1008
1009
and i insert with an incremented batch id which is 2
batch id id
2 1005
2 1008
2 1009
how can i generate the same batch id for each transaction?
View 2 Replies
View Related
Nov 12, 2007
Hi
I am new to Reporting service, Trying to create a report RS2005.
I defined two dataset (query getting data from same database)
In report designer, I inserted a group and drap columns from first dataset. and inserted second group, when i dragcolumns from second dataset it shows like this
=First(Fields!GroupStatus.Value, "ParentGroup")
in my first group columns shows like
=Fields!Member.Value
when i run the report i am getting one row in second group.
this looks like very basic mistake I am doing, since I don't have any experience in RS2005, i am posting this question,
thanks in advance
kal
View 4 Replies
View Related
Apr 26, 2007
I understand how to find (generate) missing dates for the year 2006 if I have a range value like 1-1-2006 to 3-1-2006. (I'm just using 2006 for arguements sake - the needed approach starts back in 2004 or so and will extend to the current day)
Now the question that presents itself is, is there an elegant way to do this same process with an arbitrary number of date ranges per customer? There would be 1 record per range, per customer
ie:
customer, start date, end date
1 _______ 11-1-2006 : 11-17-2006
1 _______ 12-15-2006 : 12-31-2006
1 _______ 1-5-2006 : 1-31-2006
What I'd like to generate is a record per missing date in 2006, which would be:
4 records for 1-1-2006 to 1-4-2006
several records for 2-1-2006 to 10-31-2006
and then more for 11-18-2006 to 12-14-2006
As I said before, the number of ranges isn't static. It could be 1 or a dozen and could increase as time goes on. At this time, all I can see to potentially make it work in query is to do a dozen joins - which kind of sucks.
My other thought is to do specific processing per customer, per range gap, but it will be terribly slow.
View 3 Replies
View Related
Apr 24, 2007
Hello all,
I'm new to SSIS and have a question about the best way to generate multiple Excel files with my current package design. I have a stored procedure that I run from an Execute SQL Task in a foreach loop container, and it generates results as appropriate for each of the parameters it loops through.
What's the best way to take the result set from each of those executions and generate an Excel file from each set? How do I map that result set variable to be the input for creating a new Excel file? Is this best done as a script task somehow?
Should I not be using SSIS for this task? I thought I would just create a package and schedule it to run daily with SQL Agent, and it would autogenerate the Excel files as needed.
Thanks for your advice!
View 6 Replies
View Related
Jan 15, 2007
Dear all,
I have a package in which, when a Cost Center has X as a value, I must insert not X but many different Y value, which are associated with X. How can I gather and treat those Y values? Only using a Script Component?
Regards,
Pedro Martins
View 1 Replies
View Related
Jan 30, 2007
Hi,
I have to design a tabular report where the data in all the columns of the table comes from the cube, but for one column where the data comes from the ODS table.
Could anyone please let me know if it is possible to combine the results of the query from a cube with query from an ODS table and display together in a single report?
Any thoughts on this would be greatly appreciated
Thanks.
View 1 Replies
View Related
May 8, 2008
I usually use Access for my database work, but a recent request needs data from a table with about 8 million records - a mite outside of Access' league.
So, I am using SQL Server Report Services to create this report.
Essentially, I need to be able to use a table from database A and a table from databse B in a single dataset for the report I'm making.
I'm self-learning this package and have not been able to locate if this is even possible, let alone how to do it. I can't see it not being possible, so I figure the procedure is simply eluding me.
I've seen a thread on a similar - if not the same - problem, and the answers given all seemed to be based upon the user being able to write to the database server or in some other manner manipulate the server. I have no such capabilities. All I can do is look at the data and create a report based upon what I see. No write access at all.
This wasn't a problem in Access, because, though the main data was on the server, I linked to any needed tables and everything else was local to my box.
Thanx in advance for any help!
View 5 Replies
View Related
Mar 14, 2006
I am using SS 2005 RS. I am able to generate single page pdf reports by passing "id" as a parameter. Now I need to send multiple ids as parameter and generate one big report which would contain reports for all the ids supplied on separate pages. Is this possible? If yes, then how do i do this.
What I have tried so far is, I created new report with a list control and added single page report as a subreport to this list control and grouped this list control using id parameter. When i send multiple ids in the parameter, for example 3 ids, it generates 3 page report but only for the first id. So it generates report for the first id and repeats same report 3 times.
Thanks for your help,
View 9 Replies
View Related
Aug 23, 2006
Hi,
Is it possible to populate a dataset with tables returned by a stored proc?Consider this:
BEGINSELECT * FROM Table1SELECT * FROM Table2SELECT * FROM Table3END
If that is my stored proc, could I call it from a page and automatically populate a dataset with all 3 tables (if yes, then how?), or would I have to make 3 seperate calls to the db for each table?
Thanks
View 2 Replies
View Related
Dec 25, 2006
Hi,
I have a stored proc which returns multiple result sets. These results sets I am capturing using a strongly typed dataset which in turn I am using to display in the code. My dataset will have 5 tables. However when I run the code only 3 tables get populated and the remaining 2 gets no data.
I have seen the problem earlier and could not resolved it. Please let me know if any one can help.
Thanks in advance
Rohit
View 3 Replies
View Related
Sep 26, 2007
I'm looking for some advice on how to manage reports that use the same query in their datasets. I have multiple reports that use several datasets that are the same. If I need to make a change to one dataset, I need to remember to update the other datasets. Of course I don't always remember to do that!
Is there a way to create a dataset in a single location and then share it? I was thinking of using a View but I don't think it'll accept the parameters.
I've been cutting & pasting the entire query as I make change but I'm afraid it'll mess that up or forget to update a dataset.
How do you do it?
Any suggestions you have would be helpful.
Rob
View 7 Replies
View Related
Sep 1, 2007
I would like to know how to dynamically change the And/Or value on the Filter tab within a dataset.
Here are my two expressions within the filter:
Code Snippet
=Switch(Parameters!TotalCostFilterOp.Value = "=",Fields!tcost.Value = Parameters!TotalCost.Value,Parameters!TotalCostFilterOp.Value = ">",Fields!tcost.Value > Parameters!TotalCost.Value,Parameters!TotalCostFilterOp.Value = ">=",Fields!tcost.Value >= Parameters!TotalCost.Value,Parameters!TotalCostFilterOp.Value = "between",Fields!tcost.Value >= Parameters!TotalCost.Value AND Fields!tcost.Value <= Parameters!TotalCost2.Value,Parameters!TotalCostFilterOp.Value = "ie", ISNothing(Fields!tcost.Value), Parameters!TotalCostFilterOp.Value= "<",Fields!tcost.Value < Parameters!TotalCost.Value,Parameters!TotalCostFilterOp.Value = "<=",Fields!tcost.Value <= Parameters!TotalCost.Value,Parameters!TotalCostFilterOp.Value = "<>",Fields!tcost.Value <> Parameters!TotalCost.Value)
Code Snippet
=Switch(Parameters!CompanyFilterOp.Value = "%",LCASE(Fields!company.Value) like "*" & LCASE(Parameters!Company.Value) & "*", Parameters!CompanyFilterOp.Value = "=",Fields!company.Value = Parameters!Company.Value, Parameters!CompanyFilterOp.Value = "ie", ISNothing(Fields!company.Value), Parameters!CompanyFilterOp.Value = "?", LCASE(Fields!company.Value) LIKE LCASE(Parameters!Company.Value), Parameters!CompanyFilterOp.Value = "<>", Fields!company.Value <> Parameters!Company.Value, Parameters!CompanyFilterOp.Value = "?%", LCASE(Fields!company.Value) LIKE LCASE(Parameters!Company.Value) & "*")
The operator for both these is set to "=", and the value is set to "=true".
Based on user selection in a report wizard, the 4th column (And/Or) may need to be changed from 'and' to 'or', or vice-versa.
Is there anyway to accomplish this programatically?
Thanks in advance for you help!
View 2 Replies
View Related
Aug 23, 2007
Hey everyone,
I am trying to combine like data from two different data sources into a single data set. Is there anyway I can do this? It seems like I can only add one data set, but is there some sort of workaround I could use?
thanks,
Keith
View 4 Replies
View Related
Jul 24, 2015
I have a generated a report in SSRS, but unable to generate a Student Hall Tickets for an examinations.
View 4 Replies
View Related
Aug 16, 2006
Hi,
is there anyway to insert all the rows from a dataset to SQL Server table in a single stretch..
Thanks
Anz
View 1 Replies
View Related
Apr 29, 2008
Hi,
OK, trying to return the results from two SQL statements into a DataSet using SqlDataAdapter. The SELECT statements query the same table but are looking for different records based on the date that the records were inserted - the 1st query looks for records fro the current month and the 2nd one looks at the same records but for the previous month. The goal is to be able to do some math within a repeater and get the difference between the two records.
Sounds easy enough and it has worked for me in different variations of the same idea but not this time - here's the code:
Protected Sub buildPartsReport(ByVal varHC) objConn.Open() sSQL = "SELECT ap.id,item_model,item_sn,aircraft_id,item_loc=item_type+ ' on ' +(SELECT tnum FROM T_Aircraft WHERE id=aircraft_id),apt.tot_time As endTimes,apt.tot_cycles as endCycles FROM T_Aircraft_Parts ap, T_Aircraft_Parts_Totals apt WHERE ap.id=apt.part_id AND report_date= '" & rD & "' AND item_type LIKE 'engine%';SELECT ap.id,apt.part_id,apt.tot_time as startTimes,apt.tot_cycles as startCycles FROM T_Aircraft_Parts ap, T_Aircraft_Parts_Totals apt WHERE ap.id=apt.part_id AND report_date= '" & oldRD & "' AND item_type LIKE 'engine%'" Dim objCommand As New SqlDataAdapter(sSQL, objConn) DS = New DataSet() objCommand.Fill(DS) Repeater1.DataSource = DS Repeater1.DataBind() DS.Dispose() objCommand.Dispose() objConn.Close() End Sub
Sorry if it wrapped a bit. The "rD" and "oldRD" are the variables for the date ranges (currently set to static numbers for testing). I'm getting the following error when I run this on an ASP.Net page:
System.Web.HttpException: DataBinding: 'System.Data.DataRowView' does not contain a property with the name 'startTimes'.
The code works fine when run via the Query Tool on the SQL server (SQL 2005 Std) though it produces two distinct "tables" which I'm guessing is the problem. I've tried variations on the code including creating a 2nd dataset and then attempting a merge (no joy) and I've tried the ".TableName" route but it complains about trying to add the tablename twice.
Thoughts? I need to get this to work - it is part of a reporting component for an application that I'm developing and I'm stuck. Thanks as always...
View 5 Replies
View Related
Aug 11, 2004
Hi!
I have a question about SQL Reporting Services. I have a stored procedure which returns multiple resultsets (multiple select). In my reporting services project I have a dataset that connects to this procedure. But, when I execute it in the Data tab, I only get the first resultset. So can't I have more resultsets?
I can't use more datasets (that have the selects in the procedure) because the procedure is complicated and has many calculation. I've managed to get all the selects in only one and the reporting services in working in this way. How about many resultsets in a dataset?
Thank you!
Irina Stanca
View 5 Replies
View Related
Nov 28, 2006
Hi
in input we have a set of rows, each one with a column containing a string (eg: "AAOOOOAAAOOA").
We'd like to split this string (using a vb.net data script task) into tokens (eg: "AA", then "OOOO","AAA","OO","A"), and to output one line per token.
How can we achieve that with a vb.net data script task ? (Or anything else ?)
best regards
Thibaut
View 9 Replies
View Related
Jan 14, 2008
Wondering how this is handled by Multicast component. If multiple copies are created in the memory and the size of the dataset is large, this could cause some performance problems. Any thoughts? TIA
View 8 Replies
View Related
Sep 13, 2007
Hi,
I want to design a report in which it will contain fields derived from 2 different stored procedures. I understand a 'table' can display data from a single dataset. How can i bind these two stored procedures into a single dataset so as when i click on the table and use its property 'DataSetName', to be able to select the dataset which holds all columns from stored proc 1 and stored proc 2. How can i link multible tables ( multible stored procedures with different column names in each one) into a single dataset to feed the report?
Thank you
George
View 1 Replies
View Related
Mar 26, 2015
I'm trying to generate a single record that shows the status of an accounting period for multiple groups. For a single accounting period they could all be the same, or all be different (open, closed, hold, etc)
What I want to do is select the accounting period with a group by that says if all the groups are in the same status, then that's the status. But if there are different status' for the period, I just want to have a single line that says 'Mixed' The query below returns 3 records, but I only want 2. I tried a CASE WHEN Having COUNT > 1 but it blew up with an aggregate subquery in a group by error.
DECLARE @AccountingPeriod Table
(AccountingPeriod varchar(10), Status char(1), GroupNum int)
Insert into @AccountingPeriod
Values('2015-03','O',1),
('2015-03','O',2),
('2015-03','O',3),
('2015-02','O',1),
('2015-02','O',2),
('2015-02','C',3)
Select AccountingPeriod, Status from @AccountingPeriod
GROUP BY AccountingPeriod, Status
View 3 Replies
View Related
Apr 10, 2007
Hi there,
I have a MS Access database (mdb) containing the following tables:
Crime
Criminal
CrimeCommitted
Hideout
CriminalType
The Criminal table contains information about each criminal and the CrimeCommitted table contains information about the specific crimes. I've written the following query to return only the latest crime committed by each criminal:
Code Snippet
SELECT Criminal.CriminalID, Criminal.Firstname, Criminal.Lastname, Criminal.Nickname, Criminal.Gender, Criminal.DOB, Criminal.Eyes, Criminal.Complexion, Criminal.Weight, Criminal.Height, Criminal.Build, Criminal.Scars, Criminal.Occupation, Criminal.CrimeOrgID, Criminal.IQ, Criminal.Hideout, Criminal.CriminalType, Max(CrimeComitted.Date) AS Last_Crime_Comitted
FROM Criminal INNER JOIN CrimeComitted ON Criminal.CriminalID=CrimeComitted.CriminalID
GROUP BY Criminal.CriminalID, Criminal.Firstname, Criminal.Lastname, Criminal.Nickname, Criminal.Gender, Criminal.DOB, Criminal.Eyes, Criminal.Complexion, Criminal.Weight, Criminal.Height, Criminal.Build, Criminal.Scars, Criminal.Occupation, Criminal.CrimeOrgID, Criminal.IQ, Criminal.Hideout, Criminal.CriminalType;
This query works fine for obtaining the Criminal table data, but once i've include CrimeCommitted.Country in the SELECT statement, the data returned contained all the crimes committed by each criminal (i just need the latest crime).
The query doesn't work when another table, other than Criminal, is selected. How can i obtain the columns in the CrimeCommitted table in this query?
View 2 Replies
View Related
Jul 15, 2015
We have the following requirement :
1. In daily basis auto loop through each item in the invoice table.
2. passing invoice number into a Summary SSRS report as parameter.
3. Auto download ALL generated PDF reports into a window folder with a special file name format i.e. <INVOICE_NO>_<DATE>.pdf
how to achieve this via SSRS, Store Procedure or Power Shell?
View 4 Replies
View Related
Oct 25, 2015
I have used my parameter value in where clause of my dataset control SQL query. When I choose a single value in my multiple-value parameter then I get output as expected. Hovewer when I choose multuple values I get an arror:
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.
----------------------------
Query execution failed for dataset 'DATABASE'. (rsErrorExecutingCommand)
----------------------------
An error has occurred during report processing. (rsProcessingAborted)
I am convinced that it's a syntax problem. Is there a way I can change syntax of data output from query:
select @parameter
I need output to be like: value, value, value ... etc.
The dataset control query looks something like the following:
select
ID,
a,
b,
c,
d
from DATASET.dbo.Table
where ID in (select @parameter)
I have been searching through google for weeks to solve this and got this far and got stuck. Is it possible at all to achieve what I am looking for?
View 4 Replies
View Related
May 5, 2008
Hi All,
I created one report having more than two datasets. How can I display the "No result found Message" If some of the dataset having no data.
Thanks,
Aneesh
View 4 Replies
View Related
Sep 12, 2007
Hi All,
We are post-deployment with a serious reporting issue that's causing us to rethink our reporting solution. We're considering moving from what we have to SQL Server Reporting Services (client side).
I have spent the past couple of days getting up to speed on this feature and seeing if we can easily migrate it into our existing application. However, I've hit a couple of stumbling blocks and was hoping perhaps someone here could either tell me the solution or point me in the right direction.
I'm not having any problems creating basic reports (e.g. flat data).
However, I am having problems creating reports where there are related tables in a dataset.
The way it works with our existing solution is that I get a dataset (which contains several data tables) and point it to the report's datasource. That report expects those tables and I have defined table-relationships in the report which process and display the information correctly.
I'm not having as much luck with RDLC.
I can go into futher detail about how I'm creating the report, but let me just ask these general questions first:
1. Can I set a dataset containing multiple datatables equal to a property on an RDLC report and that RDLC report know how to treat and display the data?
2. Is there a better/smarter than this to get a field selection from my datasource (remember, this information is coming from a stored procedure so connecting directly to the database is not an option):
a. In code, populate dataset
b. In code, write dataset schema to xml (e.g. an xsd file)
c. In Visual Studio, add the XSD file to project
d. Use fields from XSD file to drag and drop fields on report
A fancy example would be nice too. I've googled like crazy the past couple of days and downloaded as many samples as I can find (including the ones on ftponline.com, gotreportviewer.com, "Tudor's WebLog", and several others). However, I have yet to find one that uses grouping and related datatables.
Thank you so much (if, for nothing else, reading this post )
View 4 Replies
View Related
