How to print multiple column values in column groups?
-
Hello All,
I am newbie. Just started using SSRS 1 week before.
I want all your help.
I designed a Report by using both Row Groups and Column Groups.
----------------------------------------------------------
DistrictName StoreName EmployeeName Category_type
Month_name
-----------------------------------------------------------
DistrictName, StoreName, EmployeeName is row column and
Category_type and Month_name is column groups.
I want to print the sum of some values under the month.
The output should be like below.
---------------------------------------------------------------------------------
DistrictName StoreName EmployeeName CATEGORY I CATEGORY I
Jan Dec Oct Nov Jan Dec Oct Nov
-------------------------------- -------------------------------------------------
Hawaii Name1 Emp1 8 7 8 7 2 7 8 28
Hawaii Name2 Emp2 3 78 12 9 7 89 2 8
I dont know how to print the sum values under the month. Now i came upto here as like below and dont know how to print the values. Since it creates columns dynamically when it run. So i dont know how to print it.
---------------------------------------------------------------------------------
DistrictName StoreName EmployeeName CATEGORY I CATEGORY I
Jan Dec Oct Nov Jan Dec Oct Nov
---------------------------------------------------------------------------------
Hawaii Name1 Emp1
Hawaii Name2 Emp2
Could you please help me to fix this problem?
Waiting for your reply
-
Note sure exactly what your'e after, but if it is to show months under each category and the count of rows under each month then
Add a matrix to the body of the report
Drag DistrictName from dataset to Rows box
Drag Storename to DistrictName box (not heading)
Drag EmployeeName to Storename box (not heading)
Drag Category_type to Columns box
Drag Month_name to Category_type box (not heading)
Right click in Data box and select Expression
After equals sign type Sum(1)
Click OK
Test report
Note that the months will be shown in alphabetical order
Far away is close at hand in the images of elsewhere.
Anon. -
Hello David,
Thanks for your reply.
I have already added Matric table and drag DistrictName, Storename,EmployeeName,
Category_type, Month_name.
My SQL query is
SELECT
Districtname, StoreName, Employee_Name,
b.category_type, DATEPART(MM,Date) as Month, DATEPART(YYYY,Date) as Year,
sum([SMTCategoryIwoD]+[SMTCategoryIwD]+[M_SMTCategoryIwoD]+[M_SMTCategoryIwD]) OVER () as [SMTCategoryIOpportunitiesCount]
,sum([SMTCategoryIIwoD]+[SMTCategoryIIwD]+[M_SMTCategoryIIwoD]+[M_SMTCategoryIIwD]) OVER () as [SMTCategoryIIOpportunitiesCount]
,sum([SMTCategoryIIIwoD]+[SMTCategoryIIIwD]+[M_SMTCategoryIIIwoD]+[M_SMTCategoryIIIwD]) OVER () as [SMTCategoryIIIOpportunitiesCount]
,SUM([Ai]+[Ui]+[MPi]+[MSi]) OVER () as [iPhoneOpportunitiesCount]
,SUM([ActivationCount]+[UpgradeCount]+[MPCount]+[MSCount]) OVER () as [BasicPhoneOpportunitiesCount]
,SUM([WHPCount]) OVER () as [WHPCount]
,SUM([UVERSETV]) OVER () as [UVERSETV]
,SUM([UVERSEHSI]) OVER () as [UVERSEHSI]
,SUM([TabCommittedCount]+[TabMSCount]) OVER () as [TabletOpportunitiesCount]
,SUM([MySwapCount]) OVER () as [MySwapCount]
,SUM([PrepaidCount]) OVER () as [PrepaidCount]
FROM tb_ClassifiedDailySubTotal_v3, tb_ClassifiedDailySubTotalCategoryType b
WHERE Date between DATEADD(MM,0,DATEADD(month,-2,@startdte)) and @startdte
GROUP BY Districtname, StoreName, Employee_Name, b.category_type,DATEPART(MM,Date),
DATEPART(YYYY,Date),SMTCategoryIwoD,SMTCategoryIwD,M_SMTCategoryIwoD, M_SMTCategoryIwD,SMTCategoryIIwoD,SMTCategoryIIwD,M_SMTCategoryIIwoD,M_SMTCategoryIIwD ,SMTCategoryIIIwoD,SMTCategoryIIIwD,M_SMTCategoryIIIwoD,M_SMTCategoryIIIwD
,Ai,Ui,MPi,MSi,ActivationCount,UpgradeCount,MPCount,MSCount,WHPCount,UVERSETV,UVERSEHSI,
TabCommittedCount,TabMSCount,MySwapCount,PrepaidCount
Now i want to print the
sum(CAT1) for Jan month, sum(CAT1) for Feb month, sum(CAT1) for Feb month,
sum(CAT2) for Jan month, sum(CAT2) for Feb month, sum(CAT2) for Feb month, etc
Since the catergory_name and month creates field dynamically. so i don't know how to do it. Do u understand what i mean?
Below i have attached screenshots. Plz have a look.
Waiting for reply.
-
What do you mean by 'sum(CAT1) for Jan month'
CAT1 is a category
What value do you want below each month for each category, your query seems to have a few summed values and year which you did not mention regarding the report
If you post DDL and sample data for the tables and what the report output should look like, I might get a better idea of what you are trying to achieve.
Far away is close at hand in the images of elsewhere.
Anon. -
Hello,
Better i will explain with example.
The report data should be like below
------------------------------------------------------------------
Month CAT1 CAT2 Districtname Storename Employeename
-------------------------------------------------------------------
Jan 5 4 district1 store1 employee1
Jan 5 4 district1 store1 employee1
Jan 5 4 district1 store1 employee1
Feb 10 1 district1 store1 employee1
Feb 10 1 district1 store1 employee1
March 3 2 district1 store1 employee1
March 3 2 district1 store1 employee1
March 3 2 district1 store1 employee1
The o/p should print like
------------------------------------------------------------------------------
| CAT 1 | CAT 2
|
DistrictName Storename Employeename | Jan Feb March | Jan Feb March
------------------------------------------------------------------------------
district1 store1 employee1 15 20 9 12 2 6
By using Row group and column group, i printed districtname, storename, employeename, category title and month name. But dont know how to print the SUM values.
I am getting output lik below. Dont know how to print SUM values for month for all category. For example, i mentioned 2 category(CAT1 and CAT2). But in real, i have many categories. Please help me.
------------------------------------------------------------------------------
| CAT 1 | CAT 2
|
DistrictName Storename Employeename | Jan Feb March | Jan Feb March
------------------------------------------------------------------------------
district1 store1 employee1
-
First you need to change your query to produce the following
MonthCategoryCategoryValueDistrictnameStorenameEmployeename
JanCAT15district1store1employee1
JanCAT15district1store1employee1
JanCAT15district1store1employee1
JanCAT24district1store1employee1
JanCAT24district1store1employee1
JanCAT24district1store1employee1
FebCAT110district1store1employee1
FebCAT110district1store1employee1
FebCAT21district1store1employee1
FebCAT21district1store1employee1
MarchCAT13district1store1employee1
MarchCAT13district1store1employee1
MarchCAT13district1store1employee1
MarchCAT22district1store1employee1
MarchCAT22district1store1employee1
MarchCAT22district1store1employee1
Then using the wizard create the matrix like this
Far away is close at hand in the images of elsewhere.
Anon. -
Thank you very much for your kind reply. I will do that.
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply