How to create the desired crystal report as mentioned.

Question

0.00/5 (No votes)

See more:

I have a Table 

ProductID  SoreID EmployeeID	ProdType	ProdQTy		Price	DateBrought

1		 R0001	 E0001	         UCO		12		  2350.0000	02-Feb-19
2		 MK001	 E0019		 UCO		15		     4000.0000	02-Feb-19
3		 MD001	 E0017		 UCO		10		     2350.0000	03-Mar-19
4		 MD001	 E0018		 UCO		20		     3500.0000	03-Mar-19
5		 MK001	 E0020		 UCO		14		     3700.0000	02-Feb-19
6		 R0001	 E0003		 UCO		11		     3000.0000	02-Feb-19
7		 R0001	 E0004		 UCO		14		     3500.0000	02-Feb-19


I Want a report such as below one 

StoreID  Empid   02-Feb-19  03-Feb-19 04-Feb-19  05-Feb-19 ---- 28-Feb-19   Total
R0001    E0001	      12	  10	      7	         8	           11       xx
MK001    E0002	      10	  10	      6	         5	           10       xx

What I have tried:

I have applied a cross tab to the report but not getting the desired format.

Posted 16-Apr-19 1:42am

Member 14108724

Updated 17-Apr-19 23:34pm

v5

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Answer 1 · 2019-04-16T08:18:00

Solution 1

You have to create pivot query to convert rows into columns. See:
Using PIVOT and UNPIVOT - SQL Server | Microsoft Docs[^]
Simple Way To Use Pivot In SQL Query[^]
SQL Server: PIVOT Clause[^]
SQL SERVER – PIVOT and UNPIVOT Table Examples[^]
Script to create dynamic PIVOT queries in SQL Server[^]

Good luck!

Posted 16-Apr-19 8:18am

Maciej Los

Comments

Member 14108724 17-Apr-19 5:16am

Thank you very much Sir. Let me try this.