January 18, 2006 at 7:46 am
I'm trying to set up a date parameter for a report that displays order information for a marketing mailer in reporting services. I am LOST. Every time I try to set up the parameters @startdate and @enddate all I get are mad errors. Please help!? I'm really new to Reporting Services.
January 18, 2006 at 8:01 am
What errors are you getting?
January 18, 2006 at 1:50 pm
the errors tell me that I have to define the "variable", only problem is, I can define the variable and it still asks me to define the variable. I was finally able to add two report parameters, @startdate and @enddate; however, I also want to add a table name parameter for the report. This would; hopefully, allow me to set up one report and be able to use it to report statistics on multiple marketing mailers.
Set up. I have a table named mailer_item with fields: ID, mailer_table, startdate, enddate. The mailer_table data matches up with the name for a table with customer numbers of people mailed in the promotion. I use a query to match up the purchase history of the customer during the promotional period. I was finally able to make startdate and enddate user selected parameters, but I want them to be data driven and match up with the correct mailer table.
Any and all attempts at helping are greatly appreciated!
January 19, 2006 at 6:51 am
Hi Jim!
What's you query look like?
We've been using SRS for about six months now, and have around 100 adhoc reports.
What I do is something like this:
1. Set up the SQL on the data tab:
Select * from table where field = @Param + '%'
2. Go to the report parameter and set it's default to 01.06
(This allows me to do a query where the first run of the report will bring back anything starting with 01.06)
January 19, 2006 at 7:27 am
Query
Select customer.custnum,
customer_order.order_id,
customer_order.order_date,
customer_order_item.item,
customer_order_item.quanto,
(customer_order_item.quanto * customer_order_item.it_unlist) as order_total
From customer
Join customer_order
On customer.custnum = customer_order.custnum
Join customer_order_item
On customer_order.order_id = customer_order_item.order_id
Where
customer.custnum in (SELECT customer_number FROM mailing_nar05_list_a)
AND customer_order.order_date >= @startdate
AND customer_order.order_date '0'
AND customer_order.order_status 'CN'
AND customer_order_item.item NOT LIKE '%SET%UP%'
AND customer_order_itme.item NOT LIKE '%PREVIOUS%ART%'
Order By customer.custnum, customer_order.order_id
I can get the @startdate and @enddate parameters to work, but I'd like to make the table for the in (SELECT customer_number FROM mailing_nar05_list_a) to be a parameter as well. I'd like for it to look like: in (SELECT customer_number FROM @mailertable). This way, I only have to write one report to track all of the promotional mailers we send out during a year. The mailer table parameter values should be obtained from the following query. SELECT mailer_table FROM mailer_item
Any and all attempts at helping are greatly appreciated. Thanks for your interest Chip!
January 20, 2006 at 3:20 pm
I think you have at least two ways to go.
a) Create a view that UNIONs the contents of your various mailing lists, appending the table name as a column. Downside is adding new lists to the view.
IF EXISTS (SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.VIEWS
WHERE TABLE_NAME = N'vwMailingLists')
DROP VIEW vwMailingLists
GO
CREATE VIEW vwMailingLists
AS
SELECT 'mailing_nar05_list_a',CustomerNumber
FROM mailing_nar05_list_a
UNION ALL
SELECT 'mailing_nar05_list_b',CustomerNumber
FROM mailing_nar05_list_b
UNION ALL
SELECT 'mailing_nar05_list_c',CustomerNumber
FROM mailing_nar05_list_c
-- etc
GO
b) Writing your query using dynamic SQL. Tedious, but do-able.
Regardless of how you write the query, you can create a parameter for the user to select the Mailing List from by querying sysobjects for Name like 'mailing_%'
Hope this helps
Mike
OOPS, didn't see The mailer table parameter values should be obtained from the following query. SELECT mailer_table FROM mailer_item until I re-read.
January 24, 2006 at 7:17 am
Thanks for the information Mike, I really appreciate it. I was finally able to make the report do what I wanted. I had to set up two data sets. One with the order information and one with the table information.
So I have Marketing Promotions Data on which I run the following query:
SELECT ID, description from mailer_item.
I also have the Order data on which I run the following query:
Select mailing_item.customer_number,
customer_order.order_id,
customer_order.order_date,
customer_order_item.item,
customer_order_item.quanto,
(customer_order_item.quanto * customer_order_item.it_unlist) as order_total
From mailing_item
Join customer_order
On mailing-item.customer_number = customer_order.custnum
Join customer_order_item
On customer_order.order_id = customer_order_item.order_id
Where
mailing_item.ID = @promotion
AND mailing_item.in_control_group = '0'
AND customer_order.order_date >= @startdate
AND customer_order.order_date '0'
AND customer_order.order_status 'CN'
AND customer_order_item.item NOT LIKE '%SET%UP%'
AND customer_order_item.item NOT LIKE '%PREVIOUS%ART%'
Make sure that these are two datasets and two queries for the same report. I have the parameter values for @promotion set to int with a value from the mailing_item.id field and a label of mailing_item.description.
I now have a report that will display data for any promotion we do. All the end user has to do is select which promotion they would like to report on and the start date and end date for the protion click the button and there it is. Has anyone done any reports like this in Reporting Services 2005?
Thanks for the help Chip and Mike, you guys put me in the right direction!
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply