Solved

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

Posted on 2013-05-15
8
671 Views
Last Modified: 2013-05-31
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
0
Comment
Question by:Sherry
  • 4
  • 3
8 Comments
 
LVL 35

Expert Comment

by:David Todd
ID: 39170391
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
 
LVL 37

Expert Comment

by:ValentinoV
ID: 39170416
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
 

Author Comment

by:Sherry
ID: 39181447
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
 

Author Comment

by:Sherry
ID: 39181791
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 37

Expert Comment

by:ValentinoV
ID: 39186506
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
 

Author Comment

by:Sherry
ID: 39187975
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
 
LVL 37

Accepted Solution

by:
ValentinoV earned 500 total points
ID: 39190061
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
 

Author Comment

by:Sherry
ID: 39191791
Yes I saw that too. and made changes.  But they did work.  Will give the DATEPART a try.  Thank you
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now