I need to pull data from one table in a report to another in SSRS 2008

I have a report with three tables.  The main table has  a column called Unit and a row that has a time in the first column and the other nine are calculated and filled in.  One table 2 I need to have the units copy over to a row from table 1(distinct unit selection).  Then I need the row of times copy to table 3 going down in a column.

I was thinking there might be a way to create an array for these after they show up in table1.  Any help is greatly appreciated.  Thanks
Sherry
SherryDeveloperAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

David ToddSenior DBACommented:
Hi Sherry,

I've sort of got the idea from what you posted.

I'm not the greatest with SSRS but can do what you want I think in the underlying query.

Can you post sample tables, and I'll try to post a sample query in reply.

Regards
  David
0
ValentinoVBI ConsultantCommented:
Table 2 can be easily achieved by using the same dataset as the one you use for table 1.  Here are the steps to get a distinct list of the Units:

o drag a Table onto the design
o drag the Unit field into the first column
o open up the properties of the (Details) Row Group
o add a group on Unit
o (optional) add Sorting on Unit

Table 3 is going to be more complicated.  If I understand you correctly, you want to unpivot the data?  I'll await the samples as asked by David before trying something here.  Could you also post some details on how your times are getting calculated?

Regards, Valentino.
0
SherryDeveloperAuthor Commented:
I did figure out the first one just before reading your post, same solution.  So thank you.  And yes, once the times are calculated, I want to put them across in the main table and down the first column in table 3.  Right now the calculations are being done in the rdl, but I'm pretty sure there's a better way to do this.  I would also like the calculations to stop when it reaches ten "times" or "time slots"  I'm attaching two rdl's.  Also, we are using military format time. One has the calculations and one is without.  The one without is the rdl I want to actually use and has the table set up the way I want.  I'd like to hide table 3 when there's no data in table 2.   So I need to figure out how to do that also.  Thank you so much for helping.
Callout.rdl
Daily-Informal-Count.rdl
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

SherryDeveloperAuthor Commented:
I think I have a clearer way to put this for the time slots.  I have a parameter that they put in the starttime "07:00".  I need to have that in the first box and then have it add 1 hr for 9 more.  Once I can do that, I can fill in the table 2 (main table), going across (column group) and a group on table 3 (row group) to put those same values in.  Does this make sense?
0
ValentinoVBI ConsultantCommented:
Do you need the report to always display those ten time slots?  In that case I would just add ten calculated fields to the "CalloutDataSource" dataset (right-click dataset > Add Calculated Field).  You can then use those calculated fields just like any other field out of the dataset.
0
SherryDeveloperAuthor Commented:
No, but we don't want it to show more than ten.  I figured out a way to do this, I think.  I added to the sql to create a table and added all the values for 0000 - 2400 hrs.  With one field.  This will work.  I'm doing a union with the final results from the other "created" table, from an If statement.  I'm getting the following error, so working on trying to resolve that now.  The complete query is attached.

The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.
sqlquery2.sql
0
ValentinoVBI ConsultantCommented:
You've got some data type clashes in your joins, here are a couple of examples:

INNER JOIN #MilHours M ON M.MHours = RC.StartDateTime

INNER JOIN #Reason R ON R.StartDateTime = MHours

You're comparing a char(4) with a datetime, which won't work as expected.  Your best option is to create a similar string out of the datetime using the DATEPART function, just like you did in the WHERE clause.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
SherryDeveloperAuthor Commented:
Yes I saw that too. and made changes.  But they did work.  Will give the DATEPART a try.  Thank you
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.