Distinct Parameteres Needed - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Development Tools

Reporting Services Development

Distinct Parameteres Needed

Author

Topic

cjpsx
Starting Member

34 Posts

Posted - 2006-05-23 : 10:35:47

I am adding a parameter to a report. I am pulling the value field from the dataset of the report (From Query). As a result, the user has to scroll through repeated parameters. Is there a way to make the parameter values distinct? I thought about adding another dataset but am unsure how to tie that dataset with the report dataset.

I think I'm going to have to get a SSRS book. I'm signed up for a class next month but may not be able to wait that long.

Thanks,
cj

JoeNak
Constraint Violating Yak Guru

292 Posts

Posted - 2006-05-23 : 13:39:29

Another data set is the best approach.

1. Create a 2nd dataset.
2. Go to your report parameters, available values should be "from query". Change dataset to your new dataset, set value field, set label field.

That's pretty much it.

cjpsx
Starting Member

34 Posts

Posted - 2006-05-23 : 13:48:06

I have done that but I cannot develop a relation between the two datasets. If I select an ID from the parameter dataset, then that selection is not reflected in the report dataset. The report will pull all data regardless of the parameter selection

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-05-23 : 14:02:31

You just need to make sure that in both data sets, you have set the value of the parameter to the same thing that you set in the other. I have two data sets in one report. Both have @NMCAccount as a parameter. Both have its value set to =Parameters!NMCAccount.Value. I then prompt for this value in the report and both data sets get the value.

Tara Kizer
aka tduggan

cjpsx
Starting Member

34 Posts

Posted - 2006-05-23 : 14:24:36

Are you putting the @parameter in your SQL or in one of the dialog boxes?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-05-23 : 14:29:50

It's in my data sets (hit the data tab, then cilck the ... button, then go to Parameters tab) as well as in the report parameters (in Visual Studio, go to Report menu option, then to Report Parameters).

Tara Kizer
aka tduggan

cjpsx
Starting Member

34 Posts

Posted - 2006-05-23 : 14:47:16

Would this method work if I have two datasets but the selection in the parameter dataset determines the output in the dataset in the report?

For example: My Parameter Dataset Has: 'ID1', 'ID2', 'ID3'

The user selects 'ID2'

So the report dataset should only pull data where report.ID = 'ID2'

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-05-23 : 14:48:18

I'd have to see the queries involved in the data sets to answer the question.

Tara Kizer
aka tduggan

cjpsx
Starting Member

34 Posts

Posted - 2006-05-23 : 14:53:53

Query/Dataset 1 for the Report Is:
SELECT ai.invoicenumber,
ai.createdate,
ek.carriermemid,
op.orgpolicyid, --THIS FIELD NEEDS TO BE FILTERED BY THE PARAMETER IN THE OTHER DATASET BELOW
eo.secondaryid as groupnumber,
m.secondaryid as membernumber,
ar.fullname as membername,
e.addr1 as address1,
e.addr2 as address2,
(LTRIM(RTRIM(e.city)) + ' ' + e.state + ' ' + e.zip) as citystatezip,
ISNULL(CSander.getBalanceForward(ar.araccountid, ai.arinvoiceid, ai.invoicestart), 0.00) as balanceforward,
ISNULL(CSander.getPaymentReceived(ai.arinvoiceid), 0.00) as paymentreceived,
eo.fullname as groupname,
CSander.getRider('M', m.memid, ai.arinvoiceid) as medicalrider,
ISNULL(CSander.getPremiumAmt('M', m.memid, ai.arinvoiceid), 0.00) as medical_premiumamt,
CSander.getRider('P', m.memid, ai.arinvoiceid) as pharmrider,
ISNULL(CSander.getPremiumAmt('P', m.memid, ai.arinvoiceid), 0.00) as pharm_premiumamt,
ISNULL(CAST(csander.getSubsidyAmt(ek.enrollid, op.orgpolicyid, ai.createdate) as money), 0.00) as subsidy_amt,
aa.adjustmentdate,
adr.description,
aa.adjustmentamt,
ai.invoiceamt as amount_due,
ai.arcreditamt,
pd1.orgpolicyid as orgpolicyid1,
pd1.billedstart as billedstart1,
CONVERT(CHAR(10), count(pd1.enrollid_pisd)) as c1_coverage,
CONVERT(CHAR(10), count(pd1.enrollid_pid)) as c1_family,
pd1.bplan as bplan1,
pd1.rider as rider1,
pd1.premiumamt as premiumamt1,
pd2.orgpolicyid as orgpolicyid2,
pd2.billedstart as billedstart2,
CONVERT(CHAR(10), count(pd2.enrollid_pisd)) as c2_coverage,
CONVERT(CHAR(10), count(pd2.enrollid_pid)) as c2_family,
pd2.bplan as bplan2,
pd2.rider as rider2,
pd2.premiumamt as premiumamt2,
pd3.orgpolicyid as orgpolicyid3,
pd3.billedstart as billedstart3,
CONVERT(CHAR(10), count(pd3.enrollid_pisd)) as c3_coverage,
CONVERT(CHAR(10), count(pd3.enrollid_pid)) as c3_family,
pd3.bplan as bplan3,
pd3.rider as rider3,
pd3.premiumamt as premiumamt3
FROM...WHERE...GROUP BY

Query/Dataset 2 for the parameters is:

SELECT DISTINCT orgpolicyid
FROM dbo.orgpolicy

Dataset 2 is in the drop-down selection. How can I get data from Dataset 1 to only show those records tied to the drop-down selection?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-05-23 : 15:08:51

Both data sets will need a WHERE clause on them that use the parameters.

Here is an example:

SELECT ...
FROM SomeTable
WHERE SomeColunm = @SomeParm AND SomeColumnA = 1...

SELECT ...
FROM SomeOtherTable
WHERE SomeColumnOrOtherColumn = @SomeParm

Then your report has a report parameter @SomeParm so that when it gets set by the user, both data sets will get the value.

Tara Kizer
aka tduggan

cjpsx
Starting Member

34 Posts

Posted - 2006-05-23 : 15:21:31

Why would I want a parameter on the second dataset when I want all data do display in the selection box?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-05-23 : 15:22:26

I don't know. I don't have a full grasp on what you are trying to do. Could you provide a data example of what you want?

Tara Kizer
aka tduggan

JoeNak
Constraint Violating Yak Guru

292 Posts

Posted - 2006-05-25 : 09:27:30

The second query is fine since all you want it for is populating the drop down. Like Tara says your first query will need a WHERE clause...

WHERE orgpolicyid = @OrgPolicyID

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources