Cascading parameters

Hi,
I have created a dataset say "DSMasterList" which has the list of items say
product list and also I have another dataset say "DSDetails" which will have
details of the products for a given productID.
My requirement is to have a report that will print ALL the products from
"DSMasterList" and its appropirate details using "DSDetails".
I tried using cascading parameters but then user will have a select the
product and then will create the product detail report for the selected
product. Is there any way where it will show the product details for all the
products available in the "DSMasterList" dataset without user selecting them.
Thanks & Regards,
RajCreate two independent reports and embed one as a subreport inside the other,
like this:
Create a report with the product details. Use productid as parameter.
Create a report with the master product list. You can use list or table data
region, because they can contain other data regions. Drag a subreport control
inside the list or data region. Go to properties of the subreport and specify
the report which has your product details. You also specify the link
parameter here, so that the product details appear before the correct product.
HTH
Charles Kangai, MCT, MCDBA
"Raj" wrote:
> Hi,
> I have created a dataset say "DSMasterList" which has the list of items say
> product list and also I have another dataset say "DSDetails" which will have
> details of the products for a given productID.
> My requirement is to have a report that will print ALL the products from
> "DSMasterList" and its appropirate details using "DSDetails".
> I tried using cascading parameters but then user will have a select the
> product and then will create the product detail report for the selected
> product. Is there any way where it will show the product details for all the
> products available in the "DSMasterList" dataset without user selecting them.
> Thanks & Regards,
> Raj|||Thanks for the reponse Charles.
I tired using the subreports, but it seems there is some issue with
exporting such reports to excel. It give a error saying "Subreports with
table/matrix cells are ignored." Any Ideas on how to handle this error?
Best Regards,
Rajiv
"Charles Kangai" wrote:
> Create two independent reports and embed one as a subreport inside the other,
> like this:
> Create a report with the product details. Use productid as parameter.
> Create a report with the master product list. You can use list or table data
> region, because they can contain other data regions. Drag a subreport control
> inside the list or data region. Go to properties of the subreport and specify
> the report which has your product details. You also specify the link
> parameter here, so that the product details appear before the correct product.
> HTH
> Charles Kangai, MCT, MCDBA
> "Raj" wrote:
> > Hi,
> >
> > I have created a dataset say "DSMasterList" which has the list of items say
> > product list and also I have another dataset say "DSDetails" which will have
> > details of the products for a given productID.
> >
> > My requirement is to have a report that will print ALL the products from
> > "DSMasterList" and its appropirate details using "DSDetails".
> >
> > I tried using cascading parameters but then user will have a select the
> > product and then will create the product detail report for the selected
> > product. Is there any way where it will show the product details for all the
> > products available in the "DSMasterList" dataset without user selecting them.
> >
> > Thanks & Regards,
> > Raj|||Raj,
I have a report that has salesperson data and underneath in a subreport the
sales details of the salesperson are listed. I even have picture of the
salesperson in the main part. I have used a subreport control inside a list
data region. And it is working. After reading your post I tried exporting it
to Excel, and it exported OK. there may be an issue I am not aware of if you
had a table inside a table. Mine was a table inside a list.
There is another option that also works. You can create master/detail report
by writing one join query to include both the master rows and the detail
rows. You can use a table data region and group by the fields from the master
record and show the detail rows underneath each one. But the first option I
showed you should work, as far as I know.
Cheers!
Charles Kangai, MCT, MCDBA
"Raj" wrote:
> Thanks for the reponse Charles.
> I tired using the subreports, but it seems there is some issue with
> exporting such reports to excel. It give a error saying "Subreports with
> table/matrix cells are ignored." Any Ideas on how to handle this error?
> Best Regards,
> Rajiv
>
> "Charles Kangai" wrote:
> > Create two independent reports and embed one as a subreport inside the other,
> > like this:
> >
> > Create a report with the product details. Use productid as parameter.
> >
> > Create a report with the master product list. You can use list or table data
> > region, because they can contain other data regions. Drag a subreport control
> > inside the list or data region. Go to properties of the subreport and specify
> > the report which has your product details. You also specify the link
> > parameter here, so that the product details appear before the correct product.
> >
> > HTH
> >
> > Charles Kangai, MCT, MCDBA
> >
> > "Raj" wrote:
> >
> > > Hi,
> > >
> > > I have created a dataset say "DSMasterList" which has the list of items say
> > > product list and also I have another dataset say "DSDetails" which will have
> > > details of the products for a given productID.
> > >
> > > My requirement is to have a report that will print ALL the products from
> > > "DSMasterList" and its appropirate details using "DSDetails".
> > >
> > > I tried using cascading parameters but then user will have a select the
> > > product and then will create the product detail report for the selected
> > > product. Is there any way where it will show the product details for all the
> > > products available in the "DSMasterList" dataset without user selecting them.
> > >
> > > Thanks & Regards,
> > > Raj

Cascading Parameters

Hi,

I am using SQL Server 2005 reporting services. I am having a problem with Cascading Parameters. In my report there are 4 parameter . (Product Class, Product Type, Product SubType and Activites). when user open the report page he/she will have to select a value for product class depending on the selection Product Type and Sub type will be populated. So far its good, but Activities also depends on the Product Class. My question is can we have 2 parameters depend on same parameter. Here in this case Product Type and Activities both depend on the Product Class. I want only those activies in the dropdown which belongs to the Product Class that is selected.

Can anyone please give an ideas?

Thanks

Ashwini

Hi Ashwini,

I am assuming that you are using datasets or stored procedures to fill the parameters. If that is the case, This link -MIGHT- get you in the right direction.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=67984&SiteID=1

It is concerning about which dataset is being executed first, and with this, you might be able to execute certain datasets to get what you need and then based the next dataset on the previously executed dataset.

Hope this helps!

Bernard

|||

Hi Bernard,

Yes I have created 4 datasets with stored procedures to fill these report parameters.

SP1 : SelectProductClassAll -->which brings in all the product class

SP2: RptSelectCascadedProductType --> which has a parameter @.selected_product_class and will bring in all the product types for the given product class.

SP3: RptSelectCascadedProductSubType --> which has a parameter @.selected_product_type and will bring in all the product sub-types for the given product types.

SP4: RptSelectCascadedProductSubType --> which has a parameter @.selected_product_class and will bring in all the Activities for the given product class.

Problem is 2nd and 4th SP expects the same parameter. After adding these datasets if you go to Report -->Report Parameters you will only have 2 parameters (which would act as a drop down filter) @.selected_product_class , @.selected_product_type and I added one for Subtypes these 3 drop downs are fine. But I am not able to get the dropdown for activites since my SP4 expects the same parameter as SP1 and I cant use the same name parameter name twice?

I hope I am clear...

I looked at the link which you send it did not give me enough information so I am still confused...

Thanks

Ashwini

|||

HI Ashwini.

Actually you should be able to use the same parameter name.

Have you tried using only 3 parameters instead of 4 parameters ?

I mean, logically speaking, since two SPs are using ONE parameter, I don't see a reason why you would try and use another Report Parameter. Just make sure that the 2 stored procedures are pointing to one Parameter.

The link I posted tells of how datasets can be executed in order depending on what you need. In this case, you would want to execute SP1, before executing Sp2 and Sp3 right ?

Of course unless, I am getting it all confused myself which is not a good thing.

Bernard

|||

Without reading in detail too much, I'd say yes to you can re-use same parameter (same name) for different parameters

e.g. our reports have performance venue dropdown based on the date range selected (@.start_date to @.end_date)

then performance type, based on the venue AND date range

then say seating zones of the venue, based on type AND venue AND date range

|||

Ok may be I am understanding the concept of report parameter wrong...If I only have 3 parameters it will only show 3 dropdown menu when I actually run the report right? for ex: Product class, type and subtype. I need to have another dropdown menu for Activities as well how would I get this if I dont have a report parameter for it?

As per my understanding SRS will create these dropdown menus for you based on the report parameter. Please correct me if I am wrong...

|||In your case, you only have 3 params, isn't it. The #2 and #4 sp share the same parameter which is your Selected_Product_Class. So when user makes a selection for ProductClass both sp should run based on that Product Class. If you want to pass different values of Selected_Product_Class to these 2 sp, then you need to define 2 separate parameters and fill them both with your #1 sp. But in that case you will have 2 Selected_Product_Class, not sure if that's what you intend to do.|||

Ashwini Ramaiah wrote:

I need to have another dropdown menu for Activities as well how would I get this if I dont have a report parameter for it?

Define another parameter and fill it with your #1 sp, just liek you did earlier. And pass the value of this param to your #4 sp. But this way, user will be able to select 2 different values of your ProductClass. If you want them to be same, then just fill this new param with the value of your already selected product class parameter.

|||

Thanks Everyone!!!

Its working now....I am not sure what was the problem. All I did was delete all my parameter and datasets and re do it. I created a 4th parameter and hooked it up with Activities Data which in turn is looking fo SP4 which expects @.selected_Product_Class parameter. Trust me I did try this like 100 times it didnt work that time when I deleted the whole thing and recreated all the parameters its all good now.

I have 4 dropdowns in my Report when user comes in He/She would only be able to select a value from Product Class rest 3 will be grayed once product class is selected Product type and Activities dropdown's data gets populated and the user can select Product Sub type which is 4th dropdown. This is exactly what I wanted.

Once again thanks to everyone!!!

Ashwini

carrying metric through a table

Hi. this is a tricky one to explain but I'm at a bit of a loss

Consider a product demand table such as
Product Demand Qty
Prod 1 500
Prod 2 300
Prod 3 0

Now consider a Inventory table such as
Product Batch Total qty Free qty
Prod 1 Batch 1 300 0
Prod 1 Batch 2 300 100
Prod 1 Batch 3 200 200
Prod 2 Batch 22 600 300

Wha I am trying to achieve is the "Free Qty" column such that, within each product, the demand is "comsumed" by each batch in turn until completly comsumed (or no batches are left). Hence the demand of 500 would result in 300 going to zero for batch 1, carry forward 200 demand which would be completly comsuemd by batch 2 leaving 100 "free". Subsequnet batches would remain all free for that first product. Then the operation continues for the next product.

How on earth do I do it ???

Thanks for you thoughts

GerryJust guessing, but I can't get mental "traction" on this problem. I passed on trying to answer, to let somebody else get a clear shot at it. Since nobody else has posted, I'm betting that they can't get a handle on it either. Maybe it would help if you explain just a wee bit more ?!?

-PatP|||Thanks Pat. I'll try

Perhaps I shoudl explain the physical process.
you have a food store and you have all the "sell buy" dates for each incoming shipment. You have some orders for a given quantity and you want to simulate a FIFO (First in First out) process of use. So the "free to sell" of the oldest stock is reduced by the open orders leaving the newest stock to last. In fact I'm talking about electronic devices and the physical date of manufacture but the analogy is good. Does that explain ?

Gerry|||[QUOTE][SIZE=1]Originally posted by GerryP
Thanks Pat. I'll try

Perhaps I shoudl explain the physical process.
you have a food store and you have all the "sell buy" dates for each incoming shipment. You have some orders for a given quantity and you want to simulate a FIFO (First in First out) process of use. So the "free to sell" of the oldest stock is reduced by the open orders leaving the newest stock to last. In fact I'm talking about electronic devices and the physical date of manufacture but the analogy is good. Does that explain ?

TO put it another way. here is a slice of the documentation I ma doing (ahead of the code.. Weird!)

Take a demand of 1000 and a Batch content as here
Batch Qty Free
Batch 1 500 0 (1000-500 carried forward demand= 500)
Batch 2 300 0 (500 300 Carried forward demand= 200
Batch 3 450 250 All consumed: nothing carried forward
Batch 4 400 400 All consumed: nothing carried forward|||CREATE TABLE Demand(
Product smallint PRIMARY KEY,
Qty smallint)

INSERT Demand
SELECT 1, 500
UNION ALL SELECT 2, 300
UNION ALL SELECT 3, 0

CREATE TABLE Inventory(
Product smallint,
Batch smallint,
Totalqty smallint,
PRIMARY KEY(Product,Batch))

INSERT Inventory
SELECT 1, 1, 300, 0
UNION ALL SELECT 1, 2, 300, 100
UNION ALL SELECT 1, 3, 200, 200
UNION ALL SELECT 2, 22, 600, 300

SELECT i.Product, i.Batch, i.TotalQty,
CASE
WHEN
(SELECT SUM(TotalQty)
FROM Inventory
WHERE Product = i.Product AND Batch <= i.Batch) <= d.Qty
THEN 0
WHEN
(SELECT SUM(TotalQty)
FROM Inventory
WHERE Product = i.Product AND Batch <= i.Batch) - d.Qty < TotalQty
THEN
(SELECT SUM(TotalQty)
FROM Inventory
WHERE Product = i.Product AND Batch <= i.Batch) - d.Qty
ELSE TotalQty
END FreeQty
FROM Inventory i LEFT JOIN Demand d ON i.Product = d.Product

If there will always be row in Demand for each product, then just use inner join instead of left join.|||It worked a DREAM.
I think you missed out defining a fourth metric in Inventory but thats fine. It's good for me to add just a little value.

My question now is.. How does it work ?
reformating the core somewhat we see
SELECT i.Product, i.Batch, i.TotalQty,
CASE
WHEN
(SELECT SUM(TotalQty) FROM Inventory WHERE Product = i.Product AND Batch <= i.Batch) <= d.Qty THEN 0
WHEN
(SELECT SUM(TotalQty) FROM Inventory WHERE Product = i.Product AND Batch <= i.Batch) - d.Qty < TotalQty
THEN
(SELECT SUM(TotalQty) FROM Inventory WHERE Product = i.Product AND Batch <= i.Batch) - d.Qty
ELSE TotalQty
END FreeQty
FROM Inventory i LEFT JOIN Demand d ON i.Product = d.Product

what is not clear is how it carries the 'remainder' down from one batch to the next ? There is no question it works... but how ?

Thanks again

Gerry|||Do not use code unless you understand it.

Try this query to understand value of subquery:

SELECT i.Product, i.Batch, i.TotalQty, d.Qty,
(SELECT SUM(TotalQty)
FROM Inventory
WHERE Product = i.Product AND Batch <= i.Batch) CumulativeTotal
FROM Inventory i LEFT JOIN Demand d ON i.Product = d.Product

Unless this is static OLAP database, FreeQty column should not be denormalized into Inventory table because that will add complexity to OLTP code and hinder performance.|||Ah. The proverbial penny has dropped and I understand !

I've applied it to my real world example with excellent results

Thanks for your help and have a good weekend

Gerry

Cant work out this SQL to return 4 random records

I've got a product table in SQL 2000 that contains say these rows:

ProductID int
ProductName varchar
IsSpecial bit

I want to always return 4 random specials from a query and can do this fine by using:

SELECT TOP 4 ProductID,ProductName
FROM Products WHERE IsSpecial = 1
ORDER BY NEWID()

This works ok if there are 4 products marked as specials but the problem is i always need 4 records returned but if only 2 products are marked as special then only 2 records are returned.

What i really need is something in there that says if <4 records are returned then just add random non-special products to make the total products returned up to 4. So it should always be 4 records but preference is given to specials if you see what i mean?

Is this possible?

Thanks

Hello my friend,

The following SQL should work. If you have any questions or problems with it, please let me know.

Kind regards

Scotty

SELECT TOP 4 * FROM
(
SELECT TOP 4 ProductID, ProductName FROM Products WHERE IsSpecial = 1

UNION ALL

SELECT ProductID, ProductName FROM Products WHERE IsSpecial = 0

) myTable

ORDER BY IsSpecial DESC, NewID()

|||

Excellent Scotty, that does exactly what i was looking for!

Cheers.