So I've been working on a report for a while now. I'm still a noob, so I'm not sure how to deal with my current problem, which are duplicate parameter values.
Here's a screenshot of my parameters. Paramater One parameter dropdown has to do with my Programs, while the second is Activities. As you can see, there are duplicates of "Basics for", when both programs are selected.
Here's a snippet of my database. Database A different ID is attached to the two different programs.
Finally, here's the code for my parameters.
Program:
SELECT distinct
A.ProgramID, A.ProgramName
FROM dwh.Bks_DWH_Programs A
INNER JOIN dwh.Bks_DWH_OrgUnits d on A.ProgramOrgUnitID = d.OrgUnitID
WHERE d.OrgUnitParentID in (@OP) and
d.OrgUnitID in (@OU)
Activity:
SELECT distinct A.Activity_ID, A.Activity_Name
FROM dwh.Bks_DWH_Activities A
INNER JOIN dwh.Bks_DWH_Programs aa on a.Activity_EntityID = aa.ProgramID
INNER JOIN dwh.Bks_DWH_OrgUnits d on aa.ProgramOrgUnitID = d.OrgUnitID
WHERE d.OrgUnitParentID in (@OP) and
d.OrgUnitID in (@OU) and
aa.ProgramID in (@TP)
ORDER by A.Activity_ID
I understand WHY there are duplicates (since the programs have different IDs), but I'm just wondering if there's a way to somehow join the two? It's annoying having duplicates in the dropdown.
Thank you.
Too long for comment. I suppose you could look at the name of the parameter. So essentially you would just remove A.Activity_ID from your Activity query. But then you would also need to do something different in your main query (assuming you're filtering on the A.Activity_ID values being passed).
Your Activity query would look like:
SELECT DISTINCT A.Activity_Name
FROM...
And your main query would have something like:
WHERE d.OrgUnitParentID IN (@OP)
AND d.OrgUnitID IN (@OU)
AND aa.ProgramID IN (@TP)
AND a.Activity_Name = @[YourActivityParameter]
or...
AND a.Activity_Name IN (@[YourActivityParameter])
Note: This may have performance implications.