Report with Multiple DB Tables

Dear All,
I want to get the data from two tables (no relationship exist) and want to display the data in a single report.
From one table i just want to show one record but from the other table i want to show multiple records.
Is it possible ? If yes please suggest me how ?Do you mean thet in first table you have only one record, and in the second multiple records?

report with groupby

hi,
i have a query working with crystal reports on oracle server.

--There is a table transaction_hist containing 'due_date' column(which is a number to be converted to date). In the report i want the summary of the amounts with current due_date, future due_date, past 1to30 days due_date,31to60,61to90 and 91to180 due_dates.

eg
current future 1_30 31_60 61_90 91_181
sum 99999 99999 99999 9999 9999 9999


PS: i have tried to make formula column for all these due_dates...but could not group by them and summarize them.Try uisng Cross tab reports. Read more in help file

Report with default of all

Have a report with three datasets. The first dataset pulls the
department table and uses this as one of the parameters.
select DEPT from dept
The second dataset pulls the division table and uses that as one of the
parameters.
select DIV from div
The third dataset calls a stored procedure that passes the startdate,
enddate, division and department for the report. With the startdate and
enddate also being parameters.
exec wo_sstm @.startdate, @.enddate, @.division, @.department
How can I set the parameters for @.division and @.department to utilize a
default of all for each.for your first dataset for dept:
select 0 as DeptID, '<All>' as DEPT FROM dept
UNION ALL
select DeptID, DEPT from dept
Then your value will be DeptID and label will be DEPT in Parameters
In your second dataset, same thing:
select 0 AS DivisionID, '<All>' as DIV from div
UNION ALL
select DivisionID, DIV from div
Then in your stored procedure wo_sstm you will process 0 as the indicator
for all.
=-Chris
"chad" <chad.heilig@.gmail.com> wrote in message
news:1163534032.594157.292550@.m73g2000cwd.googlegroups.com...
> Have a report with three datasets. The first dataset pulls the
> department table and uses this as one of the parameters.
> select DEPT from dept
> The second dataset pulls the division table and uses that as one of the
> parameters.
> select DIV from div
> The third dataset calls a stored procedure that passes the startdate,
> enddate, division and department for the report. With the startdate and
> enddate also being parameters.
> exec wo_sstm @.startdate, @.enddate, @.division, @.department
> How can I set the parameters for @.division and @.department to utilize a
> default of all for each.
>|||Chris,
Getting invalid column name 'DeptID'. How does this work with no DeptID
column?
Chris Conner wrote:
> for your first dataset for dept:
> select 0 as DeptID, '<All>' as DEPT FROM dept
> UNION ALL
> select DeptID, DEPT from dept
> Then your value will be DeptID and label will be DEPT in Parameters
> In your second dataset, same thing:
> select 0 AS DivisionID, '<All>' as DIV from div
> UNION ALL
> select DivisionID, DIV from div
>
> Then in your stored procedure wo_sstm you will process 0 as the indicator
> for all.
> =-Chris
> "chad" <chad.heilig@.gmail.com> wrote in message
> news:1163534032.594157.292550@.m73g2000cwd.googlegroups.com...
> > Have a report with three datasets. The first dataset pulls the
> > department table and uses this as one of the parameters.
> > select DEPT from dept
> >
> > The second dataset pulls the division table and uses that as one of the
> > parameters.
> > select DIV from div
> >
> > The third dataset calls a stored procedure that passes the startdate,
> > enddate, division and department for the report. With the startdate and
> > enddate also being parameters.
> > exec wo_sstm @.startdate, @.enddate, @.division, @.department
> >
> > How can I set the parameters for @.division and @.department to utilize a
> > default of all for each.
> >|||No - DeptID is something I made up - Do you have a column that uniquely
identifies your departments? I assumed you would understand that DeptID in
this case was an ID that uniquely identifies that department.
=-Chris
"chad" <chad.heilig@.gmail.com> wrote in message
news:1163601753.813127.152770@.h54g2000cwb.googlegroups.com...
> Chris,
> Getting invalid column name 'DeptID'. How does this work with no DeptID
> column?
>
> Chris Conner wrote:
>> for your first dataset for dept:
>> select 0 as DeptID, '<All>' as DEPT FROM dept
>> UNION ALL
>> select DeptID, DEPT from dept
>> Then your value will be DeptID and label will be DEPT in Parameters
>> In your second dataset, same thing:
>> select 0 AS DivisionID, '<All>' as DIV from div
>> UNION ALL
>> select DivisionID, DIV from div
>>
>> Then in your stored procedure wo_sstm you will process 0 as the indicator
>> for all.
>> =-Chris
>> "chad" <chad.heilig@.gmail.com> wrote in message
>> news:1163534032.594157.292550@.m73g2000cwd.googlegroups.com...
>> > Have a report with three datasets. The first dataset pulls the
>> > department table and uses this as one of the parameters.
>> > select DEPT from dept
>> >
>> > The second dataset pulls the division table and uses that as one of the
>> > parameters.
>> > select DIV from div
>> >
>> > The third dataset calls a stored procedure that passes the startdate,
>> > enddate, division and department for the report. With the startdate and
>> > enddate also being parameters.
>> > exec wo_sstm @.startdate, @.enddate, @.division, @.department
>> >
>> > How can I set the parameters for @.division and @.department to utilize a
>> > default of all for each.
>> >
>

Report Viewer Image Control

Hi all,

I can't figure out how to display a dynamic image using the Image control. I've a database table that holds image information, including an address to the image on the server. ex:\\ServerName\x$\ImageDir\Header.jpg

I've tried to set the Source to External and the Value to Fields!vcPictureLocation.Value (field for the address above) in the Properties panel, but the images appear as broken symbols. Does anyone know how to accomplish this?

As always any help is greatly appreciated. Thanks!

msnyder,

The way i have done it..

Drag the image control to your report.. when the wizard pops up.

Select database as the option ,, Specify ur fields in the database and the mime type associated with in... and

Once that is done.. select the image and set its source to external and then it should work...

while ur saving the link to the database make sure its set as http://servername/Applicationname/anyfolders/filename

hope it helps

Regards

KAren

|||

KAren, thx for the reply. I was able to get the image working locally using your method. However, when I deploy the report onto the server the images are still not displaying. I viewed the source of the report viewer and it says IMG src="http://pics.10026.com/?src=" for all of the images.

As a test, I dragged an image object onto the report, set it equal to a web source, put in the same URL as what's being pulled from the database. Worked fine locally when I previewed the report, but it would not display once deployed.

I followed your steps exactly and also changed the link thats being stored in the database to your format. Any ideas? Thanks a lot!

|||

ok.. where are ur images located?

|||

Very sorry for the delay...

I actually made a new directory called 'Pictures" located in my website project directory. I am storing the images there. The URL I'm storing in the database looks like this...

http://SERVER/safetyforms/Pictures/0d8a3ee0-ee01-48d4-baba-b408a573e1b3.jpg

The image displays in the report viewer when I click the preview tab, but won't display when I publish it. Shows IMG src="http://pics.10026.com/?src=" in the cource code.

|||

are u reports stored in a report server or are they local reports

|||

they are on a report server.

i actually found the solution. there were 2 important details, the first was the steps you outlined about how to set up the image in the report viewer. that solved problem #1. problem #2 was that anonymous access needed to be enabled. the option to enable it was in iis in the project properties.

thanks for the help!

|||

I have the same problem!

but i do not solve the problem!

can you detail the solution to me?

|||

Nanim,

FollowKarenros instructions for setting up the image in the report viewer. Make sure the pictures are being stored in a subdirectory of your main project (ex. root/Pictures) Then, go into IIS and set the permissions for the Pictures folder to anoymous. The option should be in the project properties.

The one mistake I made was setting the entire project to anoymous. We just want the pictures directory to be anoymous.

Report Viewer and Stored Procedure

I'm trying to build a report using the Report Viewer and a stored procedure. My sproc has paramerters and uses a #temp table. I have been able to get all the way to the point of adding fields to the RDLC , however, there is no table or fields in my dataset. Do I need to do something special that I'm missing to build the dataset with a sproc that uses a #temp table? My parameters are provided by fields on the aspx page where the Report Viewer is located.

I appreciate any help.

You should be able to execute the proc under the Data tab and see the results and make sure your proc is returning some data before you even start off building the report. So you konw what fields are available.sql

Report Viewer - add table

Is it possible to add tables (like in the report editor) programmatically to a report ?

I want to use 1 report for an infinite number of datatables.

Hi Friend,

I think this will give you some idea....... Thanks

publicvoid logonInfo()

{

TableLogOnInfo logOnInfo =newTableLogOnInfo();

logOnInfo = oRpt.Database.Tables["<table>"].LogOnInfo;

logOnInfo.ConnectionInfo.ServerName ="<name of Server>";

logOnInfo.ConnectionInfo.DatabaseName ="<name of DB>";

logOnInfo.ConnectionInfo.UserID ="<user>";

logOnInfo.ConnectionInfo.Password ="<pwd>";

logOnInfo.TableName ="<table>";TableLogOnInfos infos =newTableLogOnInfos();

infos.Add(logOnInfo);

CrystalReportViewer1.LogOnInfo = infos;

}

Thank you for reading my reply.

HGONE

|||

Sorry, I forgot to metion that I am using the Microsoft Reports , not Crystal Reports.

Thanks for the reply though!

Drew

report usage

I'm trying to establish usage statistics on my reports...I got a tip to look
at the executionlog table on my reportserver database. This looks like it
might do the trick, but it lists the reports in a "reportID" column that
looks like a system generated unique id. Is there a way to related the
report ID back to the name of an individual report?Join ReportID in the table ExecutionLog to the ItemID field in the
Catalog tablesql

Report to have n.. columns

Hi,

I have a table with following structure:

There can be different weeks for different activities. Depending on weeeks on weeks having data should be displayed. i.e. when activity was running.

Activitynumber Line Product Week1 Week2 Week3 .....Week52

100 Volume Product1 23 32

100 Volume Product2 11 46

102 Volume Product3 45 56 67 76

100 Revenue Product1 766 232

100 Revenue Product2 763 146

102 Revenue Product3 890 356 467 576

I want a report which should look like this

For Line=Volume Report:

-

Weeknumber Product1 Product2

Week1 23 11

Week2 32 46

For Line=Revenue Report:

-

Weeknumber Product1 Product2

Week1 766 763

Week2 232 146

Can anyone help me to suggest what should be the wayout?

A matrix will represent that for you. With a matrix, you can set row level grouping and column grouping. For both of your reports i would have the weeks as the row level grouping, and Product as the column grouping, and then either volume or revenue as the detail. The column group means that you will have a dynamic number of columns, one for each product in your dataset.

|||

Hi Sluggy,

Thanks for your answer. The problem here is that the table has got 52 columns for 52 weeks starting from week1, week2...week52.

some activities may be for 3 weeks some for 10 or some for 50. Will this display only the weeks whihc has the data?

regards

Josh

|||

In your report data, just make sure pull back either a week number or a week commencing, then you can row group on that. This means that you will have 52 rows in your matrix. Then if a product doesn't have any volume or revenue for that week you can show a zero (or empty cell) in the detail section. You could even go to the extent of hiding (set the hidden property to true) rows or columns that are only zeros or empty.

Hope that helps.

sluggy

|||

How can I hide rows which dont have any data? I mean which options do I have to select?

thanks

Josh

Report Title Help

How can I make my report title width the same as the table? The table can hide some columns based on the If/Else logic, but Report Title textbox is always static. What happens is that my table can be longer or shorter then my title.

Thanks

Yadgor2000

You can add an outermost table with two header rows to your report. Put the report title textbox in the first row, and the table you currently have into the second row. That way, the title textbox will grow or shrink based on the width of the inner table.

Report Timeout

I have designed a report that hits a large table of over a million rows. The report runs in the development environment as a preview. When deployed, the report runs about 1.5 hours and then returns an error. The report is set not to timeout. What could be causing this behavior?What error is being returned?
--
Bruce Johnson [MSFT]
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"DJP" <DJP@.discussions.microsoft.com> wrote in message
news:D8468C56-4D72-47FB-AA76-9A82D8A9CDAC@.microsoft.com...
> I have designed a report that hits a large table of over a million rows.
The report runs in the development environment as a preview. When deployed,
the report runs about 1.5 hours and then returns an error. The report is
set not to timeout. What could be causing this behavior?|||The page displayed is: The page cannot be displayed
"Bruce Johnson [MSFT]" wrote:
> What error is being returned?
> --
> Bruce Johnson [MSFT]
> Microsoft SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "DJP" <DJP@.discussions.microsoft.com> wrote in message
> news:D8468C56-4D72-47FB-AA76-9A82D8A9CDAC@.microsoft.com...
> > I have designed a report that hits a large table of over a million rows.
> The report runs in the development environment as a preview. When deployed,
> the report runs about 1.5 hours and then returns an error. The report is
> set not to timeout. What could be causing this behavior?
>
>

report table with links

How do you add a URL to the table? For example:

Post column
=Fields!PostID.Value

URL column
="<a href = ""http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=" & Fields!PostID.Value & """>" & Fields!PostID.Value & "</a"

Check out the JumpToUrl property of your textbox (on the Navigation tab), and set it to something like this:

=void(window.open('http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=" & Fields!PostID.Value & "','_blank'))"

Report Table Styles

When creating a Report using the Report Wizard you are asked to select a
Table Style from Bolb, Casual, Compact etc.
I read somewhere that you can add your own Table Style by editing a XML
File, but I can't remember where or what the XML File Name was.
Does anybody know?
Many thanks in advance.\Program Files\Microsoft SQL Server\80\Tools\Report Designer\Business
Intelligence Wizards\Reports\Styles\StyleTemplates.xml
Enjoy!
Mike G.
"John" <John@.discussions.microsoft.com> wrote in message
news:6EB19567-6F02-4B26-A705-ED9AD1ECC898@.microsoft.com...
> When creating a Report using the Report Wizard you are asked to select a
> Table Style from Bolb, Casual, Compact etc.
> I read somewhere that you can add your own Table Style by editing a XML
> File, but I can't remember where or what the XML File Name was.
> Does anybody know?
> Many thanks in advance.|||Thanks Mike.
"Mike G." wrote:
> \Program Files\Microsoft SQL Server\80\Tools\Report Designer\Business
> Intelligence Wizards\Reports\Styles\StyleTemplates.xml
> Enjoy!
> Mike G.
>
> "John" <John@.discussions.microsoft.com> wrote in message
> news:6EB19567-6F02-4B26-A705-ED9AD1ECC898@.microsoft.com...
> > When creating a Report using the Report Wizard you are asked to select a
> > Table Style from Bolb, Casual, Compact etc.
> >
> > I read somewhere that you can add your own Table Style by editing a XML
> > File, but I can't remember where or what the XML File Name was.
> >
> > Does anybody know?
> >
> > Many thanks in advance.
>
>

Report Table Question

I have a report that contains 3 tables. I want to create a 4th table that contains data from the 3 other tables. Is there a way to reference the value from one of the other tables' textbox in my new table. All of these tables are contained in the same report. The 4th table is basically a summary of the other three tables.

Thanks in advance.

i dont think so, because all the textboxes in the table will be created dynamically and you can't refer those textboxes.

you better go for a stored procedure which fetch data from those tables and create a dataset based on this SP and refer this dataset in your fourth table.

Thanks.

|||I've done this before. You have to name the text box that you are referencing first in its name property. Then in the other table you put =ReportItems!Nameoftextbox

Report table does not display all rows from dataset

I have a dataset that when run returns 270 rows. The table using the dataset in the report only prints the first row. I have the table grouped by a status type, but this is for when I can get multi-select paramenters installed and working. For now I just need the report to print all the returned rows. Help!!

Thanks!

Terry

Try to remove the grouping and see if you get the 270 rows in your report.

Jarret

Report Service output suppressing duplicate info

Hello,
Here is the situation. I have a table with a fieldname called VAL. I
call a stored procedure to return 4 records from the table.
Ok, the 4 records's field name VAL all have the same value , "Horray".
I created a report with a table and only want to display the VAL column.
I was expecting to see 4 rows of 'Horray' but I only see one row. Is there
place in the Report Service to turn off suppressing duplicate values?
product is Sql Server Reporting Service 2000.
Thanks
SteveOn the table, select your val column textbox , right click for properties you
can see "Hide duplicate" so this does the things..
Amarnath
"steve kwon" wrote:
> Hello,
> Here is the situation. I have a table with a fieldname called VAL. I
> call a stored procedure to return 4 records from the table.
> Ok, the 4 records's field name VAL all have the same value , "Horray".
> I created a report with a table and only want to display the VAL column.
> I was expecting to see 4 rows of 'Horray' but I only see one row. Is there
> place in the Report Service to turn off suppressing duplicate values?
> product is Sql Server Reporting Service 2000.
> Thanks
> Steve
>
>|||hi steve
here's another solution
right click the text box, click properties, click on visibility tab. select
Expression radio button and click on the function button. enter this
expression there
=(Previous(Fields!PK_Field1.Value) = Fields!PK_Field1.Value)
replace PK_Field1 with the field name that determines the duplicate value
for you. repeat the same step for all the text boxes that where you want to
suppress the duplicates.
this way, if you had a report like this
field1val 1234 abcd
field1val 1234 xyz
field2val 3988 alsdk
you'll see this output:
field1val 1234 abcd
xyz
field2val 3988 alsdk
hope it helps
"steve kwon" wrote:
> Hello,
> Here is the situation. I have a table with a fieldname called VAL. I
> call a stored procedure to return 4 records from the table.
> Ok, the 4 records's field name VAL all have the same value , "Horray".
> I created a report with a table and only want to display the VAL column.
> I was expecting to see 4 rows of 'Horray' but I only see one row. Is there
> place in the Report Service to turn off suppressing duplicate values?
> product is Sql Server Reporting Service 2000.
> Thanks
> Steve
>
>

Report Server: division between 2 subtotal, can this be done?

I have a table that have the following fields:
Outlet,EmployeeNumber,WorkingDate,WorkingHour,Ince ntive

when I put it into a matrix report, the Outlet is assiged to Page, the EmployeeNumber is assgined to ROW, the WorkingDate is assigned to Column and both WorkingHour & Incentive is assigned to Data(display in row but not column). There is a subtotal at the extreme right side.

May I know how can I squeeze in another element display at the report showing sum of Incentive / sum of WorkingHour? This new element shall be placed at each row as it's indicating the average incentive of each employee.

Thanks! :)I am not sure what you mean by assigned but in a query:

select Outlet
, EmployeeNumber
, WorkingDate
, WorkingHour
, Incentive
, ((select sum(WorkingHour) from tablename) / (select sum(Incentive) from tablename)) as HrsPerIncentive
from tablename

good luck

ddave|||Hi Dolfandave,

Thanks a lot, I did it the similar way but didn't realize i could just do it like that! there is an important condition for the subqueries as the EmployeeNumber must match each other.

However, this is not my ultimate goal. By doing this way, we are "hardcode"-ing the IncentivePerHr in every single row, which is not the proper way to do it isn't it? Yes, I'm trying to calculate IncentivePerHr not HrPerIncentive.

This is how the report should look like:
................WorkingDates............. ......................Subtotal <-- given by Report Designer in BI Dev Studio
EmployeNumber...............1/1..1/2..1/3..1/4.........1/31
12345..........Incentive......$0...$3...$6... $1..........$5.....$68
................WorkingHour...3hr..5hr..6hr.. 2hr.........4hr.....200hr
45678..........Incentive......$1...$6...$3... $3..........$8.....$100
................WorkingHour...7hr..7hr..3hr.. 3hr.........3hr.....190hr
23456..........Incentive......$6...$7...$6... $2..........$3.....$99
................WorkingHour...4hr..2hr..9hr.. 7hr.........8hr.....140hr

There is a subtotal given by the report designer. How can I just add a column to show the IncentivePerHr by making use of the subtotal results to do a division? There is a potential problem too as some EmployeeNumber has ZERO hours in total thus the parser will complain that we are trying to divide by ZERO.

any idea?

Report referencing dynamic table

Hi all
Just wondering how one would approach buidling an RS report based on a
dynamic table ie. Each time the report is run, the table may have a
different number of columns.
Here is what I'm trying to acheive:
The user enters a date range for the report. The report then calls a SQL
stored proc which generates a table containing columns for each day in the
date range. That table is then used as a datasource in the report.
Any assistance guidance will be appreciated
Thanks in advance
GeorgeOn Jul 5, 6:23 pm, "_george" <none@.nojne@.none> wrote:
> Hi all
> Just wondering how one would approach buidling an RS report based on a
> dynamic table ie. Each time the report is run, the table may have a
> different number of columns.
> Here is what I'm trying to acheive:
> The user enters a date range for the report. The report then calls a SQL
> stored proc which generates a table containing columns for each day in the
> date range. That table is then used as a datasource in the report.
> Any assistance guidance will be appreciated
> Thanks in advance
> George
The best way to handle this is to use a matrix report. The days would
appear as columns in the matrix report; of course, it supports dynamic
pivoted columns. Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant