Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

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
?
760 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Does Your Cloud Backup Use Blockchain Technology?

Blockchain technology has already revolutionized finance thanks to Bitcoin. Now it's disrupting other areas, including the realm of data protection. Learn how blockchain is now being used to authenticate backup files and keep them safe from hackers.

 

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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

721 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