Hi,
I am trying to implement partitioning on a table
depending upon the fiscal_month value...
The current values are from 1-6...
Create partition function LoadDataPartitionFunction ( smallint)
as
Range for values (1,2,3,4,5,6)
-- drop partition scheme LoadDataPartitionScheme
create partition scheme LoadDataPartitionScheme
as
Partition LoadDataPartitionFunction ALL to ([PRIMARY])
CREATE TABLE Load_Data_Partition (
[RowID] [int] NOT NULL,
[Fiscal_Month] [smallint] NOT NULL,
[Fiscal_Year] [smallint] NOT NULL,
...
[Service] [nvarchar](100) COLLATE
) ON LoadDataPartitionScheme (Fiscal_Month)
truncate table Load_Data_old -- same schema as load_data_partition
Alter table load_data_partition switch partition 1 to Load_Data_old
-- which month's data to be moved out
alter partition function LoadDataPartitionFunction () merge range (1)
Alter partition scheme LoadDataPartitionScheme next used [primary]
-- which months data to be moved in
alter partition function LoadDataPartitionFunction () split range(7)
Select * from sys.partition_range_values
function_id boundary_id parameter_id value
---- ---- ---- --
65545 1 1 2
65545 2 1 3
65545 3 1 4
65545 4 1 5
65545 5 1 6
65545 6 1 7
Alter table [Load_Data_new] switch to [Load_Data_partition] partition 6
ALTER TABLE SWITCH statement failed. Check constraints of source table Load_Data_new' allow values that are not allowed by range defined by partition 6 on target table 'Load_Data_partition'.
Values in Load_Data_new for fiscal_month is 7
But when i try
Insert into [Load_Data_partition]
Select * from [Load_Data_new]
where fiscal_month = 7
it works fine...
reference used : http://www.sqlskills.com/resources/Whitepapers/Partitioning%20in%20SQL%20Server%202005%20Beta%20I I.htmI got the answer..
Alter table Load_Data_new add constraint load_data_new_month check ( fiscal_month =7)
even though the Load_Data_new table has only month = 7 data...
a constraint is mandatory....
Showing posts with label current. Show all posts
Showing posts with label current. Show all posts
Friday, March 23, 2012
Monday, March 12, 2012
Parameters with a union statement
I have a report that is using a union statement to pull in data from two identical tables except that one is for current month, the other for archived data.
What I want to do is prompt the user once for a date and use the value to select from the right table. Since a sales date can only exist in one of the tables, one union will work, the other not.
But the report in prompting me for a parameter for each query....which is in Informix and the prompt is this: "?"
Is there anyway to force both halves of the query to see this as one parameter so the user is only prompted once?
Thanks
Have you tried to use this sql structure
="select * from table_1 where Sale_date = '" & format(Parameters!date,"MM-dd-yyyy") & "'"
union
select * from table_2 where Sale_date = '" & format(Parameters!date,"MM-dd-yyyy") & "'"
Can I do this in the data set?
Thanks
Wednesday, March 7, 2012
Parameters for sp that depend on the current row fields
How can I specify parameters to a store procedure that depend on a row
without using ado.net in code behind?
The problem is that I use 5 parameters for my store procedure. 2 are report
parameters, and I want other 3 to be specified by the fields of the current
row, in this way the returned value by the store procedure depends on certain
values of each row.
I currently use code behind with Ado.Net to specify the parameters but
opening and closing the connection takes some time (depending on the # of
rows), if I somewhat could call the sp from the given field in the row in
reporting services it would be way faster. Does someone knows of a way to do
this?
Thanks.You can embed a subreport in a field of the current row and pass those
fields and parameters to the subreport (I do this). Give it a try, should be
a lot easier and cleaner.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"gchinl" <gchinl@.discussions.microsoft.com> wrote in message
news:64EF7852-D7B0-4479-8B64-971E93E88B9A@.microsoft.com...
> How can I specify parameters to a store procedure that depend on a row
> without using ado.net in code behind?
> The problem is that I use 5 parameters for my store procedure. 2 are
> report
> parameters, and I want other 3 to be specified by the fields of the
> current
> row, in this way the returned value by the store procedure depends on
> certain
> values of each row.
> I currently use code behind with Ado.Net to specify the parameters but
> opening and closing the connection takes some time (depending on the # of
> rows), if I somewhat could call the sp from the given field in the row in
> reporting services it would be way faster. Does someone knows of a way to
> do
> this?
> Thanks.
without using ado.net in code behind?
The problem is that I use 5 parameters for my store procedure. 2 are report
parameters, and I want other 3 to be specified by the fields of the current
row, in this way the returned value by the store procedure depends on certain
values of each row.
I currently use code behind with Ado.Net to specify the parameters but
opening and closing the connection takes some time (depending on the # of
rows), if I somewhat could call the sp from the given field in the row in
reporting services it would be way faster. Does someone knows of a way to do
this?
Thanks.You can embed a subreport in a field of the current row and pass those
fields and parameters to the subreport (I do this). Give it a try, should be
a lot easier and cleaner.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"gchinl" <gchinl@.discussions.microsoft.com> wrote in message
news:64EF7852-D7B0-4479-8B64-971E93E88B9A@.microsoft.com...
> How can I specify parameters to a store procedure that depend on a row
> without using ado.net in code behind?
> The problem is that I use 5 parameters for my store procedure. 2 are
> report
> parameters, and I want other 3 to be specified by the fields of the
> current
> row, in this way the returned value by the store procedure depends on
> certain
> values of each row.
> I currently use code behind with Ado.Net to specify the parameters but
> opening and closing the connection takes some time (depending on the # of
> rows), if I somewhat could call the sp from the given field in the row in
> reporting services it would be way faster. Does someone knows of a way to
> do
> this?
> Thanks.
Saturday, February 25, 2012
parameters
I am trying to make a report that will select the sales people who have
made 10 sales or more during the current month and show what those
sales are with the price..., and then show what those same sales
persons totals were for the previous month. my problem is to get the
totals from the previous month. example of the report is below. I am
trying to use two datasets one to get the first name, last name,
sales_id, product sold, price, and quantity. the other dataset will
get the last month total. I want to pass the sales person id to the
other dataset for each sales person. is there a way to do this?
Thanks, Landon
sales people who sold 10 or more:
first name, last name, sale_id, product sold, price, quantity
last month total: total
< -- this is where my problem is
first name, last name, sale_id, product sold, price, quantity
last month total: total
first name, last name, sale_id, product sold, price, quantity
last month total: total
first name, last name, sale_id, product sold, price, quantity
last month total: totalWhat works best in this is to have a sub report. Create a normal report that
you pass the sales person id as a parameter. Make sure the report works.
Drag and drop the report onto the first report. Right mouse click on report,
parameters. Set the parameter for the sub report to the field which has the
sales person id.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Landon" <landonb@.gmail.com> wrote in message
news:1135788313.801129.98180@.g49g2000cwa.googlegroups.com...
>I am trying to make a report that will select the sales people who have
> made 10 sales or more during the current month and show what those
> sales are with the price..., and then show what those same sales
> persons totals were for the previous month. my problem is to get the
> totals from the previous month. example of the report is below. I am
> trying to use two datasets one to get the first name, last name,
> sales_id, product sold, price, and quantity. the other dataset will
> get the last month total. I want to pass the sales person id to the
> other dataset for each sales person. is there a way to do this?
> Thanks, Landon
> sales people who sold 10 or more:
> first name, last name, sale_id, product sold, price, quantity
> last month total: total
> < -- this is where my problem is
> first name, last name, sale_id, product sold, price, quantity
> last month total: total
> first name, last name, sale_id, product sold, price, quantity
> last month total: total
> first name, last name, sale_id, product sold, price, quantity
> last month total: total
>|||would i be able to use that data from the sub report in a graph with
the data from the other dataset?|||You can't join two datasets regardless of whether or not it is in a
subreport or not. You need to join the data into a single dataset if you
want to do this. Your subreport can have a graph, that isn't the problem.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Landon" <landonb@.gmail.com> wrote in message
news:1135793391.283032.284030@.z14g2000cwz.googlegroups.com...
> would i be able to use that data from the sub report in a graph with
> the data from the other dataset?
>|||so in one graph i can not have the current month and last month totals?|||Not that I am aware of. A graph is based on a dataset, not on two datasets.
You can't join datasets. So if you want the data from two datasets on the
graph then you need to join the data at the source.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Landon" <landonb@.gmail.com> wrote in message
news:1135794518.867450.152820@.g49g2000cwa.googlegroups.com...
> so in one graph i can not have the current month and last month totals?
>|||Thanks Bruce for your help!
Landon|||You should fetch your data as a subquery in your data stream. You would then
have access to both current and prior month values. Example:
select first_name, last_name, sale_id, product_sold, price,
quantity,last_month_total
,(SELECT SUM(amount)
FROM sales
WHERE first_name = s.first_name and last_name = s.last_name
and sales_date in <month>
) AS prior_month_total
FROM sales s
where ...|||If they are on different databases you can link them in the query like
so:
Lets say 1 data set db is DB1 and the second is DB2
Place the query in DB1(and the dataset) and then you can say:
SELECT first_name, last_name, sale_id, product_sold, price,
quantity,DB2.tablename.last_month_total|||I would make a stored procedure and use a temp table based on the
months needed by the user. That way you can have both months in the
same dataset.
made 10 sales or more during the current month and show what those
sales are with the price..., and then show what those same sales
persons totals were for the previous month. my problem is to get the
totals from the previous month. example of the report is below. I am
trying to use two datasets one to get the first name, last name,
sales_id, product sold, price, and quantity. the other dataset will
get the last month total. I want to pass the sales person id to the
other dataset for each sales person. is there a way to do this?
Thanks, Landon
sales people who sold 10 or more:
first name, last name, sale_id, product sold, price, quantity
last month total: total
< -- this is where my problem is
first name, last name, sale_id, product sold, price, quantity
last month total: total
first name, last name, sale_id, product sold, price, quantity
last month total: total
first name, last name, sale_id, product sold, price, quantity
last month total: totalWhat works best in this is to have a sub report. Create a normal report that
you pass the sales person id as a parameter. Make sure the report works.
Drag and drop the report onto the first report. Right mouse click on report,
parameters. Set the parameter for the sub report to the field which has the
sales person id.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Landon" <landonb@.gmail.com> wrote in message
news:1135788313.801129.98180@.g49g2000cwa.googlegroups.com...
>I am trying to make a report that will select the sales people who have
> made 10 sales or more during the current month and show what those
> sales are with the price..., and then show what those same sales
> persons totals were for the previous month. my problem is to get the
> totals from the previous month. example of the report is below. I am
> trying to use two datasets one to get the first name, last name,
> sales_id, product sold, price, and quantity. the other dataset will
> get the last month total. I want to pass the sales person id to the
> other dataset for each sales person. is there a way to do this?
> Thanks, Landon
> sales people who sold 10 or more:
> first name, last name, sale_id, product sold, price, quantity
> last month total: total
> < -- this is where my problem is
> first name, last name, sale_id, product sold, price, quantity
> last month total: total
> first name, last name, sale_id, product sold, price, quantity
> last month total: total
> first name, last name, sale_id, product sold, price, quantity
> last month total: total
>|||would i be able to use that data from the sub report in a graph with
the data from the other dataset?|||You can't join two datasets regardless of whether or not it is in a
subreport or not. You need to join the data into a single dataset if you
want to do this. Your subreport can have a graph, that isn't the problem.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Landon" <landonb@.gmail.com> wrote in message
news:1135793391.283032.284030@.z14g2000cwz.googlegroups.com...
> would i be able to use that data from the sub report in a graph with
> the data from the other dataset?
>|||so in one graph i can not have the current month and last month totals?|||Not that I am aware of. A graph is based on a dataset, not on two datasets.
You can't join datasets. So if you want the data from two datasets on the
graph then you need to join the data at the source.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Landon" <landonb@.gmail.com> wrote in message
news:1135794518.867450.152820@.g49g2000cwa.googlegroups.com...
> so in one graph i can not have the current month and last month totals?
>|||Thanks Bruce for your help!
Landon|||You should fetch your data as a subquery in your data stream. You would then
have access to both current and prior month values. Example:
select first_name, last_name, sale_id, product_sold, price,
quantity,last_month_total
,(SELECT SUM(amount)
FROM sales
WHERE first_name = s.first_name and last_name = s.last_name
and sales_date in <month>
) AS prior_month_total
FROM sales s
where ...|||If they are on different databases you can link them in the query like
so:
Lets say 1 data set db is DB1 and the second is DB2
Place the query in DB1(and the dataset) and then you can say:
SELECT first_name, last_name, sale_id, product_sold, price,
quantity,DB2.tablename.last_month_total|||I would make a stored procedure and use a temp table based on the
months needed by the user. That way you can have both months in the
same dataset.
Subscribe to:
Posts (Atom)