Building out sums inside of a table (SSRS 2000)

Hi all,
I am converting some Crystal reports... and replacing formulas which
are automatically grouped in Crystal. Essentially, I am comparing two
different date ranges, and need to be able to know how many items were
shipped for each customer/item combination within each date range.
I have created calculated fields to hold the quantity shipped if that
row falls in the appropriate date range... this just adds the quantity
shipped in the field if it falls within the appropriate date range.
Qty2ShipPD1=IIF(( Parameters!startInvoicePD1.Value< Fields!
inv_dt.Value AND Parameters!EndInvoicePD1.Value> Fields!inv_dt.Value),
Fields!qty_to_ship.Value,0.0)
and
Qty2ShipPD2=IIF(( Parameters!startInvoicePD2.Value< Fields!
inv_dt.Value AND Parameters!EndInvoicePD2.Value> Fields!inv_dt.Value),
Fields!qty_to_ship.Value,0.0)
I need to build out a table that looks close to the following example:
Cust No
Item No Total for the Period
{need total quantity here}
PD1 {Qty2ShipPD1}
PD2 {Qty2ShipPD2}
I created a matrix control and dragged cus_no, item_no into the rows,
with item_no in the columns, and then summed the Qty2ShipPD1 in the
Details...
This works, in the sense that I am actually getting grouped
information which is correct for the customer/item combinations...
But the format won't work, and I have no idea how to do the same thing
in a table. Every time I do it in a table, I just get the same,
entire sum for the entire query for the two calculated fields.
Help?
THANKS!
JoshAlternatively - is there a way I can use a Matrix Control with only 1
column? If so, I might be able to live with that.
Thanks,
Josh
On Apr 23, 5:57 pm, rumplyminz <squa...@.gmail.com> wrote:
> Hi all,
> I am converting some Crystal reports... and replacing formulas which
> are automatically grouped in Crystal. Essentially, I am comparing two
> different date ranges, and need to be able to know how many items were
> shipped for each customer/item combination within each date range.
> I have created calculated fields to hold the quantity shipped if that
> row falls in the appropriate date range... this just adds the quantity
> shipped in the field if it falls within the appropriate date range.
> Qty2ShipPD1=IIF(( Parameters!startInvoicePD1.Value< Fields!
> inv_dt.Value AND Parameters!EndInvoicePD1.Value> Fields!inv_dt.Value),
> Fields!qty_to_ship.Value,0.0)
> and
> Qty2ShipPD2=IIF(( Parameters!startInvoicePD2.Value< Fields!
> inv_dt.Value AND Parameters!EndInvoicePD2.Value> Fields!inv_dt.Value),
> Fields!qty_to_ship.Value,0.0)
> I need to build out a table that looks close to the following example:
> Cust No
> Item No Total for the Period
> {need total quantity here}
> PD1 {Qty2ShipPD1}
> PD2 {Qty2ShipPD2}
> I created a matrix control and dragged cus_no, item_no into the rows,
> with item_no in the columns, and then summed the Qty2ShipPD1 in the
> Details...
> This works, in the sense that I am actually getting grouped
> information which is correct for the customer/item combinations...
> But the format won't work, and I have no idea how to do the same thing
> in a table. Every time I do it in a table, I just get the same,
> entire sum for the entire query for the two calculated fields.
> Help?
> THANKS!
> Josh|||Doh! Got it.
In my
Sum statement, I was referencing the entire dataset, and not just the
group. I just didn't know I could reference the group.
*This* works...
=Sum(Fields!qty_to_ship.Value, "table1_Group1")
*This does not (well, it gives me values for the entire dataset)
=Sum(Fields!qty_to_ship.Value, "devdbds")
FYI. Hopefully this will help someone else.
THANKS
On Apr 24, 9:44 am, rumplyminz <squa...@.gmail.com> wrote:
> Alternatively - is there a way I can use a Matrix Control with only 1
> column? If so, I might be able to live with that.
> Thanks,
> Josh
> On Apr 23, 5:57 pm, rumplyminz <squa...@.gmail.com> wrote:
> > Hi all,
> > I am converting some Crystal reports... and replacing formulas which
> > are automatically grouped in Crystal. Essentially, I am comparing two
> > different date ranges, and need to be able to know how many items were
> > shipped for each customer/item combination within each date range.
> > I have created calculated fields to hold the quantity shipped if that
> > row falls in the appropriate date range... this just adds the quantity
> > shipped in the field if it falls within the appropriate date range.
> > Qty2ShipPD1=IIF(( Parameters!startInvoicePD1.Value< Fields!
> > inv_dt.Value AND Parameters!EndInvoicePD1.Value> Fields!inv_dt.Value),
> > Fields!qty_to_ship.Value,0.0)
> > and
> > Qty2ShipPD2=IIF(( Parameters!startInvoicePD2.Value< Fields!
> > inv_dt.Value AND Parameters!EndInvoicePD2.Value> Fields!inv_dt.Value),
> > Fields!qty_to_ship.Value,0.0)
> > I need to build out a table that looks close to the following example:
> > Cust No
> > Item No Total for the Period
> > {need total quantity here}
> > PD1 {Qty2ShipPD1}
> > PD2 {Qty2ShipPD2}
> > I created a matrix control and dragged cus_no, item_no into the rows,
> > with item_no in the columns, and then summed the Qty2ShipPD1 in the
> > Details...
> > This works, in the sense that I am actually getting grouped
> > information which is correct for the customer/item combinations...
> > But the format won't work, and I have no idea how to do the same thing
> > in a table. Every time I do it in a table, I just get the same,
> > entire sum for the entire query for the two calculated fields.
> > Help?
> > THANKS!
> > Josh

Building a Generic Report Viewer

I am just learning SSRS. I am coming from a Crystal Reports background. In
the app that I am upgrading to SSRS, I had a generic report viewer that I
fed the name of the report. The viewer than opened the report pulled out
the data source and set it and passed through any selection criteria from
the app and then ran the report in the viewer.
Is this possible with SSRS?
Will the report viewer sit in the client side app and I set the processing
mode to remote? I am doing a remote app connecting through a VPN to a SQL
Server 2005 database.
Thanks
BillGWhat you want is the report viewer control (winform or webform) that ships
with VS 2005. There is no viewer control that ships with RS. You have to get
the control via VS 2005 (or VS 2008). It does exactly as you describe.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"BillG" <billgower@.charter.net> wrote in message
news:DC9683FB-6354-44AD-89C9-B1A577541939@.microsoft.com...
>I am just learning SSRS. I am coming from a Crystal Reports background.
>In the app that I am upgrading to SSRS, I had a generic report viewer that
>I fed the name of the report. The viewer than opened the report pulled out
>the data source and set it and passed through any selection criteria from
>the app and then ran the report in the viewer.
> Is this possible with SSRS?
> Will the report viewer sit in the client side app and I set the processing
> mode to remote? I am doing a remote app connecting through a VPN to a SQL
> Server 2005 database.
>
> Thanks
> BillG
>

Build database application on Internet with ISP?

I have been building ASP, ASP.NET and Crystal Reports on SQL server
for a few years now.

I have build them mostly on an Intranet or with companies that have
their own web servers.

I now need to build an application for a company that doesn't not have
a web server. Just a small shop that wants an internet application.
How do I build an database application when the site will be host by
an ISP? The database will be SQL Server 2000, but located locally.
The site will be maintained by the ISP? How does the an application
hosted by an ISP interact with a database located locally (client's
site)?

If someone could point me toward some tutorial or tell me how to do
this, I would appreciate it."CrystalDBA" <tturner6@.hotmail.com> wrote in message
news:b0fe186f.0406030344.5e22f306@.posting.google.c om...
> I have been building ASP, ASP.NET and Crystal Reports on SQL server
> for a few years now.
> I have build them mostly on an Intranet or with companies that have
> their own web servers.
> I now need to build an application for a company that doesn't not have
> a web server. Just a small shop that wants an internet application.
> How do I build an database application when the site will be host by
> an ISP? The database will be SQL Server 2000, but located locally.
> The site will be maintained by the ISP? How does the an application
> hosted by an ISP interact with a database located locally (client's
> site)?
> If someone could point me toward some tutorial or tell me how to do
> this, I would appreciate it.

You may want to consider looking for an ISP who can host MSSQL for you, as
that would help to avoid the security issues of exposing a database server
to the internet. Otherwise, if you can set up a VPN from the ISP's web
server to your MSSQL server, that might be a possible alternative. The web
server would need to have the MSSQL client tools, and it would connect like
any other client.

Simon