Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2013-05-15
8
Medium Priority
?
778 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
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 

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
 
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 1500 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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have a large data set and a SSIS package. How can I load this file in multi threading?
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

963 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