Concatenate strings in group column

I have following problem:
I want to display a concatenated string in a group row of a report.
The grouping works fine, i can "sum" numerical values etc. as
expected, but i could not find a way to concatenate strings (from the
rows that make up the group). "Join(...)" does not work (gives me an
error message).
I can use "First(...) & Last(...)" to concatenate the string columns
from the first and last row of the group, but couldn't find a way to
concatenate the string columns from all rows...
I am using SQL Server 2005 / Visual Studio 2005.I use a CLR aggregate directly in SQL Server. Hopes your datasource is SQL
Server 2005.
In reports, I use Replace function to change my separator character.
--
Jean-Pierre Riehl
http://blog.djeepy1.net
http://www.bewise.fr
"PR" <pr_gg@.egal.de> wrote in message
news:76e4156a-8805-4e71-927a-7f2c848f295d@.q77g2000hsh.googlegroups.com...
>I have following problem:
> I want to display a concatenated string in a group row of a report.
> The grouping works fine, i can "sum" numerical values etc. as
> expected, but i could not find a way to concatenate strings (from the
> rows that make up the group). "Join(...)" does not work (gives me an
> error message).
> I can use "First(...) & Last(...)" to concatenate the string columns
> from the first and last row of the group, but couldn't find a way to
> concatenate the string columns from all rows...
> I am using SQL Server 2005 / Visual Studio 2005.
>
>|||On Jan 17, 8:21 am, "Jean-Pierre Riehl" <jean-pierre.ri...@.bewise.fr>
wrote:
> I use a CLR aggregate directly in SQL Server. Hopes your datasource is SQL
> Server 2005.
> In reports, I use Replace function to change my separator character.
> --
> Jean-Pierre Riehlhttp://blog.djeepy1.nethttp://www.bewise.fr
> "PR" <pr...@.egal.de> wrote in message
> news:76e4156a-8805-4e71-927a-7f2c848f295d@.q77g2000hsh.googlegroups.com...
>
thanks for the idea. I do use SQL Server 2005, however, since this is
a "group" column in a report, i can't use SQL to concatenate the
strings as i deal with a grouped subset of the sql query result...|||On Jan 16, 9:31=A0am, PR <pr...@.egal.de> wrote:
> I have following problem:
> I want to display a concatenated string in a group row of a report.
> The grouping works fine, i can "sum" numerical values etc. as
> expected, but i could not find a way to concatenate strings (from the
> rows that make up the group). "Join(...)" does not work (gives me an
> error message).
> I can use "First(...) & Last(...)" =A0to concatenate the string columns
> from the first and last row of the group, but couldn't find a way to
> concatenate the string columns from all rows...
> I am using SQL Server 2005 / Visual Studio 2005.
I'm having a hard time visualizing what you want to display. Do you
want your concantenation in each detail row or just in a group header?
Can you mock up an example?|||On Jan 21, 4:06 pm, toolman <t...@.infocision.com> wrote:
> I'm having a hard time visualizing what you want to display. Do you
> want your concantenation in each detail row or just in a group header?
> Can you mock up an example?
I want the concatenation in each detail row. I have some group "sum"s
that work fine, and want to do the same for a string. Basically it's
just a normal grouping of columns with group sums etc.. just that i
need to concatenate a string besides building the group sums.|||On Jan 22, 4:23=A0am, PR <pr...@.egal.de> wrote:
> On Jan 21, 4:06 pm, toolman <t...@.infocision.com> wrote:
> > I'm having a hard time visualizing what you want to display. =A0Do you
> > want your concantenation in each detail row or just in a group header?
> > Can you mock up an example?
> I want the concatenation in each detail row. I have some group "sum"s
> that work fine, and want to do the same for a string. Basically it's
> just a normal grouping of columns with group sums etc.. just that i
> need to concatenate a string besides building the group sums.
Is this what you want?
=3D"String Expression " & SUM(Fields!Name.Value)
It would look something like:
String Expression 1234.56
If you're wanting to concantenate actual field values, say like if
you're grouping on a combination of fields then:
=3DFields!GroupField1.Value & ", " & Fields!GroupField2.Value
would give you something like City, State or Company, Division
HTH|||On Jan 22, 8:57 pm, toolman <t...@.infocision.com> wrote:
> Is this what you want?
> ="String Expression " & SUM(Fields!Name.Value)
> It would look something like:
> String Expression 1234.56
> If you're wanting to concantenate actual field values, say like if
> you're grouping on a combination of fields then:
> =Fields!GroupField1.Value & ", " & Fields!GroupField2.Value
> would give you something like City, State or Company, Division
> HTH
Sorry, no, i don't want to concatenate strings with the sum value of
the group.
I have a grouping in the report, where a sum value (i.e. =Sum(Fields!
Name.NumValue)) is written to one textfield of the row.
What i want to do is concatenate a string from the groups result set -
pretty much like the "Sum" sums up the numerical values from the
result set over the rows. If "Join" would work it would look like
=Join(Fields!Name.StringValue) for the other text field.. but
unfortunately that does not work.
Any ideas ?|||On Jan 23, 3:50=A0am, PR <pr...@.egal.de> wrote:
> On Jan 22, 8:57 pm, toolman <t...@.infocision.com> wrote:
>
> > Is this what you want?
> > =3D"String Expression " & SUM(Fields!Name.Value)
> > It would look something like:
> > String Expression 1234.56
> > If you're wanting to concantenate actual field values, say like if
> > you're grouping on a combination of fields then:
> > =3DFields!GroupField1.Value & ", " & Fields!GroupField2.Value
> > would give you something like City, State or Company, Division
> > HTH
> Sorry, no, i don't want to concatenate strings with the sum value of
> the group.
> I have a grouping in the report, where a sum value (i.e. =3DSum(Fields!
> Name.NumValue)) is written to one textfield of the row.
> What i want to do is concatenate a string from the groups result set -
> pretty much like the "Sum" sums up the numerical values from the
> result set over the rows. If "Join" would work it would look like
> =3DJoin(Fields!Name.StringValue) for the other text field.. but
> unfortunately that does not work.
> Any ideas ?
I think if you can get your Fields!Name.StringValue values into an
array, you could then use Join() to get what you want. Unfortunately,
I'm not enough of a VB or .NET guy to guide you through that.
Hopefully someone else can jump at this point. Sorry I can't get you
further.|||On Jan 23, 5:03 pm, toolman <t...@.infocision.com> wrote:
> I think if you can get your Fields!Name.StringValue values into an
> array, you could then use Join() to get what you want. Unfortunately,
> I'm not enough of a VB or .NET guy to guide you through that.
> Hopefully someone else can jump at this point. Sorry I can't get you
> further.
Thanks for your effort. Yes, Join() requires an array.. and i have no
idea how to convert the string values into an array to do that (and
could not find anything about it in the documentation).|||Hello, PR.
Did you find any solution to your problem? I have the same issue and I can't
find any solution for this...
Thank you.
--
Alexandra
"PR" wrote:
> On Jan 23, 5:03 pm, toolman <t...@.infocision.com> wrote:
> >
> > I think if you can get your Fields!Name.StringValue values into an
> > array, you could then use Join() to get what you want. Unfortunately,
> > I'm not enough of a VB or .NET guy to guide you through that.
> > Hopefully someone else can jump at this point. Sorry I can't get you
> > further.
> Thanks for your effort. Yes, Join() requires an array.. and i have no
> idea how to convert the string values into an array to do that (and
> could not find anything about it in the documentation).
>|||On Feb 22, 12:13 pm, Alexandra Ribeiro
<AlexandraRibe...@.discussions.microsoft.com> wrote:
> Hello, PR.
> Did you find any solution to your problem? I have the same issue and I can't
> find any solution for this...
> Thank you.
> --
> Alexandra
>
Hi Alexandra,
no, i haven't found a solution for this. Maybe using a sub-report is
an option, but i haven't tried this yet.sqlsql

Concatenate strings after assigning text in place of bit strings

I have a whole bunch of bit fields in an SQL data base, which makes it a little messy to report on.

I thought a nice idea would be to assigne a text string/null value to each bit field and concatenate all of them into a result.

This is the basic logic goes soemthing like this:

select case new_accountant = 1 then 'acct/' end +

case new_advisor = 1 then 'adv/' end +

case new_attorney = 1 then 'atty/' end as String

from new_database

The output would be

Null, acct/, adv/, atty, acct/adv/, acct/atty/... acct/adv/atty/

So far, nothing I have tried has worked.

Any ideas?

I solved my own problem:

I needed to include an else statement

select fullname,
case when new_accountant = 1 then 'acct/' else '' end +
case when new_advisor = 1 then 'adv/' else '' end +
case when new_attorney = 1 then 'atty/' else '' end as Str1
from dbo.newdatabase

|||

I solved my own problem:

select fullname,
case when new_accountant = 1 then 'acct/' else '' end +
case when new_advisor = 1 then 'adv/' else '' end +
case when new_attorney = 1 then 'atty/' else '' end as Str1
from dbo.database

Concatenate Multiple Records Into One Field

Someone please help!. I am trying to create a view in SQL Server 2000 to use for a report but I am having problems with concatenating multiple values into one field. In my example below I am trying to list all records in my queried table in columnA then concatenate a list of all other records that share the same value in column B of the queried table into another field. If there are no other matches for a row in columnA then I would leave the corresponding field in columnB blank. Thanks in advance.

TABLE
ColumnA ColumnB
1......A
2......B
3......C
4......A
5......A
6......B
7......C
8......D
9......C
10.....E

EXPECTED OUTPUT
ColumnA ColumnB
1......4,5
2......6
3......7
4......1,5
5......1, 4
6......2
7......3
8......
9......3,7
10.....create function ConcatFld (@.RowId int, @.RowVal char(1))
returns varchar(100) AS
begin
declare @.Ret varchar(100)
set @.Ret=''
select @.Ret= @.Ret + cast(ColA as varchar)+',' from Tbl1 where ColB=@.RowVal and ColA<>@.RowId
if len(@.Ret) > 0
set @.Ret = left(@.Ret,len(@.Ret)-1)
return @.Ret
end
--------

select ColA, dbo.ConcatFld(ColA,ColB) from Tbl1|||Thanks Upalsen, this is exactly what I need!!!

Computing and assigning a value to a textbox from other data regions

I am new to SSRS, so perhaps its a trivial question. I was wondering that since all controls have names in the report, is it possible to programatically access values of different textboxes, do some computation and then assign to another text box? I know how to do it using the Aggregate functions and operators, but am not sure if I can access values from textboxes within two different tables and assign the computed value to a third text box on the page (not belonging to any table or other control).

somethig like.... txtTotal.Value = FormatCurrency(txtSalesTotal.Value) - txtDiscount.Value));

Any ideas?

DNG.

People!!!!!!!!!!!

How can I add values in two text boxes and display in the third one? Apparently I can't do this simple thing in SSRS or may be I am missing something? I remember you can do this in Crystal by accessing textbox control and can access their values to be used elsewhere on the page. Can we create variables, where I can store the value of a text box and use it later in other text boxes?

Plz. help!!!

DNG

|||

Let's see if I understand what you are asking. TextBox1 has the sum of values from DataSet1. TextBox2 has the sum of values from DataSet2. These text boxes are not in a table. To sum the value you would need the following in the expression of textbox3:

Code Snippet

=reportitems!TextBox1.Value + reportitems!TextBox2.Value

Hope this helps.

Simone

|||If any of these textboxes are in tables (which I believe you may have mentioned) then you can only reference items within the same scope and you will receive errors.|||

I should add that in the above case, you can use the expression contained within the first two text boxes to give you the value in the 3rd.

ex. TextBox3 expression =

Code Snippet

=Sum(Fields!Value.Value, "dsTest") + Sum(Fields!Value.Value, "dsTest2")

Simone|||Thanks for your help.sqlsql

Computed parameters for subscribed & scheduled report?

Hi folks,
I've done some experimenting with the SQL Server Reporting Services and
like them very much.
However, I now have a problem I wasn't able to find a solution for: I need
to create a monthly report about my company's sales. I've set up my
subscription so that the report is mailed (as pdf) on every Monday in the
new month's first week.
Of course, I want to have the *LAST* month reported, not the current one,
so I'd need to compute the start- and end date of the report (which are
parameters). How can I do that?
Thanks!
JensCheck out the DateAdd function at
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vblr7/html/vaorivbruntimelibrarykeywords.asp
roland|||I had exactly the same need (actually who doesn't! this should be easier ..)
and finally decided to write and external assembly that defines the ToDate
and FromDate based on relative periods stored in the DB (PeriodID passed )
The PeriodID allows me to find the calculation rule for taking today as a
starting point and start calculating relative Periods (range or single dates
( last month, last 3 month, first quarter, etc, etc.))
Sebastian|||In news:OtzAWWzYEHA.3564@.TK2MSFTNGP11.phx.gbl,
Sebastian Talamoni <sebastian.talamoni@.radventure.nl> typed:
> I had exactly the same need (actually who doesn't! this
> should be easier ..) and finally decided to write and
> external assembly that defines the ToDate and FromDate
> based on relative periods stored in the DB (PeriodID
> passed )
> The PeriodID allows me to find the calculation rule for
> taking today as a starting point and start calculating
> relative Periods (range or single dates ( last month,
> last 3 month, first quarter, etc, etc.))
> Sebastian
What I meant was: Don't provide any parameters on call. Schedule your
reports to run on every first of a month. Define defaults for the parameters
which calculate e.g. the first of the last month that way:
= new System.DateTime(Year(DateAdd(DateInterval.Month, -1,
Globals!ExecutionTime)), Month(DateAdd(DateInterval.Month, -1,
Globals!ExecutionTime)),1)
Other values in comparible manner, left to your phantasy :)
roland

Computed column question

I have a SQL table that maintains a field on the status of a report being completed.

I have in the record the date the report is due (DateDue)

I also have a field called DaysLate which I have set to be a calculated field with formula:

DATEDIFF(dd, DateDue, GETDATE())

Thsi works but when the report is *not* late I'd like this to be null is there I way I can do this conditional calculation in a calculated field?

Regards

Cvive

CASE WHEN {Your formula}<0 THEN NULL ELSE {Your formula} END

|||

Many thanks for that - that did the job perfectly.

Clive

complicated derived column

I have a complicated derived column. In a Crystal report it is this:

if isnull( {YSA.YSAbA}) then
(
stringVar accountPart1 := "1";
stringVar accountPart2;
stringVar accountPart3;
stringVar accountPart4;

if left({YAAREP.A7KMCD},2) = 'aa' then
accountPart4 := '01'
else if left({YAAREP.A7KMCD},2) = 'bb' then
accountPart4 := '02'
else if left({YAAREP.A7KMCD},2) = 'cc' then
accountPart4 := '03';

select {YAAREP.A7KNCD}
case "39000": accountPart3 := "115" //this is freight
case "35000": accountPart3 := "110"
case "36000": accountPart3 := "105"
default: accountPart3 := "105";

if "SERVICE" in {YAAREP.A7KMCD}
//if MIKE RYAN
then accountPart2 := "3300";

if {YAAREP.A7KNCD} = "39000" //this is freight
then accountPart2 := "4300";

if cdbl({YAAREP.A7KNCD}) > 44000 then
accountPart2 := left({YAAREP.A7KNCD},4);
if cdbl({YAAREP.A7KNCD}) > 44000 then
accountPart3 := right({YAAREP.A7KNCD},3);

accountPart1 + "-" + accountPart2 + "-" + accountPart3 + "-" + accountPart4
)
else {@.PrintDefaultSalesGL}

How should I do this in an SSIS? Or do I need to show how do this in SQL Query?

Thanks for the help!

Ashley:

I would avoid using the Derived column for what you are trying to do. Not that it cannot be done; it is just that I have not figured out a way to make the cell where you type the logic larger. Consequently, you end up having to keep typing a horizontal string and pretty soon you lose track of which elements stay together. It is great for "short" logic but for what you are trying to do I would just do it using SQL in SSIS.

desibull

|||

Or put it in a script component.

Complex sum

I have a report created using SQL Server Reporting tools.

The report contains a field 'FIELD A' which displays the difference of hours in that record.
At the end of the report, I need to sum all FIELD A and display in FIELD B.

How do I do it?One way to do this, if all the 'FIELD A' textboxes exist on one page, is to add the 'Field B' textbox to the Page Footer, and use the SUM aggregate over the textbox. For example,

=(Sum(ReportItems!FieldATextbox.Value))

This will only work if the expression is in the Page Header or Footer, and if all instances of the 'Field A' textbox is on the same page.

If not all instances will exist on one page, then you could use a simple custom function that is called from the 'Field A' textbox. This custom function adds the value passed to a member field, and then returns the value passed. In the 'Field B' textbox call a separate custom function that returns the value stored in the member field.

Ian

Complex SQL query

Hi,

I'm doing a report with a group of queries but right now is very slow, so I need to do it faster. These are not the real tables but will help:

The report needs to show the total products for every combination of ADDRESS and PRODUCT_TYPE. Assume these are the tables:

ADDRESS: ADDRESS_ID, ADDRESS_NAME
PRODUCT_TYPE: PRODUCT_TYPE_CODE, PRODUCT_TYPE_NAME
ORDER: ORDER_ID, DATE, ORDER_STATUS
ORDER_LINE: ORDER_ID, ORDER_PRODUCT_TYPE, PRODUCT_TOTAL
(This is an special table to handle the stock)
STOCK_INFO: STOCK_ACTUAL, DATE_UPDATED

This is what I'm doing in code (asp):

1. Retrieve all the address (and put it in array)
2. Retrieve all the product types (and put it in array)
3. Using double "FOR" I build the query for every combination of Address and ProductType

This is still slow (and is even better than before) and I would like to put everything in just 1 query and get this data ready to show in HTML

Address Product Type 1 Product Type2 Product Type3
Address1 TotProdType11 TotProdType21 TotProdType31
Address2 TotProdType12 TotProdType22 TotProdType32
.....

I'll really appreciate any help. And also any better idea to do these is welcome (is just I don't have to much knowledge in very complex queries)

Thanks in advance

Moving to Transact-SQL forum...|||I don't see any relationship between Address table and the Product_Type at all. How is it related ?|||

use a CROSS JOIN in SQL server if you want a combination of all products and addresses.

eg

select a.address, Address p.Product Type from address a cross join product_type p

Note that if you are wanting a cartesian product here, you should specify no join criteria, as you want every address and product combination. That should be much quicker than doing it in client side code. However, you will need some kind of join to get the totals for each product, as a cross joins blindly combines all rows from 1 table to all the rows from another. I need further clarification here.

You will then have to turn the results into a pivot table. In SQL 2005, use the PIVOT function, in SQL 2003 and earlier, you will need to use a case statement:

SELECT a.address,

CASE

WHEN p.Product_type = 'Product A' -- whatever first product type is

THEN ..... -- your code, I think from your example you want a sum() here

WHEN p.Product_type = 'Product A' --

THEN

etc

END

from.......

Hope that helps

from address a cross join product_type p

GROUP BY a.address

Complex report, I think

Hi,
I have a report that is basically a Matrix report that has totalling rows
throughout the vertical aspect of the report (not just at the end) and a
totalling columns at the end. Can Reporting Services deal with this using
the toolbox and so on, or do I need to get coding? I am pretty sure the
wizard aint going to help. I have split my data into a number of datasets
and hidden the column headings on all but the top Matrix, but then how do I
add the totalling rows? Especially as they need to be the sum of a number
of fields up the length of the column that aren't even contiguous. I
basically want to add totals at various places that I define and define
which values should be added. I know that there will always be the same row
and column headings popping up each time, so from that side there is no
problem.
Please give me some direction on this as I am sort of looking at this a bit
:-s at the moment.
TIA,
JarrydOn Jul 3, 5:23 am, "Jarryd" <jar...@.community.nospam> wrote:
> Hi,
> I have a report that is basically a Matrix report that has totalling rows
> throughout the vertical aspect of the report (not just at the end) and a
> totalling columns at the end. Can Reporting Services deal with this using
> the toolbox and so on, or do I need to get coding? I am pretty sure the
> wizard aint going to help. I have split my data into a number of datasets
> and hidden the column headings on all but the top Matrix, but then how do I
> add the totalling rows? Especially as they need to be the sum of a number
> of fields up the length of the column that aren't even contiguous. I
> basically want to add totals at various places that I define and define
> which values should be added. I know that there will always be the same row
> and column headings popping up each time, so from that side there is no
> problem.
> Please give me some direction on this as I am sort of looking at this a bit
> :-s at the moment.
> TIA,
> Jarryd
The most common way I have dealt w/this same issue is to use either
cursors or while loops to loop through the dataset in the stored
procedure/query that is sourcing the report (usually one cursor/while
loop per desired subtotal/etc). It can get a bit messy; however, it
does get the job done. Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant|||Hi,
Yeah I suppose that is the way. Just kind of a bit long winded and I was
just hoping I could get the basic Matrix and use tools to add things like
totaling rows and columns in with a bit more control and flexability as what
is available. Cos what I am going to have to do now means that RS isn't
really going to do much more than add some colour to cells and gridlines.
TIA,
Jarryd
"EMartinez" <emartinez.pr1@.gmail.com> wrote in message
news:1183468763.165468.188890@.m36g2000hse.googlegroups.com...
> On Jul 3, 5:23 am, "Jarryd" <jar...@.community.nospam> wrote:
>> Hi,
>> I have a report that is basically a Matrix report that has totalling rows
>> throughout the vertical aspect of the report (not just at the end) and a
>> totalling columns at the end. Can Reporting Services deal with this
>> using
>> the toolbox and so on, or do I need to get coding? I am pretty sure the
>> wizard aint going to help. I have split my data into a number of
>> datasets
>> and hidden the column headings on all but the top Matrix, but then how do
>> I
>> add the totalling rows? Especially as they need to be the sum of a
>> number
>> of fields up the length of the column that aren't even contiguous. I
>> basically want to add totals at various places that I define and define
>> which values should be added. I know that there will always be the same
>> row
>> and column headings popping up each time, so from that side there is no
>> problem.
>> Please give me some direction on this as I am sort of looking at this a
>> bit
>> :-s at the moment.
>> TIA,
>> Jarryd
>
> The most common way I have dealt w/this same issue is to use either
> cursors or while loops to loop through the dataset in the stored
> procedure/query that is sourcing the report (usually one cursor/while
> loop per desired subtotal/etc). It can get a bit messy; however, it
> does get the job done. Hope this helps.
> Regards,
> Enrique Martinez
> Sr. Software Consultant
>|||Doesn't help you now but RS 2008 will have a lot more functionality around
both tables and matrixes.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Jarryd" <jarryd@.community.nospam> wrote in message
news:%23LyQVYYvHHA.4012@.TK2MSFTNGP03.phx.gbl...
> Hi,
> Yeah I suppose that is the way. Just kind of a bit long winded and I was
> just hoping I could get the basic Matrix and use tools to add things like
> totaling rows and columns in with a bit more control and flexability as
> what is available. Cos what I am going to have to do now means that RS
> isn't really going to do much more than add some colour to cells and
> gridlines.
> TIA,
> Jarryd
> "EMartinez" <emartinez.pr1@.gmail.com> wrote in message
> news:1183468763.165468.188890@.m36g2000hse.googlegroups.com...
>> On Jul 3, 5:23 am, "Jarryd" <jar...@.community.nospam> wrote:
>> Hi,
>> I have a report that is basically a Matrix report that has totalling
>> rows
>> throughout the vertical aspect of the report (not just at the end) and a
>> totalling columns at the end. Can Reporting Services deal with this
>> using
>> the toolbox and so on, or do I need to get coding? I am pretty sure the
>> wizard aint going to help. I have split my data into a number of
>> datasets
>> and hidden the column headings on all but the top Matrix, but then how
>> do I
>> add the totalling rows? Especially as they need to be the sum of a
>> number
>> of fields up the length of the column that aren't even contiguous. I
>> basically want to add totals at various places that I define and define
>> which values should be added. I know that there will always be the same
>> row
>> and column headings popping up each time, so from that side there is no
>> problem.
>> Please give me some direction on this as I am sort of looking at this a
>> bit
>> :-s at the moment.
>> TIA,
>> Jarryd
>>
>> The most common way I have dealt w/this same issue is to use either
>> cursors or while loops to loop through the dataset in the stored
>> procedure/query that is sourcing the report (usually one cursor/while
>> loop per desired subtotal/etc). It can get a bit messy; however, it
>> does get the job done. Hope this helps.
>> Regards,
>> Enrique Martinez
>> Sr. Software Consultant
>|||Fair enough, Rome wasn't built in a day.
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:uNDwjfYvHHA.4552@.TK2MSFTNGP03.phx.gbl...
> Doesn't help you now but RS 2008 will have a lot more functionality around
> both tables and matrixes.
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Jarryd" <jarryd@.community.nospam> wrote in message
> news:%23LyQVYYvHHA.4012@.TK2MSFTNGP03.phx.gbl...
>> Hi,
>> Yeah I suppose that is the way. Just kind of a bit long winded and I was
>> just hoping I could get the basic Matrix and use tools to add things like
>> totaling rows and columns in with a bit more control and flexability as
>> what is available. Cos what I am going to have to do now means that RS
>> isn't really going to do much more than add some colour to cells and
>> gridlines.
>> TIA,
>> Jarryd
>> "EMartinez" <emartinez.pr1@.gmail.com> wrote in message
>> news:1183468763.165468.188890@.m36g2000hse.googlegroups.com...
>> On Jul 3, 5:23 am, "Jarryd" <jar...@.community.nospam> wrote:
>> Hi,
>> I have a report that is basically a Matrix report that has totalling
>> rows
>> throughout the vertical aspect of the report (not just at the end) and
>> a
>> totalling columns at the end. Can Reporting Services deal with this
>> using
>> the toolbox and so on, or do I need to get coding? I am pretty sure
>> the
>> wizard aint going to help. I have split my data into a number of
>> datasets
>> and hidden the column headings on all but the top Matrix, but then how
>> do I
>> add the totalling rows? Especially as they need to be the sum of a
>> number
>> of fields up the length of the column that aren't even contiguous. I
>> basically want to add totals at various places that I define and define
>> which values should be added. I know that there will always be the
>> same row
>> and column headings popping up each time, so from that side there is no
>> problem.
>> Please give me some direction on this as I am sort of looking at this a
>> bit
>> :-s at the moment.
>> TIA,
>> Jarryd
>>
>> The most common way I have dealt w/this same issue is to use either
>> cursors or while loops to loop through the dataset in the stored
>> procedure/query that is sourcing the report (usually one cursor/while
>> loop per desired subtotal/etc). It can get a bit messy; however, it
>> does get the job done. Hope this helps.
>> Regards,
>> Enrique Martinez
>> Sr. Software Consultant
>>
>

complex report

have the following data from a query
age sex
39 M
48 F
14 M
etc...
need to be displayed as
Age Group Number of Male Number of Female Total in Age Group
0 -2 2 3
5
3 - 9 24 33
57
....
70 and above 12 11
23
Total Males ##
Total Females ##
Total
##
Any suggestions would be helpful.This question is better geared towards a SQL forum rather then reporting
services.
Search online for the SQL syntax of Case. You'll want to create a select
statment where you do something similar to this.
SELECT '0-2' as Age Group, SUM(CASE age>0 AND age < 2 AND sex = "M", 1, 0
END) as Number of Male, SUM(CASE age>0 AND age < 2 AND sex = "F", 1, 0 END)
as Number of Female, SUM(CASE age > 0 AND age < 2) as Total in Age Group
UNION
SELECT '3-9' as Age Group, SUM(CASE age>3 AND age < 9 AND sex = "M", 1, 0
END) as Number of Male, SUM(CASE age>3 AND age < 9 AND sex = "F", 1, 0 END)
as Number of Female, SUM(CASE age > 3 AND age < 9) as Total in Age Group
And so on and so forth for each of the age/sex ranges.
-Michael Abair
Programmer Analyst
Chicos FAS Inc
"Chop" <Chop@.discussions.microsoft.com> wrote in message
news:0D4FB1BC-D77D-4E6B-A728-B798B6EAF6E4@.microsoft.com...
> have the following data from a query
> age sex
> 39 M
> 48 F
> 14 M
> etc...
> need to be displayed as
> Age Group Number of Male Number of Female Total in Age Group
> 0 -2 2 3
> 5
> 3 - 9 24 33
> 57
> ....
> 70 and above 12 11
> 23
> Total Males ##
> Total Females ##
> Total
> ##
> Any suggestions would be helpful.|||You also have the option of creating fields within RS; for the values
use:
iif(fields!age.value >=0 and fields!age.value < 3, "0 - 2",
iif(fields!age.value < 10, "3-9", ...))
Then use a matrix item and use the calculated field as your row group,
sex as your column group.
Chop wrote:
> have the following data from a query
> age sex
> 39 M
> 48 F
> 14 M
> etc...
> need to be displayed as
> Age Group Number of Male Number of Female Total in Age Group
> 0 -2 2 3
> 5
> 3 - 9 24 33
> 57
> ....
> 70 and above 12 11
> 23
> Total Males ##
> Total Females ##
> Total
> ##
> Any suggestions would be helpful.

Complex query, please help

I think this is any easy one, and hopefully full DDL is not required as ther
e
is A LOT.
Goal: Create a report(using RS) that shows client name, id, total amt
loaned, total amt paid. Data will be displayed in a grid and the balance
must be displayed in the pertinent age category..so if Tom is 10 days late
his balance would be displayed in a 30 Days or Less column.
There are a total of 4 tables (2 for most of the data, 2 to display cust id
and name)
ISSUE: The probelm I am running into is that I have select the last date
where there a payment has not been posted where the date is > the due date
but and < the current date, but I receive mulitple records for each customer
.
Cust table A contains (Name, CustID)
Cust table B contains (CustID, LoanId)
Data table C contains (LoanId, LoanAmt)
Data table D contains (CustID, AmtPaid, Duedate,#of DaysLate) Contains
records up until end of loan schedule (could be 2008) I didn't create this
PSUEDO Code:
Select CustId, Name, LoanAmt, Balance (LoanAmt - AmtPaid )
From
Table A, Table B, Table, C, Table E (all joined)
Where the last Duedate < today and AmtPaid is null
SELECT DISTINCT
loan_payments.pmt_loan_id,
loan_payments.pmt_received_date, SUM(loan_payments.pmt_received_total) AS
PAID, loans.loan_cust_id,
loans.loan_total_amount, loans.loan_pending,
loans.loan_closed, Party.LName, Party.FName, loan_payments.pmt_due_date,
loan_payments.pmt_days_late
FROM Party RIGHT OUTER JOIN
Customer LEFT OUTER JOIN
loans LEFT OUTER JOIN
loan_payments ON loans.loan_id =
loan_payments.pmt_loan_id ON Customer.ID = loans.loan_cust_id ON Party.ID =
Customer.PartyID
WHERE (loans.loan_closed = 0) AND (loans.loan_pending = 0) AND
pmt_received_total is null AND pmt_days_late IN
(Select pmt_days_late
FROM loan_payments
WHERE pmt_days_late > 0
)
GROUP BY loan_payments.pmt_loan_id, loan_payments.pmt_received_date,
loans.loan_cust_id, loans.loan_total_amount, loans.loan_pending,
loans.loan_closed, Party.LName, Party.FName,
loan_payments.pmt_due_date,loan_payments.pmt_days_late
Order by loans.loan_cust_id
Thanks for any direction, advice, sites...Hi
You are wrong DDL is always the clearest and most useful thing to post.
In your query you do not need DISTINCT as you have an aggregate. If there
have multiple values then you are grouping on the wrong set of columns. You
may need to do your calculations in a derived table and then rejoin to the
load table to get the rows as you require.
I am not sure why you are using OUTER JOINS everywhere, I would have thought
that with load_payments being projected you can use an INNER JOIN for all
the JOINS.
It is usually clearer if you have your ON clause next to each JOIN it
relates to.
To get all those that have not paid in the date range try replacing:
AND pmt_days_late IN (Select pmt_days_late
FROM loan_payments
WHERE pmt_days_late > 0 )
with
AND EXISTS ( SELECT * FROM loan_payments d
WHERE d.due_date < getdate()
AND d.pmt_received_total is null
AND d.pmt_loan_id = loan_payments.pmt_loan_id )
If you wish to pivot this information you can use:
SELECT
p.pmt_loan_id,
SUM(p.pmt_received_total) AS PAID,
L.loan_cust_id,
L.loan_total_amount,
L.loan_pending,
L.loan_closed,
R.LName,
R.FName,
D.OverDue10,
D.OverDue20,
D.OverDue30
FROM Customer C
JOIN Party R ON R.ID = C.PartyID
JOIN loans L C.ID = L.loan_cust_id
JOIN loan_payments p ON L.loan_id = p.pmt_loan_id
JOIN
( SELECT pmt_load_id,
SUM(CASE WHEN DATEDIFF(dd,GetDate(),Duedate) <= 10 THEN AmtDue ELSE 0 END)
AS OverDue10,
SUM(CASE WHEN DATEDIFF(dd,GetDate(),Duedate) > 10 AND
DATEDIFF(dd,GetDate(),Duedate) <= 20 THEN AmtDue ELSE 0 END) AS OverDue20
SUM(CASE WHEN DATEDIFF(dd,GetDate(),Duedate) > 20 AND
DATEDIFF(dd,GetDate(),Duedate) <= 30 THEN AmtDue ELSE 0 END) AS OverDue30
FROM loan_payments
WHERE due_date < getdate()
AND pmt_received_total is null
GROUP BY pmt_load_id ) D ON D.pmt_load_id = L.pmt_load_id
WHERE L.loan_closed = 0
AND L.loan_pending = 0
AND EXISTS ( SELECT * FROM loan_payments y
WHERE y.due_date < getdate()
AND y.pmt_received_total is null
AND y.pmt_loan_id = p.pmt_loan_id )
John
"DigitalVixen" <DigitalVixen@.discussions.microsoft.com> wrote in message
news:6D4E75E5-9A8C-4E20-A372-63E0724967B4@.microsoft.com...
>I think this is any easy one, and hopefully full DDL is not required as
>there
> is A LOT.
> Goal: Create a report(using RS) that shows client name, id, total amt
> loaned, total amt paid. Data will be displayed in a grid and the balance
> must be displayed in the pertinent age category..so if Tom is 10 days late
> his balance would be displayed in a 30 Days or Less column.
> There are a total of 4 tables (2 for most of the data, 2 to display cust
> id
> and name)
> ISSUE: The probelm I am running into is that I have select the last date
> where there a payment has not been posted where the date is > the due date
> but and < the current date, but I receive mulitple records for each
> customer.
> Cust table A contains (Name, CustID)
> Cust table B contains (CustID, LoanId)
> Data table C contains (LoanId, LoanAmt)
> Data table D contains (CustID, AmtPaid, Duedate,#of DaysLate) Contains
> records up until end of loan schedule (could be 2008) I didn't create this
> PSUEDO Code:
> Select CustId, Name, LoanAmt, Balance (LoanAmt - AmtPaid )
> From
> Table A, Table B, Table, C, Table E (all joined)
> Where the last Duedate < today and AmtPaid is null
> SELECT DISTINCT
> loan_payments.pmt_loan_id,
> loan_payments.pmt_received_date, SUM(loan_payments.pmt_received_total) AS
> PAID, loans.loan_cust_id,
> loans.loan_total_amount, loans.loan_pending,
> loans.loan_closed, Party.LName, Party.FName, loan_payments.pmt_due_date,
> loan_payments.pmt_days_late
> FROM Party RIGHT OUTER JOIN
> Customer LEFT OUTER JOIN
> loans LEFT OUTER JOIN
> loan_payments ON loans.loan_id =
> loan_payments.pmt_loan_id ON Customer.ID = loans.loan_cust_id ON Party.ID
> =
> Customer.PartyID
> WHERE (loans.loan_closed = 0) AND (loans.loan_pending = 0) AND
> pmt_received_total is null AND pmt_days_late IN
> (Select pmt_days_late
> FROM loan_payments
> WHERE pmt_days_late > 0
> )
> GROUP BY loan_payments.pmt_loan_id, loan_payments.pmt_received_date,
> loans.loan_cust_id, loans.loan_total_amount, loans.loan_pending,
> loans.loan_closed, Party.LName, Party.FName,
> loan_payments.pmt_due_date,loan_payments.pmt_days_late
> Order by loans.loan_cust_id
> Thanks for any direction, advice, sites...
>|||I am wrong in that this is not an easy one or with regards to the DDL? I
thank you for your reply. I apologize as I do not know how to derive the
query, hence my asking for help. I guess if I would have posted the DDL the
n
I wouldn't be receiving errors. (Server: Msg 156, Level 15, State 1, Line 11
Incorrect syntax near the keyword 'On'.
Server: Msg 170, Level 15, State 1, Line 20
Line 20: Incorrect syntax near 'D'.)
Please see DDL below.
Once again thank you
CREATE TABLE [loans] (
[loan_id] [int] IDENTITY (1000, 1) NOT NULL ,
[loan_typ_id] [int] NOT NULL ,
[loan_cust_id] [int] NOT NULL ,
[loan_emp_id] [int] NOT NULL ,
[loan_cnt_id] [int] NULL ,
[loan_tax_total_amt] [decimal](9, 4) NOT NULL ,
[loan_total_amount] [decimal](9, 2) NOT NULL ,
[loan_amount_down] [decimal](9, 2) NOT NULL ,
[loan_payment_amount] [decimal](9, 2) NOT NULL ,
[loan_interest_rate] [decimal](9, 2) NOT NULL ,
[loan_first_due_date] [datetime] NOT NULL ,
[loan_pending] [tinyint] NOT NULL ,
[loan_closed] [tinyint] NOT NULL ,
[loan_date_created] [datetime] NOT NULL ,
[loan_date_closed] [datetime] NULL ,
[loan_clarion_total] [decimal](9, 2) NULL ,
[loan_clarion_amtfin] [decimal](9, 2) NULL ,
[loan_clarion_adj_total] [decimal](9, 2) NULL ,
[loan_amtfin_chk] [int] NULL ,
[loan_contract_chk] [int] NULL
) ON [PRIMARY]
GO
CREATE TABLE [loan_payments] (
[pmt_loan_id] [int] NOT NULL ,
[pmt_number] [int] NOT NULL ,
[pmt_type] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[pmt_due_date] [datetime] NOT NULL ,
[pmt_due_total] [decimal](9, 2) NOT NULL ,
[pmt_due_taxes_total] [decimal](9, 4) NULL ,
[pmt_due_principal] [decimal](9, 2) NOT NULL ,
[pmt_due_interest] [decimal](9, 2) NOT NULL ,
[pmt_late_date] [datetime] NOT NULL ,
[pmt_late_fee] [decimal](9, 2) NOT NULL ,
[pmt_received_date] [datetime] NULL ,
[pmt_received_total] [decimal](9, 2) NULL ,
[pmt_received_taxes_total] [decimal](9, 4) NULL ,
[pmt_received_principal] [decimal](9, 2) NULL ,
[pmt_received_interest] [decimal](9, 2) NULL ,
[pmt_received_late_fee] [decimal](9, 2) NULL ,
[pmt_received_mtd_id] [int] NULL ,
[pmt_received_emp_id] [int] NULL ,
[pmt_recorded_date] [datetime] NULL ,
[pmt_trk_pmt_num] [int] NULL ,
[pmt_source] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[pmt_days_late] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
CREATE TABLE [Party] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[LName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[MName] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[FName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Address1] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Address2] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[City] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[State] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PostalCode] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Country] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Gender] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[MaritalStatus] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Age] [int] NULL ,
[Attention] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Department] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Type] [varchar] (7) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Phone1] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Phone2] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Fax1] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Fax2] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[EMail] [varchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Title] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Comments] [varchar] (1000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Orig_ID] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[County] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LengthAtAddress] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[OwnRent] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
CREATE TABLE [Customer] (
[ID] [int] IDENTITY (1000, 1) NOT NULL ,
[PartyID] [int] NULL ,
[ShipPartyID] [int] NULL ,
[Pending] [tinyint] NOT NULL ,
[BirthDate] [datetime] NULL ,
[SSNumber] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[EmployeeID] [int] NOT NULL ,
[License] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LicenseVerified] [int] NULL ,
[LicenseExp] [datetime] NULL ,
[ScrubSize] [varchar] (400) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ReferralID] [int] NULL ,
[DoNotContact] [bit] NOT NULL ,
[BestDay] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[BestTime] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TimeZone] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TranscriptionLocation] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[Bank] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RoutingNumber] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CheckingNumber] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SavingsNumber] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[BankCity] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[BankAddress] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[BankState] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[BankZip] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[BankPhone] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CCType] [int] NULL ,
[CCNumber] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CCName] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CCExpMM] [int] NULL ,
[CCExpYYYY] [int] NULL ,
[CCVerificationNumber] [int] NULL ,
[CCBillingFrequency] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[EmployerPartyID] [int] NULL ,
[PrevEmpID] [int] NULL ,
[RelativePartyID] [int] NULL ,
[DriverLicense] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DriverLicenseState] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[NursingLicState] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[NursingLicenseNumber] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[ListOfNurses] [bit] NOT NULL ,
[StudyBuddy] [bit] NOT NULL ,
[WebFormAcct] [bit] NOT NULL ,
[LONBegin] [datetime] NULL ,
[LONEnd] [datetime] NULL ,
[SBBegin] [datetime] NULL ,
[SBEnd] [datetime] NULL ,
[WFBegin] [datetime] NULL ,
[WFEnd] [datetime] NULL ,
[ClinicalExamDate] [datetime] NULL ,
[ClinicalPassed] [bit] NOT NULL ,
[EnrollDate] [datetime] NULL ,
[Enroll] [bit] NULL ,
[GraduationExamDate] [datetime] NOT NULL ,
[GraduationPassed] [bit] NOT NULL ,
[StateBoardDate] [datetime] NULL ,
[StateBoardPassed] [bit] NOT NULL ,
[DegreeGoal] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DateCreated] [datetime] NOT NULL ,
[GroupID] [int] NULL ,
[Orig_ID] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Import_Status] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Pending_RecNo] [int] NULL ,
[DOB] [datetime] NULL ,
[MID] [int] NULL ,
[LastFUDate] [datetime] NOT NULL ,
[ReferralName] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[StudentLoan] [bit] NULL ,
[NumberStudentLoan] [int] NULL ,
[Scholarship] [int] NULL ,
[ScholarshipName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LPNTimeLength] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
"John Bell" wrote:

> Hi
> You are wrong DDL is always the clearest and most useful thing to post.
> In your query you do not need DISTINCT as you have an aggregate. If there
> have multiple values then you are grouping on the wrong set of columns. Yo
u
> may need to do your calculations in a derived table and then rejoin to the
> load table to get the rows as you require.
> I am not sure why you are using OUTER JOINS everywhere, I would have thoug
ht
> that with load_payments being projected you can use an INNER JOIN for all
> the JOINS.
> It is usually clearer if you have your ON clause next to each JOIN it
> relates to.
> To get all those that have not paid in the date range try replacing:
> AND pmt_days_late IN (Select pmt_days_late
> FROM loan_payments
> WHERE pmt_days_late > 0 )
> with
> AND EXISTS ( SELECT * FROM loan_payments d
> WHERE d.due_date < getdate()
> AND d.pmt_received_total is null
> AND d.pmt_loan_id = loan_payments.pmt_loan_id )
> If you wish to pivot this information you can use:
> SELECT
> p.pmt_loan_id,
> SUM(p.pmt_received_total) AS PAID,
> L.loan_cust_id,
> L.loan_total_amount,
> L.loan_pending,
> L.loan_closed,
> R.LName,
> R.FName,
> D.OverDue10,
> D.OverDue20,
> D.OverDue30
> FROM Customer C
> JOIN Party R ON R.ID = C.PartyID
> JOIN loans L C.ID = L.loan_cust_id
> JOIN loan_payments p ON L.loan_id = p.pmt_loan_id
> JOIN
> ( SELECT pmt_load_id,
> SUM(CASE WHEN DATEDIFF(dd,GetDate(),Duedate) <= 10 THEN AmtDue ELSE 0 END)
> AS OverDue10,
> SUM(CASE WHEN DATEDIFF(dd,GetDate(),Duedate) > 10 AND
> DATEDIFF(dd,GetDate(),Duedate) <= 20 THEN AmtDue ELSE 0 END) AS OverDue20
> SUM(CASE WHEN DATEDIFF(dd,GetDate(),Duedate) > 20 AND
> DATEDIFF(dd,GetDate(),Duedate) <= 30 THEN AmtDue ELSE 0 END) AS OverDue30
> FROM loan_payments
> WHERE due_date < getdate()
> AND pmt_received_total is null
> GROUP BY pmt_load_id ) D ON D.pmt_load_id = L.pmt_load_id
> WHERE L.loan_closed = 0
> AND L.loan_pending = 0
> AND EXISTS ( SELECT * FROM loan_payments y
> WHERE y.due_date < getdate()
> AND y.pmt_received_total is null
> AND y.pmt_loan_id = p.pmt_loan_id )
> John
> "DigitalVixen" <DigitalVixen@.discussions.microsoft.com> wrote in message
> news:6D4E75E5-9A8C-4E20-A372-63E0724967B4@.microsoft.com...
>
>|||Although your syntax is technically ok, it is a difficult to read and/or to
maintain. I recommend that you consistently use table Aliases, and separate
teh major clauses of the query, use indenting, and cosistently use one type
of Outer Join (Left or Right), but do not mix them. Also, unless absilutel
y
necessary, keep join conditions next to join - If you can;t then use
parenmtheses to make clear what is happeningLook at the below to see how muc
h
easier it is to erad and comprehend then what you posted...
SELECT P.pmt_loan_id, P.pmt_received_date,
SUM(P.pmt_received_total) PAID,
L.loan_cust_id, L.loan_total_amount,
L.loan_pending, L.loan_closed, R.LName,
R.FName, P.pmt_due_date,
P.pmt_days_late
-- --
From Loans L
Left Join Loan_Payments P On P.pmt_loan_id = L.loan_id
Left Join Customer C On C.ID = L.loan_cust_id
Left Join Party R On R.ID = C.PartyID
-- --
Where L.loan_closed = 0
And (L.loan_pending = 0)
And pmt_received_total Is Null
And pmt_days_late IN
(Select pmt_days_late
From loan_payments
From pmt_days_late > 0)
-- --
GROUP BY P.pmt_loan_id, P.pmt_received_date,
L.loan_cust_id, L.loan_total_amount,
L.loan_pending, L.loan_closed, R.LName,
R.FName, P.pmt_due_date,
P.pmt_days_late
-- --
Order by L.loan_cust_id
-- --
-- --
"DigitalVixen" wrote:

> I think this is any easy one, and hopefully full DDL is not required as th
ere
> is A LOT.
> Goal: Create a report(using RS) that shows client name, id, total amt
> loaned, total amt paid. Data will be displayed in a grid and the balance
> must be displayed in the pertinent age category..so if Tom is 10 days late
> his balance would be displayed in a 30 Days or Less column.
> There are a total of 4 tables (2 for most of the data, 2 to display cust i
d
> and name)
> ISSUE: The probelm I am running into is that I have select the last date
> where there a payment has not been posted where the date is > the due date
> but and < the current date, but I receive mulitple records for each custom
er.
> Cust table A contains (Name, CustID)
> Cust table B contains (CustID, LoanId)
> Data table C contains (LoanId, LoanAmt)
> Data table D contains (CustID, AmtPaid, Duedate,#of DaysLate) Contains
> records up until end of loan schedule (could be 2008) I didn't create this
> PSUEDO Code:
> Select CustId, Name, LoanAmt, Balance (LoanAmt - AmtPaid )
> From
> Table A, Table B, Table, C, Table E (all joined)
> Where the last Duedate < today and AmtPaid is null
> SELECT DISTINCT
> loan_payments.pmt_loan_id,
> loan_payments.pmt_received_date, SUM(loan_payments.pmt_received_total) AS
> PAID, loans.loan_cust_id,
> loans.loan_total_amount, loans.loan_pending,
> loans.loan_closed, Party.LName, Party.FName, loan_payments.pmt_due_date,
> loan_payments.pmt_days_late
> FROM Party RIGHT OUTER JOIN
> Customer LEFT OUTER JOIN
> loans LEFT OUTER JOIN
> loan_payments ON loans.loan_id =
> loan_payments.pmt_loan_id ON Customer.ID = loans.loan_cust_id ON Party.ID
=
> Customer.PartyID
> WHERE (loans.loan_closed = 0) AND (loans.loan_pending = 0) AND
> pmt_received_total is null AND pmt_days_late IN
> (Select pmt_days_late
> FROM loan_payments
> WHERE pmt_days_late > 0
> )
> GROUP BY loan_payments.pmt_loan_id, loan_payments.pmt_received_date,
> loans.loan_cust_id, loans.loan_total_amount, loans.loan_pending,
> loans.loan_closed, Party.LName, Party.FName,
> loan_payments.pmt_due_date,loan_payments.pmt_days_late
> Order by loans.loan_cust_id
> Thanks for any direction, advice, sites...
>|||And the error you describe is because the Join COndiditions were not properl
y
nested - caused by poor formatting.
"CBretana" wrote:
> Although your syntax is technically ok, it is a difficult to read and/or t
o
> maintain. I recommend that you consistently use table Aliases, and separa
te
> teh major clauses of the query, use indenting, and cosistently use one typ
e
> of Outer Join (Left or Right), but do not mix them. Also, unless absilut
ely
> necessary, keep join conditions next to join - If you can;t then use
> parenmtheses to make clear what is happeningLook at the below to see how m
uch
> easier it is to erad and comprehend then what you posted...
>
> SELECT P.pmt_loan_id, P.pmt_received_date,
> SUM(P.pmt_received_total) PAID,
> L.loan_cust_id, L.loan_total_amount,
> L.loan_pending, L.loan_closed, R.LName,
> R.FName, P.pmt_due_date,
> P.pmt_days_late
> -- --
> From Loans L
> Left Join Loan_Payments P On P.pmt_loan_id = L.loan_id
> Left Join Customer C On C.ID = L.loan_cust_id
> Left Join Party R On R.ID = C.PartyID
> -- --
> Where L.loan_closed = 0
> And (L.loan_pending = 0)
> And pmt_received_total Is Null
> And pmt_days_late IN
> (Select pmt_days_late
> From loan_payments
> From pmt_days_late > 0)
> -- --
> GROUP BY P.pmt_loan_id, P.pmt_received_date,
> L.loan_cust_id, L.loan_total_amount,
> L.loan_pending, L.loan_closed, R.LName,
> R.FName, P.pmt_due_date,
> P.pmt_days_late
> -- --
> Order by L.loan_cust_id
> -- --
> -- --
>
> "DigitalVixen" wrote:
>|||Thanks but I still get a record for each due date where the
pmt_recieved_total is null. I need to display 1 record (being the oldest du
e
due date where pmt_received_total is null) for each cust_id.
"CBretana" wrote:
> Although your syntax is technically ok, it is a difficult to read and/or t
o
> maintain. I recommend that you consistently use table Aliases, and separa
te
> teh major clauses of the query, use indenting, and cosistently use one typ
e
> of Outer Join (Left or Right), but do not mix them. Also, unless absilut
ely
> necessary, keep join conditions next to join - If you can;t then use
> parenmtheses to make clear what is happeningLook at the below to see how m
uch
> easier it is to erad and comprehend then what you posted...
>
> SELECT P.pmt_loan_id, P.pmt_received_date,
> SUM(P.pmt_received_total) PAID,
> L.loan_cust_id, L.loan_total_amount,
> L.loan_pending, L.loan_closed, R.LName,
> R.FName, P.pmt_due_date,
> P.pmt_days_late
> -- --
> From Loans L
> Left Join Loan_Payments P On P.pmt_loan_id = L.loan_id
> Left Join Customer C On C.ID = L.loan_cust_id
> Left Join Party R On R.ID = C.PartyID
> -- --
> Where L.loan_closed = 0
> And (L.loan_pending = 0)
> And pmt_received_total Is Null
> And pmt_days_late IN
> (Select pmt_days_late
> From loan_payments
> From pmt_days_late > 0)
> -- --
> GROUP BY P.pmt_loan_id, P.pmt_received_date,
> L.loan_cust_id, L.loan_total_amount,
> L.loan_pending, L.loan_closed, R.LName,
> R.FName, P.pmt_due_date,
> P.pmt_days_late
> -- --
> Order by L.loan_cust_id
> -- --
> -- --
>
> "DigitalVixen" wrote:
>|||Then you cannot have the Due Date in the Group BY Clause, Doing so tells th
e
Query Processor to output one row per Due Date...
Also, since you want the SUM(pmt_received_total) to include ALL Payments,
you cannot restrict the query to only those payment records where payment is
Null...
So, one way to do this is t ojoin to the payments table twice, once with all
the records, so we can so the Sum, and once to only get the last record than
SELECT P.pmt_loan_id,
SUM(P.pmt_received_total) PAID,
L.loan_cust_id, L.loan_total_amount,
L.loan_pending, L.loan_closed,
R.LName, R.FName,
LP.pmt_received_date, LP.pmt_due_date,
LP.pmt_days_late
-- --
From Loans L
Left Join Customer C On C.ID = L.loan_cust_id
Left Join Party R On R.ID = C.PartyID
Left Join Loan_Payments P On P.pmt_loan_id = L.loan_id
Left Join Loan_Payments LP
On LP.pmt_loan_id = L.loan_id
And LP.pmt_due_date =
(Select Max(pmt_due_date)
From Loan_Payments
Where pmt_loan_id = L.loan_id
And pmt_received_total Is Null
And pmt_days_late > 0)
-- --
Where L.loan_closed = 0
And (L.loan_pending = 0)
-- --
GROUP BY L.loan_cust_id,
P.pmt_loan_id,
L.loan_total_amount,
L.loan_pending, L.loan_closed,
R.LName, R.FName, LP.pmt_due_date,
LP.pmt_received_date, LP.pmt_days_late
-- --
Order by L.loan_cust_id
"DigitalVixen" wrote:
> Thanks but I still get a record for each due date where the
> pmt_recieved_total is null. I need to display 1 record (being the oldest
due
> due date where pmt_received_total is null) for each cust_id.
> "CBretana" wrote:
>|||>> Cust table A contains (Name, CustID)
Cust table B contains (CustID, LoanId)
Data table C contains (LoanId, LoanAmt)
Data table D contains (CustID, AmtPaid, Duedate,#of DaysLate) Contains
records [sic] up until end of loan schedule (could be 2008) I didn't
create this <<
Can you kill the guy that did? You need tables like:
LoanCoupons (loan_id, cust_id, loan_amt, due_date)
LoanPayments (loan_id, cust_id, payment_amt, payment_date)
Now you resolve these together to get the current status of each loan.
The normalization is awful in what you posted.
For the temporal stuff, use a SUM(CASE..) to get the ranges.
And why are you using all those OUTER JOINs? Don' t you have any DRI
in the schema?|||LOL, if only I knew. I am a newbie, please explain what is DRI?
"--CELKO--" wrote:

> Cust table B contains (CustID, LoanId)
> Data table C contains (LoanId, LoanAmt)
> Data table D contains (CustID, AmtPaid, Duedate,#of DaysLate) Contains
> records [sic] up until end of loan schedule (could be 2008) I didn't
> create this <<
> Can you kill the guy that did? You need tables like:
> LoanCoupons (loan_id, cust_id, loan_amt, due_date)
> LoanPayments (loan_id, cust_id, payment_amt, payment_date)
> Now you resolve these together to get the current status of each loan.
> The normalization is awful in what you posted.
> For the temporal stuff, use a SUM(CASE..) to get the ranges.
> And why are you using all those OUTER JOINs? Don' t you have any DRI
> in the schema?
>|||DRI = Declarative Referential Integrity. It is a mechanism that prevents you
from orphaning rows in a table. For example, if you have the following:
Create Table Customers
(
CustomerId Int
, FirstName VarChar(25)
, LastName VarChar(25)
)
Create Table Orders
(
CustomerId Int
, OrderDate DateTime
, OrderNumber Int
)
Without DRI, there is nothing to prevent you from accidently putting a
CustomerId value in the Orders table that does not exist in the Customers ta
ble.
The problem is that we then do not have any idea to whom the order belongs.
Further, without DRI mechanims, if you deleted a customer from the Customers
table, those customers might have orders in the Orders table. Those Orders w
ould
now be orphaned (the child table Orders would no longer have any parent
Customers) and again we would have no idea who made those Orders.
There are a handful of ways to enable DRI in SQL Server:
1. The "References" statement in the Create Table statement like so:
Create Table Orders
(
CustomerId Int References Customers(CustomerId)
, OrderDate DateTime
, OrderNumber Int
)
Note that you use the References statement on the child portion of the equat
ion.
2. Create a data diagram in the Enterprise Manager. Add both tables to the
display and drag from the column of one table to the matching column of the
other table. There are tutorials online with pretty pictures that better
illustrate this process.
There are other options that go with enabling DRI between two tables, but fo
r
now, that should get you started.
Thomas
"DigitalVixen" <DigitalVixen@.discussions.microsoft.com> wrote in message
news:73919415-C2A2-42E8-A058-FD3AF57E12F2@.microsoft.com...
> LOL, if only I knew. I am a newbie, please explain what is DRI?
> "--CELKO--" wrote:
>

Complex join

I have a C# application for tracking training. When I need to get the employee's missed training (exception report) it works fine on a one-by-one basis. Our HR folks can't sit and spin through 100s of screens, so I want to provide a comprehensive report for all employees.

Here is the first hack which places everything into a temporary table. Problem is I can't get a while loop to work with it as is. All three "sub queries" build the exception (missing) topics into the temp table.

I can loop in my application, but it makes the user click the print button for each report. I'm trying to get one report for all employees and all exceptions, breaking on employeeID. I figure they will run this quarterly to make sure everyone has their required training.

Any help is greatly appreciated. I didn't find anything in my ref books to help.

_E

DECLARE @.EmployeeID int
DECLARE @.MaxCount int

-- example only, real count will be +/- 200 and = to the number of active employees
set @.MaxCount = 10

-- would like to exclude inactive employees
SET @.EmployeeID =1

-- can use the employee table as input/limit?
WHILE @.EmployeeID < (select employeeID from employee)

DECLARE @.MyTable TABLE (TopicID INT)
INSERT INTO @.MyTable

-- topics based on primary role
SELECT rt.TopicID FROM ROLETOPICS rt
LEFT OUTER JOIN Employee e ON e.EmployeeID = @.EmployeeID
WHERE rt.RoleID = e.PrimaryRoleID
UNION
-- optional topcs
SELECT TopicID
FROM EmployeeTopics et
WHERE et.EmployeeID = @.EmployeeID
-- required topics (all employees)
UNION
SELECT TopicID
FROM Topic t
WHERE t.CategoryID = 3
SELECT mt.TopicID, Topic FROM @.MyTable mt
INNER JOIN Topic t ON mt.TopicID = t.TopicID
WHERE mt.TopicID NOT IN(SELECT tr.TopicID FROM Training tr WHERE tr.EmployeeID = @.EmployeeID)

--increment counters

I know I've left out some counter and variable initializers, but I can't get the basis loop to work at all for more than one record and that's specifice to an EmployeeID.

You don't need a loop, not sure of the best way to do the mandatory training, but this is a starter for 10.

SELECT ReqdTraining.employeeId, ReqdTraining.TopicId

FROM(

SELECT e.employeeId, rt.TopicID

FROM ROLETOPICS rt

JOIN Employee e ON rt.RoleID = e.PrimaryRoleID

UNION

SELECT et.employeeid, TopicID

FROM EmployeeTopics et

UNION

-- required topics (all employees)

SELECT E.EmployeeId, T.TopicID

FROM Topic t

CROSS JOIN Employee E

WHERE t.CategoryID = 3) ReqdTraining

LEFT JOIN Training T ON T.TopicId = ReqdTraining.TopicId

AND T.EmployeeId = ReqdTraining.EmployeeId

WHERE T.TopicId IS NULL

|||

Thank you, SimonSa. Cross join. D'Oh! Guess I should stick to object code, my DBA skills need work (which I'll get in the MCSD program.)

_E

Complex Dataset Filtering

I have an Item Report that lists over 50,000 items, we need to restrict using
one of several different methods.
Some times they will run the report with all items (no Where clause in
the SQL Statement).
Some times they will want to see only the items they call "common"
which are stored in a table (normally I would just do a join to that table).
Lastly, they want to run the report for a specific item (where Item = @.ItemNumber)
I would prefer not to create three different reports.
My first hope was to create all three datasets and then dynamically assigne
the dataset depending on a paramater, I have not been able to accomplish this.
Any suggestion would sure be great.
TerryPiece of cake...well, not at all. I don't have the time to write a specific
answer right now, but I promise that next week I will do it.
By now, I'll copy-paste an answer that I gave in another forum. I hope it
helps you:
My scenario was the following.
I had a parameterized report. There's only one parameter (called @.id_acm)
and the values are taken from a query (in the report>report parameters...
menu I choose "From Query" instead of "non-queried" and so a drop down list
is shown to the user) The source query for the parameter values looked like
this:
SELECT id_acm AS param_value, nombre AS param_label
FROM tbl_acm
And the query used to get the information for the report (the one I wrote in
the DataSet) looked like this:
SELECT *
FROM tbl_sales S
INNER JOIN tbl_acm acm ON S.id_acm = acm.id_acm
WHERE acm.id_acm = @.id_acm
I also did NOT specified any default value for the parameter.
When my boss executed the report he noticed that it showed anything until he
selected a value from the drop down list. My boss told me that he wanted to
see all the information by default, and if he needed information about a
specific person he would select the person from the drop down list. Here
comes the tricky part.
I noticed that all the current (and future) values for the id_acm field in
the tbl_acm table were greater than 0.
So I modified the source query for the parameter like this:
SELECT -5 AS param_value, 'Everyone' AS param_label
UNION
SELECT id_acm AS param_value, nombre AS param_label
FROM tbl_acm
And I specified a default value of -5 for the @.id_acm parameter.
Also, I modified the principal query like this:
IF @.id_acm = -5
BEGIN
SELECT *
FROM tbl_sales S
INNER JOIN tbl_acm acm ON S.id_acm = acm.id_acb
END ELSE
BEGIN
SELECT *
FROM tbl_sales S
INNER JOIN tbl_acm acm ON S.id_acm = acm.id_acb
WHERE acm.id_acm = @.id_acm
END
Let me explain this: when the user selects "Everyone" the principal query
returns the information of all the persons (because there's no WHERE clause)
If the user selects a specific name, then the principal query returns only
that person's information (because there's a WHERE clause filtering the data)
By default the paramter value is -5 and so the report shows everyone's
information.
Of course, if you have 2 parameters you'll have to nest 2 IF statemens. In
fact, if you have n parameters you'll have to nest n IF statemens and you'll
get 2^n posibilities for the WHERE clauses (that's the big pain)
I don't know if this helps in your scenario, but I hope it does.|||U Rock! That toally solved my problem.
I tried something simular prior but I really used more of a SQL Notation.
Thanks, I had all but givin up!
"F. Dwarf" wrote:
> Piece of cake...well, not at all. I don't have the time to write a specific
> answer right now, but I promise that next week I will do it.
> By now, I'll copy-paste an answer that I gave in another forum. I hope it
> helps you:
> My scenario was the following.
> I had a parameterized report. There's only one parameter (called @.id_acm)
> and the values are taken from a query (in the report>report parameters...
> menu I choose "From Query" instead of "non-queried" and so a drop down list
> is shown to the user) The source query for the parameter values looked like
> this:
> SELECT id_acm AS param_value, nombre AS param_label
> FROM tbl_acm
> And the query used to get the information for the report (the one I wrote in
> the DataSet) looked like this:
> SELECT *
> FROM tbl_sales S
> INNER JOIN tbl_acm acm ON S.id_acm = acm.id_acm
> WHERE acm.id_acm = @.id_acm
> I also did NOT specified any default value for the parameter.
> When my boss executed the report he noticed that it showed anything until he
> selected a value from the drop down list. My boss told me that he wanted to
> see all the information by default, and if he needed information about a
> specific person he would select the person from the drop down list. Here
> comes the tricky part.
> I noticed that all the current (and future) values for the id_acm field in
> the tbl_acm table were greater than 0.
> So I modified the source query for the parameter like this:
> SELECT -5 AS param_value, 'Everyone' AS param_label
> UNION
> SELECT id_acm AS param_value, nombre AS param_label
> FROM tbl_acm
> And I specified a default value of -5 for the @.id_acm parameter.
> Also, I modified the principal query like this:
> IF @.id_acm = -5
> BEGIN
> SELECT *
> FROM tbl_sales S
> INNER JOIN tbl_acm acm ON S.id_acm = acm.id_acb
> END ELSE
> BEGIN
> SELECT *
> FROM tbl_sales S
> INNER JOIN tbl_acm acm ON S.id_acm = acm.id_acb
> WHERE acm.id_acm = @.id_acm
> END
> Let me explain this: when the user selects "Everyone" the principal query
> returns the information of all the persons (because there's no WHERE clause)
> If the user selects a specific name, then the principal query returns only
> that person's information (because there's a WHERE clause filtering the data)
> By default the paramter value is -5 and so the report shows everyone's
> information.
> Of course, if you have 2 parameters you'll have to nest 2 IF statemens. In
> fact, if you have n parameters you'll have to nest n IF statemens and you'll
> get 2^n posibilities for the WHERE clauses (that's the big pain)
> I don't know if this helps in your scenario, but I hope it does.|||What I do in my reports is something like
SELECT * FROM tbl_sales S
INNER JOIN tbl_acm acm ON S.id_acm = acm.id_acm
WHERE (acm.id_acm = @.id_acm or @.id_acm = -5)
"Terry B" wrote:
> U Rock! That toally solved my problem.
> I tried something simular prior but I really used more of a SQL Notation.
> Thanks, I had all but givin up!
>
> "F. Dwarf" wrote:
> > Piece of cake...well, not at all. I don't have the time to write a specific
> > answer right now, but I promise that next week I will do it.
> > By now, I'll copy-paste an answer that I gave in another forum. I hope it
> > helps you:
> >
> > My scenario was the following.
> > I had a parameterized report. There's only one parameter (called @.id_acm)
> > and the values are taken from a query (in the report>report parameters...
> > menu I choose "From Query" instead of "non-queried" and so a drop down list
> > is shown to the user) The source query for the parameter values looked like
> > this:
> >
> > SELECT id_acm AS param_value, nombre AS param_label
> > FROM tbl_acm
> >
> > And the query used to get the information for the report (the one I wrote in
> > the DataSet) looked like this:
> > SELECT *
> > FROM tbl_sales S
> > INNER JOIN tbl_acm acm ON S.id_acm = acm.id_acm
> > WHERE acm.id_acm = @.id_acm
> >
> > I also did NOT specified any default value for the parameter.
> >
> > When my boss executed the report he noticed that it showed anything until he
> > selected a value from the drop down list. My boss told me that he wanted to
> > see all the information by default, and if he needed information about a
> > specific person he would select the person from the drop down list. Here
> > comes the tricky part.
> > I noticed that all the current (and future) values for the id_acm field in
> > the tbl_acm table were greater than 0.
> > So I modified the source query for the parameter like this:
> >
> > SELECT -5 AS param_value, 'Everyone' AS param_label
> > UNION
> > SELECT id_acm AS param_value, nombre AS param_label
> > FROM tbl_acm
> >
> > And I specified a default value of -5 for the @.id_acm parameter.
> > Also, I modified the principal query like this:
> >
> > IF @.id_acm = -5
> > BEGIN
> > SELECT *
> > FROM tbl_sales S
> > INNER JOIN tbl_acm acm ON S.id_acm = acm.id_acb
> > END ELSE
> > BEGIN
> > SELECT *
> > FROM tbl_sales S
> > INNER JOIN tbl_acm acm ON S.id_acm = acm.id_acb
> > WHERE acm.id_acm = @.id_acm
> > END
> >
> > Let me explain this: when the user selects "Everyone" the principal query
> > returns the information of all the persons (because there's no WHERE clause)
> > If the user selects a specific name, then the principal query returns only
> > that person's information (because there's a WHERE clause filtering the data)
> > By default the paramter value is -5 and so the report shows everyone's
> > information.
> > Of course, if you have 2 parameters you'll have to nest 2 IF statemens. In
> > fact, if you have n parameters you'll have to nest n IF statemens and you'll
> > get 2^n posibilities for the WHERE clauses (that's the big pain)
> > I don't know if this helps in your scenario, but I hope it does.

Complete Execution History of a report

Hi,

I have some reports(both parameterized and non-parameterized) for which I need to have an execution history on the report server. These are not scheduled and so the history should only be for the instances when users view the report.

I checked the 'Store all report execution snapshots in history' but it does not store the snapshot in history everytime the report is viewed. What do I do to achieve this?

Thanks

One way is to query the ExecutionLog table in the Reporting Services Database. You will need to join to the Catalog table to retrieve the report names. The only issue here is that the ExectutionLog table is cleaned out periodically (monthly?) so you will only get the last month's worth of executions.

I have copies of the ExecutionLog and Catalog table created in another database and run a job to update them every few minutes. I run a Reporting Statisitcs report off these tables.

regards

Ray

|||

You may also want to read this RS 2005 technical article: http://msdn.microsoft.com/library/en-us/dnsql90/html/RSMnRptExPf.asp

-- Robert

|||

Hi Robert,

Thanks for your the link. However, I do not need the detailed execution statistics provided by the solution. I simply want the report snapshot to be stored in history whenever a report is viewed/run. This behaviour is by default in Crystal Enterprise 10 and I thought it would be reasonably simple in Reporting Services as well. Is the answer no ?

Thanks again,

Hemil.

|||SSRS supports automatically creating history snapshots for reports which are set to execute from a snapshot. Each time the snapshot is updated, it will be added to report history. That said, we do not support automatically creating history for every single live execution. I am not certain how useful that would be...|||

Considering this sceranio...there are three users of the same 'roles' who can access the same report. At any time whoever generates the report the first time. It will be kept in the history folder. When the rest of the users checks on the history they know who's has generated the report and that they themselve need not generate the reports ( since someone has done). I guess in this situation...it would be useful to describe why he needs that facility?

Regards

Alu

Complete Execution History of a report

Hi,

I have some reports(both parameterized and non-parameterized) for which I need to have an execution history on the report server. These are not scheduled and so the history should only be for the instances when users view the report.

I checked the 'Store all report execution snapshots in history' but it does not store the snapshot in history everytime the report is viewed. What do I do to achieve this?

Thanks

One way is to query the ExecutionLog table in the Reporting Services Database. You will need to join to the Catalog table to retrieve the report names. The only issue here is that the ExectutionLog table is cleaned out periodically (monthly?) so you will only get the last month's worth of executions.

I have copies of the ExecutionLog and Catalog table created in another database and run a job to update them every few minutes. I run a Reporting Statisitcs report off these tables.

regards

Ray

|||

You may also want to read this RS 2005 technical article: http://msdn.microsoft.com/library/en-us/dnsql90/html/RSMnRptExPf.asp

-- Robert

|||

Hi Robert,

Thanks for your the link. However, I do not need the detailed execution statistics provided by the solution. I simply want the report snapshot to be stored in history whenever a report is viewed/run. This behaviour is by default in Crystal Enterprise 10 and I thought it would be reasonably simple in Reporting Services as well. Is the answer no ?

Thanks again,

Hemil.

|||SSRS supports automatically creating history snapshots for reports which are set to execute from a snapshot. Each time the snapshot is updated, it will be added to report history. That said, we do not support automatically creating history for every single live execution. I am not certain how useful that would be...|||

Considering this sceranio...there are three users of the same 'roles' who can access the same report. At any time whoever generates the report the first time. It will be kept in the history folder. When the rest of the users checks on the history they know who's has generated the report and that they themselve need not generate the reports ( since someone has done). I guess in this situation...it would be useful to describe why he needs that facility?

Regards

Alu

Compilation

Hi
I want to know how to compiled a report in crystal report 8.5 or 9.0??
In the previous version of crystal Report (Version 8.0) I could do this task, but in the newest version I can find the option...
Please help meHi
This file contains an add-in that enables you to compile and distribute reports in the English version of Crystal Reports 8 and 8.5. This is the instalation File scr8_distr_expert.exe

Thanks

Compatibility vs2005/sql2005 and sql2000

Hi,
Are there any known compatibility issues with VS/SQL 2005 and SQL Server 2000?
In other words, can I (continue to) develop a report project for SQL server
2000 when I have VS/SQL2005 installed on my pc?
--
Thanks,
EdgarI have both VS 2003 and VS 2005. I have modified and deployed reports from
VS 2003 to RS 2000.
What you cannot do is create or modify a report in VS 2005 and deploy to RS
2000. RS 2005 reports require RS 2005.
But, you can upgrade RS 2000 to RS 2005 leaving SQL Server at 2000 (still
need a SQL Server 1005 license).
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Edgar" <Edgar@.discussions.microsoft.com> wrote in message
news:6DDB23AB-3B8D-4635-B85C-08C7EF2CE08B@.microsoft.com...
> Hi,
> Are there any known compatibility issues with VS/SQL 2005 and SQL Server
> 2000?
> In other words, can I (continue to) develop a report project for SQL
> server
> 2000 when I have VS/SQL2005 installed on my pc?
>
> --
> Thanks,
> Edgar|||Ok...but can you create reports in 2003 and deploy to 2005?|||Yes, you can use the RS 2000 report designer which installs into VS 2003 for
this purpose. RS 2000 RDLs can be directly published from the old report
designer to RS 2005 report servers. Note: the RS 2000 report designer will
not support any of the new RS 2005 features (such as Interactive Sort,
etc.).
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Dave" <KillnComputers@.Verizon.Net> wrote in message
news:1130962651.742396.163040@.g43g2000cwa.googlegroups.com...
> Ok...but can you create reports in 2003 and deploy to 2005?
>|||Some more questions about this to get it clear:
- Can I use VS 2005 with RS2000 to deploy reports to SQL 2000 Report Server?
- Can I use VS 2005 with RS2005 to deploy reports to SQL 2000 Report Server?
Thanks,
Edgar
"Robert Bruckner [MSFT]" wrote:
> Yes, you can use the RS 2000 report designer which installs into VS 2003 for
> this purpose. RS 2000 RDLs can be directly published from the old report
> designer to RS 2005 report servers. Note: the RS 2000 report designer will
> not support any of the new RS 2005 features (such as Interactive Sort,
> etc.).
> -- Robert
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Dave" <KillnComputers@.Verizon.Net> wrote in message
> news:1130962651.742396.163040@.g43g2000cwa.googlegroups.com...
> > Ok...but can you create reports in 2003 and deploy to 2005?
> >
>
>|||No to both.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Edgar" <Edgar@.discussions.microsoft.com> wrote in message
news:4068C225-D2EE-4613-B1EE-C03A098F810C@.microsoft.com...
> Some more questions about this to get it clear:
> - Can I use VS 2005 with RS2000 to deploy reports to SQL 2000 Report
> Server?
> - Can I use VS 2005 with RS2005 to deploy reports to SQL 2000 Report
> Server?
>
> --
> Thanks,
> Edgar
>
> "Robert Bruckner [MSFT]" wrote:
>> Yes, you can use the RS 2000 report designer which installs into VS 2003
>> for
>> this purpose. RS 2000 RDLs can be directly published from the old report
>> designer to RS 2005 report servers. Note: the RS 2000 report designer
>> will
>> not support any of the new RS 2005 features (such as Interactive Sort,
>> etc.).
>> -- Robert
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>>
>> "Dave" <KillnComputers@.Verizon.Net> wrote in message
>> news:1130962651.742396.163040@.g43g2000cwa.googlegroups.com...
>> > Ok...but can you create reports in 2003 and deploy to 2005?
>> >
>>

Compatibility Crystal report

i have application developed in MS ACCESS 2003. (not VB and Access)
can i use CReports with my mdb;
i split the mdb into frontend and backend.
can i use CR Successfully.
Kindly reply.
ThanksSearch here
http://support.businessobjects.com/

Comparison

Hey guys what would be the easiest way to create a report of value changes for particular records from one day to the next.... ?
Any suggestions would be greatly appreciated...
thanks,
JonathanDo you record record changes? (audit)
Does the audit contain a time stamp?|||That database has a modifieddate field in epoch...
I convert it into human readable then will have compare values from
(i.e 9/13/2007 <> 9/14/2007)
If there are any changes amongst the field values create a report of those changes