• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 203
  • Last Modified:

MSSQL Reporting 2005, Mutiple Datasets on one Table

Is it possible to use 2 different datasets on the same table, somehow to link them. I have 3 different queries which pull data on a week trend(2 weeks, 4 weeks and 6 weeks). The columns are all the same and they are grouped by a primary manager. I knwo i can have these datasets used in different tables so the problem here is how do i display all the detail data from 3 datasets on one table.
0
zx10r
Asked:
zx10r
  • 6
  • 4
1 Solution
 
SQL_SERVER_DBACommented:
create of stored procedure with the 3 queries so you can obtain one dataset, then group the header based on trend. let me know how it goes...
0
 
zx10rAuthor Commented:
I have to group by Manager and can't group by trend. Here is the query in case you are wondering. Is it still possible with this data? The only difference in queries is how much they trend back as in -28 days or -56, etc

SELECT
goto_last_name as 'Manager',
advisor_ao_number as 'AO',
COUNT (CASE advisor_termination_date WHEN '<Null>' THEN 1 END) as '#Advisors',
SUM(Weekly_TOS_GDC) as 'TOS GDC',
SUM(Weekly_Trail_GDC) as 'Trail GDC',
SUM(Weekly_Total_GDC) as 'Total GDC',
SUM(Calendar_Year_to_Date_TOSGDC) as 'YTD TOS GDC',
SUM(Calendar_Year_to_Date_Trail_GDC) as 'YTD Trail GDC',
SUM(Calendar_Year_to_Date_Total_GDC) as 'YTD Total GDC',
SUM(Calendar_Year_to_Date_Fees) as 'YTD Fees',
SUM(weekly_cas) as 'Weekly CAs',
SUM(weekly_plans) as 'Weekly Plans',
SUM(weekly_fees) as 'Weekly Fees',
SUM(Calendar_Year_to_Date_Plans) as 'YTD PLans',
SUM(Calendar_Year_to_Date_CAs) as 'YTD CAs'
FROM weekly_condensed_tb
where advisor_platform_number = 1
and market_group_number = @MG
and goto_last_name <> 'unassigned'
and report_date BETWEEN CAST(CONVERT(VARCHAR,@Date,112) AS DATETIME) -28 and CAST(CONVERT(VARCHAR,@Date,112) AS DATETIME)
GROUP BY goto_last_name,advisor_ao_number
HAVING COUNT (CASE advisor_termination_date WHEN '<Null>' THEN 1 END) > 1
0
 
PFrogCommented:
Your best option is to combine all 3 queries into one by using UNION, you can then show them all on the same table.

i.e.
SELECT xxxx
UNION
SELECT xxxx
UNION
SELECT xxxx

You'll probably want to add another field to indicate the trend
SELECT xxxxx, 2 as trend
UNION
SELECT xxxxx, 4 as trend
etc.

To get your grouping to work, you may need to include the unions within an outer sub query
i.e.
SELECT * FROM
( SELECT xxx UNION SELECT xxxx UNION SELECT xxxxx) tbl1
GROUP BY tbl1.xxx

Alternatively, you could wrap this up with sql_server_dba's suggestion of a stored proc, either using union, or temporary tables. The problem with temporary tables is that SSRS can't see them, and so causes big problems in the design view.

Once you've got them in the same dataset, you can group them however you like - manager, trend or anything else you like...
0
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 
zx10rAuthor Commented:
I tried with the union, sub query and SP but all i get back is results for one trend and not 2 or 3. Can anyone post how i need to write this query based on what i provided above to get the sums of all the columns with different trends. Thanks
0
 
zx10rAuthor Commented:
to be more clear as to the result i am looking for. An example the column SUM(Weekly_TOS_GDC) as 'TOS GDC',

So i need one column to return the SUM of that for 2 weeks and then a different one for SUM of 4 weeks and so on
0
 
PFrogCommented:
Right ok - the union will append more rows, each row having a different number of weeks. What you want it more columns not more rows. The only way to do this would be a lot of sub queries, which would be very slow to run.

I would recommend sticking to the union, and changing your report structure to use a matrix control.


0
 
zx10rAuthor Commented:
i did do it with the union but now i have a problem in grouping. When i try to group by CAST(CONVERT(VARCHAR,'9/11/2007',112) AS DATETIME) -28, because it is an expression SQl gives me an error. Any idea how i can get past this. I have to group by this so i get back different sums for different ranges.

SELECT
goto_last_name as 'Manager',
advisor_name,
advisor_ao_number as 'AO',
COUNT (CASE advisor_termination_date WHEN '<Null>' THEN 1 END) as '#Advisors',
CAST(CONVERT(VARCHAR,'9/11/2007',112) AS DATETIME) -28 as 'date',
SUM(Weekly_TOS_GDC) as 'TOS GDC'
FROM weekly_condensed_tb
WHERE advisor_platform_number = 1
AND report_date BETWEEN CAST(CONVERT(VARCHAR,'9/11/2007',112) AS DATETIME) -28
AND CAST(CONVERT(VARCHAR,'9/11/2007',112) AS DATETIME)
AND goto_last_name = 'burk'
GROUP BY goto_last_name,advisor_ao_number,advisor_name,CAST(CONVERT(VARCHAR,'9/11/2007',112) AS DATETIME) -28
HAVING COUNT (CASE advisor_termination_date WHEN '<Null>' THEN 1 END) > 1
union all
SELECT
goto_last_name as 'Manager',
advisor_name,
advisor_ao_number as 'AO',
COUNT (CASE advisor_termination_date WHEN '<Null>' THEN 1 END) as '#Advisors',
CAST(CONVERT(VARCHAR,'9/11/2007',112) AS DATETIME) -56 as 'date',
SUM(Weekly_TOS_GDC) as 'TOS GDC'
FROM weekly_condensed_tb
WHERE advisor_platform_number = 1
AND report_date BETWEEN CAST(CONVERT(VARCHAR,'9/11/2007',112) AS DATETIME) -56
AND CAST(CONVERT(VARCHAR,'9/11/2007',112) AS DATETIME)
AND goto_last_name = 'burk'
GROUP BY goto_last_name,advisor_ao_number,advisor_name,CAST(CONVERT(VARCHAR,'9/11/2007',112) AS DATETIME) -56
HAVING COUNT (CASE advisor_termination_date WHEN '<Null>' THEN 1 END) > 1
0
 
PFrogCommented:
You don't need to group by the cast(convert(etc.
Just add an extra field onto each query that identifies which range the data applies to.

i.e. using this (this may need some corrections - I can't test it!) you can create a matrix using Manager/AO and Range as the row and column groupings

SELECT
Manager,
AO,
Range,
COUNT ('#Advisors') AS '#Advisors',
SUM(Weekly_TOS_GDC) as 'TOS GDC',
SUM(Weekly_Trail_GDC) as 'Trail GDC',
SUM(Weekly_Total_GDC) as 'Total GDC',
SUM(Calendar_Year_to_Date_TOSGDC) as 'YTD TOS GDC',
SUM(Calendar_Year_to_Date_Trail_GDC) as 'YTD Trail GDC',
SUM(Calendar_Year_to_Date_Total_GDC) as 'YTD Total GDC',
SUM(Calendar_Year_to_Date_Fees) as 'YTD Fees',
SUM(weekly_cas) as 'Weekly CAs',
SUM(weekly_plans) as 'Weekly Plans',
SUM(weekly_fees) as 'Weekly Fees',
SUM(Calendar_Year_to_Date_Plans) as 'YTD PLans',
SUM(Calendar_Year_to_Date_CAs) as 'YTD CAs'

FROM (

  SELECT
   goto_last_name as 'Manager',
   advisor_ao_number as 'AO',
   14 AS Range,
   CASE advisor_termination_date WHEN '<Null>' THEN 1 END as '#Advisors',
   Weekly_TOS_GDC,
   Weekly_Trail_GDC,
   Weekly_Total_GDC,
   Calendar_Year_to_Date_TOSGDC,
   Calendar_Year_to_Date_Trail_GDC,
   Calendar_Year_to_Date_Total_GDC,
   Calendar_Year_to_Date_Fees,
   weekly_cas,
   weekly_plans,
   weekly_fees,
   Calendar_Year_to_Date_Plans,
   Calendar_Year_to_Date_CAs
  FROM weekly_condensed_tb
  where advisor_platform_number = 1
   and market_group_number = @MG
   and goto_last_name <> 'unassigned'
   and report_date BETWEEN CAST(CONVERT(VARCHAR,@Date,112) AS DATETIME) -14 and CAST(CONVERT(VARCHAR,@Date,112) AS DATETIME)
 
  UNION
 
  SELECT
   goto_last_name as 'Manager',
   advisor_ao_number as 'AO',
   28 AS Range,
   CASE advisor_termination_date WHEN '<Null>' THEN 1 END as '#Advisors',
   Weekly_TOS_GDC,
   Weekly_Trail_GDC,
   Weekly_Total_GDC,
   Calendar_Year_to_Date_TOSGDC,
   Calendar_Year_to_Date_Trail_GDC,
   Calendar_Year_to_Date_Total_GDC,
   Calendar_Year_to_Date_Fees,
   weekly_cas,
   weekly_plans,
   weekly_fees,
   Calendar_Year_to_Date_Plans,
   Calendar_Year_to_Date_CAs
  FROM weekly_condensed_tb
  where advisor_platform_number = 1
   and market_group_number = @MG
   and goto_last_name <> 'unassigned'
   and report_date BETWEEN CAST(CONVERT(VARCHAR,@Date,112) AS DATETIME) -28 and CAST(CONVERT(VARCHAR,@Date,112) AS DATETIME)
 
  UNION
 
  SELECT
   goto_last_name as 'Manager',
   advisor_ao_number as 'AO',
   42 AS Range,
   CASE advisor_termination_date WHEN '<Null>' THEN 1 END as '#Advisors',
   Weekly_TOS_GDC,
   Weekly_Trail_GDC,
   Weekly_Total_GDC,
   Calendar_Year_to_Date_TOSGDC,
   Calendar_Year_to_Date_Trail_GDC,
   Calendar_Year_to_Date_Total_GDC,
   Calendar_Year_to_Date_Fees,
   weekly_cas,
   weekly_plans,
   weekly_fees,
   Calendar_Year_to_Date_Plans,
   Calendar_Year_to_Date_CAs
  FROM weekly_condensed_tb
  where advisor_platform_number = 1
   and market_group_number = @MG
   and goto_last_name <> 'unassigned'
   and report_date BETWEEN CAST(CONVERT(VARCHAR,@Date,112) AS DATETIME) -42 and CAST(CONVERT(VARCHAR,@Date,112) AS DATETIME)
 
) tbl

GROUP BY Manager, AO, Range
HAVING COUNT (CASE advisor_termination_date WHEN '<Null>' THEN 1 END) > 1
0
 
zx10rAuthor Commented:
Works perfectly PFrog. I never knew about using Range. It just helped me solve 3 other problems i had =)
0
 
PFrogCommented:
No problem - glad to help!

btw Range is just an arbitrary name I gave to that field, not a keyword
0
 
zx10rAuthor Commented:
Yeah i know. But the concept itself seems of great use. Thanks!
0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

  • 6
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now