DataSet Problems

Hi,
We have noticed a problem with following:
1. creating a new report
2. adding a DataSet which pulls from a shared data source and displays
information correctly
3. choosing 'Save' and not pulling the fields into the layout form
When we open the report again the dataset is there but all references to the
table we selected are gone.
Is this supposed to happen? We have tried this on 2 separate installations,
one with SP2 and one without.
Any suggestions?Sometimes RS does not recognize that anything has changed in the data tab
and does not save. Just go to the layout tab and click on save all and you
will be good to go.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"KDogg" <KDogg@.discussions.microsoft.com> wrote in message
news:B3D6B39F-12D0-4F95-A998-1CF4A83B231F@.microsoft.com...
> Hi,
> We have noticed a problem with following:
> 1. creating a new report
> 2. adding a DataSet which pulls from a shared data source and displays
> information correctly
> 3. choosing 'Save' and not pulling the fields into the layout form
> When we open the report again the dataset is there but all references to
> the
> table we selected are gone.
> Is this supposed to happen? We have tried this on 2 separate
> installations,
> one with SP2 and one without.
> Any suggestions?|||Bruce - Thank you, that worked! Would this be fixed in a future release?
Although we were working on a small dataset I would hate to have lost
development time on a larger query?!
"Bruce L-C [MVP]" wrote:
> Sometimes RS does not recognize that anything has changed in the data tab
> and does not save. Just go to the layout tab and click on save all and you
> will be good to go.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "KDogg" <KDogg@.discussions.microsoft.com> wrote in message
> news:B3D6B39F-12D0-4F95-A998-1CF4A83B231F@.microsoft.com...
> > Hi,
> > We have noticed a problem with following:
> > 1. creating a new report
> > 2. adding a DataSet which pulls from a shared data source and displays
> > information correctly
> > 3. choosing 'Save' and not pulling the fields into the layout form
> > When we open the report again the dataset is there but all references to
> > the
> > table we selected are gone.
> > Is this supposed to happen? We have tried this on 2 separate
> > installations,
> > one with SP2 and one without.
> >
> > Any suggestions?
>
>|||I have no idea. I have lost work so I do this compulsively now. I'll
probably keep doing it when the bug no longer exists.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"KDogg" <KDogg@.discussions.microsoft.com> wrote in message
news:DC256683-1D12-4C55-B578-8A4440F1B41E@.microsoft.com...
> Bruce - Thank you, that worked! Would this be fixed in a future release?
> Although we were working on a small dataset I would hate to have lost
> development time on a larger query?!
> "Bruce L-C [MVP]" wrote:
>> Sometimes RS does not recognize that anything has changed in the data tab
>> and does not save. Just go to the layout tab and click on save all and
>> you
>> will be good to go.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "KDogg" <KDogg@.discussions.microsoft.com> wrote in message
>> news:B3D6B39F-12D0-4F95-A998-1CF4A83B231F@.microsoft.com...
>> > Hi,
>> > We have noticed a problem with following:
>> > 1. creating a new report
>> > 2. adding a DataSet which pulls from a shared data source and displays
>> > information correctly
>> > 3. choosing 'Save' and not pulling the fields into the layout form
>> > When we open the report again the dataset is there but all references
>> > to
>> > the
>> > table we selected are gone.
>> > Is this supposed to happen? We have tried this on 2 separate
>> > installations,
>> > one with SP2 and one without.
>> >
>> > Any suggestions?
>>

Datagrid and sql query isssue. Revenue reporting system

Hi-

I am trying to develop a page that pulls all customers from a database and display the revenue for each customer for each day I query in a months time.

I am able to pull out the revenue for one day in the month and display it in the column with the appropriate revenue next to the customer name.

My problem is two things.

1. It only displays customers that actually have revenue. So how would I get my datagrid to display all customers regardless if they have revenue in the database.

2. I need to be able to display more than 1 day in columns format. For example

Customer | Day1 | Day 2 |
----------
Acme Inc | $2200. |$1300.

Here is my sql code that pulls out the customers that have revenue and displays one day between a specific set of dates.

SQL = "SELECT pb_customers.customer_name AS 'customer', sum(pb_report_shippers.total_ext_price + pb_report_shippers.setup_cost) as 'total' FROM pb_report_shippers INNER JOIN pb_jobs ON pb_report_shippers.job_id = pb_jobs.job_id INNER JOIN pb_customers ON pb_jobs.customer_id = pb_customers.customer_id WHERE pb_report_shippers.shipper_date_time between cast('9/01/03' as datetime) and cast('9/02/03' as datetime) AND job_completed = '1' GROUP by pb_customers.customer_name"

Any help would be appreciated

ThanksTo get all customers, you'll want a left outer join for your related tables. Then try something like this

SELECT pb_customers.customer_name AS 'customer',
 sum(case (when day(pb_report_shippers.shipper_date_time) = 1
 then pb_report_shippers.total_ext_price + pb_report_shippers.setup_cost
 else 0
 end) as day1,
 sum(case (when day(pb_report_shippers.shipper_date_time) = 2
 then pb_report_shippers.total_ext_price + pb_report_shippers.setup_cost
 else 0
 end) as day2,
... and so on
FROM pb_report_shippers
 Left outer JOIN pb_jobs ON pb_report_shippers.job_id = pb_jobs.job_id
 left outer JOIN pb_customers ON pb_jobs.customer_id = pb_customers.customer_id WHERE pb_report_shippers.shipper_date_time between cast('9/01/03' as datetime) and cast('9/02/03' as datetime) AND job_completed = '1' GROUP by pb_customers.customer_name

HTH|||Thanks for the reply.

I am confused about this line.

sum(case (when day(pb_report_shippers.shipper_date_time) = 1

then pb_report_shippers.total_ext_price + pb_report_shippers.setup_cost

else 0

end) as day1,

What does the "1" represent. Am I supposed to put the datevalue I am looking for right there? When I do i get an error with the "when" keyword.|||The DAY() function returns the DD portion of MM/DD/YYYY. So, for 10/23/2003 it would contain 23. I don't think this is exactly what you are looking for, but the methodology should work. Instead I would use the DATEDIFF() function.

SELECT
 pb_customers.customer_name AS 'customer',
 SUM(
 CASE
 WHEN DATEDIFF(d,pb_report_shippers.shipper_date_time,CAST('9/01/03' AS datetime)) = 1
 THEN pb_report_shippers.total_ext_price + pb_report_shippers.setup_cost
 ELSE 0
 END
 ) AS day1,
 SUM(
 CASE
 WHEN DATEDIFF(d,pb_report_shippers.shipper_date_time,CAST('9/01/03' AS datetime)) = 2
 THEN pb_report_shippers.total_ext_price + pb_report_shippers.setup_cost
 ELSE 0
 END
 ) AS day2,
 SUM(
 CASE
 WHEN DATEDIFF(d,pb_report_shippers.shipper_date_time,cast('9/01/03' AS datetime)) = 3
 THEN pb_report_shippers.total_ext_price + pb_report_shippers.setup_cost
 ELSE 0
 END
 ) AS day3,
... and so on
FROM
 pb_report_shippers
LEFT OUTER JOIN
 pb_jobs ON pb_report_shippers.job_id = pb_jobs.job_id
LEFT OUTER JOIN
 pb_customers ON pb_jobs.customer_id = pb_customers.customer_id
WHERE
 pb_report_shippers.shipper_date_time between cast('9/01/03' as datetime) and cast('9/02/03' as datetime) AND
 job_completed = '1'
GROUP BY
 pb_customers.customer_name

Terri|||thanks for the help. I am getting closer to what I need.

One thing that is still occuring however is it only displays the customers that have revenue. It wont display all customers regardless is no revenue is found in the database. Is this what the left outer join is suppoesed to handle. What does it doe exactly compated to a normal Inner join.|||The left outer join should accomplish this. experiment a bit and comment out the

LEFT OUTER JOIN
 pb_customers ON pb_jobs.customer_id = pb_customers.customer_id

and any related columns of the query to see if you get the same results. This should help you track down why not all customers are showing up.|||Then I would restruture slightly. I would make the primary table you are pulling from your customers table, and I would LEFT OUTER join your shippers table. Like this (untested but hopefully it's close):

SELECT
 pb_customers.customer_name AS 'customer',
 SUM(
 CASE
 WHEN DATEDIFF(d,pb_report_shippers.shipper_date_time,CAST('9/01/03' AS datetime)) = 1
 THEN pb_report_shippers.total_ext_price + pb_report_shippers.setup_cost
 ELSE 0
 END
 ) AS day1,
 SUM(
 CASE
 WHEN DATEDIFF(d,pb_report_shippers.shipper_date_time,CAST('9/01/03' AS datetime)) = 2
 THEN pb_report_shippers.total_ext_price + pb_report_shippers.setup_cost
 ELSE 0
 END
 ) AS day2,
 SUM(
 CASE
 WHEN DATEDIFF(d,pb_report_shippers.shipper_date_time,cast('9/01/03' AS datetime)) = 3
 THEN pb_report_shippers.total_ext_price + pb_report_shippers.setup_cost
 ELSE 0
 END
 ) AS day3,
... and so on
FROM
 pb_customers
LEFT OUTER JOIN
 pb_jobs ON pb_jobs.customer_id = pb_customers.customer_id AND pb_report_shippers.shipper_date_time BETWEEN CAST('9/01/03' as datetime) AND CAST('9/02/03' as datetime) AND
 job_completed = '1'
LEFT OUTER JOIN
 pb_report_shippers ON pb_report_shippers.job_id = pb_jobs.job_id
GROUP BY
 pb_customers.customer_name